package railo.runtime.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Set;
import railo.commons.db.DBUtil;
import railo.commons.lang.SerializableObject;
import railo.commons.lang.StringUtil;
import railo.runtime.PageContext;
import railo.runtime.config.ConfigImpl;
import railo.runtime.exp.DatabaseException;
import railo.runtime.exp.PageException;
import railo.runtime.op.Caster;
import railo.runtime.op.date.DateCaster;
import railo.runtime.sql.SQLParserException;
import railo.runtime.sql.SelectParser;
import railo.runtime.sql.Selects;
import railo.runtime.sql.old.ParseException;
import railo.runtime.timer.Stopwatch;
import railo.runtime.type.Collection.Key;
import railo.runtime.type.Query;
import railo.runtime.type.QueryColumn;
import railo.runtime.type.QueryImpl;
import railo.runtime.type.util.CollectionUtil;
import static railo.runtime.db.DatasourceManagerImpl.QOQ_DATASOURCE_NAME;
/**
* class to reexecute queries on the resultset object inside the cfml enviroment
*/
public final class HSQLDBHandler {
private static final int STRING=0;
private static final int INT=1;
private static final int DOUBLE=2;
private static final int DATE=3;
private static final int TIME=4;
private static final int TIMESTAMP=5;
private static final int BINARY=6;
Executer executer=new Executer();
QoQ qoq=new QoQ();
private static Object lock=new SerializableObject();
/**
* constructor of the class
*/
public HSQLDBHandler() {
}
/**
* adds a table to the memory database
* @param conn
* @param pc
* @param name name of the new table
* @param query data source for table
* @throws SQLException
* @throws PageException
*/
private static void addTable(Connection conn, PageContext pc,String name,Query query, boolean doSimpleTypes,ArrayList<String> usedTables) throws SQLException, PageException {
Statement stat;
usedTables.add(name);
stat = conn.createStatement();
Key[] keys = CollectionUtil.keys(query);
int[] types=query.getTypes();
int[] innerTypes=toInnerTypes(types);
// CREATE STATEMENT
String comma="";
StringBuffer create=new StringBuffer("CREATE TABLE "+name+" (");
StringBuffer insert=new StringBuffer("INSERT INTO "+name+" (");
StringBuffer values=new StringBuffer("VALUES (");
for(int i=0;i<keys.length;i++) {
String key=keys[i].getString();
String type=(doSimpleTypes)?"VARCHAR_IGNORECASE":toUsableType(types[i]);
create.append(comma+key);
create.append(" ");
create.append(type);
insert.append(comma+key);
values.append(comma+"?");
comma=",";
}
create.append(")");
insert.append(")");
values.append(")");
stat.execute(create.toString());
PreparedStatement prepStat = conn.prepareStatement(insert.toString()+values.toString());
// INSERT STATEMENT
//HashMap integerTypes=getIntegerTypes(types);
int count=query.getRecordcount();
QueryColumn[] columns=new QueryColumn[keys.length];
for(int i=0;i<keys.length;i++) {
columns[i]=query.getColumn(keys[i]);
}
for(int y=0;y<count;y++) {
for(int i=0;i<keys.length;i++) {
int type=innerTypes[i];
Object value=columns[i].get(y+1,null);
//print.out("*** "+type+":"+Caster.toString(value));
if(doSimpleTypes) {
prepStat.setObject(i+1,Caster.toString(value));
}
else {
if(value==null)
prepStat.setNull(i+1,types[i]);
else if(type==BINARY)
prepStat.setBytes(i+1,Caster.toBinary(value));
else if(type==DATE) {
//print.out(new java.util.Date(new Date(DateCaster.toDateAdvanced(value,pc.getTimeZone()).getTime()).getTime()));
prepStat.setTimestamp(i+1,(value.equals(""))?null:new Timestamp(DateCaster.toDateAdvanced(query.getAt(keys[i],y+1),pc.getTimeZone()).getTime()));
//prepStat.setObject(i+1,Caster.toDate(value,null));
//prepStat.setDate(i+1,(value==null || value.equals(""))?null:new Date(DateCaster.toDateAdvanced(value,pc.getTimeZone()).getTime()));
}
else if(type==TIME)
prepStat.setTime(i+1,(value.equals(""))?null:new Time(DateCaster.toDateAdvanced(query.getAt(keys[i],y+1),pc.getTimeZone()).getTime()));
else if(type==TIMESTAMP)
prepStat.setTimestamp(i+1,(value.equals(""))?null:new Timestamp(DateCaster.toDateAdvanced(query.getAt(keys[i],y+1),pc.getTimeZone()).getTime()));
else if(type==DOUBLE)
prepStat.setDouble(i+1,(value.equals(""))?0:Caster.toDoubleValue(query.getAt(keys[i],y+1)));
else if(type==INT)
prepStat.setLong(i+1,(value.equals(""))?0:Caster.toIntValue(query.getAt(keys[i],y+1)));
else if(type==STRING)
prepStat.setObject(i+1,Caster.toString(value));
}
}
prepStat.execute();
}
}
private static int[] toInnerTypes(int[] types) {
int[] innerTypes=new int[types.length];
for(int i=0;i<types.length;i++) {
int type=types[i];
if(
type==Types.BIGINT ||
type==Types.BIT ||
type==Types.INTEGER ||
type==Types.SMALLINT ||
type==Types.TINYINT)innerTypes[i]=INT;
else if(
type==Types.DECIMAL ||
type==Types.DOUBLE ||
type==Types.NUMERIC ||
type==Types.REAL)innerTypes[i]=DOUBLE;
else if(type==Types.DATE)innerTypes[i]=DATE;
else if(type==Types.TIME)innerTypes[i]=TIME;
else if(type==Types.TIMESTAMP)innerTypes[i]=TIMESTAMP;
else if(
type==Types.BINARY ||
type==Types.LONGVARBINARY ||
type==Types.VARBINARY)innerTypes[i]=BINARY;
else
innerTypes[i]=STRING;
}
return innerTypes;
}
private static String toUsableType(int type) {
if(type==Types.NCHAR)return "CHAR";
if(type==Types.NCLOB)return "CLOB";
if(type==Types.NVARCHAR)return "VARCHAR_IGNORECASE";
if(type==Types.VARCHAR)return "VARCHAR_IGNORECASE";
if(type==Types.JAVA_OBJECT)return "VARCHAR_IGNORECASE";
return QueryImpl.getColumTypeName(type);
}
/**
* remove a table from the memory database
* @param conn
* @param name
* @throws DatabaseException
*/
private static void removeTable(Connection conn, String name) throws SQLException {
name=name.replace('.','_');
Statement stat = conn.createStatement();
stat.execute("DROP TABLE "+name);
DBUtil.commitEL(conn);
}
/**
* remove all table inside the memory database
* @param conn
*/
private static void removeAll(Connection conn, ArrayList<String> usedTables) {
int len=usedTables.size();
for(int i=0;i<len;i++) {
String tableName=usedTables.get(i).toString();
//print.out("remove:"+tableName);
try {
removeTable(conn,tableName);
} catch (Throwable t) {}
}
}
/**
* executes a query on the queries inside the cld fusion enviroment
* @param pc Page Context
* @param sql
* @param maxrows
* @return result as Query
* @throws PageException
* @throws PageException
*/
public Query execute(PageContext pc, SQL sql, int maxrows, int fetchsize, int timeout) throws PageException {
Stopwatch stopwatch=new Stopwatch(Stopwatch.UNIT_NANO);
stopwatch.start();
String prettySQL =null;
Selects selects=null;
// First Chance
try {
SelectParser parser=new SelectParser();
selects = parser.parse(sql.getSQLString());
Query q=qoq.execute(pc,sql,selects,maxrows);
q.setExecutionTime(stopwatch.time());
return q;
}
catch (SQLParserException spe) {
//railo.print.printST(spe);
//sp
//railo.print.out("sql parser crash at:");
//railo.print.out("--------------------------------");
//railo.print.out(sql.getSQLString().trim());
//railo.print.out("--------------------------------");
//print.e("1:"+sql.getSQLString());
prettySQL = SQLPrettyfier.prettyfie(sql.getSQLString());
//print.e("2:"+prettySQL);
try {
Query query=executer.execute(pc,sql,prettySQL,maxrows);
query.setExecutionTime(stopwatch.time());
return query;
} catch (PageException ex) {
//railo.print.printST(ex);
//railo.print.out("old executor/zql crash at:");
//railo.print.out("--------------------------------");
//railo.print.out(sql.getSQLString().trim());
//railo.print.out("--------------------------------");
}
}
catch (PageException e) {
//throw e;
//print.out("new executor crash at:");
//print.out("--------------------------------");
//print.out(sql.getSQLString().trim());
//print.out("--------------------------------");
}
//if(true) throw new RuntimeException();
// SECOND Chance with hsqldb
try {
boolean isUnion=false;
Set<String> tables=null;
if(selects!=null) {
HSQLUtil2 hsql2=new HSQLUtil2(selects);
isUnion=hsql2.isUnion();
tables=hsql2.getInvokedTables();
}
else {
if(prettySQL==null)prettySQL = SQLPrettyfier.prettyfie(sql.getSQLString());
HSQLUtil hsql=new HSQLUtil(prettySQL);
tables=hsql.getInvokedTables();
isUnion=hsql.isUnion();
}
String strSQL=StringUtil.replace(sql.getSQLString(),"[","",false);
strSQL=StringUtil.replace(strSQL,"]","",false);
sql.setSQLString(strSQL);
return _execute(pc, sql, maxrows, fetchsize, timeout,stopwatch,tables,isUnion);
}
catch(ParseException e) {
throw new DatabaseException(e.getMessage(),null,sql,null);
}
}
private QueryImpl _execute(PageContext pc, SQL sql, int maxrows, int fetchsize, int timeout, Stopwatch stopwatch, Set<String> tables, boolean isUnion) throws PageException {
try {
return __execute(pc, SQLImpl.duplicate(sql), maxrows, fetchsize, timeout,stopwatch,tables,false);
}
catch(PageException pe) {
if(isUnion || StringUtil.indexOf(pe.getMessage(), "NumberFormatException:")!=-1){
return __execute(pc, sql, maxrows, fetchsize, timeout,stopwatch,tables,true);
}
throw pe;
}
}
public static QueryImpl __execute(PageContext pc, SQL sql, int maxrows, int fetchsize, int timeout,Stopwatch stopwatch,Set<String> tables, boolean doSimpleTypes) throws PageException {
ArrayList<String> usedTables=new ArrayList<String>();
synchronized(lock) {
QueryImpl nqr=null;
ConfigImpl config = (ConfigImpl)pc.getConfig();
DatasourceConnectionPool pool = config.getDatasourceConnectionPool();
DatasourceConnection dc=pool.getDatasourceConnection(pc,config.getDataSource(QOQ_DATASOURCE_NAME),"sa","");
Connection conn = dc.getConnection();
try {
DBUtil.setAutoCommitEL(conn,false);
//sql.setSQLString(HSQLUtil.sqlToZQL(sql.getSQLString(),false));
try {
Iterator<String> it = tables.iterator();
//int len=tables.size();
while(it.hasNext()) {
String tableName=it.next().toString();//tables.get(i).toString();
String modTableName=tableName.replace('.','_');
String modSql=StringUtil.replace(sql.getSQLString(),tableName,modTableName,false);
sql.setSQLString(modSql);
addTable(conn,pc,modTableName,Caster.toQuery(pc.getVariable(tableName)),doSimpleTypes,usedTables);
}
DBUtil.setReadOnlyEL(conn,true);
try {
nqr =new QueryImpl(pc,dc,sql,maxrows,fetchsize,timeout,"query",null,false,false);
}
finally {
DBUtil.setReadOnlyEL(conn,false);
DBUtil.commitEL(conn);
DBUtil.setAutoCommitEL(conn,true);
}
}
catch (SQLException e) {
DatabaseException de = new DatabaseException("there is a problem to execute sql statement on query",null,sql,null);
de.setDetail(e.getMessage());
throw de;
}
}
finally {
removeAll(conn,usedTables);
DBUtil.setAutoCommitEL(conn,true);
pool.releaseDatasourceConnection(dc);
//manager.releaseConnection(dc);
}
nqr.setExecutionTime(stopwatch.time());
return nqr;
}
}
}