Unofficial Dub Package Documentation 
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.PagerUtils;
17 
18 import hunt.collection;
19 
20 import std.exception;
21 import hunt.sql.ast;
22 import hunt.sql.ast.expr;
23 import hunt.sql.ast.statement;
24 // import hunt.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock;
25 import hunt.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
26 import hunt.sql.dialect.mysql.visitor.MySqlASTVisitorAdapter;
27 // import hunt.sql.dialect.odps.ast.OdpsSelectQueryBlock;
28 // import hunt.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock;
29 // import hunt.sql.dialect.oracle.visitor.OracleASTVisitorAdapter;
30 import hunt.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock;
31 // import hunt.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock;
32 // import hunt.sql.dialect.sqlserver.ast.SQLServerTop;
33 import hunt.sql.visitor.SQLASTVisitor;
34 import hunt.sql.util.DBType;
35 import hunt.sql.util.DBType;
36 import hunt.sql.SQLUtils;
37 import hunt.Integer;
38 import hunt.sql.visitor.SQLASTVisitorAdapter;
39 
40 public class PagerUtils {
41 
42     public static string count(string sql, string dbType) {
43         List!(SQLStatement) stmtList = SQLUtils.parseStatements(sql, dbType);
44 
45         if (stmtList.size() != 1) {
46             throw new Exception("sql not support count : " ~ sql);
47         }
48 
49         SQLStatement stmt = stmtList.get(0);
50 
51         if (!(cast(SQLSelectStatement)(stmt) !is null)) {
52             throw new Exception("sql not support count : " ~ sql);
53         }
54 
55         SQLSelectStatement selectStmt = cast(SQLSelectStatement) stmt;
56         return count(selectStmt.getSelect(), dbType);
57     }
58 
59     public static string limit(string sql, string dbType, int offset, int count) {
60         List!(SQLStatement) stmtList = SQLUtils.parseStatements(sql, dbType);
61 
62         if (stmtList.size() != 1) {
63             throw new Exception("sql not support count : " ~ sql);
64         }
65 
66         SQLStatement stmt = stmtList.get(0);
67 
68         if (!(cast(SQLSelectStatement)(stmt) !is null)) {
69             throw new Exception("sql not support count : " ~ sql);
70         }
71 
72         SQLSelectStatement selectStmt = cast(SQLSelectStatement) stmt;
73 
74         return limit(selectStmt.getSelect(), dbType, offset, count);
75     }
76 
77     public static string limit(SQLSelect select, string dbType, int offset, int count) {
78         limit(select, dbType, offset, count, false);
79 
80         return SQLUtils.toSQLString(select, dbType);
81     }
82 
83     public static bool limit(SQLSelect select, string dbType, int offset, int count, bool check) {
84         SQLSelectQuery query = select.getQuery();
85 
86         // if (DBType.ORACLE.opEquals(dbType)) {
87         //     return limitOracle(select, dbType, offset, count, check);
88         // }
89 
90         // if (DBType.DB2.opEquals(dbType)) {
91         //     return limitDB2(select, dbType, offset, count, check);
92         // }
93 
94         // if (DBType.SQL_SERVER.opEquals(dbType) || DBType.JTDS.opEquals(dbType)) {
95         //     return limitSQLServer(select, dbType, offset, count, check);
96         // }
97 
98         return limitQueryBlock(select, dbType, offset, count, check);
99     }
100 
101     private static bool limitQueryBlock(SQLSelect select, string dbType, int offset, int count, bool check) {
102         SQLSelectQuery query = select.getQuery();
103         if (cast(SQLUnionQuery)(query) !is null) {
104             SQLUnionQuery union_p = cast(SQLUnionQuery) query;
105             return limitUnion(union_p, dbType, offset, count, check);
106         }
107 
108         SQLSelectQueryBlock queryBlock = cast(SQLSelectQueryBlock) query;
109         if (DBType.MYSQL.opEquals(dbType) || //
110             DBType.MARIADB.opEquals(dbType) /*|| //
111              DBType.H2.opEquals(dbType) */) {
112             return limitMySqlQueryBlock(queryBlock, dbType, offset, count, check);
113         }
114 
115         if (DBType.POSTGRESQL.opEquals(dbType)) {
116             return limitPostgreSQLQueryBlock(cast(PGSelectQueryBlock) queryBlock, dbType, offset, count, check);
117         }
118         throw new Exception("limitQueryBlock");
119     }
120 
121     private static bool limitPostgreSQLQueryBlock(PGSelectQueryBlock queryBlock, string dbType, int offset, int count, bool check) {
122         SQLLimit limit = queryBlock.getLimit();
123         if (limit !is null) {
124             if (offset > 0) {
125                 limit.setOffset(new SQLIntegerExpr(offset));
126             }
127 
128             if (check && cast(SQLNumericLiteralExpr)limit.getRowCount() !is null) {
129                 int rowCount = (cast(SQLNumericLiteralExpr) limit.getRowCount()).getNumber().intValue();
130                 if (rowCount <= count && offset <= 0) {
131                     return false;
132                 }
133             }
134 
135             limit.setRowCount(new SQLIntegerExpr(count));
136         }
137 
138         limit = new SQLLimit();
139         if (offset > 0) {
140             limit.setOffset(new SQLIntegerExpr(offset));
141         }
142         limit.setRowCount(new SQLIntegerExpr(count));
143         queryBlock.setLimit(limit);
144         return true;
145     }
146 
147     // private static bool limitDB2(SQLSelect select, string dbType, int offset, int count, bool check) {
148     //     SQLSelectQuery query = select.getQuery();
149 
150     //     SQLBinaryOpExpr gt = new SQLBinaryOpExpr(new SQLIdentifierExpr("ROWNUM"), //
151     //                                              SQLBinaryOperator.GreaterThan, //
152     //                                              new SQLNumberExpr(offset), //
153     //                                              DBType.DB2);
154     //     SQLBinaryOpExpr lteq = new SQLBinaryOpExpr(new SQLIdentifierExpr("ROWNUM"), //
155     //                                                SQLBinaryOperator.LessThanOrEqual, //
156     //                                                new SQLNumberExpr(count + offset), //
157     //                                                DBType.DB2);
158     //     SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, DBType.DB2);
159 
160     //     if (cast(SQLSelectQueryBlock)(query) !is null) {
161     //         DB2SelectQueryBlock queryBlock = cast(DB2SelectQueryBlock) query;
162     //         if (offset <= 0) {
163     //             SQLExpr first = queryBlock.getFirst();
164     //             if (check && first !is null && cast(SQLNumericLiteralExpr)(first) !is null) {
165     //                 int rowCount = (cast(SQLNumericLiteralExpr) first).getNumber().intValue();
166     //                 if (rowCount < count) {
167     //                     return false;
168     //                 }
169     //             }
170     //             queryBlock.setFirst(new SQLIntegerExpr(count));
171     //             return true;
172     //         }
173 
174     //         SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
175     //         SQLOrderBy orderBy = select.getOrderBy();
176             
177     //         if (orderBy is null && cast(SQLSelectQueryBlock)select.getQuery() !is null) {
178     //             SQLSelectQueryBlock selectQueryBlcok = cast(SQLSelectQueryBlock) select.getQuery();
179     //             orderBy = selectQueryBlcok.getOrderBy();
180     //             selectQueryBlcok.setOrderBy(null);
181     //         } else {
182     //             select.setOrderBy(null);                
183     //         }
184             
185     //         aggregateExpr.setOver(new SQLOver(orderBy));
186 
187     //         queryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
188 
189     //         DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
190     //         countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
191 
192     //         countQueryBlock.setFrom(new SQLSubqueryTableSource(select.clone(), "XX"));
193 
194     //         countQueryBlock.setWhere(pageCondition);
195 
196     //         select.setQuery(countQueryBlock);
197 
198     //         return true;
199     //     }
200 
201     //     DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
202     //     countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
203     //     SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
204     //     SQLOrderBy orderBy = select.getOrderBy();
205     //     aggregateExpr.setOver(new SQLOver(orderBy));
206     //     select.setOrderBy(null);
207     //     countQueryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
208 
209     //     countQueryBlock.setFrom(new SQLSubqueryTableSource(select.clone(), "XX"));
210 
211     //     if (offset <= 0) {
212     //         select.setQuery(countQueryBlock);
213     //         return true;
214     //     }
215 
216     //     DB2SelectQueryBlock offsetQueryBlock = new DB2SelectQueryBlock();
217     //     offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
218     //     offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
219     //     offsetQueryBlock.setWhere(pageCondition);
220 
221     //     select.setQuery(offsetQueryBlock);
222 
223     //     return true;
224     // }
225 
226     // private static bool limitSQLServer(SQLSelect select, string dbType, int offset, int count, bool check) {
227     //     SQLSelectQuery query = select.getQuery();
228 
229     //     SQLBinaryOpExpr gt = new SQLBinaryOpExpr(new SQLIdentifierExpr("ROWNUM"), //
230     //                                              SQLBinaryOperator.GreaterThan, //
231     //                                              new SQLNumberExpr(offset), //
232     //                                              DBType.SQL_SERVER);
233     //     SQLBinaryOpExpr lteq = new SQLBinaryOpExpr(new SQLIdentifierExpr("ROWNUM"), //
234     //                                                SQLBinaryOperator.LessThanOrEqual, //
235     //                                                new SQLNumberExpr(count + offset), //
236     //                                                DBType.SQL_SERVER);
237     //     SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq,
238     //                                                         DBType.SQL_SERVER);
239 
240     //     if (cast(SQLSelectQueryBlock)(query) !is null) {
241     //         SQLServerSelectQueryBlock queryBlock = cast(SQLServerSelectQueryBlock) query;
242     //         if (offset <= 0) {
243     //             SQLServerTop top = queryBlock.getTop();
244     //             if (check && top !is null && !top.isPercent() && cast(SQLNumericLiteralExpr)top.getExpr() !is null) {
245     //                 int rowCount = (cast(SQLNumericLiteralExpr) top.getExpr()).getNumber().intValue();
246     //                 if (rowCount <= count) {
247     //                     return false;
248     //                 }
249     //             }
250     //             queryBlock.setTop(new SQLServerTop(new SQLNumberExpr(count)));
251     //             return true;
252     //         }
253 
254     //         SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
255     //         SQLOrderBy orderBy = select.getOrderBy();
256     //         aggregateExpr.setOver(new SQLOver(orderBy));
257     //         select.setOrderBy(null);
258 
259     //         queryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
260 
261     //         SQLServerSelectQueryBlock countQueryBlock = new SQLServerSelectQueryBlock();
262     //         countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
263 
264     //         countQueryBlock.setFrom(new SQLSubqueryTableSource(select.clone(), "XX"));
265 
266     //         countQueryBlock.setWhere(pageCondition);
267 
268     //         select.setQuery(countQueryBlock);
269 
270     //         return true;
271     //     }
272 
273     //     SQLServerSelectQueryBlock countQueryBlock = new SQLServerSelectQueryBlock();
274     //     countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
275 
276     //     countQueryBlock.setFrom(new SQLSubqueryTableSource(select.clone(), "XX"));
277 
278     //     if (offset <= 0) {
279     //         countQueryBlock.setTop(new SQLServerTop(new SQLNumberExpr(count)));
280 
281     //         select.setQuery(countQueryBlock);
282     //         return true;
283     //     }
284 
285     //     SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
286     //     SQLOrderBy orderBy = select.getOrderBy();
287     //     aggregateExpr.setOver(new SQLOver(orderBy));
288     //     select.setOrderBy(null);
289     //     countQueryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
290 
291     //     SQLServerSelectQueryBlock offsetQueryBlock = new SQLServerSelectQueryBlock();
292     //     offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
293     //     offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
294     //     offsetQueryBlock.setWhere(pageCondition);
295 
296     //     select.setQuery(offsetQueryBlock);
297 
298     //     return true;
299     // }
300 
301     // private static bool limitOracle(SQLSelect select, string dbType, int offset, int count, bool check) {
302     //     SQLSelectQuery query = select.getQuery();
303 
304     //     if (cast(SQLSelectQueryBlock)(query) !is null) {
305     //         OracleSelectQueryBlock queryBlock = cast(OracleSelectQueryBlock) query;
306     //         SQLOrderBy orderBy = select.getOrderBy();
307     //         if (orderBy is null && queryBlock.getOrderBy() !is null) {
308     //             orderBy = queryBlock.getOrderBy();
309     //         }
310 
311     //         if (queryBlock.getGroupBy() is null
312     //                 && orderBy is null && offset <= 0) {
313 
314     //             SQLExpr where = queryBlock.getWhere();
315     //             if (check && cast(SQLBinaryOpExpr)(where) !is null) {
316     //                 SQLBinaryOpExpr binaryOpWhere = cast(SQLBinaryOpExpr) where;
317     //                 if (binaryOpWhere.getOperator() == SQLBinaryOperator.LessThanOrEqual) {
318     //                     SQLExpr left = binaryOpWhere.getLeft();
319     //                     SQLExpr right = binaryOpWhere.getRight();
320     //                     if (cast(SQLIdentifierExpr)(left) !is null
321     //                             && (cast(SQLIdentifierExpr) left).getName().equalsIgnoreCase("ROWNUM")
322     //                             && cast(SQLNumericLiteralExpr)(right) !is null) {
323     //                         int rowCount = (cast(SQLNumericLiteralExpr) right).getNumber().intValue();
324     //                         if (rowCount <= count) {
325     //                             return false;
326     //                         }
327     //                     }
328     //                 }
329     //             }
330 
331     //             SQLExpr condition = new SQLBinaryOpExpr(new SQLIdentifierExpr("ROWNUM"), //
332     //                                                     SQLBinaryOperator.LessThanOrEqual, //
333     //                                                     new SQLNumberExpr(count), //
334     //                                                     DBType.ORACLE);
335     //             if (queryBlock.getWhere() is null) {
336     //                 queryBlock.setWhere(condition);
337     //             } else {
338     //                 queryBlock.setWhere(new SQLBinaryOpExpr(queryBlock.getWhere(), //
339     //                                                         SQLBinaryOperator.BooleanAnd, //
340     //                                                         condition, //
341     //                                                         DBType.ORACLE));
342     //             }
343 
344     //             return true;
345     //         }
346     //     }
347 
348     //     OracleSelectQueryBlock countQueryBlock = new OracleSelectQueryBlock();
349     //     countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
350     //     countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLIdentifierExpr("ROWNUM"), "RN"));
351 
352     //     countQueryBlock.setFrom(new SQLSubqueryTableSource(select.clone(), "XX"));
353     //     countQueryBlock.setWhere(new SQLBinaryOpExpr(new SQLIdentifierExpr("ROWNUM"), //
354     //                                                  SQLBinaryOperator.LessThanOrEqual, //
355     //                                                  new SQLNumberExpr(count + offset), //
356     //                                                  DBType.ORACLE));
357 
358     //     select.setOrderBy(null);
359     //     if (offset <= 0) {
360     //         select.setQuery(countQueryBlock);
361     //         return true;
362     //     }
363 
364     //     OracleSelectQueryBlock offsetQueryBlock = new OracleSelectQueryBlock();
365     //     offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
366     //     offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
367     //     offsetQueryBlock.setWhere(new SQLBinaryOpExpr(new SQLIdentifierExpr("RN"), //
368     //                                                   SQLBinaryOperator.GreaterThan, //
369     //                                                   new SQLNumberExpr(offset), //
370     //                                                   DBType.ORACLE));
371 
372     //     select.setQuery(offsetQueryBlock);
373     //     return true;
374     // }
375 
376     private static bool limitMySqlQueryBlock(SQLSelectQueryBlock queryBlock, string dbType, int offset, int count, bool check) {
377         SQLLimit limit = queryBlock.getLimit();
378         if (limit !is null) {
379             if (offset > 0) {
380                 limit.setOffset(new SQLIntegerExpr(offset));
381             }
382 
383             if (check && cast(SQLNumericLiteralExpr)limit.getRowCount() !is null) {
384                 int rowCount = (cast(SQLNumericLiteralExpr) limit.getRowCount()).getNumber().intValue();
385                 if (rowCount <= count && offset <= 0) {
386                     return false;
387                 }
388             } else if (check && cast(SQLVariantRefExpr)limit.getRowCount() !is null) {
389                 return false;
390             }
391 
392             limit.setRowCount(new SQLIntegerExpr(count));
393         }
394 
395         if (limit is null) {
396             limit = new SQLLimit();
397             if (offset > 0) {
398                 limit.setOffset(new SQLIntegerExpr(offset));
399             }
400             limit.setRowCount(new SQLIntegerExpr(count));
401             queryBlock.setLimit(limit);
402         }
403 
404         return true;
405     }
406 
407     private static bool limitUnion(SQLUnionQuery queryBlock, string dbType, int offset, int count, bool check) {
408         SQLLimit limit = queryBlock.getLimit();
409         if (limit !is null) {
410             if (offset > 0) {
411                 limit.setOffset(new SQLIntegerExpr(offset));
412             }
413 
414             if (check && cast(SQLNumericLiteralExpr)limit.getRowCount() !is null) {
415                 int rowCount = (cast(SQLNumericLiteralExpr) limit.getRowCount()).getNumber().intValue();
416                 if (rowCount <= count && offset <= 0) {
417                     return false;
418                 }
419             } else if (check && cast(SQLVariantRefExpr)limit.getRowCount() !is null) {
420                 return false;
421             }
422 
423             limit.setRowCount(new SQLIntegerExpr(count));
424         }
425 
426         if (limit is null) {
427             limit = new SQLLimit();
428             if (offset > 0) {
429                 limit.setOffset(new SQLIntegerExpr(offset));
430             }
431             limit.setRowCount(new SQLIntegerExpr(count));
432             queryBlock.setLimit(limit);
433         }
434 
435         return true;
436     }
437 
438     private static string count(SQLSelect select, string dbType) {
439         if (select.getOrderBy() !is null) {
440             select.setOrderBy(null);
441         }
442 
443         SQLSelectQuery query = select.getQuery();
444         clearOrderBy(query);
445 
446         if (cast(SQLSelectQueryBlock)(query) !is null) {
447             SQLSelectItem countItem = createCountItem(dbType);
448 
449             SQLSelectQueryBlock queryBlock = cast(SQLSelectQueryBlock) query;
450             List!(SQLSelectItem) selectList = queryBlock.getSelectList();
451 
452             if (queryBlock.getGroupBy() !is null
453                     && queryBlock.getGroupBy().getItems().size() > 0) {
454                 return createCountUseSubQuery(select, dbType);
455             }
456             
457             int option = queryBlock.getDistionOption();
458             if (option == SQLSetQuantifier.DISTINCT
459                     && selectList.size() >= 1) {
460                 SQLAggregateExpr countExpr = new SQLAggregateExpr("COUNT", SQLAggregateOption.DISTINCT);
461                 for (int i = 0; i < selectList.size(); ++i) {
462                     countExpr.addArgument(selectList.get(i).getExpr());
463                 }
464                 selectList.clear();
465                 queryBlock.setDistionOption(0);
466                 queryBlock.addSelectItem(countExpr);
467             } else {
468                 selectList.clear();
469                 selectList.add(countItem);
470             }
471             return SQLUtils.toSQLString(select, dbType);
472         } else if (cast(SQLUnionQuery)(query) !is null) {
473             return createCountUseSubQuery(select, dbType);
474         }
475 
476         throw new Exception("IllegalState");
477     }
478 
479     private static string createCountUseSubQuery(SQLSelect select, string dbType) {
480         SQLSelectQueryBlock countSelectQuery = createQueryBlock(dbType);
481 
482         SQLSelectItem countItem = createCountItem(dbType);
483         countSelectQuery.getSelectList().add(countItem);
484 
485         SQLSubqueryTableSource fromSubquery = new SQLSubqueryTableSource(select);
486         fromSubquery.setAlias("ALIAS_COUNT");
487         countSelectQuery.setFrom(fromSubquery);
488 
489         SQLSelect countSelect = new SQLSelect(countSelectQuery);
490         SQLSelectStatement countStmt = new SQLSelectStatement(countSelect, dbType);
491 
492         return SQLUtils.toSQLString(countStmt, dbType);
493     }
494 
495     private static SQLSelectQueryBlock createQueryBlock(string dbType) {
496         if (DBType.MYSQL.opEquals(dbType)
497                 || DBType.MARIADB.opEquals(dbType)
498                 /* || DBType.ALIYUN_ADS.opEquals(dbType) */) {
499             return new MySqlSelectQueryBlock();
500         }
501 
502         if (DBType.MARIADB.opEquals(dbType)) {
503             return new MySqlSelectQueryBlock();
504         }
505 
506         // if (DBType.H2.opEquals(dbType)) {
507         //     return new MySqlSelectQueryBlock();
508         // }
509 
510         // if (DBType.ORACLE.opEquals(dbType)) {
511         //     return new OracleSelectQueryBlock();
512         // }
513 
514         if (DBType.POSTGRESQL.opEquals(dbType)) {
515             return new PGSelectQueryBlock();
516         }
517 
518         // if (DBType.SQL_SERVER.opEquals(dbType) || DBType.JTDS.opEquals(dbType)) {
519         //     return new SQLServerSelectQueryBlock();
520         // }
521 
522         // if (DBType.DB2.opEquals(dbType)) {
523         //     return new DB2SelectQueryBlock();
524         // }
525 
526         return new SQLSelectQueryBlock();
527     }
528 
529     private static SQLSelectItem createCountItem(string dbType) {
530         SQLAggregateExpr countExpr = new SQLAggregateExpr("COUNT");
531 
532         countExpr.addArgument(new SQLAllColumnExpr());
533 
534         SQLSelectItem countItem = new SQLSelectItem(countExpr);
535         return countItem;
536     }
537 
538     private static void clearOrderBy(SQLSelectQuery query) {
539         if (cast(SQLSelectQueryBlock)(query) !is null) {
540             SQLSelectQueryBlock queryBlock = cast(SQLSelectQueryBlock) query;
541             if (queryBlock.getOrderBy() !is null) {
542                 queryBlock.setOrderBy(null);
543             }
544             return;
545         }
546 
547         if (cast(SQLUnionQuery)(query) !is null) {
548             SQLUnionQuery union_p = cast(SQLUnionQuery) query;
549             if (union_p.getOrderBy() !is null) {
550                 union_p.setOrderBy(null);
551             }
552             clearOrderBy(union_p.getLeft());
553             clearOrderBy(union_p.getRight());
554         }
555     }
556     
557     /**
558      * 
559      * @param sql
560      * @param dbType
561      * @return if not exists limit, return -1;
562      */
563     public static int getLimit(string sql, string dbType) {
564         List!(SQLStatement) stmtList = SQLUtils.parseStatements(sql, dbType);
565 
566         if (stmtList.size() != 1) {
567             return -1;
568         }
569 
570         SQLStatement stmt = stmtList.get(0);
571 
572         if (cast(SQLSelectStatement)(stmt) !is null) {
573             SQLSelectStatement selectStmt = cast(SQLSelectStatement) stmt;
574             SQLSelectQuery query = selectStmt.getSelect().getQuery();
575             if (cast(SQLSelectQueryBlock)(query) !is null) {
576                 if (cast(MySqlSelectQueryBlock)(query) !is null) {
577                     SQLLimit limit = (cast(MySqlSelectQueryBlock) query).getLimit();
578 
579                     if (limit is null) {
580                         return -1;
581                     }
582 
583                     SQLExpr rowCountExpr = limit.getRowCount();
584 
585                     if (cast(SQLNumericLiteralExpr)(rowCountExpr) !is null) {
586                         int rowCount = (cast(SQLNumericLiteralExpr) rowCountExpr).getNumber().intValue();
587                         return rowCount;
588                     }
589 
590                     return Integer.MAX_VALUE;
591                 }
592 
593                 // if (cast(OdpsSelectQueryBlock)(query) !is null) {
594                 //     SQLLimit limit = (cast(OdpsSelectQueryBlock) query).getLimit();
595                 //     SQLExpr rowCountExpr = limit !is null ? limit.getRowCount() : null;
596 
597                 //     if (cast(SQLNumericLiteralExpr)(rowCountExpr) !is null) {
598                 //         int rowCount = (cast(SQLNumericLiteralExpr) rowCountExpr).getNumber().intValue();
599                 //         return rowCount;
600                 //     }
601 
602                 //     return Integer.MAX_VALUE;
603                 // }
604 
605                 return -1;
606             }
607         }
608         
609         return -1;
610     }
611 
612     public static bool hasUnorderedLimit(string sql, string dbType) {
613         List!(SQLStatement) stmtList = SQLUtils.parseStatements(sql, dbType);
614 
615         if (DBType.MYSQL.opEquals(dbType)) {
616 
617             MySqlUnorderedLimitDetectVisitor visitor = new MySqlUnorderedLimitDetectVisitor();
618 
619             foreach(SQLStatement stmt ; stmtList) {
620                 stmt.accept(visitor);
621             }
622 
623             return visitor.unorderedLimitCount > 0;
624         }
625 
626         // if (DBType.ORACLE.opEquals(dbType)) {
627 
628         //     OracleUnorderedLimitDetectVisitor visitor = new OracleUnorderedLimitDetectVisitor();
629 
630         //     foreach(SQLStatement stmt ; stmtList) {
631         //         stmt.accept(visitor);
632         //     }
633 
634         //     return visitor.unorderedLimitCount > 0;
635         // }
636 
637         throw new Exception("not supported. dbType : " ~ dbType);
638     }
639 
640     private static class MySqlUnorderedLimitDetectVisitor : MySqlASTVisitorAdapter {
641         public int unorderedLimitCount;
642         // alias endVisit = SQLASTVisitorAdapter.endVisit;
643         // alias visit = SQLASTVisitorAdapter.visit;
644 
645         alias endVisit = MySqlASTVisitorAdapter.endVisit;
646         alias visit = MySqlASTVisitorAdapter.visit;
647 
648         override
649         public bool visit(MySqlSelectQueryBlock x) {
650             SQLOrderBy orderBy = x.getOrderBy();
651             SQLLimit limit = x.getLimit();
652 
653             if (limit !is null && (orderBy is null || orderBy.getItems().size() == 0)) {
654                 bool subQueryHasOrderBy = false;
655                 SQLTableSource from = x.getFrom();
656                 if (cast(SQLSubqueryTableSource)(from) !is null) {
657                     SQLSubqueryTableSource subqueryTabSrc = cast(SQLSubqueryTableSource) from;
658                     SQLSelect select = subqueryTabSrc.getSelect();
659                     if (cast(SQLSelectQueryBlock)select.getQuery() !is null) {
660                         SQLSelectQueryBlock subquery = cast(SQLSelectQueryBlock) select.getQuery();
661                         if (subquery.getOrderBy() !is null && subquery.getOrderBy().getItems().size() > 0) {
662                             subQueryHasOrderBy = true;
663                         }
664                     }
665                 }
666 
667                 if (!subQueryHasOrderBy) {
668                     unorderedLimitCount++;
669                 }
670             }
671             return true;
672         }
673     }
674 
675     // private static class OracleUnorderedLimitDetectVisitor : OracleASTVisitorAdapter {
676     //     public int unorderedLimitCount;
677 
678     //     public bool visit(SQLBinaryOpExpr x) {
679     //         SQLExpr left = x.getLeft();
680     //         SQLExpr right = x.getRight();
681 
682     //         bool rownum = false;
683     //         if (cast(SQLIdentifierExpr)(left) !is null
684     //                 && (cast(SQLIdentifierExpr) left).getName().equalsIgnoreCase("ROWNUM")
685     //                 && cast(SQLLiteralExpr)(right) !is null) {
686     //             rownum = true;
687     //         } else if (cast(SQLIdentifierExpr)(right) !is null
688     //                 && (cast(SQLIdentifierExpr) right).getName().equalsIgnoreCase("ROWNUM")
689     //                 && cast(SQLLiteralExpr)(left) !is null) {
690     //             rownum = true;
691     //         }
692 
693     //         OracleSelectQueryBlock selectQuery = null;
694     //         if (rownum) {
695     //             for (SQLObject parent = x.getParent(); parent !is null; parent = parent.getParent()) {
696     //                 if (cast(SQLSelectQuery)(parent) !is null) {
697     //                     if (cast(OracleSelectQueryBlock)(parent) !is null) {
698     //                         OracleSelectQueryBlock queryBlock = cast(OracleSelectQueryBlock) parent;
699     //                         SQLTableSource from = queryBlock.getFrom();
700     //                         if (cast(SQLExprTableSource)(from) !is null) {
701     //                             selectQuery = queryBlock;
702     //                         } else if (cast(SQLSubqueryTableSource)(from) !is null) {
703     //                             SQLSelect subSelect = (cast(SQLSubqueryTableSource) from).getSelect();
704     //                             if (cast(OracleSelectQueryBlock)subSelect.getQuery() !is null) {
705     //                                 selectQuery = cast(OracleSelectQueryBlock) subSelect.getQuery();
706     //                             }
707     //                         }
708     //                     }
709     //                     break;
710     //                 }
711     //             }
712     //         }
713 
714 
715     //         if (selectQuery !is null) {
716     //             SQLOrderBy orderBy = selectQuery.getOrderBy();
717 
718     //             SQLObject parent = selectQuery.getParent();
719     //             if (orderBy is null && cast(SQLSelect)(parent) !is null) {
720     //                 SQLSelect select = cast(SQLSelect) parent;
721     //                 orderBy = select.getOrderBy();
722     //             }
723 
724     //             if (orderBy is null || orderBy.getItems().size() == 0) {
725     //                 unorderedLimitCount++;
726     //             }
727     //         }
728 
729     //         return true;
730     //     }
731 
732     //     override
733     //     public bool visit(OracleSelectQueryBlock queryBlock) {
734     //         bool isExprTableSrc =  cast(SQLExprTableSource)queryBlock.getFrom() !is null;
735 
736     //         if (!isExprTableSrc) {
737     //             return true;
738     //         }
739 
740     //         bool rownum = false;
741     //         foreach(SQLSelectItem item ; queryBlock.getSelectList()) {
742     //             SQLExpr itemExpr = item.getExpr();
743     //             if (cast(SQLIdentifierExpr)(itemExpr) !is null) {
744     //                 if ((cast(SQLIdentifierExpr) itemExpr).getName().equalsIgnoreCase("ROWNUM")) {
745     //                     rownum = true;
746     //                     break;
747     //                 }
748     //             }
749     //         }
750 
751     //         if (!rownum) {
752     //             return true;
753     //         }
754 
755     //         SQLObject parent = queryBlock.getParent();
756     //         if (!(cast(SQLSelect)(parent) !is null)) {
757     //             return true;
758     //         }
759 
760     //         SQLSelect select = cast(SQLSelect) parent;
761 
762     //         if (select.getOrderBy() is null || select.getOrderBy().getItems().size() == 0) {
763     //             unorderedLimitCount++;
764     //         }
765 
766     //         return false;
767     //     }
768     // }
769 }

Page generated by adrdox