Added reflection method :meth:`.Inspector.get_sequence_names`

Added new reflection method :meth:`.Inspector.get_sequence_names` which
returns all the sequences defined. Support for this method has been added
to the backend that support :class:`.Sequence`: PostgreSql, Oracle,
MSSQL and MariaDB >= 10.3.

Fixes: #2056
Change-Id: I0949696a39aa28c849edf2504779241f7443778a

4 files changed, 89 insertions, 33 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index c35ab2880..5aaecf23a 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -2640,6 +2640,19 @@ class MSDialect(default.DefaultDialect):
         return c.first() is not None
 
     @reflection.cache
+    @_db_plus_owner_listing
+    def get_sequence_names(self, connection, dbname, owner, schema, **kw):
+        sequences = ischema.sequences
+
+        s = sql.select([sequences.c.sequence_name])
+        if owner:
+            s = s.where(sequences.c.sequence_schema == owner)
+
+        c = connection.execute(s)
+
+        return [row[0] for row in c]
+
+    @reflection.cache
     def get_schema_names(self, connection, **kw):
         s = sql.select(
             [ischema.schemata.c.schema_name],
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index d009d656e..b34422e65 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2517,6 +2517,8 @@ class MySQLDialect(default.DefaultDialect):
                 rs.close()
 
     def has_sequence(self, connection, sequence_name, schema=None):
+        if not self.supports_sequences:
+            self._sequences_not_supported()
         if not schema:
             schema = self.default_schema_name
         # MariaDB implements sequences as a special type of table
@@ -2524,13 +2526,40 @@ class MySQLDialect(default.DefaultDialect):
         cursor = connection.execute(
             sql.text(
                 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
-                "WHERE TABLE_NAME=:name AND "
+                "WHERE TABLE_TYPE='SEQUENCE' and TABLE_NAME=:name AND "
                 "TABLE_SCHEMA=:schema_name"
             ),
             dict(name=sequence_name, schema_name=schema),
         )
         return cursor.first() is not None
 
+    def _sequences_not_supported(self):
+        raise NotImplementedError(
+            "Sequences are supported only by the "
+            "MariaDB series 10.3 or greater"
+        )
+
+    @reflection.cache
+    def get_sequence_names(self, connection, schema=None, **kw):
+        if not self.supports_sequences:
+            self._sequences_not_supported()
+        if not schema:
+            schema = self.default_schema_name
+        # MariaDB implements sequences as a special type of table
+        cursor = connection.execute(
+            sql.text(
+                "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
+                "WHERE TABLE_TYPE='SEQUENCE' and TABLE_SCHEMA=:schema_name"
+            ),
+            dict(schema_name=schema),
+        )
+        return [
+            row[0]
+            for row in self._compat_fetchall(
+                cursor, charset=self._connection_charset
+            )
+        ]
+
     def initialize(self, connection):
         self._connection_charset = self._detect_charset(connection)
         self._detect_sql_mode(connection)
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 481ea7263..5e912a0c2 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1635,6 +1635,19 @@ class OracleDialect(default.DefaultDialect):
         return [self.normalize_name(row[0]) for row in cursor]
 
     @reflection.cache
+    def get_sequence_names(self, connection, schema=None, **kw):
+        if not schema:
+            schema = self.default_schema_name
+        cursor = connection.execute(
+            sql.text(
+                "SELECT sequence_name FROM all_sequences "
+                "WHERE sequence_owner = :schema_name"
+            ),
+            schema_name=self.denormalize_name(schema),
+        )
+        return [self.normalize_name(row[0]) for row in cursor]
+
+    @reflection.cache
     def get_table_options(self, connection, table_name, schema=None, **kw):
         options = {}
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 441e77a37..2bfb9b494 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2682,39 +2682,23 @@ class PGDialect(default.DefaultDialect):
 
     def has_sequence(self, connection, sequence_name, schema=None):
         if schema is None:
-            cursor = connection.execute(
-                sql.text(
-                    "SELECT relname FROM pg_class c join pg_namespace n on "
-                    "n.oid=c.relnamespace where relkind='S' and "
-                    "n.nspname=current_schema() "
-                    "and relname=:name"
-                ).bindparams(
-                    sql.bindparam(
-                        "name",
-                        util.text_type(sequence_name),
-                        type_=sqltypes.Unicode,
-                    )
-                )
-            )
-        else:
-            cursor = connection.execute(
-                sql.text(
-                    "SELECT relname FROM pg_class c join pg_namespace n on "
-                    "n.oid=c.relnamespace where relkind='S' and "
-                    "n.nspname=:schema and relname=:name"
-                ).bindparams(
-                    sql.bindparam(
-                        "name",
-                        util.text_type(sequence_name),
-                        type_=sqltypes.Unicode,
-                    ),
-                    sql.bindparam(
-                        "schema",
-                        util.text_type(schema),
-                        type_=sqltypes.Unicode,
-                    ),
-                )
+            schema = self.default_schema_name
+        cursor = connection.execute(
+            sql.text(
+                "SELECT relname FROM pg_class c join pg_namespace n on "
+                "n.oid=c.relnamespace where relkind='S' and "
+                "n.nspname=:schema and relname=:name"
+            ).bindparams(
+                sql.bindparam(
+                    "name",
+                    util.text_type(sequence_name),
+                    type_=sqltypes.Unicode,
+                ),
+                sql.bindparam(
+                    "schema", util.text_type(schema), type_=sqltypes.Unicode,
+                ),
             )
+        )
 
         return bool(cursor.first())
 
@@ -2871,6 +2855,23 @@ class PGDialect(default.DefaultDialect):
         return [name for name, in result]
 
     @reflection.cache
+    def get_sequence_names(self, connection, schema=None, **kw):
+        if not schema:
+            schema = self.default_schema_name
+        cursor = connection.execute(
+            sql.text(
+                "SELECT relname FROM pg_class c join pg_namespace n on "
+                "n.oid=c.relnamespace where relkind='S' and "
+                "n.nspname=:schema"
+            ).bindparams(
+                sql.bindparam(
+                    "schema", util.text_type(schema), type_=sqltypes.Unicode,
+                ),
+            )
+        )
+        return [row[0] for row in cursor]
+
+    @reflection.cache
     def get_view_definition(self, connection, view_name, schema=None, **kw):
         view_def = connection.scalar(
             sql.text(