SQLite Forum

DUMP issue with FTS5
Login
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.

<pre>
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;

</pre>