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