001 package railo.runtime.type.scope.storage.db; 002 003 import java.sql.Connection; 004 import java.sql.PreparedStatement; 005 import java.sql.SQLException; 006 import java.sql.Types; 007 import java.util.TimeZone; 008 009 import railo.commons.io.log.Log; 010 import railo.runtime.PageContext; 011 import railo.runtime.config.Config; 012 import railo.runtime.converter.ConverterException; 013 import railo.runtime.converter.ScriptConverter; 014 import railo.runtime.db.DatasourceConnection; 015 import railo.runtime.db.SQL; 016 import railo.runtime.db.SQLCaster; 017 import railo.runtime.db.SQLImpl; 018 import railo.runtime.db.SQLItem; 019 import railo.runtime.db.SQLItemImpl; 020 import railo.runtime.engine.ThreadLocalPageContext; 021 import railo.runtime.exp.DatabaseException; 022 import railo.runtime.exp.PageException; 023 import railo.runtime.interpreter.VariableInterpreter; 024 import railo.runtime.op.Caster; 025 import railo.runtime.type.Query; 026 import railo.runtime.type.QueryImpl; 027 import railo.runtime.type.Struct; 028 import railo.runtime.type.scope.ScopeContext; 029 import railo.runtime.type.scope.storage.StorageScopeDatasource; 030 import railo.runtime.type.scope.storage.StorageScopeEngine; 031 import railo.runtime.type.scope.storage.StorageScopeListener; 032 import railo.runtime.type.scope.storage.clean.DatasourceStorageScopeCleaner; 033 import railo.runtime.type.util.KeyConstants; 034 035 public class Ansi92 extends SQLExecutorSupport { 036 037 public static final String PREFIX = "cf"; 038 039 @Override 040 public Query select(Config config,String cfid,String applicationName,DatasourceConnection dc, int type,Log log, boolean createTableIfNotExist) throws PageException { 041 String strType = VariableInterpreter.scopeInt2String(type); 042 Query query=null; 043 SQL sqlSelect=new SQLImpl("select data from "+PREFIX+"_"+strType+"_data where cfid=? and name=? and expires > ?" 044 ,new SQLItem[]{ 045 new SQLItemImpl(cfid,Types.VARCHAR), 046 new SQLItemImpl(applicationName,Types.VARCHAR), 047 new SQLItemImpl(now(config),Types.VARCHAR) 048 }); 049 050 PageContext pc = ThreadLocalPageContext.get(); 051 052 try { 053 query = new QueryImpl(pc,dc,sqlSelect,-1,-1,-1,"query"); 054 } 055 catch (DatabaseException de) { 056 if(dc==null || !createTableIfNotExist) throw de; 057 try { 058 SQL sql = createSQL(dc,"text",strType); 059 ScopeContext.info(log,sql.toString()); 060 new QueryImpl(pc,dc,sql,-1,-1,-1,"query"); 061 } 062 catch (DatabaseException _de) { 063 try { 064 SQL sql = createSQL(dc,"memo",strType); 065 ScopeContext.info(log,sql.toString()); 066 new QueryImpl(pc,dc,sql,-1,-1,-1,"query"); 067 } 068 catch (DatabaseException __de) { 069 SQL sql = createSQL(dc,"clob",strType); 070 ScopeContext.info(log,sql.toString()); 071 new QueryImpl(pc,dc,sql,-1,-1,-1,"query"); 072 } 073 } 074 query = new QueryImpl(pc,dc,sqlSelect,-1,-1,-1,"query"); 075 } 076 ScopeContext.info(log,sqlSelect.toString()); 077 return query; 078 } 079 080 @Override 081 public void update(Config config, String cfid, String applicationName, DatasourceConnection dc, int type, Struct data, long timeSpan, Log log) throws PageException, SQLException { 082 String strType = VariableInterpreter.scopeInt2String(type); 083 TimeZone tz = ThreadLocalPageContext.getTimeZone(); 084 int recordsAffected = _update(config,dc.getConnection(),cfid,applicationName,"update "+PREFIX+"_"+strType+"_data set expires=?,data=? where cfid=? and name=?",data,timeSpan,log,tz); 085 086 if(recordsAffected>1) { 087 delete(config, cfid, applicationName, dc, type, log); 088 recordsAffected=0; 089 } 090 if(recordsAffected==0) { 091 _update(config,dc.getConnection(),cfid,applicationName,"insert into "+PREFIX+"_"+strType+"_data (expires,data,cfid,name) values(?,?,?,?)",data,timeSpan,log,tz); 092 } 093 } 094 095 private static int _update(Config config,Connection conn,String cfid, String applicationName, String strSQL,Struct data, long timeSpan, Log log, TimeZone tz) throws SQLException, PageException { 096 //String appName = pc.getApplicationContext().getName(); 097 try{ 098 SQLImpl sql = new SQLImpl(strSQL,new SQLItem[]{ 099 new SQLItemImpl(createExpires(config,timeSpan),Types.VARCHAR), 100 new SQLItemImpl(new ScriptConverter().serializeStruct(data,ignoreSet),Types.VARCHAR), 101 new SQLItemImpl(cfid,Types.VARCHAR), 102 new SQLItemImpl(applicationName,Types.VARCHAR) 103 }); 104 ScopeContext.info(log,sql.toString()); 105 106 return execute(conn, sql,tz); 107 } 108 catch(ConverterException ce){ 109 throw Caster.toPageException(ce); 110 } 111 } 112 113 @Override 114 public void delete(Config config, String cfid, String applicationName, DatasourceConnection dc, int type, Log log) throws PageException, SQLException { 115 String strType = VariableInterpreter.scopeInt2String(type); 116 String strSQL="delete from "+PREFIX+"_"+strType+"_data where cfid=? and name=?"; 117 SQLImpl sql = new SQLImpl(strSQL,new SQLItem[]{ 118 new SQLItemImpl(cfid,Types.VARCHAR), 119 new SQLItemImpl(applicationName,Types.VARCHAR) 120 }); 121 execute(dc.getConnection(), sql,ThreadLocalPageContext.getTimeZone()); 122 ScopeContext.info(log,sql.toString()); 123 124 } 125 126 127 @Override 128 public void clean(Config config, DatasourceConnection dc, int type,StorageScopeEngine engine,DatasourceStorageScopeCleaner cleaner,StorageScopeListener listener, Log log) throws PageException { 129 String strType = VariableInterpreter.scopeInt2String(type); 130 // select 131 SQL sqlSelect=new SQLImpl("select cfid,name from "+PREFIX+"_"+strType+"_data where expires<=?" 132 ,new SQLItem[]{ 133 new SQLItemImpl(System.currentTimeMillis(),Types.VARCHAR) 134 }); 135 QueryImpl query; 136 try{ 137 query = new QueryImpl(ThreadLocalPageContext.get(),dc,sqlSelect,-1,-1,-1,"query"); 138 } 139 catch(Throwable t){ 140 // possible that the table not exist, if not there is nothing to clean 141 return; 142 } 143 144 int recordcount=query.getRecordcount(); 145 146 String cfid,name; 147 for(int row=1;row<=recordcount;row++){ 148 cfid=Caster.toString(query.getAt(KeyConstants._cfid, row, null),null); 149 name=Caster.toString(query.getAt(KeyConstants._name, row, null),null); 150 151 if(listener!=null)listener.doEnd(engine, cleaner,name, cfid); 152 153 154 ScopeContext.info(log,"remove "+strType+"/"+name+"/"+cfid+" from datasource "+dc.getDatasource().getName()); 155 engine.remove(type,name,cfid); 156 SQLImpl sql = new SQLImpl("delete from "+StorageScopeDatasource.PREFIX+"_"+strType+"_data where cfid=? and name=?",new SQLItem[]{ 157 new SQLItemImpl(cfid,Types.VARCHAR), 158 new SQLItemImpl(name,Types.VARCHAR) 159 }); 160 new QueryImpl(ThreadLocalPageContext.get(),dc,sql,-1,-1,-1,"query"); 161 162 163 164 } 165 } 166 167 168 private static int execute(Connection conn, SQLImpl sql, TimeZone tz) throws SQLException, PageException { 169 PreparedStatement preStat = conn.prepareStatement(sql.getSQLString()); 170 int count=0; 171 try { 172 SQLItem[] items=sql.getItems(); 173 for(int i=0;i<items.length;i++) { 174 SQLCaster.setValue(tz,preStat,i+1,items[i]); 175 } 176 count= preStat.executeUpdate(); 177 } 178 finally { 179 preStat.close(); 180 } 181 return count; 182 } 183 184 private static SQL createSQL(DatasourceConnection dc, String textType, String type) { 185 String clazz = dc.getDatasource().getClazz().getName(); 186 187 boolean isMSSQL= 188 clazz.equals("com.microsoft.jdbc.sqlserver.SQLServerDriver") || 189 clazz.equals("net.sourceforge.jtds.jdbc.Driver"); 190 boolean isHSQLDB= 191 clazz.equals("org.hsqldb.jdbcDriver"); 192 boolean isOracle= 193 clazz.indexOf("OracleDriver")!=-1; 194 195 StringBuffer sb=new StringBuffer("CREATE TABLE "); 196 197 if(isMSSQL)sb.append("dbo."); 198 sb.append(PREFIX+"_"+type+"_data ("); 199 200 // expires 201 sb.append("expires varchar(64) NOT NULL, "); 202 // cfid 203 sb.append("cfid varchar(64) NOT NULL, "); 204 // name 205 sb.append("name varchar(255) NOT NULL, "); 206 // data 207 sb.append("data "); 208 if(isHSQLDB)sb.append("varchar "); 209 else if(isOracle)sb.append("CLOB "); 210 else sb.append(textType+" "); 211 sb.append(" NOT NULL"); 212 213 sb.append(")"); 214 return new SQLImpl(sb.toString()); 215 } 216 }