diff options
| -rw-r--r-- | doc/build/changelog/unreleased_14/8903.rst | 7 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 12 | ||||
| -rw-r--r-- | test/dialect/test_sqlite.py | 59 |
3 files changed, 77 insertions, 1 deletions
diff --git a/doc/build/changelog/unreleased_14/8903.rst b/doc/build/changelog/unreleased_14/8903.rst new file mode 100644 index 000000000..fe1590c04 --- /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 11554fcc0..fb9a19c89 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 d460ef64e..c5147e37f 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -1879,6 +1879,20 @@ class ConstraintReflectionTest(fixtures.TestBase): ) 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" "CONSTRAINT cq CHECK (q == 1 OR (q > 2 AND q < 5))\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( |