/*
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 fake@yahoo.com as the Sender (From column)
- Have "Order Number" in the body.
---> SELECT * FROM Message WHERE SEARCH(Subject, "Promotion") OR From="fake@yahoo.com" 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<String>
WHERE - Input Data Structure (Column X, Value Y, Operator [=, SEARCH])
List<FilterCriteria> (Column, Value, Operator)
Logical Operator (AND|OR)
ORDER BY (Column X, ColumnOrder ASC|DESC) List<SortOrder>
LIMIT (Int)
OFFSET (Int)
public static String query(List<String> column, WhereClause whereClause, List<SortOrder> orders, Integer limit, Integer offset){
FilterCriteria
- String Column
- String value
- Operator operator (EQUALS, SEARCH, NOT_EQUALS)
WhereClause
- List<FilterCriteria> 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) {
}
}