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]

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

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
~~~

(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.

(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).

(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 [link]

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
~~~