auditor-0001.sql (8664B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2024 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 BEGIN; 18 19 SELECT _v.register_patch('auditor-0001', NULL, NULL); 20 21 CREATE SCHEMA auditor; 22 COMMENT ON SCHEMA auditor IS 'taler-auditor data'; 23 24 SET search_path TO auditor; 25 26 --------------------------------------------------------------------------- 27 -- General procedures for DB setup 28 --------------------------------------------------------------------------- 29 30 CREATE TABLE auditor_tables 31 (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY 32 ,name TEXT NOT NULL 33 ,version TEXT NOT NULL 34 ,action TEXT NOT NULL 35 ,partitioned BOOL NOT NULL 36 ,by_range BOOL NOT NULL 37 ,finished BOOL NOT NULL DEFAULT(FALSE)); 38 COMMENT ON TABLE auditor_tables 39 IS 'Tables of the auditor and their status'; 40 COMMENT ON COLUMN auditor_tables.name 41 IS 'Base name of the table (without partition/shard)'; 42 COMMENT ON COLUMN auditor_tables.version 43 IS 'Version of the DB in which the given action happened'; 44 COMMENT ON COLUMN auditor_tables.action 45 IS 'Action to take on the table (e.g. create, constrain, or foreign). Create is done for the master table and each partition; constrain is only for partitions or for master if there are no partitions; master only on master (takes no argument); foreign only on master if there are no partitions.'; 46 COMMENT ON COLUMN auditor_tables.partitioned 47 IS 'TRUE if the table is partitioned'; 48 COMMENT ON COLUMN auditor_tables.by_range 49 IS 'TRUE if the table is partitioned by range'; 50 COMMENT ON COLUMN auditor_tables.finished 51 IS 'TRUE if the respective migration has been run'; 52 53 54 CREATE FUNCTION create_partitioned_table( 55 IN table_definition TEXT -- SQL template for table creation 56 ,IN table_name TEXT -- base name of the table 57 ,IN main_table_partition_str TEXT -- declaration for how to partition the table 58 ,IN partition_suffix TEXT DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding 59 ) 60 RETURNS VOID 61 LANGUAGE plpgsql 62 AS $$ 63 BEGIN 64 IF (partition_suffix IS NULL) 65 THEN 66 -- no partitioning, disable option 67 main_table_partition_str = ''; 68 ELSE 69 IF (partition_suffix::int > 0) 70 THEN 71 -- sharding, add shard name 72 table_name=table_name || '_' || partition_suffix; 73 END IF; 74 END IF; 75 EXECUTE FORMAT( 76 table_definition, 77 table_name, 78 main_table_partition_str 79 ); 80 END $$; 81 82 COMMENT ON FUNCTION create_partitioned_table 83 IS 'Generic function to create a table that is partitioned or sharded.'; 84 85 86 CREATE FUNCTION comment_partitioned_table( 87 IN table_comment TEXT 88 ,IN table_name TEXT 89 ,IN partition_suffix TEXT DEFAULT NULL 90 ) 91 RETURNS VOID 92 LANGUAGE plpgsql 93 AS $$ 94 BEGIN 95 IF ( (partition_suffix IS NOT NULL) AND 96 (partition_suffix::int > 0) ) 97 THEN 98 -- sharding, add shard name 99 table_name=table_name || '_' || partition_suffix; 100 END IF; 101 EXECUTE FORMAT( 102 'COMMENT ON TABLE %s IS %s' 103 ,table_name 104 ,quote_literal(table_comment) 105 ); 106 END $$; 107 108 COMMENT ON FUNCTION comment_partitioned_table 109 IS 'Generic function to create a comment on table that is partitioned.'; 110 111 112 CREATE FUNCTION comment_partitioned_column( 113 IN table_comment TEXT 114 ,IN column_name TEXT 115 ,IN table_name TEXT 116 ,IN partition_suffix TEXT DEFAULT NULL 117 ) 118 RETURNS VOID 119 LANGUAGE plpgsql 120 AS $$ 121 BEGIN 122 IF ( (partition_suffix IS NOT NULL) AND 123 (partition_suffix::int > 0) ) 124 THEN 125 -- sharding, add shard name 126 table_name=table_name || '_' || partition_suffix; 127 END IF; 128 EXECUTE FORMAT( 129 'COMMENT ON COLUMN %s.%s IS %s' 130 ,table_name 131 ,column_name 132 ,quote_literal(table_comment) 133 ); 134 END $$; 135 136 COMMENT ON FUNCTION comment_partitioned_column 137 IS 'Generic function to create a comment on column of a table that is partitioned.'; 138 139 140 --------------------------------------------------------------------------- 141 -- Main DB setup loop 142 --------------------------------------------------------------------------- 143 144 CREATE FUNCTION do_create_tables( 145 num_partitions INTEGER 146 -- NULL: no partitions, add foreign constraints 147 -- 0: no partitions, no foreign constraints 148 -- 1: only 1 default partition 149 -- > 1: normal partitions 150 ) 151 RETURNS VOID 152 LANGUAGE plpgsql 153 AS $$ 154 DECLARE 155 tc CURSOR FOR 156 SELECT table_serial_id 157 ,name 158 ,action 159 ,partitioned 160 ,by_range 161 FROM auditor.auditor_tables 162 WHERE NOT finished 163 ORDER BY table_serial_id ASC; 164 BEGIN 165 FOR rec IN tc 166 LOOP 167 CASE rec.action 168 -- "create" actions apply to master and partitions 169 WHEN 'create' 170 THEN 171 IF (rec.partitioned AND 172 (num_partitions IS NOT NULL)) 173 THEN 174 -- Create master table with partitioning. 175 EXECUTE FORMAT( 176 'SELECT auditor.%s_table_%s (%s)'::text 177 ,rec.action 178 ,rec.name 179 ,quote_literal('0') 180 ); 181 IF (rec.by_range OR 182 (num_partitions = 0)) 183 THEN 184 -- Create default partition. 185 IF (rec.by_range) 186 THEN 187 -- Range partition 188 EXECUTE FORMAT( 189 'CREATE TABLE auditor.%s_default' 190 ' PARTITION OF %s' 191 ' DEFAULT' 192 ,rec.name 193 ,rec.name 194 ); 195 ELSE 196 -- Hash partition 197 EXECUTE FORMAT( 198 'CREATE TABLE auditor.%s_default' 199 ' PARTITION OF %s' 200 ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)' 201 ,rec.name 202 ,rec.name 203 ); 204 END IF; 205 ELSE 206 FOR i IN 1..num_partitions LOOP 207 -- Create num_partitions 208 EXECUTE FORMAT( 209 'CREATE TABLE auditor.%I' 210 ' PARTITION OF %I' 211 ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' 212 ,rec.name || '_' || i 213 ,rec.name 214 ,num_partitions 215 ,i-1 216 ); 217 END LOOP; 218 END IF; 219 ELSE 220 -- Only create master table. No partitions. 221 EXECUTE FORMAT( 222 'SELECT auditor.%s_table_%s ()'::text 223 ,rec.action 224 ,rec.name 225 ); 226 END IF; 227 -- Constrain action apply to master OR each partition 228 WHEN 'constrain' 229 THEN 230 ASSERT rec.partitioned, 'constrain action only applies to partitioned tables'; 231 IF (num_partitions IS NULL) 232 THEN 233 -- Constrain master table 234 EXECUTE FORMAT( 235 'SELECT auditor.%s_table_%s (NULL)'::text 236 ,rec.action 237 ,rec.name 238 ); 239 ELSE 240 IF ( (num_partitions = 0) OR 241 (rec.by_range) ) 242 THEN 243 -- Constrain default table 244 EXECUTE FORMAT( 245 'SELECT auditor.%s_table_%s (%s)'::text 246 ,rec.action 247 ,rec.name 248 ,quote_literal('default') 249 ); 250 ELSE 251 -- Constrain each partition 252 FOR i IN 1..num_partitions LOOP 253 EXECUTE FORMAT( 254 'SELECT auditor.%s_table_%s (%s)'::text 255 ,rec.action 256 ,rec.name 257 ,quote_literal(i) 258 ); 259 END LOOP; 260 END IF; 261 END IF; 262 -- Foreign actions only apply if partitioning is off 263 WHEN 'foreign' 264 THEN 265 IF (num_partitions IS NULL) 266 THEN 267 -- Add foreign constraints 268 EXECUTE FORMAT( 269 'SELECT auditor.%s_table_%s (%s)'::text 270 ,rec.action 271 ,rec.name 272 ,NULL 273 ); 274 END IF; 275 WHEN 'master' 276 THEN 277 EXECUTE FORMAT( 278 'SELECT auditor.%s_table_%s ()'::text 279 ,rec.action 280 ,rec.name 281 ); 282 ELSE 283 ASSERT FALSE, 'unsupported action type: ' || rec.action; 284 END CASE; -- END CASE (rec.action) 285 -- Mark as finished 286 UPDATE auditor.auditor_tables 287 SET finished=TRUE 288 WHERE table_serial_id=rec.table_serial_id; 289 END LOOP; -- create/alter/drop actions 290 END $$; 291 292 COMMENT ON FUNCTION do_create_tables 293 IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.'; 294 295 296 COMMIT;