SQLite Forum

DUMP issue with FTS5
Login

DUMP issue with FTS5

(1) By anonymous on 2021-05-24 15:48:38 [link] [source]

The .dump shell command will NOT dump its contents, only the schema, indirectly. I get this from .dump:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t','t',0,'CREATE VIRTUAL TABLE "t" using fts5(id,level,su,en)');
PRAGMA writable_schema=OFF;
COMMIT;

The only work around is to create another real table (not even a temp one), and then .dump that table.

Could this somehow be 'fixed'?

I understand this is possibly not viewed as a bug, but from a practical point of view, it sucks!

I happened to have a single table and took notice of this issue immediately.

But, if it had been part of a multi-table database, I possibly wouldn't have noticed that table's contents are missing from the dump until some time later when I would have to restore the database from the incomplete dump.

(2) By Dan Kennedy (dan) on 2021-05-24 20:58:50 in reply to 1 [source]

Looks like it does work if you just run plain ".dump", but if you do ".dump tblname" you don't get the shadow tables that actually contain the data. Another workaround would be ".dump tblname%". e.g.

sqlite> .dump t1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t1','t1',0,'CREATE VIRTUAL TABLE t1 USING fts5(x)');
PRAGMA writable_schema=OFF;
COMMIT;

sqlite> .dump t1%
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t1','t1',0,'CREATE VIRTUAL TABLE t1 USING fts5(x)');
CREATE TABLE IF NOT EXISTS 't1_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO t1_data VALUES(1,X'');
INSERT INTO t1_data VALUES(10,X'00000000000000');
CREATE TABLE IF NOT EXISTS 't1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 't1_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 't1_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 't1_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO t1_config VALUES('version',4);
PRAGMA writable_schema=OFF;
COMMIT;

sqlite> .dump 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','t1','t1',0,'CREATE VIRTUAL TABLE t1 USING fts5(x)');
CREATE TABLE IF NOT EXISTS 't1_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO t1_data VALUES(1,X'');
INSERT INTO t1_data VALUES(10,X'00000000000000');
CREATE TABLE IF NOT EXISTS 't1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 't1_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 't1_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 't1_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO t1_config VALUES('version',4);
PRAGMA writable_schema=OFF;
COMMIT;

(3) By Dan Kennedy (dan) on 2021-05-25 16:14:42 in reply to 2 [link] [source]