001    package railo.runtime.db;
002    
003    import java.io.ByteArrayInputStream;
004    import java.util.Map;
005    import java.util.Vector;
006    
007    import railo.commons.collections.HashTable;
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.old.ZConstant;
016    import railo.runtime.sql.old.ZExp;
017    import railo.runtime.sql.old.ZExpression;
018    import railo.runtime.sql.old.ZFromItem;
019    import railo.runtime.sql.old.ZOrderBy;
020    import railo.runtime.sql.old.ZQuery;
021    import railo.runtime.sql.old.ZSelectItem;
022    import railo.runtime.sql.old.ZqlParser;
023    import railo.runtime.type.List;
024    import railo.runtime.type.Query;
025    import railo.runtime.type.QueryColumn;
026    import railo.runtime.type.QueryImpl;
027    
028    /**
029     * 
030     */
031    public final class Executer {
032            
033    
034            /**
035             * execute a SQL Statement against CFML Scopes
036             * @param pc PageContext of the Request
037             * @param sql
038             * @param maxrows
039             * @return result
040             * @throws PageException
041             */
042            public Query execute(Vector statements, PageContext pc,SQL sql, int maxrows) throws PageException {
043                    // parse sql
044                    if(statements.size()!=1) throw new DatabaseException("only one SQL Statement allowed at time",null,null,null);
045                    ZQuery query=(ZQuery) statements.get(0);
046                    
047                    // single table
048                    if(query.getFrom().size()==1) {
049                            return testExecute(pc,sql,getSingleTable(pc, query),query,maxrows);
050                            
051                    }       
052                    // multiple table
053                    throw new DatabaseException("can only work with single tables yet",null,null,null);
054                            
055            }
056            
057            public Query execute(PageContext pc,SQL sql,String prettySQL, int maxrows) throws PageException {
058                    if(StringUtil.isEmpty(prettySQL))prettySQL=SQLPrettyfier.prettyfie(sql.getSQLString());
059                    
060                    ZqlParser parser = new ZqlParser(new ByteArrayInputStream(prettySQL.getBytes()));
061                    Vector statements;
062                    try {
063                        statements=parser.readStatements();
064                    }
065                    catch(Throwable t) {
066                        throw Caster.toPageException(t);
067                    }
068                    return execute(statements,pc, sql, maxrows);
069                    
070            }
071    
072        private Query testExecute(PageContext pc,SQL sql, Query qr, ZQuery query, int maxrows) throws PageException {
073                    
074            int recCount=qr.getRecordcount();
075                    Vector vSelects=query.getSelect();
076                    int selCount=vSelects.size();
077                    
078                    Map selects=new HashTable();
079                    boolean isSMS=false;
080            // headers
081                    for(int i=0;i<selCount;i++) {
082                            ZSelectItem select=(ZSelectItem) vSelects.get(i);
083    
084                            if(select.isWildcard() || (isSMS=select.getColumn().equals(SQLPrettyfier.PLACEHOLDER_ASTERIX))) {
085                                    
086                                    if(!isSMS && !select.getColumn().equals("*"))
087                                            throw new DatabaseException("can't execute this type of query at the moment",null,sql,null);
088                                    String[] keys = qr.keysAsString();
089                                    for(int y=0;y<keys.length;y++){
090                                            selects.put(keys[y],keys[y]);
091                                    }
092                                    isSMS=false;
093                            }
094                            else {
095                                    //if(SQLPrettyfier.PLACEHOLDER_COUNT.equals(select.getAlias())) select.setAlias("count");
096                                    //if(SQLPrettyfier.PLACEHOLDER_COUNT.equals(select.getColumn())) select.setExpression(new ZConstant("count",ZConstant.COLUMNNAME));
097                                    
098                                    String alias=select.getAlias();
099                                    String column=select.getColumn();
100                                    if(alias==null)alias=column;
101                                    alias=alias.toLowerCase();
102                                    
103                                    selects.put(alias,select);
104                            }
105                    }
106                    String[] headers = (String[])selects.keySet().toArray(new String[selects.size()]);
107                    
108                    // aHeaders.toArray(new String[aHeaders.size()]);
109                    QueryImpl rtn=new QueryImpl(headers,0,"query");
110                    rtn.setSql(sql);
111                    
112            // loop records
113                    Vector orders = query.getOrderBy();
114                    ZExp where = query.getWhere();
115                    //print.out(headers);
116                    // int newRecCount=0;
117                    boolean hasMaxrow=maxrows>-1 && (orders==null || orders.size()==0);
118                    for(int row=1;row<=recCount;row++) {
119                        sql.setPosition(0);
120                            if(hasMaxrow && maxrows<=rtn.getRecordcount())break;
121                        boolean useRow=where==null || Caster.toBooleanValue(executeExp(pc,sql,qr, where, row));
122                            if(useRow) {
123                                
124                                    rtn.addRow(1);
125                                    for(int cell=0;cell<headers.length;cell++){
126                                            Object value = selects.get(headers[cell]);
127    
128                                                    rtn.setAt(
129                                                            headers[cell],
130                                                            rtn.getRecordcount(),
131                                                            getValue(pc,sql,qr,row,headers[cell],value)
132                                                            //executeExp(qr, selects[cell].getExpression(),row)
133                                                    );
134                                    }
135                            }
136                    }
137    
138            // Group By     
139            if(query.getGroupBy()!=null)
140                    throw new DatabaseException("group by are not supported at the moment",null,sql,null);
141            
142            // Order By     
143                    if(orders!=null && orders.size()>0) {
144                
145                            int len=orders.size();
146                            for(int i=len-1;i>=0;i--) {
147                                    ZOrderBy order=(ZOrderBy) orders.get(i);
148                                    ZConstant name=(ZConstant)order.getExpression();
149                                    rtn.sort(name.getValue().toLowerCase(),order.getAscOrder()?Query.ORDER_ASC:Query.ORDER_DESC);
150                            }
151                            if(maxrows>-1) {
152                                rtn.cutRowsTo(maxrows);
153                            }
154                    }
155        // Distinct
156            if(query.isDistinct()) {
157                String[] keys=rtn.getColumns();
158                QueryColumn[] columns=new QueryColumn[keys.length];
159                for(int i=0;i<columns.length;i++) {
160                    columns[i]=rtn.getColumn(keys[i]);
161                }
162                
163                int i;
164                outer:for(int row=rtn.getRecordcount();row>1;row--) {
165                    for(i=0;i<columns.length;i++) {
166                        if(!Operator.equals(columns[i].get(row),columns[i].get(row-1),true))
167                            continue outer;
168                    }
169                    rtn.removeRow(row);
170                }
171                
172            }
173                    // UNION // TODO support it
174            ZExpression set = query.getSet();
175                    if(set!=null){
176                            ZExp op = set.getOperand(0);
177                            if(op instanceof ZQuery) throw new DatabaseException("union is not supported at the moment",null,sql,null);
178                            //getInvokedTables((ZQuery)op, tablesNames);
179                    }
180                    
181                    return rtn;
182            }
183            
184            /**
185             * return value
186             * @param sql
187             * @param querySource
188             * @param row
189             * @param key
190             * @param value
191             * @return value
192             * @throws PageException
193             */
194            private Object getValue(PageContext pc,SQL sql,Query querySource, int row, String key, Object value) throws PageException {
195                    if(value instanceof ZSelectItem)return executeExp(pc,sql,querySource, ((ZSelectItem)value).getExpression(),row);
196                    return querySource.getAt(key,row);
197            }
198    
199            /**
200             * @param pc Page Context of the Request
201             * @param query ZQLQuery
202             * @return Railo Query
203             * @throws PageException
204             */
205            private Query getSingleTable(PageContext pc, ZQuery query) throws PageException {
206                    return Caster.toQuery(pc.getVariable(((ZFromItem)query.getFrom().get(0)).getFullName()));
207            }
208            
209    
210            /**
211             * Executes a ZEXp
212             * @param sql
213             * @param qr Query Result
214             * @param exp expression to execute
215             * @param row current row of resultset
216             * @return result
217             * @throws PageException
218             */
219            private Object executeExp(PageContext pc,SQL sql,Query qr, ZExp exp, int row) throws PageException {
220                    if(exp instanceof ZConstant) return executeConstant(sql,qr, (ZConstant)exp, row);
221                    else if(exp instanceof ZExpression)return executeExpression(pc,sql,qr, (ZExpression)exp, row);
222                    throw new DatabaseException("unsupported sql statement ["+exp+"]",null,sql,null);
223                    
224            }
225    
226    
227            /**
228             * Executes a Expression
229             * @param sql
230             * @param qr
231             * @param expression
232             * @param row
233             * @return result
234             * @throws PageException
235             */
236            private Object executeExpression(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
237                    String op=StringUtil.toLowerCase(expression.getOperator());
238                    int count=expression.nbOperands();
239                    
240                    if(op.equals("and")) return executeAnd(pc,sql,qr,expression,row);
241                    else if(op.equals("or")) return executeOr(pc,sql,qr,expression,row);
242                    if(count==0 && op.equals("?")) {
243                        int pos=sql.getPosition(); 
244                        if(sql.getItems().length<=pos) throw new DatabaseException("invalid syntax for SQL Statment",null,sql,null);
245                        sql.setPosition(pos+1);
246                        return sql.getItems()[pos].getValueForCF();
247                    }
248            // 11111111111111111111111111111111111111111111111111111
249            else if(count==1) {
250                Object value = executeExp( pc,sql,qr,expression.getOperand(0),row);
251                
252                // Functions
253                switch(op.charAt(0)) {
254                case 'a':
255                    if(op.equals("abs"))    return new Double(MathUtil.abs(Caster.toDoubleValue(value)));
256                    if(op.equals("acos"))   return new Double(Math.acos(Caster.toDoubleValue(value)));
257                    if(op.equals("asin"))   return new Double(Math.asin(Caster.toDoubleValue(value)));
258                    if(op.equals("atan"))   return new Double(Math.atan(Caster.toDoubleValue(value)));
259                break;
260                case 'c':
261                    if(op.equals("ceiling"))return new Double(Math.ceil(Caster.toDoubleValue(value)));
262                    if(op.equals("cos"))    return new Double(Math.cos(Caster.toDoubleValue(value)));
263                break;
264                case 'e':
265                    if(op.equals("exp"))    return new Double(Math.exp(Caster.toDoubleValue(value)));
266                break;
267                case 'f':
268                    if(op.equals("floor"))  return new Double(Math.floor(Caster.toDoubleValue(value)));
269                break;
270                case 'i':
271                    if(op.equals("is not null"))  return Boolean.valueOf(value!=null);
272                    if(op.equals("is null"))  return Boolean.valueOf(value==null);
273                break;
274                case 'u':
275                    if(op.equals("upper") || op.equals("ucase")) return Caster.toString(value).toUpperCase();
276                break;
277                
278                case 'l':
279                    if(op.equals("lower")|| op.equals("lcase")) return Caster.toString(value).toLowerCase();
280                    if(op.equals("ltrim"))  return StringUtil.ltrim(Caster.toString(value),null);
281                    if(op.equals("length")) return new Double(Caster.toString(value).length());
282                break;
283                case 'r':
284                    if(op.equals("rtrim"))  return StringUtil.rtrim(Caster.toString(value),null);
285                break;
286                case 's':
287                    if(op.equals("sign"))   return new Double(MathUtil.sgn(Caster.toDoubleValue(value)));
288                    if(op.equals("sin"))    return new Double(Math.sin(Caster.toDoubleValue(value)));
289                    if(op.equals("soundex"))return StringUtil.soundex(Caster.toString(value));
290                    if(op.equals("sin"))    return new Double(Math.sqrt(Caster.toDoubleValue(value)));
291                break;
292                case 't':
293                    if(op.equals("tan"))    return new Double(Math.tan(Caster.toDoubleValue(value)));
294                    if(op.equals("trim"))   return Caster.toString(value).trim();
295                break;
296                }
297                
298            }
299            
300            // 22222222222222222222222222222222222222222222222222222
301                    else if(count==2) {
302                            
303                            if(op.equals("=") || op.equals("in")) return executeEQ(pc,sql,qr,expression,row);
304                            else if(op.equals("!=") || op.equals("<>")) return executeNEQ(pc,sql,qr,expression,row);
305                            else if(op.equals("<")) return executeLT(pc,sql,qr,expression,row);
306                            else if(op.equals("<=")) return executeLTE(pc,sql,qr,expression,row);
307                            else if(op.equals(">")) return executeGT(pc,sql,qr,expression,row);
308                            else if(op.equals(">=")) return executeGTE(pc,sql,qr,expression,row);
309                            else if(op.equals("-")) return executeMinus(pc,sql,qr,expression,row);
310                            else if(op.equals("+")) return executePlus(pc,sql,qr,expression,row);
311                            else if(op.equals("/")) return executeDivide(pc,sql,qr,expression,row);
312                            else if(op.equals("*")) return executeMultiply(pc,sql,qr,expression,row);
313                            else if(op.equals("^")) return executeExponent(pc,sql,qr,expression,row);
314                
315                Object left = executeExp(pc,sql,qr,expression.getOperand(0),row);
316                Object right = executeExp(pc,sql,qr,expression.getOperand(1),row);
317                
318                            // Functions
319                switch(op.charAt(0)) {
320                case 'a':
321                    if(op.equals("atan2"))
322                        return new Double(Math.atan2(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
323                break;
324                case 'b':
325                    if(op.equals("bitand"))
326                        return new Double(Operator.bitand(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
327                    if(op.equals("bitor"))
328                        return new Double(Operator.bitor(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
329                break;
330                case 'c':
331                    if(op.equals("concat"))
332                        return Caster.toString(left).concat(Caster.toString(right));
333                break;
334                case 'l':
335                    if(op.equals("like"))
336                            return executeLike(pc,sql,qr,expression,row);
337                break;
338                case 'm':
339                    if(op.equals("mod"))
340                        return new Double(Operator.modulus(Caster.toDoubleValue(left),Caster.toDoubleValue(right)));
341                break;
342                }
343                    
344                            throw new DatabaseException("unsopprted sql statement ["+op+"]",null,sql,null);
345                    }
346            // 3333333333333333333333333333333333333333333333333333333333333333333
347                    else if(count==3) {
348                        if(op.equals("between")) return executeBetween(pc,sql,qr,expression,row);
349                    }
350            
351                    if(op.equals("in")) return executeIn(pc,sql,qr,expression,row);
352                    
353            
354            /*
355            
356            addCustomFunction("cot",1);
357            addCustomFunction("degrees",1);
358            addCustomFunction("log",1);
359            addCustomFunction("log10",1);
360    
361            addCustomFunction("pi",0);
362            addCustomFunction("power",2);
363            addCustomFunction("radians",1);
364            addCustomFunction("rand",0);
365            addCustomFunction("round",2);
366            addCustomFunction("roundmagic",1);
367            addCustomFunction("truncate",2);
368            addCustomFunction("ascii",1);
369            addCustomFunction("bit_length",1);
370            addCustomFunction("char",1);
371            addCustomFunction("char_length",1);
372            addCustomFunction("difference",2);
373            addCustomFunction("hextoraw",1);
374            addCustomFunction("insert",4);
375            addCustomFunction("left",2);
376            addCustomFunction("locate",3);
377            addCustomFunction("octet_length",1);
378            addCustomFunction("rawtohex",1);
379            addCustomFunction("repeat",2);
380            addCustomFunction("replace",3);
381            addCustomFunction("right",2);
382            addCustomFunction("space",1);
383            addCustomFunction("substr",3);
384            addCustomFunction("substring",3);
385            addCustomFunction("curdate",0);
386            addCustomFunction("curtime",0);
387            addCustomFunction("datediff",3);
388            addCustomFunction("dayname",1);
389            addCustomFunction("dayofmonth",1);
390            addCustomFunction("dayofweek",1);
391            addCustomFunction("dayofyear",1);
392            addCustomFunction("hour",1);
393            addCustomFunction("minute",1);
394            addCustomFunction("month",1);
395            addCustomFunction("monthname",1);
396            addCustomFunction("now",0);
397            addCustomFunction("quarter",1);
398            addCustomFunction("second",1);
399            addCustomFunction("week",1);
400            addCustomFunction("year",1);
401            addCustomFunction("current_date",1);
402            addCustomFunction("current_time",1);
403            addCustomFunction("current_timestamp",1);
404            addCustomFunction("database",0);
405            addCustomFunction("user",0);
406            addCustomFunction("current_user",0);
407            addCustomFunction("identity",0);
408            addCustomFunction("ifnull",2);
409            addCustomFunction("casewhen",3);
410            addCustomFunction("convert",2);
411            //addCustomFunction("cast",1);
412            addCustomFunction("coalesce",1000);
413            addCustomFunction("nullif",2);
414            addCustomFunction("extract",1);
415            addCustomFunction("position",1);
416            */
417            
418                    
419                    //print(expression);
420                    throw new DatabaseException(
421                                    "unsopprted sql statement (op-count:"+expression.nbOperands()+";operator:"+op+") ",null,sql,null);
422    
423            }
424    
425    
426            /* *
427             * @param expression
428             * /
429            private void print(ZExpression expression) {
430                    print.ln("Operator:"+expression.getOperator().toLowerCase());
431                    int len=expression.nbOperands();
432                    for(int i=0;i<len;i++) {
433                            print.ln("      ["+i+"]=        "+expression.getOperand(i));
434                    }
435            }/*
436    
437            /**
438             * 
439             * execute a and operation
440             * @param qr QueryResult to execute on it
441             * @param expression
442             * @param row row of resultset to execute
443             * @return
444             * @throws PageException
445             */
446            private Object executeAnd(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
447                    int len=expression.nbOperands();
448                    
449                    //boolean rtn=Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getOperand(0),row));
450                    for(int i=0;i<len;i++) {
451                            //if(!rtn)break;
452                            //rtn=rtn && Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getOperand(i),row));
453                            if(!Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getOperand(i),row))) return Boolean.FALSE;
454                    }
455                    return Boolean.TRUE;
456            }
457            
458            /**
459             * 
460             * execute a and operation
461             * @param sql
462             * @param qr QueryResult to execute on it
463             * @param expression
464             * @param row row of resultset to execute
465             * @return result
466             * @throws PageException
467             */
468            private Object executeOr(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
469                    int len=expression.nbOperands();
470                    
471                    //boolean rtn=Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getOperand(0),row));
472                    for(int i=0;i<len;i++) {
473                            if(Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getOperand(i),row))) return Boolean.TRUE;
474                            //if(rtn)break;
475                            //rtn=rtn || Caster.toBooleanValue(executeExp(pc,sql,qr,expression.getOperand(i),row));
476                    }
477                    return Boolean.FALSE;
478            }
479            
480            /**
481             * 
482             * execute a equal operation
483             * @param sql
484             * @param qr QueryResult to execute on it
485             * @param expression
486             * @param row row of resultset to execute
487             * @return result
488             * @throws PageException
489             */
490            private Object executeEQ(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {       
491                    return (executeCompare(pc,sql,qr, expression, row)==0)?Boolean.TRUE:Boolean.FALSE;
492            }
493    
494            /**
495             * 
496             * execute a not equal operation
497             * @param sql
498             * @param qr QueryResult to execute on it
499             * @param expression
500             * @param row row of resultset to execute
501             * @return result
502             * @throws PageException
503             */
504            private Object executeNEQ(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
505                    return (executeCompare(pc,sql,qr, expression, row)!=0)?Boolean.TRUE:Boolean.FALSE;
506            }
507    
508            /**
509             * 
510             * execute a less than operation
511             * @param sql
512             * @param qr QueryResult to execute on it
513             * @param expression
514             * @param row row of resultset to execute
515             * @return result
516             * @throws PageException
517             */
518            private Object executeLT(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
519                    return (executeCompare(pc,sql,qr, expression, row)<0)?Boolean.TRUE:Boolean.FALSE;
520            }
521    
522            /**
523             * 
524             * execute a less than or equal operation
525             * @param sql
526             * @param qr QueryResult to execute on it
527             * @param expression
528             * @param row row of resultset to execute
529             * @return result
530             * @throws PageException
531             */
532            private Object executeLTE(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
533                    return (executeCompare(pc,sql,qr, expression, row)<=0)?Boolean.TRUE:Boolean.FALSE;
534            }
535    
536            /**
537             * 
538             * execute a greater than operation
539             * @param sql
540             * @param qr QueryResult to execute on it
541             * @param expression
542             * @param row row of resultset to execute
543             * @return result
544             * @throws PageException
545             */
546            private Object executeGT(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
547                    return (executeCompare(pc,sql,qr, expression, row)>0)?Boolean.TRUE:Boolean.FALSE;
548            }
549    
550            /**
551             * 
552             * execute a greater than or equal operation
553             * @param sql
554             * @param qr QueryResult to execute on it
555             * @param expression
556             * @param row row of resultset to execute
557             * @return result
558             * @throws PageException
559             */
560            private Object executeGTE(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
561                    return (executeCompare(pc,sql,qr, expression, row)>=0)?Boolean.TRUE:Boolean.FALSE;
562            }
563            
564            /**
565             * 
566             * execute a equal operation
567             * @param sql
568             * @param qr QueryResult to execute on it
569             * @param expression
570             * @param row row of resultset to execute
571             * @return result
572             * @throws PageException
573             */
574            private int executeCompare(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
575                    return 
576                            Operator.compare(
577                                    executeExp(pc,sql,qr,expression.getOperand(0),row)
578                                    ,
579                                    executeExp(pc,sql,qr,expression.getOperand(1),row)
580                            );
581            }
582    
583            private Object executeLike(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
584                    return Caster.toBoolean(like(sql,
585                                    Caster.toString(executeExp(pc,sql,qr,expression.getOperand(0),row)),
586                                    Caster.toString(executeExp(pc,sql,qr,expression.getOperand(1),row))));
587            }
588            
589            private boolean like(SQL sql,String haystack, String needle) throws PageException {
590                    return LikeCompare.like(sql,haystack, needle);
591            }
592    
593            /**
594             * 
595             * execute a greater than or equal operation
596             * @param sql
597             * @param qr QueryResult to execute on it
598             * @param expression
599             * @param row row of resultset to execute
600             * @return result
601             * @throws PageException
602             */
603            private Object executeIn(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
604                    int len=expression.nbOperands();
605                    Object left=executeExp(pc,sql,qr,expression.getOperand(0),row);
606                    
607                    for(int i=1;i<len;i++) {
608                            if(Operator.compare(left,executeExp(pc,sql,qr,expression.getOperand(i),row))==0) 
609                                    return Boolean.TRUE;
610                    }
611                    return Boolean.FALSE;
612            }
613            
614            /**
615             * 
616             * execute a minus operation
617             * @param sql
618             * @param qr QueryResult to execute on it
619             * @param expression
620             * @param row row of resultset to execute
621             * @return result
622             * @throws PageException
623             */
624            private Object executeMinus(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
625                    return 
626                    new Double(
627                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getOperand(0),row))
628                            -
629                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getOperand(1),row))
630                    );
631            }
632            
633            /**
634             * 
635             * execute a divide operation
636             * @param sql
637             * @param qr QueryResult to execute on it
638             * @param expression
639             * @param row row of resultset to execute
640             * @return result
641             * @throws PageException
642             */
643            private Object executeDivide(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
644                    return 
645                    new Double(
646                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getOperand(0),row))
647                            /
648                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getOperand(1),row))
649                    );
650            }
651            
652            /**
653             * 
654             * execute a multiply operation
655             * @param sql
656             * @param qr QueryResult to execute on it
657             * @param expression
658             * @param row row of resultset to execute
659             * @return result
660             * @throws PageException
661             */
662            private Object executeMultiply(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
663                    return 
664                    new Double(
665                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getOperand(0),row))
666                            *
667                            Caster.toDoubleValue(executeExp(pc,sql,qr,expression.getOperand(1),row))
668                    );
669            }
670            
671            /**
672             * 
673             * execute a multiply 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 executeExponent(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
682                    return 
683                    Integer.valueOf(
684                            Caster.toIntValue(executeExp(pc,sql,qr,expression.getOperand(0),row))
685                            ^
686                            Caster.toIntValue(executeExp(pc,sql,qr,expression.getOperand(1),row))
687                    );
688            }
689            
690            /**
691             * 
692             * execute a plus operation
693             * @param sql
694             * @param qr QueryResult to execute on it
695             * @param expression
696             * @param row row of resultset to execute
697             * @return result
698             * @throws PageException
699             */
700            private Object executePlus(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
701                    Object left=executeExp(pc,sql,qr,expression.getOperand(0),row);
702                    Object right=executeExp(pc,sql,qr,expression.getOperand(1),row);
703                    
704                    try {
705                            return new Double(Caster.toDoubleValue(left)+Caster.toDoubleValue(right));
706                    } catch (PageException e) {
707                            return Caster.toString(left)+Caster.toString(right);
708                    } 
709            }
710            
711            /**
712             * 
713             * execute a between operation
714             * @param sql
715             * @param qr QueryResult to execute on it
716             * @param expression
717             * @param row row of resultset to execute
718             * @return result
719             * @throws PageException
720             */
721            private Object executeBetween(PageContext pc,SQL sql,Query qr, ZExpression expression, int row) throws PageException {
722                    Object left=executeExp(pc,sql,qr,expression.getOperand(0),row);
723                    Object right1=executeExp(pc,sql,qr,expression.getOperand(1),row);
724                    Object right2=executeExp(pc,sql,qr,expression.getOperand(2),row);
725                    return (
726                            (Operator.compare(left,right1)<=0)
727                            &&
728                            (Operator.compare(left,right2)>=0)
729                    )?Boolean.TRUE:Boolean.FALSE;
730            }
731            
732    
733            /**
734             * Executes a constant value
735             * @param sql 
736             * @param qr
737             * @param constant
738             * @param row
739             * @return result
740             * @throws PageException
741             */
742            private Object executeConstant(SQL sql,Query qr, ZConstant constant, int row) throws PageException {
743                    switch(constant.getType()) {
744                            case ZConstant.COLUMNNAME:              {
745                                if(constant.getValue().equals(SQLPrettyfier.PLACEHOLDER_QUESTION)) {
746                                        int pos=sql.getPosition();
747                                        sql.setPosition(pos+1);
748                                        if(sql.getItems().length<=pos) throw new DatabaseException("invalid syntax for SQL Statment",null,sql,null);
749                                        return sql.getItems()[pos].getValueForCF();
750                                    }
751                            return qr.getAt(List.last(constant.getValue(),".",true),row);
752                            }
753                            case ZConstant.NULL:                    return null;
754                            case ZConstant.NUMBER:                  return Caster.toDouble(constant.getValue());
755                            case ZConstant.STRING:                  return constant.getValue();
756                            case ZConstant.UNKNOWN:                 
757                            default:                                                throw new DatabaseException("invalid constant value",null,sql,null);    
758                    }
759            }
760    }