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    }