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    }