Dumps from SQLite

apsw shell.sql

SQLite version 3.7.15.2 (APSW 3.7.15.2-r1)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
-- SQLite dump (by APSW 3.7.15.2-r1)
-- SQLite version 3.7.15.2
-- Date: Fri Mar  1 05:34:14 2013
-- Tables like: (All)
-- Database: /tmp/foo.db
-- User: rogerb @ workstation

-- The values of various per-database settings
-- PRAGMA page_size=1024;
-- PRAGMA encoding='UTF-8';
-- PRAGMA auto_vacuum=NONE;
-- PRAGMA max_page_count=1073741823;

-- This pragma is needed to restore virtual tables
PRAGMA writable_schema=ON;
-- This pragma turns off checking of foreign keys as tables would be
-- inconsistent while restoring.  It was introduced in SQLite 3.6.19.
PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

-- Table  another
DROP TABLE IF EXISTS another;
CREATE TABLE another(y, foreign key(y) references x(z));
INSERT INTO another VALUES('abc');

-- Table  fts
DELETE FROM sqlite_master WHERE name='fts' AND type='table';
INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql) VALUES('table','fts','fts',0,'CREATE VIRTUAL TABLE fts using fts4(title,body)');

-- Table  fts_content
DROP TABLE IF EXISTS fts_content;
CREATE TABLE 'fts_content'(docid INTEGER PRIMARY KEY, 'c0title', 'c1body');
INSERT INTO fts_content VALUES(1,'a title','the body');

-- Table  fts_docsize
DROP TABLE IF EXISTS fts_docsize;
CREATE TABLE 'fts_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
INSERT INTO fts_docsize VALUES(1,X'0202');

-- Table  fts_segdir
DROP TABLE IF EXISTS fts_segdir;
CREATE TABLE 'fts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
INSERT INTO fts_segdir VALUES(0,0,0,0,0,X'000161030102000004626F64790501010103000003746865050101010200010469746C6503010300');

-- Table  fts_segments
DROP TABLE IF EXISTS fts_segments;
CREATE TABLE 'fts_segments'(blockid INTEGER PRIMARY KEY, block BLOB);

-- Table  fts_stat
DROP TABLE IF EXISTS fts_stat;
CREATE TABLE 'fts_stat'(id INTEGER PRIMARY KEY, value BLOB);
INSERT INTO fts_stat VALUES(0,X'0102020F');

-- Table  incr
DROP TABLE IF EXISTS incr;
CREATE TABLE incr(x integer primary key autoincrement,z);
INSERT INTO incr VALUES(1,1);
INSERT INTO incr VALUES(2,'one');
INSERT INTO incr VALUES(3,1.0);

-- Table  x
DROP TABLE IF EXISTS x;
CREATE TABLE x(y integer primary key, z);
INSERT INTO x VALUES(3,'abc');
INSERT INTO x VALUES(7,'nul char->'||X'00'||'<- nul char');

-- For primary key autoincrements the next id to use is stored in
-- sqlite_sequence
DELETE FROM main.sqlite_sequence WHERE name='incr';
INSERT INTO main.sqlite_sequence VALUES ('incr', 3);

-- Your database may need this.  It is sometimes used to keep track of the
-- schema version (eg Firefox does this).
-- pragma user_version=7;

COMMIT TRANSACTION;

-- Restoring foreign key checking back on.  Note that SQLite 3.6.19 is off by
-- default
PRAGMA foreign_keys=ON;

-- Restoring writable schema back to default
PRAGMA writable_schema=OFF;

-- We need to force SQLite to reread the schema because otherwise it doesn't
-- know that the virtual tables we inserted directly into sqlite_master exist.
-- See last comments of https://sqlite.org/cvstrac/tktview?tn=3425
BEGIN;
CREATE TABLE no_such_table(x,y,z);
ROLLBACK;

sqlite shell.sql

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(y integer primary key, z);
INSERT INTO "x" VALUES(3,'abc');
INSERT INTO "x" VALUES(7,'nul char->');
PRAGMA writable_schema=ON;
INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)VALUES('table','fts','fts',0,'CREATE VIRTUAL TABLE fts using fts4(title,body)');
CREATE TABLE 'fts_content'(docid INTEGER PRIMARY KEY, 'c0title', 'c1body');
INSERT INTO "fts_content" VALUES(1,'a title','the body');
CREATE TABLE 'fts_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'fts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
INSERT INTO "fts_segdir" VALUES(0,0,0,0,0,X'000161030102000004626F64790501010103000003746865050101010200010469746C6503010300');
CREATE TABLE 'fts_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
INSERT INTO "fts_docsize" VALUES(1,X'0202');
CREATE TABLE 'fts_stat'(id INTEGER PRIMARY KEY, value BLOB);
INSERT INTO "fts_stat" VALUES(0,X'0102020F');
CREATE TABLE another(y, foreign key(y) references x(z));
INSERT INTO "another" VALUES('abc');
CREATE TABLE incr(x integer primary key autoincrement,z);
INSERT INTO "incr" VALUES(1,1);
INSERT INTO "incr" VALUES(2,'one');
INSERT INTO "incr" VALUES(3,1.0);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('incr',3);
PRAGMA writable_schema=OFF;
COMMIT;