From 752237a05d424c132f51a0bba76dea84e2d72b81 Mon Sep 17 00:00:00 2001 From: Shane Synan Date: Sun, 17 Jun 2018 14:25:39 -0500 Subject: [PATCH] docs: Move SQL schema to README.md, add more help Move SQL schema guidelines to a dedicated README.md, with all the niceties of Markdown formatting and the added prominence of GitHub displaying it whenever navigating to the `src/sql` folder. Add a brief high-level overview of Quassel's database schema. Polish up the SQL schema guidelines to follow the current Quassel workflow. Clean up remarks in the script files, including pointing to the new README.md file. --- src/core/SQL/README.md | 277 ++++++++++++++++++++++++++++++ src/core/SQL/updateSQLResource.sh | 4 +- src/core/SQL/upgradeSchema.sh | 143 +-------------- 3 files changed, 287 insertions(+), 137 deletions(-) create mode 100644 src/core/SQL/README.md diff --git a/src/core/SQL/README.md b/src/core/SQL/README.md new file mode 100644 index 00000000..13e905ce --- /dev/null +++ b/src/core/SQL/README.md @@ -0,0 +1,277 @@ +# Database schema management in Quassel IRC + +## Introduction + +Quassel IRC supports two database backends for storing chat history, user +sessions, etc: + +* [PostgreSQL][postgres-home], in the [`PostgreSQL`](PostgreSQL/) folder +* [SQLite][sqlite-home], in the [`SQLite`](SQLite/) folder + +For the most part, Quassel strives to have consistency between the two +backends, offering the same user experience and similar `.sql` query files, +though there are times when one database supports features that another does +not. + +All of the current schema version queries are stored at the top level of the +backend subdirectory, with schema upgrade queries stored in +`[backend]/version/##` folders. + +At compile time, Qt reads [`sql.qrc`][file-qrc-sql] to know which queries to +include. This list can be automatically updated on Linux and macOS using +[`updateSQLResource.sh`][file-sh-updateresource]; for now, on Windows you'll +need to install some form of Bash. + +## Managing the database outside of Quassel + +Whenever possible, it's recommended to use Quassel itself to make changes, such +as deleting chats or changing user passwords. + +However, some tasks aren't yet possible in Quassel, and you should +[read the documentation on managing the database][docs-wiki-db-manage] if the +need arises. + +## Making changes to the database +### Changes to existing queries, no new/moved files + +If you're only modifying existing queries in a way that does **not** require +any schema changes (*e.g. `ALTER TABLE`, `CREATE TABLE`, `CREATE INDEX` +statements*), you can just modify the `.sql` files in the appropriate backends. + +However, any database schema changes must fulfill a number of requirements. + +### New queries, schema changes, etc + +> One does not simply 'upgrade the schema'... + +When changing Quassel's database schema, you need to follow several steps to +handle all cases (*upgrade, Postgres migration, etc*). Some of these steps may +not apply to all schema upgrades. + +1. **Run [`upgradeSchema.sh`][file-sh-upgradeschema] script on *both* +PostgreSQL and SQLite directories** + +Make sure you're on the Git branch you want to modify + +*Example:* +```sh +./upgradeSchema.sh "PostgreSQL" +./upgradeSchema.sh "SQLite" +``` + +Or, you can manually create new `[backend]/version/##` folders for both +`PostgreSQL` and `SQLite`. Pick the next higher number from the largest +version number for each (*the exact version numbers will usually differ; that's +fine*). + +2. **Modify queries and setup scripts to handle your change** + +The specifics depend on your change; in general, you'll want to modify any +query files that select from or insert to a modified table. Then, modify the +`setup_###_[...].sql` files to include your changes on new database installs. + +> *Example: modifying the `ircserver` table to add column `test`* +> +> Modify all query/setup `.sql` files that touch the `ircserver` table for +> *both* `PostgreSQL` and `SQLite`. + +3. **Create upgrade scripts for *both* PostgreSQL and SQLite** + +These should go in the newest `[backend]/version/##` folders that were created +in Step 1. + +Outside of special circumstances, do **not** modify the files in lower-numbered +version folders. Existing Quassel cores have already run those statements and +will not run them if changed. + +> *Example: modifying the `ircserver` table to add column `test`* +> +> Add the file `upgrade_000_alter_ircserver_add_test.sql` with contents: +> +> ```sql +> ALTER TABLE ircserver +> ADD COLUMN test [additional column-specific details] +> ``` + +4. **Update the pair of migration scripts for moving from SQLite to +PostgreSQL** + +For any table changes, you'll need to update the relevant +`SQLite/migrate_read_[table].sql` to read the existing data, and the +`PostgreSQL/migrate_write_[table].sql` to insert this data. + +> *Example: modifying the `ircserver` table to add column `test`* +> +> Modify `SQLite/migrate_read_ircserver.sql` to select from new column +> +> Modify `PostgreSQL/migrate_write_ircserver.sql` to insert to new column + +5. **Update the SQL resource file; re-run CMake if needed** + +**The easy way:** + +Run [`updateSQLResource.sh`][file-sh-updateresource] in this directory. + +**The manual way:** + +Add the new SQL queries to [`src/core/sql.qrc`][file-qrc-sql], update all +changed existing files. + +> *Example: modifying the `ircserver` table to add column `test`* +> +> Add the new upgrade scripts: +> ```diff +> + ./SQL/SQLite/version/19/upgrade_000_alter_ircserver_add_test.sql +> + ./SQL/PostgreSQL/version/18/upgrade_000_alter_ircserver_add_test.sql +> ``` +> +> Add/update non-upgrade scripts, if any: +> ```diff +> + ./SQL/SQLite/update_buffer_persistent_channel.sql +> + ./SQL/PostgreSQL/update_buffer_persistent_channel.sql +> + [etc] +> ``` + +6. **Update the migration logic in +[`src/core/abstractsqlstorage.cpp`][file-cpp-abstract], and the storage +backends [`postgresqlstorage.cpp`][file-cpp-postgres] and +[`sqlitestorage.cpp`][file-cpp-sqlite]** + +Make sure to read the data from SQLite in the right types and order, then write +it to PostgreSQL also with the right types and order. SQLite and PostgreSQL +may use different database representations of certain types. + +> *Example: modifying the `ircserver` table to add column `test`* +> +> Modify struct `IrcServerMO` in [`abstractsqlstorage.h`][file-h-abstract], +> adding an entry for `test` of the appropriate data-type. +> +> Modify `SqliteMigrationReader::readMo(IrcServerMO &ircserver)` in +> [`sqlitestorage.cpp`][file-cpp-sqlite] to read from the new column and store +> it in the migration object. You may need to convert from SQLite's looser +> types. +> +> Modify `PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver)` in +> [`postgresqlstorage.cpp`][file-cpp-postgres] to write to the new column from +> the data in the migration object. + +7. **Update any affected queries in storage backends +[`postgresqlstorage.cpp`][file-cpp-postgres] and +[`sqlitestorage.cpp`][file-cpp-sqlite], and any related synchronized +`src/common` classes.** + +> *Example: modifying the `ircserver` table to add column `test`* +> +> Update `network.h` to add new column to Server structure: +> ```diff +> QString proxyPass; // Existing code +> + Typename test; // New column 'test' +> [...] +> - Server() : port(6667), ..., proxyPort(8080) {} +> + Server() : port(6667), ..., proxyPort(8080), test("defaultValue") {} +> ``` +> +> Modify reading data in `[PostgreSql/Sqlite]Storage::networks(...)`: +> ```diff +> server.proxyPass = serversQuery.value(10).toString(); // Existing code +> + server.test = serversQuery.value(11).toType(); // New column 'test' +> servers << server; // Existing code +> ``` +> +> Modify writing data in `[PostgreSql/Sqlite]Storage::bindServerInfo(...)`: +> ```diff +> query.bindValue(":proxypass", server.proxyPass); // Existing code +> + query.bindValue(":test", server.test); // New column 'test' +> ``` + +8. **If protocol changed (*add a setting, etc*), add a new `Feature` flag** + +Newer clients need to detect when they're on an older core to disable the +feature. Use the `Feature` enum in [`quassel.h`][file-h-quassel]. + +In client-side code, test for a feature with... + +```cpp +if (Client::isCoreFeatureEnabled(Quassel::Feature::FeatureName)) { ... } +``` + +9. **Test everything! Upgrade, migrate, new setups, new client/old core, +old client/new core, etc.** + +More specifically, you should try the following combinations, especially if you +change the protocol. Check if any data or settings get lost or corrupted, or +if anything unusual shows up in the log. Restart the Quassel core and client, +to check that data persists. + +--- + +**Linux/macOS** + +*Fresh configuration (initialize the database and settings from scratch)* + +Test conditions | Result | Remarks +-----------------|---------|------------- +SQLite, new core, new client | ❓ **TODO** | +SQLite, new core, old client | ❓ **TODO** | +SQLite, old core, new client | ❓ **TODO** | +SQLite, new monolithic | ❓ **TODO** | +Postgres, new core, new client | ❓ **TODO** | +Postgres, new core, old client | ❓ **TODO** | +Postgres, old core, new client | ❓ **TODO** | + +*Migration (set up SQLite, then `--select-backend PostgreSQL`)* + +Test conditions | Result | Remarks +-----------------|---------|------------- +SQLite → Postgres, new core, new client | ❓ **TODO** | + +*Upgrading existing (set up a copy from `master`, then upgrade to your branch)* + +Test conditions | Result | Remarks +-----------------|---------|------------- +SQLite, old → new core | ❓ **TODO** | +SQLite, old monolithic → new monolithic | ❓ **TODO** | +Postgres, old → new core | ❓ **TODO** | + +**Windows** + +*Fresh configuration (initialize the database and settings from scratch)* + +Test conditions | Result | Remarks +-----------------|---------|------------- +SQLite, new core, new client | ❓ **TODO** | +SQLite, new core, old client | ❓ **TODO** | +SQLite, old core, new client | ❓ **TODO** | +SQLite, new monolithic | ❓ **TODO** | + +*Upgrading existing (set up a copy from `master`, then upgrade to your branch)* + +Test conditions | Result | Remarks +-----------------|---------|------------- +SQLite, old → new core | ❓ **TODO** | +SQLite, old monolithic → new monolithic | ❓ **TODO** | + +*If someone figures out how Postgres runs on Windows with Quassel, please +update this...* + +--- + +Yes, this looks excessive, and maybe it is. But it's easy to overlook +some minor typo that breaks the client/core for a certain configuration. +People may get unhappy and rioting might happen in the streets. And we don't +want that, do we? + +Thank you for reading this guide and good luck with your changes! + +[postgres-home]: https://www.postgresql.org/ +[sqlite-home]: https://sqlite.org/ +[docs-wiki-db-manage]: https://bugs.quassel-irc.org/projects/quassel-irc/wiki/Database_Management +[file-cpp-abstract]: ../abstractsqlstorage.cpp +[file-h-abstract]: ../abstractsqlstorage.h +[file-cpp-postgres]: ../postgresqlstorage.cpp +[file-cpp-sqlite]: ../sqlitestorage.cpp +[file-sh-updateresource]: updateSQLResource.sh +[file-sh-upgradeschema]: upgradeSchema.sh +[file-qrc-sql]: ../sql.qrc +[file-h-quassel]: ../../common/quassel.h + diff --git a/src/core/SQL/updateSQLResource.sh b/src/core/SQL/updateSQLResource.sh index 95232728..0829364c 100755 --- a/src/core/SQL/updateSQLResource.sh +++ b/src/core/SQL/updateSQLResource.sh @@ -7,9 +7,9 @@ # folder. It'll regenerate 'sql.qrc' for you. If it fails, you can manually # edit the file, too; just follow the pattern already in there. # -# NOTE: In most cases you must upgrade the database schema version to make a +# NOTE: In many cases you must upgrade the database schema version to make a # change, which includes adding upgrade scripts and modifying existing setup -# queries. See 'upgradeSchema.sh' for details. +# queries. See 'README.md' for details. set -u # Bash - fail on undefined variable diff --git a/src/core/SQL/upgradeSchema.sh b/src/core/SQL/upgradeSchema.sh index 0b37a2da..95f2cc58 100755 --- a/src/core/SQL/upgradeSchema.sh +++ b/src/core/SQL/upgradeSchema.sh @@ -3,141 +3,14 @@ # Licensed under GNU General Public License version 2, or (at your option) # version 3. # -# "One does not simply 'upgrade the schema'..." -# -# When changing Quassel's database schema, you need to follow several steps to -# handle all cases (upgrade, Postgres migration, etc). Some of these steps may -# not apply to all schema upgrades. -# -# 1. Run this script on -both- the PostgreSQL and SQLite directory -# -# Make sure you're on the Git branch you want to modify -# > ./upgradeSchema.sh "PostgreSQL" -# > ./upgradeSchema.sh "SQLite" -# -# 2. Modify queries and setup scripts to handle your change -# -# [Example] Modifying the 'ircserver' table to add column 'test' -# Modify all query/setup .sql files that touch the 'ircserver' table for -# -both- PostgreSQL and SQLite. -# -# 3. Create upgrade scripts for -both- PostgreSQL and SQLite -# -# They should go in the newest "[...]/version/##" folders that were created by -# running this script. Don't modify the files in lower-numbered version -# folders. -# -# [Example] Modifying the 'ircserver' table to add column 'test' -# Add the file 'upgrade_000_alter_ircserver_add_test.sql' with contents: -# > ALTER TABLE ircserver -# > ADD COLUMN test [additional column-specific details] -# -# 4. Update the pair of migration scripts for moving from SQLite to PostgreSQL -# -# [Example] Modifying the 'ircserver' table to add column 'test' -# > Modify 'SQLite/migrate_read_ircserver.sql' to select from new column -# > Modify 'PostgreSQL/migrate_write_ircserver.sql' to insert to new column -# -# 5. Update the SQL resource file; re-run CMake if needed -# -# The easy way: run "updateSQLResource.sh" in this directory. -# -# The manual way: -# Add the new SQL queries to 'src/core/sql.qrc', update all changed existing -# files. -# -# [Example] Modifying the 'ircserver' table to add column 'test' -# > Add the new upgrade scripts... -# ./SQL/SQLite/version/19/upgrade_000_alter_ircserver_add_test.sql -# ./SQL/PostgreSQL/version/18/upgrade_000_alter_ircserver_add_test.sql -# > Add/update non-upgrade scripts, if any... -# ./SQL/SQLite/update_buffer_persistent_channel.sql -# ./SQL/PostgreSQL/update_buffer_persistent_channel.sql -# (etc) -# -# 6. Update the migration logic in 'src/core/abstractsqlstorage.h', and the -# storage backends 'postgresqlstorage.cpp' and 'sqlitestorage.cpp' -# -# [Example] Modifying the 'ircserver' table to add column 'test' -# > Modify struct 'IrcServerMO' in 'abstractsqlstorage.h', adding an entry for -# 'test' of the appropriate data-type. -# > Modify 'SqliteMigrationReader::readMo(IrcServerMO &ircserver)' in -# 'sqlitestorage.cpp' to read from the new column and store it in the -# migration object. You may need to convert from SQLite's looser types. -# > Modify 'PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver)' -# in 'postgresqlstorage.cpp' to write to the new column from the data in the -# migration object. -# -# 7. Update any affected queries in storage backends 'postgresqlstorage.cpp' -# and 'sqlitestorage.cpp', and any related synchronized 'src/common' classes. -# -# [Example] Modifying the 'ircserver' table to add column 'test' -# > Update 'network.h' to add new column to Server structure -# QString proxyPass; // Existing code -# Typename test; // New column 'test' -# [...] -# Server() : port(6667), ..., proxyPort(8080), test("defaultValue") {} -# > Modify reading data in ____Storage::networks(...) -# server.proxyPass = serversQuery.value(10).toString(); // Existing code -# server.test = serversQuery.value(11).toType(); // New column 'test' -# servers << server; // Existing code -# > Modify writing data in ____Storage::bindServerInfo(...) -# query.bindValue(":proxypass", server.proxyPass); // Existing code -# query.bindValue(":test", server.test); // New column 'test' -# -# 8. If protocol changed (add a setting, etc), add a new "Feature" flag -# -# Newer clients need to detect when they're on an older core to disable the -# feature. Use 'enum Feature' in 'quassel.h'. In client-side code, test with -# 'if (Client::isCoreFeatureEnabled(Quassel::Feature::FeatureName)) { ... }' -# -# 9. Test everything! Upgrade, migrate, new setups, new client/old core, -# old client/new core, etc. -# -# More specifically, you likely should try the following combinations, -# especially if you change the protocol. Check if any data or settings get -# lost or corrupted, or if anything unusual shows up in the log. -# -# [Mac/Linux] -# Fresh configuration (reset the database and settings) -# > SQLite -# > New core, new client -# > New core, old client -# > Old core, new client -# > New monolithic (combined core/client build) -# > Postgres -# > New core, new client -# > New core, old client -# > Old core, new client -# Migration (set up SQLite, then --select-backend PostgreSQL) -# > SQLite -> Postgres, new core, new client -# Upgrading existing (set up a copy from 'master', then build your branch) -# > SQLite -# > Old -> new core -# > Old monolithic -> new monolithic -# > Postgres -# > Old -> new core -# -# [Windows] -# Fresh configuration (reset the database and settings) -# > SQLite -# > New core, new client -# > New core, old client -# > Old core, new client -# > New monolithic (combined core/client build) -# Upgrading existing (set up a copy from 'master', then build your branch) -# > SQLite -# > Old -> new core -# > Old monolithic -> new monolithic -# (If someone figures out how Postgres runs on Windows with Quassel, please -# update this comment) -# -# Yes, this looks excessive, and maybe it is. But it's easy to overlook -# some minor typo that breaks the client/core for a certain configuration. -# People may get unhappy and rioting might happen in the streets. And we don't -# want that, do we? -# -# Thank you for reading this guide and good luck with your changes! +# Call this script whenever you need to add a new database schema version. +# You can also simply create a new folder in '[SQLite/PostgreSQL]/version/##', +# where '##' represents your new version number, incremented by one from +# whatever the current latest version is (e.g. 21 -> 22). +# +# NOTE: If you upgrade the database schema version, you must add upgrade +# scripts and modify the existing setup queries, then regenerate sql.qrc with +# 'updateSQLResource'. See 'README.md' for details. TARGET_DIR="$1" # If not specified, assume current directory -- 2.20.1