Return-Path: Delivered-To: apmail-incubator-empire-db-commits-archive@minotaur.apache.org Received: (qmail 32360 invoked from network); 8 Feb 2009 11:41:25 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 8 Feb 2009 11:41:25 -0000 Received: (qmail 46347 invoked by uid 500); 8 Feb 2009 11:41:25 -0000 Delivered-To: apmail-incubator-empire-db-commits-archive@incubator.apache.org Received: (qmail 46324 invoked by uid 500); 8 Feb 2009 11:41:24 -0000 Mailing-List: contact empire-db-commits-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: empire-db-dev@incubator.apache.org Delivered-To: mailing list empire-db-commits@incubator.apache.org Received: (qmail 46313 invoked by uid 99); 8 Feb 2009 11:41:24 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 08 Feb 2009 03:41:24 -0800 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 08 Feb 2009 11:41:15 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 9BEDF238895D; Sun, 8 Feb 2009 11:40:53 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r742064 - in /incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db: DBColumn.java DBDatabase.java DBDatabaseDriver.java DBRowSet.java DBSQLScript.java mysql/DBDatabaseDriverMySQL.java sqlserver/DBDatabaseDriverMSSQL.java Date: Sun, 08 Feb 2009 11:40:53 -0000 To: empire-db-commits@incubator.apache.org From: doebele@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20090208114053.9BEDF238895D@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: doebele Date: Sun Feb 8 11:40:52 2009 New Revision: 742064 URL: http://svn.apache.org/viewvc?rev=742064&view=rev Log: EMPIREDB-34 Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBColumn.java incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabase.java incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabaseDriver.java incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBRowSet.java incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBSQLScript.java incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBColumn.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBColumn.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBColumn.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBColumn.java Sun Feb 8 11:40:52 2009 @@ -45,6 +45,8 @@ // Predefined column attributes public static final String DBCOLATTR_MANDATORY = "mandatory"; public static final String DBCOLATTR_READONLY = "readonly"; + public static final String DBCOLATTR_MINVALUE = "minValue"; + public static final String DBCOLATTR_MAXVALUE = "maxValue"; // basic data protected final DBRowSet rowset; Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabase.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabase.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabase.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabase.java Sun Feb 8 11:40:52 2009 @@ -948,7 +948,7 @@ * @param conn a valid connection to the database. * @return the row count for insert, update or delete or 0 for SQL statements that return nothing */ - public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn) + public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn, DBDatabaseDriver.DBSetGenKeys setGenKeys) { try { @@ -960,7 +960,7 @@ log.info("Executing: " + sqlCmd); // execute SQL long start = System.currentTimeMillis(); - int affected = driver.executeSQL(sqlCmd, sqlParams, conn); + int affected = driver.executeSQL(sqlCmd, sqlParams, conn, setGenKeys); // Log if (log.isInfoEnabled()) log.info("executeSQL affected " + String.valueOf(affected) + " Records / " + (System.currentTimeMillis() - start) + "ms"); @@ -980,6 +980,11 @@ } } + public final int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn) + { + return executeSQL(sqlCmd, sqlParams, conn, null); + } + /** * Executes an update, insert or delete SQL-Statement.
* We recommend to use a DBCommand object in order to build the sqlCmd.
Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabaseDriver.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabaseDriver.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabaseDriver.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBDatabaseDriver.java Sun Feb 8 11:40:52 2009 @@ -115,8 +115,18 @@ "select", "udpate", "insert", "alter", "delete" }; protected final Set reservedSQLKeywords; + /** + * This interface is used to set the auto generated keys when executing insert statements. + */ + public interface DBSetGenKeys + { + void set(Object value); + } - // DBSeqTable + /** + * This class is used to emulate sequences by using a sequence table. + * It is used with the executeSQL function and only required for insert statements + */ public static class DBSeqTable extends DBTable { public DBColumn C_SEQNAME; @@ -319,20 +329,6 @@ public abstract Object getNextSequenceValue(DBDatabase db, String SeqName, int minValue, Connection conn); /** - * Returns the value of an AutoIncrement field for the last inserted record.
- * This function is only used for databases that do not support sequences.
- * Hence getNextSequenceValue() must return 'null' - * @param db the database - * @param conn a valid database connection - * @return the id of the last inserted record null if this feature is not supported - */ - public Object getPostInsertAutoIncValue(DBDatabase db, Connection conn) - { - error(Errors.NotSupported); - return null; - } - - /** * Prepares an sql statement by setting the supplied objects as parameters. * * @param pstmt the prepared statement @@ -414,26 +410,41 @@ * @param sqlCmd the SQL-Command * @param sqlParams array of sql command parameters used for prepared statements (Optional). * @param conn a valid connection to the database. + * @param seqValue allows to set the auto generated key of a record (INSERT statements only) * @return the row count for insert, update or delete or 0 for SQL statements that return nothing */ - public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn) + public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn, DBSetGenKeys genKeys) throws SQLException { // Execute the Statement Statement stmt = null; try { + int count = 0; + int retGenKeys = (genKeys!=null) ? Statement.RETURN_GENERATED_KEYS + : Statement.NO_GENERATED_KEYS; if (sqlParams!=null) - { - PreparedStatement pstmt = conn.prepareStatement(sqlCmd); - stmt = pstmt; + { // Use a prepared statement + PreparedStatement pstmt = conn.prepareStatement(sqlCmd, retGenKeys); prepareStatement(pstmt, sqlParams); - return pstmt.executeUpdate(); + count = pstmt.executeUpdate(); } else { // Execute a simple statement stmt = conn.createStatement(); - return stmt.executeUpdate(sqlCmd); + count = stmt.executeUpdate(sqlCmd, retGenKeys); + } + // Retrieve any auto-generated keys + if (genKeys!=null) + { // Return Keys + ResultSet rs = stmt.getGeneratedKeys(); + try { + while(rs.next()) + genKeys.set(rs.getObject(1)); + } finally { + rs.close(); + } } + return count; } finally { // Close close(stmt); Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBRowSet.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBRowSet.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBRowSet.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBRowSet.java Sun Feb 8 11:40:52 2009 @@ -47,6 +47,25 @@ */ public abstract class DBRowSet extends DBExpr { + /** + * This class is used to set the auto generated key of a record if the database does not support sequences. + * It is used with the executeSQL function and only required for insert statements + */ + private static class DBSetGenKey implements DBDatabaseDriver.DBSetGenKeys + { + private Object[] fields; + private int index; + public DBSetGenKey(Object[] fields, int index) + { + this.fields = fields; + this.index = index; + } + public void set(Object value) + { + fields[index]=value; + } + } + // Logger @SuppressWarnings("hiding") protected static Log log = LogFactory.getLog(DBRowSet.class); @@ -559,13 +578,13 @@ // Get the new Timestamp String name = getName(); Timestamp timestamp = (timestampColumn!=null) ? db.getUpdateTimestamp(conn) : null; + DBDatabaseDriver.DBSetGenKeys setGenKey = null; // Get the fields and the flags Object[] fields = rec.getFields(); // Build SQL-Statement DBCommand cmd = db.createCommand(); String sql = null; int setCount = 0; - int autoIncFieldIndex = -1; // for post insert auto increment processing switch (rec.getState()) { case DBRecord.REC_MODIFIED: @@ -622,7 +641,7 @@ { // Set Autoinc value if not already done if (db.getDriver().isSupported(DBDriverFeature.SEQUENCES)==false) { // Post Dectect Autoinc Value - autoIncFieldIndex = i; + setGenKey = new DBSetGenKey(fields, i); continue; } // Get Next Sequence value @@ -663,7 +682,7 @@ return success(); } // Perform action - int affected = db.executeSQL(sql, cmd.getCmdParams(), conn); + int affected = db.executeSQL(sql, cmd.getCmdParams(), conn, setGenKey); if (affected < 0) { // Update Failed return error(db); @@ -676,13 +695,6 @@ { // Multiple Records affected return error(DBErrors.RecordUpdateInvalid, name); } - // Post Insert Autoincrement processing - if (autoIncFieldIndex>=0 && fields[autoIncFieldIndex]==null) - { // Driver must supply the value - fields[autoIncFieldIndex]=db.getDriver().getPostInsertAutoIncValue(db, conn); - if (fields[autoIncFieldIndex]==null) - return error(db.getDriver()); - } // Correct Timestamp if (timestampColumn != null) { // Set the correct Timestamp Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBSQLScript.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBSQLScript.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBSQLScript.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/DBSQLScript.java Sun Feb 8 11:40:52 2009 @@ -110,7 +110,7 @@ try { // Execute Statement log.debug("Executing: " + stmt); - driver.executeSQL(stmt, null, conn); + driver.executeSQL(stmt, null, conn, null); } catch(SQLException e) { // SQLException log.error(e.toString(), e); Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java Sun Feb 8 11:40:52 2009 @@ -148,7 +148,7 @@ try { // Set Database if (StringUtils.isValid(databaseName)) - executeSQL("USE " + databaseName, null, conn); + executeSQL("USE " + databaseName, null, conn, null); // Sequence Table if (useSequenceTable && db.getTable(sequenceTableName)==null) new DBSeqTable(sequenceTableName, db); @@ -292,7 +292,7 @@ */ @Override public Object getNextSequenceValue(DBDatabase db, String seqName, int minValue, Connection conn) - { //Use Oracle Sequences + { if (useSequenceTable) { // Use a sequence Table to generate Sequences DBTable t = db.getTable(sequenceTableName); @@ -303,12 +303,6 @@ return null; } } - - @Override - public Object getPostInsertAutoIncValue(DBDatabase db, Connection conn) - { - return db.querySingleInt("SELECT LAST_INSERT_ID()", conn); - } /** * @see DBDatabaseDriver#getDDLScript(DBCmdType, DBObject, DBSQLScript) Modified: incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java?rev=742064&r1=742063&r2=742064&view=diff ============================================================================== --- incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java (original) +++ incubator/empire-db/trunk/core/Empire-db/src/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java Sun Feb 8 11:40:52 2009 @@ -24,6 +24,7 @@ import java.util.Iterator; import org.apache.empire.commons.Errors; +import org.apache.empire.commons.ObjectUtils; import org.apache.empire.commons.StringUtils; import org.apache.empire.data.DataType; import org.apache.empire.db.DBCmdType; @@ -66,6 +67,9 @@ private String databaseName = null; private String objectOwner = "dbo"; private String sequenceTableName = "Sequences"; + // Sequence treatment + // When set to 'false' (default) MySQL's autoincrement feature is used. + private boolean useSequenceTable = false; /** * Constructor for the MSSQL database driver.
@@ -96,6 +100,26 @@ } /** + * returns whether a sequence table is used for record identiy management.
+ * Default is false. In this case the AutoIncrement feature of MySQL is used. + * @return true if a sequence table is used instead of identity columns. + */ + public boolean isUseSequenceTable() + { + return useSequenceTable; + } + + /** + * If set to true a special table is used for sequence number generation.
+ * Otherwise the AutoIncrement feature of MySQL is used identiy fields. + * @param useSequenceTable true to use a sequence table or false otherwise. + */ + public void setUseSequenceTable(boolean useSequenceTable) + { + this.useSequenceTable = useSequenceTable; + } + + /** * returns the name of the sequence table * @return the name of the table used for sequence number generation */ @@ -125,11 +149,11 @@ try { // Set Database if (StringUtils.isValid(databaseName)) - executeSQL("USE " + databaseName, null, conn); + executeSQL("USE " + databaseName, null, conn, null); // Set Dateformat - executeSQL("SET DATEFORMAT ymd", null, conn); + executeSQL("SET DATEFORMAT ymd", null, conn, null); // Sequence Table - if (db.getTable(sequenceTableName)==null) + if (useSequenceTable && db.getTable(sequenceTableName)==null) new DBSeqTable(sequenceTableName, db); // Check Schema String schema = db.getSchema(); @@ -171,7 +195,7 @@ switch (type) { // return support info case CREATE_SCHEMA: return true; - case SEQUENCES: return true; + case SEQUENCES: return useSequenceTable; } return false; } @@ -282,9 +306,16 @@ */ @Override public Object getNextSequenceValue(DBDatabase db, String seqName, int minValue, Connection conn) - { //Use Oracle Sequences - DBTable t = db.getTable(sequenceTableName); - return ((DBSeqTable)t).getNextValue(seqName, minValue, conn); + { + if (useSequenceTable) + { // Use a sequence Table to generate Sequences + DBTable t = db.getTable(sequenceTableName); + return ((DBSeqTable)t).getNextValue(seqName, minValue, conn); + } + else + { // Post Detection + return null; + } } /** @@ -511,6 +542,14 @@ break; case AUTOINC: sql.append("[int]"); + if (useSequenceTable==false) + { // Make this column the identity column + int minValue = ObjectUtils.getInteger(c.getAttribute(DBColumn.DBCOLATTR_MINVALUE), 1); + sql.append(" IDENTITY("); + sql.append(String.valueOf(minValue)); + sql.append(", 1) NOT NULL"); + return true; + } break; case TEXT: { // Check fixed or variable length