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.type.util; 020 021import java.math.BigDecimal; 022import java.sql.Blob; 023import java.sql.Clob; 024import java.sql.PreparedStatement; 025import java.sql.Ref; 026import java.sql.ResultSet; 027import java.sql.ResultSetMetaData; 028import java.sql.SQLException; 029import java.sql.SQLFeatureNotSupportedException; 030import java.sql.Statement; 031import java.sql.Time; 032import java.util.Date; 033 034import lucee.commons.lang.FormatUtil; 035import lucee.commons.lang.SizeOf; 036import lucee.commons.lang.StringUtil; 037import lucee.commons.sql.SQLUtil; 038import lucee.runtime.PageContext; 039import lucee.runtime.config.NullSupportHelper; 040import lucee.runtime.db.DataSource; 041import lucee.runtime.db.DatasourceConnection; 042import lucee.runtime.db.SQL; 043import lucee.runtime.db.driver.PreparedStatementPro; 044import lucee.runtime.db.driver.StatementPro; 045import lucee.runtime.dump.DumpData; 046import lucee.runtime.dump.DumpProperties; 047import lucee.runtime.dump.DumpRow; 048import lucee.runtime.dump.DumpTable; 049import lucee.runtime.dump.DumpUtil; 050import lucee.runtime.dump.SimpleDumpData; 051import lucee.runtime.exp.DatabaseException; 052import lucee.runtime.exp.PageException; 053import lucee.runtime.exp.PageRuntimeException; 054import lucee.runtime.functions.arrays.ArrayFind; 055import lucee.runtime.op.Caster; 056import lucee.runtime.type.Array; 057import lucee.runtime.type.ArrayImpl; 058import lucee.runtime.type.Collection; 059import lucee.runtime.type.Collection.Key; 060import lucee.runtime.type.KeyImpl; 061import lucee.runtime.type.Query; 062import lucee.runtime.type.QueryColumn; 063import lucee.runtime.type.QueryColumnImpl; 064import lucee.runtime.type.QueryImpl; 065import lucee.runtime.type.query.SimpleQuery; 066 067public class QueryUtil { 068 069 public static long sizeOf(QueryColumn column) { 070 if(column instanceof QueryColumnImpl){ 071 return ((QueryColumnImpl)column).sizeOf(); 072 } 073 int len = column.size(); 074 long size=0; 075 for(int i=1;i<=len;i++){ 076 size+=SizeOf.size(column.get(i,null)); 077 } 078 return size; 079 } 080 081 /** 082 * return column names as Key from a query 083 * 084 * @param qry 085 * @return 086 */ 087 public static Key[] getColumnNames(Query qry) { 088 Query qp = Caster.toQuery(qry,null); 089 090 if(qp!=null) return qp.getColumnNames(); 091 String[] strNames = qry.getColumns(); 092 Key[] names=new Key[strNames.length]; 093 for(int i=0;i<names.length;i++){ 094 names[i]=KeyImpl.getInstance(strNames[i]); 095 } 096 return names; 097 } 098 099 public static String[] toStringArray(Collection.Key[] keys) { 100 if(keys==null) return new String[0]; 101 String[] strKeys=new String[keys.length]; 102 for(int i=0 ;i<keys.length;i++) { 103 strKeys[i]=keys[i].getString(); 104 } 105 return strKeys; 106 } 107 108 /** 109 * check if there is a sql restriction 110 * @param dc 111 * @param sql 112 * @throws PageException 113 */ 114 public static void checkSQLRestriction(DatasourceConnection dc, SQL sql) throws PageException { 115 Array sqlparts = ListUtil.listToArrayRemoveEmpty( 116 SQLUtil.removeLiterals(sql.getSQLString()) 117 ," \t"+System.getProperty("line.separator")); 118 119 //print.ln(List.toStringArray(sqlparts)); 120 DataSource ds = dc.getDatasource(); 121 if(!ds.hasAllow(DataSource.ALLOW_ALTER)) checkSQLRestriction(dc,"alter",sqlparts,sql); 122 if(!ds.hasAllow(DataSource.ALLOW_CREATE)) checkSQLRestriction(dc,"create",sqlparts,sql); 123 if(!ds.hasAllow(DataSource.ALLOW_DELETE)) checkSQLRestriction(dc,"delete",sqlparts,sql); 124 if(!ds.hasAllow(DataSource.ALLOW_DROP)) checkSQLRestriction(dc,"drop",sqlparts,sql); 125 if(!ds.hasAllow(DataSource.ALLOW_GRANT)) checkSQLRestriction(dc,"grant",sqlparts,sql); 126 if(!ds.hasAllow(DataSource.ALLOW_INSERT)) checkSQLRestriction(dc,"insert",sqlparts,sql); 127 if(!ds.hasAllow(DataSource.ALLOW_REVOKE)) checkSQLRestriction(dc,"revoke",sqlparts,sql); 128 if(!ds.hasAllow(DataSource.ALLOW_SELECT)) checkSQLRestriction(dc,"select",sqlparts,sql); 129 if(!ds.hasAllow(DataSource.ALLOW_UPDATE)) checkSQLRestriction(dc,"update",sqlparts,sql); 130 } 131 132 133 private static void checkSQLRestriction(DatasourceConnection dc, String keyword, Array sqlparts, SQL sql) throws PageException { 134 if(ArrayFind.find(sqlparts,keyword,false)>0) { 135 throw new DatabaseException("access denied to execute \""+StringUtil.ucFirst(keyword)+"\" SQL statement for datasource "+dc.getDatasource().getName(),null,sql,dc); 136 } 137 } 138 139 public static DumpData toDumpData(Query query,PageContext pageContext, int maxlevel, DumpProperties dp) { 140 maxlevel--; 141 Collection.Key[] keys=CollectionUtil.keys(query); 142 DumpData[] heads=new DumpData[keys.length+1]; 143 //int tmp=1; 144 heads[0]=new SimpleDumpData(""); 145 for(int i=0;i<keys.length;i++) { 146 heads[i+1]=new SimpleDumpData(keys[i].getString()); 147 } 148 149 StringBuilder comment=new StringBuilder(); 150 151 //table.appendRow(1, new SimpleDumpData("SQL"), new SimpleDumpData(sql.toString())); 152 String template=query.getTemplate(); 153 if(!StringUtil.isEmpty(template)) 154 comment.append("Template: ").append(template).append("\n"); 155 //table.appendRow(1, new SimpleDumpData("Template"), new SimpleDumpData(template)); 156 157 int top = dp.getMaxlevel(); // in Query dump maxlevel is used as Top 158 159 comment.append("Execution Time: ").append(Caster.toString(FormatUtil.formatNSAsMSDouble(query.getExecutionTime()))).append(" ms \n"); 160 comment.append("Record Count: ").append(Caster.toString(query.getRecordcount())); 161 if ( query.getRecordcount() > top ) 162 comment.append( " (showing top " ).append( Caster.toString( top ) ).append( ")" ); 163 comment.append("\n"); 164 comment.append("Cached: ").append(query.isCached()?"Yes\n":"No\n"); 165 if(query.isCached() && query instanceof QueryImpl) { 166 String ct=((QueryImpl)query).getCacheType(); 167 comment.append("Cache Type: ").append(ct).append("\n"); 168 } 169 170 comment.append("Lazy: ").append(query instanceof SimpleQuery?"Yes\n":"No\n"); 171 172 SQL sql=query.getSql(); 173 if(sql!=null) 174 comment.append("SQL: ").append("\n").append(StringUtil.suppressWhiteSpace(sql.toString().trim())).append("\n"); 175 176 //table.appendRow(1, new SimpleDumpData("Execution Time (ms)"), new SimpleDumpData(exeTime)); 177 //table.appendRow(1, new SimpleDumpData("recordcount"), new SimpleDumpData(getRecordcount())); 178 //table.appendRow(1, new SimpleDumpData("cached"), new SimpleDumpData(isCached()?"Yes":"No")); 179 180 DumpTable recs=new DumpTable("query","#cc99cc","#ffccff","#000000"); 181 recs.setTitle("Query"); 182 if(dp.getMetainfo())recs.setComment(comment.toString()); 183 recs.appendRow(new DumpRow(-1,heads)); 184 185 // body 186 DumpData[] items; 187 int recordcount=query.getRecordcount(); 188 int columncount=query.getColumnNames().length; 189 for(int i=0;i<recordcount;i++) { 190 items=new DumpData[columncount+1]; 191 items[0]=new SimpleDumpData(i+1); 192 for(int y=0;y<keys.length;y++) { 193 try { 194 Object o=query.getAt(keys[y],i+1); 195 if(o instanceof String)items[y+1]=new SimpleDumpData(o.toString()); 196 else if(o instanceof Number) items[y+1]=new SimpleDumpData(Caster.toString(((Number)o))); 197 else if(o instanceof Boolean) items[y+1]=new SimpleDumpData(((Boolean)o).booleanValue()); 198 else if(o instanceof Date) items[y+1]=new SimpleDumpData(Caster.toString(o)); 199 else if(o instanceof Clob) items[y+1]=new SimpleDumpData(Caster.toString(o)); 200 else items[y+1]=DumpUtil.toDumpData(o, pageContext,maxlevel,dp); 201 } catch (PageException e) { 202 items[y+1]=new SimpleDumpData("[empty]"); 203 } 204 } 205 recs.appendRow(new DumpRow(1,items)); 206 207 if ( i == top - 1 ) 208 break; 209 } 210 if(!dp.getMetainfo()) return recs; 211 212 //table.appendRow(1, new SimpleDumpData("result"), recs); 213 return recs; 214 } 215 216 public static void removeRows(Query query, int index, int count) throws PageException { 217 if(query.getRecordcount()==0) 218 throw new DatabaseException("cannot remove rows, query is empty",null,null,null); 219 if(index<0 || index>=query.getRecordcount()) 220 throw new DatabaseException("invalid index ["+index+"], index must be between 0 and "+(query.getRecordcount()-1),null,null,null); 221 if(index+count>query.getRecordcount()) 222 throw new DatabaseException("invalid count ["+count+"], count+index ["+(count+index)+"] must less or equal to "+(query.getRecordcount()),null,null,null); 223 224 for(int row=count;row>=1;row--){ 225 query.removeRow(index+row); 226 } 227 } 228 229 public static boolean execute(PageContext pc,Statement stat, boolean createGeneratedKeys, SQL sql) throws SQLException { 230 if(stat instanceof StatementPro) { 231 StatementPro sp=(StatementPro) stat; 232 return createGeneratedKeys?sp.execute(pc,sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):sp.execute(pc,sql.getSQLString()); 233 } 234 return createGeneratedKeys?stat.execute(sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):stat.execute(sql.getSQLString()); 235 } 236 237 public static boolean execute(PageContext pc,PreparedStatement ps) throws SQLException { 238 if(ps instanceof PreparedStatementPro) { 239 PreparedStatementPro psp=(PreparedStatementPro) ps; 240 return psp.execute(pc); 241 } 242 return ps.execute(); 243 } 244 245 public static String getColumnName(ResultSetMetaData meta, int column) throws SQLException { 246 try { 247 return meta.getColumnLabel(column); 248 } catch (SQLException e) { 249 return meta.getColumnName(column); 250 } 251 } 252 253 public static Object getObject(ResultSet rs,int columnIndex, Class type) throws SQLException { 254 if(BigDecimal.class==type) return rs.getBigDecimal(columnIndex); 255 if(Blob.class==type) return rs.getBlob(columnIndex); 256 if(boolean.class==type || Boolean.class==type) return rs.getBoolean(columnIndex); 257 if(byte.class==type || Byte.class==type) return rs.getByte(columnIndex); 258 if(Clob.class==type) return rs.getClob(columnIndex); 259 if(Date.class==type) return rs.getDate(columnIndex); 260 if(double.class==type || Double.class==type) return rs.getDouble(columnIndex); 261 if(float.class==type || Float.class==type) return rs.getFloat(columnIndex); 262 if(int.class==type || Integer.class==type) return rs.getInt(columnIndex); 263 if(long.class==type || Long.class==type) return rs.getLong(columnIndex); 264 if(short.class==type || Short.class==type) return rs.getShort(columnIndex); 265 if(String.class==type) return rs.getString(columnIndex); 266 if(Time.class==type) return rs.getTime(columnIndex); 267 if(Ref.class==type) return rs.getRef(columnIndex); 268 269 throw new SQLFeatureNotSupportedException("type ["+type.getName()+"] is not supported"); 270 } 271 272 public static Object getObject(ResultSet rs,String columnLabel, Class type) throws SQLException { 273 if(BigDecimal.class==type) return rs.getBigDecimal(columnLabel); 274 if(Blob.class==type) return rs.getBlob(columnLabel); 275 if(boolean.class==type || Boolean.class==type) return rs.getBoolean(columnLabel); 276 if(byte.class==type || Byte.class==type) return rs.getByte(columnLabel); 277 if(Clob.class==type) return rs.getClob(columnLabel); 278 if(Date.class==type) return rs.getDate(columnLabel); 279 if(double.class==type || Double.class==type) return rs.getDouble(columnLabel); 280 if(float.class==type || Float.class==type) return rs.getFloat(columnLabel); 281 if(int.class==type || Integer.class==type) return rs.getInt(columnLabel); 282 if(long.class==type || Long.class==type) return rs.getLong(columnLabel); 283 if(short.class==type || Short.class==type) return rs.getShort(columnLabel); 284 if(String.class==type) return rs.getString(columnLabel); 285 if(Time.class==type) return rs.getTime(columnLabel); 286 if(Ref.class==type) return rs.getRef(columnLabel); 287 288 throw new SQLFeatureNotSupportedException("type ["+type.getName()+"] is not supported"); 289 } 290 291 /** 292 * return the value at the given position (row), returns the default empty value ("" or null) for wrong row or null values. 293 * this method only exist for backward compatibility and should not be used for new functinality 294 * @param column 295 * @param row 296 * @return 297 * @deprecated use instead QueryColumn.get(int,Object) 298 */ 299 public static Object getValue(QueryColumn column, int row) {//print.ds(); 300 if(NullSupportHelper.full()) return column.get(row, null); 301 Object v = column.get(row, ""); 302 return v==null?"":v; 303 } 304 305 public static QueryColumnImpl duplicate2QueryColumnImpl(QueryImpl targetQuery,QueryColumn col, boolean deepCopy) { 306 if(col instanceof QueryColumnImpl) 307 return ((QueryColumnImpl)col).cloneColumnImpl(deepCopy); 308 309 // fill array for column 310 Array content=new ArrayImpl(); 311 int len=col.size(); 312 for(int i=1;i<=len;i++){ 313 content.setEL(i, col.get(i,null)); 314 } 315 316 // create and return column 317 try { 318 return new QueryColumnImpl(targetQuery,col.getKey(),content,col.getType()); 319 } catch (PageException e) { 320 throw new PageRuntimeException(e); 321 } 322 } 323}