Author: tfischer
Date: Fri Dec 27 22:40:44 2013
New Revision: 1553756

URL: http://svn.apache.org/r1553756
Log:
TORQUE-305 fix insert into ... select statements when primary key is not given 
and sequences are used to generate primary keys

Modified:
    
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
    
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
    
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java

Modified: 
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
URL: 
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java?rev=1553756&r1=1553755&r2=1553756&view=diff
==============================================================================
--- 
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
 (original)
+++ 
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
 Fri Dec 27 22:40:44 2013
@@ -73,7 +73,7 @@ public abstract class AbstractIdGenerato
      *
      * @throws TorqueException if a database error occurs.
      */
-    public int getIdAsInt(Connection connection, Object keyInfo)
+    public int getIdAsInt(final Connection connection, final Object keyInfo)
         throws TorqueException
     {
         return getId(connection, keyInfo, new IntegerMapper());
@@ -89,7 +89,7 @@ public abstract class AbstractIdGenerato
      *
      * @throws TorqueException if a database error occurs.
      */
-    public long getIdAsLong(Connection connection, Object keyInfo)
+    public long getIdAsLong(final Connection connection, final Object keyInfo)
         throws TorqueException
     {
         return getId(connection, keyInfo, new LongMapper());
@@ -105,7 +105,7 @@ public abstract class AbstractIdGenerato
      *
      * @throws TorqueException if a database error occurs.
      */
-    public BigDecimal getIdAsBigDecimal(Connection connection, Object keyInfo)
+    public BigDecimal getIdAsBigDecimal(final Connection connection, final 
Object keyInfo)
         throws TorqueException
     {
         return getId(connection, keyInfo, new BigDecimalMapper());
@@ -122,7 +122,7 @@ public abstract class AbstractIdGenerato
      *
      * @throws TorqueException if a database error occurs.
      */
-    public String getIdAsString(Connection connection, Object keyInfo)
+    public String getIdAsString(final Connection connection, final Object 
keyInfo)
         throws TorqueException
     {
         return getId(connection, keyInfo, new StringMapper());
@@ -159,21 +159,36 @@ public abstract class AbstractIdGenerato
      *        appropriate java object.
      *
      * @return The generated id.
-     * @exception TorqueException if a database error occurs.
+     * @throws TorqueException if a database error occurs.
      */
     protected <T> T getId(
-                Connection connection,
-                Object keyInfo,
-                RecordMapper<T> mapper)
+                final Connection connection,
+                final Object keyInfo,
+                final RecordMapper<T> mapper)
             throws TorqueException
     {
-        String tableName = SqlBuilder.getFullTableName(
-                String.valueOf(keyInfo),
-                databaseName);
-        String idSql = adapter.getIDMethodSQL(tableName);
+        String idSql = getIdSql(keyInfo);
 
         BasePeerImpl<T> peer = new BasePeerImpl<T>(mapper, null, databaseName);
         List<T> result = peer.doSelect(idSql, connection);
         return result.get(0);
     }
+
+    /**
+     * Returns the SQL to retrieve the next id.
+     *
+     * @param keyInfo an Object that contains additional info.
+     *
+     * @return the SQL to retrieve the next id.
+     *
+     * @throws TorqueException
+     */
+    public String getIdSql(final Object keyInfo) throws TorqueException
+    {
+        String tableName = SqlBuilder.getFullTableName(
+                String.valueOf(keyInfo),
+                databaseName);
+        String idSql = adapter.getIDMethodSQL(tableName);
+        return idSql;
+    }
 }

Modified: 
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
URL: 
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java?rev=1553756&r1=1553755&r2=1553756&view=diff
==============================================================================
--- 
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
 (original)
+++ 
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
 Fri Dec 27 22:40:44 2013
@@ -35,6 +35,7 @@ import org.apache.commons.lang.StringUti
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.apache.torque.Column;
+import org.apache.torque.ColumnImpl;
 import org.apache.torque.Database;
 import org.apache.torque.TooManyRowsException;
 import org.apache.torque.Torque;
@@ -48,6 +49,7 @@ import org.apache.torque.map.ColumnMap;
 import org.apache.torque.map.MapHelper;
 import org.apache.torque.map.TableMap;
 import org.apache.torque.oid.IdGenerator;
+import org.apache.torque.oid.SequenceIdGenerator;
 import org.apache.torque.om.NumberKey;
 import org.apache.torque.om.ObjectKey;
 import org.apache.torque.om.SimpleKey;
@@ -636,7 +638,8 @@ public class BasePeerImpl<T> implements 
     /**
      * Executes a insert into...select statement.
      *
-     * @param toInsertInto the columns in which to insert, not null.
+     * @param toInsertInto the columns in which to insert, not null,
+     *        must not contain null.
      * @param criteria the criteria which selects the values to insert,
      *        not null.
      * @param dbName the database name, or null to take the database name
@@ -654,15 +657,74 @@ public class BasePeerImpl<T> implements 
                 final Connection connection)
             throws TorqueException
     {
-       if (dbName == null)
+        if (dbName == null)
         {
             dbName = getDatabaseName();
         }
 
+        ColumnMap pk = getTableMap().getPrimaryKey();
+        boolean pkExistsInColumnMap = false;
+
         List<String> columnNames = new ArrayList<String>();
         for (Column column : toInsertInto)
         {
             columnNames.add(column.getColumnName());
+            if (pk != null
+                    && column.getSqlExpression().equals(pk.getSqlExpression()))
+            {
+                pkExistsInColumnMap = true;
+            }
+        }
+        if (!pkExistsInColumnMap)
+        {
+            IDMethod idMethod = getTableMap().getPrimaryKeyMethod();
+            Adapter adapter = Torque.getAdapter(dbName);
+            if (idMethod == IDMethod.ID_BROKER)
+            {
+                log.debug("pk does not exist in column map and id method is "
+                        + idMethod
+                        + ", SQL will fail if column has no default value");
+                // try SQL, maybe database has a default value set
+            }
+            else if (idMethod == IDMethod.SEQUENCE
+                    || (idMethod == IDMethod.NATIVE
+                        && adapter.getIDMethodType() == IDMethod.SEQUENCE))
+            {
+                IdGenerator keyGen = Torque.getDatabase(dbName).getIdGenerator(
+                        getTableMap().getPrimaryKeyMethod());
+                if (keyGen instanceof SequenceIdGenerator)
+                {
+                    SequenceIdGenerator sequenceIdGenerator
+                            = (SequenceIdGenerator) keyGen;
+                    String idSql = sequenceIdGenerator.getIdSql(
+                            getIdMethodInfo());
+                    // This is a bit of a hack.
+                    // The idSql is usually a stand-alone statement, but we
+                    // need the part to be inserted as a column.
+                    // Therefore we extract the complete word containing
+                    // the term nextval
+                    int nextvalPos = idSql.toLowerCase().indexOf("nextval");
+                    int spacePos = idSql.lastIndexOf(" ", nextvalPos);
+                    if (spacePos != -1)
+                    {
+                        idSql = idSql.substring(spacePos + 1);
+                    }
+                    spacePos = idSql.indexOf(" ");
+                    if (spacePos != -1)
+                    {
+                        idSql = idSql.substring(0, idSql.indexOf(" "));
+                    }
+                    columnNames.add(pk.getColumnName());
+                    criteria.addSelectColumn(
+                            new ColumnImpl(null, null, null, idSql));
+                }
+                else
+                {
+                    log.warn("id method is sequence "
+                            + "but keyGen is no Sequence id generator, "
+                            + "cannot add sequence generation info");
+                }
+            }
         }
 
         String fullTableName = SqlBuilder.getFullTableName(

Modified: 
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
URL: 
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java?rev=1553756&r1=1553755&r2=1553756&view=diff
==============================================================================
--- 
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
 (original)
+++ 
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
 Fri Dec 27 22:40:44 2013
@@ -23,13 +23,19 @@ import java.math.BigDecimal;
 import java.sql.Types;
 import java.util.List;
 
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
 import org.apache.torque.BaseDatabaseTestCase;
+import org.apache.torque.Column;
 import org.apache.torque.ColumnImpl;
+import org.apache.torque.adapter.IDMethod;
 import org.apache.torque.criteria.Criteria;
 import org.apache.torque.om.ObjectKey;
 import org.apache.torque.test.dbobject.Author;
+import org.apache.torque.test.dbobject.Book;
 import org.apache.torque.test.dbobject.IntegerType;
 import org.apache.torque.test.peer.AuthorPeer;
+import org.apache.torque.test.peer.BookPeer;
 import org.apache.torque.test.peer.IntegerTypePeer;
 import org.apache.torque.util.ColumnValues;
 import org.apache.torque.util.JdbcTypedValue;
@@ -43,6 +49,8 @@ public class DoInsertTest extends BaseDa
 {
     private List<Author> authorList;
 
+    private static Log log =LogFactory.getLog(DoInsertTest.class);
+
     @Override
     public void setUp() throws Exception
     {
@@ -131,4 +139,120 @@ public class DoInsertTest extends BaseDa
                 ((BigDecimal) objectKey.getValue()).intValue(),
                 allIntegerTypes.get(0).getId());
     }
+
+    /**
+     * Tests the doInsert method where the inserted values are selected
+     * from the table.
+     *
+     * @throws Exception if a database error occurs.
+     */
+    public void testDoInsertWithSelectSingleRecordIdSet()
+            throws Exception
+    {
+        Criteria selectCriteria = new Criteria()
+            .where(AuthorPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+            .addSelectColumn(new ColumnImpl(
+                    AuthorPeer.AUTHOR_ID.getSchemaName(),
+                    AuthorPeer.AUTHOR_ID.getTableName(),
+                    AuthorPeer.AUTHOR_ID.getColumnName(),
+                    AuthorPeer.AUTHOR_ID.getSqlExpression() + " + 100"))
+            .addSelectColumn(AuthorPeer.NAME);
+
+        // execute
+        int numberOfInsertedRows = AuthorPeer.doInsert(
+                new Column[] {AuthorPeer.AUTHOR_ID, AuthorPeer.NAME},
+                selectCriteria);
+
+        // verify
+        assertEquals(1, numberOfInsertedRows);
+        Author author = new Author();
+        author.setAuthorId(authorList.get(0).getAuthorId() + 100);
+        author.setName(authorList.get(0).getName());
+        authorList.add(author);
+        verifyBookstore(authorList);
+    }
+
+    /**
+     * Tests the doInsert method where the inserted values are selected
+     * from the table.
+     *
+     * @throws Exception if a database error occurs.
+     */
+    public void testDoInsertWithSelectSingleRecord()
+            throws Exception
+    {
+        if ( IDMethod.ID_BROKER == 
AuthorPeer.getTableMap().getPrimaryKeyMethod())
+        {
+            log.warn("Cannot test insert.. select statement with autoincrement"
+                     + " id method");
+            return;
+        }
+        Criteria selectCriteria = new Criteria()
+            .where(AuthorPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+            .addSelectColumn(AuthorPeer.NAME);
+
+        // execute
+        int numberOfInsertedRows = AuthorPeer.doInsert(
+                new Column[] {AuthorPeer.NAME},
+                selectCriteria);
+
+        // verify
+        assertEquals(1, numberOfInsertedRows);
+        Author author = new Author();
+        author.setAuthorId(authorList.get(9).getAuthorId() + 1);
+        author.setName(authorList.get(0).getName());
+        authorList.add(author);
+        verifyBookstore(authorList);
+    }
+
+    /**
+     * Tests the doInsert method where the inserted values are selected
+     * from the table.
+     *
+     * @throws Exception if a database error occurs.
+     */
+    public void testDoInsertWithSelectMultipleRecords()
+            throws Exception
+    {
+        if ( IDMethod.ID_BROKER == 
AuthorPeer.getTableMap().getPrimaryKeyMethod())
+        {
+            log.warn("Cannot test insert.. select statement with autoincrement"
+                     + " id method");
+            return;
+        }
+        Criteria selectCriteria = new Criteria()
+            .where(BookPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+            .addSelectColumn(BookPeer.TITLE);
+
+        // execute
+        int numberOfInsertedRows = AuthorPeer.doInsert(
+                new Column[] {AuthorPeer.NAME},
+                selectCriteria);
+
+        // verify
+        assertEquals(10, numberOfInsertedRows);
+        List<Author> selectedAuthorList = AuthorPeer.doSelect(new Criteria());
+        // check size of List
+        assertEquals(20, selectedAuthorList.size());
+        for (Author author : selectedAuthorList)
+        {
+            if (author.getAuthorId() > authorList.get(9).getAuthorId())
+            {
+                boolean found = false;
+                for (Book book : authorList.get(0).getBooks())
+                {
+                    if (author.getName().equals(book.getTitle()))
+                    {
+                        found = true;
+                        authorList.get(0).getBooks().remove(book);
+                        break;
+                    }
+                }
+                if (!found)
+                {
+                    fail("unexpected author with name " + author.getName());
+                }
+            }
+        }
+    }
 }



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscr...@db.apache.org
For additional commands, e-mail: torque-dev-h...@db.apache.org

Reply via email to