001package com.randomnoun.common.jexl.sql; 002 003/* (c) 2013 randomnoun. All Rights Reserved. This work is licensed under a 004 * BSD Simplified License. (http://www.randomnoun.com/bsd-simplified.html) 005 */ 006import java.text.SimpleDateFormat; 007import java.util.ArrayList; 008import java.util.Date; 009import java.util.Enumeration; 010import java.util.List; 011 012import org.apache.log4j.Logger; 013 014import com.randomnoun.common.Text; 015import com.randomnoun.common.jexl.DateSpan; 016import com.randomnoun.common.jexl.EvalFallbackException; 017import com.randomnoun.common.jexl.ast.AdditiveExpression; 018import com.randomnoun.common.jexl.ast.ArgumentList; 019import com.randomnoun.common.jexl.ast.Arguments; 020import com.randomnoun.common.jexl.ast.BooleanLiteral; 021import com.randomnoun.common.jexl.ast.ConditionalAndExpression; 022import com.randomnoun.common.jexl.ast.EqualityExpression; 023import com.randomnoun.common.jexl.ast.Expression; 024import com.randomnoun.common.jexl.ast.FunctionCall; 025import com.randomnoun.common.jexl.ast.Literal; 026import com.randomnoun.common.jexl.ast.MultiplicativeExpression; 027import com.randomnoun.common.jexl.ast.Name; 028import com.randomnoun.common.jexl.ast.NodeChoice; 029import com.randomnoun.common.jexl.ast.NodeSequence; 030import com.randomnoun.common.jexl.ast.NodeToken; 031import com.randomnoun.common.jexl.ast.NullLiteral; 032import com.randomnoun.common.jexl.ast.PrimaryExpression; 033import com.randomnoun.common.jexl.ast.RelationalExpression; 034import com.randomnoun.common.jexl.ast.TopLevelExpression; 035import com.randomnoun.common.jexl.ast.UnaryExpression; 036import com.randomnoun.common.jexl.eval.EvalContext; 037import com.randomnoun.common.jexl.eval.EvalException; 038import com.randomnoun.common.jexl.eval.EvalFunction; 039import com.randomnoun.common.jexl.eval.Evaluator; 040 041 042/** 043 * This class traverses a TopLevelExpression object and translates the AST graph 044 * into a text representation that forms the 'WHERE' clause of an SQL query. 045 * 046 * <p>Only a subset of expressions are supported by the SQL Generator; an 047 * EvalException is thrown if an invalid expression is supplied. 048 * 049 * <p>The context passed into this class contains: 050 * <ul> 051 * <li>internal parameters that effect how the SQL is generated (such as the database vendor) 052 * <li>evaluatable functions (run at translation time) 053 * <li>translatable functions (converted into SQL at translation time) 054 * <li>database columns used in the SQL 055 * <li>variables (which are substituted at <i>translation</i> time into the value 056 * of that variable) 057 * <li>positional parameters (which are substituted at <i>execution</i> time into 058 * JDBC positional parameters) 059 * </ul> 060 * 061 * <h3>Variables</h3> 062 * 063 * It's probably worth going into the difference between the two different types of variables. 064 * If I have a variable that I know the value of at translation time; e.g. the variable 065 * <code>messageVar</code> is <code>MT500</code>, I can generate standalone SQL that uses this variable: 066 * 067 * <pre style="code"> 068 * // [1] parse the expression into an AST 069 * String exprString = "externalMessageType == messageVar"; 070 * ExpressionParser parser = new ExpressionParser(new StringReader(exprString)); 071 * TopLevelExpression expr = parser.TopLevelExpression(); 072 * 073 * // [2] set up an evaluation context to define variables 074 * EvalContext context = new EvalContext(); 075 * 076 * // [3] set up database columns used in the query 077 * // note the first 'externalMessageType' in this line refers to the name 078 * // of the column in exprString, and the second 'externalMessageType' refers 079 * // to the name of the column in the table we're querying. It's clearer 080 * // if these are set to the same value, but there's no other reason they have to. 081 * context.setVariable("externalMessageType", new SqlColumn("externalMessageType")); 082 * 083 * // [4] set up variables used in the query 084 * context.setVariable("messageVar", "MT500"); 085 * 086 * // [5] generate the SQL 087 * SqlGenerator generator = new SqlGenerator(); 088 * String result = (String) generator.visit(expr, context); 089 * </pre> 090 * 091 * <p>The result here being <code>externalMessageType = 'MT500'</code>. 092 * 093 * <p>If, however, I won't know <code>messageVar</code> until execution time, I can still 094 * generate the SQL using a PositionalParameter object: 095 * 096 * <pre style="code"> 097 * // parse the expression and setup context as per [1], [2] and [3] above 098 * 099 * // [4] set up an evaluation context to define variables 100 * context.setVariable("messageVar", new PositionalParameter("messageVar")); 101 * 102 * // [5] generate the SQL as above 103 * SqlGenerator generator = new SqlGenerator(); 104 * String result = (String) generator.visit(expr, context); 105 * </pre> 106 * 107 * The result here being <code>externalMessageType = ?</code> instead. The value of the 108 * '<code>?</code>' in the result is supplied 109 * by the program at execution time, using standard JDBC positional parameters. But, you're 110 * probably thinking, how do I know <i>which</i> positional parameter to use? After all, 111 * I could have had more than one PositionalParameter's in the context, and they may 112 * appear in any order or any number of times in the result String. To determine how to 113 * supply these values to JDBC, the context variable VAR_PARAMETERS is set to a List of 114 * Strings, each being the name of the positional parameter above. In the example above, 115 * if we then executed 116 * 117 * <pre style="code"> 118 * List parameList = (List) context.getVariable(SqlGenerator.VAR_PARAMETERS); 119 * </pre> 120 * 121 * this will set paramList to a one-element list, containing the String "messageVar" 122 * (corresponding to the single '<code>?</code>' in the result String). We can 123 * iterate over this list to create the actual parameters to pass into JDBC when running 124 * the query. 125 * 126 * <h3>Static dates</h3> 127 * If a date is inserted into an SQL query, we need to know which database we're running 128 * on, since each vendor seems to have their own way of representing fixed dates. This is 129 * set using the VAR_DATABASE_TYPE variable in the ExpressionContext, which is set to 130 * either DATABASE_ORACLE, DATABASE_DB2, DATABASE_SQLSERVER or DATABASE_MYSQL. 131 * 132 * <h3>Evaluatable functions</h3> 133 * Any functions which implement EvalFunction are evaluated at translation time, (i.e. 134 * are not translated into SQL). This is useful for things like nextWorkingDay(), which 135 * need to execute before the SQL is passed through to the database. 136 * 137 * <h3>Translatable functions</h3> 138 * Any functions which implement SqlFunction are translated into SQL and evaluated by 139 * the database. This is useful for things like between() and like(), which are 140 * converted into relational operators (< and >) and LIKE operators. 141 * 142 * <p>If a function implements both EvalFunction and SqlFunction, then this class 143 * will always attempt to translate the function into SQL using the SqlFunction interface. 144 * 145 * <h3>Database columns</h3> 146 * If a column is to be referenced from within the SQL, it must be set up in the EvalContext 147 * as well (as per the 'externalMessageType' lines in the previous example). 148 * 149 * <h4>Column types</h4> 150 * If not specified, the type of the column is VARCHAR, although this can be set 151 * in the constructor: 152 * 153 * <pre style="code"> 154 * SqlColumn arrivalDate = new SqlColumn("arrivalDate", SqlColumn.DATE); 155 * </pre> 156 * 157 * <p>(All java.sql.Types.* constants are supported here). 158 * 159 * <h4>Column tables</h4> 160 * <p>If our resultset JOINs many tables, and we wish to retrieve a value from a specific 161 * table, that can also be placed in the SqlColumn constructor. 162 * 163 * <pre style="code"> 164 * // e.g. SELECT tableA.id, tableB.id FROM tableA, tableB WHERE tableA.x = tableB.x 165 * SqlColumn tableA_id = new SqlColumn("id", "tableA"); 166 * SqlColumn tableB_id = new SqlColumn("id", "tableB"); 167 * </pre> 168 * 169 * <!-- 170 * <h4>Money values</h4> 171 * <p>If we wish to do monetary comparisons and the 'money value' spans two columns (currency and 172 * amount), we can use the custom 'MONEYVALUE' type: 173 * 174 * <pre style="code"> 175 * SqlColumn moneyColumn = new SqlColumn("amount", SqlColumn.MONEYVALUE); 176 * moneyColumn.setCurrencyCodeName("currency"); 177 * </pre> 178 * 179 * <p>this sets up a column which retrieves it's amount from the 'amount' value, and 180 * the currency the 'currency' column of the table. MONEYVALUE columns can only be 181 * compared against other MONEYVALUE columns or MoneyValue objects, e.g.: 182 * 183 * <pre style="code"> 184 * String exprString = "amount > toMoneyValue('USD', 10000)"; 185 * ... 186 * context.setFunction("toMoneyValue", new EvalFunction.MoneyValueFunction() ); 187 * context.setVariable("amount", moneyColumn); 188 * </pre 189 * 190 * which will generate SQL of the form <code>currency = "USD" AND amount > 10000</code>. 191 * (MoneyValueFunction is an EvalFunction which returns a MoneyValue object). 192 * --> 193 * 194 * <h4>FixedDate values</h4> 195 * <p>Another custom data type is the 'fixed date', which is a date represented as a 196 * string, in YYYYMMDD format. It is treated internally as identical to a VARCHAR 197 * column, but allows operations on these columns to be aware that the information 198 * being stored in it is a date. 199 * 200 * <p>Other references: 201 * <ul> 202 * <li>DB2 date syntax: http://www-106.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html 203 * <li>SQL date syntax: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp 204 * <li>Oracle date syntax: http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html 205 * </ul> 206 * 207 * 208 * @author knoxg 209 */ 210public class SqlGenerator 211 extends Evaluator { 212 213 /** Internal variable to contain database type. 214 * (internal variables start with "." since they are not valid identifiers, 215 * and therefore cannot be referenced from within Expressions). */ 216 public static final String VAR_DATABASE_TYPE = ".databaseType"; 217 218 /** Internal variable used to contain positional parameter names. */ 219 public static final String VAR_PARAMETERS = ".parameters"; 220 221 /** Internal variable to configure empty string comparison handling. 222 * 223 * When set to Boolean.TRUE, a comparison with the String constant "" will also 224 * include NULL values. See {{@link #comparisonOpToSql(String, EvalContext, Object, Object)} 225 */ 226 public static final String VAR_EMPTY_STRING_COMPARISON_INCLUDES_NULL = ".emptyStringComparisonIncludesNull"; 227 228 229 /** Oracle VAR_DATABASE_TYPE value */ 230 public static final String DATABASE_ORACLE = "Oracle"; 231 232 /** DB2 VAR_DATABASE_TYPE value */ 233 public static final String DATABASE_DB2 = "DB2"; 234 235 /** SQL Server VAR_DATABASE_TYPE value */ 236 public static final String DATABASE_SQLSERVER = "SqlServer"; 237 238 /** MySQL VAR_DATABASE_TYPE value */ 239 public static final String DATABASE_MYSQL = "MySQL"; 240 241 /** Jet (MSAccess) VAR_DATABASE_TYPE value */ 242 public static final String DATABASE_JET = "JET"; 243 244 245 // these are non-printable to reduce the chance of conflicts within strings. 246 247 /** Used to internally delimit positional paramaeters in generated SQL */ 248 private static final String POS_MARKER_LEFT = "\uF0000"; 249 250 /** Used to internally delimit positional paramaeters in generated SQL */ 251 private static final String POS_MARKER_RIGHT = "\uF0001"; 252 253 /** Logger instance for this class */ 254 Logger logger = Logger.getLogger(SqlGenerator.class); 255 256 /** Generates code for comparisons of MoneyValue and Date types. Other data types 257 * fall back to "(" + lhs + " " + sqlOp + " " + rhs + ")". 258 * 259 * @param sqlOp The operation we wish to insert into the SQL. 260 * (NB: this is *NOT* the operation in the .javacc syntax; e.g. 261 * use "=" as an sqlOp, not "==" ). 262 * @param newSqlOp A modified SQL operator to use when dealing with dates, in 263 * certain conditions. See the wiki for details. 264 * @param lhs The left hand side of the operation 265 * @param rhs The right hand side of the operation 266 * @return The SQL for this comparison 267 */ 268 public static SqlText comparisonOpToSql(String sqlOp, EvalContext evalContext, Object lhs, Object rhs) { 269 // Date date = null; 270 // boolean startOfDay = false; 271 272 SqlColumn sqlColumn = (lhs instanceof SqlColumn) ? (SqlColumn) lhs : null; 273 int dataType = (sqlColumn == null) ? -1 : sqlColumn.getDataType(); 274 DateSpan dateSpan; 275 276 // Expression formats 277 // DateFormat isoDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 278 // DateFormat isoDateTimeFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); 279 280 if (rhs instanceof Date) { 281 throw new EvalException("Date objects not supported (use DateSpan instead)"); 282 } 283 284 // manipulate the conditional operator 285 switch (dataType) { 286 case -1: 287 // lhs is not an SqlColumn; treat normally 288 break; 289 290 /* 291 case SqlColumn.MONEYVALUE: 292 if (!(rhs instanceof MoneyValue)) { 293 throw new EvalException("The lhs of this comparison is a MONEYVALUE SqlColumn; " + 294 "rhs can only be a MoneyValue literal (found " + rhs.getClass().getName() + "')"); 295 } 296 MoneyValue money = (MoneyValue) rhs; 297 return new SqlText("((" + sqlColumn.getFullCurrencyCodeName() + " = '" + 298 money.getCurrency().getCurrencyCode() + "') AND (" + 299 sqlColumn.getFullName() + " " + sqlOp + " " + money.getAmount() + "))"); 300 */ 301 302 case SqlColumn.TIMEVALUE: // used to represent ConditionTO.DATATYPE_DATETIME columns 303 if (!(rhs instanceof DateSpan)) { 304 throw new EvalException("The lhs of this comparison is a TIMEVALUE SqlColumn; " + 305 "rhs can only be a DateSpan literal (found " + rhs.getClass().getName() + "')"); 306 } 307 dateSpan = (DateSpan) rhs; 308 if (dateSpan.isDay()) { 309 if (sqlOp.equals("=")) { 310 return new SqlText("(" + toSql(evalContext, lhs) + " >= " + toSql(evalContext, dateSpan.getStartAsDate()) + 311 " AND " + toSql(evalContext, lhs) + " <= " + toSql(evalContext, dateSpan.getEndAsDate()) + ")"); 312 } else if (sqlOp.equals("<>")) { 313 return new SqlText("(" + toSql(evalContext, lhs) + " < " + toSql(evalContext, dateSpan.getStartAsDate()) + 314 " OR " + toSql(evalContext, lhs) + " > " + toSql(evalContext, dateSpan.getEndAsDate()) + ")"); 315 } else if (sqlOp.equals(">")) { 316 rhs = dateSpan.getEndAsDate(); 317 } else if (sqlOp.equals(">=")) { 318 rhs = dateSpan.getStartAsDate(); 319 } else if (sqlOp.equals("<")) { 320 rhs = dateSpan.getStartAsDate(); 321 } else if (sqlOp.equals("<=")) { 322 rhs = dateSpan.getEndAsDate(); 323 } else { 324 throw new EvalException("Unknown SQL operator '" + sqlOp + "'"); 325 } 326 } else { 327 rhs = dateSpan.getStartAsDate(); 328 } 329 break; 330 331 case SqlColumn.FIXEDDATEVALUE: // used to represent ConditionTO.DATATYPE_FIXEDDATE columns 332 if (!(rhs instanceof DateSpan)) { 333 throw new EvalException("The lhs of this comparison is a FIXEDDATEVALUE SqlColumn; " + "can only compare against a DateSpan literal"); 334 } 335 dateSpan = (DateSpan) rhs; 336 if (!dateSpan.isDay()) { 337 throw new EvalException("The lhs of this comparison is a FIXEDDATEVALUE SqlColumn; " + "DateSpan cannot contain time components"); 338 } 339 340 // just do a string comparison 341 rhs = dateSpan.getStartAsYyyymmdd(); 342 343 break; 344 default: 345 // any other data types are compared normally, as below. 346 } 347 348 if (rhs instanceof SqlColumn) { 349 // query builder doesn't allow these types of expressions, and throwing an exception just keeps the code simpler. 350 throw new EvalException("rhs SqlColumn not implemented"); 351 } 352 353 boolean emptyStringIncludesNull = Boolean.TRUE.equals(evalContext.getVariable(VAR_EMPTY_STRING_COMPARISON_INCLUDES_NULL)); 354 355 // default action: just compare the lhs to the rhs 356 SqlText result = new SqlText("(" + toSql(evalContext, lhs) + " " + sqlOp + " " + toSql(evalContext, rhs) + ")"); 357 358 if (emptyStringIncludesNull) { 359 // empty/null checks against strings 360 if ("=".equals(sqlOp) && "".equals(rhs)) { 361 result = new SqlText("(" + result + " OR (" + toSql(evalContext, lhs) + " IS NULL))"); 362 } else if ("<>".equals(sqlOp) && "".equals(rhs)) { 363 result = new SqlText("(" + result + " OR (NOT " + toSql(evalContext, lhs) + " IS NULL))"); 364 } else if ("<>".equals(sqlOp) && !"".equals(rhs)) { 365 result = new SqlText("(" + result + " OR (" + toSql(evalContext, lhs) + " IS NULL))"); 366 } 367 } 368 369 return result; 370 } 371 372 /** 373 * Return the database type from this context 374 * 375 * @param evalContext The context passed to the .visit() method of this class 376 * @return a DATABASE_* constant 377 * 378 * @throws EvalException if database type is not set, or is invalid 379 */ 380 public static String getDatabaseType(EvalContext evalContext) { 381 String databaseType = (String) evalContext.getVariable(VAR_DATABASE_TYPE); 382 383 if (databaseType == null) { 384 throw new EvalException("Database type must be set to translate this expression"); 385 } 386 387 if (databaseType.equals(DATABASE_DB2) || 388 databaseType.equals(DATABASE_ORACLE) || 389 databaseType.equals(DATABASE_SQLSERVER) || 390 databaseType.equals(DATABASE_JET) || 391 databaseType.equals(DATABASE_MYSQL)) { 392 return databaseType; 393 } 394 395 throw new EvalException("Unknown database type '" + databaseType + "'"); 396 } 397 398 399 /** Convert an evaluated node object into it's SQL representation. 400 * The code must have evaluated to a literal String or Number by the 401 * time this method is called, or an EvalException is thrown. 402 * 403 * @param obj The object to convert to SQL 404 * @return The SQL representation of this object 405 */ 406 public static SqlText toSql(EvalContext evalContext, Object obj) { 407 if (obj == null) { 408 // ? 409 throw new EvalException("Null values not supported"); 410 411 } else if (obj instanceof SqlColumn) { 412 return new SqlText(((SqlColumn) obj).getFullName()); 413 414 } else if (obj instanceof SqlText) { 415 return (SqlText) obj; 416 417 } else if (obj instanceof String) { 418 return new SqlText(escapeStringLiteral(evalContext, (String) obj)); 419 420 } else if (obj instanceof Number) { 421 // NB: this will probably fail when processing E+ exponent notation 422 // in numbers, even though it is actually allowed in expression.jj. 423 // This shouldn't occur in practice, however. 424 return new SqlText(obj.toString()); 425 426 } else if (obj instanceof Date) { 427 String databaseType = getDatabaseType(evalContext); 428 Date date; 429 SimpleDateFormat sdf; 430 431 if (databaseType.equals(DATABASE_ORACLE)) { 432 date = (Date) obj; 433 // @TODO (low priority) we may need to set timezone for sdf below to the database timezone, but we'll just assume the server tz = database tz 434 sdf = new SimpleDateFormat("yyyy/MM/dd:HH:mm:ss"); 435 return new SqlText("TO_DATE('" + sdf.format(date) + "', 'yyyy/mm/dd:HH24:mi:ss')"); 436 437 } else if (databaseType.equals(DATABASE_DB2)) { 438 date = (Date) obj; 439 sdf = new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss"); 440 return new SqlText("TIMESTAMP('" + sdf.format(date) + "')"); 441 442 } else if (databaseType.equals(DATABASE_SQLSERVER)) { 443 date = (Date) obj; 444 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 445 return new SqlText("CONVERT(datetime, '" + sdf.format(date) + "', 20)"); 446 447 } else if (databaseType.equals(DATABASE_MYSQL)) { 448 date = (Date) obj; 449 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 450 return new SqlText("'" + sdf.format(date) + "'"); 451 452 } else if (databaseType.equals(DATABASE_JET)) { 453 date = (Date) obj; 454 sdf = new SimpleDateFormat("#dd/MM/yyyy HH:mm:ss#"); // american date ordering in JET SQL. ODBC might reverse this though. let's see. 455 return new SqlText("'" + sdf.format(date) + "'"); 456 457 458 } else { 459 throw new EvalException("Date constants unsupported for database type '" + databaseType + "'"); 460 } 461 462 } else if (obj instanceof Boolean b) { 463 return b ? new SqlText("TRUE") : new SqlText("FALSE"); 464 465 } else { 466 throw new EvalException("Cannot convert class '" + obj.getClass().getName() + "' to SQL"); 467 } 468 } 469 470 /** 471 * Escape a string literal. (in oracle, this surrounds a value with quotes and 472 * replaces quotes with doubled-up quote characters). 473 * 474 * @param evalContext 475 * @param string 476 * @return 477 */ 478 public static String escapeStringLiteral(EvalContext evalContext, String string) { 479 String databaseType = getDatabaseType(evalContext); 480 481 if (databaseType.equals(DATABASE_ORACLE)) { 482 string = "'" + Text.replaceString(string, "'", "''") + "'"; 483 } else if (databaseType.equals(DATABASE_DB2)) { 484 string = "'" + Text.replaceString(string, "'", "''") + "'"; 485 } else if (databaseType.equals(DATABASE_SQLSERVER)) { 486 string = "'" + Text.replaceString(string, "'", "''") + "'"; 487 } else if (databaseType.equals(DATABASE_MYSQL)) { 488 // mysql string escaping rules: https://dev.mysql.com/doc/refman/8.0/en/string-literals.html 489 490 // we're not going to escape backslashes in here as the user may have intended to already escape 491 // some characters in LIKE expressions; we don't want to convert "\% starts with a percentage sign" 492 // to "\\% starts with a percentage sign". 493 // That also means the user is responsible for converting newlines to "\n" etc 494 // (nb: you can actually have newlines in string literals in mysql) 495 496 // also note \% is treated by mysql as "\%" outside of a like expression 497 // and \_ is treated by mysql as "\_" outside of a like expression 498 // but most other escapes, and unknown escapes are converted to single characters; e.g. 499 // \x is treated by mysql as "x" outside of a like expression 500 // but again this is up to the user to grok. 501 502 // if I start using this class across databases again may want to come up with some 503 // crossvendor escaping rules which will have a myriad of tiny bugs in it 504 505 string = "'" + Text.replaceString(string, "'", "\\'") + "'"; 506 } else if (databaseType.equals(DATABASE_JET)) { 507 string = "'" + Text.replaceString(string, "'", "\\'") + "'"; 508 509 } else { 510 throw new EvalException("Do not know how to escape string literals in database type '" + databaseType + "'"); 511 } 512 513 return string; 514 } 515 516 /** 517 * Escape the text used in a LIKE function, so that % and _ characters have no 518 * special meaning. Note that an escaped like literal still needs to be passed 519 * through escapeStringLiteral in order to surround it with quotes/escape quotes, etc... 520 * 521 * <p>(This isn't done in this method because we want to be able to modify the LIKE 522 * value that the user enters e.g. for the startsWith() function) 523 * 524 * @param evalContext The evaluation context (containing the database type) 525 * @param string The string to be escaped 526 527 * @return The escaped string 528 */ 529 public static String escapeLikeLiteral(EvalContext evalContext, String string) { 530 String databaseType = getDatabaseType(evalContext); 531 532 // only the Oracle rules below have been verified; should probably check 533 // the rest 534 if (databaseType.equals(DATABASE_ORACLE)) { 535 string = Text.replaceString(string, "%", "\\%"); // % becomes \% 536 string = Text.replaceString(string, "_", "\\_"); // _ becomes \_ 537 } else if (databaseType.equals(DATABASE_SQLSERVER)) { 538 string = Text.replaceString(string, "%", "\\%"); // % becomes \% 539 string = Text.replaceString(string, "_", "\\_"); // _ becomes \_ 540 } else if (databaseType.equals(DATABASE_DB2)) { 541 string = Text.replaceString(string, "%", "\\%"); // % becomes \% 542 string = Text.replaceString(string, "_", "\\_"); // _ becomes \_ 543 } else if (databaseType.equals(DATABASE_MYSQL)) { 544 string = Text.replaceString(string, "%", "\\%"); // % becomes \% 545 string = Text.replaceString(string, "_", "\\_"); // _ becomes \_ 546 } else if (databaseType.equals(DATABASE_JET)) { 547 string = Text.replaceString(string, "%", "\\%"); // % becomes \% . Possibly '*' in JET. 548 string = Text.replaceString(string, "_", "\\_"); // _ becomes \_ 549 550 } else { 551 throw new EvalException("Do not know how to escape string literals in database type '" + databaseType + "'"); 552 } 553 554 return string; 555 } 556 557 /** Convert a TopLevelExpression node to it's SQL representation. The SQL is returned 558 * as a String. If any positional parameters have been referenced inside the SQL, 559 * then the context variable VAR_PARAMETERS is set, containing an ordered list of Strings, 560 * containing the positional parameter names. 561 * 562 * <p>The PRE text in these javadocs correspond to the javacc expansion of 563 * this node; e.g. in expression.jj, the rule for TopLevelExpression 564 * is 565 * 566 * <pre> 567 * void TopLevelExpression(): 568 * {} 569 * { 570 * Expression() <EOF> 571 * } 572 * </pre> 573 * 574 * <p>jtb then creates two fields in the TopLevelExpression object, "expression" (containing 575 * the Expression() node) and "nodeToken" (containing the EOF token). This is documented 576 * in the form below (this is included for all jtb-generated visit methods): 577 * 578 * <p>See the class javadocs for detailed instructions on how to use this method. 579 * 580 * <PRE> 581 * expression -> Expression() 582 * nodeToken -> <EOF> 583 * </PRE> 584 */ 585 public Object visit(TopLevelExpression n, EvalContext context) { 586 EvalContext evalContext = (EvalContext) context; 587 String sql = n.expression.accept(this, context).toString(); 588 589 // replace {positionalNames} with '?' markers and remember what we've hit so far ... 590 int pos = sql.indexOf(POS_MARKER_LEFT); 591 int pos2; 592 List<String> paramList = new ArrayList<>(); 593 594 while (pos != -1) { 595 pos2 = sql.indexOf(POS_MARKER_RIGHT, pos + POS_MARKER_LEFT.length()); 596 597 if (pos2 == -1) { 598 throw new EvalException("Internal error (unclosed positional parameter)"); 599 } 600 601 paramList.add(sql.substring(pos + POS_MARKER_LEFT.length(), pos2)); 602 sql = sql.substring(0, pos) + "?" + sql.substring(pos2 + POS_MARKER_RIGHT.length()); 603 pos = sql.indexOf(POS_MARKER_LEFT); 604 } 605 606 if (paramList.size() > 0) { 607 evalContext.setVariable(VAR_PARAMETERS, paramList); 608 } else { 609 evalContext.unsetVariable(VAR_PARAMETERS); 610 } 611 612 return sql.toString(); 613 } 614 615 /** Generate the SQL for an Expression node. 616 * 617 * <PRE> 618 * conditionalAndExpression -> ConditionalAndExpression() 619 * nodeListOptional -> ( "||" ConditionalAndExpression() )* 620 * </PRE> 621 */ 622 public Object visit(Expression n, EvalContext context) { 623 NodeSequence seq; 624 Object lhs; 625 Object rhs; 626 EvalContext evalContext = (EvalContext) context; 627 628 lhs = n.conditionalAndExpression.accept(this, context); 629 630 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 631 seq = (NodeSequence) e.nextElement(); 632 rhs = seq.elementAt(1).accept(this, context); 633 lhs = new SqlText("(" + toSql(evalContext, lhs) + " OR " + toSql(evalContext, rhs) + ")"); 634 } 635 636 return lhs; 637 } 638 639 /** Generate the SQL for a ConditionalAndExpression node. 640 * 641 * <PRE> 642 * equalityExpression -> EqualityExpression() 643 * nodeListOptional -> ( "&&" EqualityExpression() )* 644 * </PRE> 645 */ 646 public Object visit(ConditionalAndExpression n, EvalContext context) { 647 NodeSequence seq; 648 Object lhs; 649 Object rhs; 650 EvalContext evalContext = (EvalContext) context; 651 652 lhs = n.equalityExpression.accept(this, context); 653 654 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 655 seq = (NodeSequence) e.nextElement(); 656 rhs = seq.elementAt(1).accept(this, context); 657 lhs = new SqlText("(" + toSql(evalContext, lhs) + " AND " + toSql(evalContext, rhs) + ")"); 658 } 659 660 return lhs; 661 } 662 663 /** Generate the SQL for a EqualityExpression node. 664 * 665 * <PRE> 666 * relationalExpression -> RelationalExpression() 667 * nodeListOptional -> ( ( "==" | "!=" ) RelationalExpression() )* 668 * </PRE> 669 */ 670 public Object visit(EqualityExpression n, EvalContext context) { 671 NodeSequence seq; 672 Object lhs; 673 Object rhs; 674 String[] ops = { "==", "!=" }; 675 EvalContext evalContext = (EvalContext) context; 676 677 lhs = n.relationalExpression.accept(this, context); 678 679 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 680 seq = (NodeSequence) e.nextElement(); 681 rhs = seq.elementAt(1).accept(this, context); 682 683 int which = ((NodeChoice) seq.elementAt(0)).which; 684 685 switch (which) { 686 // this requires a special-case on null rhs, but 687 // leaving this as it won't occur in my app at the moment. (I 688 // use the isNull() functions for this sort of thing) 689 case 0: 690 lhs = comparisonOpToSql("=", evalContext, lhs, rhs); 691 break; 692 case 1: 693 lhs = comparisonOpToSql("<>", evalContext, lhs, rhs); 694 break; 695 default: 696 throw new EvalException("Internal error (EqualityExpression)"); 697 } 698 } 699 700 return lhs; 701 } 702 703 /** Generate the SQL for a RelationalExpression node. 704 * 705 * <PRE> 706 * additiveExpression -> AdditiveExpression() 707 * nodeListOptional -> ( ( "<" | ">" | "<=" | ">=" ) AdditiveExpression() )* 708 * </PRE> 709 */ 710 public Object visit(RelationalExpression n, EvalContext context) { 711 NodeSequence seq; 712 Object lhs; 713 Object rhs; 714 String[] ops = { "<", ">", "<=", ">=" }; 715 EvalContext evalContext = (EvalContext) context; 716 717 lhs = n.additiveExpression.accept(this, context); 718 719 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 720 seq = (NodeSequence) e.nextElement(); 721 rhs = seq.elementAt(1).accept(this, context); 722 723 int which = ((NodeChoice) seq.elementAt(0)).which; 724 725 // logger.debug("Running op '" + ops[which] + "' on lhs:" + lhs + ", rhs:" + rhs); 726 // note comparisons ops here may be modified. 727 switch (which) { 728 case 0: 729 lhs = comparisonOpToSql("<", evalContext, lhs, rhs); 730 break; 731 case 1: 732 lhs = comparisonOpToSql(">", evalContext, lhs, rhs); 733 break; 734 case 2: 735 lhs = comparisonOpToSql("<=", evalContext, lhs, rhs); 736 break; 737 case 3: 738 lhs = comparisonOpToSql(">=", evalContext, lhs, rhs); 739 break; 740 default: 741 throw new EvalException("Internal error (RelationalExpression)"); 742 } 743 } 744 745 return lhs; 746 } 747 748 /** Generate the SQL for a AdditiveExpression node. 749 * 750 * <PRE> 751 * multiplicativeExpression -> MultiplicativeExpression() 752 * nodeListOptional -> ( ( "+" | "-" ) MultiplicativeExpression() )* 753 * </PRE> 754 */ 755 public Object visit(AdditiveExpression n, EvalContext context) { 756 NodeSequence seq; 757 Object lhs; 758 Object rhs; 759 String[] ops = { "+", "-" }; 760 EvalContext evalContext = (EvalContext) context; 761 762 lhs = n.multiplicativeExpression.accept(this, context); 763 764 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 765 seq = (NodeSequence) e.nextElement(); 766 rhs = seq.elementAt(1).accept(this, context); 767 768 int which = ((NodeChoice) seq.elementAt(0)).which; 769 770 // logger.debug("Running op '" + ops[which] + "' on lhs:" + lhs + ", rhs:" + rhs); 771 switch (which) { 772 case 0: 773 // NB: does not support String addition, only numeric addition 774 lhs = new SqlText("(" + toSql(evalContext, lhs) + " + " + toSql(evalContext, rhs) + ")"); 775 break; 776 case 1: 777 lhs = new SqlText("(" + toSql(evalContext, lhs) + " - " + toSql(evalContext, rhs) + ")"); 778 break; 779 default: 780 throw new EvalException("Internal error (AdditiveExpression)"); 781 } 782 } 783 784 return lhs; 785 } 786 787 /** Generate the SQL for a MultiplicativeExpression node. 788 * 789 * <PRE> 790 * unaryExpression -> UnaryExpression() 791 * nodeListOptional -> ( ( "*" | "/" | "%" ) UnaryExpression() )* 792 * </PRE> 793 */ 794 public Object visit(MultiplicativeExpression n, EvalContext context) { 795 NodeSequence seq; 796 Object lhs; 797 Object rhs; 798 String[] ops = { "*", "/", "%" }; 799 800 lhs = n.unaryExpression.accept(this, context); 801 802 EvalContext evalContext = (EvalContext) context; 803 804 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 805 seq = (NodeSequence) e.nextElement(); 806 rhs = seq.elementAt(1).accept(this, context); 807 808 int which = ((NodeChoice) seq.elementAt(0)).which; 809 810 // logger.debug("Running op '" + ops[which] + "' on lhs:" + lhs + ", rhs:" + rhs); 811 switch (which) { 812 case 0: 813 lhs = new SqlText("(" + toSql(evalContext, lhs) + " * " + toSql(evalContext, rhs) + ")"); 814 break; 815 case 1: 816 lhs = new SqlText("(" + toSql(evalContext, lhs) + " / " + toSql(evalContext, rhs) + ")"); 817 break; 818 case 2: 819 lhs = new SqlText("(" + toSql(evalContext, lhs) + " % " + toSql(evalContext, rhs) + ")"); 820 break; 821 default: 822 throw new EvalException("Internal error (MultiplicativeExpression)"); 823 } 824 } 825 826 return lhs; 827 } 828 829 /** Generate the SQL for a UnaryExpression node. 830 * 831 * <PRE> 832 * nodeChoice -> ( "~" | "!" | "-" ) UnaryExpression() 833 * | PrimaryExpression() 834 * </PRE> 835 */ 836 public Object visit(UnaryExpression n, EvalContext context) { 837 NodeSequence seq; 838 Object lhs; 839 EvalContext evalContext = (EvalContext) context; 840 841 if (n.nodeChoice.which == 0) { 842 seq = (NodeSequence) n.nodeChoice.choice; 843 lhs = seq.elementAt(1).accept(this, context); 844 845 int which = ((NodeChoice) seq.elementAt(0)).which; 846 847 // String op = ((NodeToken) ((NodeChoice) nl.nodes.get(0)).choice).tokenImage; 848 switch (which) { 849 case 0: 850 // long rhs = makeNumeric ( ((Node) nl.nodes.get(0)).accept(this, context) ); 851 // return new Long(~rhs); 852 return new SqlText("(~" + toSql(evalContext, lhs) + ")"); 853 case 1: 854 return new SqlText("(NOT " + toSql(evalContext, lhs) + ")"); 855 case 2: 856 return new SqlText("(- " + toSql(evalContext, lhs) + ")"); 857 default: 858 throw new EvalException("Internal error (UnaryExpression)"); 859 } 860 } else { 861 return n.nodeChoice.choice.accept(this, context); 862 } 863 } 864 865 /** Generate the SQL for a PrimaryExpression node. 866 * 867 * <PRE> 868 * nodeChoice -> FunctionCall() 869 * | Name() 870 * | Literal() 871 * | "(" Expression() ")" 872 * </PRE> 873 */ 874 public Object visit(PrimaryExpression n, EvalContext context) { 875 NodeSequence seq; 876 int which = n.nodeChoice.which; 877 878 switch (which) { 879 case 0: 880 case 1: 881 case 2: 882 return n.nodeChoice.choice.accept(this, context); 883 case 3: 884 seq = (NodeSequence) n.nodeChoice.choice; 885 Object obj = seq.elementAt(1).accept(this, context); 886 887 return obj; 888 default: 889 throw new EvalException("Internal parser error (PrimaryExpression)"); 890 } 891 } 892 893 /** Generate the SQL for a Name node. 894 * 895 * <PRE> 896 * nodeToken -> <IDENTIFIER> 897 * nodeListOptional -> ( "." <IDENTIFIER> )* 898 * </PRE> 899 */ 900 public Object visit(Name n, EvalContext context) { 901 EvalContext evalContext = (EvalContext) context; 902 String varComponentName; 903 String varBaseName = n.nodeToken.tokenImage; 904 905 // logger.debug("Fetching var '" + varBaseName + "'"); 906 if (!evalContext.hasVariable(varBaseName)) { 907 throw new EvalException("Unknown variable '" + varBaseName + "'"); 908 } 909 910 Object value = evalContext.getVariable(varBaseName); 911 NodeSequence seq; 912 913 /* 914 if (value == null) { 915 logger.debug(" = null"); 916 } else { 917 logger.debug(" = " + value.getClass().getName() + " with value '" + value + "'"); 918 } 919 */ 920 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 921 seq = (NodeSequence) e.nextElement(); 922 varComponentName = ((NodeToken) seq.elementAt(1)).tokenImage; 923 924 // logger.debug("Fetching component '" + varComponentName + "' from var '" + varBaseName + "'"); 925 if (evalContext.hasVariableComponent(value, varBaseName, varComponentName)) { 926 throw new EvalException("Unknown variable component '" + varComponentName + "' in variable '" + varBaseName + "'"); 927 } 928 929 value = evalContext.getVariableComponent(value, varBaseName, varComponentName); 930 varBaseName = varBaseName + "." + varComponentName; 931 } 932 933 934 935 936 // insert positional parameters into the generated SQL as 937 // {paramName} 938 // ... these will get substituted with '?' just before 939 // visit(TopLevelExpression) returns the result to the caller. 940 if (value instanceof PositionalParameter) { 941 PositionalParameter posParameter = (PositionalParameter) value; 942 943 return new SqlText(POS_MARKER_LEFT + posParameter.getName() + POS_MARKER_RIGHT); 944 } 945 946 947 // otherwise just get the value itself (this will be converted into SqlText 948 // by other visit() methods) 949 return value; 950 } 951 952 /** Generate the SQL for a FunctionCall node. 953 * 954 * <PRE> 955 * nodeToken -> <IDENTIFIER> 956 * arguments -> Arguments() 957 * </PRE> 958 */ 959 public Object visit(FunctionCall n, EvalContext context) { 960 List argumentList = (List) n.arguments.accept(this, context); 961 String functionName = n.nodeToken.tokenImage; 962 EvalContext evalContext = (EvalContext) context; 963 Object function = evalContext.getFunction(functionName); 964 Evaluator evaluator = new Evaluator(); 965 966 if (function == null) { 967 throw new EvalException("Unknown function '" + functionName + "'"); 968 969 } else if (function instanceof SqlFunction) { 970 SqlFunction sqlFunction = (SqlFunction) function; 971 try { 972 return new SqlText(sqlFunction.toSql(functionName, evalContext, argumentList)); 973 } catch (EvalFallbackException ee) { 974 // I think this only occurs in replaced promptable values 975 976 logger.info("evaluating fallback for '" + functionName + "'"); 977 argumentList = (List) evaluator.visit((Arguments) n.arguments, context); 978 return ((EvalFunction) sqlFunction).evaluate(functionName, evalContext, argumentList); 979 } 980 981 } else if (function instanceof EvalFunction) { 982 EvalFunction evalFunction = (EvalFunction) function; 983 // evaluate the argument list (pass in the context to allow TZ_* references) 984 argumentList = (List) evaluator.visit((Arguments) n.arguments, context); 985 return evalFunction.evaluate(functionName, evalContext, argumentList); 986 987 } 988 989 throw new EvalException("Cannot translate or evaluate function '" + function + "'"); 990 } 991 992 /** Generate the SQL for an Arguments node 993 * 994 * <PRE> 995 * nodeToken -> "(" 996 * nodeOptional -> [ ArgumentList() ] 997 * nodeToken1 -> ")" 998 * </PRE> 999 */ 1000 public Object visit(Arguments n, EvalContext context) { 1001 if (n.nodeOptional.present()) { 1002 return n.nodeOptional.accept(this, context); 1003 } else { 1004 return new ArrayList<>(0); 1005 } 1006 } 1007 1008 /** Generate the SQL for a ArgumentList node. 1009 * 1010 * <p>(Since an ArgumentList is fed directly into the EvalFunction to generate SQL, 1011 * we just return a List of arguments) 1012 * 1013 * <PRE> 1014 * expression -> Expression() 1015 * nodeListOptional -> ( "," Expression() )* 1016 * </PRE> 1017 */ 1018 public Object visit(ArgumentList n, EvalContext context) { 1019 NodeSequence seq; 1020 List<Object> arguments = new ArrayList<>(); 1021 1022 arguments.add(n.expression.accept(this, context)); 1023 1024 for (Enumeration<?> e = n.nodeListOptional.elements(); e.hasMoreElements();) { 1025 seq = (NodeSequence) e.nextElement(); 1026 arguments.add(seq.elementAt(1).accept(this, context)); 1027 } 1028 1029 return arguments; 1030 } 1031 1032 /** Generate the SQL for a Literal node. 1033 * 1034 * <PRE> 1035 * nodeChoice -> <INTEGER_LITERAL> 1036 * | <FLOATING_POINT_LITERAL> 1037 * | <CHARACTER_LITERAL> 1038 * | <STRING_LITERAL> 1039 * | BooleanLiteral() 1040 * | NullLiteral() 1041 * </PRE> 1042 * 1043 */ 1044 public Object visit(Literal n, EvalContext context) { 1045 String token = null; 1046 1047 if (n.nodeChoice.choice instanceof NodeToken) { 1048 token = ((NodeToken) n.nodeChoice.choice).tokenImage; 1049 } 1050 1051 switch (n.nodeChoice.which) { 1052 case 0: 1053 return Long.valueOf(token); 1054 case 1: 1055 return Double.valueOf(token); 1056 case 2: 1057 return Character.valueOf(token.charAt(1)); 1058 case 3: 1059 return Text.unescapeJava(token.substring(1, token.length() - 1)); 1060 } 1061 1062 // must be 'true', 'false', or 'null' 1063 return n.nodeChoice.accept(this, context); 1064 } 1065 1066 /** Generate the SQL for a BooleanLiteral node. 1067 * 1068 * <PRE> 1069 * nodeChoice -> "true" 1070 * | "false" 1071 * </PRE> 1072 */ 1073 public Object visit(BooleanLiteral n, EvalContext context) { 1074 if (n.nodeChoice.which == 0) { 1075 return Boolean.valueOf(true); 1076 } else { 1077 return Boolean.valueOf(false); 1078 } 1079 } 1080 1081 /** Generate the SQL for a NullLiteral node. 1082 * 1083 * <PRE> 1084 * nodeToken -> "null" 1085 * </PRE> 1086 */ 1087 public Object visit(NullLiteral n, EvalContext context) { 1088 return null; 1089 } 1090 1091 /** This is never executed (we do not evaluate tokens) */ 1092 public Object visit(NodeToken n, EvalContext context) { 1093 return n.tokenImage; 1094 } 1095}