package railo.runtime.type.util;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.sql.Time;
import java.util.Date;
import railo.commons.lang.FormatUtil;
import railo.commons.lang.SizeOf;
import railo.commons.lang.StringUtil;
import railo.commons.sql.SQLUtil;
import railo.runtime.PageContext;
import railo.runtime.config.NullSupportHelper;
import railo.runtime.db.DataSource;
import railo.runtime.db.DatasourceConnection;
import railo.runtime.db.SQL;
import railo.runtime.db.driver.PreparedStatementPro;
import railo.runtime.db.driver.StatementPro;
import railo.runtime.dump.DumpData;
import railo.runtime.dump.DumpProperties;
import railo.runtime.dump.DumpRow;
import railo.runtime.dump.DumpTable;
import railo.runtime.dump.DumpUtil;
import railo.runtime.dump.SimpleDumpData;
import railo.runtime.exp.DatabaseException;
import railo.runtime.exp.PageException;
import railo.runtime.exp.PageRuntimeException;
import railo.runtime.functions.arrays.ArrayFind;
import railo.runtime.op.Caster;
import railo.runtime.type.Array;
import railo.runtime.type.ArrayImpl;
import railo.runtime.type.Collection;
import railo.runtime.type.Collection.Key;
import railo.runtime.type.KeyImpl;
import railo.runtime.type.Query;
import railo.runtime.type.QueryColumn;
import railo.runtime.type.QueryColumnImpl;
import railo.runtime.type.QueryImpl;
import railo.runtime.type.query.SimpleQuery;
public class QueryUtil {
public static long sizeOf(QueryColumn column) {
if(column instanceof QueryColumnImpl){
return ((QueryColumnImpl)column).sizeOf();
}
int len = column.size();
long size=0;
for(int i=1;i<=len;i++){
size+=SizeOf.size(column.get(i,null));
}
return size;
}
/**
* return column names as Key from a query
*
* @param qry
* @return
*/
public static Key[] getColumnNames(Query qry) {
Query qp = Caster.toQuery(qry,null);
if(qp!=null) return qp.getColumnNames();
String[] strNames = qry.getColumns();
Key[] names=new Key[strNames.length];
for(int i=0;i<names.length;i++){
names[i]=KeyImpl.getInstance(strNames[i]);
}
return names;
}
public static String[] toStringArray(Collection.Key[] keys) {
if(keys==null) return new String[0];
String[] strKeys=new String[keys.length];
for(int i=0 ;i<keys.length;i++) {
strKeys[i]=keys[i].getString();
}
return strKeys;
}
/**
* check if there is a sql restriction
* @param dc
* @param sql
* @throws PageException
*/
public static void checkSQLRestriction(DatasourceConnection dc, SQL sql) throws PageException {
Array sqlparts = ListUtil.listToArrayRemoveEmpty(
SQLUtil.removeLiterals(sql.getSQLString())
," \t"+System.getProperty("line.separator"));
//print.ln(List.toStringArray(sqlparts));
DataSource ds = dc.getDatasource();
if(!ds.hasAllow(DataSource.ALLOW_ALTER)) checkSQLRestriction(dc,"alter",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_CREATE)) checkSQLRestriction(dc,"create",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_DELETE)) checkSQLRestriction(dc,"delete",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_DROP)) checkSQLRestriction(dc,"drop",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_GRANT)) checkSQLRestriction(dc,"grant",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_INSERT)) checkSQLRestriction(dc,"insert",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_REVOKE)) checkSQLRestriction(dc,"revoke",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_SELECT)) checkSQLRestriction(dc,"select",sqlparts,sql);
if(!ds.hasAllow(DataSource.ALLOW_UPDATE)) checkSQLRestriction(dc,"update",sqlparts,sql);
}
private static void checkSQLRestriction(DatasourceConnection dc, String keyword, Array sqlparts, SQL sql) throws PageException {
if(ArrayFind.find(sqlparts,keyword,false)>0) {
throw new DatabaseException("access denied to execute \""+StringUtil.ucFirst(keyword)+"\" SQL statement for datasource "+dc.getDatasource().getName(),null,sql,dc);
}
}
public static DumpData toDumpData(Query query,PageContext pageContext, int maxlevel, DumpProperties dp) {
maxlevel--;
Collection.Key[] keys=CollectionUtil.keys(query);
DumpData[] heads=new DumpData[keys.length+1];
//int tmp=1;
heads[0]=new SimpleDumpData("");
for(int i=0;i<keys.length;i++) {
heads[i+1]=new SimpleDumpData(keys[i].getString());
}
StringBuilder comment=new StringBuilder();
//table.appendRow(1, new SimpleDumpData("SQL"), new SimpleDumpData(sql.toString()));
String template=query.getTemplate();
if(!StringUtil.isEmpty(template))
comment.append("Template: ").append(template).append("\n");
//table.appendRow(1, new SimpleDumpData("Template"), new SimpleDumpData(template));
int top = dp.getMaxlevel(); // in Query dump maxlevel is used as Top
comment.append("Execution Time: ").append(Caster.toString(FormatUtil.formatNSAsMSDouble(query.getExecutionTime()))).append(" ms \n");
comment.append("Record Count: ").append(Caster.toString(query.getRecordcount()));
if ( query.getRecordcount() > top )
comment.append( " (showing top " ).append( Caster.toString( top ) ).append( ")" );
comment.append("\n");
comment.append("Cached: ").append(query.isCached()?"Yes\n":"No\n");
comment.append("Lazy: ").append(query instanceof SimpleQuery?"Yes\n":"No\n");
SQL sql=query.getSql();
if(sql!=null)
comment.append("SQL: ").append("\n").append(StringUtil.suppressWhiteSpace(sql.toString().trim())).append("\n");
//table.appendRow(1, new SimpleDumpData("Execution Time (ms)"), new SimpleDumpData(exeTime));
//table.appendRow(1, new SimpleDumpData("recordcount"), new SimpleDumpData(getRecordcount()));
//table.appendRow(1, new SimpleDumpData("cached"), new SimpleDumpData(isCached()?"Yes":"No"));
DumpTable recs=new DumpTable("query","#cc99cc","#ffccff","#000000");
recs.setTitle("Query");
if(dp.getMetainfo())recs.setComment(comment.toString());
recs.appendRow(new DumpRow(-1,heads));
// body
DumpData[] items;
int recordcount=query.getRecordcount();
int columncount=query.getColumnNames().length;
for(int i=0;i<recordcount;i++) {
items=new DumpData[columncount+1];
items[0]=new SimpleDumpData(i+1);
for(int y=0;y<keys.length;y++) {
try {
Object o=query.getAt(keys[y],i+1);
if(o instanceof String)items[y+1]=new SimpleDumpData(o.toString());
else if(o instanceof Number) items[y+1]=new SimpleDumpData(Caster.toString(((Number)o)));
else if(o instanceof Boolean) items[y+1]=new SimpleDumpData(((Boolean)o).booleanValue());
else if(o instanceof Date) items[y+1]=new SimpleDumpData(Caster.toString(o));
else if(o instanceof Clob) items[y+1]=new SimpleDumpData(Caster.toString(o));
else items[y+1]=DumpUtil.toDumpData(o, pageContext,maxlevel,dp);
} catch (PageException e) {
items[y+1]=new SimpleDumpData("[empty]");
}
}
recs.appendRow(new DumpRow(1,items));
if ( i == top - 1 )
break;
}
if(!dp.getMetainfo()) return recs;
//table.appendRow(1, new SimpleDumpData("result"), recs);
return recs;
}
public static void removeRows(Query query, int index, int count) throws PageException {
if(query.getRecordcount()==0)
throw new DatabaseException("cannot remove rows, query is empty",null,null,null);
if(index<0 || index>=query.getRecordcount())
throw new DatabaseException("invalid index ["+index+"], index must be between 0 and "+(query.getRecordcount()-1),null,null,null);
if(index+count>query.getRecordcount())
throw new DatabaseException("invalid count ["+count+"], count+index ["+(count+index)+"] must less or equal to "+(query.getRecordcount()),null,null,null);
for(int row=count;row>=1;row--){
query.removeRow(index+row);
}
}
public static boolean execute(PageContext pc,Statement stat, boolean createGeneratedKeys, SQL sql) throws SQLException {
if(stat instanceof StatementPro) {
StatementPro sp=(StatementPro) stat;
return createGeneratedKeys?sp.execute(pc,sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):sp.execute(pc,sql.getSQLString());
}
return createGeneratedKeys?stat.execute(sql.getSQLString(),Statement.RETURN_GENERATED_KEYS):stat.execute(sql.getSQLString());
}
public static boolean execute(PageContext pc,PreparedStatement ps) throws SQLException {
if(ps instanceof PreparedStatementPro) {
PreparedStatementPro psp=(PreparedStatementPro) ps;
return psp.execute(pc);
}
return ps.execute();
}
public static String getColumnName(ResultSetMetaData meta, int column) throws SQLException {
try {
return meta.getColumnLabel(column);
} catch (SQLException e) {
return meta.getColumnName(column);
}
}
public static Object getObject(ResultSet rs,int columnIndex, Class type) throws SQLException {
if(BigDecimal.class==type) return rs.getBigDecimal(columnIndex);
if(Blob.class==type) return rs.getBlob(columnIndex);
if(boolean.class==type || Boolean.class==type) return rs.getBoolean(columnIndex);
if(byte.class==type || Byte.class==type) return rs.getByte(columnIndex);
if(Clob.class==type) return rs.getClob(columnIndex);
if(Date.class==type) return rs.getDate(columnIndex);
if(double.class==type || Double.class==type) return rs.getDouble(columnIndex);
if(float.class==type || Float.class==type) return rs.getFloat(columnIndex);
if(int.class==type || Integer.class==type) return rs.getInt(columnIndex);
if(long.class==type || Long.class==type) return rs.getLong(columnIndex);
if(short.class==type || Short.class==type) return rs.getShort(columnIndex);
if(String.class==type) return rs.getString(columnIndex);
if(Time.class==type) return rs.getTime(columnIndex);
if(Ref.class==type) return rs.getRef(columnIndex);
throw new SQLFeatureNotSupportedException("type ["+type.getName()+"] is not supported");
}
public static Object getObject(ResultSet rs,String columnLabel, Class type) throws SQLException {
if(BigDecimal.class==type) return rs.getBigDecimal(columnLabel);
if(Blob.class==type) return rs.getBlob(columnLabel);
if(boolean.class==type || Boolean.class==type) return rs.getBoolean(columnLabel);
if(byte.class==type || Byte.class==type) return rs.getByte(columnLabel);
if(Clob.class==type) return rs.getClob(columnLabel);
if(Date.class==type) return rs.getDate(columnLabel);
if(double.class==type || Double.class==type) return rs.getDouble(columnLabel);
if(float.class==type || Float.class==type) return rs.getFloat(columnLabel);
if(int.class==type || Integer.class==type) return rs.getInt(columnLabel);
if(long.class==type || Long.class==type) return rs.getLong(columnLabel);
if(short.class==type || Short.class==type) return rs.getShort(columnLabel);
if(String.class==type) return rs.getString(columnLabel);
if(Time.class==type) return rs.getTime(columnLabel);
if(Ref.class==type) return rs.getRef(columnLabel);
throw new SQLFeatureNotSupportedException("type ["+type.getName()+"] is not supported");
}
/**
* return the value at the given position (row), returns the default empty value ("" or null) for wrong row or null values.
* this method only exist for backward compatibility and should not be used for new functinality
* @param column
* @param row
* @return
* @deprecated use instead QueryColumn.get(int,Object)
*/
public static Object getValue(QueryColumn column, int row) {//print.ds();
if(NullSupportHelper.full()) return column.get(row, null);
Object v = column.get(row, "");
return v==null?"":v;
}
public static QueryColumnImpl duplicate2QueryColumnImpl(QueryImpl targetQuery,QueryColumn col, boolean deepCopy) {
if(col instanceof QueryColumnImpl)
return ((QueryColumnImpl)col).cloneColumnImpl(deepCopy);
// fill array for column
Array content=new ArrayImpl();
int len=col.size();
for(int i=1;i<=len;i++){
content.setEL(i, col.get(i,null));
}
// create and return column
try {
return new QueryColumnImpl(targetQuery,col.getKey(),content,col.getType());
} catch (PageException e) {
throw new PageRuntimeException(e);
}
}
}