package org.apache.torque.util;
   
   /*
    * Licensed to the Apache Software Foundation (ASF) under one
    * or more contributor license agreements.  See the NOTICE file
    * distributed with this work for additional information
    * regarding copyright ownership.  The ASF licenses this file
    * to you 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.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.ArrayList;
  import java.util.Collections;
  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.Database;
  import org.apache.torque.TooManyRowsException;
  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.map.MapBuilder;
  import org.apache.torque.map.TableMap;
  import org.apache.torque.oid.IdGenerator;
  import org.apache.torque.om.NumberKey;
  import org.apache.torque.om.ObjectKey;
  import org.apache.torque.om.SimpleKey;
  import org.apache.torque.om.StringKey;
  
  import com.workingdogs.village.Column;
  import com.workingdogs.village.DataSet;
  import com.workingdogs.village.DataSetException;
  import com.workingdogs.village.KeyDef;
  import com.workingdogs.village.QueryDataSet;
  import com.workingdogs.village.Record;
  import com.workingdogs.village.Schema;
  import com.workingdogs.village.TableDataSet;
  
  /***
   * This is the base class for all Peer classes in the system.  Peer
   * classes are responsible for isolating all of the database access
   * for a specific business object.  They execute all of the SQL
   * against the database.  Over time this class has grown to include
   * utility methods which ease execution of cross-database queries and
   * the implementation of concrete Peers.
   *
   * @author <a href="mailto:frank.kim@clearink.com">Frank Y. Kim</a>
   * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
   * @author <a href="mailto:bmclaugh@algx.net">Brett McLaughlin</a>
   * @author <a href="mailto:stephenh@chase3000.com">Stephen Haberman</a>
   * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
   * @author <a href="mailto:vido@ldh.org">Augustin Vidovic</a>
   * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
   * @version $Id: BasePeer.java 750198 2009-03-04 22:34:43Z gmonroe $
   */
  public abstract class BasePeer
          implements Serializable
  {
      /*** Constant criteria key to reference ORDER BY columns. */
      public static final String ORDER_BY = "ORDER BY";
  
      /***
       * Constant criteria key to remove Case Information from
       * search/ordering criteria.
       */
      public static final String IGNORE_CASE = "IgNOrE cAsE";
  
      /*** Classes that implement this class should override this value. */
      public static final String TABLE_NAME = "TABLE_NAME";
  
      /*** the log */
      protected static final Log log = LogFactory.getLog(BasePeer.class);
  
      private static void throwTorqueException(Exception e)
          throws TorqueException
      {
         if (e instanceof TorqueException)
         {
             throw (TorqueException) e;
         }
         else
         {
             throw new TorqueException(e);
         }
     }
 
     /***
      * Sets up a Schema for a table.  This schema is then normally
      * used as the argument for initTableColumns().
      *
      * @param tableName The name of the table.
      * @return A Schema.
      */
     public static Schema initTableSchema(String tableName)
     {
         return initTableSchema(tableName, Torque.getDefaultDB());
     }
 
     /***
      * Sets up a Schema for a table.  This schema is then normally
      * used as the argument for initTableColumns
      *
      * @param tableName The propery name for the database in the
      * configuration file.
      * @param dbName The name of the database.
      * @return A Schema.
      */
     public static Schema initTableSchema(String tableName, String dbName)
     {
         Schema schema = null;
         Connection con = null;
 
         try
         {
             con = Torque.getConnection(dbName);
             schema = new Schema().schema(con, tableName);
         }
         catch (Exception e)
         {
             log.error(e);
             throw new Error("Error in BasePeer.initTableSchema("
                     + tableName
                     + "): "
                     + e.getMessage());
         }
         finally
         {
             Torque.closeConnection(con);
         }
         return schema;
     }
 
     /***
      * Creates a Column array for a table based on its Schema.
      *
      * @param schema A Schema object.
      * @return A Column[].
      */
     public static Column[] initTableColumns(Schema schema)
     {
         Column[] columns = null;
         try
         {
             int numberOfColumns = schema.numberOfColumns();
             columns = new Column[numberOfColumns];
             for (int i = 0; i < numberOfColumns; i++)
             {
                 columns[i] = schema.column(i + 1);
             }
         }
         catch (Exception e)
         {
             log.error(e);
             throw new Error(
                 "Error in BasePeer.initTableColumns(): " + e.getMessage());
         }
         return columns;
     }
 
     /***
      * Convenience method to create a String array of column names.
      *
      * @param columns A Column[].
      * @return A String[].
      */
     public static String[] initColumnNames(Column[] columns)
     {
         String[] columnNames = new String[columns.length];
         for (int i = 0; i < columns.length; i++)
         {
             columnNames[i] = columns[i].name().toUpperCase();
         }
         return columnNames;
     }
 
     /***
      * Convenience method to create a String array of criteria keys.
      *
      * @param tableName Name of table.
      * @param columnNames A String[].
      * @return A String[].
      */
     public static String[] initCriteriaKeys(
         String tableName,
         String[] columnNames)
     {
         String[] keys = new String[columnNames.length];
         for (int i = 0; i < columnNames.length; i++)
         {
             keys[i] = tableName + "." + columnNames[i].toUpperCase();
         }
         return keys;
     }
 
     /***
      * Convenience method that uses straight JDBC to delete multiple
      * rows.  Village throws an Exception when multiple rows are
      * deleted.
      *
      * @param con A Connection.
      * @param table The table to delete records from.
      * @param column The column in the where clause.
      * @param value The value of the column.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void deleteAll(
         Connection con,
         String table,
         String column,
         int value)
         throws TorqueException
     {
         Statement statement = null;
         try
         {
             statement = con.createStatement();
 
             StringBuffer query = new StringBuffer();
             query.append("DELETE FROM ")
                 .append(table)
                 .append(" WHERE ")
                 .append(column)
                 .append(" = ")
                 .append(value);
 
             statement.executeUpdate(query.toString());
         }
         catch (SQLException e)
         {
             throw new TorqueException(e);
         }
         finally
         {
             if (statement != null)
             {
                 try
                 {
                     statement.close();
                 }
                 catch (SQLException e)
                 {
                     throw new TorqueException(e);
                 }
             }
         }
     }
 
     /***
      * Convenience method that uses straight JDBC to delete multiple
      * rows.  Village throws an Exception when multiple rows are
      * deleted.  This method attempts to get the default database from
      * the pool.
      *
      * @param table The table to delete records from.
      * @param column The column in the where clause.
      * @param value The value of the column.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void deleteAll(String table, String column, int value)
         throws TorqueException
     {
         Connection con = null;
         try
         {
             // Get a connection to the db.
             con = Torque.getConnection(Torque.getDefaultDB());
             deleteAll(con, table, column, value);
         }
         finally
         {
             Torque.closeConnection(con);
         }
     }
 
     /***
      * Method to perform deletes based on values and keys in a
      * Criteria.
      *
      * @param criteria The criteria to use.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      * @deprecated This method causes unexpected results when joins are used.
      *              Please use doDelete(Criteria, String).
      */
     public static void doDelete(Criteria criteria) throws TorqueException
     {
         doDelete(criteria, (String) null);
     }
 
     /***
      * Method to perform deletes based on values and keys in a
      * Criteria.
      * This method is protected because it may cause ambiguity between
      * doDelete(Criteria,Connection) and this method. It will be made public
      * once doDelete(Criteria, Connection) is removed.
      *
      * @param criteria The criteria to use.
      * @param tableName the name of the table to delete records from.
      *         If set to null, the name of the table(s) can be extracted from
      *         the criteria, but this can cause unexpected results.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     protected static void doDelete(Criteria criteria, String tableName) throws TorqueException
     {
         Connection con = null;
         try
         {
             con = Transaction.beginOptional(
                     criteria.getDbName(),
                     criteria.isUseTransaction());
             doDelete(criteria, tableName, con);
             Transaction.commit(con);
         }
         catch (TorqueException e)
         {
             Transaction.safeRollback(con);
             throw e;
         }
     }
 
     /***
      * Method to perform deletes based on values and keys in a Criteria.
      *
      * @param criteria The criteria to use.
      * @param con A Connection.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      * @deprecated This method causes unexpected results when joins are used.
      *              Please use doDelete(Criteria, String, Connection).
      */
     public static void doDelete(Criteria criteria, Connection con)
         throws TorqueException
     {
         doDelete(criteria, null, con);
     }
 
     /***
      * Method to perform deletes based on values and keys in a Criteria.
      *
      * @param criteria The criteria to use.
      * @param tableName the name of the table to delete records from.
      *         If set to null, the name of the table(s) can be extracted from
      *         the criteria, but this can cause unexpected results.
      * @param con A Connection.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void doDelete(Criteria criteria, String tableName, Connection con)
         throws TorqueException
     {
         String dbName = criteria.getDbName();
         final DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
 
         // This Callback adds all tables to the Table set which
         // are referenced from a cascading criteria. As a result, all
         // data that is referenced through foreign keys will also be
         // deleted.
         SQLBuilder.TableCallback tc = new SQLBuilder.TableCallback() {
                 public void process (Set tables, String key, Criteria crit)
                 {
                     if (crit.isCascade())
                     {
                         // This steps thru all the columns in the database.
                         TableMap[] tableMaps = dbMap.getTables();
                         for (int i = 0; i < tableMaps.length; i++)
                         {
                             ColumnMap[] columnMaps = tableMaps[i].getColumns();
 
                             for (int j = 0; j < columnMaps.length; j++)
                             {
                                 // Only delete rows where the foreign key is
                                 // also a primary key.  Other rows need
                                 // updating, but that is not implemented.
                                 if (columnMaps[j].isForeignKey()
                                         && columnMaps[j].isPrimaryKey()
                                         && key.equals(columnMaps[j].getRelatedName()))
                                 {
                                     tables.add(tableMaps[i].getName());
                                     crit.add(columnMaps[j].getFullyQualifiedName(),
                                             crit.getValue(key));
                                 }
                             }
                         }
                     }
                 }
             };
 
         Set tables;
         if (tableName == null)
         {
             tables = SQLBuilder.getTableSet(criteria, tc);
         }
         else
         {
             tables = new HashSet(1);
             tables.add(tableName);
         }
 
         try
         {
             processTables(criteria, tables, con, new ProcessCallback() {
                     public void process(String table, String dbName, Record rec)
                         throws Exception
                     {
                         rec.markToBeDeleted();
                         rec.save();
                     }
                 });
         }
         catch (Exception e)
         {
             throwTorqueException(e);
         }
     }
 
     /***
      * Method to perform inserts based on values and keys in a
      * Criteria.
      * <p>
      * If the primary key is auto incremented the data in Criteria
      * will be inserted and the auto increment value will be returned.
      * <p>
      * If the primary key is included in Criteria then that value will
      * be used to insert the row.
      * <p>
      * If no primary key is included in Criteria then we will try to
      * figure out the primary key from the database map and insert the
      * row with the next available id using util.db.IDBroker.
      * <p>
      * If no primary key is defined for the table the values will be
      * inserted as specified in Criteria and -1 will be returned.
      *
      * @param criteria Object containing values to insert.
      * @return An Object which is the id of the row that was inserted
      * (if the table has a primary key) or null (if the table does not
      * have a primary key).
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static ObjectKey doInsert(Criteria criteria) throws TorqueException
     {
         Connection con = null;
         ObjectKey id = null;
 
         try
         {
             con = Transaction.beginOptional(
                     criteria.getDbName(),
                     criteria.isUseTransaction());
             id = doInsert(criteria, con);
             Transaction.commit(con);
         }
         catch (TorqueException e)
         {
             Transaction.safeRollback(con);
             throw e;
         }
 
         return id;
     }
 
     /***
      * Method to perform inserts based on values and keys in a
      * Criteria.
      * <p>
      * If the primary key is auto incremented the data in Criteria
      * will be inserted and the auto increment value will be returned.
      * <p>
      * If the primary key is included in Criteria then that value will
      * be used to insert the row.
      * <p>
      * If no primary key is included in Criteria then we will try to
      * figure out the primary key from the database map and insert the
      * row with the next available id using util.db.IDBroker.
      * <p>
      * If no primary key is defined for the table the values will be
      * inserted as specified in Criteria and null will be returned.
      *
      * @param criteria Object containing values to insert.
      * @param con A Connection.
      * @return An Object which is the id of the row that was inserted
      * (if the table has a primary key) or null (if the table does not
      * have a primary key).
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static ObjectKey doInsert(Criteria criteria, Connection con)
         throws TorqueException
     {
         SimpleKey id = null;
 
         // Get the table name and method for determining the primary
         // key value.
         String table = null;
         Iterator keys = criteria.keySet().iterator();
         if (keys.hasNext())
         {
             table = criteria.getTableName((String) keys.next());
         }
         else
         {
             throw new TorqueException("Database insert attempted without "
                     + "anything specified to insert");
         }
 
         String dbName = criteria.getDbName();
         Database database = Torque.getDatabase(dbName);
         DatabaseMap dbMap = database.getDatabaseMap();
         TableMap tableMap = dbMap.getTable(table);
         Object keyInfo = tableMap.getPrimaryKeyMethodInfo();
         IdGenerator keyGen
                 = database.getIdGenerator(tableMap.getPrimaryKeyMethod());
 
         ColumnMap pk = getPrimaryKey(criteria);
 
         // If the keyMethod is SEQUENCE or IDBROKERTABLE, get the id
         // before the insert.
         if (keyGen != null && keyGen.isPriorToInsert())
         {
             // pk will be null if there is no primary key defined for the table
             // we're inserting into.
             if (pk != null && !criteria.containsKey(pk.getFullyQualifiedName()))
             {
                 id = getId(pk, keyGen, con, keyInfo);
                 criteria.add(pk.getFullyQualifiedName(), id);
             }
         }
 
         // Use Village to perform the insert.
         TableDataSet tds = null;
         try
         {
             String tableName = SQLBuilder.getFullTableName(table, dbName);
             tds = new TableDataSet(con, tableName);
             Record rec = tds.addRecord();
             // not the fully qualified name, insertOrUpdateRecord wants to use table as an index...
             BasePeer.insertOrUpdateRecord(rec, table, dbName, criteria);
         }
         catch (DataSetException e)
         {
             throwTorqueException(e);
         }
         catch (SQLException e)
         {
             throwTorqueException(e);
         }
         catch (TorqueException e)
         {
             throwTorqueException(e);
         }
         finally
         {
             VillageUtils.close(tds);
         }
 
         // If the primary key column is auto-incremented, get the id
         // now.
         if (keyGen != null && keyGen.isPostInsert())
         {
             id = getId(pk, keyGen, con, keyInfo);
         }
 
         return id;
     }
 
     /***
      * Create an Id for insertion in the Criteria
      *
      * @param pk ColumnMap for the Primary key
      * @param keyGen The Id Generator object
      * @param con The SQL Connection to run the id generation under
      * @param keyInfo KeyInfo Parameter from the Table map
      *
      * @return A simple Key representing the new Id value
      * @throws TorqueException Possible errors get wrapped in here.
      */
     private static SimpleKey getId(ColumnMap pk, IdGenerator keyGen, Connection con, Object keyInfo)
             throws TorqueException
     {
         SimpleKey id = null;
 
         try
         {
             if (pk != null && keyGen != null)
             {
                 if (pk.getType() instanceof Number)
                 {
                     id = new NumberKey(
                             keyGen.getIdAsBigDecimal(con, keyInfo));
                 }
                 else
                 {
                     id = new StringKey(keyGen.getIdAsString(con, keyInfo));
                 }
             }
         }
         catch (Exception e)
         {
             throwTorqueException(e);
         }
         return id;
     }
 
     /***
      * Grouping of code used in both doInsert() and doUpdate()
      * methods.  Sets up a Record for saving.
      *
      * @param rec A Record.
      * @param table Name of table.
      * @param criteria A Criteria.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     private static void insertOrUpdateRecord(
         Record rec,
         String table,
         String dbName,
         Criteria criteria)
         throws TorqueException
     {
         DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
 
         ColumnMap[] columnMaps = dbMap.getTable(table).getColumns();
         boolean shouldSave = false;
         for (int j = 0; j < columnMaps.length; j++)
         {
             ColumnMap colMap = columnMaps[j];
             String colName = colMap.getColumnName();
             String key = new StringBuffer(colMap.getTableName())
                     .append('.')
                     .append(colName)
                     .toString();
             if (criteria.containsKey(key))
             {
                 try
                 {
                     VillageUtils.setVillageValue(criteria, key, rec, colName);
                     shouldSave = true;
                 }
                 catch (Exception e)
                 {
                     throwTorqueException(e);
                 }
             }
         }
 
         if (shouldSave)
         {
             try
             {
                 rec.save();
             }
             catch (Exception e)
             {
                 throwTorqueException(e);
             }
         }
         else
         {
             throw new TorqueException("No changes to save");
         }
     }
 
     /***
      * Method to create an SQL query for display only based on values in a
      * Criteria.
      *
      * @param criteria A Criteria.
      * @return the SQL query for display
      * @exception TorqueException Trouble creating the query string.
      */
     static String createQueryDisplayString(Criteria criteria)
         throws TorqueException
     {
         return createQuery(criteria).toString();
     }
 
     /***
      * Method to create an SQL query for actual execution based on values in a
      * Criteria.
      *
      * @param criteria A Criteria.
      * @return the SQL query for actual execution
      * @exception TorqueException Trouble creating the query string.
      */
     public static String createQueryString(Criteria criteria)
         throws TorqueException
     {
         Query query = createQuery(criteria);
         return query.toString();
     }
 
     /***
      * Method to create an SQL query based on values in a Criteria.  Note that
      * final manipulation of the limit and offset are performed when the query
      * is actually executed.
      *
      * @param criteria A Criteria.
      * @return the sql query
      * @exception TorqueException Trouble creating the query string.
      */
     static Query createQuery(Criteria criteria)
         throws TorqueException
     {
         return SQLBuilder.buildQueryClause(criteria, null, new SQLBuilder.QueryCallback() {
                 public String process(Criteria.Criterion criterion, List params)
                 {
                     return criterion.toString();
                 }
             });
     }
 
     /***
      * Returns all results.
      *
      * @param criteria A Criteria.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List doSelect(Criteria criteria) throws TorqueException
     {
         Connection con = null;
         List results = null;
 
         try
         {
             con = Transaction.beginOptional(
                     criteria.getDbName(),
                     criteria.isUseTransaction());
             results = doSelect(criteria, con);
             Transaction.commit(con);
         }
         catch (TorqueException e)
         {
             Transaction.safeRollback(con);
             throw e;
         }
         return results;
     }
 
     /***
      * Returns all results.
      *
      * @param criteria A Criteria.
      * @param con A Connection.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List doSelect(Criteria criteria, Connection con)
         throws TorqueException
     {
         Query query = createQuery(criteria);
         DB dbadapter = Torque.getDB(criteria.getDbName());
 
         // Call Village depending on the capabilities of the DB
         return executeQuery(query.toString(),
                 dbadapter.supportsNativeOffset() ? 0 : criteria.getOffset(),
                 dbadapter.supportsNativeLimit() ? -1 : criteria.getLimit(),
                 criteria.isSingleRecord(),
                 con);
     }
 
     /***
      * Utility method which executes a given sql statement.  This
      * method should be used for select statements only.  Use
      * executeStatement for update, insert, and delete operations.
      *
      * @param queryString A String with the sql statement to execute.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List executeQuery(String queryString) throws TorqueException
     {
         return executeQuery(queryString, Torque.getDefaultDB(), false);
     }
 
     /***
      * Utility method which executes a given sql statement.  This
      * method should be used for select statements only.  Use
      * executeStatement for update, insert, and delete operations.
      *
      * @param queryString A String with the sql statement to execute.
      * @param dbName The database to connect to.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List executeQuery(String queryString, String dbName)
         throws TorqueException
     {
         return executeQuery(queryString, dbName, false);
     }
 
     /***
      * Method for performing a SELECT.  Returns all results.
      *
      * @param queryString A String with the sql statement to execute.
      * @param dbName The database to connect to.
      * @param singleRecord Whether or not we want to select only a
      * single record.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List executeQuery(
         String queryString,
         String dbName,
         boolean singleRecord)
         throws TorqueException
     {
         return executeQuery(queryString, 0, -1, dbName, singleRecord);
     }
 
     /***
      * Method for performing a SELECT.  Returns all results.
      *
      * @param queryString A String with the sql statement to execute.
      * @param singleRecord Whether or not we want to select only a
      * single record.
      * @param con A Connection.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List executeQuery(
         String queryString,
         boolean singleRecord,
         Connection con)
         throws TorqueException
     {
         return executeQuery(queryString, 0, -1, singleRecord, con);
     }
 
     /***
      * Method for performing a SELECT.
      *
      * @param queryString A String with the sql statement to execute.
      * @param start The first row to return.
      * @param numberOfResults The number of rows to return.
      * @param dbName The database to connect to.
      * @param singleRecord Whether or not we want to select only a
      * single record.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List executeQuery(
         String queryString,
         int start,
         int numberOfResults,
         String dbName,
         boolean singleRecord)
         throws TorqueException
     {
         Connection con = null;
         List results = null;
         try
         {
             con = Torque.getConnection(dbName);
             // execute the query
             results = executeQuery(
                     queryString,
                     start,
                     numberOfResults,
                     singleRecord,
                     con);
         }
         finally
         {
             Torque.closeConnection(con);
         }
         return results;
     }
 
     /***
      * Method for performing a SELECT.  Returns all results.
      *
      * @param queryString A String with the sql statement to execute.
      * @param start The first row to return.
      * @param numberOfResults The number of rows to return.
      * @param singleRecord Whether or not we want to select only a
      * single record.
      * @param con A Connection.
      * @return List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List executeQuery(
         String queryString,
         int start,
         int numberOfResults,
         boolean singleRecord,
         Connection con)
         throws TorqueException
     {
         QueryDataSet qds = null;
         List results = Collections.EMPTY_LIST;
         try
         {
             // execute the query
             long startTime = System.currentTimeMillis();
             qds = new QueryDataSet(con, queryString);
             if (log.isDebugEnabled())
             {
                 log.debug("Elapsed time="
                         + (System.currentTimeMillis() - startTime) + " ms");
             }
             results = getSelectResults(
                     qds, start, numberOfResults, singleRecord);
         }
         catch (DataSetException e)
         {
             throwTorqueException(e);
         }
         catch (SQLException e)
         {
             throwTorqueException(e);
         }
         finally
         {
             VillageUtils.close(qds);
         }
         return results;
     }
 
     /***
      * Returns all records in a QueryDataSet as a List of Record
      * objects.  Used for functionality like util.LargeSelect.
      *
      * @see #getSelectResults(QueryDataSet, int, int, boolean)
      * @param qds the QueryDataSet
      * @return a List of Record objects
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List getSelectResults(QueryDataSet qds)
         throws TorqueException
     {
         return getSelectResults(qds, 0, -1, false);
     }
 
     /***
      * Returns all records in a QueryDataSet as a List of Record
      * objects.  Used for functionality like util.LargeSelect.
      *
      * @see #getSelectResults(QueryDataSet, int, int, boolean)
      * @param qds the QueryDataSet
      * @param singleRecord
      * @return a List of Record objects
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List getSelectResults(QueryDataSet qds, boolean singleRecord)
         throws TorqueException
     {
         return getSelectResults(qds, 0, -1, singleRecord);
     }
 
     /***
      * Returns numberOfResults records in a QueryDataSet as a List
      * of Record objects.  Starting at record 0.  Used for
      * functionality like util.LargeSelect.
      *
      * @see #getSelectResults(QueryDataSet, int, int, boolean)
      * @param qds the QueryDataSet
      * @param numberOfResults
      * @param singleRecord
      * @return a List of Record objects
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List getSelectResults(
         QueryDataSet qds,
         int numberOfResults,
         boolean singleRecord)
         throws TorqueException
     {
         List results = null;
         if (numberOfResults != 0)
         {
             results = getSelectResults(qds, 0, numberOfResults, singleRecord);
         }
         return results;
     }
 
     /***
      * Returns numberOfResults records in a QueryDataSet as a List
      * of Record objects.  Starting at record start.  Used for
      * functionality like util.LargeSelect.
      *
      * @param qds The <code>QueryDataSet</code> to extract results
      * from.
      * @param start The index from which to start retrieving
      * <code>Record</code> objects from the data set.
      * @param numberOfResults The number of results to return (or
      * <code> -1</code> for all results).
      * @param singleRecord Whether or not we want to select only a
      * single record.
      * @return A <code>List</code> of <code>Record</code> objects.
      * @exception TorqueException If any <code>Exception</code> occurs.
      */
     public static List getSelectResults(
         QueryDataSet qds,
         int start,
         int numberOfResults,
         boolean singleRecord)
         throws TorqueException
     {
         List results = null;
         try
         {
             if (numberOfResults < 0)
             {
                 results = new ArrayList();
                 qds.fetchRecords();
             }
             else
             {
                 results = new ArrayList(numberOfResults);
                 qds.fetchRecords(start, numberOfResults);
             }
 
             int startRecord = 0;
 
             //Offset the correct number of records
             if (start > 0 && numberOfResults <= 0)
             {
                 startRecord = start;
             }
 
             // Return a List of Record objects.
             for (int i = startRecord; i < qds.size(); i++)
             {
                 Record rec = qds.getRecord(i);
                 results.add(rec);
             }
 
             if (results.size() > 1 && singleRecord)
             {
                 handleMultipleRecords(qds);
             }
         }
         catch (Exception e)
         {
             throwTorqueException(e);
         }
         return results;
     }
 
     /***
      * Helper method which returns the primary key contained
      * in the given Criteria object.
      *
      * @param criteria A Criteria.
      * @return ColumnMap if the Criteria object contains a primary
      *          key, or null if it doesn't.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     private static ColumnMap getPrimaryKey(Criteria criteria)
         throws TorqueException
     {
         // Assume all the keys are for the same table.
         String key = (String) criteria.keys().nextElement();
 
         String table = criteria.getTableName(key);
         ColumnMap pk = null;
 
         if (!table.equals(""))
         {
             DatabaseMap dbMap = Torque.getDatabaseMap(criteria.getDbName());
             if (dbMap == null)
             {
                 throw new TorqueException("dbMap is null");
             }
             if (dbMap.getTable(table) == null)
             {
                 throw new TorqueException("dbMap.getTable() is null");
             }
 
             ColumnMap[] columns = dbMap.getTable(table).getColumns();
 
             for (int i = 0; i < columns.length; i++)
             {
                 if (columns[i].isPrimaryKey())
                 {
                     pk = columns[i];
                     break;
                 }
             }
         }
         return pk;
     }
 
     /***
      * Convenience method used to update rows in the DB.  Checks if a
      * <i>single</i> int primary key is specified in the Criteria
      * object and uses it to perform the udpate.  If no primary key is
      * specified an Exception will be thrown.
      * <p>
      * Use this method for performing an update of the kind:
      * <p>
      * "WHERE primary_key_id = an int"
      * <p>
      * To perform an update with non-primary key fields in the WHERE
      * clause use doUpdate(criteria, criteria).
      *
      * @param updateValues A Criteria object containing values used in
      *        set clause.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void doUpdate(Criteria updateValues) throws TorqueException
     {
         Connection con = null;
         try
         {
             con = Transaction.beginOptional(
                     updateValues.getDbName(),
                     updateValues.isUseTransaction());
             doUpdate(updateValues, con);
             Transaction.commit(con);
         }
         catch (TorqueException e)
         {
             Transaction.safeRollback(con);
             throw e;
         }
     }
 
     /***
      * Convenience method used to update rows in the DB.  Checks if a
      * <i>single</i> int primary key is specified in the Criteria
      * object and uses it to perform the udpate.  If no primary key is
      * specified an Exception will be thrown.
      * <p>
      * Use this method for performing an update of the kind:
      * <p>
      * "WHERE primary_key_id = an int"
      * <p>
      * To perform an update with non-primary key fields in the WHERE
      * clause use doUpdate(criteria, criteria).
      *
      * @param updateValues A Criteria object containing values used in
      * set clause.
      * @param con A Connection.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void doUpdate(Criteria updateValues, Connection con)
         throws TorqueException
     {
         ColumnMap pk = getPrimaryKey(updateValues);
         Criteria selectCriteria = null;
 
         if (pk != null && updateValues.containsKey(pk.getFullyQualifiedName()))
         {
             selectCriteria = new Criteria(2);
             selectCriteria.put(pk.getFullyQualifiedName(),
                 updateValues.remove(pk.getFullyQualifiedName()));
         }
         else
         {
             throw new TorqueException("No PK specified for database update");
         }
 
         doUpdate(selectCriteria, updateValues, con);
     }
 
     /***
      * Method used to update rows in the DB.  Rows are selected based
      * on selectCriteria and updated using values in updateValues.
      * <p>
      * Use this method for performing an update of the kind:
      * <p>
      * WHERE some_column = some value AND could_have_another_column =
      * another value AND so on...
      *
      * @param selectCriteria A Criteria object containing values used in where
      *        clause.
      * @param updateValues A Criteria object containing values used in set
      *        clause.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void doUpdate(Criteria selectCriteria, Criteria updateValues)
         throws TorqueException
     {
         Connection con = null;
         try
         {
             con = Transaction.beginOptional(
                     selectCriteria.getDbName(),
                     updateValues.isUseTransaction());
             doUpdate(selectCriteria, updateValues, con);
             Transaction.commit(con);
         }
         catch (TorqueException e)
         {
             Transaction.safeRollback(con);
             throw e;
         }
     }
 
     /***
      * Method used to update rows in the DB.  Rows are selected based
      * on criteria and updated using values in updateValues.
      * <p>
      * Use this method for performing an update of the kind:
      * <p>
      * WHERE some_column = some value AND could_have_another_column =
      * another value AND so on.
      *
      * @param criteria A Criteria object containing values used in where
      *        clause.
      * @param updateValues A Criteria object containing values used in set
      *        clause.
      * @param con A Connection.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void doUpdate(
         Criteria criteria,
         final Criteria updateValues,
         Connection con)
         throws TorqueException
     {
         Set tables = SQLBuilder.getTableSet(criteria, null);
 
         try
         {
             processTables(criteria, tables, con, new ProcessCallback() {
                     public void process (String table, String dbName, Record rec)
                         throws Exception
                     {
                         // Callback must be called with table name without Schema!
                         BasePeer.insertOrUpdateRecord(rec, table, dbName, updateValues);
                     }
                 });
         }
         catch (Exception e)
         {
             throwTorqueException(e);
         }
     }
 
     /***
      * Utility method which executes a given sql statement.  This
      * method should be used for update, insert, and delete
      * statements.  Use executeQuery() for selects.
      *
      * @param statementString A String with the sql statement to execute.
      * @return The number of rows affected.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static int executeStatement(String statementString) throws TorqueException
     {
         return executeStatement(statementString, Torque.getDefaultDB());
     }
 
     /***
      * Utility method which executes a given sql statement.  This
      * method should be used for update, insert, and delete
      * statements.  Use executeQuery() for selects.
      *
      * @param statementString A String with the sql statement to execute.
      * @param dbName Name of database to connect to.
      * @return The number of rows affected.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static int executeStatement(String statementString, String dbName)
         throws TorqueException
     {
         Connection con = null;
         int rowCount = -1;
         try
         {
             con = Torque.getConnection(dbName);
             rowCount = executeStatement(statementString, con);
         }
         finally
         {
             Torque.closeConnection(con);
         }
         return rowCount;
     }
 
     /***
      * Utility method which executes a given sql statement.  This
      * method should be used for update, insert, and delete
      * statements.  Use executeQuery() for selects.
      *
      * @param statementString A String with the sql statement to execute.
      * @param con A Connection.
      * @return The number of rows affected.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static int executeStatement(String statementString, Connection con)
         throws TorqueException
     {
         int rowCount = -1;
         Statement statement = null;
         try
         {
             statement = con.createStatement();
             rowCount = statement.executeUpdate(statementString);
         }
         catch (SQLException e)
         {
             throw new TorqueException(e);
         }
         finally
         {
             if (statement != null)
             {
                 try
                 {
                     statement.close();
                 }
                 catch (SQLException e)
                 {
                     throw new TorqueException(e);
                 }
             }
         }
         return rowCount;
     }
 
     /***
      * If the user specified that (s)he only wants to retrieve a
      * single record and multiple records are retrieved, this method
      * is called to handle the situation.  The default behavior is to
      * throw an exception, but subclasses can override this method as
      * needed.
      *
      * @param ds The DataSet which contains multiple records.
      * @exception TooManyRowsException Couldn't handle multiple records.
      */
     protected static void handleMultipleRecords(DataSet ds)
         throws TooManyRowsException
     {
         throw new TooManyRowsException("Criteria expected single Record and "
                 + "Multiple Records were selected");
     }
 
     /***
      * This method returns the MapBuilder specified in the name
      * parameter.  You should pass in the full path to the class, ie:
      * org.apache.torque.util.db.map.TurbineMapBuilder.  The
      * MapBuilder instances are cached in the TorqueInstance for speed.
      *
      * @param name name of the MapBuilder
      * @return A MapBuilder, not null
      * @throws TorqueException if the Map Builder cannot be instantiated
      * @deprecated Use Torque.getMapBuilder(name) instead
      */
     public static MapBuilder getMapBuilder(String name)
         throws TorqueException
     {
         return Torque.getMapBuilder(name);
     }
 
     /***
      * Performs a SQL <code>select</code> using a PreparedStatement.
      * Note: this method does not handle null criteria values.
      *
      * @param criteria
      * @param con
      * @return a List of Record objects.
      * @throws TorqueException Error performing database query.
      */
     public static List doPSSelect(Criteria criteria, Connection con)
         throws TorqueException
     {
         List v = null;
 
         StringBuffer qry = new StringBuffer();
         List params = new ArrayList(criteria.size());
 
         createPreparedStatement(criteria, qry, params);
 
         PreparedStatement statement = null;
         try
         {
             statement = con.prepareStatement(qry.toString());
 
             for (int i = 0; i < params.size(); i++)
             {
                 Object param = params.get(i);
                 if (param instanceof java.sql.Date)
                 {
                     statement.setDate(i + 1, (java.sql.Date) param);
                 }
                 else if (param instanceof NumberKey)
                 {
                     statement.setBigDecimal(i + 1,
                         ((NumberKey) param).getBigDecimal());
                 }
                 else if (param instanceof Integer)
                 {
                     statement.setInt(i + 1, ((Integer) param).intValue());
                 }
                 else
                 {
                     statement.setString(i + 1, param.toString());
                 }
             }
 
             QueryDataSet qds = null;
             try
             {
                 qds = new QueryDataSet(statement.executeQuery());
                 v = getSelectResults(qds);
             }
             finally
             {
                 VillageUtils.close(qds);
             }
         }
         catch (DataSetException e)
         {
             throwTorqueException(e);
         }
         catch (SQLException e)
         {
             throwTorqueException(e);
         }
         finally
         {
             if (statement != null)
             {
                 try
                 {
                     statement.close();
                 }
                 catch (SQLException e)
                 {
                     throw new TorqueException(e);
                 }
             }
         }
         return v;
     }
 
     /***
      * Do a Prepared Statement select according to the given criteria
      *
      * @param criteria
      * @return a List of Record objects.
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static List doPSSelect(Criteria criteria) throws TorqueException
     {
         Connection con = Torque.getConnection(criteria.getDbName());
         List v = null;
 
         try
         {
             v = doPSSelect(criteria, con);
         }
         finally
         {
             Torque.closeConnection(con);
         }
         return v;
     }
 
     /***
      * Create a new PreparedStatement.  It builds a string representation
      * of a query and a list of PreparedStatement parameters.
      *
      * @param criteria
      * @param queryString
      * @param params
      * @throws TorqueException Any exceptions caught during processing will be
      *         rethrown wrapped into a TorqueException.
      */
     public static void createPreparedStatement(
         Criteria criteria,
         StringBuffer queryString,
         List params)
         throws TorqueException
     {
         Query query = SQLBuilder.buildQueryClause(criteria, params, new SQLBuilder.QueryCallback() {
                 public String process(Criteria.Criterion criterion, List params)
                 {
                     StringBuffer sb = new StringBuffer();
                     criterion.appendPsTo(sb, params);
                     return sb.toString();
                 }
             });
 
         String sql = query.toString();
         log.debug(sql);
 
         queryString.append(sql);
     }
 
     /***
      * Checks all columns in the criteria to see whether
      * booleanchar and booleanint columns are queried with a boolean.
      * If yes, the query values are mapped onto values the database
      * does understand, i.e. 0 and 1 for booleanints and N and Y for
      * booleanchar columns.
      *
      * @param criteria The criteria to be checked for booleanint and booleanchar
      *        columns.
      * @param defaultTableMap the table map to be used if the table name is
      *        not given in a column.
      * @throws TorqueException if the database map for the criteria cannot be
      *         retrieved.
      */
     public static void correctBooleans(
             Criteria criteria,
             TableMap defaultTableMap)
         throws TorqueException
     {
         Iterator keyIt = criteria.keySet().iterator();
         while (keyIt.hasNext())
         {
             String key = (String) keyIt.next();
             String columnName;
             TableMap tableMap = null;
             int dotPosition = key.lastIndexOf(".");
             if (dotPosition == -1)
             {
                 columnName = key;
                 tableMap = defaultTableMap;
             }
             else
             {
                 columnName = key.substring(dotPosition + 1);
                 String tableName = key.substring(0, dotPosition);
                 String databaseName = criteria.getDbName();
                 if (databaseName == null)
                 {
                     databaseName = Torque.getDefaultDB();
                 }
                 DatabaseMap databaseMap = Torque.getDatabaseMap(databaseName);
                 if (databaseMap != null)
                 {
                     tableMap = databaseMap.getTable(tableName);
                 }
                 if (tableMap == null)
                 {
                     // try aliases
                     Map aliases = criteria.getAliases();
                     if (aliases.get(tableName) != null)
                     {
                         tableName = (String) aliases.get(tableName);
                         tableMap = databaseMap.getTable(tableName);
                     }
                 }
             }
             if (tableMap == null)
             {
                 // no description of table available, do not modify anything
                 continue;
             }
 
             ColumnMap columnMap = tableMap.getColumn(columnName);
             if (columnMap != null)
             {
                 if ("BOOLEANINT".equals(columnMap.getTorqueType()))
                 {
                     Criteria.Criterion criterion = criteria.getCriterion(key);
                     replaceBooleanValues(
                             criterion,
                             new Integer(1),
                             new Integer(0));
                 }
                 else if ("BOOLEANCHAR".equals(columnMap.getTorqueType()))
                 {
                     Criteria.Criterion criterion = criteria.getCriterion(key);
                     replaceBooleanValues(criterion, "Y", "N");
                  }
             }
         }
     }
 
     /***
      * Replaces any Boolean value in the criterion and its attached Criterions
      * by trueValue if the Boolean equals <code>Boolean.TRUE</code>
      * and falseValue if the Boolean equals <code>Boolean.FALSE</code>.
      *
      * @param criterion the criterion to replace Boolean values in.
      * @param trueValue the value by which Boolean.TRUE should be replaced.
      * @param falseValue the value by which Boolean.FALSE should be replaced.
      */
     private static void replaceBooleanValues(
             Criteria.Criterion criterion,
             Object trueValue,
             Object falseValue)
     {
         // attachedCriterions also contains the criterion itself,
         // so no additional treatment is needed for the criterion itself.
         Criteria.Criterion[] attachedCriterions
             = criterion.getAttachedCriterion();
         for (int i = 0; i < attachedCriterions.length; ++i)
         {
             Object criterionValue
                     = attachedCriterions[i].getValue();
             if (criterionValue instanceof Boolean)
             {
                 Boolean booleanValue = (Boolean) criterionValue;
                 attachedCriterions[i].setValue(
                         Boolean.TRUE.equals(booleanValue)
                                 ? trueValue
                                 : falseValue);
             }
 
         }
 
     }
 
     /***
      * Process the result of a Table list generation.
      * This runs the statements onto the list of tables and
      * provides a callback hook to add functionality.
      *
      * This method should've been in SQLBuilder, but is uses the handleMultipleRecords callback thingie..
      *
      * @param crit The criteria
      * @param tables A set of Tables to run on
      * @param con The SQL Connection to run the statements on
      * @param pc A ProcessCallback object
      *
      * @throws Exception An Error occured (should be wrapped into TorqueException)
      */
     private static void processTables(Criteria crit, Set tables, Connection con, ProcessCallback pc)
             throws Exception
     {
         String dbName = crit.getDbName();
         DB db = Torque.getDB(dbName);
         DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
 
         // create the statements for the tables
         for (Iterator it = tables.iterator(); it.hasNext();)
         {
             String table = (String) it.next();
             KeyDef kd = new KeyDef();
             Set whereClause = new HashSet();
 
             ColumnMap[] columnMaps = dbMap.getTable(table).getColumns();
 
             for (int j = 0; j < columnMaps.length; j++)
             {
                 ColumnMap colMap = columnMaps[j];
                 if (colMap.isPrimaryKey())
                 {
                     kd.addAttrib(colMap.getColumnName());
                 }
 
                 String key = new StringBuffer(colMap.getTableName())
                         .append('.')
                         .append(colMap.getColumnName())
                         .toString();
 
                 if (crit.containsKey(key))
                 {
                     if (crit
                             .getComparison(key)
                             .equals(Criteria.CUSTOM))
                     {
                         whereClause.add(crit.getString(key));
                     }
                     else
                     {
                         whereClause.add(
                                 SqlExpression.build(
                                         colMap.getColumnName(),
                                         crit.getValue(key),
                                         crit.getComparison(key),
                                         crit.isIgnoreCase(),
                                         db));
                     }
                 }
             }
 
             // Execute the statement for each table
             TableDataSet tds = null;
             try
             {
                 String tableName = SQLBuilder.getFullTableName(table, dbName);
 
                 // Get affected records.
                 tds = new TableDataSet(con, tableName, kd);
                 String sqlSnippet = StringUtils.join(whereClause.iterator(), " AND ");
 
                 if (log.isDebugEnabled())
                 {
                     log.debug("BasePeer: whereClause=" + sqlSnippet);
                 }
 
                 tds.where(sqlSnippet);
                 tds.fetchRecords();
 
                 if (tds.size() > 1 && crit.isSingleRecord())
                 {
                     handleMultipleRecords(tds);
                 }
 
                 for (int j = 0; j < tds.size(); j++)
                 {
                     Record rec = tds.getRecord(j);
 
                     if (pc != null)
                     {
                         // Table name _without_ schema!
                         pc.process(table, dbName, rec);
                     }
                 }
             }
             finally
             {
                 VillageUtils.close(tds);
             }
         }
     }
 
     /***
      * Inner Interface that defines the Callback method for
      * the Record Processing
      */
     protected interface ProcessCallback
     {
         void process (String table, String dbName, Record rec)
                 throws Exception;
     }
 }