aboutsummaryrefslogtreecommitdiff
path: root/src/auditordb/auditor-0001.sql
blob: 0b7823cec3285b8dc40a9548b1f797279e9149f1 (plain)
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
--
-- This file is part of TALER
-- Copyright (C) 2014--2024 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('auditor-0001', NULL, NULL);

CREATE SCHEMA auditor;
COMMENT ON SCHEMA auditor IS 'taler-auditor data';

SET search_path TO auditor;

---------------------------------------------------------------------------
--                   General procedures for DB setup
---------------------------------------------------------------------------

CREATE TABLE auditor_tables
  (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
  ,name TEXT NOT NULL
  ,version TEXT NOT NULL
  ,action TEXT NOT NULL
  ,partitioned BOOL NOT NULL
  ,by_range BOOL NOT NULL
  ,finished BOOL NOT NULL DEFAULT(FALSE));
COMMENT ON TABLE auditor_tables
  IS 'Tables of the auditor and their status';
COMMENT ON COLUMN auditor_tables.name
  IS 'Base name of the table (without partition/shard)';
COMMENT ON COLUMN auditor_tables.version
  IS 'Version of the DB in which the given action happened';
COMMENT ON COLUMN auditor_tables.action
  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.';
COMMENT ON COLUMN auditor_tables.partitioned
  IS 'TRUE if the table is partitioned';
COMMENT ON COLUMN auditor_tables.by_range
  IS 'TRUE if the table is partitioned by range';
COMMENT ON COLUMN auditor_tables.finished
  IS 'TRUE if the respective migration has been run';


