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    }