https://github.com/python/cpython/commit/de777e490fb356d7bcc7c907141c20a5135d97df commit: de777e490fb356d7bcc7c907141c20a5135d97df branch: main author: Mariusz Felisiak <[email protected]> committer: erlend-aasland <[email protected]> date: 2024-01-12T10:50:37+01:00 summary:
gh-108364: In sqlite3, disable foreign keys before dumping SQL schema (#113957) sqlite3.Connection.iterdump now ensures that foreign key support is disabled before dumping the database schema, if there is any foreign key violation. Co-authored-by: Erlend E. Aasland <[email protected]> files: A Misc/NEWS.d/next/Library/2024-01-11-22-22-51.gh-issue-108364.QH7C-1.rst M Lib/sqlite3/dump.py M Lib/test/test_sqlite3/test_dump.py diff --git a/Lib/sqlite3/dump.py b/Lib/sqlite3/dump.py index ead3360ce67608..719dfc8947697d 100644 --- a/Lib/sqlite3/dump.py +++ b/Lib/sqlite3/dump.py @@ -26,6 +26,10 @@ def _iterdump(connection): writeable_schema = False cu = connection.cursor() + # Disable foreign key constraints, if there is any foreign key violation. + violations = cu.execute("PRAGMA foreign_key_check").fetchall() + if violations: + yield('PRAGMA foreign_keys=OFF;') yield('BEGIN TRANSACTION;') # sqlite_master table contains the SQL CREATE statements for the database. diff --git a/Lib/test/test_sqlite3/test_dump.py b/Lib/test/test_sqlite3/test_dump.py index 14a18c1ad37102..2e1f0b80c10f46 100644 --- a/Lib/test/test_sqlite3/test_dump.py +++ b/Lib/test/test_sqlite3/test_dump.py @@ -20,7 +20,8 @@ def test_table_dump(self): , "CREATE TABLE t1(id integer primary key, s1 text, " \ "t1_i1 integer not null, i2 integer, unique (s1), " \ - "constraint t1_idx1 unique (i2));" + "constraint t1_idx1 unique (i2), " \ + "constraint t1_i1_idx1 unique (t1_i1));" , "INSERT INTO \"t1\" VALUES(1,'foo',10,20);" , @@ -30,6 +31,9 @@ def test_table_dump(self): "t2_i2 integer, primary key (id)," \ "foreign key(t2_i1) references t1(t1_i1));" , + # Foreign key violation. + "INSERT INTO \"t2\" VALUES(1,2,3);" + , "CREATE TRIGGER trigger_1 update of t1_i1 on t1 " \ "begin " \ "update t2 set t2_i1 = new.t1_i1 where t2_i1 = old.t1_i1; " \ @@ -41,8 +45,12 @@ def test_table_dump(self): [self.cu.execute(s) for s in expected_sqls] i = self.cx.iterdump() actual_sqls = [s for s in i] - expected_sqls = ['BEGIN TRANSACTION;'] + expected_sqls + \ - ['COMMIT;'] + expected_sqls = [ + "PRAGMA foreign_keys=OFF;", + "BEGIN TRANSACTION;", + *expected_sqls, + "COMMIT;", + ] [self.assertEqual(expected_sqls[i], actual_sqls[i]) for i in range(len(expected_sqls))] diff --git a/Misc/NEWS.d/next/Library/2024-01-11-22-22-51.gh-issue-108364.QH7C-1.rst b/Misc/NEWS.d/next/Library/2024-01-11-22-22-51.gh-issue-108364.QH7C-1.rst new file mode 100644 index 00000000000000..943a74db18d053 --- /dev/null +++ b/Misc/NEWS.d/next/Library/2024-01-11-22-22-51.gh-issue-108364.QH7C-1.rst @@ -0,0 +1,3 @@ +:meth:`sqlite3.Connection.iterdump` now ensures that foreign key support is +disabled before dumping the database schema, if there is any foreign key +violation. Patch by Erlend E. Aasland and Mariusz Felisiak. _______________________________________________ Python-checkins mailing list -- [email protected] To unsubscribe send an email to [email protected] https://mail.python.org/mailman3/lists/python-checkins.python.org/ Member address: [email protected]
