OK, it's done. I came up with another solution for Postgres which
requires minimal changes to existing classes: Make a new User class
which extends TurbineUser for postgres (and one to extend
TurbineUserPeer). I also used the DBConnection as the transaction key,
and not Connection because then the Commit and RollBack can easily
release the DBConnection back to the pool. Below are the patches to
existing code, and the two new files can be found in a tgz archive at
http://nissim.karpenstein.com/turbine_postgres.tgz ... I hope you all
buy it this time :-)
-Nissim
Index: docs/schemas/Postgres_users_roles_permissions.sql
===================================================================
RCS file:
/products/cvs/turbine/turbine/docs/schemas/Postgres_users_roles_permissions.sql,v
retrieving revision 1.1
diff -r1.1 Postgres_users_roles_permissions.sql
83c83
< OBJECTDATA varchar (255), -- oder doch
bytea???
---
> OBJECTDATA oid,
Index: src/java/org/apache/turbine/om/peer/BasePeer.java
===================================================================
RCS file:
/products/cvs/turbine/turbine/src/java/org/apache/turbine/om/peer/BasePeer.java,v
retrieving revision 1.25
diff -r1.25 BasePeer.java
287a288,341
> * Method to begin a transaction.
> *
> * @return the DBConnection for the transaction
> */
> public static DBConnection beginTransaction(String dbName) throws Exception
> {
> // get a connection to the db
> DBConnection dbCon = DBBroker.getInstance().getConnection( dbName );
> Connection conn = dbCon.getConnection();
>
> Statement stmt = conn.createStatement();
> stmt.executeUpdate("BEGIN TRANSACTION");
> stmt.close();
>
> return dbCon;
>
> }
>
> /**
> * Method to commit a transaction.
> *
> * @param dbCon The Connection for the transaction
> */
> public static void commitTransaction(DBConnection dbCon) throws Exception
> {
> Connection conn = dbCon.getConnection();
>
> Statement stmt = conn.createStatement();
> stmt.executeUpdate("COMMIT TRANSACTION");
> stmt.close();
>
> // release a connection to the pool
> DBBroker.getInstance().releaseConnection( dbCon );
> }
>
> /**
> * Method to roll bask a transaction.
> *
> * @param dbCon The Connection for the transaction
> */
> public static void rollBackTransaction(DBConnection dbCon) throws Exception
> {
> Connection conn = dbCon.getConnection();
>
> Statement stmt = conn.createStatement();
> stmt.executeUpdate("ROLLBACK TRANSACTION");
> stmt.close();
>
> // release a connection to the pool
> DBBroker.getInstance().releaseConnection( dbCon );
> }
>
>
> /**
291c345
< * @param con - a db connection
---
> * @param dbCon - a db connection
296c350
< public static void deleteAll( Connection con, String table, String
column, int value)
---
> public static void deleteAll( DBConnection dbCon, String table, String column,
>int value)
298a353
> Connection conn = dbCon.getConnection();
303c358
< statement = con.createStatement();
---
> statement = conn.createStatement();
334c389
< DBConnection db = null;
---
> DBConnection dbCon = null;
338,339c393
< db = DBBroker.getInstance().getConnection();
< Connection connection = db.getConnection();
---
> dbCon = DBBroker.getInstance().getConnection();
341c395
< deleteAll ( connection, table, column, value );
---
> deleteAll ( dbCon, table, column, value );
345c399
< DBBroker.getInstance().releaseConnection(db);
---
> DBBroker.getInstance().releaseConnection(dbCon);
354,358c408
< DBConnection dbCon = null;
< TableDataSet tds = null;
< DB db = DBBroker.getInstance().getDB( criteria.getDbName() );
< DatabaseMap dbMap = DBBroker.getInstance().getDatabaseMap(
criteria.getMapName() );
<
---
> DBConnection dbCon = null;
363d412
< Connection connection = dbCon.getConnection();
365,378c414,446
< // set up a list of required tables and add extra entries
to
< // criteria if directed to delete all related records.
< // StringStackBuffer.add() only adds element if it is
unique.
< StringStackBuffer tables = new StringStackBuffer();
< Enumeration e = criteria.keys();
< while(e.hasMoreElements())
< {
< String key = (String)e.nextElement();
<
tables.add(criteria.getTableName(key));
< if ( criteria.isCascade() )
< {
< // This steps thru all the columns in the
database.
< TableMap[] tableMaps = dbMap.getTables();
< for (int i=0; i<tableMaps.length; i++)
---
> doDelete(criteria, dbCon);
>
> }
> finally
> {
> DBBroker.getInstance().releaseConnection(dbCon);
> }
> }
>
> public static void doDelete(Criteria criteria, DBConnection dbCon)
> throws Exception
> {
> DB db = DBBroker.getInstance().getDB( criteria.getDbName() );
> DatabaseMap dbMap = DBBroker.getInstance().getDatabaseMap(
>criteria.getMapName() );
> Connection connection = dbCon.getConnection();
>
> // set up a list of required tables and add extra entries to
> // criteria if directed to delete all related records.
> // StringStackBuffer.add() only adds element if it is unique.
> StringStackBuffer tables = new StringStackBuffer();
> Enumeration e = criteria.keys();
> while(e.hasMoreElements())
> {
> String key = (String)e.nextElement();
> tables.add(criteria.getTableName(key));
> if ( criteria.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++)
380,381c448,452
< ColumnMap[] columnMaps =
tableMaps[i].getColumns();
< for (int j=0; j<columnMaps.length; j++)
---
> if ( columnMaps[j].isForeignKey()
> // only delete rows where the foreign key is also a
>primary key.
> // other rows need updated, but that is not implemented.
> && columnMaps[j].isPrimaryKey()
> && key.equals(columnMaps[j].getRelatedName()) )
383,391c454,455
< if ( columnMaps[j].isForeignKey()
< // only delete rows where the foreign
key is also a primary key.
< // other rows need updated, but that
is not implemented.
< && columnMaps[j].isPrimaryKey()
< &&
key.equals(columnMaps[j].getRelatedName()) )
< {
< tables.add(tableMaps[i].getName());
< criteria.add(
columnMaps[j].getFullyQualifiedName(), criteria.getValue(key) );
< }
---
> tables.add(tableMaps[i].getName());
> criteria.add( columnMaps[j].getFullyQualifiedName(),
>criteria.getValue(key) );
394c458,473
< }
---
> }
> }
> }
>
> for (int i=0; i<tables.size(); i++)
> {
> KeyDef kd = new KeyDef();
> StringStackBuffer whereClause = new StringStackBuffer();
>
> DatabaseMap tempDbMap = null;
> try
> {
> // attempt to find the right DatabaseMap for the table that we
> // are currently working on. This allows for distributed
>DatabaseMap's
> // across several files.
> tempDbMap = DBBroker.getInstance().getDatabaseMap( tables.get(i) );
396,397c475
<
< for (int i=0; i<tables.size(); i++)
---
> catch (Exception f)
399,400c477,478
< KeyDef kd = new KeyDef();
< StringStackBuffer whereClause = new
StringStackBuffer();
---
> tempDbMap = dbMap;
> }
402,403c480,484
< DatabaseMap tempDbMap = null;
< try
---
> ColumnMap[] columnMaps = tempDbMap.getTable( tables.get(i)
>).getColumns();
> for (int j=0; j<columnMaps.length; j++)
> {
> ColumnMap colMap = columnMaps[j];
> if ( colMap.isPrimaryKey() )
405,408c486
< // attempt to find the right DatabaseMap for the
table that we
< // are currently working on. This allows for
distributed DatabaseMap's
< // across several files.
< tempDbMap = DBBroker.getInstance().getDatabaseMap(
tables.get(i) );
---
> kd.addAttrib( colMap.getColumnName() );
410c488,490
< catch (Exception f)
---
> String key = new StringBuffer(colMap.getTableName())
> .append('.').append(colMap.getColumnName()).toString();
> if ( criteria.containsKey(key) )
412,419c492
< tempDbMap = dbMap;
< }
<
< ColumnMap[] columnMaps = tempDbMap.getTable(
tables.get(i) ).getColumns();
< for (int j=0; j<columnMaps.length; j++)
< {
< ColumnMap colMap = columnMaps[j];
< if ( colMap.isPrimaryKey() )
---
> if ( criteria.getComparison(key).equals(Criteria.CUSTOM) )
421c494
< kd.addAttrib( colMap.getColumnName() );
---
> whereClause.add( criteria.getString(key) );
423,441c496,509
< String key = new
StringBuffer(colMap.getTableName())
<
.append('.').append(colMap.getColumnName()).toString();
< if ( criteria.containsKey(key) )
< {
< if (
criteria.getComparison(key).equals(Criteria.CUSTOM) )
< {
< whereClause.add( criteria.getString(key)
);
< }
< else
< {
< whereClause.add( SqlExpression.build(
colMap.getColumnName(),
< criteria.getValue(key),
< criteria.getComparison(key),
< criteria.isIgnoreCase(),
< db));
< }
< }
< }
< // execute the statement
---
> else
> {
> whereClause.add( SqlExpression.build( colMap.getColumnName(),
> criteria.getValue(key),
> criteria.getComparison(key),
> criteria.isIgnoreCase(),
> db));
> }
> }
> }
> // execute the statement
> TableDataSet tds = null;
> try
> {
443c511
< if (DEBUG) System.out.println("BasePeer.doDelete whereClause = " +
whereClause.toString(" AND ") );
---
> if (DEBUG) System.out.println("BasePeer.doDelete whereClause = " +
>whereClause.toString(" AND ") );
456a525,562
> finally
> {
> if (tds != null) tds.close();
> }
> }
> }
>
> /**
> * 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.
> * <br>
> * If the primary key is included in Criteria then that value will
> * be used to insert the row.
> * <br>
> * 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.
> * <br>
> * 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 int id of the row that was inserted if the table
> * has a primary key or -1 if the table does not have
> * a primary key.
> */
> public static int doInsert(Criteria criteria) throws Exception
> {
> DBConnection db = null;
> int id = -1;
> try
> {
> // get a connection to the db
> db = DBBroker.getInstance().getConnection( criteria.getDbName() );
>
> id = doInsert(criteria, db);
460,461c566
< if (tds != null) tds.close();
< DBBroker.getInstance().releaseConnection(dbCon);
---
> DBBroker.getInstance().releaseConnection(db);
462a568
> return id;
463a570
>
486c593
< public static int doInsert(Criteria criteria) throws Exception
---
> public static int doInsert(Criteria criteria, DBConnection dbCon) throws
>Exception
491c598
< return autoIntInsert(criteria);
---
> return autoIntInsert(criteria, dbCon);
510c617
< voidInsert(criteria);
---
> voidInsert(criteria, dbCon);
513a621
>
520c628
< private static void voidInsert(Criteria criteria) throws Exception
---
> private static void voidInsert(Criteria criteria, DBConnection dbCon) throws
>Exception
521a630,631
> Connection connection = dbCon.getConnection();
>
531d640
< DBConnection db = null;
534,538c643
< {
< // get a connection to the db
< db = DBBroker.getInstance().getConnection(
criteria.getDbName() );
< Connection connection = db.getConnection();
<
---
> {
551d655
< DBBroker.getInstance().releaseConnection(db);
555d658
<
565a669,696
> DBConnection db = null;
> Object ids = null;
> try
> {
> // get a connection to the db
> db = DBBroker.getInstance().getConnection( criteria.getDbName() );
>
> ids = objectIdInsert(criteria, db);
> }
> finally
> {
> DBBroker.getInstance().releaseConnection(db);
> }
> return ids;
> }
>
> /**
> * Very Generic method to perform inserts based on values and keys in a Criteria.
> * Returns ids as an Hashtable. Keys are the table names involved and values are
> * Value arrays containing the column values specified in the IDSql statement.
> * Currently called by autoIntInsert, but left public in the event some more
> * complicated scheme is implemented by a specific application.
> * This method probably still needs some debugging.
> */
> public static Object objectIdInsert(Criteria criteria, DBConnection dbCon)
>throws Exception
> {
> Connection connection = dbCon.getConnection();
>
581,588d711
<
< DBConnection db = null;
< TableDataSet tds = null;
< try
< {
< // get a connection to the db
< db = DBBroker.getInstance().getConnection(
criteria.getDbName() );
< Connection connection = db.getConnection();
590c713,717
< for (int i=0; i<tables.size(); i++)
---
> for (int i=0; i<tables.size(); i++)
> {
> // insert a new record
> TableDataSet tds = null;
> try
592,593d718
< // insert a new record
< if (tds != null) tds.close();
615,620c740,744
< }
< }
< finally
< {
< if (tds != null) tds.close();
< DBBroker.getInstance().releaseConnection(db);
---
> }
> finally
> {
> if (tds != null) tds.close();
> }
631c755
< private static int autoIntInsert(Criteria criteria) throws
Exception
---
> private static int autoIntInsert(Criteria criteria, DBConnection dbCon) throws
>Exception
633c757
< Object idObject = objectIdInsert(criteria);
---
> Object idObject = objectIdInsert(criteria, dbCon);
682,685d805
< else if(obj == null)
< {
< rec.setValueNull(colMap.getColumnName());
< }
778c898,899
< ignorCase, db ));
---
> ignorCase,
> db));
788c909
< // Check for each String/Character column and apply
ignoreCaseInOrderBy
---
> // Check for each String/Character column and apply toUpperCase()
797c918
< orderByClause.add(
db.ignoreCaseInOrderBy(orderByColumn) );
---
> orderByClause.add( db.toUpperCase(orderByColumn) );
805c926
< if (DEBUG) System.out.println("BasePeer.querySql= "+
querySql.toString());
---
> if (DEBUG) System.out.println("BasePeer.querySql= "+ querySql.toString());
820a942,952
> * Old method for performing a SELECT. Returns all results.
> *
> * @return Vector of Record objects.
> */
> public static Vector doSelect(Criteria criteria, DBConnection dbCon) throws
>Exception
> {
> return executeQuery( createQueryString(criteria), criteria.getDbName(),
>criteria.isSingleRecord(), dbCon );
> }
>
>
> /**
860,862c992
< QueryDataSet qds = null;
< Vector results = new Vector();
<
---
> Vector results = null;
867c997,1023
< Connection connection = db.getConnection();
---
> // execute the query
> results = executeQuery(queryString, dbName, singleRecord, db);
> }
> finally
> {
> DBBroker.getInstance().releaseConnection(db);
> }
> return results;
> }
>
> /**
> * Method for performing a SELECT. Returns all results.
> *
> * @param String the sql SELECT statment.
> * @param String the database to connect to.
> * @param boolean whether or not we want to select only a single record.
> * @return Vector of Record objects.
> */
> public static Vector executeQuery(String queryString, String dbName,
> boolean singleRecord, DBConnection dbCon) throws Exception
> {
> Connection connection = dbCon.getConnection();
>
> QueryDataSet qds = null;
> Vector results = new Vector();
> try
> {
875d1030
< DBBroker.getInstance().releaseConnection(db);
878a1034
>
1011a1168,1199
> DBConnection db = null;
> try
> {
> // get a connection to the db
> db = DBBroker.getInstance().getConnection( updateValues.getDbName() );
>
> doUpdate(updateValues, db);
> }
> finally
> {
> DBBroker.getInstance().releaseConnection(db);
> }
> }
>
>
> /**
> * Use this method for performing an update
> * "WHERE primary_key_id = an int"
> * <p>
> * 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>
> * To perform an update with non-primary key fields in the WHERE
> * clause use doUpdate(criteria, criteria).
> *
> * @param Criteria object containing values used in set clause.
> */
> public static void doUpdate(Criteria updateValues, DBConnection dbCon)
> throws Exception
> {
1027c1215
< doUpdate( selectCriteria, updateValues );
---
> doUpdate( selectCriteria, updateValues, dbCon );
1044,1045c1232,1261
< DBConnection dbCon = null;
< TableDataSet tds = null;
---
> DBConnection db = null;
> try
> {
> // get a connection to the db
> db = DBBroker.getInstance().getConnection( selectCriteria.getDbName() );
>
> doUpdate(selectCriteria, updateValues, db);
> }
> finally
> {
> DBBroker.getInstance().releaseConnection(db);
> }
> }
>
>
> /**
> * Use this method for performing an update
> * "WHERE some_column = some value<br>
> * AND could_have_another_column = another value"<br>
> * and so on.
> * <p>
> * Method used to update rows in the DB. Rows are selected based on
> * selectCriteria and updated using values in updateValues.
> *
> * @param Criteria object containing values used in where clause.
> * @param Criteria object containing values used in set clause.
> */
> public static void doUpdate(Criteria selectCriteria, Criteria updateValues,
> DBConnection dbCon) throws Exception
> {
1047a1264
> Connection connection = dbCon.getConnection();
1058c1275
< try
---
> for (int i=0; i<tables.size(); i++)
1060,1064c1277,1280
< // get a connection to the db
< dbCon = DBBroker.getInstance().getConnection(
selectCriteria.getDbName() );
< Connection connection = dbCon.getConnection();
<
< for (int i=0; i<tables.size(); i++)
---
> KeyDef kd = new KeyDef();
> StringStackBuffer whereClause = new StringStackBuffer();
> DatabaseMap tempDbMap = null;
> try
1066,1076c1282,1295
< KeyDef kd = new KeyDef();
< StringStackBuffer whereClause = new
StringStackBuffer();
< DatabaseMap tempDbMap = null;
< try
< {
< // attempt to find the right DatabaseMap for the
table that we
< // are currently working on. This allows for
distributed DatabaseMap's
< // across several files.
< tempDbMap = DBBroker.getInstance().getDatabaseMap(
tables.get(i) );
< }
< catch (Exception f)
---
> // attempt to find the right DatabaseMap for the table that we
> // are currently working on. This allows for distributed
>DatabaseMap's
> // across several files.
> tempDbMap = DBBroker.getInstance().getDatabaseMap( tables.get(i) );
> }
> catch (Exception f)
> {
> tempDbMap = dbMap;
> }
> ColumnMap[] columnMaps = tempDbMap.getTable( tables.get(i)
>).getColumns();
> for (int j=0; j<columnMaps.length; j++)
> {
> ColumnMap colMap = columnMaps[j];
> if ( colMap.isPrimaryKey() )
1078c1297
< tempDbMap = dbMap;
---
> kd.addAttrib( colMap.getColumnName() );
1080,1081c1299,1301
< ColumnMap[] columnMaps = tempDbMap.getTable(
tables.get(i) ).getColumns();
< for (int j=0; j<columnMaps.length; j++)
---
> String key = new StringBuffer(colMap.getTableName())
> .append('.').append(colMap.getColumnName()).toString();
> if ( selectCriteria.containsKey(key) )
1083,1084c1303
< ColumnMap colMap = columnMaps[j];
< if ( colMap.isPrimaryKey() )
---
> if ( selectCriteria.getComparison( key ).equals(Criteria.CUSTOM)
>)
1086c1305
< kd.addAttrib( colMap.getColumnName() );
---
> whereClause.add( selectCriteria.getString( key ));
1088,1105c1307,1319
< String key = new
StringBuffer(colMap.getTableName())
<
.append('.').append(colMap.getColumnName()).toString();
< if ( selectCriteria.containsKey(key) )
< {
< if ( selectCriteria.getComparison( key
).equals(Criteria.CUSTOM) )
< {
< whereClause.add( selectCriteria.getString(
key ));
< }
< else
< {
< whereClause.add( SqlExpression.build(
colMap.getColumnName(),
< selectCriteria.getValue( key ),
< selectCriteria.getComparison( key ),
< selectCriteria.isIgnoreCase(),
< db));
< }
< }
< }
---
> else
> {
> whereClause.add( SqlExpression.build( colMap.getColumnName(),
> selectCriteria.getValue( key ),
> selectCriteria.getComparison( key ),
> selectCriteria.isIgnoreCase(),
> db ));
> }
> }
> }
> TableDataSet tds = null;
> try
> {
1107d1320
< if (tds != null) tds.close();
1110c1323
< if (DEBUG) System.out.println("Update (select) whereClause=" +
whereClause.toString(" AND ") );
---
> if (DEBUG) System.out.println("Update (select) whereClause=" +
>whereClause.toString(" AND ") );
1123,1127c1336,1339
< }
< finally
< {
< if (tds != null) tds.close();
< DBBroker.getInstance().releaseConnection(dbCon);
---
> finally
> {
> if (tds != null) tds.close();
> }
1153c1365,1393
< DBConnection dbCon = null;
---
> DBConnection db = null;
> int rowCount = -1;
> try
> {
> // get a connection to the db
> db = DBBroker.getInstance().getConnection( dbName );
>
> rowCount = executeStatement( stmt, dbName, db );
> }
> finally
> {
> DBBroker.getInstance().releaseConnection(db);
> }
> 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 String sql statement to execute.
> * @param String database to connect to.
> */
> public static int executeStatement(String stmt, String dbName, DBConnection
>dbCon)
> throws Exception
> {
> Connection con = dbCon.getConnection();
1159,1162d1398
< // get a connection to the db
< dbCon = DBBroker.getInstance().getConnection( dbName );
< Connection con = dbCon.getConnection();
<
1169d1404
< DBBroker.getInstance().releaseConnection(dbCon);
1172a1408
>
Index: src/java/org/apache/turbine/om/user/peer/TurbineUserPeer.java
===================================================================
RCS file:
/products/cvs/turbine/turbine/src/java/org/apache/turbine/om/user/peer/TurbineUserPeer.java,v
retrieving revision 1.16
diff -r1.16 TurbineUserPeer.java
93c93
< private static final String VISITOR_ID_COLUMN =
mapBuilder.getVisitor_VisitorId();
---
> protected static final String VISITOR_ID_COLUMN =
>mapBuilder.getVisitor_VisitorId();
95c95
< private static final String OBJECT_DATA_COLUMN = "OBJECTDATA";
---
> protected static final String OBJECT_DATA_COLUMN = "OBJECTDATA";
116c116
< private static final String TABLE_NAME =
mapBuilder.getTableVisitor();
---
> protected static final String TABLE_NAME = mapBuilder.getTableVisitor();
199c199
< return doSelect(criteria,null);
---
> return doSelect(criteria,(User)null);
241a242
>
380c381
< }
\ No newline at end of file
---
> }
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
