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