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                            str.append(raw.getCurrent());
673                            
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    }