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