2 # Copyright (C) 2005-2016 by the Quassel Project - devel@quassel-irc.org
3 # Licensed under GNU General Public License version 2, or (at your option)
6 # "One does not simply 'upgrade the schema'..."
8 # When changing Quassel's database schema, you need to follow several steps to
9 # handle all cases (upgrade, Postgres migration, etc). Some of these steps may
10 # not apply to all schema upgrades.
12 # 1. Run this script on -both- the PostgreSQL and SQLite directory
14 # Make sure you're on the Git branch you want to modify
15 # > ./upgradeSchema.sh "PostgreSQL"
16 # > ./upgradeSchema.sh "SQLite"
18 # 2. Modify queries and setup scripts to handle your change
20 # [Example] Modifying the 'ircserver' table to add column 'test'
21 # Modify all query/setup .sql files that touch the 'ircserver' table for
22 # -both- PostgreSQL and SQLite.
24 # 3. Create upgrade scripts for -both- PostgreSQL and SQLite
26 # They should go in the newest "[...]/version/##" folders that were created by
27 # running this script. Don't modify the files in lower-numbered version
30 # [Example] Modifying the 'ircserver' table to add column 'test'
31 # Add the file 'upgrade_000_alter_ircserver_add_test.sql' with contents:
32 # > ALTER TABLE ircserver
33 # > ADD COLUMN test [additional column-specific details]
35 # 4. Update the pair of migration scripts for moving from SQLite to PostgreSQL
37 # [Example] Modifying the 'ircserver' table to add column 'test'
38 # > Modify 'SQLite/migrate_read_ircserver.sql' to select from new column
39 # > Modify 'PostgreSQL/migrate_write_ircserver.sql' to insert to new column
41 # 5. Update the SQL resource file; re-run CMake if needed
43 # The easy way: run "updateSQLResource.sh" in this directory.
46 # Add the new SQL queries to 'src/core/sql.qrc', update all changed existing
49 # [Example] Modifying the 'ircserver' table to add column 'test'
50 # > Add the new upgrade scripts...
51 # <file>./SQL/SQLite/version/19/upgrade_000_alter_ircserver_add_test.sql</file>
52 # <file>./SQL/PostgreSQL/version/18/upgrade_000_alter_ircserver_add_test.sql</file>
53 # > Add/update non-upgrade scripts, if any...
54 # <file>./SQL/SQLite/update_buffer_persistent_channel.sql</file>
55 # <file>./SQL/PostgreSQL/update_buffer_persistent_channel.sql</file>
58 # 6. Update the migration logic in 'src/core/abstractsqlstorage.h', and the
59 # storage backends 'postgresqlstorage.cpp' and 'sqlitestorage.cpp'
61 # [Example] Modifying the 'ircserver' table to add column 'test'
62 # > Modify struct 'IrcServerMO' in 'abstractsqlstorage.h', adding an entry for
63 # 'test' of the appropriate data-type.
64 # > Modify 'SqliteMigrationReader::readMo(IrcServerMO &ircserver)' in
65 # 'sqlitestorage.cpp' to read from the new column and store it in the
66 # migration object. You may need to convert from SQLite's looser types.
67 # > Modify 'PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver)'
68 # in 'postgresqlstorage.cpp' to write to the new column from the data in the
71 # 7. Update any affected queries in storage backends 'postgresqlstorage.cpp'
72 # and 'sqlitestorage.cpp', and any related synchronized 'src/common' classes.
74 # [Example] Modifying the 'ircserver' table to add column 'test'
75 # > Update 'network.h' to add new column to Server structure
76 # QString proxyPass; // Existing code
77 # Typename test; // New column 'test'
79 # Server() : port(6667), ..., proxyPort(8080), test("defaultValue") {}
80 # > Modify reading data in ____Storage::networks(...)
81 # server.proxyPass = serversQuery.value(10).toString(); // Existing code
82 # server.test = serversQuery.value(11).toType(); // New column 'test'
83 # servers << server; // Existing code
84 # > Modify writing data in ____Storage::bindServerInfo(...)
85 # query.bindValue(":proxypass", server.proxyPass); // Existing code
86 # query.bindValue(":test", server.test); // New column 'test'
88 # 8. If protocol changed (add a setting, etc), add a new "Feature" flag
90 # Newer clients need to detect when they're on an older core to disable the
91 # feature. Use 'enum Feature' in 'quassel.h'. In client-side code, test with
92 # 'if (Client::coreFeatures() & Quassel::FeatureName) { ... }'
94 # 9. Test everything! Upgrade, migrate, new setups, new client/old core,
95 # old client/new core, etc.
97 # More specifically, you likely should try the following combinations,
98 # especially if you change the protocol. Check if any data or settings get
99 # lost or corrupted, or if anything unusual shows up in the log.
102 # Fresh configuration (reset the database and settings)
104 # > New core, new client
105 # > New core, old client
106 # > Old core, new client
107 # > New monolithic (combined core/client build)
109 # > New core, new client
110 # > New core, old client
111 # > Old core, new client
112 # Migration (set up SQLite, then --select-backend PostgreSQL)
113 # > SQLite -> Postgres, new core, new client
114 # Upgrading existing (set up a copy from 'master', then build your branch)
117 # > Old monolithic -> new monolithic
122 # Fresh configuration (reset the database and settings)
124 # > New core, new client
125 # > New core, old client
126 # > Old core, new client
127 # > New monolithic (combined core/client build)
128 # Upgrading existing (set up a copy from 'master', then build your branch)
131 # > Old monolithic -> new monolithic
132 # (If someone figures out how Postgres runs on Windows with Quassel, please
133 # update this comment)
135 # Yes, this looks excessive, and maybe it is. But it's easy to overlook
136 # some minor typo that breaks the client/core for a certain configuration.
137 # People may get unhappy and rioting might happen in the streets. And we don't
140 # Thank you for reading this guide and good luck with your changes!
143 # If not specified, assume current directory
144 if [ ! "$TARGET_DIR" ]; then
148 if [[ ! -d "$TARGET_DIR" ]]; then
149 echo "No such directory '$TARGET_DIR'" >&2
153 # Find out the name of the target directory to offer some guidance later.
154 TARGET_DB_NAME=$(basename "$TARGET_DIR")
156 # Upgrade scripts are stored in the 'version' subdirectory, e.g.
157 # 'SQL/[database]/version/##'.
158 cd "$TARGET_DIR/version"
160 # Grab the current schema version
161 CURRENT_VERSION=$(ls | sort -n | tail -n1)
163 if [ ! $CURRENT_VERSION ]; then
164 echo "No previous schema found to upgrade from" >&2
169 ((NEW_VERSION=$CURRENT_VERSION + 1))
171 # Create the new schema directory.
173 # Git doesn't track empty folders, no need for 'git add "$NEW_VERSION"'.
175 echo "New schema version '$TARGET_DB_NAME/version/$NEW_VERSION' created." >&2
176 echo "Create any needed 'upgrade_[...].sql' scripts in this folder." >&2
178 # Don't move any files over. Schema version upgrade scripts are now stored
179 # independently of the main SQL files in order to make the repository history
180 # more useful and easier to work with.
182 # Granted, this script doesn't do anything one couldn't easily manually do.
183 # I'd argue that's a good thing. Though as this script offers documentation
184 # and guidance for contributors new to the database schema system as well as
185 # helping migrate those used to the older method, it seems worthwhile keeping.