Add dev script to generate full SQL schema files#6394
Add dev script to generate full SQL schema files#6394anoadragon453 merged 13 commits intodevelopfrom
Conversation
f019aff to
f6eff4b
Compare
richvdh
left a comment
There was a problem hiding this comment.
looks generally good, though it's bash so I have Opinions.
scripts-dev/generate_full_schema.sh
Outdated
| rm "$KEY_FILE" | ||
| dropdb $POSTGRES_DB_NAME | ||
|
|
||
| # Remove last pesky instance of this table from the output |
Co-Authored-By: Richard van der Hoff <1389908+richvdh@users.noreply.github.com>
| cat > "$SQLITE_CONFIG" <<EOF | ||
| server_name: "test" | ||
|
|
||
| signing_key_path: "$KEY_FILE" |
There was a problem hiding this comment.
so what happens if you don't set this?
There was a problem hiding this comment.
It looks for test.signing.key in the current directory, not the temp dir.
There was a problem hiding this comment.
Might be a bug. It's due to config_dir_path being "" here:
Line 97 in 54fef09
There was a problem hiding this comment.
well, that doesn't sound right. Ok, let's not get distracted by going on a bughunt now.
…ate_full_schema_instructions
…ate_full_schema_instructions
* commit '6676ee9c4': Add dev script to generate full SQL schema files (#6394)
| sqlite3 "$SQLITE_DB" ".dump" > "$OUTPUT_DIR/$SQLITE_FULL_SCHEMA_OUTPUT_FILE" | ||
|
|
||
| echo "Dumping Postgres schema to '$OUTPUT_DIR/$POSTGRES_FULL_SCHEMA_OUTPUT_FILE'..." | ||
| pg_dump --format=plain --no-tablespaces --no-acl --no-owner $POSTGRES_DB_NAME | sed -e '/^--/d' -e 's/public\.//g' -e '/^SET /d' -e '/^SELECT /d' > "$OUTPUT_DIR/$POSTGRES_FULL_SCHEMA_OUTPUT_FILE" |
There was a problem hiding this comment.
Removing ^SELECT lines has the unfortunate side effect of removing
SELECT pg_catalog.setval('account_data_sequence', 1, true);
SELECT pg_catalog.setval('application_services_txn_id_seq', 1, false);
SELECT pg_catalog.setval('cache_invalidation_stream_seq', 1, true);
SELECT pg_catalog.setval('device_inbox_sequence', 1, true);
SELECT pg_catalog.setval('event_auth_chain_id', 1, false);
SELECT pg_catalog.setval('events_backfill_stream_seq', 1, true);
SELECT pg_catalog.setval('events_stream_seq', 1, true);
SELECT pg_catalog.setval('instance_map_instance_id_seq', 1, false);
SELECT pg_catalog.setval('presence_stream_sequence', 1, true);
SELECT pg_catalog.setval('receipts_sequence', 1, true);
SELECT pg_catalog.setval('user_id_seq', 1, false);
Note that passing true to setval marks the set value as having been used, so that nextval will be larger. https://www.postgresql.org/docs/current/functions-sequence.html
This probably explains #13873 (comment).
Fixes #6476
So we ran into a bug that was a result of
INSERTstatements getting lost from converting SQL delta files to full schema. This was due to that process just copying over the schema of fully updated SQLite and Postgres tables, instead of the actual data.Additionally, there wasn't any automated way (or much documentation) on how to generate full schemas, and I think if we had a canonical, automated way we might do it more often.
The script does this by generating a sqlite database with all background updates applied to it, then making use of the now-working
synapse_port_dbscript to convert it to a postgres database. These two databases are then dumped tofull.{sqlite,postgres}.sqlfiles.Also converted the full schema README from .txt to .md.