hunt.sql.SQLUtils 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.SQLUtils;
17 
18 import hunt.collection;
19 
20 import hunt.sql.ast;
21 import hunt.sql.ast.expr;
22 // import hunt.sql.ast.statement;
23 // import hunt.sql.dialect.db2.visitor.DB2OutputVisitor;
24 // import hunt.sql.dialect.db2.visitor.DB2SchemaStatVisitor;
25 // import hunt.sql.dialect.h2.visitor.H2OutputVisitor;
26 // import hunt.sql.dialect.h2.visitor.H2SchemaStatVisitor;
27 // import hunt.sql.dialect.hive.visitor.HiveOutputVisitor;
28 // import hunt.sql.dialect.hive.visitor.HiveSchemaStatVisitor;
29 import hunt.sql.dialect.mysql.visitor.MySqlOutputVisitor;
30 import hunt.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
31 // import hunt.sql.dialect.odps.visitor.OdpsOutputVisitor;
32 // import hunt.sql.dialect.odps.visitor.OdpsSchemaStatVisitor;
33 // import hunt.sql.dialect.oracle.visitor.OracleOutputVisitor;
34 // import hunt.sql.dialect.oracle.visitor.OracleSchemaStatVisitor;
35 // import hunt.sql.dialect.oracle.visitor.OracleToMySqlOutputVisitor;
36 import hunt.sql.dialect.postgresql.visitor.PGOutputVisitor;
37 import hunt.sql.dialect.postgresql.visitor.PGSchemaStatVisitor;
38 // import hunt.sql.dialect.sqlserver.visitor.SQLServerOutputVisitor;
39 // import hunt.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor;
40 // import hunt.sql.parser;
41 
42 import hunt.sql.parser.CharTypes;
43 import hunt.sql.parser.EOFParserException;
44 // import hunt.sql.parser.InsertColumnsCache;
45 import hunt.sql.parser.Keywords;
46 import hunt.sql.parser.LayoutCharacters;
47 import hunt.sql.parser.Lexer;
48 import hunt.sql.parser.NotAllowCommentException;
49 import hunt.sql.parser.ParserException;
50 import hunt.sql.parser.SQLCreateTableParser;
51 // import hunt.sql.parser.SQLDDLParser;
52 import hunt.sql.parser.SQLExprParser;
53 import hunt.sql.parser.SQLParseException;
54 import hunt.sql.parser.SQLParser;
55 import hunt.sql.parser.SQLParserFeature;
56 import hunt.sql.parser.SQLParserUtils;
57 import hunt.sql.parser.SQLSelectListCache;
58 import hunt.sql.parser.SQLSelectParser;
59 // import hunt.sql.parser.SQLStatementParser;
60 import hunt.sql.parser.SymbolTable;
61 import hunt.sql.parser.Token;
62 
63 import hunt.sql.visitor.SQLASTOutputVisitor;
64 import hunt.sql.visitor.SchemaStatVisitor;
65 import hunt.sql.visitor.VisitorFeature;
66 import hunt.sql.ast.statement.SQLSelectOrderByItem;
67 import hunt.sql.ast.statement.SQLUpdateSetItem;
68 import hunt.sql.ast.statement.SQLSelectItem;
69 import hunt.sql.ast.statement.SQLSelectQueryBlock;
70 import hunt.sql.ast.statement.SQLSetStatement;
71 import hunt.sql.ast.statement.SQLSelectStatement;
72 import hunt.sql.ast.statement.SQLSelectQuery;
73 import hunt.sql.ast.statement.SQLDeleteStatement;
74 import hunt.sql.ast.statement.SQLUpdateStatement;
75 import hunt.sql.ast.statement.SQLCreateTableStatement;
76 import hunt.util.Common;
77 // import entity.support.logging.Log;
78 // import entity.support.logging.LogFactory;
79 import hunt.sql.util;
80 import hunt.logging;
81 import hunt.collection;
82 import hunt.String;
83 import std.array;
84 import hunt.text;
85 import hunt.util.Appendable;
86 
87 import std.concurrency : initOnce;
88 
89 public class SQLUtils {
90     private  enum SQLParserFeature[] FORMAT_DEFAULT_FEATURES = [
91             SQLParserFeature.KeepComments,
92             SQLParserFeature.EnableSQLBinaryOpExprGroup
93     ];
94 
95     static FormatOption DEFAULT_FORMAT_OPTION() {
96         __gshared FormatOption inst;
97         return initOnce!inst(new FormatOption(true, true));
98      }
99 
100     static FormatOption DEFAULT_LCASE_FORMAT_OPTION() {
101         __gshared FormatOption inst;
102         return initOnce!inst(new FormatOption(false, true));
103      }
104 
105 
106     public static string toSQLString(SQLObject sqlObject, string dbType) {
107         return toSQLString(sqlObject, dbType, null);
108     }
109 
110     public static string toSQLString(SQLObject sqlObject, string dbType, FormatOption option) {
111         StringBuilder _out = new StringBuilder();
112         SQLASTOutputVisitor visitor = createOutputVisitor(_out, dbType);
113 
114         if (option is null) {
115             option = DEFAULT_FORMAT_OPTION;
116         }
117         visitor.setUppCase(option.isUppCase());
118         visitor.setPrettyFormat(option.isPrettyFormat());
119         visitor.setParameterized(option.isParameterized());
120         visitor.setFeatures(option.features);
121 
122         sqlObject.accept(visitor);
123 
124         string sql = _out.toString();
125         return sql;
126     }
127 
128     public static string toSQLString(SQLObject sqlObject) {
129         StringBuilder _out = new StringBuilder();
130         sqlObject.accept(new SQLASTOutputVisitor(_out));
131 
132         string sql = _out.toString();
133         return sql;
134     }
135 
136     // public static string toOdpsString(SQLObject sqlObject) {
137     //     return toOdpsString(sqlObject, null);
138     // }
139 
140     // public static string toOdpsString(SQLObject sqlObject, FormatOption option) {
141     //     return toSQLString(sqlObject, DBType.ODPS, option);
142     // }
143 
144     public static string toMySqlString(SQLObject sqlObject) {
145         return toMySqlString(sqlObject, cast(FormatOption) null);
146     }
147 
148     public static string toMySqlString(SQLObject sqlObject, VisitorFeature[] features...) {
149         return toMySqlString(sqlObject, new FormatOption(features));
150     }
151 
152     public static string toMySqlString(SQLObject sqlObject, FormatOption option) {
153         return toSQLString(sqlObject, DBType.MYSQL.name(), option);
154     }
155 
156     public static SQLExpr toMySqlExpr(string sql) {
157         return toSQLExpr(sql, DBType.MYSQL.name());
158     }
159 
160     public static string formatMySql(string sql) {
161         return format(sql, DBType.MYSQL.name());
162     }
163 
164     public static string formatMySql(string sql, FormatOption option) {
165         return format(sql, DBType.MYSQL.name(), option);
166     }
167 
168     // public static string formatOracle(string sql) {
169     //     return format(sql, DBType.ORACLE);
170     // }
171 
172     // public static string formatOracle(string sql, FormatOption option) {
173     //     return format(sql, DBType.ORACLE, option);
174     // }
175 
176     // public static string formatOdps(string sql) {
177     //     return format(sql, DBType.ODPS);
178     // }
179 
180     // public static string formatHive(string sql) {
181     //     return format(sql, DBType.HIVE);
182     // }
183 
184     // public static string formatOdps(string sql, FormatOption option) {
185     //     return format(sql, DBType.ODPS, option);
186     // }
187 
188     // public static string formatHive(string sql, FormatOption option) {
189     //     return format(sql, DBType.HIVE, option);
190     // }
191 
192     // public static string formatSQLServer(string sql) {
193     //     return format(sql, DBType.SQL_SERVER);
194     // }
195 
196     // public static string toOracleString(SQLObject sqlObject) {
197     //     return toOracleString(sqlObject, null);
198     // }
199 
200     // public static string toOracleString(SQLObject sqlObject, FormatOption option) {
201     //     return toSQLString(sqlObject, DBType.ORACLE, option);
202     // }
203 
204     public static string toPGString(SQLObject sqlObject) {
205         return toPGString(sqlObject, null);
206     }
207 
208     public static string toPGString(SQLObject sqlObject, FormatOption option) {
209         return toSQLString(sqlObject, DBType.POSTGRESQL.name(), option);
210     }
211 
212     // public static string toDB2String(SQLObject sqlObject) {
213     //     return toDB2String(sqlObject, null);
214     // }
215 
216     // public static string toDB2String(SQLObject sqlObject, FormatOption option) {
217     //     return toSQLString(sqlObject, DBType.DB2, option);
218     // }
219 
220     // public static string toSQLServerString(SQLObject sqlObject) {
221     //     return toSQLServerString(sqlObject, null);
222     // }
223 
224     // public static string toSQLServerString(SQLObject sqlObject, FormatOption option) {
225     //     return toSQLString(sqlObject, DBType.SQL_SERVER, option);
226     // }
227 
228     public static string formatPGSql(string sql, FormatOption option) {
229         return format(sql, DBType.POSTGRESQL.name(), option);
230     }
231 
232     public static SQLExpr toSQLExpr(string sql, string dbType) {
233         SQLExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
234         SQLExpr expr = parser.expr();
235 
236         if (parser.getLexer().token() != Token.EOF) {
237             throw new ParserException("illegal sql expr : " ~ sql);
238         }
239         // logDebug("toSQLExpr : ",toSQLString(expr,dbType));
240         return expr;
241     }
242 
243     public static SQLSelectOrderByItem toOrderByItem(string sql, string dbType) {
244         SQLExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
245         SQLSelectOrderByItem orderByItem = parser.parseSelectOrderByItem();
246 
247         if (parser.getLexer().token() != Token.EOF) {
248             throw new ParserException("illegal sql expr : " ~ sql);
249         }
250 
251         return orderByItem;
252     }
253 
254     public static SQLUpdateSetItem toUpdateSetItem(string sql, string dbType) {
255         SQLExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
256         SQLUpdateSetItem updateSetItem = parser.parseUpdateSetItem();
257 
258         if (parser.getLexer().token() != Token.EOF) {
259             throw new ParserException("illegal sql expr : " ~ sql);
260         }
261 
262         return updateSetItem;
263     }
264 
265     public static SQLSelectItem toSelectItem(string sql, string dbType) {
266         SQLExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
267         SQLSelectItem selectItem = parser.parseSelectItem();
268 
269         if (parser.getLexer().token() != Token.EOF) {
270             throw new ParserException("illegal sql expr : " ~ sql);
271         }
272 
273         return selectItem;
274     }
275 
276     public static List!(SQLStatement) toStatementList(string sql, string dbType) {
277         auto parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
278         return parser.parseStatementList();
279     }
280 
281     public static SQLExpr toSQLExpr(string sql) {
282         return toSQLExpr(sql, null);
283     }
284 
285     public static string format(string sql, string dbType) {
286         return format(sql, dbType, null, null);
287     }
288 
289     public static string format(string sql, string dbType, FormatOption option) {
290         return format(sql, dbType, null, option);
291     }
292 
293     public static string format(string sql, string dbType, List!(Object) parameters) {
294         return format(sql, dbType, parameters, null);
295     }
296 
297     public static string format(string sql, string dbType, List!(Object) parameters, FormatOption option) {
298         try {
299             auto parser = SQLParserUtils.createSQLStatementParser(sql, dbType, FORMAT_DEFAULT_FEATURES);
300             List!(SQLStatement) statementList = parser.parseStatementList();
301             return toSQLString(statementList, dbType, parameters, option);
302         } catch (Exception ex) {
303             warningf("format error, dbType: %s, message: %s", dbType, ex.msg);
304             version(HUNT_DEBUG) {
305                 warning(ex);
306             }
307             return sql;
308         }
309         // } catch (ParserException ex) {
310         //     logWarning("format error", ex);
311         //     return sql;
312         // }
313     }
314 
315     public static string toSQLString(List!(SQLStatement) statementList, string dbType) {
316         return toSQLString(statementList, dbType, cast(List!(Object)) null);
317     }
318 
319     public static string toSQLString(List!(SQLStatement) statementList, string dbType, FormatOption option) {
320         return toSQLString(statementList, dbType, null, option);
321     }
322 
323     public static string toSQLString(List!(SQLStatement) statementList, DBType dbType, FormatOption option) {
324         return toSQLString(statementList, dbType.name, null, option);
325     }
326 
327     public static string toSQLString(List!(SQLStatement) statementList, string dbType, List!(Object) parameters) {
328         return toSQLString(statementList, dbType, parameters, null, null);
329     }
330 
331     public static string toSQLString(List!(SQLStatement) statementList, string dbType, List!(Object) parameters, FormatOption option) {
332         return toSQLString(statementList, dbType, parameters, option, null);
333     }
334 
335     public static string toSQLString(List!(SQLStatement) statementList
336             , string dbType
337             , List!(Object) parameters
338             , FormatOption option
339             , Map!(string , string) tableMapping) {
340         StringBuilder _out = new StringBuilder();
341         SQLASTOutputVisitor visitor = createFormatOutputVisitor(_out, statementList, dbType);
342         if (parameters !is null) {
343             visitor.setInputParameters(parameters);
344         }
345 
346         if (option is null) {
347             option = DEFAULT_FORMAT_OPTION;
348         }
349         visitor.setFeatures(option.features);
350 
351         if (tableMapping !is null) {
352             visitor.setTableMapping(tableMapping);
353         }
354 
355         bool printStmtSeperator;
356         if (DBType.SQL_SERVER.opEquals(dbType)) {
357             printStmtSeperator = false;
358         } else {
359             printStmtSeperator = !DBType.ORACLE.opEquals(dbType);
360         }
361 
362         for (int i = 0, size = statementList.size(); i < size; i++) {
363             SQLStatement stmt = statementList.get(i);
364 
365             if (i > 0) {
366                 SQLStatement preStmt = statementList.get(i - 1);
367                 if (printStmtSeperator && !preStmt.isAfterSemi()) {
368                     visitor.print(";");
369                 }
370 
371                 List!(string) comments = preStmt.getAfterCommentsDirect();
372                 if (comments !is null){
373                     for (int j = 0; j < comments.size(); ++j) {
374                         string comment = comments.get(j);
375                         if (j != 0) {
376                             visitor.println();
377                         }
378                         visitor.printComment(comment);
379                     }
380                 }
381 
382                 if (printStmtSeperator) {
383                     visitor.println();
384                 }
385 
386                 if (!(cast(SQLSetStatement)(stmt) !is null)) {
387                     visitor.println();
388                 }
389             }
390             {
391                 List!(string) comments = stmt.getBeforeCommentsDirect();
392                 if (comments !is null){
393                     foreach(string comment ; comments) {
394                         visitor.printComment(comment);
395                         visitor.println();
396                     }
397                 }
398             }
399             stmt.accept(visitor);
400 
401             if (i == size - 1) {
402                 List!(string) comments = stmt.getAfterCommentsDirect();
403                 if (comments !is null){
404                     for (int j = 0; j < comments.size(); ++j) {
405                         string comment = comments.get(j);
406                         if (j != 0) {
407                             visitor.println();
408                         }
409                         visitor.printComment(comment);
410                     }
411                 }
412             }
413         }
414 
415         return _out.toString();
416     }
417 
418     public static SQLASTOutputVisitor createOutputVisitor(Appendable _out, string dbType) {
419         return createFormatOutputVisitor(_out, null, dbType);
420     }
421 
422     public static SQLASTOutputVisitor createFormatOutputVisitor(Appendable _out, //
423                                                                 List!(SQLStatement) statementList, //
424                                                                 string dbType) {
425         // if (DBType.ORACLE.opEquals(dbType) || DBType.ALI_ORACLE.opEquals(dbType)) {
426         //     if (statementList is null || statementList.size() == 1) {
427         //         return new OracleOutputVisitor(_out, false);
428         //     } else {
429         //         return new OracleOutputVisitor(_out, true);
430         //     }
431         // }
432 
433         if (DBType.MYSQL.opEquals(dbType) //
434                 || DBType.MARIADB.opEquals(dbType)) {
435             return new MySqlOutputVisitor(_out);
436         }
437 
438         if (DBType.POSTGRESQL.opEquals(dbType)) {
439             return new PGOutputVisitor(_out);
440         }
441 
442         // if (DBType.SQL_SERVER.opEquals(dbType) || DBType.JTDS.opEquals(dbType)) {
443         //     return new SQLServerOutputVisitor(_out);
444         // }
445 
446         // if (DBType.DB2.opEquals(dbType)) {
447         //     return new DB2OutputVisitor(_out);
448         // }
449 
450         // if (DBType.ODPS.opEquals(dbType)) {
451         //     return new OdpsOutputVisitor(_out);
452         // }
453 
454         // if (DBType.H2.opEquals(dbType)) {
455         //     return new H2OutputVisitor(_out);
456         // }
457 
458         // if (DBType.HIVE.opEquals(dbType)) {
459         //     return new HiveOutputVisitor(_out);
460         // }
461 
462         if (DBType.ELASTIC_SEARCH.opEquals(dbType)) {
463             return new MySqlOutputVisitor(_out);
464         }
465 
466         return new SQLASTOutputVisitor(_out, dbType);
467     }
468 
469     //@Deprecated
470     public static SchemaStatVisitor createSchemaStatVisitor(List!(SQLStatement) statementList, string dbType) {
471         return createSchemaStatVisitor(dbType);
472     }
473 
474     public static SchemaStatVisitor createSchemaStatVisitor(string dbType) {
475         // if (DBType.ORACLE.opEquals(dbType) || DBType.ALI_ORACLE.opEquals(dbType)) {
476         //     return new OracleSchemaStatVisitor();
477         // }
478 
479         if (DBType.MYSQL.opEquals(dbType) || //
480                 DBType.MARIADB.opEquals(dbType)) {
481             return new MySqlSchemaStatVisitor();
482         }
483 
484         if (DBType.POSTGRESQL.opEquals(dbType)) {
485             return new PGSchemaStatVisitor();
486         }
487 
488         // if (DBType.SQL_SERVER.opEquals(dbType) || DBType.JTDS.opEquals(dbType)) {
489         //     return new SQLServerSchemaStatVisitor();
490         // }
491 
492         // if (DBType.DB2.opEquals(dbType)) {
493         //     return new DB2SchemaStatVisitor();
494         // }
495 
496         // if (DBType.ODPS.opEquals(dbType)) {
497         //     return new OdpsSchemaStatVisitor();
498         // }
499 
500         // if (DBType.H2.opEquals(dbType)) {
501         //     return new H2SchemaStatVisitor();
502         // }
503 
504         // if (DBType.HIVE.opEquals(dbType)) {
505         //     return new HiveSchemaStatVisitor();
506         // }
507 
508         if (DBType.ELASTIC_SEARCH.opEquals(dbType)) {
509             return new MySqlSchemaStatVisitor();
510         }
511 
512         return new SchemaStatVisitor();
513     }
514 
515     public static List!(SQLStatement) parseStatements(string sql, string dbType) {
516         auto parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
517         List!(SQLStatement) stmtList = parser.parseStatementList();
518         if (parser.getLexer().token() != Token.EOF) {
519             throw new ParserException("syntax error : " ~ sql);
520         }
521         return stmtList;
522     }
523 
524     public static List!(SQLStatement) parseStatements(string sql, string dbType, bool keepComments) {
525         auto parser = SQLParserUtils.createSQLStatementParser(sql, dbType, keepComments);
526         List!(SQLStatement) stmtList = parser.parseStatementList();
527         if (parser.getLexer().token() != Token.EOF) {
528             throw new ParserException("syntax error. " ~ sql);
529         }
530         return stmtList;
531     }
532 
533     /**
534      * @param columnName
535      * @param tableAlias
536      * @param pattern if pattern is null,it will be set {%Y-%m-%d %H:%i:%s} as mysql default value and set {yyyy-mm-dd
537      * hh24:mi:ss} as oracle default value
538      * @param dbType {@link DBType} if dbType is null ,it will be set the mysql as a default value
539      */
540     public static string buildToDate(string columnName, string tableAlias, string pattern, string dbType) {
541         StringBuilder sql = new StringBuilder();
542         if (columnName.length == 0) return "";
543         if (dbType.length == 0) dbType = DBType.MYSQL.name();
544         string formatMethod = "";
545         if (equalsIgnoreCase(DBType.MYSQL.name(), dbType)) {
546             formatMethod = "STR_TO_DATE";
547             if (pattern.length == 0) pattern = "%Y-%m-%d %H:%i:%s";
548         } else if (equalsIgnoreCase(DBType.ORACLE.name(), dbType)) {
549             formatMethod = "TO_DATE";
550             if (pattern.length == 0) pattern = "yyyy-mm-dd hh24:mi:ss";
551         } else {
552             return "";
553             // expand date's handle method for other database
554         }
555         sql.append(formatMethod).append("(");
556         if (!(tableAlias.length == 0)) sql.append(tableAlias).append(".");
557         sql.append(columnName).append(",");
558         sql.append("'");
559         sql.append(pattern);
560         sql.append("')");
561         return sql.toString();
562     }
563 
564     public static List!(SQLExpr) split(SQLBinaryOpExpr x) {
565         return SQLBinaryOpExpr.split(x);
566     }
567 
568     // public static string translateOracleToMySql(string sql) {
569     //     List!(SQLStatement) stmtList = toStatementList(sql, DBType.ORACLE);
570 
571     //     StringBuilder _out = new StringBuilder();
572     //     OracleToMySqlOutputVisitor visitor = new OracleToMySqlOutputVisitor(_out, false);
573     //     for (int i = 0; i < stmtList.size(); ++i) {
574     //         stmtList.get(i).accept(visitor);
575     //     }
576 
577     //     string mysqlSql = _out.toString();
578     //     return mysqlSql;
579 
580     // }
581 
582     public static string addCondition(string sql, string condition, string dbType) {
583         string result = addCondition(sql, condition, SQLBinaryOperator.BooleanAnd, false, dbType);
584         return result;
585     }
586 
587     public static string addCondition(string sql, string condition, SQLBinaryOperator op, bool left, string dbType) {
588         if (sql is null) {
589             throw new Exception("IllegalArgument : sql is null");
590         }
591 
592         if (condition is null) {
593             return sql;
594         }
595 
596         if (op.getName == string.init) {
597             op = SQLBinaryOperator.BooleanAnd;
598         }
599 
600         if (op != SQLBinaryOperator.BooleanAnd //
601                 && op != SQLBinaryOperator.BooleanOr) {
602             throw new Exception("add condition not support : " ~ op.getName());
603         }
604 
605         List!(SQLStatement) stmtList = parseStatements(sql, dbType);
606 
607         if (stmtList.size() == 0) {
608             throw new Exception("not support empty-statement :" ~ sql);
609         }
610 
611         if (stmtList.size() > 1) {
612             throw new Exception("not support multi-statement :" ~ sql);
613         }
614 
615         SQLStatement stmt = stmtList.get(0);
616 
617         SQLExpr conditionExpr = toSQLExpr(condition, dbType);
618 
619         addCondition(stmt, op, conditionExpr, left);
620 
621         return toSQLString(stmt, dbType);
622     }
623 
624     public static void addCondition(SQLStatement stmt, SQLBinaryOperator op, SQLExpr condition, bool left) {
625         if (cast(SQLSelectStatement)(stmt) !is null) {
626             SQLSelectQuery query = (cast(SQLSelectStatement) stmt).getSelect().getQuery();
627             if (cast(SQLSelectQueryBlock)(query) !is null) {
628                 SQLSelectQueryBlock queryBlock = cast(SQLSelectQueryBlock) query;
629                 SQLExpr newCondition = buildCondition(op, condition, left, queryBlock.getWhere());
630                 queryBlock.setWhere(newCondition);
631             } else {
632                 throw new Exception("add condition not support " ~ typeid(stmt).stringof);
633             }
634 
635             return;
636         }
637 
638         if (cast(SQLDeleteStatement)(stmt) !is null) {
639             SQLDeleteStatement _delete = cast(SQLDeleteStatement) stmt;
640 
641             SQLExpr newCondition = buildCondition(op, condition, left, _delete.getWhere());
642             _delete.setWhere(newCondition);
643 
644             return;
645         }
646 
647         if (cast(SQLUpdateStatement)(stmt) !is null) {
648             SQLUpdateStatement update = cast(SQLUpdateStatement) stmt;
649 
650             SQLExpr newCondition = buildCondition(op, condition, left, update.getWhere());
651             update.setWhere(newCondition);
652 
653             return;
654         }
655 
656         throw new Exception("add condition not support " ~ typeid(stmt).stringof);
657     }
658 
659     public static SQLExpr buildCondition(SQLBinaryOperator op, SQLExpr condition, bool left, SQLExpr where) {
660         if (where is null) {
661             return condition;
662         }
663 
664         SQLBinaryOpExpr newCondition;
665         if (left) {
666             newCondition = new SQLBinaryOpExpr(condition, op, where);
667         } else {
668             newCondition = new SQLBinaryOpExpr(where, op, condition);
669         }
670         return newCondition;
671     }
672 
673     public static string addSelectItem(string selectSql, string expr, string _alias, string dbType) {
674         return addSelectItem(selectSql, expr, _alias, false, dbType);
675     }
676 
677     public static string addSelectItem(string selectSql, string expr, string _alias, bool first, string dbType) {
678         List!(SQLStatement) stmtList = parseStatements(selectSql, dbType);
679 
680         if (stmtList.size() == 0) {
681             throw new Exception("not support empty-statement :" ~ selectSql);
682         }
683 
684         if (stmtList.size() > 1) {
685             throw new Exception("not support multi-statement :" ~ selectSql);
686         }
687 
688         SQLStatement stmt = stmtList.get(0);
689 
690         SQLExpr columnExpr = toSQLExpr(expr, dbType);
691 
692         addSelectItem(stmt, columnExpr, _alias, first);
693 
694         return toSQLString(stmt, dbType);
695     }
696 
697     public static void addSelectItem(SQLStatement stmt, SQLExpr expr, string _alias, bool first) {
698         if (expr is null) {
699             return;
700         }
701 
702         if (cast(SQLSelectStatement)(stmt) !is null) {
703             SQLSelectQuery query = (cast(SQLSelectStatement) stmt).getSelect().getQuery();
704             if (cast(SQLSelectQueryBlock)(query) !is null) {
705                 SQLSelectQueryBlock queryBlock = cast(SQLSelectQueryBlock) query;
706                 addSelectItem(queryBlock, expr, _alias, first);
707             } else {
708                 throw new Exception("add condition not support " ~ typeid(stmt).stringof);
709             }
710 
711             return;
712         }
713 
714         throw new Exception("add selectItem not support " ~ typeid(stmt).stringof);
715     }
716 
717     public static void addSelectItem(SQLSelectQueryBlock queryBlock, SQLExpr expr, string _alias, bool first) {
718         SQLSelectItem selectItem = new SQLSelectItem(expr, _alias);
719         queryBlock.getSelectList().add(selectItem);
720         selectItem.setParent(selectItem);
721     }
722 
723 
724     public static string refactor(string sql, string dbType, Map!(string , string) tableMapping) {
725         List!(SQLStatement) stmtList = parseStatements(sql, dbType);
726         return SQLUtils.toSQLString(stmtList, dbType, null, null, tableMapping);
727     }
728 
729     public static long hash(string sql, string dbType) {
730         Lexer lexer = SQLParserUtils.createLexer(sql, dbType);
731 
732         StringBuilder buf = new StringBuilder(sql.length);
733 
734         for (;;) {
735             lexer.nextToken();
736 
737             Token token = lexer.token();
738             if (token == Token.EOF) {
739                 break;
740             }
741 
742             if (token == Token.ERROR) {
743                 return FnvHash.fnv1a_64(sql);
744             }
745 
746             if (buf.length != 0) {
747 
748             }
749         }
750 
751         return (cast(Object)buf).toHash();
752     }
753 
754     public static SQLExpr not(SQLExpr expr) {
755         if (cast(SQLBinaryOpExpr)(expr) !is null) {
756             SQLBinaryOpExpr binaryOpExpr = cast(SQLBinaryOpExpr) expr;
757             SQLBinaryOperator op = binaryOpExpr.getOperator();
758 
759             SQLBinaryOperator notOp;
760 
761             switch (op.getName){
762                 case SQLBinaryOperator.Equality.getName:
763                     notOp = SQLBinaryOperator.LessThanOrGreater;
764                     break;
765                 case SQLBinaryOperator.LessThanOrEqualOrGreaterThan.getName:
766                     notOp = SQLBinaryOperator.Equality;
767                     break;
768                 case SQLBinaryOperator.LessThan.getName:
769                     notOp = SQLBinaryOperator.GreaterThanOrEqual;
770                     break;
771                 case SQLBinaryOperator.LessThanOrEqual.getName:
772                     notOp = SQLBinaryOperator.GreaterThan;
773                     break;
774                 case SQLBinaryOperator.GreaterThan.getName:
775                     notOp = SQLBinaryOperator.LessThanOrEqual;
776                     break;
777                 case SQLBinaryOperator.GreaterThanOrEqual.getName:
778                     notOp = SQLBinaryOperator.LessThan;
779                     break;
780                 case SQLBinaryOperator.Is.getName:
781                     notOp = SQLBinaryOperator.IsNot;
782                     break;
783                 case SQLBinaryOperator.IsNot.getName:
784                     notOp = SQLBinaryOperator.Is;
785                     break;
786                 default:
787                     break;
788             }
789 
790 
791             if (notOp.getName != string.init) {
792                 return new SQLBinaryOpExpr(binaryOpExpr.getLeft(), notOp, binaryOpExpr.getRight());
793             }
794         }
795 
796         if (cast(SQLInListExpr)(expr) !is null) {
797             SQLInListExpr inListExpr = cast(SQLInListExpr) expr;
798 
799             SQLInListExpr newInListExpr = new SQLInListExpr(inListExpr);
800             newInListExpr.getTargetList().addAll(inListExpr.getTargetList());
801             newInListExpr.setNot(!inListExpr.isNot());
802             return newInListExpr;
803         }
804 
805         return new SQLUnaryExpr(SQLUnaryOperator.Not, expr);
806     }
807 
808     public static string normalize(string name) {
809         return normalize(name, null);
810     }
811 
812     public static string normalize(string name, string dbType) {
813         if (name is null) {
814             return null;
815         }
816 
817         if (name.length > 2) {
818             char c0 = charAt(name, 0);
819             char x0 = charAt(name, name.length - 1);
820             if ((c0 == '"' && x0 == '"') || (c0 == '`' && x0 == '`')) {
821                 string normalizeName = name.substring(1, cast(int)name.length - 1);
822                 if (c0 == '`') {
823                     normalizeName = normalizeName.replace("`\\.`", ".");
824                 }
825 
826                 if (DBType.ORACLE.opEquals(dbType)) {
827                     // if (OracleUtils.isKeyword(normalizeName)) {
828                     //     return name;
829                     // }
830                 } else if (DBType.MYSQL.opEquals(dbType)) {
831                     if (MySqlUtils.isKeyword(normalizeName)) {
832                         return name;
833                     }
834                 } else if (DBType.POSTGRESQL.opEquals(dbType)
835                         /* || DBType.ENTERPRISEDB.opEquals(dbType) */) {
836                     if (PGUtils.isKeyword(normalizeName)) {
837                         return name;
838                     }
839                 }
840 
841                 return normalizeName;
842             }
843         }
844 
845         return name;
846     }
847 
848     public static bool nameEquals(SQLName a, SQLName b) {
849         if (a == b) {
850             return true;
851         }
852 
853         if (a is null || b is null) {
854             return false;
855         }
856 
857         return a.nameHashCode64() == b.nameHashCode64();
858     }
859 
860     public static bool nameEquals(string a, string b) {
861         if (a == b) {
862             return true;
863         }
864 
865         if (a is null || b is null) {
866             return false;
867         }
868 
869         if (equalsIgnoreCase(a, b)) {
870             return true;
871         }
872 
873         string normalize_a = normalize(a);
874         string normalize_b = normalize(b);
875 
876         return equalsIgnoreCase(normalize_a, normalize_b);
877     }
878 
879     public static bool isValue(SQLExpr expr) {
880         if (cast(SQLLiteralExpr)(expr) !is null) {
881             return true;
882         }
883 
884         if (cast(SQLVariantRefExpr)(expr) !is null) {
885             return true;
886         }
887 
888         if (cast(SQLBinaryOpExpr)(expr) !is null) {
889             SQLBinaryOpExpr binaryOpExpr = cast(SQLBinaryOpExpr) expr;
890             SQLBinaryOperator op = binaryOpExpr.getOperator();
891             if (op == SQLBinaryOperator.Add
892                     || op == SQLBinaryOperator.Subtract
893                     || op == SQLBinaryOperator.Multiply) {
894                 return isValue(binaryOpExpr.getLeft())
895                         && isValue(binaryOpExpr.getRight());
896             }
897         }
898 
899         return false;
900     }
901 
902     public static bool replaceInParent(SQLExpr expr, SQLExpr target) {
903         if (expr is null) {
904             return false;
905         }
906 
907         SQLObject parent = expr.getParent();
908 
909         if (cast(SQLReplaceable)(parent) !is null) {
910             return (cast(SQLReplaceable) parent).replace(expr, target);
911         }
912 
913         return false;
914     }
915 
916     public static string desensitizeTable(string tableName) {
917         if (tableName is null) {
918             return null;
919         }
920 
921         tableName = normalize(tableName);
922         long hash = FnvHash.hashCode64(tableName);
923         return Utils.hex_t(hash);
924     }
925 
926     /**
927      * 
928      * @param sql
929      * @param dbType
930      * @return
931      */
932     public static string sort(string sql, string dbType) {
933         List!(SQLStatement) stmtList = SQLUtils.parseStatements(sql, DBType.ORACLE.name());
934         SQLCreateTableStatement.sort(stmtList);
935         return SQLUtils.toSQLString(stmtList, dbType);
936     }
937 }
938 
939 
940 class FormatOption {
941     private int features = VisitorFeature.of(VisitorFeature.OutputUCase, VisitorFeature.OutputPrettyFormat);
942 
943     this() {
944 
945     }
946 
947     this(VisitorFeature[] features...) {
948         this.features = VisitorFeature.of(features);
949     }
950 
951     this(bool ucase) {
952         this(ucase, true);
953     }
954 
955     this(bool ucase, bool prettyFormat) {
956         this(ucase, prettyFormat, false);
957     }
958 
959     this(bool ucase, bool prettyFormat, bool parameterized) {
960         this.features = VisitorFeature.config(this.features, VisitorFeature.OutputUCase, ucase);
961         this.features = VisitorFeature.config(this.features, VisitorFeature.OutputPrettyFormat, prettyFormat);
962         this.features = VisitorFeature.config(this.features, VisitorFeature.OutputParameterized, parameterized);
963     }
964 
965     bool isDesensitize() {
966         return isEnabled(VisitorFeature.OutputDesensitize);
967     }
968 
969     void setDesensitize(bool val) {
970         config(VisitorFeature.OutputDesensitize, val);
971     }
972 
973     bool isUppCase() {
974         return isEnabled(VisitorFeature.OutputUCase);
975     }
976 
977     void setUppCase(bool val) {
978         config(VisitorFeature.OutputUCase, val);
979     }
980 
981     bool isPrettyFormat() {
982         return isEnabled(VisitorFeature.OutputPrettyFormat);
983     }
984 
985     void setPrettyFormat(bool prettyFormat) {
986         config(VisitorFeature.OutputPrettyFormat, prettyFormat);
987     }
988 
989     bool isParameterized() {
990         return isEnabled(VisitorFeature.OutputParameterized);
991     }
992 
993     void setParameterized(bool parameterized) {
994         config(VisitorFeature.OutputParameterized, parameterized);
995     }
996 
997     void config(VisitorFeature feature, bool state) {
998         features = VisitorFeature.config(features, feature, state);
999     }
1000 
1001     bool isEnabled(VisitorFeature feature) {
1002         return VisitorFeature.isEnabled(this.features, feature);
1003     }
1004 }