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