- sequences on a non-pk column will properly fire off on INSERT for PG/oracle

4 files changed, 32 insertions, 8 deletions

diff --git a/CHANGES b/CHANGES
index f4338c681..bfd310a07 100644
--- a/CHANGES
+++ b/CHANGES
@@ -31,11 +31,13 @@
   table PK if not applicable, i.e. for a UNION.  checking for DISTINCT, GROUP BY
   (other places that rowid is invalid) still a TODO.  allows polymorphic mappings
   to function, [ticket:436]
+  - sequences on a non-pk column will properly fire off on INSERT
 - mysql:
   - fix to reflection on older DB's that might return array() type for 
   "show variables like" statements
 - postgres:
   - better reflection of sequences for alternate-schema Tables [ticket:442]
+  - sequences on a non-pk column will properly fire off on INSERT
 
 0.3.4
 - general:
diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py
index b5bd72e8f..8730ecdb1 100644
--- a/lib/sqlalchemy/databases/oracle.py
+++ b/lib/sqlalchemy/databases/oracle.py
@@ -349,6 +349,11 @@ class OracleCompiler(ansisql.ANSICompiler):
             self._outertable = None
 
         self.visit_compound(self.wheres[join])
+
+    def visit_insert_sequence(self, column, sequence, parameters):
+        """this is the 'sequence' equivalent to ANSICompiler's 'visit_insert_column_default' which ensures
+        that the column is present in the generated column list"""
+        parameters.setdefault(column.key, None)
        
     def visit_alias(self, alias):
 	"""oracle doesnt like 'FROM table AS alias'.  is the AS standard SQL??"""
diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py
index 3fea2e1a5..74b23d8f9 100644
--- a/lib/sqlalchemy/databases/postgres.py
+++ b/lib/sqlalchemy/databases/postgres.py
@@ -456,13 +456,15 @@ class PGDialect(ansisql.ANSIDialect):
 class PGCompiler(ansisql.ANSICompiler):
         
     def visit_insert_column(self, column, parameters):
-        # Postgres advises against OID usage and turns it off in 8.1,
-        # effectively making cursor.lastrowid
-        # useless, effectively making reliance upon SERIAL useless.  
-        # so all column primary key inserts must be explicitly present
+        # all column primary key inserts must be explicitly present
         if column.primary_key:
             parameters[column.key] = None
 
+    def visit_insert_sequence(self, column, sequence, parameters):
+        """this is the 'sequence' equivalent to ANSICompiler's 'visit_insert_column_default' which ensures
+        that the column is present in the generated column list"""
+        parameters.setdefault(column.key, None)
+
     def limit_clause(self, select):
         text = ""
         if select.limit is not None:
diff --git a/test/sql/defaults.py b/test/sql/defaults.py
index d5aac19fd..9d426ab0d 100644
--- a/test/sql/defaults.py
+++ b/test/sql/defaults.py
@@ -161,15 +161,30 @@ class AutoIncrementTest(PersistTest):
 class SequenceTest(PersistTest):
     @testbase.supported('postgres', 'oracle')
     def setUpAll(self):
-        global cartitems
-        cartitems = Table("cartitems", db, 
+        global cartitems, sometable, metadata
+        metadata = BoundMetaData(testbase.db)
+        cartitems = Table("cartitems", metadata, 
             Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),
             Column("description", String(40)),
             Column("createdate", DateTime())
         )
+        sometable = Table( 'Manager', metadata,
+               Column( 'obj_id', Integer, Sequence('obj_id_seq'), ),
+               Column( 'name', type= String, ),
+               Column( 'id', Integer, primary_key= True, ),
+           )
         
-        cartitems.create()
+        metadata.create_all()
     
+    def testseqnonpk(self):
+        """test sequences fire off as defaults on non-pk columns"""
+        sometable.insert().execute(name="somename")
+        sometable.insert().execute(name="someother")
+        assert sometable.select().execute().fetchall() == [
+            (1, "somename", 1),
+            (2, "someother", 2),
+        ]
+        
     @testbase.supported('postgres', 'oracle')
     def testsequence(self):
         cartitems.insert().execute(description='hi')
@@ -196,7 +211,7 @@ class SequenceTest(PersistTest):
         
     @testbase.supported('postgres', 'oracle')
     def tearDownAll(self): 
-        cartitems.drop()
+        metadata.drop_all()
 
 if __name__ == "__main__":
     testbase.main()