001/** 002 * 003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved. 004 * 005 * This library is free software; you can redistribute it and/or 006 * modify it under the terms of the GNU Lesser General Public 007 * License as published by the Free Software Foundation; either 008 * version 2.1 of the License, or (at your option) any later version. 009 * 010 * This library is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 013 * Lesser General Public License for more details. 014 * 015 * You should have received a copy of the GNU Lesser General Public 016 * License along with this library. If not, see <http://www.gnu.org/licenses/>. 017 * 018 **/ 019package lucee.runtime.sql; 020 021import java.util.ArrayList; 022import java.util.List; 023 024import lucee.commons.lang.ParserString; 025import lucee.commons.lang.types.RefBoolean; 026import lucee.commons.lang.types.RefBooleanImpl; 027import lucee.runtime.exp.PageException; 028import lucee.runtime.op.Caster; 029import lucee.runtime.sql.exp.Column; 030import lucee.runtime.sql.exp.ColumnExpression; 031import lucee.runtime.sql.exp.Expression; 032import lucee.runtime.sql.exp.op.Operation; 033import lucee.runtime.sql.exp.op.Operation1; 034import lucee.runtime.sql.exp.op.Operation2; 035import lucee.runtime.sql.exp.op.Operation3; 036import lucee.runtime.sql.exp.op.OperationN; 037import lucee.runtime.sql.exp.value.ValueBoolean; 038import lucee.runtime.sql.exp.value.ValueDate; 039import lucee.runtime.sql.exp.value.ValueNull; 040import lucee.runtime.sql.exp.value.ValueNumber; 041import lucee.runtime.sql.exp.value.ValueString; 042 043public class SelectParser { 044 045 046 047 /* 048SELECT [{LIMIT <offset> <limit> | TOP <limit>}[1]][ALL | DISTINCT] 049{ <selectExpression> | table.* | * } [, ...] 050[INTO [CACHED | TEMP | TEXT][1] newTable] 051FROM tableList 052[WHERE Expression] 053[GROUP BY Expression [, ...]] 054[HAVING Expression] 055[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } | INTERSECT [DISTINCT] } selectStatement] 056[ORDER BY orderExpression [, ...]] 057[LIMIT <limit> [OFFSET <offset>]]; 058 * */ 059 060 private int columnIndex=0; 061 062 // select <select-statement> from <tables> where <where-statement> 063 public Selects parse(String sql) throws SQLParserException { 064 columnIndex=0; 065 ParserString raw = new ParserString(sql.trim()); 066 Selects selects = new Selects(); 067 Select select = new Select(); 068 069 boolean runAgain=false; 070 do { 071 072 // select 073 if(!raw.forwardIfCurrentAndNoWordNumberAfter("select")) throw new SQLParserException("missing select"); 074 raw.removeSpace(); 075 076 // top 077 if(raw.forwardIfCurrentAndNoWordNumberAfter("top")) { 078 raw.removeSpace(); 079 ValueNumber number = number(raw); 080 if(number==null)throw new SQLParserException("missing top number"); 081 select.setTop(number); 082 raw.removeSpace(); 083 } 084 085 // distinct 086 if(raw.forwardIfCurrentAndNoWordNumberAfter("distinct")) { 087 select.setDistinct(true); 088 raw.removeSpace(); 089 } 090 091 // all 092 if(raw.forwardIfCurrentAndNoWordNumberAfter("all")) { 093 select.setDistinct(false); 094 raw.removeSpace(); 095 } 096 097 // select expression 098 selectExpressions(raw,select); 099 raw.removeSpace(); 100 101 // from 102 if(!raw.forwardIfCurrentAndNoWordNumberAfter("from")) throw new SQLParserException("missing from"); 103 tableList(raw,select); 104 raw.removeSpace(); 105 106 // where 107 if(raw.forwardIfCurrentAndNoWordNumberAfter("where")) whereExpressions(raw,select); 108 raw.removeSpace(); 109 110 // group by 111 if(raw.forwardIfCurrentAndNoWordNumberAfter("group by")) { 112 groupByExpressions(raw,select); 113 raw.removeSpace(); 114 115 // having 116 if(raw.forwardIfCurrentAndNoWordNumberAfter("having")) havingExpressions(raw,select); 117 raw.removeSpace(); 118 } 119 selects.addSelect(select); 120 121 runAgain=false; 122 // union 123 if(raw.forwardIfCurrentAndNoWordNumberAfter("union")) { 124 select = new Select(); 125 raw.removeSpace(); 126 if(raw.forwardIfCurrentAndNoWordNumberAfter("all")){ 127 raw.removeSpace(); 128 select.setUnionDistinct(false); 129 } 130 else if(raw.forwardIfCurrentAndNoWordNumberAfter("distinct")){ 131 raw.removeSpace(); 132 select.setUnionDistinct(true); 133 } 134 else select.setDistinct(true); 135 raw.removeSpace(); 136 runAgain=true; 137 } 138 139 } 140 while(runAgain); 141 142 143 // order by 144 if(raw.forwardIfCurrentAndNoWordNumberAfter("order by")) orderByExpressions(raw,selects); 145 raw.removeSpace(); 146 147 if(raw.forwardIfCurrent(';'))raw.removeSpace(); 148 149 if(!raw.isAfterLast()) throw new SQLParserException("can not read the full sql statement (stop at:"+raw.getCurrent()+")"); 150 return selects; 151 } 152 153 private void orderByExpressions(ParserString raw, Selects selects) throws SQLParserException { 154 Expression exp=null; 155 do { 156 raw.removeSpace(); 157 //print.out(raw.getCurrent()); 158 exp=expression(raw); 159 if(!(exp instanceof Column)) throw new SQLParserException("invalid order by part of query"); 160 Column col=(Column) exp; 161 162 raw.removeSpace(); 163 if(raw.forwardIfCurrent("desc")) col.setDirectionBackward(true); 164 if(raw.forwardIfCurrent("asc")) col.setDirectionBackward(false); 165 selects.addOrderByExpression(col); 166 raw.removeSpace(); 167 } 168 while(raw.forwardIfCurrent(',')); 169 raw.removeSpace(); 170 } 171 172 private void whereExpressions(ParserString raw, Select select) throws SQLParserException { 173 raw.removeSpace(); 174 Expression exp = expression(raw); 175 if(exp==null) throw new SQLParserException("missing where expression"); 176 if(!(exp instanceof Operation)) throw new SQLParserException("invalid where expression ("+Caster.toClassName(exp)+")"); 177 select.setWhereExpression((Operation)exp); 178 raw.removeSpace(); 179 } 180 181 private void havingExpressions(ParserString raw, Select select) throws SQLParserException { 182 raw.removeSpace(); 183 Expression exp = expression(raw); 184 if(exp==null) throw new SQLParserException("missing having expression"); 185 if(!(exp instanceof Operation)) throw new SQLParserException("invalid having expression"); 186 select.setHaving((Operation)exp); 187 raw.removeSpace(); 188 } 189 190 private void groupByExpressions(ParserString raw, Select select) throws SQLParserException { 191 Expression exp=null; 192 do { 193 raw.removeSpace(); 194 //print.out(raw.getCurrent()); 195 exp=expression(raw); 196 if(!(exp instanceof Column)) throw new SQLParserException("invalid group by part of query"); 197 Column col=(Column) exp; 198 199 select.addGroupByExpression(col); 200 raw.removeSpace(); 201 } 202 while(raw.forwardIfCurrent(',')); 203 raw.removeSpace(); 204 } 205 206 private void tableList(ParserString raw, Select select) throws SQLParserException { 207 Column column=null; 208 Expression exp=null; 209 do { 210 raw.removeSpace(); 211 212 exp=column(raw); 213 if(!(exp instanceof Column)) throw new SQLParserException("invalid table definition"); 214 column=(Column) exp; 215 raw.removeSpace(); 216 if(raw.forwardIfCurrent("as ")) { 217 String alias=identifier(raw,new RefBooleanImpl(false)); 218 if(alias==null) throw new SQLParserException("missing alias in select part"); 219 column.setAlias(alias); 220 } 221 else { 222 int start=raw.getPos(); 223 RefBoolean hasBracked = new RefBooleanImpl(false); 224 String alias=identifier(raw,hasBracked);//TODO having usw 225 if(!hasBracked.toBooleanValue()) { 226 if("where".equalsIgnoreCase(alias)) raw.setPos(start); 227 else if("group".equalsIgnoreCase(alias)) raw.setPos(start); 228 else if("having".equalsIgnoreCase(alias)) raw.setPos(start); 229 else if("union".equalsIgnoreCase(alias)) raw.setPos(start); 230 else if("order".equalsIgnoreCase(alias)) raw.setPos(start); 231 else if("limit".equalsIgnoreCase(alias)) raw.setPos(start); 232 else if(alias!=null) column.setAlias(alias); 233 } 234 else { 235 if(alias!=null) column.setAlias(alias); 236 } 237 238 } 239 select.addFromExpression(column); 240 raw.removeSpace(); 241 } 242 while(raw.forwardIfCurrent(',')); 243 244 245 } 246 247 // { (selectStatement) [AS] label | tableName [AS] label} 248 private void selectExpressions(ParserString raw, Select select) throws SQLParserException { 249 Expression exp=null; 250 do { 251 raw.removeSpace(); 252 253 exp=expression(raw); 254 if(exp==null) throw new SQLParserException("missing expression in select part of query"); 255 raw.removeSpace(); 256 if(raw.forwardIfCurrent("as ")) { 257 String alias=identifier(raw,new RefBooleanImpl(false)); 258 if(alias==null) throw new SQLParserException("missing alias in select part"); 259 exp.setAlias(alias); 260 } 261 else { 262 int start=raw.getPos(); 263 RefBoolean hb = new RefBooleanImpl(false); 264 String alias=identifier(raw,hb); 265 if(!hb.toBooleanValue() && "from".equalsIgnoreCase(alias)) raw.setPos(start); 266 else if(alias!=null) exp.setAlias(alias); 267 } 268 select.addSelectExpression(exp); 269 raw.removeSpace(); 270 } 271 while(raw.forwardIfCurrent(',')); 272 273 274 } 275 276 private Expression expression(ParserString raw) throws SQLParserException { 277 return xorOp(raw); 278 } 279 280 private Expression xorOp(ParserString raw) throws SQLParserException { 281 Expression expr = orOp(raw); 282 283 while(raw.forwardIfCurrentAndNoWordNumberAfter("xor")) { 284 raw.removeSpace(); 285 expr=new Operation2(expr, orOp(raw), Operation.OPERATION2_XOR); 286 } 287 return expr; 288 } 289 290 private Expression orOp(ParserString raw) throws SQLParserException { 291 Expression expr = andOp(raw); 292 293 while(raw.forwardIfCurrentAndNoWordNumberAfter("or")) { 294 raw.removeSpace(); 295 expr=new Operation2(expr, andOp(raw),Operation.OPERATION2_OR); 296 } 297 return expr; 298 } 299 300 private Expression andOp(ParserString raw) throws SQLParserException { 301 Expression expr = notOp(raw); 302 303 while(raw.forwardIfCurrentAndNoWordNumberAfter("and")) { 304 raw.removeSpace(); 305 expr=new Operation2(expr, notOp(raw), Operation.OPERATION2_AND); 306 } 307 return expr; 308 } 309 310 private Expression notOp(ParserString raw) throws SQLParserException { 311 // NOT 312 if (raw.forwardIfCurrentAndNoWordNumberAfter("not")) { 313 raw.removeSpace(); 314 return new Operation1(decsionOp(raw),Operation.OPERATION1_NOT); 315 } 316 return decsionOp(raw); 317 } 318 319 private Expression decsionOp(ParserString raw) throws SQLParserException { 320 321 Expression expr = plusMinusOp(raw); 322 boolean hasChanged=false; 323 do { 324 hasChanged=false; 325 326 // value BETWEEN value AND value 327 if (raw.forwardIfCurrent("between ")) { 328 raw.removeSpace(); 329 Expression left = plusMinusOp(raw); 330 raw.removeSpace(); 331 if(!raw.forwardIfCurrent("and "))throw new SQLParserException("invalid operation (between) missing operator and"); 332 raw.removeSpace(); 333 Expression right = plusMinusOp(raw); 334 raw.removeSpace(); 335 expr= new Operation3(expr,left,right, Operation.OPERATION3_BETWEEN); 336 hasChanged=true; 337 } 338 339 // value like value [escape value] 340 else if (raw.forwardIfCurrentAndNoWordNumberAfter("like")) { 341 raw.removeSpace(); 342 Expression left = plusMinusOp(raw); 343 raw.removeSpace(); 344 if(raw.forwardIfCurrentAndNoWordNumberAfter("escape ")){ 345 raw.removeSpace(); 346 Expression right = plusMinusOp(raw); 347 raw.removeSpace(); 348 expr= new Operation3(expr,left,right, Operation.OPERATION3_LIKE); 349 } 350 else { 351 raw.removeSpace(); 352 expr= new Operation2(expr,left, Operation.OPERATION2_LIKE); 353 } 354 hasChanged=true; 355 } 356 357 358 // IS [NOT] NULL 359 else if (raw.isCurrent("is ")) { 360 int start=raw.getPos(); 361 if (raw.forwardIfCurrentAndNoWordNumberAfter("is null")) { 362 raw.removeSpace(); 363 return new Operation1(expr, Operation.OPERATION1_IS_NULL); 364 365 } 366 else if (raw.forwardIfCurrentAndNoWordNumberAfter("is not null")) { 367 raw.removeSpace(); 368 return new Operation1(expr, Operation.OPERATION1_IS_NOT_NULL); 369 370 } 371 else { 372 raw.setPos(start); 373 raw.removeSpace(); 374 } 375 } 376 377 // not in 378 else if (raw.forwardIfCurrent("not in",'(')) { 379 expr=new OperationN("not_in",readArguments(raw,expr)); 380 hasChanged=true; 381 } 382 // in 383 else if (raw.forwardIfCurrent("in",'(')) { 384 expr=new OperationN("in",readArguments(raw,expr)); 385 hasChanged=true; 386 } 387 // not like 388 if (raw.forwardIfCurrentAndNoWordNumberAfter("not like")) { 389 expr = decisionOpCreate(raw,Operation.OPERATION2_NOT_LIKE,expr); 390 hasChanged=true; 391 } 392 /* / like 393 else if (raw.forwardIfCurrentAndNoWordNumberAfter("like")) { 394 expr = decisionOpCreate(raw,Operation.OPERATION2_LIKE,expr); 395 hasChanged=true; 396 }*/ 397 // = 398 else if (raw.forwardIfCurrent('=')) { 399 expr = decisionOpCreate(raw,Operation.OPERATION2_EQ,expr); 400 hasChanged=true; 401 } 402 // != 403 else if (raw.forwardIfCurrent("!=")) { 404 expr = decisionOpCreate(raw,Operation.OPERATION2_NEQ,expr); 405 hasChanged=true; 406 } 407 // <> 408 else if (raw.forwardIfCurrent("<>")) { 409 expr = decisionOpCreate(raw,Operation.OPERATION2_LTGT,expr); 410 hasChanged=true; 411 } 412 // <, <= 413 else if (raw.isCurrent('<')) { 414 if (raw.forwardIfCurrent("<=")) { 415 expr = decisionOpCreate(raw,Operation.OPERATION2_LTE,expr); 416 hasChanged=true; 417 } 418 else { 419 raw.next(); 420 expr = decisionOpCreate(raw,Operation.OPERATION2_LT,expr); 421 hasChanged=true; 422 } 423 } 424 // >, => 425 else if (raw.isCurrent('>')) { 426 if (raw.forwardIfCurrent("=>")) { 427 expr = decisionOpCreate(raw,Operation.OPERATION2_GTE,expr); 428 hasChanged=true; 429 } 430 if (raw.forwardIfCurrent(">=")) { 431 expr = decisionOpCreate(raw,Operation.OPERATION2_GTE,expr); 432 hasChanged=true; 433 } 434 else { 435 raw.next(); 436 expr = decisionOpCreate(raw,Operation.OPERATION2_GT,expr); 437 hasChanged=true; 438 } 439 } 440 }while(hasChanged); 441 return expr; 442 } 443 private Expression decisionOpCreate(ParserString raw,int operation, Expression left) throws SQLParserException { 444 raw.removeSpace(); 445 return new Operation2(left, plusMinusOp(raw), operation); 446 } 447 private Expression plusMinusOp(ParserString raw) throws SQLParserException { 448 Expression expr = modOp(raw); 449 450 while(!raw.isLast()) { 451 452 // Plus Operation 453 if (raw.forwardIfCurrent('+')) { 454 raw.removeSpace(); 455 expr=new Operation2(expr, modOp(raw), Operation.OPERATION2_PLUS); 456 } 457 // Minus Operation 458 else if (raw.forwardIfCurrent('-')) { 459 raw.removeSpace(); 460 expr=new Operation2(expr, modOp(raw), Operation.OPERATION2_MINUS); 461 } 462 else break; 463 } 464 return expr; 465 } 466 467 private Expression modOp(ParserString raw) throws SQLParserException { 468 Expression expr = divMultiOp(raw); 469 470 // Modulus Operation 471 while(raw.forwardIfCurrent('%')) { 472 raw.removeSpace(); 473 expr=new Operation2(expr, divMultiOp(raw), Operation.OPERATION2_MOD); 474 } 475 return expr; 476 } 477 478 private Expression divMultiOp(ParserString raw) throws SQLParserException { 479 Expression expr = expoOp(raw); 480 while (!raw.isLast()) { 481 // Multiply Operation 482 if(raw.forwardIfCurrent('*')) { 483 raw.removeSpace(); 484 expr=new Operation2(expr, expoOp(raw), Operation.OPERATION2_MULTIPLY); 485 } 486 // Divide Operation 487 else if (raw.forwardIfCurrent('/')) { 488 raw.removeSpace(); 489 expr=new Operation2(expr, expoOp(raw), Operation.OPERATION2_DIVIDE); 490 } 491 else { 492 break; 493 } 494 495 } 496 return expr; 497 } 498 499 private Expression expoOp(ParserString raw) throws SQLParserException { 500 Expression exp = negateMinusOp(raw); 501 502 // Modulus Operation 503 while(raw.forwardIfCurrent('^')) { 504 raw.removeSpace(); 505 exp=new Operation2(exp, negateMinusOp(raw),Operation.OPERATION2_EXP); 506 } 507 return exp; 508 } 509 510 private Expression negateMinusOp(ParserString raw) throws SQLParserException { 511 // And Operation 512 if (raw.forwardIfCurrent('-')) { 513 raw.removeSpace(); 514 return new Operation1(clip(raw),Operation.OPERATION1_MINUS); 515 } 516 else if (raw.forwardIfCurrent('+')) { 517 raw.removeSpace(); 518 return new Operation1(clip(raw),Operation.OPERATION1_PLUS); 519 } 520 return clip(raw); 521 } 522 523 // { Expression | COUNT(*) | {COUNT | MIN | MAX | SUM | AVG | SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP} ([ALL | DISTINCT][1]] Expression) } [[AS] label] 524 private Expression clip(ParserString raw) throws SQLParserException { 525 Expression exp=column(raw); 526 //if(exp==null)exp=brackedColumn(raw); 527 if(exp==null)exp=date(raw); 528 if(exp==null)exp=bracked(raw); 529 if(exp==null)exp=number(raw); 530 if(exp==null)exp=string(raw); 531 return exp; 532 } 533 534 private Expression bracked(ParserString raw) throws SQLParserException { 535 if(!raw.forwardIfCurrent('(')) return null; 536 raw.removeSpace(); 537 Expression exp = expression(raw); 538 raw.removeSpace(); 539 if(!raw.forwardIfCurrent(')')) throw new SQLParserException("missing closing )"); 540 raw.removeSpace(); 541 return exp;//new BracketExpression(exp); 542 } 543 544 private Expression column(ParserString raw) throws SQLParserException { 545 RefBoolean hb = new RefBooleanImpl(false); 546 String name = identifier(raw,hb); 547 if(name==null) return null; 548 if(!hb.toBooleanValue()) { 549 if("true".equalsIgnoreCase(name)) return ValueBoolean.TRUE; 550 if("false".equalsIgnoreCase(name)) return ValueBoolean.FALSE; 551 if("null".equalsIgnoreCase(name)) return ValueNull.NULL; 552 } 553 554 ColumnExpression column=new ColumnExpression(name,name.equals("?")?columnIndex++:0); 555 raw.removeSpace(); 556 while(raw.forwardIfCurrent(".")) { 557 raw.removeSpace(); 558 String sub=identifier(raw,hb); 559 if(sub==null) throw new SQLParserException("invalid column definition"); 560 column.setSub(sub); 561 } 562 raw.removeSpace(); 563 if(raw.forwardIfCurrent('(')) { 564 return new OperationN(column.getFullName(),readArguments(raw)); 565 } 566 return column; 567 } 568 569 570 571 572 private List readArguments(ParserString raw) throws SQLParserException { 573 return readArguments(raw,null); 574 } 575 private List readArguments(ParserString raw,Expression exp) throws SQLParserException { 576 List args=new ArrayList(); 577 Expression arg; 578 if(exp!=null)args.add(exp); 579 do { 580 raw.removeSpace(); 581 if(raw.isCurrent(')')) break; 582 583 args.add(arg=expression(raw)); 584 raw.removeSpace(); 585 // check for alias 586 if(raw.forwardIfCurrent("as ")) { 587 raw.removeSpace(); 588 arg.setAlias(identifier(raw,null)); 589 raw.removeSpace(); 590 } 591 592 593 } 594 while(raw.forwardIfCurrent(',')); 595 if(!raw.forwardIfCurrent(')')) throw new SQLParserException("missing closing )"); 596 raw.removeSpace(); 597 return args; 598 } 599 600 private ValueNumber number(ParserString raw) throws SQLParserException { 601 // check first character is a number literal representation 602 if(!(raw.isCurrentBetween('0','9') || raw.isCurrent('.'))) return null; 603 604 StringBuffer rtn=new StringBuffer(); 605 606 // get digit on the left site of the dot 607 if(raw.isCurrent('.')) rtn.append('0'); 608 else rtn.append(digit(raw)); 609 // read dot if exist 610 if(raw.forwardIfCurrent('.')) { 611 rtn.append('.'); 612 String rightSite=digit(raw); 613 if(rightSite.length()> 0 && raw.forwardIfCurrent('e')) { 614 if(raw.isCurrentBetween('0','9')) { 615 rightSite+='e'+digit(raw); 616 } 617 else { 618 raw.previous(); 619 } 620 } 621 // read right side of the dot 622 if(rightSite.length()==0) 623 throw new SQLParserException("Number can't end with [.]"); 624 rtn.append(rightSite); 625 } 626 raw.removeSpace(); 627 return new ValueNumber(rtn.toString()); 628 629 630 } 631 632 private String digit(ParserString raw) { 633 String rtn=""; 634 while (raw.isValidIndex()) { 635 if(!raw.isCurrentBetween('0','9'))break; 636 rtn+=raw.getCurrentLower(); 637 raw.next(); 638 } 639 return rtn; 640 } 641 642 643 private ValueString string(ParserString raw) throws SQLParserException { 644 645 // check starting character for a string literal 646 if(!raw.isCurrent('\'')) 647 return null; 648 649 // Init Parameter 650 StringBuffer str=new StringBuffer(); 651 652 while(raw.hasNext()) { 653 raw.next(); 654 655 // check quoter 656 if(raw.isCurrent('\'')) { 657 // Ecaped sharp 658 if(raw.isNext('\'')){ 659 raw.next(); 660 str.append('\''); 661 } 662 // finsish 663 else { 664 break; 665 } 666 } 667 // all other character 668 else { 669 str.append(raw.getCurrent()); 670 } 671 } 672 if(!raw.forwardIfCurrent('\'')) 673 throw new SQLParserException("Invalid Syntax Closing ['] not found"); 674 675 raw.removeSpace(); 676 return new ValueString(str.toString()); 677 } 678 679private ValueDate date(ParserString raw) throws SQLParserException { 680 681 if(!raw.isCurrent('{')) 682 return null; 683 684 // Init Parameter 685 StringBuilder str=new StringBuilder(); 686 687 while(raw.hasNext()) { 688 raw.next(); 689 if(raw.isCurrent('}'))break; 690 str.append(raw.getCurrent()); 691 692 } 693 if(!raw.forwardIfCurrent('}')) 694 throw new SQLParserException("Invalid Syntax Closing [}] not found"); 695 696 raw.removeSpace(); 697 try { 698 return new ValueDate("{"+str.toString()+"}"); 699 } 700 catch (PageException e) { 701 throw new SQLParserException("can't cast value [{"+str.toString()+"}] to date object"); 702 } 703 } 704 705 private String identifier(ParserString raw,RefBoolean hasBracked) throws SQLParserException { 706 707 if(hasBracked!=null && raw.forwardIfCurrent('[')) { 708 hasBracked.setValue(true); 709 return identifierBracked(raw); 710 } 711 else if(!(raw.isCurrentLetter() || raw.isCurrent('*') || raw.isCurrent('?')|| raw.isCurrent('_'))) return null; 712 713 714 int start = raw.getPos(); 715 do { 716 raw.next(); 717 if(!(raw.isCurrentLetter() || raw.isCurrentBetween('0','9') || raw.isCurrent('*') || raw.isCurrent('?')|| raw.isCurrent('_'))) { 718 break; 719 } 720 } 721 while (raw.isValidIndex()); 722 String str = raw.substring(start,raw.getPos()-start); 723 raw.removeSpace(); 724 725 return str; 726 } 727 private String identifierBracked(ParserString raw) throws SQLParserException { 728 int start = raw.getPos(); 729 do { 730 raw.next(); 731 } 732 while (raw.isValidIndex() && !raw.isCurrent(']')); 733 734 String str = raw.substring(start,raw.getPos()-start); 735 if(!raw.forwardIfCurrent(']')) throw new SQLParserException("missing ending ] of identifier"); 736 return str; 737 } 738 739 740 741 public static void main(String[] args) { 742 743 744 //print.out(new SelectParser().parse("select a, b as c, d e from test limit 3").toString()); 745 746 747 748 //String sql="select cast(susi as integer) as y,a=b as x from source";//WHERE (lft BETWEEN 1 AND 4 AND ID = 111) 749 //print.out(new SelectParser().parse(sql).toString()); 750 751 /* 752 print.out(new SelectParser().parse("select (a and b) s,'abc'<b as c,'abc'+b as c,'abc'%b as c,'abc'*b as c,'susi''s lustige dings', 'sss' as a, 'xxx' b, 1.1,1.2 as c, 1.3 d, e, f as g, h i, a.b.c.d as j, 1^1 as k " + 753 "from test, a as x, b y,a.b.c as y " + 754 " where not t=d and x>y or x=0 xor '1'>'2' ").toString()); 755 */ 756 //print.out("*****************************"); 757 /* 758 print.out(new SelectParser().parse("select a, b as c, d e from test").toString()); 759 print.out(new SelectParser().parse("select c is not null,c is null as x from test").toString()); 760 print.out(new SelectParser().parse("select c between 'a' and 1,c between 'a' and 1 as x,a from test").toString()); 761 print.out(new SelectParser().parse("select c from test where a like b and c=1").toString()); 762 print.out(new SelectParser().parse("select true as x,b in(1,a,'x'), count(c),count(c) as x from test where s not in(1,2,3) and a like b and c=count(c)").toString()); 763 */ 764 //print.out(new SelectParser().parse("select c from test where x=y order by test,a.b desc , a.b.c asc ").toString()); 765 //print.out(new SelectParser().parse("select *, *.lastname from test").toString()); 766 //Selects select = new SelectParser().parse("SELECT * FROM qTest WHERE (ID = 7 AND data_ID = 1) OR (data_ID = 110422)"); 767 //select = new SelectParser().parse("\nSELECT *\nFROM qAllTodos\nWHERE (\n (\n (\n toeditor_ID = ? OR\n workflowtaskseditor_ID = ? OR\n (editorgroup_ID IN (?) AND\n workflowmethod_ID = 1) OR\n (editorgroup_ID IN (?) AND\n workflowmethod_ID = 2 AND status <> 6) OR\n (editorgroup_ID IN (?,?) AND\n workflowmethod_ID = 3) OR\n (editorgroup_ID IN (?) AND\n workflowmethod_ID = 4) OR\n (editorgroup_ID IN (?) AND\n ? <> editor_ID AND\n workflowmethod_ID = 5) OR\n (acceptedbyeditor_ID = ?) OR\n (passedtoeditor_ID = ?)\n ) AND\n status IN (1,2,6) AND\n (acceptedbyeditor_ID = ? OR\n acceptedbyeditor_ID = 0) AND\n\n (site_ID = ? OR\n workflowprocessessite_ID = ? OR\n workflowprocessessite_ID = 0) AND\n\n startdate <= '2007-09-14 17:09:48' AND\n\n istimeouttask = 0\n )\n\n\n OR\n\n (\n\n confirm_needed <> 0 AND\n\n (confirm_editor_ID = ? OR\n\n (confirm_editorgroup_ID IN (?) AND\n confirm_method_ID = 1) OR\n\n (confirm_editorgroup_ID IN (?) AND\n confirm_method_ID = 2) OR\n\n (confirm_editorgroup_ID IN (?,?) AND\n confirm_method_ID = 3) OR\n\n (confirm_editorgroup_ID IN (?) AND\n confirm_method_ID = 4) OR\n\n (confirm_editorgroup_ID IN (?) AND\n donebyeditor_ID <> ? AND\n confirm_method_ID = 5)\n ) AND\n\n confirmedbyeditor_ID = 0 AND\n\n status = 3 AND\n\n (workflowprocessessite_ID = ? OR\n workflowprocessessite_ID = 0)\n )\n\n\n OR\n\n (\n\n istimeouttask <> 0 AND\n\n (timeout_editor_ID = ? OR\n\n (timeout_editorgroup_ID IN (?) AND\n timeout_method_ID = 1) OR\n\n (timeout_editorgroup_ID IN (?) AND\n timeout_method_ID = 2 AND\n status <> 6) OR\n\n (timeout_editorgroup_ID IN (?,?) AND\n timeout_method_ID = 3) OR\n\n (timeout_editorgroup_ID IN (?) AND\n timeout_method_ID = 4) OR\n\n (timeout_editorgroup_ID IN (?) AND\n ? <> deputyeditor_ID AND\n timeout_method_ID = 5) OR\n\n (acceptedbyeditor_ID = ?) OR\n\n (passedtoeditor_ID = ?)\n ) AND\n\n status IN (1,2,6) AND\n\n (acceptedbyeditor_ID = ? OR\n acceptedbyeditor_ID = 0) AND\n\n (workflowprocessessite_ID = ? OR\n workflowprocessessite_ID = 0)\n\n )\n )\n"); 768 //print.out(select.toString()); 769 //print.out(select.getTables().length); 770 //lucee.print.out(new SelectParser().parse("select * from qryData where tableName like '%@_array' or x=1").toString()); 771 //lucee.print.out(new SelectParser().parse("select * from qryData where tableName like '%@_array' escape '@' or x=1").toString()); 772 773 774 775 776 } 777}