FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

gh-108364: In sqlite3, disable foreign keys before dumping SQL schema by erlend-aasland · Pull Request #108471 · python/cpython · GitHub

  1. python
  2. cpython

Repository navigation

gh-108364: In sqlite3, disable foreign keys before dumping SQL schema #108471

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
erlend-aasland wants to merge 6 commits into from
Conversation 6 Commits 6 Checks 0 Files changed
Closed

gh-108364: In sqlite3, disable foreign keys before dumping SQL schema #108471

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
6 commits
Select commit Hold shift + click to select a range
9f43f34
gh-108364: In sqlite3, disable foreign keys before dumping SQL schema
erlend-aasland Aug 25, 2023
421e8a3
Merge branch 'main' into sqlite/iterdump/foreign-keys
erlend-aasland Aug 25, 2023
bc7dfda
Merge branch 'main' into sqlite/iterdump/foreign-keys
erlend-aasland Aug 25, 2023
d07fbf1
Merge branch 'main' into sqlite/iterdump/foreign-keys
erlend-aasland Aug 25, 2023
030d89f
Pull in main
erlend-aasland Aug 27, 2023
04a740d
Adapt test
erlend-aasland Aug 27, 2023
File filter

Filter by extension

Filter by extension .py  (2) .rst  (1) All 2 file types selected
Viewed files
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Unified
Split
Hide whitespace
Diff view
Unified
Split
Hide whitespace
1 change: 1 addition & 0 deletions Lib/sqlite3/dump.py
Show comments
View file
Open in desktop
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
Show hidden characters
Original file line number Diff line number Diff line change
Expand Up @@ -26,6 +26,7 @@ def _iterdump(connection):

writeable_schema = False
cu = connection.cursor()
yield('PRAGMA foreign_keys=OFF;')

CorvinM Aug 30, 2023

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

This is consistent with the sqlite CLI .dump but seems to contradict the documentation here (section 2). Perhaps we should query foreign_keys for future proofing?

Excerpt from section 2:
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)

felixxm Aug 31, 2023 •
edited
Loading

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

I don't think we can make assumptions about a connection on which the dump will be imported. We can check if there is any foreign key violation on the source database before adding this, e.g.

violations = cursor.execute("PRAGMA foreign_key_check").fetchall()
if len(violations) > 0:
    yield('PRAGMA foreign_keys=OFF;')

felixxm Sep 20, 2023 •
edited
Loading

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

@erlend-aasland Do you need help with this patch?

erlend-aasland Sep 28, 2023

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

@felixxm: I currently have very little bandwidth for CPython development. Feel free to take this over, if you want.

felixxm Jan 11, 2024

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

Updated in #113957.
yield('BEGIN TRANSACTION;')

# sqlite_master table contains the SQL CREATE statements for the database.
Expand Down
35 changes: 16 additions & 19 deletions Lib/test/test_sqlite3/test_dump.py
Show comments
View file
Open in desktop
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
Show hidden characters
Original file line number Diff line number Diff line change
Expand Up @@ -9,6 +9,15 @@

class DumpTests(MemoryDatabaseMixin, unittest.TestCase):

def checkDump(self, actual, expected):
expected = [
"PRAGMA foreign_keys=OFF;",
"BEGIN TRANSACTION;",
] + expected + [
"COMMIT;",
]
self.assertEqual(actual, expected)

def test_table_dump(self):
expected_sqls = [
"""CREATE TABLE "index"("index" blob);"""
Expand Down Expand Up @@ -42,10 +51,7 @@ 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;']
[self.assertEqual(expected_sqls[i], actual_sqls[i])
for i in range(len(expected_sqls))]
self.checkDump(actual_sqls, expected_sqls)

def test_dump_autoincrement(self):
expected = [
Expand All @@ -57,15 +63,13 @@ def test_dump_autoincrement(self):

# the NULL value should now be automatically be set to 1
expected[1] = expected[1].replace("NULL", "1")
expected.insert(0, "BEGIN TRANSACTION;")
expected.extend([
'DELETE FROM "sqlite_sequence";',
'INSERT INTO "sqlite_sequence" VALUES(\'t1\',1);',
'COMMIT;',
])

actual = [stmt for stmt in self.cx.iterdump()]
self.assertEqual(expected, actual)
self.checkDump(actual, expected)

def test_dump_autoincrement_create_new_db(self):
self.cu.execute("BEGIN TRANSACTION")
Expand Down Expand Up @@ -100,23 +104,17 @@ def __init__(self, cursor, row):
def __getitem__(self, index):
return self.row[index]
self.cx.row_factory = UnorderableRow
CREATE_ALPHA = """CREATE TABLE "alpha" ("one");"""
CREATE_BETA = """CREATE TABLE "beta" ("two");"""
expected = [
"BEGIN TRANSACTION;",
CREATE_ALPHA,
CREATE_BETA,
"COMMIT;"
]
CREATE_ALPHA = 'CREATE TABLE "alpha" ("one");'
CREATE_BETA = 'CREATE TABLE "beta" ("two");'
expected = [CREATE_ALPHA, CREATE_BETA]
self.cu.execute(CREATE_BETA)
self.cu.execute(CREATE_ALPHA)
got = list(self.cx.iterdump())
self.assertEqual(expected, got)
self.checkDump(got, expected)

def test_dump_virtual_tables(self):
# gh-64662
expected = [
"BEGIN TRANSACTION;",
"PRAGMA writable_schema=ON;",
("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
"VALUES('table','test','test',0,'CREATE VIRTUAL TABLE test USING fts4(example)');"),
Expand All @@ -127,11 +125,10 @@ def test_dump_virtual_tables(self):
"CREATE TABLE 'test_segments'(blockid INTEGER PRIMARY KEY, block BLOB);",
"CREATE TABLE 'test_stat'(id INTEGER PRIMARY KEY, value BLOB);",
"PRAGMA writable_schema=OFF;",
"COMMIT;"
]
self.cu.execute("CREATE VIRTUAL TABLE test USING fts4(example)")
actual = list(self.cx.iterdump())
self.assertEqual(expected, actual)
self.checkDump(actual, expected)


if __name__ == "__main__":
Expand Down
2 changes: 2 additions & 0 deletions Misc/NEWS.d/next/Library/2023-08-25-11-02-27.gh-issue-108364.l1KeRe.rst
Show comments
View file
Open in desktop
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
Show hidden characters
Original file line number Diff line number Diff line change
@@ -0,0 +1,2 @@
:meth:`sqlite3.Connection.iterdump` now ensures that foreign key support is
disabled before dumping the database schema. Patch by Erlend E. Aasland.
Back | FazBrowse Home | New Git URL