Create a new full schema snapshot

[erikjohnston]

If nothing else, this will speed up the unit tests quite a lot.

There is a scripts-dev/make_full_schema.sh script that sort of half works ish. The challenges are:

1. We have to handle the fact there are two different sets of schemas, "main" and "state".
2. There are also some tables that exist on all DBs (e.g. schema_version table)

[DMRobertson]

I would like to try and simplify the make_full_schema script as I do this. My current objections:

• We use portdb to port the sqlite schema onto postgres, meaning that any postgres-specific deltas might not be represented in the new postgres schema? (I don't trust portdb.)
• We have to manually omit sqlite's full text search extra tables (_content, _segments, _segdir, _docsize, _stat)
• We also have to manually omit the schema metadata tables schema_version, applied_schema_deltas, applied_module_schemas
• The generated output from sqlite requires some manual tidy-up.
• Unclear how to handle the main and state schemas.

[DMRobertson]

We can generate a tidier schema using sqlite's .schema instead of .dump. But doing so omits various INSERT INTO statements:

$ scripts-dev/make_full_schema.sh -o dump2 -p synapse_user
[...]
$ grep "INSERT INTO" dump2/full.sql.sqlite 
INSERT INTO schema_compat_version VALUES('X',72);
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','event_search','event_search',0,'CREATE VIRTUAL TABLE event_search USING fts4 ( event_id, room_id, sender, key, value )');
INSERT INTO appservice_stream_position VALUES('X',0);
INSERT INTO federation_stream_position VALUES('federation',-1,'master');
INSERT INTO federation_stream_position VALUES('events',-1,'master');
INSERT INTO event_push_summary_stream_ordering VALUES('X',0);
INSERT INTO user_directory_stream_pos VALUES('X',1);
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','user_directory_search','user_directory_search',0,'CREATE VIRTUAL TABLE user_directory_search USING fts4 ( user_id, value )');
INSERT INTO stats_incremental_position VALUES('X',1);
INSERT INTO event_push_summary_last_receipt_stream_id VALUES('X',0);

If Synapse uses upserts to populate these rows, then there is no need to include inserts in the schema, and I can use .schema to make the process of taking a schema dump more automatic.

• appservice_stream_position: update
• federation_stream_position: upsert here but update here. Needs thought.
• event_push_summary_stream_ordering: update
• user_directory_stream_pos: update; portdb does an insert
• stats_incremental_position: update
• event_push_summary_last_receipt_stream_id: update

[DMRobertson]

If [...] I can use .schema to make the process of taking a schema dump more automatic.

On reflection I think I should suck it up and leave the dump as it is, lest we reintroduce #6311.

[richvdh]

@DMRobertson are you actively working on this? I've got most of a rewrite of make_full_schema which I think will work.

[clokep]

@DMRobertson are you actively working on this? I've got most of a rewrite of make_full_schema which I think will work.

I believe #13770, #13788, #13792, and #13808 are related to this.

[richvdh]

ah, thanks. Sorry, I should have said something sooner.

[DMRobertson]

@DMRobertson are you actively working on this? I've got most of a rewrite of make_full_schema which I think will work.

I believe #13770, #13788, #13792, and #13808 are related to this.

That's right. #13792 is the most interesting of those. I wasn't planning to do anything more on making a snapshot before of the above reviews and before @clokep and @MadLittleMods get their upcoming database changes into a stableish state.

[DMRobertson]

#13873 has

• additional fixes to the schema dump script
• Synapse changes which ingest SQL directly (avoiding problems where our hand-written parser(!?) cannot handle trigger definitions
• The actual dumps