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 static lucee.runtime.db.DatasourceManagerImpl.QOQ_DATASOURCE_NAME; 022 023import java.sql.Connection; 024import java.sql.PreparedStatement; 025import java.sql.SQLException; 026import java.sql.Statement; 027import java.sql.Time; 028import java.sql.Timestamp; 029import java.sql.Types; 030import java.util.ArrayList; 031import java.util.Iterator; 032import java.util.Set; 033 034import lucee.commons.db.DBUtil; 035import lucee.commons.lang.ExceptionUtil; 036import lucee.commons.lang.SerializableObject; 037import lucee.commons.lang.StringUtil; 038import lucee.runtime.PageContext; 039import lucee.runtime.config.ConfigImpl; 040import lucee.runtime.exp.DatabaseException; 041import lucee.runtime.exp.PageException; 042import lucee.runtime.op.Caster; 043import lucee.runtime.op.date.DateCaster; 044import lucee.runtime.sql.SQLParserException; 045import lucee.runtime.sql.SelectParser; 046import lucee.runtime.sql.Selects; 047import lucee.runtime.sql.old.ParseException; 048import lucee.runtime.timer.Stopwatch; 049import lucee.runtime.type.Collection.Key; 050import lucee.runtime.type.Query; 051import lucee.runtime.type.QueryColumn; 052import lucee.runtime.type.QueryImpl; 053import lucee.runtime.type.dt.TimeSpan; 054import lucee.runtime.type.util.CollectionUtil; 055 056/** 057 * class to reexecute queries on the resultset object inside the cfml enviroment 058 */ 059public final class HSQLDBHandler { 060 061 private static final int STRING=0; 062 private static final int INT=1; 063 private static final int DOUBLE=2; 064 private static final int DATE=3; 065 private static final int TIME=4; 066 private static final int TIMESTAMP=5; 067 private static final int BINARY=6; 068 069 070 Executer executer=new Executer(); 071 QoQ qoq=new QoQ(); 072 private static Object lock=new SerializableObject(); 073 074 /** 075 * constructor of the class 076 */ 077 public HSQLDBHandler() { 078 079 } 080 081 /** 082 * adds a table to the memory database 083 * @param conn 084 * @param pc 085 * @param name name of the new table 086 * @param query data source for table 087 * @throws SQLException 088 * @throws PageException 089 */ 090 private static void addTable(Connection conn, PageContext pc,String name,Query query, boolean doSimpleTypes,ArrayList<String> usedTables) throws SQLException, PageException { 091 Statement stat; 092 usedTables.add(name); 093 stat = conn.createStatement(); 094 Key[] keys = CollectionUtil.keys(query); 095 int[] types=query.getTypes(); 096 int[] innerTypes=toInnerTypes(types); 097 // CREATE STATEMENT 098 String comma=""; 099 StringBuilder create=new StringBuilder("CREATE TABLE "+name+" ("); 100 StringBuilder insert=new StringBuilder("INSERT INTO "+name+" ("); 101 StringBuilder values=new StringBuilder("VALUES ("); 102 for(int i=0;i<keys.length;i++) { 103 String key=keys[i].getString(); 104 String type=(doSimpleTypes)?"VARCHAR_IGNORECASE":toUsableType(types[i]); 105 106 107 create.append(comma+key); 108 create.append(" "); 109 create.append(type); 110 insert.append(comma+key); 111 values.append(comma+"?"); 112 comma=","; 113 } 114 create.append(")"); 115 insert.append(")"); 116 values.append(")"); 117 stat.execute(create.toString()); 118 PreparedStatement prepStat = conn.prepareStatement(insert.toString()+values.toString()); 119 120 // INSERT STATEMENT 121 //HashMap integerTypes=getIntegerTypes(types); 122 123 int count=query.getRecordcount(); 124 QueryColumn[] columns=new QueryColumn[keys.length]; 125 for(int i=0;i<keys.length;i++) { 126 columns[i]=query.getColumn(keys[i]); 127 } 128 for(int y=0;y<count;y++) { 129 for(int i=0;i<keys.length;i++) { 130 int type=innerTypes[i]; 131 Object value=columns[i].get(y+1,null); 132 133 //print.out("*** "+type+":"+Caster.toString(value)); 134 if(doSimpleTypes) { 135 136 prepStat.setObject(i+1,Caster.toString(value)); 137 } 138 else { 139 if(value==null) 140 prepStat.setNull(i+1,types[i]); 141 else if(type==BINARY) 142 prepStat.setBytes(i+1,Caster.toBinary(value)); 143 else if(type==DATE) { 144 //print.out(new java.util.Date(new Date(DateCaster.toDateAdvanced(value,pc.getTimeZone()).getTime()).getTime())); 145 146 prepStat.setTimestamp(i+1,(value.equals(""))?null:new Timestamp(DateCaster.toDateAdvanced(query.getAt(keys[i],y+1),pc.getTimeZone()).getTime())); 147 //prepStat.setObject(i+1,Caster.toDate(value,null)); 148 //prepStat.setDate(i+1,(value==null || value.equals(""))?null:new Date(DateCaster.toDateAdvanced(value,pc.getTimeZone()).getTime())); 149 } 150 else if(type==TIME) 151 prepStat.setTime(i+1,(value.equals(""))?null:new Time(DateCaster.toDateAdvanced(query.getAt(keys[i],y+1),pc.getTimeZone()).getTime())); 152 else if(type==TIMESTAMP) 153 prepStat.setTimestamp(i+1,(value.equals(""))?null:new Timestamp(DateCaster.toDateAdvanced(query.getAt(keys[i],y+1),pc.getTimeZone()).getTime())); 154 else if(type==DOUBLE) 155 prepStat.setDouble(i+1,(value.equals(""))?0:Caster.toDoubleValue(query.getAt(keys[i],y+1))); 156 else if(type==INT) 157 prepStat.setLong(i+1,(value.equals(""))?0:Caster.toIntValue(query.getAt(keys[i],y+1))); 158 else if(type==STRING) 159 prepStat.setObject(i+1,Caster.toString(value)); 160 } 161 162 } 163 prepStat.execute(); 164 } 165 166 } 167 168 169 private static int[] toInnerTypes(int[] types) { 170 int[] innerTypes=new int[types.length]; 171 for(int i=0;i<types.length;i++) { 172 int type=types[i]; 173 174 if( 175 type==Types.BIGINT || 176 type==Types.BIT || 177 type==Types.INTEGER || 178 type==Types.SMALLINT || 179 type==Types.TINYINT)innerTypes[i]=INT; 180 else if( 181 type==Types.DECIMAL || 182 type==Types.DOUBLE || 183 type==Types.NUMERIC || 184 type==Types.REAL)innerTypes[i]=DOUBLE; 185 else if(type==Types.DATE)innerTypes[i]=DATE; 186 else if(type==Types.TIME)innerTypes[i]=TIME; 187 else if(type==Types.TIMESTAMP)innerTypes[i]=TIMESTAMP; 188 else if( 189 type==Types.BINARY || 190 type==Types.LONGVARBINARY || 191 type==Types.VARBINARY)innerTypes[i]=BINARY; 192 else 193 innerTypes[i]=STRING; 194 195 196 } 197 return innerTypes; 198 } 199 200 201 private static String toUsableType(int type) { 202 if(type==Types.NCHAR)return "CHAR"; 203 if(type==Types.NCLOB)return "CLOB"; 204 if(type==Types.NVARCHAR)return "VARCHAR_IGNORECASE"; 205 if(type==Types.VARCHAR)return "VARCHAR_IGNORECASE"; 206 if(type==Types.JAVA_OBJECT)return "VARCHAR_IGNORECASE"; 207 208 209 210 return QueryImpl.getColumTypeName(type); 211 212 } 213 214 215 /** 216 * remove a table from the memory database 217 * @param conn 218 * @param name 219 * @throws DatabaseException 220 */ 221 private static void removeTable(Connection conn, String name) throws SQLException { 222 name=name.replace('.','_'); 223 Statement stat = conn.createStatement(); 224 stat.execute("DROP TABLE "+name); 225 DBUtil.commitEL(conn); 226 } 227 228 /** 229 * remove all table inside the memory database 230 * @param conn 231 */ 232 private static void removeAll(Connection conn, ArrayList<String> usedTables) { 233 int len=usedTables.size(); 234 235 for(int i=0;i<len;i++) { 236 237 String tableName=usedTables.get(i).toString(); 238 //print.out("remove:"+tableName); 239 try { 240 removeTable(conn,tableName); 241 } catch (Throwable t) { 242 ExceptionUtil.rethrowIfNecessary(t); 243 } 244 } 245 } 246 247 /** 248 * executes a query on the queries inside the cld fusion enviroment 249 * @param pc Page Context 250 * @param sql 251 * @param maxrows 252 * @return result as Query 253 * @throws PageException 254 * @throws PageException 255 */ 256 public Query execute(PageContext pc, SQL sql, int maxrows, int fetchsize, TimeSpan timeout) throws PageException { 257 Stopwatch stopwatch=new Stopwatch(Stopwatch.UNIT_NANO); 258 stopwatch.start(); 259 String prettySQL =null; 260 Selects selects=null; 261 262 // First Chance 263 try { 264 SelectParser parser=new SelectParser(); 265 selects = parser.parse(sql.getSQLString()); 266 267 Query q=qoq.execute(pc,sql,selects,maxrows); 268 q.setExecutionTime(stopwatch.time()); 269 270 return q; 271 } 272 catch (SQLParserException spe) { 273 //lucee.print.printST(spe); 274 //sp 275 //lucee.print.out("sql parser crash at:"); 276 //lucee.print.out("--------------------------------"); 277 //lucee.print.out(sql.getSQLString().trim()); 278 //lucee.print.out("--------------------------------"); 279 //print.e("1:"+sql.getSQLString()); 280 prettySQL = SQLPrettyfier.prettyfie(sql.getSQLString()); 281 //print.e("2:"+prettySQL); 282 try { 283 Query query=executer.execute(pc,sql,prettySQL,maxrows); 284 query.setExecutionTime(stopwatch.time()); 285 return query; 286 } catch (PageException ex) { 287 //lucee.print.printST(ex); 288 //lucee.print.out("old executor/zql crash at:"); 289 //lucee.print.out("--------------------------------"); 290 //lucee.print.out(sql.getSQLString().trim()); 291 //lucee.print.out("--------------------------------"); 292 293 } 294 295 } 296 catch (PageException e) { 297 //throw e; 298 //print.out("new executor crash at:"); 299 //print.out("--------------------------------"); 300 //print.out(sql.getSQLString().trim()); 301 //print.out("--------------------------------"); 302 } 303 //if(true) throw new RuntimeException(); 304 305 // SECOND Chance with hsqldb 306 try { 307 boolean isUnion=false; 308 Set<String> tables=null; 309 if(selects!=null) { 310 HSQLUtil2 hsql2=new HSQLUtil2(selects); 311 isUnion=hsql2.isUnion(); 312 tables=hsql2.getInvokedTables(); 313 } 314 else { 315 if(prettySQL==null)prettySQL = SQLPrettyfier.prettyfie(sql.getSQLString()); 316 HSQLUtil hsql=new HSQLUtil(prettySQL); 317 tables=hsql.getInvokedTables(); 318 isUnion=hsql.isUnion(); 319 } 320 321 String strSQL=StringUtil.replace(sql.getSQLString(),"[","",false); 322 strSQL=StringUtil.replace(strSQL,"]","",false); 323 sql.setSQLString(strSQL); 324 return _execute(pc, sql, maxrows, fetchsize, timeout,stopwatch,tables,isUnion); 325 326 } 327 catch(ParseException e) { 328 throw new DatabaseException(e.getMessage(),null,sql,null); 329 } 330 331 } 332 333 private QueryImpl _execute(PageContext pc, SQL sql, int maxrows, int fetchsize, TimeSpan timeout, Stopwatch stopwatch, Set<String> tables, boolean isUnion) throws PageException { 334 try { 335 return __execute(pc, SQLImpl.duplicate(sql), maxrows, fetchsize, timeout,stopwatch,tables,false); 336 } 337 catch(PageException pe) { 338 if(isUnion || StringUtil.indexOf(pe.getMessage(), "NumberFormatException:")!=-1){ 339 return __execute(pc, sql, maxrows, fetchsize, timeout,stopwatch,tables,true); 340 } 341 throw pe; 342 } 343 } 344 345 public static QueryImpl __execute(PageContext pc, SQL sql, int maxrows, int fetchsize, TimeSpan timeout,Stopwatch stopwatch,Set<String> tables, boolean doSimpleTypes) throws PageException { 346 ArrayList<String> usedTables=new ArrayList<String>(); 347 synchronized(lock) { 348 349 QueryImpl nqr=null; 350 ConfigImpl config = (ConfigImpl)pc.getConfig(); 351 DatasourceConnectionPool pool = config.getDatasourceConnectionPool(); 352 DatasourceConnection dc=pool.getDatasourceConnection(config.getDataSource(QOQ_DATASOURCE_NAME),"sa",""); 353 Connection conn = dc.getConnection(); 354 try { 355 DBUtil.setAutoCommitEL(conn,false); 356 357 //sql.setSQLString(HSQLUtil.sqlToZQL(sql.getSQLString(),false)); 358 try { 359 Iterator<String> it = tables.iterator(); 360 //int len=tables.size(); 361 while(it.hasNext()) { 362 String tableName=it.next().toString();//tables.get(i).toString(); 363 364 String modTableName=tableName.replace('.','_'); 365 String modSql=StringUtil.replace(sql.getSQLString(),tableName,modTableName,false); 366 sql.setSQLString(modSql); 367 addTable(conn,pc,modTableName,Caster.toQuery(pc.getVariable(tableName)),doSimpleTypes,usedTables); 368 } 369 DBUtil.setReadOnlyEL(conn,true); 370 try { 371 nqr =new QueryImpl(pc,dc,sql,maxrows,fetchsize,timeout,"query",null,false,false); 372 } 373 finally { 374 DBUtil.setReadOnlyEL(conn,false); 375 DBUtil.commitEL(conn); 376 DBUtil.setAutoCommitEL(conn,true); 377 } 378 379 } 380 catch (SQLException e) { 381 DatabaseException de = new DatabaseException("there is a problem to execute sql statement on query",null,sql,null); 382 de.setDetail(e.getMessage()); 383 throw de; 384 } 385 386 } 387 finally { 388 removeAll(conn,usedTables); 389 DBUtil.setAutoCommitEL(conn,true); 390 pool.releaseDatasourceConnection(config,dc,true); 391 392 //manager.releaseConnection(dc); 393 } 394 nqr.setExecutionTime(stopwatch.time()); 395 return nqr; 396 } 397 } 398}