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