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