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}