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: [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
---
 test/dialect/test_sqlite.py | 59 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 59 insertions(+)

(limited to 'test')

diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index d460ef64e..c5147e37f 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(
-- 
cgit v1.2.1