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