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}