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}