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}