Yahoo

/ We need to write a query api to a mail storage system. For practical reasons, we want to define some method that takes some sort of query structure (input) that supports our use cases listed below, and outputs a sql statement (string). We don't need to execute the statement, we just want to generate sql statements with this method. Context: - We have a relational database as our underlying data store. - Our database engine supports standard sql operators (AND, OR, =, !=, <=, >=, etc) and a special search function: SEARCH(, ) that allows us to perform full-text search queries against our stored mail. - We are using the schema/table below. Use cases to support: - (basic filtering) : List messages on a folderId ordered by delivery date(DeliveryTs) in descending. ---> SELECT * FROM Message WHERE FolderId=1 ORDER BY DeliveryTs DESC - (basic pagination) : List 100 messages after the first 100 on a folderId ordered by Subject in ascending. ---> SELECT * FROM Message WHERE FolderId=1 ORDER BY Subject ASC LIMIT 100 OFFSET 100; - (ORing) List messages that contain either: - "Promotion" in the Subject - Have [email protected] as the Sender (From column) - Have "Order Number" in the body. ---> SELECT * FROM Message WHERE SEARCH(Subject, "Promotion") OR From="[email protected]" OR SEARCH(Body, "Order Number") ORDER BY DeliveryTs DESC; - (ANDing) List messages that contain both of the following: - "Promotion" in the Subject - Have "Order Number" in the body. SELECT * FROM Message WHERE SEARCH(Subject, "Promotion") AND SEARCH(Body, "Order Number") ORDER BY DeliveryTs DESC - (complex nested logical operators) - List messages that belong to a folderId, OR have "test" in the subject column AND belong to a different folderId. --> SELECT * FROM Message WHERE FolderId=1 OR (FolderId=2 AND SEARCH(Subject, "test")) ORDER BY DeliveryTs DESC Input: Columns - ALL () - List WHERE - Input Data Structure (Column X, Value Y, Operator [=, SEARCH]) List (Column, Value, Operator) Logical Operator (AND|OR) ORDER BY (Column X, ColumnOrder ASC|DESC) List LIMIT (Int) OFFSET (Int) public static String query(List column, WhereClause whereClause, List orders, Integer limit, Integer offset){ FilterCriteria - String Column - String value - Operator operator (EQUALS, SEARCH, NOT_EQUALS) WhereClause - List criteria - LogicalOperator (OR, AND) - FilterCriteria } TreeNode - WhereClause - next FolderId=1 OR (FolderId=2 AND SEARCH(Subject, "test")) FilterCriteria fs1 = new FilterCriteria(FolderId, 1, EQUALS); FilterCriteria fs2 = new FilterCriteria(FolderId, 2, EQUALS); FilterCriteria fs3 = new FilterCriteria(Subject, "test", SEARCH); WhereClause ws = new WhereClause(List.of( new WhereClause(List.of(fs1)), new WhereClause(List.of(fs2, fs3), AND)), OR) TreeNode.addFilterCriteria(fs); if("OR"){ } new TreeNode(List.of(new WhereClause(FolderId, 1, EQUALS), ) Schema: +----------------+--------+ | Column | Type | +----------------+--------+ | UserId | Int | +----------------+--------+ | MessageId | Int | +----------------+--------+ | Subject | String | +----------------+--------+ | DeliveryTs | Int | +----------------+--------+ | From | String | +----------------+--------+ | To | String | +----------------+--------+ | Body | String | +----------------+--------+ | FolderId | Int | +----------------+--------+ | ConversationId | String | +----------------+--------+ / import java.io.; import java.util.; / * To execute Java, please define "static void main" on a class * named Solution. * * If you need more classes, simply define them inline. */ class Solution { public static void main(String[] args) { } }