Skip to content

gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement #9621

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

Merged
erlend-aasland merged 15 commits into from
Jun 19, 2022
Merged

gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement #9621

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
15 commits
Select commit Hold shift + click to select a range
9bf9eb7
bpo-34828 sqlite3.iterdump now correctly handles tables with autoincr…
itssme Sep 28, 2018
5d7b0ae
bpo-34828: added entry in NEWS.d/next/Library and in ACKS
itssme Sep 28, 2018
1ac20b0
Merge remote-tracking branch 'origin/master' into fix-issue-34828
itssme Jul 3, 2021
365a2f1
bpo-34828 Removed redundant code and made some small formatting chang…
itssme Jul 3, 2021
7c5901b
Merge branch 'main' into fix-issue-34828
itssme Oct 15, 2021
b259219
bpo-34828 formatting changes as well as readability improvements in s…
itssme Oct 15, 2021
3812bf6
Merge branch 'main' into fix-issue-34828
itssme May 22, 2022
daed6e5
bpo-34828: reformatted some tests in test_sqlite3/test.dump.py as wel…
itssme May 22, 2022
d5daa91
Merge branch 'main' into fix-issue-34828
erlend-aasland Jun 9, 2022
4e573e9
bpo-34828: minified test case for some sqlite3 dump command tests, fi…
itssme Jun 17, 2022
64f4101
Merge branch 'main' into fix-issue-34828
erlend-aasland Jun 19, 2022
1abc7d0
Clean up dump.py
erlend-aasland Jun 19, 2022
6bfac7e
Cleanup: don't use abbreviated link in NEWS entry
erlend-aasland Jun 19, 2022
103c50f
Cleanup: tests
erlend-aasland Jun 19, 2022
53076e9
Fix 1abc7d05b7a605224611a09eaa74e50ef1cc38e1
erlend-aasland Jun 19, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
Next Next commit
bpo-34828: reformatted some tests in test_sqlite3/test.dump.py as wel… 
…l as small formatting changes in sqlite3/dump.py
  • Loading branch information
@itssme
itssme committed May 22, 2022
commit daed6e50fc65ef27a64d1be6fc3440e1ec884bfb
8 changes: 4 additions & 4 deletions Lib/sqlite3/dump.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -30,10 +30,10 @@ def _iterdump(connection):
schema_res = cu.execute(q)
sqlite_sequence = []
for table_name, type, sql in schema_res.fetchall():
if table_name == 'sqlite_sequence':
sqlite_sequence = ['DELETE FROM "sqlite_sequence"']
rows = cu.execute('SELECT * FROM "sqlite_sequence";').fetchall()
sqlite_sequence.extend(['INSERT INTO "sqlite_sequence" VALUES(\'{0}\',{1})'
if table_name == "sqlite_sequence":
sqlite_sequence = ["DELETE FROM \"sqlite_sequence\""]
rows = cu.execute("SELECT * FROM \"sqlite_sequence\";").fetchall()
sqlite_sequence.extend(["INSERT INTO \"sqlite_sequence\" VALUES('{0}',{1})"
.format(row[0], row[1]) for row in rows])
continue
elif table_name == 'sqlite_stat1':
Expand Down
79 changes: 32 additions & 47 deletions Lib/test/test_sqlite3/test_dump.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,6 +2,8 @@

import unittest
import sqlite3 as sqlite
from .test_dbapi import memory_database


class DumpTests(unittest.TestCase):
def setUp(self):
Expand Down Expand Up @@ -51,17 +53,11 @@ def test_table_dump(self):

def test_dump_autoincrement(self):
expected_sqls = [
"""CREATE TABLE "posts" (id int primary key);""",
"""INSERT INTO "posts" VALUES(0);""",
"""CREATE TABLE "tags" ( """
"""id integer primary key autoincrement,"""
"""tag varchar(256),"""
"""post int references posts);""",
"""INSERT INTO "tags" VALUES(NULL,'test',0);""",
"""CREATE TABLE "tags2" ( """
"""id integer primary key autoincrement,"""
"""tag varchar(256),"""
"""post int references posts);"""
"CREATE TABLE \"posts\" (id int primary key);",
"INSERT INTO \"posts\" VALUES(0);",
"CREATE TABLE \"tags\" (id integer primary key autoincrement, tag varchar(256), post int references posts);",
"INSERT INTO \"tags\" VALUES(NULL,'test',0);",
"CREATE TABLE \"tags2\" (id integer primary key autoincrement, tag varchar(256), post int references posts);"
]

for sql_statement in expected_sqls:
Expand All @@ -71,53 +67,42 @@ def test_dump_autoincrement(self):

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

for i in range(len(expected_sqls)):
self.assertEqual(expected_sqls[i], actual_sqls[i])
self.assertEqual(expected_sqls, actual_sqls)

def test_dump_autoincrement_create_new_db(self):
old_db = [
"""BEGIN TRANSACTION ;""",
"""CREATE TABLE "posts" (id int primary key);""",
"""INSERT INTO "posts" VALUES(0);""",
"""CREATE TABLE "tags" ( """
"""id integer primary key autoincrement,"""
"""tag varchar(256),"""
"""post int references posts);""",
"""CREATE TABLE "tags2" ( """
"""id integer primary key autoincrement,"""
"""tag varchar(256),"""
"""post int references posts);"""
"BEGIN TRANSACTION ;",
"CREATE TABLE \"posts\" (id int primary key);",
"INSERT INTO \"posts\" VALUES(0);",
"CREATE TABLE \"tags\" (id integer primary key autoincrement, tag varchar(256), post int references posts);",
"CREATE TABLE \"tags2\" (id integer primary key autoincrement, tag varchar(256), post int references posts);"
]
for i in range(1, 10):
old_db.append("""INSERT INTO "tags"
VALUES(NULL,'test{0}',0);""".format(i))
old_db.append("INSERT INTO \"tags\" VALUES(NULL,'test{0}',0);".format(i))
for i in range(1, 5):
old_db.append("""INSERT INTO "tags2"
VALUES(NULL,'test{0}',0);""".format(i))
old_db.append("INSERT INTO \"tags2\" VALUES(NULL,'test{0}',0);".format(i))
old_db.append("COMMIT;")

for sql_statement in old_db:
self.cu.execute(sql_statement)

cx2 = sqlite.connect(":memory:")
query = "".join(line for line in self.cx.iterdump())
cx2.executescript(query)
cu2 = cx2.cursor()
self.cu.executescript("".join(old_db))
query = "".join(self.cx.iterdump())

self.assertEqual(cu2.execute('SELECT "seq"'
'FROM "sqlite_sequence"'
'WHERE "name" == "tags"').fetchall()[0][0], 9)
self.assertEqual(cu2.execute('SELECT "seq" FROM'
'"sqlite_sequence"'
'WHERE "name" == "tags2"').fetchall()[0][0], 4)
with memory_database() as cx2:
cx2.executescript(query)
cu2 = cx2.cursor()

cu2.close()
cx2.close()
self.assertEqual(cu2.execute("SELECT \"seq\""
"FROM \"sqlite_sequence\""
"WHERE \"name\" == \"tags\"").fetchall()[0][0], 9)
Comment thread
erlend-aasland marked this conversation as resolved.
Outdated
self.assertEqual(cu2.execute("SELECT \"seq\" FROM"
"\"sqlite_sequence\""
"WHERE \"name\" == \"tags2\"").fetchall()[0][0], 4)

def test_unorderable_row(self):
# iterdump() should be able to cope with unorderable row types (issue #15545)
Expand Down