001    package railo.runtime.db;
002    
003    import java.util.HashMap;
004    import java.util.Iterator;
005    import java.util.Map;
006    
007    import railo.commons.lang.CFTypes;
008    import railo.commons.lang.StringUtil;
009    import railo.commons.math.MathUtil;
010    import railo.runtime.PageContext;
011    import railo.runtime.exp.DatabaseException;
012    import railo.runtime.exp.PageException;
013    import railo.runtime.op.Caster;
014    import railo.runtime.op.Operator;
015    import railo.runtime.sql.Select;
016    import railo.runtime.sql.SelectParser;
017    import railo.runtime.sql.Selects;
018    import railo.runtime.sql.exp.BracketExpression;
019    import railo.runtime.sql.exp.Column;
020    import railo.runtime.sql.exp.Expression;
021    import railo.runtime.sql.exp.op.Operation;
022    import railo.runtime.sql.exp.op.Operation1;
023    import railo.runtime.sql.exp.op.Operation2;
024    import railo.runtime.sql.exp.op.Operation3;
025    import railo.runtime.sql.exp.op.OperationN;
026    import railo.runtime.sql.exp.value.Value;
027    import railo.runtime.sql.exp.value.ValueNumber;
028    import railo.runtime.type.Array;
029    import railo.runtime.type.ArrayImpl;
030    import railo.runtime.type.Collection.Key;
031    import railo.runtime.type.Query;
032    import railo.runtime.type.QueryColumn;
033    import railo.runtime.type.QueryImpl;
034    import railo.runtime.type.util.QueryUtil;
035    
036    /**
037     * 
038     */
039    public final class QoQ {
040    
041    
042    
043    
044    
045            public Query execute(PageContext pc,SQL sql,int maxrows) throws PageException {
046                    try {
047                            SelectParser parser=new SelectParser();
048                            Selects selects = parser.parse(sql.getSQLString());
049    
050                            return execute(pc,sql,selects,maxrows);
051                    }
052                    catch(Throwable t) {
053                        throw Caster.toPageException(t);
054                    }       
055            }
056    
057            /**
058             * execute a SQL Statement against CFML Scopes
059             */
060        public Query execute(PageContext pc,SQL sql, Selects selects,int maxrows) throws PageException {
061            Column[] orders=selects.getOrderbys();
062            Select[] arrSelects = selects.getSelects();
063            
064            
065            QueryImpl target=new QueryImpl(new String[0],0,"query");
066                    target.setSql(sql);
067            
068            for(int i=0;i<arrSelects.length;i++) {
069                    arrSelects[i].getFroms();
070                    Column[] froms = arrSelects[i].getFroms();
071                    if(froms.length>1) throw new DatabaseException("can only work with single tables yet",null,sql,null);
072                    executeSingle(pc,arrSelects[i],getSingleTable(pc, froms[0]),target,arrSelects.length>1?-1:maxrows,sql,
073                                    orders.length>0);
074            }
075            
076            // Order By     
077                    if(orders.length>0) {
078                
079                            for(int i=orders.length-1;i>=0;i--) {
080                                    Column order = orders[i];
081                                    target.sort(order.getColumn().toLowerCase(),order.isDirectionBackward()?Query.ORDER_DESC:Query.ORDER_ASC);
082                            }
083                            if(maxrows>-1) {
084                                target.cutRowsTo(maxrows);
085                            }
086                    }
087        // Distinct
088            if(selects.isDistinct()) {
089                String[] _keys=target.getColumns();
090                QueryColumn[] columns=new QueryColumn[_keys.length];
091                for(int i=0;i<columns.length;i++) {
092                    columns[i]=target.getColumn(_keys[i]);
093                }
094                
095                int i;
096                outer:for(int row=target.getRecordcount();row>1;row--) {
097                    for(i=0;i<columns.length;i++) {
098                        if(!Operator.equals(QueryUtil.getValue(columns[i],row),QueryUtil.getValue(columns[i],row-1),true))
099                            continue outer;
100                    }
101                    target.removeRow(row);
102                }
103            }
104            return target;
105            }
106        
107    
108    
109        private void executeSingle(PageContext pc, Select select, Query qr, QueryImpl target, int maxrows, SQL sql,boolean hasOrders) throws PageException {
110            ValueNumber oTop = select.getTop();
111                    if(oTop!=null) {
112                            int top=(int)oTop.getValueAsDouble();
113                            if(maxrows==-1 || maxrows>top) maxrows=top;
114                    }
115                    
116            
117            int recCount=qr.getRecordcount();
118                    Expression[] expSelects = select.getSelects();
119                    int selCount=expSelects.length;
120    
121                    Map selects=new HashMap();
122                    Iterator<Key> it;
123                    Key k;
124            // headers
125                    for(int i=0;i<selCount;i++) {
126                            Expression expSelect = expSelects[i];
127                            
128                            if(expSelect.getAlias().equals("*")) {
129                                    it = qr.keyIterator();
130                                    while(it.hasNext()){
131                                            k = it.next();
132                                            selects.put(k.getLowerString(),k.getLowerString());
133                                            queryAddColumn(target,k.getLowerString());
134                                    }
135                            }
136                            else {
137                                    String alias=expSelect.getAlias();
138                                    alias=alias.toLowerCase();
139                                    
140                                    selects.put(alias,expSelect);
141                                    queryAddColumn(target,alias);
142                            }
143                    }
144                    String[] headers = (String[])selects.keySet().toArray(new String[selects.size()]);
145                    //QueryImpl rtn=new QueryImpl(headers,0,"query");
146                    //rtn.setSql(sql);
147                    
148            // loop records
149                    //Column[] orders = select.getOrderbys();
150                    Operation where = select.getWhere();
151                    
152                    boolean hasMaxrow=maxrows>-1 && !hasOrders;
153                    
154                    // get target columns
155                    QueryColumn[] trgColumns=new QueryColumn[headers.length];
156                    Object[] trgValues=new Object[headers.length];
157                    for(int cell=0;cell<headers.length;cell++){
158                            trgColumns[cell]=target.getColumn(headers[cell]);
159                            trgValues[cell]=selects.get(headers[cell]);
160                    }
161                    
162                    for(int row=1;row<=recCount;row++) {
163                        sql.setPosition(0);
164                            if(hasMaxrow && maxrows<=target.getRecordcount())break;
165                        boolean useRow=where==null || Caster.toBooleanValue(executeExp(pc,sql,qr, where, row));
166                        if(useRow) {
167                                target.addRow(1);
168                                for(int cell=0;cell<headers.length;cell++){
169                                            //Object value = selects.get(headers[cell]);
170                                            trgColumns[cell].set(target.getRecordcount(), getValue(pc,sql,qr,row,headers[cell],trgValues[cell]));
171                                                    /*target.setAt(
172                                                            headers[cell],
173                                                            target.getRecordcount(),
174                                                            getValue(pc,sql,qr,row,headers[cell],trgValues[cell])
175                                                    );*/
176                                    }
177                            }
178                    }
179    
180            // Group By     
181            if(select.getGroupbys().length>0)
182                    throw new DatabaseException("group by are not supported at the moment",null,sql,null);
183            if(select.getHaving()!=null)
184                    throw new DatabaseException("having is not supported at the moment",null,sql,null);
185                    
186            }
187    
188            private void queryAddColumn(QueryImpl query, String column) throws DatabaseException {
189                    if(!query.containsKey(column)) {
190                            query.addColumn(column, new ArrayImpl());
191                    }
192            }
193    
194            private Array array(String value, int recordcount) {
195                    Array array = new ArrayImpl();
196                    if(recordcount==0) return array;
197                    for(int i=0;i<recordcount;i++) {
198                            array.appendEL(value);
199                    }
200                    return array;
201            }
202    
203            /*private QueryImpl execute2(PageContext pc,SQL sql, Query qr, Select select,Column[] orders,int maxrows) throws PageException {
204                    
205            int recCount=qr.getRecordcount();
206                    Expression[] expSelects = select.getSelects();
207                    int selCount=expSelects.length;
208                    
209                    Map selects=new HashTable();
210                    boolean isSMS=false;
211                    Key[] keys;
212            // headers
213                    for(int i=0;i<selCount;i++) {
214                            Expression expSelect = expSelects[i];
215                            
216                            if(expSelect.getAlias().equals("*")) {
217                                    
218                                    keys = qr.keys();
219                                    for(int y=0;y<keys.length;y++){
220                                            selects.put(keys[y].getLowerString(),keys[y].getLowerString());
221                                    }
222                            }
223                            else {
224                                    String alias=expSelect.getAlias();
225                                    alias=alias.toLowerCase();
226                                    
227                                    selects.put(alias,expSelect);
228                            }
229                    }
230                    String[] headers = (String[])selects.keySet().toArray(new String[selects.size()]);
231                    
232                    QueryImpl rtn=new QueryImpl(headers,0,"query");
233                    rtn.setSql(sql);
234                    
235            // loop records
236                    Operation where = select.getWhere();
237                    
238                    boolean hasMaxrow=maxrows>-1 && (orders==null || orders.length==0);
239                    for(int row=1;row<=recCount;row++) {
240                        sql.setPosition(0);
241                            if(hasMaxrow && maxrows<=rtn.getRecordcount())break;
242                        boolean useRow=where==null || Caster.toBooleanValue(executeExp(pc,sql,qr, where, row));
243                            if(useRow) {
244                                
245                                    rtn.addRow(1);
246                                    for(int cell=0;cell<headers.length;cell++){
247                                            Object value = selects.get(headers[cell]);
248    
249                                                    rtn.setAt(
250                                                            headers[cell],
251                                                            rtn.getRecordcount(),
252                                                            getValue(pc,sql,qr,row,headers[cell],value)
253                                                    );
254                                    }
255                            }
256                    }
257    
258            // Group By     
259            if(select.getGroupbys().length>0)
260                    throw new DatabaseException("group by are not supported at the moment",null,sql);
261            if(select.getHaving()!=null)
262                    throw new DatabaseException("having is not supported at the moment",null,sql);
263                    
264            // Order By     
265                    if(orders.length>0) {
266                
267                            for(int i=orders.length-1;i>=0;i--) {
268                                    Column order = orders[i];
269                                    rtn.sort(order.getColumn().toLowerCase(),order.isDirectionBackward()?Query.ORDER_DESC:Query.ORDER_ASC);
270                            }
271                            if(maxrows>-1) {
272                                rtn.cutRowsTo(maxrows);
273                            }
274                    }
275        // Distinct
276            if(select.isDistinct()) {
277                String[] _keys=rtn.getColumns();
278                QueryColumn[] columns=new QueryColumn[_keys.length];
279                for(int i=0;i<columns.length;i++) {
280                    columns[i]=rtn.getColumn(_keys[i]);
281                }
282                
283                int i;
284                outer:for(int row=rtn.getRecordcount();row>1;row--) {
285                    for(i=0;i<columns.length;i++) {
286                        if(!Operator.equals(columns[i].get(row),columns[i].get(row-1),true))
287                            continue outer;
288                    }
289                    rtn.removeRow(row);
290                }
291            }
292                    return rtn;
293            }*/
294            
295            /**
296             * return value
297             * @param sql
298             * @param querySource
299             * @param row
300             * @param key
301             * @param value
302             * @return value
303             * @throws PageException
304             */
305            private Object getValue(PageContext pc,SQL sql,Query querySource, int row, String key, Object value) throws PageException {
306                    if(value instanceof Expression)return executeExp(pc,sql,querySource, ((Expression)value),row);
307                    return querySource.getAt(key,row);
308            }
309    
310            /**
311             * @param pc Page Context of the Request
312             * @param query ZQLQuery
313             * @return Railo Query
314             * @throws PageException
315             */
316            private Query getSingleTable(PageContext pc, Column table) throws PageException {
317                    return Caster.toQuery(pc.getVariable(table.getFullName()));
318            }
319            
320    
321            /**
322             * Executes a ZEXp
323             * @param sql
324             * @param qr Query Result
325             * @param exp expression to execute
326             * @param row current row of resultset
327             * @return result
328             * @throws PageException
329             */
330            private Object executeExp(PageContext pc,SQL sql,Query qr, Expression exp, int row) throws PageException {
331                    //print.e("name:"+exp.getClass().getName());
332                    if(exp instanceof Value) return ((Value)exp).getValue();//executeConstant(sql,qr, (Value)exp, row);
333                    if(exp instanceof Column) return executeColumn(sql,qr, (Column)exp, row);
334                    if(exp instanceof Operation) return executeOperation(pc,sql,qr, (Operation)exp, row);
335                    if(exp instanceof BracketExpression) return executeBracked(pc,sql,qr, (BracketExpression)exp, row);
336                    throw new DatabaseException("unsupported sql statement ["+exp+"]",null,sql,null);
337            }
338            
339            private Object executeExp(PageContext pc,SQL sql,Query qr, Expression exp, int row, Object columnDefault) throws PageException {
340                    //print.o(exp.getClass().getName());
341                    if(exp instanceof Value) return ((Value)exp).getValue();//executeConstant(sql,qr, (Value)exp, row);
342                    if(exp instanceof Column) return executeColumn(sql,qr, (Column)exp, row,columnDefault);
343                    if(exp instanceof Operation) return executeOperation(pc,sql,qr, (Operation)exp, row);
344                    if(exp instanceof BracketExpression) return executeBracked(pc,sql,qr, (BracketExpression)exp, row);
345                    throw new DatabaseException("unsupported sql statement ["+exp+"]",null,sql,null);
346            }
347    
348            private Object executeOperation(PageContext pc,SQL sql,Query qr, Operation operation, int row) throws PageException {
349                    
350                    if(operation instanceof Operation2) {
351                            Operation2 op2=(Operation2) operation;
352                            
353                            switch(op2.getOperator()){
354                            case Operation.OPERATION2_AND:          return executeAnd(pc,sql,qr,op2,row);
355                            case Operation.OPERATION2_OR:           return executeOr(pc,sql,qr,op2,row);
356                            case Operation.OPERATION2_XOR:          return executeXor(pc,sql,qr,op2,row);
357                            case Operation.OPERATION2_EQ:           return executeEQ(pc,sql,qr,op2,row);
358                            case Operation.OPERATION2_NEQ:          return executeNEQ(pc,sql,qr,op2,row);
359                            case Operation.OPERATION2_LTGT:         return executeNEQ(pc,sql,qr,op2,row);
360                            case Operation.OPERATION2_LT:           return executeLT(pc,sql,qr,op2,row);
361                            case Operation.OPERATION2_LTE:          return executeLTE(pc,sql,qr,op2,row);
362                            case Operation.OPERATION2_GT:           return executeGT(pc,sql,qr,op2,row);
363                            case Operation.OPERATION2_GTE:          return executeGTE(pc,sql,qr,op2,row);
364                            case Operation.OPERATION2_MINUS:        return executeMinus(pc,sql,qr,op2,row);
365                            case Operation.OPERATION2_PLUS:         return executePlus(pc,sql,qr,op2,row);
366                            case Operation.OPERATION2_DIVIDE:       return executeDivide(pc,sql,qr,op2,row);
367                            case Operation.OPERATION2_MULTIPLY:     return executeMultiply(pc,sql,qr,op2,row);
368                            case Operation.OPERATION2_EXP:          return executeExponent(pc,sql,qr,op2,row);
369                            case Operation.OPERATION2_LIKE:         return Caster.toBoolean(executeLike(pc,sql,qr,op2,row));
370                            case Operation.OPERATION2_NOT_LIKE:     return Caster.toBoolean(!executeLike(pc,sql,qr,op2,row));
371                            case Operation.OPERATION2_MOD:          return executeMod(pc,sql,qr,op2,row);
372                            }
373                                    
374                                    
375                    }
376    
377                    if(operation instanceof Operation1) {
378                            Operation1 op1=(Operation1) operation;
379                            int o = op1.getOperator();
380                            
381                            if(o==Operation.OPERATION1_IS_NULL)     {
382                                    Object value = executeExp( pc,sql,qr,op1.getExp(),row,null);
383                                    return Caster.toBoolean(value==null);
384                            }
385                            if(o==Operation.OPERATION1_IS_NOT_NULL) {
386                                    Object value = executeExp( pc,sql,qr,op1.getExp(),row,null);
387                                    return Caster.toBoolean(value!=null);
388                            }
389                            
390                            Object value = executeExp( pc,sql,qr,op1.getExp(),row);
391                            
392                            if(o==Operation.OPERATION1_MINUS)       return Caster.toDouble(-Caster.toDoubleValue(value));
393                            if(o==Operation.OPERATION1_PLUS)        return Caster.toDouble(value);
394                            if(o==Operation.OPERATION1_NOT)         return Caster.toBoolean(!Caster.toBooleanValue(value));
395                            
396                    }
397    
398                    if(operation instanceof Operation3) {
399                            Operation3 op3=(Operation3) operation;
400                            int o = op3.getOperator();
401                            if(o==Operation.OPERATION3_BETWEEN) return executeBetween(pc,sql,qr,op3,row);
402                            if(o==Operation.OPERATION3_LIKE) return executeLike(pc,sql,qr,op3,row);
403                    }
404                    
405                    if(!(operation instanceof OperationN)) throw new DatabaseException("invalid syntax for SQL Statement",null,sql,null);
406                    
407                    OperationN opn=(OperationN) operation;
408                    
409                    String op=StringUtil.toLowerCase(opn.getOperator());
410                    Expression[] operators = opn.getOperants();
411                    
412                    /*if(count==0 && op.equals("?")) {
413                        int pos=sql.getPosition(); 
414                        if(sql.getItems().length<=pos) throw new DatabaseException("invalid syntax for SQL Statement",null,sql);
415                        sql.setPosition(pos+1);
416                        return sql.getItems()[pos].getValueForCF();
417                    }*/
418            // 11111111111111111111111111111111111111111111111111111
419            if(operators.length==1) {
420                Object value = executeExp( pc,sql,qr,operators[0],row);
421                
422                // Functions
423                switch(op.charAt(0)) {
424                case 'a':
425                    if(op.equals("abs"))    return new Double(MathUtil.abs(Caster.toDoubleValue(value)));
426                    if(op.equals("acos"))   return new Double(Math.acos(Caster.toDoubleValue(value)));
427                    if(op.equals("asin"))   return new Double(Math.asin(Caster.toDoubleValue(value)));
428                    if(op.equals("atan"))   return new Double(Math.atan(Caster.toDoubleValue(value)));
429                break;
430                case 'c':
431                    if(op.equals("ceiling"))return new Double(Math.ceil(Caster.toDoubleValue(value)));
432                    if(op.equals("cos"))    return new Double(Math.cos(Caster.toDoubleValue(value)));
433                    if(op.equals("cast"))    return Caster.castTo(pc, CFTypes.toShort(operators[0].getAlias(),true,CFTypes.TYPE_UNKNOW),operators[0].getAlias(), value);
434                break;
435                case 'e':
436                    if(op.equals("exp"))    return new Double(Math.exp(Caster.toDoubleValue(value)));
437                break;
438                case 'f':
439                    if(op.equals("floor"))  return new Double(Math.floor(Caster.toDoubleValue(value)));
440                break;
441                case 'u':
442                    if(op.equals("upper") || op.equals("ucase")) return Caster.toString(value).toUpperCase();
443                break;
444                
445                case 'l':
446                    if(op.equals("lower")|| op.equals("lcase")) return Caster.toString(value).toLowerCase();
447                    if(op.equals("ltrim"))  return StringUtil.ltrim(Caster.toString(value),null);
448                    if(op.equals("length")) return new Double(Caster.toString(value).length());
449                break;
450                case 'r':
451                    if(op.equals("rtrim"))  return StringUtil.rtrim(Caster.toString(value),null);
452                break;
453                case 's':
454                    if(op.equals("sign"))   return new Double(MathUtil.sgn(Caster.toDoubleValue(value)));
455                    if(op.equals("sin"))    return new Double(Math.sin(Caster.toDoubleValue(value)));
456                    if(op.equals("soundex"))return StringUtil.soundex(Caster.toString(value));
457                    if(op.equals("sin"))    return new Double(Math.sqrt(Caster.toDoubleValue(value)));
458                break;
459                case 't':
460                    if(op.equals("tan"))    return new Double(Math.tan(Caster.toDoubleValue(value)));
461                    if(op.equals("trim"))   return Caster.toString(value).trim();
462                break;
463                }
464                
465            }
466            
467            // 22222222222222222222222222222222222222222222222222222
468                    else if(operators.length==2) {
469                            
470                            //if(op.equals("=") || op.equals("in")) return executeEQ(pc,sql,qr,expression,row);
471                            
472                Object left = executeExp(pc,sql,qr,operators[0],row);
473                Object right = executeExp(pc,sql,qr,operators[1],row);
474                
475                            // Functions
476                switch(op.charAt(0)) {
477                case 'a':
478                    if(op.equals("atan2"))
479                        return new Double(Math.atan2(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
480                break;
481                case 'b':
482                    if(op.equals("bitand"))
483                        return new Double(Operator.bitand(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
484                    if(op.equals("bitor"))
485                        return new Double(Operator.bitor(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
486                break;
487                case 'c':
488                    if(op.equals("concat"))
489                        return Caster.toString(left).concat(Caster.toString(right));
490                break;
491                case 'm':
492                    if(op.equals("mod"))
493                        return new Double(Operator.modulus(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
494                break;
495                }
496                    
497                            //throw new DatabaseException("unsopprted sql statement ["+op+"]",null,sql);
498                    }
499            // 3333333333333333333333333333333333333333333333333333333333333333333
500                    
501            if(op.equals("in")) return executeIn(pc,sql,qr,opn,row,false);
502            if(op.equals("not_in")) return executeIn(pc,sql,qr,opn,row,true);
503                    
504            
505            /*
506            
507            addCustomFunction("cot",1);
508            addCustomFunction("degrees",1);
509            addCustomFunction("log",1);
510            addCustomFunction("log10",1);
511    
512            addCustomFunction("pi",0);
513            addCustomFunction("power",2);
514            addCustomFunction("radians",1);
515            addCustomFunction("rand",0);
516            addCustomFunction("round",2);
517            addCustomFunction("roundmagic",1);
518            addCustomFunction("truncate",2);
519            addCustomFunction("ascii",1);
520            addCustomFunction("bit_length",1);
521            addCustomFunction("char",1);
522            addCustomFunction("char_length",1);
523            addCustomFunction("difference",2);
524            addCustomFunction("hextoraw",1);
525            addCustomFunction("insert",4);
526            addCustomFunction("left",2);
527            addCustomFunction("locate",3);
528            addCustomFunction("octet_length",1);
529            addCustomFunction("rawtohex",1);
530            addCustomFunction("repeat",2);
531            addCustomFunction("replace",3);
532            addCustomFunction("right",2);
533            addCustomFunction("space",1);
534            addCustomFunction("substr",3);
535            addCustomFunction("substring",3);
536            addCustomFunction("curdate",0);
537            addCustomFunction("curtime",0);
538            addCustomFunction("datediff",3);
539            addCustomFunction("dayname",1);
540            addCustomFunction("dayofmonth",1);
541            addCustomFunction("dayofweek",1);
542            addCustomFunction("dayofyear",1);
543            addCustomFunction("hour",1);
544            addCustomFunction("minute",1);
545            addCustomFunction("month",1);
546            addCustomFunction("monthname",1);
547            addCustomFunction("now",0);
548            addCustomFunction("quarter",1);
549            addCustomFunction("second",1);
550            addCustomFunction("week",1);
551            addCustomFunction("year",1);
552            addCustomFunction("current_date",1);
553            addCustomFunction("current_time",1);
554            addCustomFunction("current_timestamp",1);
555            addCustomFunction("database",0);
556            addCustomFunction("user",0);
557            addCustomFunction("current_user",0);
558            addCustomFunction("identity",0);
559            addCustomFunction("ifnull",2);
560            addCustomFunction("casewhen",3);
561            addCustomFunction("convert",2);
562            //addCustomFunction("cast",1);
563            addCustomFunction("coalesce",1000);
564            addCustomFunction("nullif",2);
565            addCustomFunction("extract",1);
566            addCustomFunction("position",1);
567            */
568            
569                    //print(expression);
570                    throw new DatabaseException(
571                                    "unsopprted sql statement ("+op+") ",null,sql,null);
572    
573            }
574    
575            /* *
576             * @param expression
577             * /
578            private void print(ZExpression expression) {
579                    print.ln("Operator:"+expression.getOperator().toLowerCase());
580                    int len=expression.nbOperands();
581                    for(int i=0;i<len;i++) {
582                            print.ln("      ["+i+"]=        "+expression.getOperand(i));
583                    }
584            }/*
585            
586            
587    
588            /**
589             * 
590             * execute a and operation
591             * @param qr QueryResult to execute on it
592             * @param expression
593             * @param row row of resultset to execute
594             * @return
595             * @throws PageException
596             */
597            private Object executeAnd(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
598                    //print.out("("+expression.getLeft().toString(true)+" AND "+expression.getRight().toString(true)+")");
599                    boolean rtn=Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getLeft(),row));
600                    if(!rtn) return Boolean.FALSE;
601                    return Caster.toBoolean(executeExp(pc,sql,qr,expression.getRight(),row));
602            }
603            
604    
605            
606            private Object executeBracked(PageContext pc, SQL sql, Query qr, BracketExpression expression, int row) throws PageException {
607                    return executeExp(pc,sql,qr,expression.getExp(),row);
608            }
609            
610            /**
611             * 
612             * execute a and operation
613             * @param sql
614             * @param qr QueryResult to execute on it
615             * @param expression
616             * @param row row of resultset to execute
617             * @return result
618             * @throws PageException
619             */
620            private Object executeOr(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
621                    //print.out("("+expression.getLeft().toString(true)+" OR "+expression.getRight().toString(true)+")");
622                    boolean rtn=Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getLeft(),row));
623                    if(rtn) return Boolean.TRUE;
624                    Boolean rtn2 = Caster.toBoolean(executeExp(pc,sql,qr,expression.getRight(),row));
625                    
626                    //print.out(rtn+ " or "+rtn2);
627                    
628                    return rtn2;
629                    
630            }
631            
632            private Object executeXor(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
633                    return 
634                    Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getLeft(),row)) 
635                    ^ 
636                    Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getRight(),row))?
637                                    Boolean.TRUE:Boolean.FALSE;
638            }
639            
640            
641    
642            
643            /**
644             * 
645             * execute a equal operation
646             * @param sql
647             * @param qr QueryResult to execute on it
648             * @param expression
649             * @param row row of resultset to execute
650             * @return result
651             * @throws PageException
652             */
653            private Object executeEQ(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {        
654                    return (executeCompare(pc,sql,qr, expression, row)==0)?Boolean.TRUE:Boolean.FALSE;
655            }
656    
657            /**
658             * 
659             * execute a not equal operation
660             * @param sql
661             * @param qr QueryResult to execute on it
662             * @param expression
663             * @param row row of resultset to execute
664             * @return result
665             * @throws PageException
666             */
667            private Object executeNEQ(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
668                    return (executeCompare(pc,sql,qr, expression, row)!=0)?Boolean.TRUE:Boolean.FALSE;
669            }
670    
671            /**
672             * 
673             * execute a less than operation
674             * @param sql
675             * @param qr QueryResult to execute on it
676             * @param expression
677             * @param row row of resultset to execute
678             * @return result
679             * @throws PageException
680             */
681            private Object executeLT(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
682                    return (executeCompare(pc,sql,qr, expression, row)<0)?Boolean.TRUE:Boolean.FALSE;
683            }
684    
685            /**
686             * 
687             * execute a less than or equal operation
688             * @param sql
689             * @param qr QueryResult to execute on it
690             * @param expression
691             * @param row row of resultset to execute
692             * @return result
693             * @throws PageException
694             */
695            private Object executeLTE(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
696                    return (executeCompare(pc,sql,qr, expression, row)<=0)?Boolean.TRUE:Boolean.FALSE;
697            }
698    
699            /**
700             * 
701             * execute a greater than operation
702             * @param sql
703             * @param qr QueryResult to execute on it
704             * @param expression
705             * @param row row of resultset to execute
706             * @return result
707             * @throws PageException
708             */
709            private Object executeGT(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
710                    return (executeCompare(pc,sql,qr, expression, row)>0)?Boolean.TRUE:Boolean.FALSE;
711            }
712    
713            /**
714             * 
715             * execute a greater than or equal operation
716             * @param sql
717             * @param qr QueryResult to execute on it
718             * @param expression
719             * @param row row of resultset to execute
720             * @return result
721             * @throws PageException
722             */
723            private Object executeGTE(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
724                    return (executeCompare(pc,sql,qr, expression, row)>=0)?Boolean.TRUE:Boolean.FALSE;
725            }
726            
727            /**
728             * 
729             * execute a equal operation
730             * @param sql
731             * @param qr QueryResult to execute on it
732             * @param expression
733             * @param row row of resultset to execute
734             * @return result
735             * @throws PageException
736             */
737            private int executeCompare(PageContext pc,SQL sql,Query qr, Operation2 op, int row) throws PageException {
738                    //print.e(op.getLeft().getClass().getName());
739                    return 
740                            Operator.compare(executeExp(pc,sql,qr,op.getLeft(),row),executeExp(pc,sql,qr,op.getRight(),row));
741            }
742    
743            
744            
745            private Object executeMod(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
746                    
747                    return Caster.toDouble(
748                                    Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getLeft(),row))%
749                                    Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getRight(),row)));
750            }
751    
752            /**
753             * 
754             * execute a greater than or equal operation
755             * @param sql
756             * @param qr QueryResult to execute on it
757             * @param expression
758             * @param row row of resultset to execute
759             * @return result
760             * @throws PageException
761             */
762            private Boolean executeIn(PageContext pc,SQL sql,Query qr, OperationN expression, int row, boolean isNot) throws PageException {
763                    Expression[] operators = expression.getOperants();
764                    Object left=executeExp(pc,sql,qr,operators[0],row);
765                    
766                    for(int i=1;i<operators.length;i++) {
767                            if(Operator.compare(left,executeExp(pc,sql,qr,operators[i],row))==0) 
768                                    return isNot?Boolean.FALSE:Boolean.TRUE;
769                    }
770                    return isNot?Boolean.TRUE:Boolean.FALSE;
771            }
772            
773            /**
774             * 
775             * execute a minus operation
776             * @param sql
777             * @param qr QueryResult to execute on it
778             * @param expression
779             * @param row row of resultset to execute
780             * @return result
781             * @throws PageException
782             */
783            private Object executeMinus(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
784                    return 
785                    new Double(
786                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getLeft(),row))
787                            -
788                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getRight(),row))
789                    );
790            }
791            
792            /**
793             * 
794             * execute a divide operation
795             * @param sql
796             * @param qr QueryResult to execute on it
797             * @param expression
798             * @param row row of resultset to execute
799             * @return result
800             * @throws PageException
801             */
802            private Object executeDivide(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
803                    return 
804                    new Double(
805                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getLeft(),row))
806                            /
807                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getRight(),row))
808                    );
809            }
810            
811            /**
812             * 
813             * execute a multiply operation
814             * @param sql
815             * @param qr QueryResult to execute on it
816             * @param expression
817             * @param row row of resultset to execute
818             * @return result
819             * @throws PageException
820             */
821            private Object executeMultiply(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
822                    return 
823                    new Double(
824                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getLeft(),row))
825                            *
826                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getRight(),row))
827                    );
828            }
829            
830            /**
831             * 
832             * execute a multiply operation
833             * @param sql
834             * @param qr QueryResult to execute on it
835             * @param expression
836             * @param row row of resultset to execute
837             * @return result 
838             * @throws PageException
839             */
840            private Object executeExponent(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
841                    return 
842                    Integer.valueOf(
843                            Caster.toIntValue(executeExp(pc,sql,qr,expression.getLeft(),row))
844                            ^
845                            Caster.toIntValue(executeExp(pc,sql,qr,expression.getRight(),row))
846                    );
847            }
848            
849            /**
850             * 
851             * execute a plus operation
852             * @param sql
853             * @param qr QueryResult to execute on it
854             * @param expression
855             * @param row row of resultset to execute
856             * @return result
857             * @throws PageException
858             */
859            private Object executePlus(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
860                    Object left=executeExp(pc,sql,qr,expression.getLeft(),row);
861                    Object right=executeExp(pc,sql,qr,expression.getRight(),row);
862                    
863                    try {
864                            return new Double(Caster.toDoubleValue(left)+Caster.toDoubleValue(right));
865                    } catch (PageException e) {
866                            return Caster.toString(left)+Caster.toString(right);
867                    } 
868            }
869            
870            /**
871             * 
872             * execute a between operation
873             * @param sql
874             * @param qr QueryResult to execute on it
875             * @param expression
876             * @param row row of resultset to execute
877             * @return result
878             * @throws PageException
879             */
880            private Object executeBetween(PageContext pc,SQL sql,Query qr, Operation3 expression, int row) throws PageException {
881                    Object left=executeExp(pc,sql,qr,expression.getExp(),row);
882                    Object right1=executeExp(pc,sql,qr,expression.getLeft(),row);
883                    Object right2=executeExp(pc,sql,qr,expression.getRight(),row);
884                    //print.out(left+" between "+right1+" and "+right2
885                    //              +" = "+((Operator.compare(left,right1)>=0)+" && "+(Operator.compare(left,right2)<=0)));
886                    
887                    return (
888                            (Operator.compare(left,right1)>=0)
889                            &&
890                            (Operator.compare(left,right2)<=0)
891                    )?Boolean.TRUE:Boolean.FALSE;
892            }
893    
894            private Object executeLike(PageContext pc,SQL sql,Query qr, Operation3 expression, int row) throws PageException {
895                    return LikeCompare.like(sql,
896                                    Caster.toString(executeExp(pc,sql,qr,expression.getExp(),row)),
897                                    Caster.toString(executeExp(pc,sql,qr,expression.getLeft(),row)),
898                                    Caster.toString(executeExp(pc,sql,qr,expression.getRight(),row)))?Boolean.TRUE:Boolean.FALSE;
899            }
900            
901            private boolean executeLike(PageContext pc,SQL sql,Query qr, Operation2 expression, int row) throws PageException {
902                    return LikeCompare.like(sql,
903                                    Caster.toString(executeExp(pc,sql,qr,expression.getLeft(),row)),
904                                    Caster.toString(executeExp(pc,sql,qr,expression.getRight(),row)));
905            }
906    
907            /**
908             * Executes a constant value
909             * @param sql 
910             * @param qr
911             * @param constant
912             * @param row
913             * @return result
914             * @throws PageException
915             */
916            private Object executeColumn(SQL sql,Query qr, Column column, int row) throws PageException {
917                    if(column.getColumn().equals("?")) {
918                        int pos=column.getColumnIndex();
919                        if(sql.getItems().length<=pos) throw new DatabaseException("invalid syntax for SQL Statement",null,sql,null);
920                        return sql.getItems()[pos].getValueForCF();
921                    }
922                    return column.getValue(qr, row);
923                    //return qr.getAt(column.getColumn(),row);      
924            }
925            
926            private Object executeColumn(SQL sql,Query qr, Column column, int row, Object defaultValue) throws PageException {
927                if(column.getColumn().equals("?")) {
928                        int pos=column.getColumnIndex();
929                        if(sql.getItems().length<=pos) throw new DatabaseException("invalid syntax for SQL Statement",null,sql,null);
930                        return sql.getItems()[pos].getValueForCF();
931                    }
932                    return column.getValue(qr, row,defaultValue);   
933            }
934    }