[ticket:277] check if pg/oracle sequence exists. checks in all cases before CREATE SEQUENCE/ DROP SEQUENCE

5 files changed, 32 insertions, 7 deletions

diff --git a/CHANGES b/CHANGES
index b8320e7b2..4459c3188 100644
--- a/CHANGES
+++ b/CHANGES
@@ -3,6 +3,7 @@
 - add_property() method on mapper does a "compile all mappers"
 step in case the given property references a non-compiled mapper
 (as it did in the case of the tutorial !)
+- [ticket:277] check for pg sequence already existing before create
 
 0.2.7
 - quoting facilities set up so that database-specific quoting can be
diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py
index 6ca81c662..e6b27afa4 100644
--- a/lib/sqlalchemy/databases/oracle.py
+++ b/lib/sqlalchemy/databases/oracle.py
@@ -180,6 +180,10 @@ class OracleDialect(ansisql.ANSIDialect):
     def has_table(self, connection, table_name):
         cursor = connection.execute("""select table_name from all_tables where table_name=:name""", {'name':table_name.upper()})
         return bool( cursor.fetchone() is not None )
+
+    def has_sequence(self, connection, sequence_name):
+        cursor = connection.execute("""select sequence_name from all_sequences where sequence_name=:name""", {'name':sequence_name.upper()})
+        return bool( cursor.fetchone() is not None )
         
     def reflecttable(self, connection, table):
         c = connection.execute ("select distinct OWNER from ALL_TAB_COLUMNS where TABLE_NAME = :table_name", {'table_name':table.name.upper()})
@@ -370,13 +374,15 @@ class OracleSchemaGenerator(ansisql.ANSISchemaGenerator):
         return colspec
 
     def visit_sequence(self, sequence):
-        self.append("CREATE SEQUENCE %s" % sequence.name)
-        self.execute()
+        if not self.engine.dialect.has_sequence(self.connection, sequence.name):
+            self.append("CREATE SEQUENCE %s" % sequence.name)
+            self.execute()
 
 class OracleSchemaDropper(ansisql.ANSISchemaDropper):
     def visit_sequence(self, sequence):
-        self.append("DROP SEQUENCE %s" % sequence.name)
-        self.execute()
+        if self.engine.dialect.has_sequence(self.connection, sequence.name):
+            self.append("DROP SEQUENCE %s" % sequence.name)
+            self.execute()
 
 class OracleDefaultRunner(ansisql.ANSIDefaultRunner):
     def exec_default_sql(self, default):
diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py
index 4efe0e162..9adc42d02 100644
--- a/lib/sqlalchemy/databases/postgres.py
+++ b/lib/sqlalchemy/databases/postgres.py
@@ -279,9 +279,14 @@ class PGDialect(ansisql.ANSIDialect):
         return self.module
 
     def has_table(self, connection, table_name):
+        # TODO: why are we case folding here ?
         cursor = connection.execute("""select relname from pg_class where lower(relname) = %(name)s""", {'name':table_name.lower()})
         return bool( not not cursor.rowcount )
 
+    def has_sequence(self, connection, sequence_name):
+        cursor = connection.execute('''SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%%' AND nspname != 'information_schema' AND relname = %(seqname)s);''', {'seqname': sequence_name})
+        return bool(not not cursor.rowcount)
+        
     def reflecttable(self, connection, table):
         if self.version == 2:
             ischema_names = pg2_ischema_names
@@ -347,13 +352,13 @@ class PGSchemaGenerator(ansisql.ANSISchemaGenerator):
         return colspec
 
     def visit_sequence(self, sequence):
-        if not sequence.optional:
+        if not sequence.optional and not self.engine.dialect.has_sequence(self.connection, sequence.name):
             self.append("CREATE SEQUENCE %s" % sequence.name)
             self.execute()
             
 class PGSchemaDropper(ansisql.ANSISchemaDropper):
     def visit_sequence(self, sequence):
-        if not sequence.optional:
+        if not sequence.optional and self.engine.dialect.has_sequence(self.connection, sequence.name):
             self.append("DROP SEQUENCE %s" % sequence.name)
             self.execute()
 
diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index cbf154e62..6a96fbcfb 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -85,6 +85,8 @@ class Dialect(sql.AbstractDialect):
         raise NotImplementedError()
     def has_table(self, connection, table_name):
         raise NotImplementedError()
+    def has_sequence(self, connection, sequence_name):
+        raise NotImplementedError()
     def dbapi(self):
         """subclasses override this method to provide the DBAPI module used to establish
         connections."""
diff --git a/test/engine/reflection.py b/test/engine/reflection.py
index 411e594b5..da6943d5d 100644
--- a/test/engine/reflection.py
+++ b/test/engine/reflection.py
@@ -117,7 +117,18 @@ class ReflectionTest(PersistTest):
             t2.create()
         finally:
             table.drop(checkfirst=True)
-        
+            
+    @testbase.supported('postgres')
+    def testredundantsequence(self):
+        meta1 = BoundMetaData(testbase.db)
+        t = Table('mytable', meta1, 
+            Column('col1', Integer, Sequence('fooseq')))
+        try:
+            testbase.db.execute("CREATE SEQUENCE fooseq")
+            t.create()
+        finally:
+            t.drop()
+            
     def testmultipk(self):
         table = Table(
             'engine_multi', testbase.db,