From f0bd40faf85ae3101a3306e72ccacd713362c2a6 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sat, 13 Nov 2021 10:27:30 -0500
Subject: [PATCH] add type synonym for mysql.LONGTEXT / JSON

Added a rule to the MySQL impl so that the translation between JSON /
LONGTEXT is accommodated by autogenerate, treating LONGTEXT from the server
as equivalent to an existing JSON in the model.

Change-Id: Ia8370d2269c4decea00aa94beafd3d9d861a1269
Fixes: #968
---
 alembic/ddl/mysql.py          |  5 +++-
 docs/build/unreleased/968.rst |  7 +++++
 tests/requirements.py         | 49 +++++++++++++++++++++++++++++++++++
 tests/test_autogen_diffs.py   |  7 +++--
 4 files changed, 65 insertions(+), 3 deletions(-)
 create mode 100644 docs/build/unreleased/968.rst

diff --git a/alembic/ddl/mysql.py b/alembic/ddl/mysql.py
index 94895605..c3d66bdf 100644
--- a/alembic/ddl/mysql.py
+++ b/alembic/ddl/mysql.py
@@ -38,7 +38,10 @@ class MySQLImpl(DefaultImpl):
     __dialect__ = "mysql"
 
     transactional_ddl = False
-    type_synonyms = DefaultImpl.type_synonyms + ({"BOOL", "TINYINT"},)
+    type_synonyms = DefaultImpl.type_synonyms + (
+        {"BOOL", "TINYINT"},
+        {"JSON", "LONGTEXT"},
+    )
     type_arg_extract = [r"character set ([\w\-_]+)", r"collate ([\w\-_]+)"]
 
     def alter_column(  # type:ignore[override]
diff --git a/docs/build/unreleased/968.rst b/docs/build/unreleased/968.rst
new file mode 100644
index 00000000..be5440b1
--- /dev/null
+++ b/docs/build/unreleased/968.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, mysql, autogenerate
+    :tickets: 968
+
+    Added a rule to the MySQL impl so that the translation between JSON /
+    LONGTEXT is accommodated by autogenerate, treating LONGTEXT from the server
+    as equivalent to an existing JSON in the model.
diff --git a/tests/requirements.py b/tests/requirements.py
index 7770da90..258c7300 100644
--- a/tests/requirements.py
+++ b/tests/requirements.py
@@ -1,3 +1,4 @@
+from sqlalchemy import exc as sqla_exc
 from sqlalchemy import text
 
 from alembic.testing import exclusions
@@ -300,6 +301,54 @@ class DefaultRequirements(SuiteRequirements):
         else:
             return False
 
+    @property
+    def json_type(self):
+        return exclusions.only_on(
+            [
+                lambda config: exclusions.against(config, "mysql")
+                and (
+                    (
+                        not config.db.dialect._is_mariadb
+                        and exclusions.against(config, "mysql >= 5.7")
+                    )
+                    or (
+                        config.db.dialect._mariadb_normalized_version_info
+                        >= (10, 2, 7)
+                    )
+                ),
+                "mariadb>=10.2.7",
+                "postgresql >= 9.3",
+                self._sqlite_json,
+                self._mssql_json,
+            ]
+        )
+
+    def _mssql_json(self, config):
+        if not sqla_compat.sqla_14:
+            return False
+        else:
+            return exclusions.against(config, "mssql")
+
+    def _sqlite_json(self, config):
+        if not sqla_compat.sqla_14:
+            return False
+        elif not exclusions.against(config, "sqlite >= 3.9"):
+            return False
+        else:
+            with config.db.connect() as conn:
+                try:
+                    return (
+                        conn.execute(
+                            text(
+                                """select json_extract('{"foo": "bar"}', """
+                                """'$."foo"')"""
+                            )
+                        ).scalar()
+                        == "bar"
+                    )
+                except sqla_exc.DBAPIError:
+                    return False
+
     @property
     def identity_columns(self):
         # TODO: in theory if these could come from SQLAlchemy dialects
diff --git a/tests/test_autogen_diffs.py b/tests/test_autogen_diffs.py
index f790be54..8657aebe 100644
--- a/tests/test_autogen_diffs.py
+++ b/tests/test_autogen_diffs.py
@@ -15,6 +15,7 @@ from sqlalchemy import Index
 from sqlalchemy import inspect
 from sqlalchemy import INTEGER
 from sqlalchemy import Integer
+from sqlalchemy import JSON
 from sqlalchemy import LargeBinary
 from sqlalchemy import MetaData
 from sqlalchemy import Numeric
@@ -927,6 +928,8 @@ class CompareMetadataToInspectorTest(TestBase):
         (String(32),),
         (LargeBinary(),),
         (Unicode(32),),
+        (JSON(), config.requirements.json_type),
+        (mysql.LONGTEXT(), config.requirements.mysql),
         (Enum("one", "two", "three", name="the_enum"),),
     )
     def test_introspected_columns_match_metadata_columns(self, cola):
@@ -965,7 +968,7 @@ class CompareMetadataToInspectorTest(TestBase):
             mysql.VARCHAR(200, charset="latin1"),
             mysql.VARCHAR(200, charset="utf-8"),
             True,
-            config.requirements.mysql + config.requirements.sqlalchemy_13,
+            config.requirements.mysql,
         ),
         (
             String(255, collation="utf8_bin"),
@@ -977,7 +980,7 @@ class CompareMetadataToInspectorTest(TestBase):
             String(255, collation="utf8_bin"),
             String(255, collation="latin1_bin"),
             True,
-            config.requirements.mysql + config.requirements.sqlalchemy_13,
+            config.requirements.mysql,
         ),
     )
     def test_string_comparisons(self, cola, colb, expect_changes):
-- 
2.47.3