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    }