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