033-database.rst (6943B)
1 DD 33: Database Schema and Versioning 2 ##################################### 3 4 :Design status: Accepted 5 :Implementation status: Implemented 6 :DD shepherd: TBD 7 :Historical contributors: Christian Grothoff 8 :First published: 2022-11-30 9 :Last substantive change: 2022-11-30 10 :Implementation evidence: exchange (2022-11-24), merchant (2024-01-07, 2024-01-10) 11 :Normative references: :ref:`DatabaseVersioning`, :ref:`MerchantDatabaseScheme` 12 13 .. note:: 14 15 This document records the shared versioning design. Current database 16 migration files and stored procedures are authoritative for implementation 17 details. 18 19 Summary 20 ======= 21 22 This document describes how we version database schema in GNU Taler 23 and enable migrations. 24 25 26 Motivation 27 ========== 28 29 As Taler evolves, it will be necessary to add new tables, change existing 30 tables, modify indices and make other changes to the database schema. As 31 production systems exist, existing data will need to be migrated to the new 32 schema, and we need to do this in a systematic way. While certain major 33 changes may require radical or manual interventions, we should have a 34 systematic way of dealing with minor or modest modifications to the schema. 35 36 37 Requirements 38 ============ 39 40 * The approach should be language-independent 41 * Schema migration should be reasonably performant 42 * Schema migration must be usable, and in particular safe to use for 43 operators without significant risk 44 * We need to support key database features we might need to use, 45 such as partitioning or sharding 46 47 48 Proposed Solution 49 ================= 50 51 We use the "versioning.sql" system to store the current set of patches that 52 have been applied to the database so far in a "_v" schema. This allows us to 53 quickly test which version of the database we are on and which migrations may 54 still need to be applied. 55 56 For each component, all tables are placed into a SCHEMA named after the 57 component. 58 59 We then use a set of numbered SQL files that create, alter or drop tables and 60 indices (like exchange-0001.sql, exchange-0002.sql, ...) to setup the 61 database. However, some setups need additional arguments, such as the number 62 of partitions. Those setups are then NOT performed explicitly, but by creating 63 stored procedures and registering those stored procedures in a general global 64 "master" table to be called from the main setup logic with arguments in a 65 particular order under certain conditions. 66 67 When setting up a database, there is no point in incrementally defining 68 ordinary stored procedures that are used at runtime (not the ones to setup the 69 tables we talked about above). Thus, all of the stored procedures used by the 70 runtime system are placed in a file "procedures.sql" which is loaded 71 last. This makes changes to stored procedures particularly easy, as one simply 72 edits "procedures.sql". Loading "procedures.sql" also does not change "_v". 73 74 A "drop.sql" file is created that DROPs the main SCHEMA of the component and 75 additionally unregisters all patches from the "_v" schema. The drop script 76 is run during tests to fully reset the database. 77 78 Exchange details 79 ^^^^^^^^^^^^^^^^ 80 81 The exchange uses "exchange_tables" to create the master 82 table mentioned above. In "exchange_tables", entries are 83 executed in the order of the "table_serial_id". Each 84 entry has a "name", which is the name of the affected table 85 (or at least the prefix in the case of partitioned or sharded 86 tables). The "version" field stores which "exchange-XXXX.sql" 87 file setup the respective table entry, but is for now mostly 88 for internal documentation. The "action" defines both the 89 condition under which to run a function. Specifically, 90 actions can be: 91 92 * create --- run on the master table and each shard; used to create or alter the main table 93 * constrain --- run only on the partitions/shards, or on master if there are no partitions; used to setup constraints like uniqueness that only apply to the lowest levels of the table 94 * master -- run only on the master table; used to setup triggers and other constraints that only apply to the master table 95 * foreign -- run only on the master table and only if there are no partition; used to setup foreign key constraints that are not supported on partitioned or sharded tables 96 97 The "partitioned" field indicates that this table is partitioned and instructs the functions to create partitions (or shards) 98 for this table. 99 100 The "by_range" field indicates if the table is partitioned by 101 range, which prevents automatic generation of partitions as 102 is done if partitioned by hash. 103 104 The "finished" field is initially false, but set to TRUE once the respective 105 function has been executed. 106 107 The main "do_create_tables" function triggers the unfinished actions 108 registered in the "exchange_tables" table. It is given arguments to control 109 the number of partitions, the use of partitions and (in the future) the use of 110 sharding. 111 112 The individual actions use helper functions ("create_partitioned_table", 113 "comment_partitioned_table" and "comment_partitioned_column") to facilitate 114 the creation of tables and associated comments. These functions are used so 115 that we can only define the schema or comment once, and have it applied to 116 tables with names and creation syntax that changes slightly if we use shards 117 or partitions. 118 119 Some additional logic will be needed to deal nicely with 120 sharding (which is currently not supported), as with 121 sharing we will need to detach shards, migrate shards, and 122 re-attach shards. So this will require additional stored 123 procedures to support operations on shards. 124 125 126 Merchant details 127 ^^^^^^^^^^^^^^^^ 128 129 The merchant does not currently use the exchange's master-table scheme for 130 sharding or partitioning. Its schema is managed by the 131 ``versioning.sql``-controlled table creation and alteration sequence and its 132 generated ``procedures.sql``. The backend now uses stored functions and 133 procedures in addition to versioned table migrations. 134 135 136 Alternatives 137 ============ 138 139 * We might want to consider storing more meta-data 140 in the database, such as the use of sharding, the 141 names of the shard servers, or even just the number 142 of partitions. 143 144 * We could require dumping out the old database and 145 loading it in via some explicit importer during each 146 migration; having migration logic in C would enable more 147 powerful migrations, but dumping and reloading the entire 148 database would also be more expensive. It would have the 149 advantage of basically having the old database around in 150 case of migration trouble, so the cost disadvantage might 151 not be so bad (as admins are likely to make a backup anyway). 152 OTOH, doing the migration with the equivalent of a 153 taler-auditor-sync would require quite a bit more code 154 than the simple ALTER/CREATE statements in an SQL file. 155 156 157 Drawbacks 158 ========= 159 160 * not exactly trival logic 161 * some complexity to implement 162 163 164 Discussion / Q&A 165 ================ 166 167 (This should be filled in with results from discussions on mailing lists / personal communication.)