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