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}