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