SQLite Forum

Feature Request: consistent .dump order
Login

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