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