classification
Title: sqlite dumpiter dumps invalid script when virtual tables are used
Type: behavior Stage:
Components: Extension Modules Versions: Python 2.7
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: ghaering Nosy List: Ronny.Pfannschmidt, ghaering, palaviv, r.david.murray, xtreak
Priority: normal Keywords: patch

Created on 2014-01-31 19:57 by Ronny.Pfannschmidt, last changed 2018-09-23 16:16 by xtreak.

Files
File name Uploaded Description Edit
20463.patch palaviv, 2016-10-10 18:04 review
Messages (3)
msg209825 - (view) Author: Ronny Pfannschmidt (Ronny.Pfannschmidt) Date: 2014-01-31 19:57
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;
msg248823 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2015-08-19 09:40
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 https://github.com/ghaering/pysqlite/issues/10 for the tasks and
https://github.com/rogerbinns/apsw/blob/master/tools/shell.py#L1012 for the apsw code.
msg278437 - (view) Author: Aviv Palivoda (palaviv) * Date: 2016-10-10 18:04
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;
History
Date User Action Args
2018-09-23 16:16:06xtreaksetnosy: + xtreak
2016-10-10 18:04:19palavivsetfiles: + 20463.patch

nosy: + palaviv
messages: + msg278437

keywords: + patch
2015-08-19 09:40:17ghaeringsetmessages: + msg248823
2015-01-11 02:01:17ghaeringsetassignee: ghaering
2014-01-31 21:54:24r.david.murraysetnosy: + r.david.murray
2014-01-31 20:44:27yselivanovsetnosy: + ghaering
2014-01-31 20:04:21Ronny.Pfannschmidtsettype: behavior
components: + Extension Modules
2014-01-31 19:57:36Ronny.Pfannschmidtcreate