/* * Copyright (C) 2000 - 2008 TagServlet Ltd * * This file is part of Open BlueDragon (OpenBD) CFML Server Engine. * * OpenBD is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * Free Software Foundation,version 3. * * OpenBD is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with OpenBD. If not, see http://www.gnu.org/licenses/ * * Additional permission under GNU GPL version 3 section 7 * * If you modify this Program, or any covered work, by linking or combining * it with any of the JARS listed in the README.txt (or a modified version of * (that library), containing parts covered by the terms of that JAR, the * licensors of this Program grant you additional permission to convey the * resulting work. * README.txt @ http://www.openbluedragon.org/license/README.txt * * http://www.openbluedragon.org/ */ package com.naryx.tagfusion.cfm.queryofqueries; /** * This class represents a SQL select statement. * You can define the columns that will be selected using addSelectColumn() * and set the tables the select is from using setFromList() */ import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.Iterator; import java.util.List; import java.util.Map; import com.nary.util.FastMap; import com.naryx.tagfusion.cfm.engine.catchDataFactory; import com.naryx.tagfusion.cfm.engine.cfData; import com.naryx.tagfusion.cfm.engine.cfQueryResultData; import com.naryx.tagfusion.cfm.engine.cfSession; import com.naryx.tagfusion.cfm.engine.cfmRunTimeException; import com.naryx.tagfusion.cfm.parser.runTime; public class selectStatement{ private List<selectColumn> selectList; private List<String> fromList; private List<columnRef> groupByList; protected List<orderByCol> orderByList; private List<String> referencedColumns; // used to validate referenced columns in the statement private List<String> aliases; // used for validation //private tableSource sourceTable; private boolean distinct; private condition whereClause = null; private condition havingClause = null; selectStatement( List<selectColumn> _selectList, boolean _distinct ){ selectList = _selectList; distinct = _distinct; }// selectStatement() private selectStatement( boolean _distinct, List<selectColumn> _selectList, List<String> _fromList, List<columnRef> _groupByList, List<orderByCol> _orderByList, List<String> _referencedColumns, List<String> _aliases, condition _where, condition _having ){ distinct = _distinct; selectList = _selectList; fromList = _fromList; groupByList = _groupByList; orderByList = _orderByList; referencedColumns = _referencedColumns; aliases = _aliases; whereClause = _where; havingClause = _having; } void addSelectColumn( selectColumn _selectCol ){ selectList.add( _selectCol ); }// setSelect() void setFromList( List<String> _fromList ){ fromList = _fromList; }// setFromList() // sets the where condition that will filter table rows // within the tableSource void setWhereCondition( condition _whereClause ){ whereClause = _whereClause; }// setWhereClause // sets the HAVING clause. Assumes the condition is valid. // i.e. void setHavingCondition( condition _havingClause ){ havingClause = _havingClause; }// setWhereClause void setGroupByList( List<columnRef> _groupByList ){ groupByList = _groupByList; }// setGroupByList() void setOrderByList( List<orderByCol> _orderBy ){ orderByList = _orderBy; }// setOrderByList() void setReferencedColumns( List<String> _referencedColumns ){ referencedColumns = _referencedColumns; }// setReferencedColumns() public void setAliases( List<String> _aliases ){ aliases = _aliases; } tableSource createTableSource( cfSession _Session, List<cfData> _pData ) throws cfmRunTimeException{ Map<String, cfQueryResultData> tabs = new FastMap<String, cfQueryResultData>(); // convert the Arraylist of tables to a Hashtable with table name as the key Iterator<String> froms = fromList.iterator(); while ( froms.hasNext() ){ String tableName = froms.next(); cfData table = runTime.runExpression( _Session, tableName ); if ( table == null ){ throw new cfmRunTimeException( catchDataFactory.generalException("errorCode.expressionError", "queryofqueries.badTable", new String[]{tableName} ) ); } tabs.put( tableName, (cfQueryResultData)table ); } // initialise the table source if ( whereClause == null ){ return new tableSource( tabs, _pData ); }else{ return new filteredTableSource( tabs, whereClause, _pData ); } }// createTableSource() public selectStatement copy(){ List<selectColumn> copiedSelectCols = new ArrayList<selectColumn>(); selectColumn nextCol; for ( int i = 0; i < this.selectList.size(); i++ ){ nextCol = selectList.get(i); copiedSelectCols.add( nextCol.shallowCopy() ); } return new selectStatement( distinct, copiedSelectCols, fromList, groupByList, orderByList, referencedColumns, aliases, whereClause, havingClause ); } cfQueryResultData execute( cfSession _Session, List<cfData> _pData ) throws cfmRunTimeException { resultTable resTable; tableSource source; // create the source table from the FROM elements source = createTableSource( _Session, _pData ); // at this point some validation has already been done in validateGroupBy(), validateHaving() validateStatement( source ); // initialise the select columns selectColumn[] selCols = getSelectColumns(); int totalNoCols = 0; rowContext rctxt = source.getInitRow(); Map<String, String> lookup = new FastMap<String, String>(); Map<String, Integer> indxLookup = new FastMap<String, Integer>(); int zIndx = 0; for ( int j = 0; j < selCols.length; j++ ){ selCols[j].initResultData( rctxt ); String [] colNames = selCols[j].getColumnNames(); totalNoCols += colNames.length; zIndx = createLookupEntries( rctxt, lookup, indxLookup, selCols[j], zIndx ); } orderByCol [] obCols = null; if ( orderByList != null ){ obCols = new orderByCol[ orderByList.size() ]; for ( int i = 0; i < obCols.length; i++ ){ obCols[i] = orderByList.get( i ); } } if ( groupByList != null ){ resTable = new groupByResultTable( selCols, obCols, groupByList, havingClause, distinct ); }else if ( selCols[0].isAggregateFunction() ){ // this differs cos there is only one returned row resTable = new expressionResultTable( selCols, obCols ); }else{ resTable = new resultTable( selCols, distinct, obCols ); } while ( source.hasNext() ){ resTable.processRow( source.nextRow(), _pData, lookup ); } selCols = resTable.getSelCols(); // get Column names // need a String[] in the end so need something from selectColumns String [] allColNames = new String [ totalNoCols ]; int [] allColTypes = new int [ totalNoCols ]; //List allExprStrs int index = 0; for ( int n = 0; n < selCols.length; n++ ){ String [] colNames = selCols[n].getColumnNames(); int [] colTypes = selCols[n].getColumnTypes(); for ( int m = 0; m < colNames.length; m++ ){ allColNames[index] = colNames[m]; if ( colTypes != null ) { allColTypes[index] = colTypes[m]; } index++; } } if ( allColTypes.length == 0 ) { allColTypes = null; } // create a new query & return it. cfQueryResultData qoqRes = new cfQueryResultData( allColNames, allColTypes, "Query of Queries" ); List<ResultRow> results = resTable.getResultData( indxLookup ); if ( results.size() > 0 ){ qoqRes.setQueryData( doOrderBy( results, lookup, qoqRes ) ); } return qoqRes; }//execute() private static int createLookupEntries( rowContext _rc, Map<String, String> _lookup, Map<String, Integer> _ilookup, selectColumn _col, int _index ){ int index = _index; switch( _col.getColumnType() ){ case selectColumn.TABLEANDCOLUMN: case selectColumn.COLUMN: String colName = _col.getColumnNames()[0]; _lookup.put( _col.getShortName().toLowerCase(), colName ); _lookup.put( _col.getFullName( _rc ).toLowerCase(), colName ); _ilookup.put( _col.getShortName().toLowerCase(), new Integer( index ) ); _ilookup.put( _col.getFullName( _rc ).toLowerCase(), new Integer( index ) ); if ( _col.getAlias() != null ){ _lookup.put( _col.getAlias().toLowerCase(), _col.getShortName() ); _ilookup.put(_col.getAlias().toLowerCase(), new Integer(index)); } index++; break; case selectColumn.TABLEANDASTERISK: String tablename = _col.getTable(); String [] colNames = _rc.getTableColumnNames( tablename ); for ( int i = 0; i < colNames.length; i++ ){ _lookup.put( colNames[i].toLowerCase(), colNames[i] ); _lookup.put( (tablename.toLowerCase() + "." + colNames[i]).toLowerCase(), colNames[i] ); _ilookup.put(colNames[i].toLowerCase(), new Integer(index)); _ilookup.put((tablename.toLowerCase() + "." + colNames[i]).toLowerCase(), new Integer(index)); index++; } break; case selectColumn.ASTERISK: String [] tablenames = _rc.getTableNames(); for ( int j = 0; j < tablenames.length; j++ ){ String [] columnNames = _rc.getTableColumnNames( tablenames[j] ); for ( int i = 0; i < columnNames.length; i++ ){ _lookup.put( columnNames[i].toLowerCase(), columnNames[i] ); _lookup.put( (tablenames[j].toLowerCase() + "." + columnNames[i]).toLowerCase(), columnNames[i] ); _ilookup.put(columnNames[i].toLowerCase(), new Integer(index)); _ilookup.put((tablenames[j].toLowerCase() + "." + columnNames[i]).toLowerCase(), new Integer(index)); index++; } } break; case selectColumn.AGGREGATEFUNCTION: _ilookup.put(_col.toString(), new Integer(index)); if ( _col.getAlias() != null ){ _lookup.put( _col.getAlias(), _col.getAlias() ); _ilookup.put(_col.getAlias(), new Integer(index)); } index++; default: break; } return index; } protected List<List<cfData>> doOrderBy( List<ResultRow> _data, Map<String, String> _lookup, cfQueryResultData _query ) throws cfmRunTimeException { // do sort if necessary if ( orderByList != null ) { // make a copy of the ORDER BY list List<orderByCol> orderByCopy = new ArrayList<orderByCol>( orderByList.size() ); for ( int j = 0; j < orderByList.size(); j++ ) { orderByCopy.add( orderByList.get( j ).copy() ); } // do the sort Collections.sort( _data, new MultiColumnComparator( orderByCopy ) ); } // create the cfData result rows from the ResultRows List<List<cfData>> returnData = new ArrayList<List<cfData>>( _data.size() ); for ( int i = 0; i < _data.size(); i++ ) { ResultRow nextRow = (ResultRow)_data.get( i ); List<cfData> rowData = nextRow.getSelectedColumns(); for ( int j = 0; j < rowData.size(); j++ ){ rowData.get(j).setQueryTableData( returnData, j+1 ); } returnData.add( rowData ); } return returnData; }// doOrderBy() class MultiColumnComparator implements Comparator<ResultRow> { orderByCol [] colList; MultiColumnComparator( List<orderByCol> _cols ) { colList = new orderByCol[ _cols.size() ]; for ( int i = 0; i < colList.length; i++ ){ colList[i] = _cols.get( i ); } }// MultiColumnComparator() public int compare( ResultRow _resultRow1, ResultRow _resultRow2 ){ orderByCol obCol; cfData dataItem1, dataItem2; // the components of the 2 rows to compare List<cfData> orderData1 = _resultRow1.getOrderByColumns(); List<cfData> orderData2 = _resultRow2.getOrderByColumns(); // loop thru the colList returning if o1 and o2 are not equal on a column for ( int i = 0; i < colList.length; i++ ){ obCol = colList[i]; dataItem1 = orderData1.get( i ); dataItem2 = orderData2.get( i ); int compResult = cfData.compare( dataItem1, dataItem2 ); if ( compResult != 0 ){ // if items not equal if ( obCol.isAscending() ){ return compResult; }else{ return -1 * compResult; // return the reverse of the comparison result } } } return 0; }//compare() }// MultiColumnComparator // converts the selectList VectorArrayList to an array of selectColumns private selectColumn[] getSelectColumns(){ if ( groupByList != null ){ for ( int i = 0; i < groupByList.size(); i++ ){ expression groupByCol = groupByList.get(i); if ( groupByCol instanceof columnRef ){ columnRef col = (columnRef) groupByCol; boolean found = false; for ( int j = 0; j < selectList.size(); j++ ){ selectColumn selCol = selectList.get(j); switch( selCol.getColumnType() ){ case selectColumn.COLUMN: if ( selCol.getShortName().equalsIgnoreCase( col.colName ) ){ found = true; }else if ( selCol.getAlias() != null && selCol.getAlias().equalsIgnoreCase( col.colName ) ){ found = true; } break; case selectColumn.ASTERISK: found = true; break; case selectColumn.TABLEANDASTERISK: if ( col.tableNamed && selCol.getTable().equalsIgnoreCase( col.table ) ){ found = true; } break; case selectColumn.TABLEANDCOLUMN: if ( col.tableNamed && selCol.getTable().equalsIgnoreCase( col.table ) && selCol.getShortName().equalsIgnoreCase( col.colName ) ){ found = true; } } if ( found ) break; } if ( !found ){ selectList.add( 0, col.tableNamed ? new selectColumn( col.table, col.colName ) : new selectColumn( col.colName ) ); } } } } Object [] objArray = selectList.toArray(); selectColumn [] selArray = new selectColumn[ objArray.length ]; for ( int i = 0; i < objArray.length; i++ ){ selArray[i] = (selectColumn) objArray[i]; if ( selArray[i].isAggregateFunction() ){ //Expression() ){ selArray[i].getExpression().reset(); } } return selArray; }// getSelectColumns() /** * returns true if all the columns specified in the select are available * in the group by. */ boolean validateGroupBy(){ selectColumn selectCol; String selectColStr; boolean fullRef = false; boolean found = false; String [] groupByStrs = getGroupByStrs(); // loop thru' the columns in the select list Iterator<selectColumn> selectByCols = selectList.iterator(); while ( selectByCols.hasNext() ){ selectCol = selectByCols.next(); switch ( selectCol.getColumnType() ){ case selectColumn.EXPRESSION: break; case selectColumn.TABLEANDCOLUMN: case selectColumn.COLUMN: selectColStr = ( selectCol.table.equals( "" ) ? selectCol.columnName : selectCol.table + "." + selectCol.columnName ).toLowerCase(); fullRef = selectColStr.indexOf( '.' ) != -1; found = false; for ( int i = 0; i < groupByStrs.length; i++ ){ if ( groupByStrs[i].equals( selectColStr ) || ( !fullRef && groupByStrs[i].endsWith( selectColStr ) ) || ( fullRef && selectColStr.endsWith( groupByStrs[i] ) ) || ( selectCol.alias != null && selectCol.alias.equalsIgnoreCase( groupByStrs[i] ) ) ){ found = true; break; } } // might want to throw an exception instead saying which column not found // in the group by if ( !found ) return false; break; case selectColumn.TABLEANDASTERISK: case selectColumn.ASTERISK: return false; default: break; }// switch } return true; }// validateGroupBy() /** * returns true if all the columns specified in the having condition * are specified in the group by */ boolean validateHaving( List<String> _refdCols ){ if ( groupByList == null ){ return true; } // create string [] for better performance String [] groupByStrs = getGroupByStrs(); for ( int i = 0; i < _refdCols.size(); i++ ){ String col = _refdCols.get( i ); boolean found = false; boolean fullRef = col.indexOf( '.' ) != -1; for ( int j = 0; j < groupByList.size(); j++ ){ // handles people.id if ( groupByStrs[j].equals( col ) || ( !fullRef && groupByStrs[j].endsWith( col ) ) || ( fullRef && col.endsWith( groupByStrs[j] ) ) ){ found = true; break; } } if ( !found ){ // could throw an exception that this column is not specified in the group by return false; } } return true; }// validateHaving() private String [] getGroupByStrs(){ String [] groupByStrs = new String[ groupByList.size() ]; for ( int i = 0; i < groupByList.size(); i++ ){ groupByStrs[i] = groupByList.get(i).toString();//.toLowerCase(); } return groupByStrs; }// getGroupByStrs() private void validateStatement( tableSource _source ) throws cfmRunTimeException{ if ( referencedColumns != null ){ // check select columns occur in table // if occur in more than one table throw ambiguity exception // if don't occur in any table then throw invalid column in select exception for ( int i = 0; i < referencedColumns.size(); i++ ){ String refdCol = referencedColumns.get(i); int dotIndex = refdCol.indexOf( '.' ); if ( dotIndex == -1 ){ // unqualified col name // could throw an exception for ambiguity in this case if ( !_source.existsColumn( refdCol ) ) { //&& !aliases.contains( refdCol.toLowerCase() ) ){ throw new cfmRunTimeException( catchDataFactory.generalException("errorCode.expressionError", "queryofqueries.invalidSelect", new String[]{refdCol} ) ); } }else{ // fully qualified name if ( !_source.existsTableColumn( refdCol.substring( 0, dotIndex ), refdCol.substring( dotIndex+1 ) ) ){ throw new cfmRunTimeException( catchDataFactory.generalException("errorCode.expressionError", "queryofqueries.invalidSelect", new String[]{refdCol} ) ); } } } } }// validateStatement public String toString(){ StringBuilder selectStr = new StringBuilder(40); selectStr.append( "SELECT " ); Iterator<selectColumn> selects = selectList.iterator(); while ( selects.hasNext() ){ selectStr.append( selects.next().toString() ); selectStr.append( ',' ); } // delete the trailing comma selectStr.setLength( selectStr.length() - 1 ); selectStr.append( " FROM " ); Iterator<String> froms = fromList.iterator(); while ( froms.hasNext() ){ selectStr.append( froms.next() ); selectStr.append( ',' ); } // delete the trailing comma selectStr.setLength( selectStr.length() - 1 ); return selectStr.toString(); }// toString() }// selectStatement