001    package railo.runtime.tag;
002    
003    import java.sql.DatabaseMetaData;
004    import java.sql.SQLException;
005    import java.util.ArrayList;
006    
007    import railo.commons.lang.StringUtil;
008    import railo.runtime.config.ConfigImpl;
009    import railo.runtime.db.DataSource;
010    import railo.runtime.db.DataSourceManager;
011    import railo.runtime.db.DatasourceConnection;
012    import railo.runtime.db.SQL;
013    import railo.runtime.db.SQLImpl;
014    import railo.runtime.db.SQLItem;
015    import railo.runtime.db.SQLItemImpl;
016    import railo.runtime.debug.DebuggerImpl;
017    import railo.runtime.debug.DebuggerPro;
018    import railo.runtime.engine.ThreadLocalPageContext;
019    import railo.runtime.exp.DatabaseException;
020    import railo.runtime.exp.PageException;
021    import railo.runtime.ext.tag.TagImpl;
022    import railo.runtime.op.Caster;
023    import railo.runtime.type.QueryImpl;
024    import railo.runtime.type.Struct;
025    import railo.runtime.type.StructImpl;
026    import railo.runtime.type.scope.Form;
027    import railo.runtime.type.util.ArrayUtil;
028    import railo.runtime.type.util.CollectionUtil;
029    import railo.runtime.type.util.ListUtil;
030    
031    /**
032    * Updates existing records in data sources.
033    *
034    *
035    *
036    **/
037    public final class Update extends TagImpl {
038    
039            /** If specified, password overrides the password value specified in the ODBC setup. */
040            private String password;
041    
042            /** Name of the data source that contains a table. */
043            private String datasource;
044    
045            /** If specified, username overrides the username value specified in the ODBC setup. */
046            private String username;
047    
048            /** A comma-separated list of form fields to update. If this attribute is not specified, all fields 
049            **              in the form are included in the operation. */
050            private String formfields;
051    
052            /** For data sources that support table ownership, for example, SQL Server, Oracle, and Sybase 
053            **              SQL Anywhere, use this field to specify the owner of the table. */
054            private String tableowner;
055    
056            /** Name of the table you want to update. */
057            private String tablename;
058    
059            /** For data sources that support table qualifiers, use this field to specify the qualifier for the 
060            **              table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 
061            **              refers to the name of the database that contains the table. For the Intersolv dBase driver, the 
062            **              qualifier refers to the directory where the DBF files are located. */
063            private String tablequalifier;
064    
065            @Override
066            public void release()   {
067                    super.release();
068                    password=null;
069                    username=null;
070                    formfields=null;
071                    tableowner=null;
072                    tablequalifier=null;
073                    datasource=null;
074            }
075    
076            /** set the value password
077            *  If specified, password overrides the password value specified in the ODBC setup.
078            * @param password value to set
079            **/
080            public void setPassword(String password)        {
081                    this.password=password;
082            }
083    
084            /** set the value datasource
085            *  Name of the data source that contains a table.
086            * @param datasource value to set
087            **/
088            public void setDatasource(String datasource)    {
089                    this.datasource=datasource;
090            }
091    
092            /** set the value username
093            *  If specified, username overrides the username value specified in the ODBC setup.
094            * @param username value to set
095            **/
096            public void setUsername(String username)        {
097                    this.username=username;
098            }
099    
100            /** set the value formfields
101            *  A comma-separated list of form fields to update. If this attribute is not specified, all fields 
102            *               in the form are included in the operation.
103            * @param formfields value to set
104            **/
105            public void setFormfields(String formfields)    {
106                    this.formfields=formfields;
107            }
108    
109            /** set the value tableowner
110            *  For data sources that support table ownership, for example, SQL Server, Oracle, and Sybase 
111            *               SQL Anywhere, use this field to specify the owner of the table.
112            * @param tableowner value to set
113            **/
114            public void setTableowner(String tableowner)    {
115                    this.tableowner=tableowner;
116            }
117    
118            /** set the value tablename
119            *  Name of the table you want to update.
120            * @param tablename value to set
121            **/
122            public void setTablename(String tablename)      {
123                    this.tablename=tablename;
124            }
125    
126            /** set the value tablequalifier
127            *  For data sources that support table qualifiers, use this field to specify the qualifier for the 
128            *               table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 
129            *               refers to the name of the database that contains the table. For the Intersolv dBase driver, the 
130            *               qualifier refers to the directory where the DBF files are located.
131            * @param tablequalifier value to set
132            **/
133            public void setTablequalifier(String tablequalifier)    {
134                    this.tablequalifier=tablequalifier;
135            }
136    
137    
138            @Override
139            public int doStartTag() {
140                    return SKIP_BODY;
141            }
142    
143            @Override
144            public int doEndTag() throws PageException      {
145    
146                    Object ds=Insert.getDatasource(pageContext,datasource);
147                    
148                    DataSourceManager manager = pageContext.getDataSourceManager();
149                DatasourceConnection dc=ds instanceof DataSource?
150                            manager.getConnection(pageContext,(DataSource)ds,username,password):
151                            manager.getConnection(pageContext,Caster.toString(ds),username,password);
152                    try {
153                            
154                            Struct meta =null;
155                    try {
156                            meta=Insert.getMeta(dc,tablequalifier,tableowner,tablename);
157                    }
158                    catch(SQLException se){
159                            meta=new StructImpl();
160                    }
161                            
162                        String[] pKeys=getPrimaryKeys(dc);
163                            SQL sql=createSQL(dc,pKeys,meta);
164                            if(sql!=null) {
165                                    railo.runtime.type.Query query = new QueryImpl(pageContext,dc,sql,-1,-1,-1,"query");
166                                    
167                                    if(pageContext.getConfig().debug()) {
168                                            String dsn=ds instanceof DataSource?((DataSource)ds).getName():Caster.toString(ds);
169                                            boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE);
170                                            if(logdb){
171                                                    boolean debugUsage=DebuggerImpl.debugQueryUsage(pageContext,query);
172                                                    ((DebuggerPro)pageContext.getDebugger()).addQuery(debugUsage?query:null,dsn,"",sql,query.getRecordcount(),pageContext.getCurrentPageSource(),query.getExecutionTime());
173                                            }
174                                    }
175                            }
176                            return EVAL_PAGE;
177                    }
178                    finally {
179                            manager.releaseConnection(pageContext,dc);
180                    }
181            }
182    
183        private String[] getPrimaryKeys(DatasourceConnection dc) throws PageException {
184            railo.runtime.type.Query query = getPrimaryKeysAsQuery(dc);
185                    int recCount=query.getRecordcount();
186            String[] pKeys=new String[recCount];
187                    
188            if(recCount==0) throw new DatabaseException("can't find primary keys of table ["+tablename+"]",null,null,dc);
189            
190            for(int row=1;row<=recCount;row++) {
191                pKeys[row-1]=Caster.toString(query.getAt("column_name",row));
192            }
193    
194                    return pKeys;
195        }
196        
197        private railo.runtime.type.Query getPrimaryKeysAsQuery(DatasourceConnection dc) throws PageException {
198    
199            // Read Meta Data
200            DatabaseMetaData meta;
201            try {
202                meta = dc.getConnection().getMetaData();
203            } 
204            catch (SQLException e) {
205                throw new DatabaseException(e,dc);
206            }
207            
208            try {
209                return new QueryImpl(meta.getPrimaryKeys(tablequalifier, tableowner, tablename),-1,"query",pageContext.getTimeZone());
210            } 
211                    catch (SQLException e) {
212                        try {
213                            return new QueryImpl(meta.getBestRowIdentifier(tablequalifier, tableowner, tablename, 0, false),-1,"query",pageContext.getTimeZone());
214                } 
215                        catch (SQLException sqle) {
216                    throw new DatabaseException("can't find primary keys of table ["+tablename+"]",sqle,null,dc);
217                }
218            }
219        }
220    
221        /**
222         * @param keys primary Keys
223         * @return return SQL String for update
224         * @throws PageException
225         */
226        private SQL createSQL(DatasourceConnection dc,String[] keys, Struct meta) throws PageException {
227            String[] fields=null; 
228            Form form = pageContext.formScope();
229            if(formfields!=null) fields=ListUtil.toStringArray(ListUtil.listToArrayRemoveEmpty(formfields,','));
230            else fields=CollectionUtil.keysAsString(pageContext.formScope());
231            
232            StringBuffer set=new StringBuffer();
233            StringBuffer where=new StringBuffer();
234            ArrayList setItems=new ArrayList();
235            ArrayList whereItems=new ArrayList();
236            String field;
237            for(int i=0;i<fields.length;i++) {
238                field = StringUtil.trim(fields[i],null);
239                if(StringUtil.startsWithIgnoreCase(field, "form."))
240                    field=field.substring(5);
241                
242                if(!field.equalsIgnoreCase("fieldnames")) {
243                    if(ArrayUtil.indexOfIgnoreCase(keys,field)==-1) {
244                            if(set.length()==0) set.append(" set ");
245                            else set.append(",");
246                            set.append(field);
247                            set.append("=?");
248                            ColumnInfo ci=(ColumnInfo) meta.get(field);
249                            if(ci!=null)setItems.add(new SQLItemImpl(form.get(field,null),ci.getType())); 
250                            else setItems.add(new SQLItemImpl(form.get(field,null))); 
251                    }
252                    else {
253                            if(where.length()==0) where.append(" where ");
254                            else where.append(" and ");
255                            where.append(field);
256                            where.append("=?");
257                            whereItems.add(new SQLItemImpl(form.get(field,null))); 
258                    }
259                }
260            }
261            if((setItems.size()+whereItems.size())==0) return null;
262            
263            if(whereItems.size()==0)throw new DatabaseException("can't find primary keys ["+ListUtil.arrayToList(keys,",")+"] of table ["+tablename+"] in form scope",null,null,dc);
264            
265            StringBuffer sql=new StringBuffer();
266            sql.append("update ");
267            if(tablequalifier!=null && tablequalifier.length()>0) {
268                sql.append(tablequalifier);
269                sql.append('.');
270            }
271            if(tableowner!=null && tableowner.length()>0) {
272                sql.append(tableowner);
273                sql.append('.');
274            }
275            sql.append(tablename);
276            sql.append(set);
277            sql.append(where);
278            
279            return new SQLImpl(sql.toString(),arrayMerge(setItems,whereItems));
280        }
281    
282        private SQLItem[] arrayMerge(ArrayList setItems, ArrayList whereItems) {
283            SQLItem[] items=new SQLItem[setItems.size()+whereItems.size()];
284    
285            int index=0;
286            // Item
287            int size=setItems.size();
288            for(int i=0;i<size;i++) {
289                items[index++]=(SQLItem) setItems.get(i);
290            }
291            // Where
292            size=whereItems.size();
293            for(int i=0;i<size;i++) {
294                items[index++]=(SQLItem) whereItems.get(i);
295            }
296            return items;
297        }
298    }
299    
300    
301    
302    
303    
304    
305    
306    
307    
308    
309    
310    
311    
312    
313    
314