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