[Feature]: Fix ordering of schema export

[nneil]

Describe the new feature

Tables, Foreign keys, etc., but obviously not columns, should be sorted on export.

When exporting the DB schema as an SQL file the order of the exported items varies from one export to the next if something has been changed in the DB. This makes it hard to detect meaningful changes amongst the noise. For example foreign keys can be in random order, even if their table has not been changed.

Does this feature exist in another product or project? Please provide a link

HediSQL sorts exported tables, views, etc. But doesn't work properly with SQLite.

Do you have a screenshot? Please add screenshots to help explain your idea.

No response

[chrisjlocke]

This has sort of been raised before. If table A has foreign keys to table B, then when the SQL is run it fails as table B doesn't exist yet, so your ordering request makes sense. However, for some users, understanding and processing this could be quite a task. Programmatically it could be even worse.

[nneil]

If the issue has been raised before it should have been addressed. Look at this diff and quickly tell me what has changed. Then justify the rest of the changes:

diff --git a/sql/create-db.sql b/sql/create-db.sql
--- a/sql/create-db.sql
+++ b/sql/create-db.sql
@@ -27,9 +27,9 @@ DROP TABLE IF EXISTS "user_i";
 CREATE TABLE IF NOT EXISTS "user_i" (
 	"user_id"	INTEGER,
 	"i_id"	INTEGER,
-	FOREIGN KEY("i_id") REFERENCES "i"("id") on delete cascade,
+	PRIMARY KEY("user_id","i_id"),
 	FOREIGN KEY("user_id") REFERENCES "user"("id") on delete cascade,
-	PRIMARY KEY("user_id","i_id")
+	FOREIGN KEY("i_id") REFERENCES "i"("id") on delete cascade
 );
 DROP TABLE IF EXISTS "connection";
 CREATE TABLE IF NOT EXISTS "connection" (
@@ -40,17 +40,17 @@ CREATE TABLE IF NOT EXISTS "connection" (
 	"a"	INTEGER NOT NULL DEFAULT 0,
 	"n"	TEXT,
 	"s"	TEXT,
-	FOREIGN KEY("b_user_id") REFERENCES "user"("id") on delete cascade,
+	PRIMARY KEY("a_user_id","b_user_id"),
 	FOREIGN KEY("a_user_id") REFERENCES "user"("id") on delete cascade,
-	PRIMARY KEY("a_user_id","b_user_id")
+	FOREIGN KEY("b_user_id") REFERENCES "user"("id") on delete cascade
 );
 DROP TABLE IF EXISTS "i"; CREATE TABLE IF NOT EXISTS "i" (
 	"id"	INTEGER,
 	"c"	INTEGER,
 	"n"	TEXT NOT NULL UNIQUE,
-	FOREIGN KEY("c") REFERENCES "i"("id"),
-	PRIMARY KEY("id")
+	PRIMARY KEY("id"),
+	FOREIGN KEY("c") REFERENCES "i"("id")
 );
 DROP TABLE IF EXISTS "m";
 CREATE TABLE IF NOT EXISTS "m" (
@@ -59,10 +59,10 @@ CREATE TABLE IF NOT EXISTS "m" (
 	"a_user_id"	INTEGER NOT NULL,
 	"b_user_id"	INTEGER NOT NULL,
 	"r"	INTEGER DEFAULT 0,
-	"m"	TEXT NOT NULL,
-	FOREIGN KEY("b_user_id") REFERENCES "user"("id") ON DELETE CASCADE,
+	"t"	TEXT NOT NULL,
+	PRIMARY KEY("id" AUTOINCREMENT),
 	FOREIGN KEY("a_user_id") REFERENCES "user"("id") ON DELETE CASCADE,
-	PRIMARY KEY("id" AUTOINCREMENT)
+	FOREIGN KEY("b_user_id") REFERENCES "user"("id") ON DELETE CASCADE
 );
 DROP INDEX IF EXISTS "idx.connection_from";
 CREATE INDEX IF NOT EXISTS "idx.connection_from" ON "connection" (

[chrisjlocke]

If the issue has been raised before it should have been addressed.

Addressing an issue requires code.
There is no code that has been posted in a PR that addresses this issue.

Without code to address the issue, how do you propose the issue should be addressed?

I have tried to wave my magic wand, but unfortunately it appears it's out of fairy dust.
Further comments on how I can obtain more fairy dust would be welcomed.

Sorry. Proper answer...

it should have been addressed

We await a PR to address the issue. This is an open-source project, so anyone can contribute.
Once a PR has been raised (and checked) it can be used.
Obviously until then, the issue cannot be 'addressed'.
Unfortunately, we don't have a team of 900 developers just standing by waiting for issues.