1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
--
-- This file is part of TALER
-- Copyright (C) 2014--2022 Taler Systems SA
--
-- TALER is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License as published by the Free Software
-- Foundation; either version 3, or (at your option) any later version.
--
-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along with
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
BEGIN;
SELECT _v.register_patch('donau-0001', NULL, NULL);
CREATE SCHEMA donau;
COMMENT ON SCHEMA donau IS 'donau data';
SET search_path TO donau;
---------------------------------------------------------------------------
-- General procedures for DB setup
---------------------------------------------------------------------------
CREATE TABLE donau_tables
(table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
,name TEXT NOT NULL
,version TEXT NOT NULL
,action TEXT NOT NULL
,finished BOOL NOT NULL DEFAULT(FALSE));
COMMENT ON TABLE donau_tables
IS 'Tables of the donau and their status';
COMMENT ON COLUMN donau_tables.name
IS 'Base name of the table';
COMMENT ON COLUMN donau_tables.version
IS 'Version of the DB in which the given action happened';
COMMENT ON COLUMN donau_tables.action
IS 'Action to take on the table (e.g. create, constrain, or foreign).';
COMMENT ON COLUMN donau_tables.finished
IS 'TRUE if the respective migration has been run';
---------------------------------------------------------------------------
-- Main DB setup loop
---------------------------------------------------------------------------
CREATE PROCEDURE do_create_tables()
LANGUAGE plpgsql
AS $$
DECLARE
tc CURSOR FOR
SELECT table_serial_id
,name
,action
FROM donau.donau_tables
WHERE NOT finished
ORDER BY table_serial_id ASC;
BEGIN
FOR rec IN tc
LOOP
CASE rec.action
WHEN 'create'
THEN
EXECUTE FORMAT(
'SELECT donau.%s_table_%s ()'::text
,rec.action
,rec.name
);
WHEN 'constrain'
THEN
-- Constrain master table
EXECUTE FORMAT(
'SELECT donau.%s_table_%s (NULL)'::text
,rec.action
,rec.name
);
WHEN 'foreign'
THEN
-- Add foreign constraints
EXECUTE FORMAT(
'SELECT donau.%s_table_%s (%s)'::text
,rec.action
,rec.name
,NULL
);
WHEN 'master'
THEN
EXECUTE FORMAT(
'SELECT donau.%s_table_%s ()'::text
,rec.action
,rec.name
);
ELSE
ASSERT FALSE, 'unsupported action type: ' || rec.action;
END CASE; -- END CASE (rec.action)
-- Mark as finished
UPDATE donau.donau_tables
SET finished=TRUE
WHERE table_serial_id=rec.table_serial_id;
END LOOP; -- create/alter/drop actions
END $$;
COMMENT ON PROCEDURE do_create_tables
IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.';
COMMIT;
|