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

2 files changed, 17 insertions, 6 deletions

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()