CREATE FUNCTION create_partitioned_table(
   IN table_definition TEXT -- SQL template for table creation
  ,IN table_name TEXT -- base name of the table
  ,IN main_table_partition_str TEXT -- declaration for how to partition the table
  ,IN partition_suffix TEXT DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
  IF (partition_suffix IS NULL)
  THEN
    -- no partitioning, disable option
    main_table_partition_str = '';
  ELSE
    IF (partition_suffix::int > 0)
    THEN
      -- sharding, add shard name
      table_name=table_name || '_' || partition_suffix;
    END IF;
  END IF;
  EXECUTE FORMAT(
    table_definition,
    table_name,
    main_table_partition_str
  );
END $$;

COMMENT ON FUNCTION create_partitioned_table
  IS 'Generic function to create a table that is partitioned or sharded.';


CREATE FUNCTION comment_partitioned_table(
   IN table_comment TEXT
  ,IN table_name TEXT
  ,IN partition_suffix TEXT DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
  IF ( (partition_suffix IS NOT NULL) AND
       (partition_suffix::int > 0) )
  THEN
    -- sharding, add shard name
    table_name=table_name || '_' || partition_suffix;
  END IF;
  EXECUTE FORMAT(
     'COMMENT ON TABLE %s IS %s'
    ,table_name
    ,quote_literal(table_comment)
  );
END $$;

COMMENT ON FUNCTION comment_partitioned_table
  IS 'Generic function to create a comment on table that is partitioned.';


CREATE FUNCTION comment_partitioned_column(
   IN table_comment TEXT
  ,IN column_name TEXT
  ,IN table_name TEXT
  ,IN partition_suffix TEXT DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
  IF ( (partition_suffix IS NOT NULL) AND
       (partition_suffix::int > 0) )
  THEN
    -- sharding, add shard name
    table_name=table_name || '_' || partition_suffix;
  END IF;
  EXECUTE FORMAT(
     'COMMENT ON COLUMN %s.%s IS %s'
    ,table_name
    ,column_name
    ,quote_literal(table_comment)
  );
END $$;

COMMENT ON FUNCTION comment_partitioned_column
  IS 'Generic function to create a comment on column of a table that is partitioned.';


---------------------------------------------------------------------------
--                   Main DB setup loop
---------------------------------------------------------------------------

CREATE FUNCTION do_create_tables(
  num_partitions INTEGER
-- NULL: no partitions, add foreign constraints
-- 0: no partitions, no foreign constraints
-- 1: only 1 default partition
-- > 1: normal partitions
)
  RETURNS VOID
  LANGUAGE plpgsql
AS $$
DECLARE
  tc CURSOR FOR
    SELECT table_serial_id
          ,name
          ,action
          ,partitioned
          ,by_range
      FROM auditor.auditor_tables
     WHERE NOT finished
     ORDER BY table_serial_id ASC;
BEGIN
  FOR rec IN tc
  LOOP
    CASE rec.action
    -- "create" actions apply to master and partitions
    WHEN 'create'
    THEN
      IF (rec.partitioned AND
          (num_partitions IS NOT NULL))
      THEN
        -- Create master table with partitioning.
        EXECUTE FORMAT(
          'SELECT auditor.%s_table_%s (%s)'::text
          ,rec.action
          ,rec.name
          ,quote_literal('0')
        );
        IF (rec.by_range OR
            (num_partitions = 0))
        THEN
          -- Create default partition.
          IF (rec.by_range)
          THEN
            -- Range partition
            EXECUTE FORMAT(
              'CREATE TABLE auditor.%s_default'
              ' PARTITION OF %s'
              ' DEFAULT'
             ,rec.name
             ,rec.name
            );
          ELSE
            -- Hash partition
            EXECUTE FORMAT(
              'CREATE TABLE auditor.%s_default'
              ' PARTITION OF %s'
              ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)'
             ,rec.name
             ,rec.name
            );
          END IF;
        ELSE
          FOR i IN 1..num_partitions LOOP
            -- Create num_partitions
            EXECUTE FORMAT(
               'CREATE TABLE auditor.%I'
               ' PARTITION OF %I'
               ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
              ,rec.name || '_' || i
              ,rec.name
              ,num_partitions
              ,i-1
            );
          END LOOP;
        END IF;
      ELSE
        -- Only create master table. No partitions.
        EXECUTE FORMAT(
          'SELECT auditor.%s_table_%s ()'::text
          ,rec.action
          ,rec.name
        );
      END IF;
    -- Constrain action apply to master OR each partition
    WHEN 'constrain'
    THEN
      ASSERT rec.partitioned, 'constrain action only applies to partitioned tables';
      IF (num_partitions IS NULL)
      THEN
        -- Constrain master table
        EXECUTE FORMAT(
           'SELECT auditor.%s_table_%s (NULL)'::text
          ,rec.action
          ,rec.name
        );
      ELSE
        IF ( (num_partitions = 0) OR
             (rec.by_range) )
        THEN
          -- Constrain default table
          EXECUTE FORMAT(
             'SELECT auditor.%s_table_%s (%s)'::text
            ,rec.action
            ,rec.name
            ,quote_literal('default')
          );
        ELSE
          -- Constrain each partition
          FOR i IN 1..num_partitions LOOP
            EXECUTE FORMAT(
              'SELECT auditor.%s_table_%s (%s)'::text
              ,rec.action
              ,rec.name
              ,quote_literal(i)
            );
          END LOOP;
        END IF;
      END IF;
    -- Foreign actions only apply if partitioning is off
    WHEN 'foreign'
    THEN
      IF (num_partitions IS NULL)
      THEN
        -- Add foreign constraints
        EXECUTE FORMAT(
          'SELECT auditor.%s_table_%s (%s)'::text
          ,rec.action
          ,rec.name
          ,NULL
        );
      END IF;
    WHEN 'master'
    THEN
      EXECUTE FORMAT(
        'SELECT auditor.%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 auditor.auditor_tables
       SET finished=TRUE
     WHERE table_serial_id=rec.table_serial_id;
  END LOOP; -- create/alter/drop actions
END $$;

COMMENT ON FUNCTION do_create_tables
  IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.';


COMMIT;