Feature Request: consistent .dump order
(1) By Mark Lawrence (mark) on 2020-04-22 12:06:02 [link]
It appears that the `.dump` shell command selects tables from sqlite_master in rowid or insertion order: > CREATE TABLE a(id INTEGER); > CREATE TABLE b(id INTEGER); > .dump CREATE TABLE a(id INTEGER); CREATE TABLE b(id INTEGER); > select * from sqlite_master; type name tbl_name rootpage sql ---------- ---------- ---------- ---------- -------------------------- table a a 2 CREATE TABLE a(id INTEGER) table b b 3 CREATE TABLE b(id INTEGER) > DROP TABLE a; > CREATE TABLE a(id INTEGER); > .dump CREATE TABLE b(id INTEGER); -- REVERSE of previous ordering CREATE TABLE a(id INTEGER); > select * from sqlite_master; type name tbl_name rootpage sql ---------- ---------- ---------- ---------- -------------------------- table b b 3 CREATE TABLE b(id INTEGER) table a a 2 CREATE TABLE a(id INTEGER) I store some SQLite databases in Git using a filter that calls .dump. If I happen to change a table or view definition the resulting diff is unecessarily large and confusing because of the change in output order. Would it be possible to modify .dump (and .schema) to produce a consistent output ordering? Thanks.
(2) By Kees Nuyt (knu) on 2020-04-22 13:59:50 in reply to 1 [link]
The .dump command accepts a table name as an optional argument. It's not overly difficult to script it any way you like: ```sh for tbl in \ $(sqlite3 $db "SELECT name FROM sqlite_master WHERE type=='table' ORDER BY name") do sqlite3 $db ".dump $tbl" done >yourdumpfile ``` Hope this helps. ~~~ -- Regards, Kees Nuyt ~~~
(4) By Mark Lawrence (mark) on 2020-04-22 15:42:06 in reply to 2 [link]
That helps to a degree. Unfortunately triggers and indexes are not explicitly ordered either, so the issue is still the same (within a single call to .dump).
(3) By Larry Brasfield (LarryBrasfield) on 2020-04-22 15:15:41 in reply to 1 [link]
Of course it's possible. You could modify the .dump command code reading ``` zSql = sqlite3_mprintf( "SELECT name, type, sql FROM sqlite_master " "WHERE (%s) AND type=='table'" " AND sql NOT NULL" " ORDER BY tbl_name='sqlite_sequence', rowid", zLike ); ``` to change the ORDER BY clause, rebuild, and have what you want.
(5) By tom (younique) on 2020-04-23 08:06:14 in reply to 1 [link]
> Would it be possible to modify .dump (and .schema) to produce a consistent output ordering? +1 for that. Additionally, I would ask for the dump to include "PRAGMA application_id" AND "PRAGMA user_version" so that databases can be fully restored from the dump.
(6.1) By Tony Papadimitriou (tonyp) on 2020-04-23 22:57:33 edited from 6.0 in reply to 5 [link]
... and "PRAGMA page_size" Thank you
(7) By Mark Lawrence (mark) on 2020-05-21 12:57:52 in reply to 1 [link]
I have a (dangerous) user-space workaround for the `.dump` order requirement: CREATE TEMPORARY TABLE _master AS select * from sqlite_master; PRAGMA writable_schema=ON; DELETE FROM sqlite_master; INSERT INTO sqlite_master SELECT * from _master ORDER BY tbl_name,type,name; PRAGMA writable_schema=OFF; DROP TABLE temp._master; Running the above before `.dump` seems to do the trick, without having to modify and build from source. Would still be nice to have it by default or as an `--ordered` option to `.dump`.
(8) By Mark Lawrence (mark) on 2020-05-21 14:12:46 in reply to 7
Actually, the above does *NOT* do the trick, but results in a corrupted database: Error: malformed database schema (sqlite_autoindex_countries_1) - orphan index Back to the drawing board :-(
(9) By Kees Nuyt (knu) on 2020-05-21 15:02:23 in reply to 8 [link]
> Actually, the above does NOT do the trick, but results in a corrupted database Yeah, tables should be defined before anything that refers to them. You need a different sort order, illustrated by this script: ```sql .mode column .headers on .width -5 9 32 32 -8 -6 SELECT ROWID,type,name,tbl_name,rootpage, CASE WHEN type=='table' THEN 1 WHEN type=='index' THEN 2 WHEN type=='trigger' THEN 3 ELSE 9 END AS srtord FROM sqlite_master ORDER BY srtord,tbl_name,name; ``` In reality, things are even more complicated of you want to insert in dependency order, with ```PRAGMA foreign_keys=on``` Parent tables have to be loaded befor children. ~~~ -- Regards, Kees Nuyt ~~~