001 package railo.runtime.tag; 002 003 import java.util.ArrayList; 004 import java.util.TimeZone; 005 006 import railo.commons.date.TimeZoneUtil; 007 import railo.commons.lang.StringUtil; 008 import railo.runtime.PageContext; 009 import railo.runtime.PageContextImpl; 010 import railo.runtime.config.ConfigImpl; 011 import railo.runtime.config.ConfigWebImpl; 012 import railo.runtime.config.Constants; 013 import railo.runtime.db.DataSource; 014 import railo.runtime.db.DatasourceConnection; 015 import railo.runtime.db.DatasourceManagerImpl; 016 import railo.runtime.db.HSQLDBHandler; 017 import railo.runtime.db.SQL; 018 import railo.runtime.db.SQLImpl; 019 import railo.runtime.db.SQLItem; 020 import railo.runtime.debug.DebuggerImpl; 021 import railo.runtime.debug.DebuggerPro; 022 import railo.runtime.exp.ApplicationException; 023 import railo.runtime.exp.DatabaseException; 024 import railo.runtime.exp.ExpressionException; 025 import railo.runtime.exp.PageException; 026 import railo.runtime.ext.tag.BodyTagTryCatchFinallyImpl; 027 import railo.runtime.listener.ApplicationContextPro; 028 import railo.runtime.op.Caster; 029 import railo.runtime.op.Decision; 030 import railo.runtime.orm.ORMSession; 031 import railo.runtime.orm.ORMUtil; 032 import railo.runtime.tag.util.DeprecatedUtil; 033 import railo.runtime.type.Array; 034 import railo.runtime.type.ArrayImpl; 035 import railo.runtime.type.Collection; 036 import railo.runtime.type.KeyImpl; 037 import railo.runtime.type.QueryColumn; 038 import railo.runtime.type.QueryImpl; 039 import railo.runtime.type.Struct; 040 import railo.runtime.type.StructImpl; 041 import railo.runtime.type.dt.DateTime; 042 import railo.runtime.type.dt.DateTimeImpl; 043 import railo.runtime.type.dt.TimeSpan; 044 import railo.runtime.type.query.SimpleQuery; 045 import railo.runtime.type.util.KeyConstants; 046 import railo.runtime.type.util.ListUtil; 047 048 049 050 /** 051 * Passes SQL statements to a data source. Not limited to queries. 052 **/ 053 public final class Query extends BodyTagTryCatchFinallyImpl { 054 055 private static final Collection.Key SQL_PARAMETERS = KeyImpl.intern("sqlparameters"); 056 private static final Collection.Key CFQUERY = KeyImpl.intern("cfquery"); 057 private static final Collection.Key GENERATEDKEY = KeyImpl.intern("generatedKey"); 058 private static final Collection.Key MAX_RESULTS = KeyImpl.intern("maxResults"); 059 private static final Collection.Key TIMEOUT = KeyConstants._timeout; 060 061 private static final int RETURN_TYPE_QUERY = 1; 062 private static final int RETURN_TYPE_ARRAY_OF_ENTITY = 2; 063 064 065 /** If specified, password overrides the password value specified in the data source setup. */ 066 private String password; 067 068 /** The name of the data source from which this query should retrieve data. */ 069 private DataSource datasource=null; 070 071 /** The maximum number of milliseconds for the query to execute before returning an error 072 ** indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. 073 ** timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values 074 ** vary, depending on the driver. */ 075 private int timeout=-1; 076 077 /** This is the age of which the query data can be */ 078 private TimeSpan cachedWithin; 079 080 /** Specifies the maximum number of rows to fetch at a time from the server. The range is 1, 081 ** default to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. 082 ** Certain ODBC drivers may dynamically reduce the block factor at runtime. */ 083 private int blockfactor=-1; 084 085 /** The database driver type. */ 086 private String dbtype; 087 088 /** Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the 089 ** data source and the number of records returned from the query to be returned. */ 090 private boolean debug=true; 091 092 /* This is specific to JTags, and allows you to give the cache a specific name */ 093 //private String cachename; 094 095 /** Specifies the maximum number of rows to return in the record set. */ 096 private int maxrows=-1; 097 098 /** If specified, username overrides the username value specified in the data source setup. */ 099 private String username; 100 101 /** */ 102 private DateTime cachedafter; 103 104 /** The name query. Must begin with a letter and may consist of letters, numbers, and the underscore 105 ** character, spaces are not allowed. The query name is used later in the page to reference the query's 106 ** record set. */ 107 private String name; 108 109 private String result=null; 110 111 //private static HSQLDBHandler hsql=new HSQLDBHandler(); 112 113 private boolean orgPSQ; 114 private boolean hasChangedPSQ; 115 116 ArrayList<SQLItem> items=new ArrayList<SQLItem>(); 117 118 private boolean clearCache; 119 private boolean unique; 120 private Struct ormoptions; 121 private int returntype=RETURN_TYPE_ARRAY_OF_ENTITY; 122 private TimeZone timezone; 123 private TimeZone tmpTZ; 124 private boolean lazy; 125 126 127 128 @Override 129 public void release() { 130 super.release(); 131 items.clear(); 132 password=null; 133 datasource=null; 134 timeout=-1; 135 clearCache=false; 136 cachedWithin=null; 137 cachedafter=null; 138 //cachename=""; 139 blockfactor=-1; 140 dbtype=null; 141 debug=true; 142 maxrows=-1; 143 username=null; 144 name=""; 145 result=null; 146 147 orgPSQ=false; 148 hasChangedPSQ=false; 149 unique=false; 150 151 ormoptions=null; 152 returntype=RETURN_TYPE_ARRAY_OF_ENTITY; 153 timezone=null; 154 tmpTZ=null; 155 lazy=false; 156 } 157 158 159 public void setOrmoptions(Struct ormoptions) { 160 this.ormoptions = ormoptions; 161 } 162 163 164 public void setReturntype(String strReturntype) throws ApplicationException { 165 if(StringUtil.isEmpty(strReturntype)) return; 166 strReturntype=strReturntype.toLowerCase().trim(); 167 168 if(strReturntype.equals("query")) 169 returntype=RETURN_TYPE_QUERY; 170 //mail.setType(railo.runtime.mail.Mail.TYPE_TEXT); 171 else if(strReturntype.equals("array_of_entity") || strReturntype.equals("array-of-entity") || 172 strReturntype.equals("array_of_entities") || strReturntype.equals("array-of-entities") || 173 strReturntype.equals("arrayofentities") || strReturntype.equals("arrayofentities")) 174 returntype=RETURN_TYPE_ARRAY_OF_ENTITY; 175 //mail.setType(railo.runtime.mail.Mail.TYPE_TEXT); 176 else 177 throw new ApplicationException("attribute returntype of tag query has an invalid value","valid values are [query,array-of-entity] but value is now ["+strReturntype+"]"); 178 } 179 180 181 public void setUnique(boolean unique) { 182 this.unique = unique; 183 } 184 /** 185 * @param result the result to set 186 */ 187 public void setResult(String result) { 188 this.result = result; 189 } 190 191 /** 192 * @param psq set preserver single quote 193 */ 194 public void setPsq(boolean psq) { 195 orgPSQ=pageContext.getPsq(); 196 if(orgPSQ!=psq){ 197 pageContext.setPsq(psq); 198 hasChangedPSQ=true; 199 } 200 } 201 202 /** set the value password 203 * If specified, password overrides the password value specified in the data source setup. 204 * @param password value to set 205 **/ 206 public void setPassword(String password) { 207 this.password=password; 208 } 209 210 /** set the value datasource 211 * The name of the data source from which this query should retrieve data. 212 * @param datasource value to set 213 **/ 214 public void setDatasource(String datasource) throws PageException { 215 this.datasource=((PageContextImpl)pageContext).getDataSource(datasource); 216 } 217 218 /** set the value timeout 219 * The maximum number of milliseconds for the query to execute before returning an error 220 * indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. 221 * timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values 222 * vary, depending on the driver. 223 * @param timeout value to set 224 **/ 225 public void setTimeout(double timeout) { 226 this.timeout=(int)timeout; 227 } 228 229 /** set the value cachedafter 230 * This is the age of which the query data can be 231 * @param cachedafter value to set 232 **/ 233 public void setCachedafter(DateTime cachedafter) { 234 //railo.print.ln("cachedafter:"+cachedafter); 235 this.cachedafter=cachedafter; 236 } 237 238 /** set the value cachename 239 * This is specific to JTags, and allows you to give the cache a specific name 240 * @param cachename value to set 241 **/ 242 public void setCachename(String cachename) { 243 DeprecatedUtil.tagAttribute(pageContext,"query", "cachename"); 244 //this.cachename=cachename; 245 } 246 247 /** set the value cachedwithin 248 * 249 * @param cachedwithin value to set 250 **/ 251 public void setCachedwithin(TimeSpan cachedwithin) { 252 if(cachedwithin.getMillis()>0) 253 this.cachedWithin=cachedwithin; 254 else clearCache=true; 255 } 256 257 public void setLazy(boolean lazy) { 258 this.lazy=lazy; 259 } 260 261 /** set the value providerdsn 262 * Data source name for the COM provider, OLE-DB only. 263 * @param providerdsn value to set 264 * @throws ApplicationException 265 **/ 266 public void setProviderdsn(String providerdsn) throws ApplicationException { 267 DeprecatedUtil.tagAttribute(pageContext,"Query", "providerdsn"); 268 } 269 270 /** set the value connectstring 271 * @param connectstring value to set 272 * @throws ApplicationException 273 **/ 274 public void setConnectstring(String connectstring) throws ApplicationException { 275 DeprecatedUtil.tagAttribute(pageContext,"Query", "connectstring"); 276 } 277 278 279 public void setTimezone(String timezone) throws ExpressionException { 280 this.timezone=TimeZoneUtil.toTimeZone(timezone); 281 } 282 283 /** set the value blockfactor 284 * Specifies the maximum number of rows to fetch at a time from the server. The range is 1, 285 * default to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. 286 * Certain ODBC drivers may dynamically reduce the block factor at runtime. 287 * @param blockfactor value to set 288 **/ 289 public void setBlockfactor(double blockfactor) { 290 this.blockfactor=(int) blockfactor; 291 } 292 293 /** set the value dbtype 294 * The database driver type. 295 * @param dbtype value to set 296 **/ 297 public void setDbtype(String dbtype) { 298 this.dbtype=dbtype.toLowerCase(); 299 } 300 301 /** set the value debug 302 * Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the 303 * data source and the number of records returned from the query to be returned. 304 * @param debug value to set 305 **/ 306 public void setDebug(boolean debug) { 307 this.debug=debug; 308 } 309 310 /** set the value dbname 311 * The database name, Sybase System 11 driver and SQLOLEDB provider only. If specified, dbName 312 * overrides the default database specified in the data source. 313 * @param dbname value to set 314 * @throws ApplicationException 315 **/ 316 public void setDbname(String dbname) { 317 DeprecatedUtil.tagAttribute(pageContext,"Query", "dbname"); 318 } 319 320 /** set the value maxrows 321 * Specifies the maximum number of rows to return in the record set. 322 * @param maxrows value to set 323 **/ 324 public void setMaxrows(double maxrows) { 325 this.maxrows=(int) maxrows; 326 } 327 328 /** set the value username 329 * If specified, username overrides the username value specified in the data source setup. 330 * @param username value to set 331 **/ 332 public void setUsername(String username) { 333 if(!StringUtil.isEmpty(username)) 334 this.username=username; 335 } 336 337 /** set the value provider 338 * COM provider, OLE-DB only. 339 * @param provider value to set 340 * @throws ApplicationException 341 **/ 342 public void setProvider(String provider) { 343 DeprecatedUtil.tagAttribute(pageContext,"Query", "provider"); 344 } 345 346 /** set the value dbserver 347 * For native database drivers and the SQLOLEDB provider, specifies the name of the database server 348 * computer. If specified, dbServer overrides the server specified in the data source. 349 * @param dbserver value to set 350 * @throws ApplicationException 351 **/ 352 public void setDbserver(String dbserver) { 353 DeprecatedUtil.tagAttribute(pageContext,"Query", "dbserver"); 354 } 355 356 /** set the value name 357 * The name query. Must begin with a letter and may consist of letters, numbers, and the underscore 358 * character, spaces are not allowed. The query name is used later in the page to reference the query's 359 * record set. 360 * @param name value to set 361 **/ 362 public void setName(String name) { 363 this.name=name; 364 } 365 366 public String getName() { 367 return name==null? "query":name; 368 } 369 370 371 372 373 374 /** 375 * @param item 376 */ 377 public void setParam(SQLItem item) { 378 items.add(item); 379 } 380 381 382 @Override 383 public int doStartTag() throws PageException { 384 // default datasource 385 if(datasource==null && (dbtype==null || !dbtype.equals("query"))){ 386 Object obj = ((ApplicationContextPro)pageContext.getApplicationContext()).getDefDataSource(); 387 if(StringUtil.isEmpty(obj)) 388 throw new ApplicationException( 389 "attribute [datasource] is required, when attribute [dbtype] has not value [query] and no default datasource is defined", 390 "you can define a default datasource as attribute [defaultdatasource] of the tag "+Constants.CFAPP_NAME+" or as data member of the "+Constants.APP_CFC+" (this.defaultdatasource=\"mydatasource\";)"); 391 392 datasource=obj instanceof DataSource?(DataSource)obj:((PageContextImpl)pageContext).getDataSource(Caster.toString(obj)); 393 } 394 395 396 // timezone 397 if(timezone!=null || (datasource!=null && (timezone=datasource.getTimeZone())!=null)) { 398 tmpTZ=pageContext.getTimeZone(); 399 pageContext.setTimeZone(timezone); 400 } 401 402 403 return EVAL_BODY_BUFFERED; 404 } 405 406 @Override 407 public void doFinally() { 408 if(tmpTZ!=null) { 409 pageContext.setTimeZone(tmpTZ); 410 } 411 super.doFinally(); 412 } 413 414 @Override 415 public int doEndTag() throws PageException { 416 if(hasChangedPSQ)pageContext.setPsq(orgPSQ); 417 String strSQL=bodyContent.getString(); 418 if(strSQL.length()==0) throw new DatabaseException("no sql string defined, inside query tag",null,null,null); 419 SQL sql=items.size()>0?new SQLImpl(strSQL,items.toArray(new SQLItem[items.size()])):new SQLImpl(strSQL); 420 421 railo.runtime.type.Query query=null; 422 long exe=0; 423 boolean hasCached=cachedWithin!=null || cachedafter!=null; 424 425 426 if(clearCache) { 427 hasCached=false; 428 pageContext.getQueryCache().remove(pageContext,sql,datasource!=null?datasource.getName():null,username,password); 429 } 430 else if(hasCached) { 431 query=pageContext.getQueryCache().getQuery(pageContext,sql,datasource!=null?datasource.getName():null,username,password,cachedafter); 432 } 433 434 435 if(query==null) { 436 if("query".equals(dbtype)) query=executeQoQ(sql); 437 else if("orm".equals(dbtype) || "hql".equals(dbtype)) { 438 long start=System.nanoTime(); 439 Object obj = executeORM(sql,returntype,ormoptions); 440 441 if(obj instanceof railo.runtime.type.Query){ 442 query=(railo.runtime.type.Query) obj; 443 } 444 else { 445 if(!StringUtil.isEmpty(name)) { 446 pageContext.setVariable(name,obj); 447 } 448 if(result!=null){ 449 Struct sct=new StructImpl(); 450 sct.setEL(KeyConstants._cached, Boolean.FALSE); 451 long time=System.nanoTime()-start; 452 sct.setEL(KeyConstants._executionTime, Caster.toDouble(time/1000000)); 453 sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(time)); 454 sct.setEL(KeyConstants._SQL, sql.getSQLString()); 455 if(Decision.isArray(obj)){ 456 457 } 458 else sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(1)); 459 460 pageContext.setVariable(result, sct); 461 } 462 else 463 setExecutionTime((System.nanoTime()-start)/1000000); 464 return EVAL_PAGE; 465 } 466 } 467 else query=executeDatasoure(sql,result!=null,pageContext.getTimeZone()); 468 //query=(dbtype!=null && dbtype.equals("query"))?executeQoQ(sql):executeDatasoure(sql,result!=null); 469 470 if(cachedWithin!=null) { 471 DateTimeImpl cachedBefore = null; 472 //if(cachedWithin!=null) 473 cachedBefore=new DateTimeImpl(pageContext,System.currentTimeMillis()+cachedWithin.getMillis(),false); 474 pageContext.getQueryCache().set(pageContext,sql,datasource!=null?datasource.getName():null,username,password,query,cachedBefore); 475 476 477 } 478 exe=query.getExecutionTime(); 479 } 480 else query.setCached(hasCached); 481 482 if(pageContext.getConfig().debug() && debug) { 483 boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE); 484 if(logdb){ 485 boolean debugUsage=DebuggerImpl.debugQueryUsage(pageContext,query); 486 ((DebuggerPro)pageContext.getDebugger()).addQuery(debugUsage?query:null,datasource!=null?datasource.getName():null,name,sql,query.getRecordcount(),pageContext.getCurrentPageSource(),exe); 487 } 488 } 489 490 if(!query.isEmpty() && !StringUtil.isEmpty(name)) { 491 pageContext.setVariable(name,query); 492 } 493 494 // Result 495 if(result!=null) { 496 497 Struct sct=new StructImpl(); 498 sct.setEL(KeyConstants._cached, Caster.toBoolean(query.isCached())); 499 if(!query.isEmpty())sct.setEL(KeyConstants._COLUMNLIST, ListUtil.arrayToList(query.getColumnNamesAsString(),",")); 500 int rc=query.getRecordcount(); 501 if(rc==0)rc=query.getUpdateCount(); 502 sct.setEL(KeyConstants._RECORDCOUNT, Caster.toDouble(rc)); 503 sct.setEL(KeyConstants._executionTime, Caster.toDouble(query.getExecutionTime()/1000000)); 504 sct.setEL(KeyConstants._executionTimeNano, Caster.toDouble(query.getExecutionTime())); 505 506 sct.setEL(KeyConstants._SQL, sql.getSQLString()); 507 508 // GENERATED KEYS 509 railo.runtime.type.Query qi = Caster.toQuery(query,null); 510 if(qi !=null){ 511 railo.runtime.type.Query qryKeys = qi.getGeneratedKeys(); 512 if(qryKeys!=null){ 513 StringBuilder generatedKey=new StringBuilder(),sb; 514 Collection.Key[] columnNames = qryKeys.getColumnNames(); 515 QueryColumn column; 516 for(int c=0;c<columnNames.length;c++){ 517 column = qryKeys.getColumn(columnNames[c]); 518 sb=new StringBuilder(); 519 int size=column.size(); 520 for(int row=1;row<=size;row++) { 521 if(row>1)sb.append(','); 522 sb.append(Caster.toString(column.get(row,null))); 523 } 524 if(sb.length()>0){ 525 sct.setEL(columnNames[c], sb.toString()); 526 if(generatedKey.length()>0)generatedKey.append(','); 527 generatedKey.append(sb); 528 } 529 } 530 if(generatedKey.length()>0) 531 sct.setEL(GENERATEDKEY, generatedKey.toString()); 532 } 533 } 534 535 // sqlparameters 536 SQLItem[] params = sql.getItems(); 537 if(params!=null && params.length>0) { 538 Array arr=new ArrayImpl(); 539 sct.setEL(SQL_PARAMETERS, arr); 540 for(int i=0;i<params.length;i++) { 541 arr.append(params[i].getValue()); 542 543 } 544 } 545 pageContext.setVariable(result, sct); 546 } 547 // cfquery.executiontime 548 else { 549 setExecutionTime(exe/1000000); 550 551 } 552 553 554 // listener 555 ((ConfigWebImpl)pageContext.getConfig()).getActionMonitorCollector() 556 .log(pageContext, "query", "Query", exe, query); 557 558 return EVAL_PAGE; 559 } 560 561 private void setExecutionTime(long exe) { 562 Struct sct=new StructImpl(); 563 sct.setEL(KeyConstants._executionTime,new Double(exe)); 564 pageContext.undefinedScope().setEL(CFQUERY,sct); 565 } 566 567 568 private Object executeORM(SQL sql, int returnType, Struct ormoptions) throws PageException { 569 ORMSession session=ORMUtil.getSession(pageContext); 570 571 // params 572 SQLItem[] _items = sql.getItems(); 573 Array params=new ArrayImpl(); 574 for(int i=0;i<_items.length;i++){ 575 params.appendEL(_items[i]); 576 } 577 578 // query options 579 if(maxrows!=-1 && !ormoptions.containsKey(MAX_RESULTS)) ormoptions.setEL(MAX_RESULTS, new Double(maxrows)); 580 if(timeout!=-1 && !ormoptions.containsKey(TIMEOUT)) ormoptions.setEL(TIMEOUT, new Double(timeout)); 581 /* MUST 582 offset: Specifies the start index of the resultset from where it has to start the retrieval. 583 cacheable: Whether the result of this query is to be cached in the secondary cache. Default is false. 584 cachename: Name of the cache in secondary cache. 585 */ 586 Object res = session.executeQuery(pageContext,sql.getSQLString(),params,unique,ormoptions); 587 if(returnType==RETURN_TYPE_ARRAY_OF_ENTITY) return res; 588 return session.toQuery(pageContext, res, null); 589 590 } 591 592 public static Object _call(PageContext pc,String hql, Object params, boolean unique, Struct queryOptions) throws PageException { 593 ORMSession session=ORMUtil.getSession(pc); 594 //ORMEngine engine= ORMUtil.getEngine(pc); 595 if(Decision.isCastableToArray(params)) 596 return session.executeQuery(pc,hql,Caster.toArray(params),unique,queryOptions); 597 else if(Decision.isCastableToStruct(params)) 598 return session.executeQuery(pc,hql,Caster.toStruct(params),unique,queryOptions); 599 else 600 return session.executeQuery(pc,hql,(Array)params,unique,queryOptions); 601 } 602 603 604 private railo.runtime.type.Query executeQoQ(SQL sql) throws PageException { 605 try { 606 return new HSQLDBHandler().execute(pageContext,sql,maxrows,blockfactor,timeout); 607 } 608 catch (Exception e) { 609 throw Caster.toPageException(e); 610 } 611 } 612 613 private railo.runtime.type.Query executeDatasoure(SQL sql,boolean createUpdateData,TimeZone tz) throws PageException { 614 DatasourceManagerImpl manager = (DatasourceManagerImpl) pageContext.getDataSourceManager(); 615 DatasourceConnection dc=manager.getConnection(pageContext,datasource, username, password); 616 617 try { 618 if(lazy && !createUpdateData && cachedWithin==null && cachedafter==null && result==null) 619 return new SimpleQuery(dc,sql,maxrows,blockfactor,timeout,getName(),pageContext.getCurrentPageSource().getDisplayPath(),tz); 620 621 622 return new QueryImpl(pageContext,dc,sql,maxrows,blockfactor,timeout,getName(),pageContext.getCurrentPageSource().getDisplayPath(),createUpdateData,true); 623 } 624 finally { 625 manager.releaseConnection(pageContext,dc); 626 } 627 } 628 629 630 @Override 631 public void doInitBody() { 632 633 } 634 635 @Override 636 public int doAfterBody() { 637 return SKIP_BODY; 638 } 639 }