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