1 # Database schema management in Quassel IRC
5 Quassel IRC supports two database backends for storing chat history, user
8 * [PostgreSQL][postgres-home], in the [`PostgreSQL`](PostgreSQL/) folder
9 * [SQLite][sqlite-home], in the [`SQLite`](SQLite/) folder
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
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.
20 At compile time, the build system generates and reads a Qt resource file to
21 know which queries to include. For past Quassel contributors, this replaces
22 the classic `sql.qrc` file and `updateSQLResource.sh` script.
24 ## Managing the database outside of Quassel
26 Whenever possible, it's recommended to use Quassel itself to make changes, such
27 as deleting chats or changing user passwords.
29 However, some tasks aren't yet possible in Quassel, and you should
30 [read the documentation on managing the database][docs-wiki-db-manage] if the
33 ## Making changes to the database
34 ### Changes to existing queries, no new/moved files
36 If you're only modifying existing queries in a way that does **not** require
37 any schema changes (*e.g. `ALTER TABLE`, `CREATE TABLE`, `CREATE INDEX`
38 statements*), you can just modify the `.sql` files in the appropriate backends.
40 However, any database schema changes must fulfill a number of requirements.
42 ### New queries, schema changes, etc
44 > One does not simply 'upgrade the schema'...
46 When changing Quassel's database schema, you need to follow several steps to
47 handle all cases (*upgrade, Postgres migration, etc*). Some of these steps may
48 not apply to all schema upgrades.
50 1. **Run [`upgradeSchema.sh`][file-sh-upgradeschema] script on *both*
51 PostgreSQL and SQLite directories**
53 Make sure you're on the Git branch you want to modify
57 ./upgradeSchema.sh "PostgreSQL"
58 ./upgradeSchema.sh "SQLite"
61 Or, you can manually create new `[backend]/version/##` folders for both
62 `PostgreSQL` and `SQLite`. Pick the next higher number from the largest
63 version number for each (*the exact version numbers will usually differ; that's
66 2. **Modify queries and setup scripts to handle your change**
68 The specifics depend on your change; in general, you'll want to modify any
69 query files that select from or insert to a modified table. Then, modify the
70 `setup_###_[...].sql` files to include your changes on new database installs.
72 > *Example: modifying the `ircserver` table to add column `test`*
74 > Modify all query/setup `.sql` files that touch the `ircserver` table for
75 > *both* `PostgreSQL` and `SQLite`.
77 3. **Create upgrade scripts for *both* PostgreSQL and SQLite**
79 These should go in the newest `[backend]/version/##` folders that were created
82 Outside of special circumstances, do **not** modify the files in lower-numbered
83 version folders. Existing Quassel cores have already run those statements and
84 will not run them if changed.
86 > *Example: modifying the `ircserver` table to add column `test`*
88 > Add the file `upgrade_000_alter_ircserver_add_test.sql` with contents:
91 > ALTER TABLE ircserver
92 > ADD COLUMN test [additional column-specific details]
95 4. **Update the pair of migration scripts for moving from SQLite to
98 For any table changes, you'll need to update the relevant
99 `SQLite/migrate_read_[table].sql` to read the existing data, and the
100 `PostgreSQL/migrate_write_[table].sql` to insert this data.
102 > *Example: modifying the `ircserver` table to add column `test`*
104 > Modify `SQLite/migrate_read_ircserver.sql` to select from new column
106 > Modify `PostgreSQL/migrate_write_ircserver.sql` to insert to new column
108 5. **Update the migration logic in
109 [`src/core/abstractsqlstorage.cpp`][file-cpp-abstract], and the storage
110 backends [`postgresqlstorage.cpp`][file-cpp-postgres] and
111 [`sqlitestorage.cpp`][file-cpp-sqlite]**
113 Make sure to read the data from SQLite in the right types and order, then write
114 it to PostgreSQL also with the right types and order. SQLite and PostgreSQL
115 may use different database representations of certain types.
117 > *Example: modifying the `ircserver` table to add column `test`*
119 > Modify struct `IrcServerMO` in [`abstractsqlstorage.h`][file-h-abstract],
120 > adding an entry for `test` of the appropriate data-type.
122 > Modify `SqliteMigrationReader::readMo(IrcServerMO &ircserver)` in
123 > [`sqlitestorage.cpp`][file-cpp-sqlite] to read from the new column and store
124 > it in the migration object. You may need to convert from SQLite's looser
127 > Modify `PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver)` in
128 > [`postgresqlstorage.cpp`][file-cpp-postgres] to write to the new column from
129 > the data in the migration object.
131 6. **Update any affected queries in storage backends
132 [`postgresqlstorage.cpp`][file-cpp-postgres] and
133 [`sqlitestorage.cpp`][file-cpp-sqlite], and any related synchronized
134 `src/common` classes.**
136 > *Example: modifying the `ircserver` table to add column `test`*
138 > Update `network.h` to add new column to Server structure:
140 > QString proxyPass; // Existing code
141 > + Typename test; // New column 'test'
143 > - Server() : port(6667), ..., proxyPort(8080) {}
144 > + Server() : port(6667), ..., proxyPort(8080), test("defaultValue") {}
147 > Modify reading data in `[PostgreSql/Sqlite]Storage::networks(...)`:
149 > server.proxyPass = serversQuery.value(10).toString(); // Existing code
150 > + server.test = serversQuery.value(11).toType(); // New column 'test'
151 > servers << server; // Existing code
154 > Modify writing data in `[PostgreSql/Sqlite]Storage::bindServerInfo(...)`:
156 > query.bindValue(":proxypass", server.proxyPass); // Existing code
157 > + query.bindValue(":test", server.test); // New column 'test'
160 7. **If protocol changed (*add a setting, etc*), add a new `Feature` flag**
162 Newer clients need to detect when they're on an older core to disable the
163 feature. Use the `Feature` enum in [`quassel.h`][file-h-quassel].
165 In client-side code, test for a feature with...
168 if (Client::isCoreFeatureEnabled(Quassel::Feature::FeatureName)) { ... }
171 Depending on the type of protocol change, you might also need to update
172 [`serializers.cpp`][file-cpp-serializers] and related files.
174 8. **Test everything! Upgrade, migrate, new setups, new client/old core,
175 old client/new core, etc.**
177 More specifically, you should try the following combinations, especially if you
178 change the protocol. Check if any data or settings get lost or corrupted, or
179 if anything unusual shows up in the log. Restart the Quassel core and client,
180 to check that data persists.
186 *Fresh configuration (initialize the database and settings from scratch)*
188 Test conditions | Result | Remarks
189 -----------------|---------|-------------
190 SQLite, new core, new client | ❓ **TODO** |
191 SQLite, new core, old client | ❓ **TODO** |
192 SQLite, old core, new client | ❓ **TODO** |
193 SQLite, new monolithic | ❓ **TODO** |
194 Postgres, new core, new client | ❓ **TODO** |
195 Postgres, new core, old client | ❓ **TODO** |
196 Postgres, old core, new client | ❓ **TODO** |
198 *Migration (set up SQLite, then `--select-backend PostgreSQL`)*
200 Test conditions | Result | Remarks
201 -----------------|---------|-------------
202 SQLite → Postgres, new core, new client | ❓ **TODO** |
204 *Upgrading existing (set up a copy from `master`, then upgrade to your branch)*
206 Test conditions | Result | Remarks
207 -----------------|---------|-------------
208 SQLite, old → new core | ❓ **TODO** |
209 SQLite, old monolithic → new monolithic | ❓ **TODO** |
210 Postgres, old → new core | ❓ **TODO** |
214 *Fresh configuration (initialize the database and settings from scratch)*
216 Test conditions | Result | Remarks
217 -----------------|---------|-------------
218 SQLite, new core, new client | ❓ **TODO** |
219 SQLite, new core, old client | ❓ **TODO** |
220 SQLite, old core, new client | ❓ **TODO** |
221 SQLite, new monolithic | ❓ **TODO** |
223 *Upgrading existing (set up a copy from `master`, then upgrade to your branch)*
225 Test conditions | Result | Remarks
226 -----------------|---------|-------------
227 SQLite, old → new core | ❓ **TODO** |
228 SQLite, old monolithic → new monolithic | ❓ **TODO** |
230 *If someone figures out how Postgres runs on Windows with Quassel, please
235 Yes, this looks excessive, and maybe it is. But it's easy to overlook
236 some minor typo that breaks the client/core for a certain configuration.
237 People may get unhappy and rioting might happen in the streets. And we don't
240 Thank you for reading this guide and good luck with your changes!
242 [postgres-home]: https://www.postgresql.org/
243 [sqlite-home]: https://sqlite.org/
244 [docs-wiki-db-manage]: https://bugs.quassel-irc.org/projects/quassel-irc/wiki/Database_Management
245 [file-cpp-abstract]: ../abstractsqlstorage.cpp
246 [file-h-abstract]: ../abstractsqlstorage.h
247 [file-cpp-postgres]: ../postgresqlstorage.cpp
248 [file-cpp-sqlite]: ../sqlitestorage.cpp
249 [file-sh-upgradeschema]: upgradeSchema.sh
250 [file-h-quassel]: ../../common/quassel.h
251 [file-cpp-serializers]: ../../common/serializers/serializers.cpp