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    }