Add sequence support for MariaDB 10.3+.

Added support for use of the :class:`.Sequence` construct with MariaDB 10.3
and greater, as this is now supported by this database.  The construct
integrates with the :class:`.Table` object in the same way that it does for
other databases like PostrgreSQL and Oracle; if is present on the integer
primary key "autoincrement" column, it is used to generate defaults.   For
backwards compatibility, to support a :class:`.Table` that has a
:class:`.Sequence` on it to support sequence only databases like Oracle,
while still not having the sequence fire off for MariaDB, the optional=True
flag should be set, which indicates the sequence should only be used to
generate the primary key if the target database offers no other option.

Fixes: #4976
Closes: #4996
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4996
Pull-request-sha: cb2e1426ea0b6bc6c93dbe8f033a11df9d8c4915

Change-Id: I507bc405eee6cae2c5991345d0eac53a37fe7512

1 files changed, 37 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index fb123bc0f..81e80fdbc 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1192,6 +1192,15 @@ class MySQLExecutionContext(default.DefaultExecutionContext):
         else:
             raise NotImplementedError()
 
+    def fire_sequence(self, seq, type_):
+        return self._execute_scalar(
+            (
+                "select nextval(%s)"
+                % self.dialect.identifier_preparer.format_sequence(seq)
+            ),
+            type_,
+        )
+
 
 class MySQLCompiler(compiler.SQLCompiler):
 
@@ -1204,6 +1213,9 @@ class MySQLCompiler(compiler.SQLCompiler):
     def visit_random_func(self, fn, **kw):
         return "rand%s" % self.function_argspec(fn)
 
+    def visit_sequence(self, seq, **kw):
+        return "nextval(%s)" % self.preparer.format_sequence(seq)
+
     def visit_sysdate_func(self, fn, **kw):
         return "SYSDATE()"
 
@@ -2146,6 +2158,11 @@ class MySQLDialect(default.DefaultDialect):
 
     supports_native_enum = True
 
+    supports_sequences = False  # default for MySQL ...
+    # ... may be updated to True for MariaDB 10.3+ in initialize()
+
+    sequences_optional = True
+
     supports_sane_rowcount = True
     supports_sane_multi_rowcount = False
     supports_multivalues_insert = True
@@ -2421,6 +2438,22 @@ class MySQLDialect(default.DefaultDialect):
             if rs:
                 rs.close()
 
+    def has_sequence(self, connection, sequence_name, schema=None):
+        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_NAME=:name AND "
+                "TABLE_SCHEMA=:schema_name"
+            ),
+            name=sequence_name,
+            schema_name=schema,
+        )
+        return cursor.first() is not None
+
     def initialize(self, connection):
         self._connection_charset = self._detect_charset(connection)
         self._detect_sql_mode(connection)
@@ -2435,6 +2468,10 @@ class MySQLDialect(default.DefaultDialect):
 
         default.DefaultDialect.initialize(self, connection)
 
+        self.supports_sequences = (
+            self._is_mariadb and self.server_version_info >= (10, 3)
+        )
+
         self._needs_correct_for_88718_96365 = (
             not self._is_mariadb and self.server_version_info >= (8,)
         )