From 07760011b5176be03c7811e9a45933b473b8b80b Mon Sep 17 00:00:00 2001
From: Michael Gorven <michael@gorven.net>
Date: Tue, 29 Nov 2022 18:36:19 -0500
Subject: [PATCH] [sqlite] Reflect DEFERRABLE and INITIALLY options for foreign
 keys

Added support for the SQLite backend to reflect the "DEFERRABLE" and
"INITIALLY" keywords which may be present on a foreign key construct. Pull
request courtesy Michael Gorven.

Fixes: #8903
Closes: #8904
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/8904
Pull-request-sha: 52aa4cf77482c4051899e21bea75b9830e4c3efa

Change-Id: I713906db1a458d8f1be39625841ca3bbc03ec835
---
 doc/build/changelog/unreleased_14/8903.rst |  7 +++
 lib/sqlalchemy/dialects/sqlite/base.py     | 12 ++++-
 test/dialect/test_sqlite.py                | 59 ++++++++++++++++++++++
 3 files changed, 77 insertions(+), 1 deletion(-)
 create mode 100644 doc/build/changelog/unreleased_14/8903.rst

diff --git a/doc/build/changelog/unreleased_14/8903.rst b/doc/build/changelog/unreleased_14/8903.rst
new file mode 100644
index 0000000000..fe1590c043
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/8903.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: usecase, sqlite
+    :tickets: 8903
+
+    Added support for the SQLite backend to reflect the "DEFERRABLE" and
+    "INITIALLY" keywords which may be present on a foreign key construct. Pull
+    request courtesy Michael Gorven.
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 11554fcc0c..fb9a19c89b 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -2462,6 +2462,8 @@ class SQLiteDialect(default.DefaultDialect):
                 r'REFERENCES +(?:(?:"(.+?)")|([a-z0-9_]+)) *\((.+?)\) *'
                 r"((?:ON (?:DELETE|UPDATE) "
                 r"(?:SET NULL|SET DEFAULT|CASCADE|RESTRICT|NO ACTION) *)*)"
+                r"((?:NOT +)?DEFERRABLE)?"
+                r"(?: +INITIALLY +(DEFERRED|IMMEDIATE))?"
             )
             for match in re.finditer(FK_PATTERN, table_data, re.I):
                 (
@@ -2471,7 +2473,9 @@ class SQLiteDialect(default.DefaultDialect):
                     referred_name,
                     referred_columns,
                     onupdatedelete,
-                ) = match.group(1, 2, 3, 4, 5, 6)
+                    deferrable,
+                    initially,
+                ) = match.group(1, 2, 3, 4, 5, 6, 7, 8)
                 constrained_columns = list(
                     self._find_cols_in_sig(constrained_columns)
                 )
@@ -2493,6 +2497,12 @@ class SQLiteDialect(default.DefaultDialect):
                         onupdate = token[6:].strip()
                         if onupdate and onupdate != "NO ACTION":
                             options["onupdate"] = onupdate
+
+                if deferrable:
+                    options["deferrable"] = "NOT" not in deferrable.upper()
+                if initially:
+                    options["initially"] = initially.upper()
+
                 yield (
                     constraint_name,
                     constrained_columns,
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index d460ef64e0..c5147e37fb 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -1878,6 +1878,20 @@ class ConstraintReflectionTest(fixtures.TestBase):
                 "ON UPDATE NO ACTION)"
             )
 
+            conn.exec_driver_sql(
+                "CREATE TABLE deferrable_test (id INTEGER PRIMARY KEY, "
+                "c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER, "
+                "CONSTRAINT fk1 FOREIGN KEY (c1) REFERENCES a1(id) "
+                "DEFERRABLE,"
+                "CONSTRAINT fk2 FOREIGN KEY (c2) REFERENCES a1(id) "
+                "NOT DEFERRABLE,"
+                "CONSTRAINT fk3 FOREIGN KEY (c3) REFERENCES a2(id) "
+                "ON UPDATE CASCADE "
+                "DEFERRABLE INITIALLY DEFERRED,"
+                "CONSTRAINT fk4 FOREIGN KEY (c4) REFERENCES a2(id) "
+                "NOT DEFERRABLE INITIALLY IMMEDIATE)"
+            )
+
             conn.exec_driver_sql(
                 "CREATE TABLE cp ("
                 "q INTEGER check (q > 1 AND q < 6),\n"
@@ -2248,6 +2262,51 @@ class ConstraintReflectionTest(fixtures.TestBase):
             ],
         )
 
+    def test_foreign_key_deferrable_initially(self):
+        inspector = inspect(testing.db)
+        fks = inspector.get_foreign_keys("deferrable_test")
+        eq_(
+            fks,
+            [
+                {
+                    "referred_table": "a1",
+                    "referred_columns": ["id"],
+                    "referred_schema": None,
+                    "name": "fk1",
+                    "constrained_columns": ["c1"],
+                    "options": {"deferrable": True},
+                },
+                {
+                    "referred_table": "a1",
+                    "referred_columns": ["id"],
+                    "referred_schema": None,
+                    "name": "fk2",
+                    "constrained_columns": ["c2"],
+                    "options": {"deferrable": False},
+                },
+                {
+                    "referred_table": "a2",
+                    "referred_columns": ["id"],
+                    "referred_schema": None,
+                    "name": "fk3",
+                    "constrained_columns": ["c3"],
+                    "options": {
+                        "deferrable": True,
+                        "initially": "DEFERRED",
+                        "onupdate": "CASCADE",
+                    },
+                },
+                {
+                    "referred_table": "a2",
+                    "referred_columns": ["id"],
+                    "referred_schema": None,
+                    "name": "fk4",
+                    "constrained_columns": ["c4"],
+                    "options": {"deferrable": False, "initially": "IMMEDIATE"},
+                },
+            ],
+        )
+
     def test_foreign_key_options_unnamed_inline(self):
         with testing.db.begin() as conn:
             conn.exec_driver_sql(
-- 
2.47.2