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