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 8. **Test everything! Upgrade, migrate, new setups, new client/old core,
172 old client/new core, etc.**
174 More specifically, you should try the following combinations, especially if you
175 change the protocol. Check if any data or settings get lost or corrupted, or
176 if anything unusual shows up in the log. Restart the Quassel core and client,
177 to check that data persists.
183 *Fresh configuration (initialize the database and settings from scratch)*
185 Test conditions | Result | Remarks
186 -----------------|---------|-------------
187 SQLite, new core, new client | ❓ **TODO** |
188 SQLite, new core, old client | ❓ **TODO** |
189 SQLite, old core, new client | ❓ **TODO** |
190 SQLite, new monolithic | ❓ **TODO** |
191 Postgres, new core, new client | ❓ **TODO** |
192 Postgres, new core, old client | ❓ **TODO** |
193 Postgres, old core, new client | ❓ **TODO** |
195 *Migration (set up SQLite, then `--select-backend PostgreSQL`)*
197 Test conditions | Result | Remarks
198 -----------------|---------|-------------
199 SQLite → Postgres, new core, new client | ❓ **TODO** |
201 *Upgrading existing (set up a copy from `master`, then upgrade to your branch)*
203 Test conditions | Result | Remarks
204 -----------------|---------|-------------
205 SQLite, old → new core | ❓ **TODO** |
206 SQLite, old monolithic → new monolithic | ❓ **TODO** |
207 Postgres, old → new core | ❓ **TODO** |
211 *Fresh configuration (initialize the database and settings from scratch)*
213 Test conditions | Result | Remarks
214 -----------------|---------|-------------
215 SQLite, new core, new client | ❓ **TODO** |
216 SQLite, new core, old client | ❓ **TODO** |
217 SQLite, old core, new client | ❓ **TODO** |
218 SQLite, new monolithic | ❓ **TODO** |
220 *Upgrading existing (set up a copy from `master`, then upgrade to your branch)*
222 Test conditions | Result | Remarks
223 -----------------|---------|-------------
224 SQLite, old → new core | ❓ **TODO** |
225 SQLite, old monolithic → new monolithic | ❓ **TODO** |
227 *If someone figures out how Postgres runs on Windows with Quassel, please
232 Yes, this looks excessive, and maybe it is. But it's easy to overlook
233 some minor typo that breaks the client/core for a certain configuration.
234 People may get unhappy and rioting might happen in the streets. And we don't
237 Thank you for reading this guide and good luck with your changes!
239 [postgres-home]: https://www.postgresql.org/
240 [sqlite-home]: https://sqlite.org/
241 [docs-wiki-db-manage]: https://bugs.quassel-irc.org/projects/quassel-irc/wiki/Database_Management
242 [file-cpp-abstract]: ../abstractsqlstorage.cpp
243 [file-h-abstract]: ../abstractsqlstorage.h
244 [file-cpp-postgres]: ../postgresqlstorage.cpp
245 [file-cpp-sqlite]: ../sqlitestorage.cpp
246 [file-sh-upgradeschema]: upgradeSchema.sh
247 [file-h-quassel]: ../../common/quassel.h