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

5 files changed, 82 insertions, 7 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,)
         )
diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index 881ea9fcd..58abc10df 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -534,13 +534,21 @@ def _append_param_insert_pk(compiler, stmt, c, values, kw):
     no value passed in either; raise an exception.
 
     """
+
     if (
         # column has a Python-side default
         c.default is not None
         and (
-            # and it won't be a Sequence
+            # and it either is not a sequence, or it is and we support
+            # sequences and want to invoke it
             not c.default.is_sequence
-            or compiler.dialect.supports_sequences
+            or (
+                compiler.dialect.supports_sequences
+                and (
+                    not c.default.optional
+                    or not compiler.dialect.sequences_optional
+                )
+            )
         )
     ) or (
         # column is the "autoincrement column"
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 8c325538c..c7d699a5f 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2281,6 +2281,8 @@ class Sequence(roles.StatementRole, DefaultGenerator):
 
     .. seealso::
 
+        :ref:`defaults_sequences`
+
         :class:`.CreateSequence`
 
         :class:`.DropSequence`
diff --git a/lib/sqlalchemy/testing/fixtures.py b/lib/sqlalchemy/testing/fixtures.py
index e2237fb17..bc7a6120c 100644
--- a/lib/sqlalchemy/testing/fixtures.py
+++ b/lib/sqlalchemy/testing/fixtures.py
@@ -120,16 +120,11 @@ class TablesTest(TestBase):
 
     def _setup_each_tables(self):
         if self.run_define_tables == "each":
-            self.tables.clear()
-            if self.run_create_tables == "each":
-                drop_all_tables(self.metadata, self.bind)
-            self.metadata.clear()
             self.define_tables(self.metadata)
             if self.run_create_tables == "each":
                 self.metadata.create_all(self.bind)
             self.tables.update(self.metadata.tables)
         elif self.run_create_tables == "each":
-            drop_all_tables(self.metadata, self.bind)
             self.metadata.create_all(self.bind)
 
     def _setup_each_inserts(self):
@@ -138,6 +133,14 @@ class TablesTest(TestBase):
             self.insert_data()
 
     def _teardown_each_tables(self):
+        if self.run_define_tables == "each":
+            self.tables.clear()
+            if self.run_create_tables == "each":
+                drop_all_tables(self.metadata, self.bind)
+            self.metadata.clear()
+        elif self.run_create_tables == "each":
+            drop_all_tables(self.metadata, self.bind)
+
         # no need to run deletes if tables are recreated on setup
         if self.run_define_tables != "each" and self.run_deletes == "each":
             with self.bind.connect() as conn:
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 5b26ac72e..8bbb7ec45 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -431,6 +431,13 @@ class SuiteRequirements(Requirements):
         )
 
     @property
+    def no_sequences(self):
+        """the oppopsite of "sequences", DB does not support sequences at
+        all."""
+
+        return exclusions.NotPredicate(self.sequences)
+
+    @property
     def sequences_optional(self):
         """Target database supports sequences, but also optionally
         as a means of generating new PK values."""
@@ -444,6 +451,24 @@ class SuiteRequirements(Requirements):
         )
 
     @property
+    def supports_lastrowid(self):
+        """target database / driver supports cursor.lastrowid as a means
+        of retrieving the last inserted primary key value.
+
+        note that if the target DB supports sequences also, this is still
+        assumed to work.  This is a new use case brought on by MariaDB 10.3.
+
+        """
+        return exclusions.only_if(
+            [lambda config: config.db.dialect.postfetch_lastrowid]
+        )
+
+    @property
+    def no_lastrowid_support(self):
+        """the opposite of supports_lastrowid"""
+        return exclusions.NotPredicate(self.supports_lastrowid)
+
+    @property
     def reflects_pk_names(self):
         return exclusions.closed()