SQLite Forum

Documentation should be improved for the CLI command .dump
Login

Documentation should be improved for the CLI command .dump

(1) By anonymous on 2021-03-15 06:43:33 [link] [source]

Here a simple database schema:

CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES ( 'aaa' );
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );

CREATE TABLE tbl2 ( b );
INSERT INTO tbl2 VALUES ( 'bbb' );
CREATE INDEX index3 ON tbl2 ( b );

CREATE VIEW tbl3 AS
SELECT a FROM tbl1
UNION ALL
SELECT b FROM tbl2;

Copying a single table with the CLI to another database was possible in old versions (e.g. version 3.30) as follows:

sqlite3 source.db ".dump tbl1" | sqlite3 dest.db

Here the dump with version 3.30:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> .dump tbl1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );
COMMIT;

In current versions the CREATE INDEX command is missing in the output.

SQLite version 3.35.0 2021-03-12 15:10:09
Enter ".help" for usage hints.
sqlite> .dump tbl1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
COMMIT;

I have read the documentation for the CLI but have not found any hint.

The built-in help says the following:

SQLite version 3.35.0 2021-03-12 15:10:09
Enter ".help" for usage hints.
sqlite> .help dump
.dump ?TABLE?            Render database content as SQL
   Options:
     --data-only            Output only INSERT statements
     --newlines             Allow unescaped newline characters in output
     --nosys                Omit system tables (ex: "sqlite_stat1")
     --preserve-rowids      Include ROWID values in the output
   TABLE is a LIKE pattern for the tables to dump
   Additional LIKE patterns can be given in subsequent arguments

It took me some time to figure out how it works now.


sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE TABLE tbl2 ( b );
INSERT INTO tbl2 VALUES('bbb');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );
CREATE INDEX index3 ON tbl2 ( b );
CREATE VIEW tbl3 AS
SELECT a FROM tbl1
UNION ALL
SELECT b FROM tbl2;
COMMIT;

sqlite> .dump tbl1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
COMMIT;

sqlite> .dump tbl1%
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
COMMIT;

sqlite> .dump tbl1 tbl1_index1 index2
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl1 ( a );
INSERT INTO tbl1 VALUES('aaa');
CREATE INDEX tbl1_index1 ON tbl1 ( a );
CREATE INDEX index2 ON tbl1 ( a DESC );
COMMIT;

sqlite> .dump %3%
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE INDEX index3 ON tbl2 ( b );
CREATE VIEW tbl3 AS
SELECT a FROM tbl1
UNION ALL
SELECT b FROM tbl2;
COMMIT;

Maybe it would be a good idea to make the documentation here clearer...

.dump ?CONTENT?            Render database content as SQL
   CONTENT is a LIKE pattern for tables, indexes, views or triggers to dump

Regards

Herbert Gläser

(2) By Larry Brasfield (larrybr) on 2021-03-15 07:44:53 in reply to 1 [link] [source]

I summarize your suggestion thusly:

The CLI shell's .help output for .dump should reflect the fact that it can dump tables, views or indexes. Presently, the .help output implies that only tables can be dumped, saying .dump ?TABLE? ... TABLE is a LIKE pattern for the tables to dump

FWIW, I have to agree that the word "TABLE" should change to something less restrictive. However, your suggested "CONTENT" also seems too restrictive as it would appear to exclude the object schema which is output by default. Maybe, when my morning caffeine dose takes effect, something better will occur to me.

(3) By anonymous on 2021-03-15 08:09:50 in reply to 2 [source]

Larry Thank you for the reply

I am convinced that a good name will be found here.

Thanks for the great work