Return-Path: Delivered-To: apmail-db-torque-dev-archive@www.apache.org Received: (qmail 73553 invoked from network); 2 Sep 2005 07:26:42 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 2 Sep 2005 07:26:42 -0000 Received: (qmail 91021 invoked by uid 500); 2 Sep 2005 07:26:42 -0000 Delivered-To: apmail-db-torque-dev-archive@db.apache.org Received: (qmail 90997 invoked by uid 500); 2 Sep 2005 07:26:41 -0000 Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Developers List" Reply-To: "Apache Torque Developers List" Delivered-To: mailing list torque-dev@db.apache.org Received: (qmail 90982 invoked by uid 99); 2 Sep 2005 07:26:41 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Sep 2005 00:26:41 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of hps@intermeta.de designates 194.77.152.163 as permitted sender) Received: from [194.77.152.163] (HELO mail.intermeta.de) (194.77.152.163) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Sep 2005 00:26:54 -0700 Received: from forge.intermeta.de (forge.intermeta.de [192.168.2.4]) (authenticated bits=0) by mail.intermeta.de (8.12.11/8.12.11) with ESMTP id j827QX3m002103 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO) for ; Fri, 2 Sep 2005 09:26:33 +0200 Subject: [Fwd: Torqe 3.2 rc1 Bug] From: Henning Schmiedehausen Reply-To: hps@intermeta.de To: Torque Developers List Content-Type: text/plain Organization: INTERMETA - Gesellschaft fuer Mehrwertdienste mbH Date: Fri, 02 Sep 2005 09:26:32 +0200 Message-Id: <1125645992.6340.116.camel@forge.intermeta.de> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-6) Content-Transfer-Encoding: 7bit X-Spam-Score: -1.709 () BAYES_00,HTML_MESSAGE,J_CHICKENPOX_56,J_CHICKENPOX_73,REMOVE_REMOVAL_1WORD X-Scanned-By: MIMEDefang 2.43 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Bug report sent to me directly. Regards Henning -------- Forwarded Message -------- > From: Jacob Champlin > To: henning@apache.org > Subject: Torqe 3.2 rc1 Bug > Date: Thu, 01 Sep 2005 18:50:20 -0500 > Henning P. Schmiedehausen, > > I sent the following to Tomas Fischer, but it looks like your doing most > of the Torque updates. So I thought I would send to you. > > Jacob Champlin > EMO Corporation > > Thomas Fischer, > > Hello, I just converted our website to use Torque 3.2 rc1 and have > discovered a bug. I realise submitting a bug to you is the way things > should work. However I can't seem to figure out how to submit issues to > the Torque project. All I can find is a link to some Maven bug repository. > > Anyways the issue at hand. It appears like you added support for SQL > functions in ORDER BY statements. For example: ORDER BY MAX(table.column) > > However the order by statement doesn't work with other clauses: > ORDER BY table.column < 100 > ORDER BY table.column IN (1, 2, 3) > > In particular the IN statement really gets messed up because of > parentheses. > > I tracked down the issue to the SQLBuilder.removeSQLFunction() . The > comment on this function > says it removes clauses and functions. But it looks to me like it only > removes functions. > > So I changed it to also remove clauses... and here you go. Hope you can > get this in before the final release. > > Thank you, > Jacob Champlin > EMO Corporation > > > plain text document attachment (SQLBuilder.java) > package org.apache.torque.util; > > /* > * Copyright 2001-2004 The Apache Software Foundation. > * > * Licensed under the Apache License, Version 2.0 (the "License") > * you may not use this file except in compliance with the License. > * You may obtain a copy of the License at > * > * http://www.apache.org/licenses/LICENSE-2.0 > * > * Unless required by applicable law or agreed to in writing, software > * distributed under the License is distributed on an "AS IS" BASIS, > * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. > * See the License for the specific language governing permissions and > * limitations under the License. > */ > > import java.io.Serializable; > import java.util.HashSet; > import java.util.Iterator; > import java.util.List; > import java.util.Map; > import java.util.Set; > > import org.apache.commons.lang.StringUtils; > import org.apache.commons.logging.Log; > import org.apache.commons.logging.LogFactory; > import org.apache.torque.Torque; > import org.apache.torque.TorqueException; > import org.apache.torque.adapter.DB; > import org.apache.torque.map.ColumnMap; > import org.apache.torque.map.DatabaseMap; > import org.apache.torque.util.Criteria.Criterion; > > /** > * Factored out code that is used to process SQL tables. This code comes > * from BasePeer and is put here to reduce complexity in the BasePeer class. > * You should not use the methods here directly! > * > * @author Henning P. Schmiedehausen > * @author Thomas Fischer > * @version $Id: SQLBuilder.java,v 1.6 2005/07/02 15:22:32 tfischer Exp $ > */ > public abstract class SQLBuilder > implements Serializable > { > /** Logging */ > protected static Log log = LogFactory.getLog(SQLBuilder.class); > > /** > * Fully qualify a table name with an optional schema reference > * > * @param table The table name to use. If null is passed in, null is returned. > * @param dbName The name of the database to which this tables belongs. > * If null is passed, the default database is used. > * > * @return The table name to use inside the SQL statement. If null is passed > * into this method, null is returned. > * @exception TorqueException if an error occurs > */ > public static final String getFullTableName(final String table, final String dbName) > throws TorqueException > { > if (table != null) > { > int dotIndex = table.indexOf("."); > > if (dotIndex == -1) // No schema given > { > String targetDBName = (dbName == null) > ? Torque.getDefaultDB() > : dbName; > > String targetSchema = Torque.getSchema(targetDBName); > > // If we have a default schema, fully qualify the > // table and return. > if (StringUtils.isNotEmpty(targetSchema)) > { > return new StringBuffer() > .append(targetSchema) > .append(".") > .append(table) > .toString(); > } > } > } > > return table; > } > > /** > * Remove a possible schema name from the table name. > * > * @param table The table name to use > * > * @return The table name with a possible schema name > * stripped off > */ > public static final String getUnqualifiedTableName(final String table) > { > if (table != null) > { > int dotIndex = table.lastIndexOf("."); // Do we have a dot? > > if (++dotIndex > 0) // Incrementation allows for better test _and_ substring... > { > return table.substring(dotIndex); > } > } > > return table; > } > > /** > * Removes a possible function name or clause from a column name > * > * @param name The column name, possibly containing a clause > * > * @return The column name > * > * @throws TorqueException If the column name was malformed > */ > private static String removeSQLFunction(final String name) > throws TorqueException > { > // Empty name => return it > if (StringUtils.isEmpty(name)) > { > return name; > } > > > String cleanName = name; > > // Do we have a clause? > final int spacePos = cleanName.trim().indexOf(' '); > if (spacePos >= 0) > { > cleanName = cleanName.substring(0, spacePos); > } > > // Do we have Parentheses? > final int leftParent = cleanName.lastIndexOf('('); > final int rightParent = cleanName.indexOf(')'); > if (leftParent >= 0 && rightParent >= 0 && rightParent > leftParent) > { > // Yes. Strip off the function, return the column name > cleanName = cleanName.substring(leftParent + 1, rightParent); > } > > return cleanName; > } > > /** > * Removes possible qualifiers (like DISTINCT) from a column name > * > * @param name The column name, possibly containing qualifiers > * > * @return The column name > * > * @throws TorqueException If the column name was malformed > */ > private static String removeQualifiers(final String name) > throws TorqueException > { > // Empty name => return it > if (StringUtils.isEmpty(name)) > { > return name; > } > > final int spacePos = name.trim().lastIndexOf(' '); > > // Do we have spaces, indicating that qualifiers are used ? > if (spacePos > 0) > { > // Qualifiers are first, tablename is piece after last space > return name.trim().substring(spacePos + 1); > } > > // no spaces, nothing changed > return name; > } > > > /** > * Returns a table name from an identifier. Each identifier is to be qualified > * as [schema.]table.column. This could also contain FUNCTION([schema.]table.column). > * > * @param name The (possible fully qualified) identifier name > * > * @return the fully qualified table name > * > * @throws TorqueException If the identifier name was malformed > */ > public static String getTableName(final String name, final String dbName) > throws TorqueException > { > final String testName = removeQualifiers(removeSQLFunction(name)); > > if (StringUtils.isEmpty(testName)) > { > throwMalformedColumnNameException( > "getTableName", > name); > } > > // Everything before the last dot is the table name > int rightDotIndex = testName.lastIndexOf('.'); > > if (rightDotIndex < 0) > { > if ("*".equals(testName)) > { > return null; > } > > throwMalformedColumnNameException( > "getTableName", > name); > } > > return getFullTableName(testName.substring(0, rightDotIndex), dbName); > } > > > > /** > * Returns a set of all tables and possible aliases referenced > * from a criterion. The resulting Set can be directly used to > * build a WHERE clause > * > * @param crit A Criteria object > * @param tableCallback A Callback Object > * @return A Set of tables. > */ > public static final Set getTableSet( > final Criteria crit, > final TableCallback tableCallback) > { > HashSet tables = new HashSet(); > > // Loop over all the Criterions > for (Iterator it = crit.keySet().iterator(); it.hasNext(); ) > { > String key = (String) it.next(); > Criteria.Criterion c = crit.getCriterion(key); > List tableNames = c.getAllTables(); > > // Loop over all Tables referenced in this criterion. > for (Iterator it2 = tableNames.iterator(); it2.hasNext(); ) > { > String name = (String) it2.next(); > String aliasName = crit.getTableForAlias(name); > > // If the tables have an alias, add an " AS statement" > if (StringUtils.isNotEmpty(aliasName)) > { > String newName = > new StringBuffer(name.length() + aliasName.length() + 4) > .append(aliasName) > .append(" AS ") > .append(name) > .toString(); > name = newName; > } > tables.add(name); > } > > if (tableCallback != null) > { > tableCallback.process(tables, key, crit); > } > } > > return tables; > } > > /** > * Builds a Query clause for Updating and deleting > * > * @param crit a Criteria value > * @param params a List value > * @param qc a QueryCallback value > * @return a Query value > * @exception TorqueException if an error occurs > */ > public static final Query buildQueryClause(final Criteria crit, > final List params, > final QueryCallback qc) > throws TorqueException > { > Query query = new Query(); > > final String dbName = crit.getDbName(); > final DB db = Torque.getDB(dbName); > final DatabaseMap dbMap = Torque.getDatabaseMap(dbName); > > JoinBuilder.processJoins(db, dbMap, crit, query); > processModifiers(crit, query); > processSelectColumns(crit, query, dbName); > processAsColumns(crit, query); > processCriterions(db, dbMap, dbName, crit, query, params, qc); > processGroupBy(crit, query); > processHaving(crit, query); > processOrderBy(db, dbMap, crit, query); > LimitHelper.buildLimit(crit, query); > > if (log.isDebugEnabled()) > { > log.debug(query.toString()); > } > return query; > } > > > /** > * adds the select columns from the criteria to the query > * @param criteria the criteria from which the select columns are taken > * @param query the query to which the select columns should be added > * @throws TorqueException if the select columns can not be processed > */ > private static final void processSelectColumns( > final Criteria criteria, > final Query query, > final String dbName) > throws TorqueException > { > UniqueList selectClause = query.getSelectClause(); > UniqueList select = criteria.getSelectColumns(); > > for (int i = 0; i < select.size(); i++) > { > String identifier = (String) select.get(i); > selectClause.add(identifier); > addTableToFromClause(getTableName(identifier, dbName), criteria, query); > } > } > > /** > * adds the As-columns from the criteria to the query. > * @param criteria the criteria from which the As-columns are taken > * @param query the query to which the As-columns should be added > */ > private static final void processAsColumns( > final Criteria criteria, > final Query query) > { > UniqueList querySelectClause = query.getSelectClause(); > Map criteriaAsColumns = criteria.getAsColumns(); > > for (Iterator it = criteriaAsColumns.keySet().iterator(); it.hasNext(); ) > { > String key = (String) it.next(); > querySelectClause.add( > new StringBuffer() > .append(criteriaAsColumns.get(key)) > .append(SqlEnum.AS) > .append(key) > .toString()); > } > } > > /** > * adds the Modifiers from the criteria to the query > * @param criteria the criteria from which the Modifiers are taken > * @param query the query to which the Modifiers should be added > */ > private static final void processModifiers( > final Criteria criteria, > final Query query) > { > UniqueList selectModifiers = query.getSelectModifiers(); > UniqueList modifiers = criteria.getSelectModifiers(); > for (int i = 0; i < modifiers.size(); i++) > { > selectModifiers.add(modifiers.get(i)); > } > } > > /** > * adds the Criterion-objects from the criteria to the query > * @param criteria the criteria from which the Criterion-objects are taken > * @param query the query to which the Criterion-objects should be added > * @param params the parameters if a prepared statement should be built, > * or null if a normal statement should be built. > * @throws TorqueException if the Criterion-objects can not be processed > */ > private static final void processCriterions( > final DB db, > final DatabaseMap dbMap, > final String dbName, > final Criteria crit, > final Query query, > final List params, > final QueryCallback qc) > throws TorqueException > { > UniqueList fromClause = query.getFromClause(); > UniqueList whereClause = query.getWhereClause(); > > for (Iterator it = crit.keySet().iterator(); it.hasNext(); ) > { > String key = (String) it.next(); > Criteria.Criterion criterion = crit.getCriterion(key); > Criteria.Criterion[] someCriteria = > criterion.getAttachedCriterion(); > > String table = null; > for (int i = 0; i < someCriteria.length; i++) > { > String tableName = someCriteria[i].getTable(); > > // add the table to the from clause, if it is not already > // contained there > // it is important that this piece of code is executed AFTER > // the joins are processed > addTableToFromClause(getFullTableName(tableName, dbName), crit, query); > > table = crit.getTableForAlias(tableName); > if (table == null) > { > table = tableName; > } > > boolean ignoreCase = ((crit.isIgnoreCase() || someCriteria[i].isIgnoreCase()) > && (dbMap.getTable(table) > .getColumn(someCriteria[i].getColumn()) > .getType() > instanceof String)); > > someCriteria[i].setIgnoreCase(ignoreCase); > } > > criterion.setDB(db); > whereClause.add(qc.process(criterion, params)); > } > } > > /** > * adds the OrderBy-Columns from the criteria to the query > * @param criteria the criteria from which the OrderBy-Columns are taken > * @param query the query to which the OrderBy-Columns should be added > * @throws TorqueException if the OrderBy-Columns can not be processed > */ > private static final void processOrderBy( > final DB db, > final DatabaseMap dbMap, > final Criteria crit, > final Query query) > throws TorqueException > { > UniqueList orderByClause = query.getOrderByClause(); > UniqueList selectClause = query.getSelectClause(); > > UniqueList orderBy = crit.getOrderByColumns(); > > if (orderBy != null && orderBy.size() > 0) > { > // Check for each String/Character column and apply > // toUpperCase(). > for (int i = 0; i < orderBy.size(); i++) > { > String orderByColumn = (String) orderBy.get(i); > > String strippedColumnName > = removeSQLFunction(orderByColumn); > int dotPos = strippedColumnName.lastIndexOf('.'); > if (dotPos == -1) > { > throwMalformedColumnNameException( > "order by", > orderByColumn); > } > > String tableName = strippedColumnName.substring(0, dotPos); > String table = crit.getTableForAlias(tableName); > if (table == null) > { > table = tableName; > } > > // See if there's a space (between the column list and sort > // order in ORDER BY table.column DESC). > int spacePos = strippedColumnName.indexOf(' '); > String columnName; > if (spacePos == -1) > { > columnName = > strippedColumnName.substring(dotPos + 1); > } > else > { > columnName = strippedColumnName.substring( > dotPos + 1, > spacePos); > } > ColumnMap column = dbMap.getTable(table).getColumn(columnName); > > // only ignore case in order by for string columns > // which do not have a function around them > if (column.getType() instanceof String > && orderByColumn.indexOf('(') == -1) > { > // find space pos relative to orderByColumn > spacePos = orderByColumn.indexOf(' '); > if (spacePos == -1) > { > orderByClause.add( > db.ignoreCaseInOrderBy(orderByColumn)); > } > else > { > orderByClause.add( > db.ignoreCaseInOrderBy( > orderByColumn.substring(0, spacePos)) > + orderByColumn.substring(spacePos)); > } > selectClause.add( > db.ignoreCaseInOrderBy(tableName + '.' + columnName)); > } > else > { > orderByClause.add(orderByColumn); > } > } > } > } > > /** > * adds the GroupBy-Columns from the criteria to the query > * @param criteria the criteria from which the GroupBy-Columns are taken > * @param query the query to which the GroupBy-Columns should be added > * @throws TorqueException if the GroupBy-Columns can not be processed > */ > private static final void processGroupBy( > final Criteria crit, > final Query query) > throws TorqueException > { > UniqueList groupByClause = query.getGroupByClause(); > UniqueList groupBy = crit.getGroupByColumns(); > > // need to allow for multiple group bys > if (groupBy != null) > { > for (int i = 0; i < groupBy.size(); i++) > { > String columnName = (String) groupBy.get(i); > String column = (String) crit.getAsColumns().get(columnName); > > if (column == null) > { > column = columnName; > } > > if (column.indexOf('.') != -1) > { > groupByClause.add(column); > } > else > { > throwMalformedColumnNameException("group by", > column); > } > } > } > } > > /** > * adds the Having-Columns from the criteria to the query > * @param criteria the criteria from which the Having-Columns are taken > * @param query the query to which the Having-Columns should be added > * @throws TorqueException if the Having-Columns can not be processed > */ > private static final void processHaving( > final Criteria crit, > final Query query) > throws TorqueException > { > Criteria.Criterion having = crit.getHaving(); > if (having != null) > { > //String groupByString = null; > query.setHaving(having.toString()); > } > } > > /** > * Throws a TorqueException with the malformed column name error > * message. The error message looks like this:

