Repository navigation
sqlite dumpiter dumps invalid script when virtual tables are used #64662
Description
| BPO | 20463 |
|---|---|
| Nosy | @bitdancer, @RonnyPfannschmidt, @palaviv, @tirkarthi |
| Files |
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
assignee = None
closed_at = None
created_at = <Date 2014-01-31.19:57:36.445>
labels = ['extension-modules', 'type-bug']
title = 'sqlite dumpiter dumps invalid script when virtual tables are used'
updated_at = <Date 2018-09-23.16:16:06.541>
user = 'https://github.com/RonnyPfannschmidt'bugs.python.org fields:
activity = <Date 2018-09-23.16:16:06.541>
actor = 'xtreak'
assignee = 'ghaering'
closed = False
closed_date = None
closer = None
components = ['Extension Modules']
creation = <Date 2014-01-31.19:57:36.445>
creator = 'Ronny.Pfannschmidt'
dependencies = []
files = ['45049']
hgrepos = []
issue_num = 20463
keywords = ['patch']
message_count = 3.0
messages = ['209825', '248823', '278437']
nosy_count = 5.0
nosy_names = ['ghaering', 'r.david.murray', 'Ronny.Pfannschmidt', 'palaviv', 'xtreak']
pr_nums = []
priority = 'normal'
resolution = None
stage = None
status = 'open'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue20463'
versions = ['Python 2.7']Linked PRs
- gh-64662: Add virtual table support to sqlite3.Connection.iterdump #108340
- [3.12] gh-64662: Fix virtual table support in sqlite3.Connection.iterdump (#108340) #108563
- [3.11] gh-64662: Fix virtual table support in sqlite3.Connection.iterdump (#108340) #108564
- gh-64662: Fix NEWS entry and remove What's New entry #108565
Activity
RonnyPfannschmidt commentedon Jan 31, 2014
when using virtual tables, dumpiter generates a broken db script
virtual table entries must be created as master table entries
the sqlite tools dump does that correctly
however pythons iterdump seems to do that rather different and wrong
sqlite3 test.db "create virtual table test using fts4(example);"
-------------------------------
sqlite dump
PRAGMA foreign_keys=OFF;
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)');
CREATE TABLE 'test_content'(docid INTEGER PRIMARY KEY, 'c0example');
CREATE TABLE 'test_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'test_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
CREATE TABLE 'test_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE 'test_stat'(id INTEGER PRIMARY KEY, value BLOB);
PRAGMA writable_schema=OFF;
COMMIT;
------------------------------
python iterdump "import sqlite3;
c=sqlite3.connect("test.db");
print("\n".join(c.iterdump()))"
BEGIN TRANSACTION;
CREATE VIRTUAL TABLE test using fts4(example);
CREATE TABLE 'test_content'(docid INTEGER PRIMARY KEY, 'c0example');
CREATE TABLE 'test_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE 'test_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
CREATE TABLE 'test_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'test_stat'(id INTEGER PRIMARY KEY, value BLOB);
COMMIT;
ghaering commentedon Aug 19, 2015
apsw contains code that handles the issues with dumping SQLite databases very well. I plan to integrate this code into pysqlite. We can then later port the fix to the sqlite3 module.
See ghaering/pysqlite#10 for the tasks and
https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L1012 for the apsw code.
palaviv commentedon Oct 10, 2016
Attached is patch with a fix for this issue. This is not as comprehensive as the solution in apsw but I think this should cover most of the cases. The result for Ronny dump after this fix is:
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)');
CREATE TABLE 'test_content'(docid INTEGER PRIMARY KEY, 'c0example');
CREATE TABLE 'test_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE 'test_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
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;
erlend-aasland commentedon Aug 22, 2023
My plan for
iterdumpis to align it with the implementation in the SQLite shell, but I haven't gotten around to it yet. I think @palaviv's patch is a step in the right direction, and I don't think we should block that patch, just becauseiterdumpalso has other problems.10 remaining items
RonnyPfannschmidt commentedon Aug 28, 2023
I consider it a bug as the output wasn't valid,it should error if it cant make it,
erlend-aasland commentedon Aug 28, 2023
Hm, yeah, that is a valid point that weighs heavier than the comment.
erlend-aasland commentedon Aug 28, 2023
OTOH, this changes the behaviour, which is an argument not to backport it.
RonnyPfannschmidt commentedon Aug 28, 2023
@erlend-aasland personally i prefer people see a error when they dump rather than when they later load that dump
this bug in the worst case makes a broken backup silently appear working - not failing early causes more damage
silent breakage/invalid datagen is imho worse than adding a error where else people have hidden broken data until they test again
RonnyPfannschmidt commentedon Aug 28, 2023
ftr - imho its fine to have a backport that just finally raises a NotImplementedError rather than silently dumping broken data
erlend-aasland commentedon Aug 28, 2023
I'd rather backport the fix. Anyway, you've convinced me; I agree that this is more a bugfix than a new feature. I'll prepare backports through 3.11.
[3.12] pythongh-64662: Add virtual table support to sqlite3.Connectio…
[3.11] pythongh-64662: Add virtual table support to sqlite3.Connectio…
erlend-aasland commentedon Aug 28, 2023
Thanks for you input, @RonnyPfannschmidt.
gh-64662: Fix NEWS entry and remove What's New entry (#108565)
[3.11] gh-64662: Fix virtual table support in sqlite3.Connection.iter…
erlend-aasland commentedon Aug 28, 2023
Backports are queued; marking this as resolved.
[3.12] gh-64662: Fix virtual table support in sqlite3.Connection.iter…