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