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