src/core/SQL/README.md

   1 # Database schema management in Quassel IRC
   2
   3 ## Introduction
   4
   5 Quassel IRC supports two database backends for storing chat history, user
   6 sessions, etc:
   7
   8 * [PostgreSQL][postgres-home], in the [`PostgreSQL`](PostgreSQL/) folder
   9 * [SQLite][sqlite-home], in the [`SQLite`](SQLite/) folder
  10
  11 For the most part, Quassel strives to have consistency between the two
  12 backends, offering the same user experience and similar `.sql` query files,
  13 though there are times when one database supports features that another does
  14 not.
  15
  16 All of the current schema version queries are stored at the top level of the
  17 backend subdirectory, with schema upgrade queries stored in
  18 `[backend]/version/##` folders.
  19
  20 At compile time, Qt reads [`sql.qrc`][file-qrc-sql] to know which queries to
  21 include.  This list can be automatically updated on Linux and macOS using
  22 [`updateSQLResource.sh`][file-sh-updateresource]; for now, on Windows you'll
  23 need to install some form of Bash.
  24
  25 ## Managing the database outside of Quassel
  26
  27 Whenever possible, it's recommended to use Quassel itself to make changes, such
  28 as deleting chats or changing user passwords.
  29
  30 However, some tasks aren't yet possible in Quassel, and you should
  31 [read the documentation on managing the database][docs-wiki-db-manage] if the
  32 need arises.
  33
  34 ## Making changes to the database
  35 ### Changes to existing queries, no new/moved files
  36
  37 If you're only modifying existing queries in a way that does **not** require
  38 any schema changes (*e.g. `ALTER TABLE`, `CREATE TABLE`, `CREATE INDEX`
  39 statements*), you can just modify the `.sql` files in the appropriate backends.
  40
  41 However, any database schema changes must fulfill a number of requirements.
  42
  43 ### New queries, schema changes, etc
  44
  45 > One does not simply 'upgrade the schema'...
  46
  47 When changing Quassel's database schema, you need to follow several steps to
  48 handle all cases (*upgrade, Postgres migration, etc*).  Some of these steps may
  49 not apply to all schema upgrades.
  50
  51 1.  **Run [`upgradeSchema.sh`][file-sh-upgradeschema] script on *both*
  52 PostgreSQL and SQLite directories**
  53
  54 Make sure you're on the Git branch you want to modify
  55
  56 *Example:*
  57 ```sh
  58 ./upgradeSchema.sh "PostgreSQL"
  59 ./upgradeSchema.sh "SQLite"
  60 ```
  61
  62 Or, you can manually create new `[backend]/version/##` folders for both
  63 `PostgreSQL` and `SQLite`.  Pick the next higher number from the largest
  64 version number for each (*the exact version numbers will usually differ; that's
  65 fine*).
  66
  67 2.  **Modify queries and setup scripts to handle your change**
  68
  69 The specifics depend on your change; in general, you'll want to modify any
  70 query files that select from or insert to a modified table.  Then, modify the
  71 `setup_###_[...].sql` files to include your changes on new database installs.
  72
  73 > *Example: modifying the `ircserver` table to add column `test`*
  74 >
  75 > Modify all query/setup `.sql` files that touch the `ircserver` table for
  76 > *both* `PostgreSQL` and `SQLite`.
  77
  78 3.  **Create upgrade scripts for *both* PostgreSQL and SQLite**
  79
  80 These should go in the newest `[backend]/version/##` folders that were created
  81 in Step 1.
  82
  83 Outside of special circumstances, do **not** modify the files in lower-numbered
  84 version folders.  Existing Quassel cores have already run those statements and
  85 will not run them if changed.
  86
  87 > *Example: modifying the `ircserver` table to add column `test`*
  88 >
  89 > Add the file `upgrade_000_alter_ircserver_add_test.sql` with contents:
  90 >
  91 > ```sql
  92 > ALTER TABLE ircserver
  93 > ADD COLUMN test [additional column-specific details]
  94 > ```
  95
  96 4.  **Update the pair of migration scripts for moving from SQLite to
  97 PostgreSQL**
  98
  99 For any table changes, you'll need to update the relevant
 100 `SQLite/migrate_read_[table].sql` to read the existing data, and the
 101 `PostgreSQL/migrate_write_[table].sql` to insert this data.
 102
 103 > *Example: modifying the `ircserver` table to add column `test`*
 104 >
 105 > Modify `SQLite/migrate_read_ircserver.sql` to select from new column
 106 >
 107 > Modify `PostgreSQL/migrate_write_ircserver.sql` to insert to new column
 108
 109 5.  **Update the SQL resource file; re-run CMake if needed**
 110
 111 **The easy way:**
 112
 113 Run [`updateSQLResource.sh`][file-sh-updateresource] in this directory.
 114
 115 **The manual way:**
 116
 117 Add the new SQL queries to [`src/core/sql.qrc`][file-qrc-sql], update all
 118 changed existing files.
 119
 120 > *Example: modifying the `ircserver` table to add column `test`*
 121 >
 122 > Add the new upgrade scripts:
 123 > ```diff
 124 > +  <file>./SQL/SQLite/version/19/upgrade_000_alter_ircserver_add_test.sql</file>
 125 > +  <file>./SQL/PostgreSQL/version/18/upgrade_000_alter_ircserver_add_test.sql</file>
 126 > ```
 127 >
 128 > Add/update non-upgrade scripts, if any:
 129 > ```diff
 130 > +  <file>./SQL/SQLite/update_buffer_persistent_channel.sql</file>
 131 > +  <file>./SQL/PostgreSQL/update_buffer_persistent_channel.sql</file>
 132 > +  [etc]
 133 > ```
 134
 135 6.  **Update the migration logic in
 136 [`src/core/abstractsqlstorage.cpp`][file-cpp-abstract], and the storage
 137 backends [`postgresqlstorage.cpp`][file-cpp-postgres] and
 138 [`sqlitestorage.cpp`][file-cpp-sqlite]**
 139
 140 Make sure to read the data from SQLite in the right types and order, then write
 141 it to PostgreSQL also with the right types and order.  SQLite and PostgreSQL
 142 may use different database representations of certain types.
 143
 144 > *Example: modifying the `ircserver` table to add column `test`*
 145 >
 146 > Modify struct `IrcServerMO` in [`abstractsqlstorage.h`][file-h-abstract],
 147 > adding an entry for `test` of the appropriate data-type.
 148 >
 149 > Modify `SqliteMigrationReader::readMo(IrcServerMO &ircserver)` in
 150 > [`sqlitestorage.cpp`][file-cpp-sqlite] to read from the new column and store
 151 > it in the migration object.  You may need to convert from SQLite's looser
 152 > types.
 153 >
 154 > Modify `PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver)` in
 155 > [`postgresqlstorage.cpp`][file-cpp-postgres] to write to the new column from
 156 > the data in the migration object.
 157
 158 7.  **Update any affected queries in storage backends
 159 [`postgresqlstorage.cpp`][file-cpp-postgres] and
 160 [`sqlitestorage.cpp`][file-cpp-sqlite], and any related synchronized
 161 `src/common` classes.**
 162
 163 > *Example: modifying the `ircserver` table to add column `test`*
 164 >
 165 > Update `network.h` to add new column to Server structure:
 166 > ```diff
 167 >    QString proxyPass;                                    // Existing code
 168 > +  Typename test;                                        // New column 'test'
 169 >    [...]
 170 > -  Server() : port(6667), ..., proxyPort(8080) {}
 171 > +  Server() : port(6667), ..., proxyPort(8080), test("defaultValue") {}
 172 > ```
 173 >
 174 > Modify reading data in `[PostgreSql/Sqlite]Storage::networks(...)`:
 175 > ```diff
 176 >    server.proxyPass = serversQuery.value(10).toString(); // Existing code
 177 > +  server.test = serversQuery.value(11).toType();        // New column 'test'
 178 >    servers << server;                                    // Existing code
 179 > ```
 180 >
 181 > Modify writing data in `[PostgreSql/Sqlite]Storage::bindServerInfo(...)`:
 182 > ```diff
 183 >    query.bindValue(":proxypass", server.proxyPass);      // Existing code
 184 > +  query.bindValue(":test", server.test);                // New column 'test'
 185 > ```
 186
 187 8.  **If protocol changed (*add a setting, etc*), add a new `Feature` flag**
 188
 189 Newer clients need to detect when they're on an older core to disable the
 190 feature.  Use the `Feature` enum in [`quassel.h`][file-h-quassel].
 191
 192 In client-side code, test for a feature with...
 193
 194 ```cpp
 195 if (Client::isCoreFeatureEnabled(Quassel::Feature::FeatureName)) { ... }
 196 ```
 197
 198 9.  **Test everything!  Upgrade, migrate, new setups, new client/old core,
 199 old client/new core, etc.**
 200
 201 More specifically, you should try the following combinations, especially if you
 202 change the protocol.  Check if any data or settings get lost or corrupted, or
 203 if anything unusual shows up in the log.  Restart the Quassel core and client,
 204 to check that data persists.
 205
 206 ---
 207
 208 **Linux/macOS**
 209
 210 *Fresh configuration (initialize the database and settings from scratch)*
 211
 212 Test conditions  | Result  | Remarks
 213 -----------------|---------|-------------
 214 SQLite, new core, new client | ❓ **TODO** |
 215 SQLite, new core, old client | ❓ **TODO** |
 216 SQLite, old core, new client | ❓ **TODO** |
 217 SQLite, new monolithic | ❓ **TODO** |
 218 Postgres, new core, new client | ❓ **TODO** |
 219 Postgres, new core, old client | ❓ **TODO** |
 220 Postgres, old core, new client | ❓ **TODO** |
 221
 222 *Migration (set up SQLite, then `--select-backend PostgreSQL`)*
 223
 224 Test conditions  | Result  | Remarks
 225 -----------------|---------|-------------
 226 SQLite → Postgres, new core, new client | ❓ **TODO** |
 227
 228 *Upgrading existing (set up a copy from `master`, then upgrade to your branch)*
 229
 230 Test conditions  | Result  | Remarks
 231 -----------------|---------|-------------
 232 SQLite, old → new core | ❓ **TODO** |
 233 SQLite, old monolithic → new monolithic | ❓ **TODO** |
 234 Postgres, old → new core | ❓ **TODO** |
 235
 236 **Windows**
 237
 238 *Fresh configuration (initialize the database and settings from scratch)*
 239
 240 Test conditions  | Result  | Remarks
 241 -----------------|---------|-------------
 242 SQLite, new core, new client | ❓ **TODO** |
 243 SQLite, new core, old client | ❓ **TODO** |
 244 SQLite, old core, new client | ❓ **TODO** |
 245 SQLite, new monolithic | ❓ **TODO** |
 246
 247 *Upgrading existing (set up a copy from `master`, then upgrade to your branch)*
 248
 249 Test conditions  | Result  | Remarks
 250 -----------------|---------|-------------
 251 SQLite, old → new core | ❓ **TODO** |
 252 SQLite, old monolithic → new monolithic | ❓ **TODO** |
 253
 254 *If someone figures out how Postgres runs on Windows with Quassel, please
 255 update this...*
 256
 257 ---
 258
 259 Yes, this looks excessive, and maybe it is.  But it's easy to overlook
 260 some minor typo that breaks the client/core for a certain configuration.
 261 People may get unhappy and rioting might happen in the streets.  And we don't
 262 want that, do we?
 263
 264 Thank you for reading this guide and good luck with your changes!
 265
 266 [postgres-home]: https://www.postgresql.org/
 267 [sqlite-home]: https://sqlite.org/
 268 [docs-wiki-db-manage]: https://bugs.quassel-irc.org/projects/quassel-irc/wiki/Database_Management
 269 [file-cpp-abstract]: ../abstractsqlstorage.cpp
 270 [file-h-abstract]: ../abstractsqlstorage.h
 271 [file-cpp-postgres]: ../postgresqlstorage.cpp
 272 [file-cpp-sqlite]: ../sqlitestorage.cpp
 273 [file-sh-updateresource]: updateSQLResource.sh
 274 [file-sh-upgradeschema]: upgradeSchema.sh
 275 [file-qrc-sql]: ../sql.qrc
 276 [file-h-quassel]: ../../common/quassel.h
 277