> * > * > * Malformed column name in Criteria [criteriaPhrase]: > * '[columnName]' is not of the form 'table.column' > * > * > * @param criteriaPhrase a String, one of "select", "join", or "order by" > * @param columnName a String containing the offending column name > * @throws TorqueException Any exceptions caught during processing will be > * rethrown wrapped into a TorqueException. > */ > public static final void throwMalformedColumnNameException( > final String criteriaPhrase, > final String columnName) > throws TorqueException > { > StringBuffer sb = new StringBuffer() > .append("Malformed column name in Criteria ") > .append(criteriaPhrase) > .append(": '") > .append(StringUtils.isEmpty(columnName) ? "" : columnName) > .append("' is not of the form 'table.column'"); > > throw new TorqueException(sb.toString()); > } > > /** > * Returns the tablename which can be added to a From Clause. > * This takes care of any aliases that might be defined. > * For example, if an alias "a" for the table AUTHOR is defined > * in the Criteria criteria, getTableNameForFromClause("a", criteria) > * returns "AUTHOR a". > * @param tableName the name of a table > * or the alias for a table > * @param criteria a criteria object to resolve a possible alias > * @return either the tablename itself if tableOrAliasName is not an alias, > * or a String of the form "tableName tableOrAliasName" > * if tableOrAliasName is an alias for a table name > */ > public static final String getTableNameForFromClause( > final String tableName, > final Criteria criteria) > { > String shortTableName = getUnqualifiedTableName(tableName); > > // Most of the time, the alias would be for the short name... > String aliasName = criteria.getTableForAlias(shortTableName); > if (StringUtils.isEmpty(aliasName)) > { > // But we should also check the FQN... > aliasName = criteria.getTableForAlias(tableName); > } > > if (StringUtils.isNotEmpty(aliasName)) > { > // If the tables have an alias, add an " statement" > // AS causes problems on oracle > return new StringBuffer( > tableName.length() + aliasName.length() + 1) > .append(aliasName) > .append(" ") > .append(tableName) > .toString(); > } > > return tableName; > } > > /** > * Checks if the Tablename tableName is already contained in a from clause. > * If tableName and the tablenames in fromClause are generated by > * getTablenameForFromClause(String, Criteria), (which they usually are), > * then different aliases for the same table are treated > * as different tables: E.g. > * fromClauseContainsTableName(fromClause, "table_a a") returns false if > * fromClause contains only another alias for table_a , > * e.g. "table_a aa" and the unaliased tablename "table_a". > * Special case: If tableName is null, true is returned. > * @param fromClause a list containing only elements of type. > * Query.FromElement > * @param tableName the tablename to check > * @return if the Tablename tableName is already contained in a from clause. > * If tableName is null, true is returned. > */ > public static final boolean fromClauseContainsTableName( > final UniqueList fromClause, > final String tableName) > { > if (tableName == null) > { > // usually this function is called to see if tableName should be > // added to the fromClause. As null should not be added, > // true is returned. > return true; > } > for ( Iterator it = fromClause.iterator(); it.hasNext();) > { > Query.FromElement fromElement > = (Query.FromElement) it.next(); > if (tableName.equals(fromElement.getTableName())) > { > return true; > } > } > return false; > } > > /** > * adds a table to the from clause of a query, if it is not already > * contained there. > * @param tableOrAliasName the name of a table > * or the alias for a table > * @param criteria a criteria object to resolve a possible alias > * @param query the query where the the tablename should be added > * to the from clause > * @return the table in the from clause which represents the > * supplied tableOrAliasName > */ > private static final String addTableToFromClause( > final String tableName, > final Criteria criteria, > Query query) > { > String tableNameForFromClause > = getTableNameForFromClause(tableName, criteria); > > UniqueList queryFromClause = query.getFromClause(); > > // it is important that this piece of code is executed AFTER > // the joins are processed > if (!fromClauseContainsTableName( > queryFromClause, > tableNameForFromClause)) > { > Query.FromElement fromElement > = new Query.FromElement( > tableNameForFromClause, null, null); > queryFromClause.add(fromElement); > } > return tableNameForFromClause; > } > > /** > * Inner Interface that defines the Callback method for > * the Table creation loop. > */ > public interface TableCallback > { > /** > * Callback Method for getTableSet() > * > * @param tables The current table name > * @param key The current criterion key. > * @param crit The Criteria used in getTableSet() > */ > void process(Set tables, String key, Criteria crit); > } > > /** > * Inner Interface that defines the Callback method for > * the buildQuery Criterion evaluation > */ > public interface QueryCallback > { > /** > * The callback for building a query String > * > * @param criterion The current criterion > * @param params The parameter list passed to buildQueryString() > * @return WHERE SQL fragment for this criterion > */ > String process(Criterion criterion, List params); > } > > } -- Dipl.-Inf. (Univ.) Henning P. Schmiedehausen INTERMETA GmbH hps@intermeta.de +49 9131 50 654 0 http://www.intermeta.de/ RedHat Certified Engineer -- Jakarta Turbine Development Linux, Java, perl, Solaris -- Consulting, Training, Engineering 4 - 8 - 15 - 16 - 23 - 42 --------------------------------------------------------------------- To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org For additional commands, e-mail: torque-dev-help@db.apache.org