Feature Request: consistent .dump order
(1) By Mark Lawrence (mark) on 2020-04-22 12:06:02 [link] [source]
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] [source]
The .dump command accepts a table name as an optional argument. It's not overly difficult to script it any way you like:
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 [source]
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] [source]
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] [source]
> 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] [source]
... and "PRAGMA page_size"
Thank you
(7) By Mark Lawrence (mark) on 2020-05-21 12:57:52 in reply to 1 [link] [source]
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 [link] [source]
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] [source]
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:
.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