donau-0001.sql (3283B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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('donau-0001', NULL, NULL); 20 21 CREATE SCHEMA donau; 22 COMMENT ON SCHEMA donau IS 'donau data'; 23 24 SET search_path TO donau; 25 26 --------------------------------------------------------------------------- 27 -- General procedures for DB setup 28 --------------------------------------------------------------------------- 29 30 CREATE TABLE donau_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 ,finished BOOL NOT NULL DEFAULT(FALSE)); 36 COMMENT ON TABLE donau_tables 37 IS 'Tables of the donau and their status'; 38 COMMENT ON COLUMN donau_tables.name 39 IS 'Base name of the table'; 40 COMMENT ON COLUMN donau_tables.version 41 IS 'Version of the DB in which the given action happened'; 42 COMMENT ON COLUMN donau_tables.action 43 IS 'Action to take on the table (e.g. create, constrain, or foreign).'; 44 COMMENT ON COLUMN donau_tables.finished 45 IS 'TRUE if the respective migration has been run'; 46 47 48 --------------------------------------------------------------------------- 49 -- Main DB setup loop 50 --------------------------------------------------------------------------- 51 52 CREATE PROCEDURE do_create_tables() 53 LANGUAGE plpgsql 54 AS $$ 55 DECLARE 56 tc CURSOR FOR 57 SELECT table_serial_id 58 ,name 59 ,action 60 FROM donau.donau_tables 61 WHERE NOT finished 62 ORDER BY table_serial_id ASC; 63 BEGIN 64 FOR rec IN tc 65 LOOP 66 CASE rec.action 67 WHEN 'create' 68 THEN 69 EXECUTE FORMAT( 70 'SELECT donau.%s_table_%s ()'::text 71 ,rec.action 72 ,rec.name 73 ); 74 WHEN 'constrain' 75 THEN 76 -- Constrain master table 77 EXECUTE FORMAT( 78 'SELECT donau.%s_table_%s (NULL)'::text 79 ,rec.action 80 ,rec.name 81 ); 82 WHEN 'foreign' 83 THEN 84 -- Add foreign constraints 85 EXECUTE FORMAT( 86 'SELECT donau.%s_table_%s (%s)'::text 87 ,rec.action 88 ,rec.name 89 ,NULL 90 ); 91 WHEN 'master' 92 THEN 93 EXECUTE FORMAT( 94 'SELECT donau.%s_table_%s ()'::text 95 ,rec.action 96 ,rec.name 97 ); 98 ELSE 99 ASSERT FALSE, 'unsupported action type: ' || rec.action; 100 END CASE; -- END CASE (rec.action) 101 -- Mark as finished 102 UPDATE donau.donau_tables 103 SET finished=TRUE 104 WHERE table_serial_id=rec.table_serial_id; 105 END LOOP; -- create/alter/drop actions 106 END $$; 107 108 COMMENT ON PROCEDURE do_create_tables 109 IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.'; 110 111 112 COMMIT;