001 package railo.runtime.type.util; 002 003 import java.sql.Clob; 004 import java.util.Date; 005 006 import org.hibernate.QueryException; 007 008 import railo.commons.lang.SizeOf; 009 import railo.commons.lang.StringUtil; 010 import railo.commons.sql.SQLUtil; 011 import railo.runtime.PageContext; 012 import railo.runtime.db.DataSource; 013 import railo.runtime.db.DatasourceConnection; 014 import railo.runtime.db.SQL; 015 import railo.runtime.dump.DumpData; 016 import railo.runtime.dump.DumpProperties; 017 import railo.runtime.dump.DumpRow; 018 import railo.runtime.dump.DumpTable; 019 import railo.runtime.dump.DumpTablePro; 020 import railo.runtime.dump.DumpUtil; 021 import railo.runtime.dump.SimpleDumpData; 022 import railo.runtime.exp.DatabaseException; 023 import railo.runtime.exp.PageException; 024 import railo.runtime.functions.arrays.ArrayFind; 025 import railo.runtime.op.Caster; 026 import railo.runtime.type.Array; 027 import railo.runtime.type.Collection; 028 import railo.runtime.type.Collection.Key; 029 import railo.runtime.type.KeyImpl; 030 import railo.runtime.type.List; 031 import railo.runtime.type.Query; 032 import railo.runtime.type.QueryColumn; 033 import railo.runtime.type.QueryColumnPro; 034 import railo.runtime.type.QueryPro; 035 import railo.runtime.type.query.SimpleQuery; 036 037 public class QueryUtil { 038 039 public static long sizeOf(QueryColumn column) { 040 if(column instanceof QueryColumnPro){ 041 return ((QueryColumnPro)column).sizeOf(); 042 } 043 int len = column.size(); 044 long size=0; 045 for(int i=1;i<=len;i++){ 046 size+=SizeOf.size(column.get(i,null)); 047 } 048 return size; 049 } 050 051 /** 052 * return column names as Key from a query 053 * 054 * @param qry 055 * @return 056 */ 057 public static Key[] getColumnNames(Query qry) { 058 QueryPro qp = Caster.toQueryPro(qry,null); 059 060 if(qp!=null) return qp.getColumnNames(); 061 String[] strNames = qry.getColumns(); 062 Key[] names=new Key[strNames.length]; 063 for(int i=0;i<names.length;i++){ 064 names[i]=KeyImpl.getInstance(strNames[i]); 065 } 066 return names; 067 } 068 069 public static String[] toStringArray(Collection.Key[] keys) { 070 if(keys==null) return new String[0]; 071 String[] strKeys=new String[keys.length]; 072 for(int i=0 ;i<keys.length;i++) { 073 strKeys[i]=keys[i].getString(); 074 } 075 return strKeys; 076 } 077 078 /** 079 * check if there is a sql restriction 080 * @param ds 081 * @param sql 082 * @throws PageException 083 */ 084 public static void checkSQLRestriction(DatasourceConnection dc, SQL sql) throws PageException { 085 Array sqlparts = List.listToArrayRemoveEmpty( 086 SQLUtil.removeLiterals(sql.getSQLString()) 087 ," \t"+System.getProperty("line.separator")); 088 089 090 091 //print.ln(List.toStringArray(sqlparts)); 092 DataSource ds = dc.getDatasource(); 093 if(!ds.hasAllow(DataSource.ALLOW_ALTER)) checkSQLRestriction(dc,"alter",sqlparts,sql); 094 if(!ds.hasAllow(DataSource.ALLOW_CREATE)) checkSQLRestriction(dc,"create",sqlparts,sql); 095 if(!ds.hasAllow(DataSource.ALLOW_DELETE)) checkSQLRestriction(dc,"delete",sqlparts,sql); 096 if(!ds.hasAllow(DataSource.ALLOW_DROP)) checkSQLRestriction(dc,"drop",sqlparts,sql); 097 if(!ds.hasAllow(DataSource.ALLOW_GRANT)) checkSQLRestriction(dc,"grant",sqlparts,sql); 098 if(!ds.hasAllow(DataSource.ALLOW_INSERT)) checkSQLRestriction(dc,"insert",sqlparts,sql); 099 if(!ds.hasAllow(DataSource.ALLOW_REVOKE)) checkSQLRestriction(dc,"revoke",sqlparts,sql); 100 if(!ds.hasAllow(DataSource.ALLOW_SELECT)) checkSQLRestriction(dc,"select",sqlparts,sql); 101 if(!ds.hasAllow(DataSource.ALLOW_UPDATE)) checkSQLRestriction(dc,"update",sqlparts,sql); 102 103 } 104 105 private static void checkSQLRestriction(DatasourceConnection dc, String keyword, Array sqlparts, SQL sql) throws PageException { 106 if(ArrayFind.find(sqlparts,keyword,false)>0) { 107 throw new DatabaseException("access denied to execute \""+StringUtil.ucFirst(keyword)+"\" SQL statment for datasource "+dc.getDatasource().getName(),null,sql,dc); 108 } 109 } 110 111 public static DumpData toDumpData(QueryPro query,PageContext pageContext, int maxlevel, DumpProperties dp) { 112 maxlevel--; 113 Collection.Key[] keys=query.keys(); 114 DumpData[] heads=new DumpData[keys.length+1]; 115 //int tmp=1; 116 heads[0]=new SimpleDumpData(""); 117 for(int i=0;i<keys.length;i++) { 118 heads[i+1]=new SimpleDumpData(keys[i].getString()); 119 } 120 121 StringBuilder comment=new StringBuilder(); 122 123 //table.appendRow(1, new SimpleDumpData("SQL"), new SimpleDumpData(sql.toString())); 124 String template=query.getTemplate(); 125 if(!StringUtil.isEmpty(template)) 126 comment.append("Template:").append(template).append("\n"); 127 //table.appendRow(1, new SimpleDumpData("Template"), new SimpleDumpData(template)); 128 129 comment.append("Execution Time (ms):").append(Caster.toString(query.getExecutionTime())).append("\n"); 130 comment.append("Recordcount:").append(Caster.toString(query.getRecordcount())).append("\n"); 131 comment.append("Cached:").append(query.isCached()?"Yes\n":"No\n"); 132 comment.append("Lazy:").append(query instanceof SimpleQuery?"Yes\n":"No\n"); 133 134 SQL sql=query.getSql(); 135 if(sql!=null) 136 comment.append("SQL:").append("\n").append(StringUtil.suppressWhiteSpace(sql.toString().trim())).append("\n"); 137 138 //table.appendRow(1, new SimpleDumpData("Execution Time (ms)"), new SimpleDumpData(exeTime)); 139 //table.appendRow(1, new SimpleDumpData("recordcount"), new SimpleDumpData(getRecordcount())); 140 //table.appendRow(1, new SimpleDumpData("cached"), new SimpleDumpData(isCached()?"Yes":"No")); 141 142 143 144 DumpTable recs=new DumpTablePro("query","#cc99cc","#ffccff","#000000"); 145 recs.setTitle("Query"); 146 if(dp.getMetainfo())recs.setComment(comment.toString()); 147 recs.appendRow(new DumpRow(-1,heads)); 148 149 // body 150 DumpData[] items; 151 int recordcount=query.getRecordcount(); 152 int columncount=query.getColumnNames().length; 153 for(int i=0;i<recordcount;i++) { 154 items=new DumpData[columncount+1]; 155 items[0]=new SimpleDumpData(i+1); 156 for(int y=0;y<keys.length;y++) { 157 try { 158 Object o=query.getAt(keys[y],i+1); 159 if(o instanceof String)items[y+1]=new SimpleDumpData(o.toString()); 160 else if(o instanceof Number) items[y+1]=new SimpleDumpData(Caster.toString(((Number)o).doubleValue())); 161 else if(o instanceof Boolean) items[y+1]=new SimpleDumpData(((Boolean)o).booleanValue()); 162 else if(o instanceof Date) items[y+1]=new SimpleDumpData(Caster.toString(o)); 163 else if(o instanceof Clob) items[y+1]=new SimpleDumpData(Caster.toString(o)); 164 else items[y+1]=DumpUtil.toDumpData(o, pageContext,maxlevel,dp); 165 } catch (PageException e) { 166 items[y+1]=new SimpleDumpData("[empty]"); 167 } 168 } 169 recs.appendRow(new DumpRow(1,items)); 170 } 171 if(!dp.getMetainfo()) return recs; 172 173 //table.appendRow(1, new SimpleDumpData("result"), recs); 174 return recs; 175 } 176 177 public static void removeRows(Query query, int index, int count) throws PageException { 178 if(query.getRecordcount()==0) 179 throw new QueryException("cannot remove rows, query is empty"); 180 if(index<0 || index>=query.getRecordcount()) 181 throw new QueryException("invalid index ["+index+"], index must be between 0 and "+(query.getRecordcount()-1)); 182 if(index+count>query.getRecordcount()) 183 throw new QueryException("invalid count ["+count+"], count+index ["+(count+index)+"] must less or equal to "+(query.getRecordcount())); 184 185 for(int row=count;row>=1;row--){ 186 query.removeRow(index+row); 187 } 188 } 189 }