hunt.sql.builder.impl.SQLSelectBuilderImpl source code

1 /*
2  * Copyright 2015-2018 HuntLabs.cn.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  *      http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 module hunt.sql.builder.impl.SQLSelectBuilderImpl;
17 
18 import hunt.collection;
19 
20 import hunt.sql.SQLUtils;
21 import hunt.sql.ast.SQLExpr;
22 import hunt.sql.ast.SQLOrderBy;
23 import hunt.sql.ast.SQLStatement;
24 import hunt.sql.ast.expr.SQLBinaryOperator;
25 import hunt.sql.ast.expr.SQLIdentifierExpr;
26 import hunt.sql.ast.expr.SQLIntegerExpr;
27 import hunt.sql.ast.statement.SQLExprTableSource;
28 import hunt.sql.ast.statement.SQLJoinTableSource;
29 import hunt.sql.ast.statement.SQLSelect;
30 import hunt.sql.ast.statement.SQLSelectGroupByClause;
31 import hunt.sql.ast.statement.SQLSelectItem;
32 import hunt.sql.ast.statement.SQLSelectOrderByItem;
33 import hunt.sql.ast.statement.SQLSelectQuery;
34 import hunt.sql.ast.statement.SQLSelectQueryBlock;
35 import hunt.sql.ast.statement.SQLSelectStatement;
36 import hunt.sql.builder.SQLSelectBuilder;
37 import hunt.sql.ast.SQLSetQuantifier;
38 // import hunt.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock;
39 import hunt.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
40 import hunt.sql.ast.SQLLimit;
41 // import hunt.sql.dialect.odps.ast.OdpsSelectQueryBlock;
42 // import hunt.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock;
43 import hunt.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock;
44 // import hunt.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock;
45 // import hunt.sql.dialect.sqlserver.ast.SQLServerTop;
46 import hunt.sql.util.DBType;
47 import hunt.sql.builder.SQLBuilder;
48 
49 class SQLSelectBuilderImpl : SQLSelectBuilder {
50 
51     private SQLSelectStatement stmt;
52     private string             dbType;
53 
54     this(string dbType){
55         this(new SQLSelectStatement(), dbType);
56     }
57     
58     this(string sql, string dbType){
59         List!SQLStatement stmtList = SQLUtils.parseStatements(sql, dbType);
60 
61         if (stmtList.size() == 0) {
62             throw new Exception("not support empty-statement :" ~ sql);
63         }
64 
65         if (stmtList.size() > 1) {
66             throw new Exception("not support multi-statement :" ~ sql);
67         }
68 
69         SQLSelectStatement stmt = cast(SQLSelectStatement) stmtList.get(0);
70         this.stmt = stmt;
71         this.dbType = dbType;
72     }
73 
74     this(SQLSelectStatement stmt, string dbType){
75         this.stmt = stmt;
76         this.dbType = dbType;
77     }
78 
79     SQLSelect getSQLSelect() {
80         if (stmt.getSelect() is null) {
81             stmt.setSelect(createSelect());
82         }
83         return stmt.getSelect();
84     }
85 
86     SQLSelectStatement getSQLSelectStatement() {
87         return stmt;
88     }
89 
90     override
91     SQLBuilder select(string[] columns...) {
92         SQLSelectQueryBlock queryBlock = getQueryBlock();
93 
94         foreach (string column ; columns) {
95             SQLSelectItem selectItem = SQLUtils.toSelectItem(column, dbType);
96             queryBlock.addSelectItem(selectItem);
97         }
98 
99 
100         return this;
101     }
102 
103     override
104     SQLBuilder selectWithAlias(string column, string _alias) {
105         SQLSelectQueryBlock queryBlock = getQueryBlock();
106 
107         SQLExpr columnExpr = SQLUtils.toSQLExpr(column, dbType);
108         SQLSelectItem selectItem = new SQLSelectItem(columnExpr, _alias);
109         queryBlock.addSelectItem(selectItem);
110 
111         return this;
112     }
113 
114     override
115     SQLBuilder from(string table) {
116         return from(table, null);
117     }
118 
119     override
120     SQLBuilder from(string table, string _alias) {
121         SQLSelectQueryBlock queryBlock = getQueryBlock();
122         SQLExprTableSource from = new SQLExprTableSource(new SQLIdentifierExpr(table), _alias);
123         queryBlock.setFrom(from);
124 
125         return this;
126     }
127 
128     override
129     SQLBuilder orderBy(string[] columns...) {
130         // SQLSelect select = this.getSQLSelect();
131         SQLSelectQueryBlock queryBlock = getQueryBlock();
132 
133         SQLOrderBy orderBy = queryBlock.getOrderBy();
134         if (orderBy is null) {
135             orderBy = createOrderBy();
136             queryBlock.setOrderBy(orderBy);
137         }
138 
139         foreach (string column ; columns) {
140             SQLSelectOrderByItem orderByItem = SQLUtils.toOrderByItem(column, dbType);
141             orderBy.addItem(orderByItem);
142         }
143 
144         return this;
145     }
146 
147     override
148     SQLBuilder groupBy(string expr) {
149         SQLSelectQueryBlock queryBlock = getQueryBlock();
150 
151         SQLSelectGroupByClause groupBy = queryBlock.getGroupBy();
152         if (groupBy is null) {
153             groupBy = createGroupBy();
154             queryBlock.setGroupBy(groupBy);
155         }
156 
157         SQLExpr exprObj = SQLUtils.toSQLExpr(expr, dbType);
158         groupBy.addItem(exprObj);
159 
160         return this;
161     }
162 
163     override
164     SQLBuilder having(string expr) {
165         SQLSelectQueryBlock queryBlock = getQueryBlock();
166 
167         SQLSelectGroupByClause groupBy = queryBlock.getGroupBy();
168         if (groupBy is null) {
169             groupBy = createGroupBy();
170             queryBlock.setGroupBy(groupBy);
171         }
172 
173         SQLExpr exprObj = SQLUtils.toSQLExpr(expr, dbType);
174         groupBy.setHaving(exprObj);
175 
176         return this;
177     }
178 
179     override
180     SQLBuilder into(string expr) {
181         SQLSelectQueryBlock queryBlock = getQueryBlock();
182 
183         SQLExpr exprObj = SQLUtils.toSQLExpr(expr, dbType);
184         queryBlock.setInto(exprObj);
185 
186         return this;
187     }
188 
189     override
190     SQLBuilder where(string expr) {
191         SQLSelectQueryBlock queryBlock = getQueryBlock();
192 
193         SQLExpr exprObj = SQLUtils.toSQLExpr(expr, dbType);
194         queryBlock.setWhere(exprObj);
195 
196         return this;
197     }
198 
199     override
200     SQLBuilder whereAnd(string expr) {
201         SQLSelectQueryBlock queryBlock = getQueryBlock();
202         queryBlock.addWhere(SQLUtils.toSQLExpr(expr, dbType));
203 
204         return this;
205     }
206 
207     override
208     SQLBuilder whereOr(string expr) {
209         SQLSelectQueryBlock queryBlock = getQueryBlock();
210 
211         SQLExpr exprObj = SQLUtils.toSQLExpr(expr, dbType);
212         SQLExpr newCondition = SQLUtils.buildCondition(SQLBinaryOperator.BooleanOr, exprObj, false,
213                                                        queryBlock.getWhere());
214         queryBlock.setWhere(newCondition);
215 
216         return this;
217     }
218 
219     override
220     SQLBuilder limit(int rowCount) {
221         auto rowLimit = getQueryBlock().getLimit();
222         if(rowLimit !is null)
223             rowLimit.setRowCount(rowCount);
224         else
225         {
226             getQueryBlock()
227                 .limit(rowCount, 0);
228         }
229         return this;
230     }
231 
232     override
233     SQLBuilder offset(int off) {
234         auto rowLimit = getQueryBlock().getLimit();
235         if(rowLimit !is null)
236             rowLimit.setOffset(off);
237         else
238         {
239             getQueryBlock()
240                 .limit(1, off);
241         }
242         return this;
243     }
244 
245     override
246     SQLBuilder limit(int rowCount, int offset) {
247         getQueryBlock()
248                 .limit(rowCount, offset);
249         return this;
250     }
251 
252     override
253     SQLBuilder join(string table , string _alias = null , string cond = null)
254     {
255         return doJoin(SQLJoinTableSource.JoinType.JOIN,table,_alias,cond);
256     }
257 
258     override
259     SQLBuilder innerJoin(string table , string _alias = null , string cond = null)
260     {
261         return doJoin(SQLJoinTableSource.JoinType.INNER_JOIN,table,_alias,cond);
262     }
263 
264     override
265     SQLBuilder leftJoin(string table , string _alias = null , string cond = null)
266     {
267         return doJoin(SQLJoinTableSource.JoinType.LEFT_OUTER_JOIN,table,_alias,cond);
268     }
269 
270     override
271     SQLBuilder rightJoin(string table , string _alias = null , string cond = null)
272     {
273         return doJoin(SQLJoinTableSource.JoinType.RIGHT_OUTER_JOIN,table,_alias,cond);
274     }
275 
276     private SQLBuilder doJoin(SQLJoinTableSource.JoinType type , string table , string _alias = null , string cond = null)
277     {
278         SQLSelectQueryBlock queryBlock = getQueryBlock();
279         auto  from = queryBlock.getFrom();
280         if(from is null)
281         {
282             throw new Exception("No From Table");
283         }
284         else
285         {
286             auto rightTable = new SQLExprTableSource();
287             rightTable.setExpr(table);
288             rightTable.setAlias(_alias);
289             if(cond is null)
290                 queryBlock.setFrom(new SQLJoinTableSource(from,type,rightTable));
291             else
292                 queryBlock.setFrom(new SQLJoinTableSource(from,type,rightTable,SQLUtils.toSQLExpr(cond)));
293         }
294 
295         return this;
296     }
297 
298 
299     protected SQLSelectQueryBlock getQueryBlock() {
300         SQLSelect select = getSQLSelect();
301         SQLSelectQuery query = select.getQuery();
302         if (query is null) {
303             query = createSelectQueryBlock();
304             select.setQuery(query);
305         }
306 
307         if (!(cast(SQLSelectQueryBlock)(query) !is null)) {
308             throw new Exception("not support from, class : " ~ typeid(query).stringof);
309         }
310 
311         SQLSelectQueryBlock queryBlock = cast(SQLSelectQueryBlock) query;
312         return queryBlock;
313     }
314 
315     protected SQLSelect createSelect() {
316         return new SQLSelect();
317     }
318 
319     protected SQLSelectQuery createSelectQueryBlock() {
320         if (DBType.MYSQL.name == dbType) {
321             return new MySqlSelectQueryBlock();
322         }
323 
324         if (DBType.POSTGRESQL.name == dbType) {
325             return new PGSelectQueryBlock();
326         }
327 
328         // if (DBType.SQL_SERVER.name == dbType) {
329         //     return new SQLServerSelectQueryBlock();
330         // }
331 
332         // if (DBType.ORACLE.name == dbType) {
333         //     return new OracleSelectQueryBlock();
334         // }
335 
336         return new SQLSelectQueryBlock();
337     }
338 
339     protected SQLOrderBy createOrderBy() {
340         return new SQLOrderBy();
341     }
342 
343     protected SQLSelectGroupByClause createGroupBy() {
344         return new SQLSelectGroupByClause();
345     }
346 
347     void setDistinct()
348     {
349         getQueryBlock().setDistionOption(SQLSetQuantifier.DISTINCT);
350         return;
351     }
352 
353     override string toString() {
354         return SQLUtils.toSQLString(stmt, dbType);
355     }
356     
357     string toString(FormatOption option) {
358         return SQLUtils.toSQLString(stmt, dbType, option);
359     }
360 }