From 0e88bcc30ed49193b91f248123f526fa30007f22 Mon Sep 17 00:00:00 2001 From: Michal Petrucha Date: Mon, 11 Apr 2016 23:16:24 -0400 Subject: [PATCH] Reflect ON DELETE and ON UPDATE for SQLite foreign keys Co-authored-by: Mike Bayer Change-Id: Icd77ddbf851b1950f767022d67c8142b1b3c50f3 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/244 --- doc/build/changelog/changelog_11.rst | 8 ++++++ doc/build/changelog/migration_11.rst | 8 ++++++ lib/sqlalchemy/dialects/sqlite/base.py | 19 ++++++++++--- test/dialect/test_sqlite.py | 37 ++++++++++++++++++++++++++ 4 files changed, 68 insertions(+), 4 deletions(-) diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index b872f12ab1..40fa46b7c1 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -52,6 +52,14 @@ tables should be reflected from if not the default schema. Pull request courtesy Josh Marlow. + .. change:: + :tags: feature, sqlite + :pullreq: github:244 + + The SQLite dialect now reflects ON UPDATE and ON DELETE phrases + within foreign key constraints. Pull request courtesy + Michal Petrucha. + .. change:: :tags: bug, mssql :pullreq: bitbucket:58 diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index 4796817983..7ebaf84a0a 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -2274,6 +2274,14 @@ both within the method :meth:`.Inspector.get_check_constraints` as well as within :class:`.Table` reflection within the :attr:`.Table.constraints` collection. +ON DELETE and ON UPDATE foreign key phrases now reflect +------------------------------------------------------- + +The :class:`.Inspector` will now include ON DELETE and ON UPDATE +phrases from foreign key constraints on the SQLite dialect, and the +:class:`.ForeignKeyConstraint` object as reflected as part of a +:class:`.Table` will also indicate these phrases. + Dialect Improvements and Changes - SQL Server ============================================= diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 2fe10556be..85ebaaa1d0 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1374,14 +1374,17 @@ class SQLiteDialect(default.DefaultDialect): FK_PATTERN = ( '(?:CONSTRAINT (\w+) +)?' 'FOREIGN KEY *\( *(.+?) *\) +' - 'REFERENCES +(?:(?:"(.+?)")|([a-z0-9_]+)) *\((.+?)\)' + 'REFERENCES +(?:(?:"(.+?)")|([a-z0-9_]+)) *\((.+?)\) *' + '((?:ON (?:DELETE|UPDATE) ' + '(?:SET NULL|SET DEFAULT|CASCADE|RESTRICT|NO ACTION) *)*)' ) for match in re.finditer(FK_PATTERN, table_data, re.I): ( constraint_name, constrained_columns, referred_quoted_name, referred_name, - referred_columns) = match.group(1, 2, 3, 4, 5) + referred_columns, onupdatedelete) = \ + match.group(1, 2, 3, 4, 5, 6) constrained_columns = list( self._find_cols_in_sig(constrained_columns)) if not referred_columns: @@ -1390,14 +1393,20 @@ class SQLiteDialect(default.DefaultDialect): referred_columns = list( self._find_cols_in_sig(referred_columns)) referred_name = referred_quoted_name or referred_name + options = {} + for token in re.split(r" *ON *", onupdatedelete): + if token.startswith("DELETE"): + options['ondelete'] = token[6:].strip() + elif token.startswith("UPDATE"): + options["onupdate"] = token[6:].strip() yield ( constraint_name, constrained_columns, - referred_name, referred_columns) + referred_name, referred_columns, options) fkeys = [] for ( constraint_name, constrained_columns, - referred_name, referred_columns) in parse_fks(): + referred_name, referred_columns, options) in parse_fks(): sig = fk_sig( constrained_columns, referred_name, referred_columns) if sig not in keys_by_signature: @@ -1411,6 +1420,8 @@ class SQLiteDialect(default.DefaultDialect): continue key = keys_by_signature.pop(sig) key['name'] = constraint_name + if options: + key['options'] = options fkeys.append(key) # assume the remainders are the unnamed, inline constraints, just # use them as is as it's extremely difficult to parse inline diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index dde9da0864..16a6287774 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -1146,6 +1146,16 @@ class ConstraintReflectionTest(fixtures.TestBase): # will contain an "autoindex" conn.execute("create table o (foo varchar(20) primary key)") + conn.execute( + "CREATE TABLE onud_test (id INTEGER PRIMARY KEY, " + "c1 INTEGER, c2 INTEGER, c3 INTEGER, " + "CONSTRAINT fk1 FOREIGN KEY (c1) REFERENCES a1(id) " + "ON DELETE SET NULL, " + "CONSTRAINT fk2 FOREIGN KEY (c2) REFERENCES a1(id) " + "ON UPDATE CASCADE, " + "CONSTRAINT fk3 FOREIGN KEY (c3) REFERENCES a2(id) " + "ON DELETE CASCADE ON UPDATE SET NULL)" + ) conn.execute( "CREATE TABLE cp (" @@ -1283,6 +1293,33 @@ class ConstraintReflectionTest(fixtures.TestBase): 'constrained_columns': ['q', 'p']}] ) + def test_foreign_key_ondelete_onupdate(self): + inspector = Inspector(testing.db) + fks = inspector.get_foreign_keys('onud_test') + eq_( + fks, + [ + { + 'referred_table': 'a1', 'referred_columns': ['id'], + 'referred_schema': None, 'name': 'fk1', + 'constrained_columns': ['c1'], + 'options': {'ondelete': 'SET NULL'} + }, + { + 'referred_table': 'a1', 'referred_columns': ['id'], + 'referred_schema': None, 'name': 'fk2', + 'constrained_columns': ['c2'], + 'options': {'onupdate': 'CASCADE'} + }, + { + 'referred_table': 'a2', 'referred_columns': ['id'], + 'referred_schema': None, 'name': 'fk3', + 'constrained_columns': ['c3'], + 'options': {'ondelete': 'CASCADE', 'onupdate': 'SET NULL'} + }, + ] + ) + def test_dont_reflect_autoindex(self): inspector = Inspector(testing.db) eq_(inspector.get_indexes('o'), []) -- 2.47.2