This document describes how we version database schema in GNU Taler and enable migrations.
As Taler evolves, it will be necessary to add new tables, change existing tables, modify indices and make other changes to the database schema. As production systems exist, existing data will need to be migrated to the new schema, and we need to do this in a systematic way. While certain major changes may require radical or manual interventions, we should have a systematic way of dealing with minor or modest modifications to the schema.
We use the “versioning.sql” system to store the current set of patches that have been applied to the database so far in a “_v” schema. This allows us to quickly test which version of the database we are on and which migrations may still need to be applied.
For each component, all tables are placed into a SCHEMA named after the component.
We then use a set of numbered SQL files that create, alter or drop tables and indices (like exchange-0001.sql, exchange-0002.sql, …) to setup the database. However, some setups need additional arguments, such as the number of partitions. Those setups are then NOT performed explicitly, but by creating stored procedures and registering those stored procedures in a general global “master” table to be called from the main setup logic with arguments in a particular order under certain conditions.
When setting up a database, there is no point in incrementally defining ordinary stored procedures that are used at runtime (not the ones to setup the tables we talked about above). Thus, all of the stored procedures used by the runtime system are placed in a file “procedures.sql” which is loaded last. This makes changes to stored procedures particularly easy, as one simply edits “procedures.sql”. Loading “procedures.sql” also does not change “_v”.
A “drop.sql” file is created that DROPs the main SCHEMA of the component and additionally unregisters all patches from the “_v” schema. The drop script is run during tests to fully reset the database.
The exchange uses “exchange_tables” to create the master table mentioned above. In “exchange_tables”, entries are executed in the order of the “table_serial_id”. Each entry has a “name”, which is the name of the affected table (or at least the prefix in the case of partitioned or sharded tables). The “version” field stores which “exchange-XXXX.sql” file setup the respective table entry, but is for now mostly for internal documentation. The “action” defines both the condition under which to run a function. Specifically, actions can be:
The “partitioned” field indicates that this table is partitioned and instructs the functions to create partitions (or shards) for this table.
The “by_range” field indicates if the table is partitioned by range, which prevents automatic generation of partitions as is done if partitioned by hash.
The “finished” field is initially false, but set to TRUE once the respective function has been executed.
The main “do_create_tables” function triggers the unfinished actions registered in the “exchange_tables” table. It is given arguments to control the number of partitions, the use of partitions and (in the future) the use of sharding.
The individual actions use helper functions (“create_partitioned_table”, “comment_partitioned_table” and “comment_partitioned_column”) to facilitate the creation of tables and associated comments. These functions are used so that we can only define the schema or comment once, and have it applied to tables with names and creation syntax that changes slightly if we use shards or partitions.
Some additional logic will be needed to deal nicely with sharding (which is currently not supported), as with sharing we will need to detach shards, migrate shards, and re-attach shards. So this will require additional stored procedures to support operations on shards.
The merchant does not (yet) need any type of master table, as we do not (yet) use any kind of sharding or partitioning. There are also no stored procedures being used by the backend. Hence, it is simply the “versioning.sql”-controlled table creation/alteration sequence (merchant-0001.sql, etc.) and the “drop.sql” to reset everything.
(This should be filled in with results from discussions on mailing lists / personal communication.)