From f2ba02aab2b9bbd976107ecc4ac7e7d657a9d73a Mon Sep 17 00:00:00 2001
From: Christian Grothoff <christian@grothoff.org>
Date: Sun, 27 Nov 2022 18:40:44 +0100
Subject: more sql refactoring

---
 src/exchangedb/exchange-0001.sql | 316 +++++++++++++++++++--------------------
 1 file changed, 150 insertions(+), 166 deletions(-)

(limited to 'src/exchangedb/exchange-0001.sql')

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 208e81965..fad27adda 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -14,9 +14,13 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
--- Everything in one big transaction
 BEGIN;
 
+SELECT _v.register_patch('exchange-0001', NULL, NULL);
+
+CREATE SCHEMA exchange;
+COMMENT ON SCHEMA exchange IS 'taler-exchange data';
+
 SET search_path TO exchange;
 
 ---------------------------------------------------------------------------
@@ -38,7 +42,7 @@ COMMENT ON COLUMN exchange_tables.name
 COMMENT ON COLUMN exchange_tables.version
   IS 'Version of the DB in which the given action happened';
 COMMENT ON COLUMN exchange_tables.action
-  IS 'Action to take on the table (e.g. create, alter, constrain, foreign, or drop). Create, alter and drop are done for master 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.';
+  IS 'Action to take on the table (e.g. create, constrain, foreign, or drop). 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 exchange_tables.partitioned
   IS 'TRUE if the table is partitioned';
 COMMENT ON COLUMN exchange_tables.by_range
@@ -48,51 +52,58 @@ COMMENT ON COLUMN exchange_tables.finished
 
 
 CREATE FUNCTION create_partitioned_table(
-   IN table_definition VARCHAR
-  ,IN table_name VARCHAR
-  ,IN main_table_partition_str VARCHAR -- Used only when it is the main table - we do not partition shard tables
-  ,IN shard_suffix VARCHAR DEFAULT NULL
+   IN table_definition VARCHAR -- SQL template for table creation
+  ,IN table_name VARCHAR -- base name of the table
+  ,IN main_table_partition_str VARCHAR -- declaration for how to partition the table
+  ,IN partition_suffix VARCHAR DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 BEGIN
-  IF shard_suffix IS NOT NULL THEN
-    table_name=table_name || '_' || shard_suffix;
+  IF partition_suffix IS NULL
+  THEN
+    -- no partitioning, disable option
     main_table_partition_str = '';
+  ELSE
+    IF partition_suffix > 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
-$$;
+END $$;
 
 COMMENT ON FUNCTION create_partitioned_table
-  IS 'Generic function to create a table that is partitioned.';
+  IS 'Generic function to create a table that is partitioned or sharded.';
 
 
 CREATE FUNCTION comment_partitioned_table(
    IN table_comment VARCHAR
   ,IN table_name VARCHAR
-  ,IN shard_suffix VARCHAR DEFAULT NULL
+  ,IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 BEGIN
-  IF shard_suffix IS NOT NULL THEN
-    table_name=table_name || '_' || shard_suffix;
-    main_table_partition_str = '';
+  IF ( (partition_suffix IS NOT NULL) AND
+       (partition_suffix > 0) )
+  THEN
+    -- sharding, add shard name
+    table_name=table_name || '_' || partition_suffix;
   END IF;
   EXECUTE FORMAT(
-     COMMENT ON TABLE %s IS '%s'
+     'COMMENT ON TABLE %s IS %s'
     ,table_name
-    ,table_comment
+    ,quote_literal(table_comment)
   );
-END
-$$;
+END $$;
 
 COMMENT ON FUNCTION comment_partitioned_table
   IS 'Generic function to create a comment on table that is partitioned.';
@@ -102,34 +113,37 @@ CREATE FUNCTION comment_partitioned_column(
    IN table_comment VARCHAR
   ,IN column_name VARCHAR
   ,IN table_name VARCHAR
-  ,IN shard_suffix VARCHAR DEFAULT NULL
+  ,IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 BEGIN
-  IF shard_suffix IS NOT NULL THEN
-    table_name=table_name || '_' || shard_suffix;
-    main_table_partition_str = '';
+  IF ( (partition_suffix IS NOT NULL) AND
+       (partition_suffix > 0) )
+  THEN
+    -- sharding, add shard name
+    table_name=table_name || '_' || partition_suffix;
   END IF;
   EXECUTE FORMAT(
-     COMMENT ON COLUMN %s.%s IS '%s'
+     'COMMENT ON COLUMN %s.%s IS %s'
     ,table_name
     ,column_name
-    ,table_comment
+    ,quote_literal(table_comment)
   );
-END
-$$;
+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 create_tables(
   num_partitions INTEGER
--- FIXME: not implemented like this, but likely good:
 -- NULL: no partitions, add foreign constraints
 -- 0: no partitions, no foreign constraints
 -- 1: only 1 default partition
@@ -139,176 +153,146 @@ CREATE FUNCTION create_tables(
   LANGUAGE plpgsql
 AS $$
 DECLARE
-  -- FIXME: use only ONE cursor and then switch on action!
   tc CURSOR FOR
     SELECT table_serial_id
           ,name
           ,action
+          ,partitioned
           ,by_range
       FROM exchange_tables
      WHERE NOT finished
-       AND partitioned
-       AND (action='create'
-         OR action='alter'
-         OR action='drop')
-     ORDER BY table_serial_id ASC;
-DECLARE
-  ta CURSOR FOR
-    SELECT table_serial_id
-          ,name
-          ,action
-          ,by_range
-      FROM exchange_tables
-     WHERE NOT finished
-       AND partitioned
-       AND action='constrain'
-     ORDER BY table_serial_id ASC;
-DECLARE
-  tf CURSOR FOR
-    SELECT table_serial_id
-          ,name
-          ,action
-          ,by_range
-      FROM exchange_tables
-     WHERE NOT finished
-       AND partitioned
-       AND action='foreign'
-     ORDER BY table_serial_id ASC;
-DECLARE
-  tm CURSOR FOR
-    SELECT table_serial_id
-          ,name
-          ,action
-          ,by_range
-      FROM exchange_tables
-     WHERE NOT finished
-       AND partitioned
-       AND action='master'
      ORDER BY table_serial_id ASC;
 BEGIN
-
-  -- run create/alter/drop actions
   FOR rec IN tc
   LOOP
-    -- First create the master table, either
-    -- completely unpartitioned, or with one
-    -- master and the 'default' partition
-    IF IS NULL num_partitions
-    THEN
-      -- No partitions at all.
-      EXECUTE FORMAT(
-        'PERFORM %s_table_%s (%s)'::text
-        ,rec.action
-        ,rec.name
-        ,NULL
-      );
-    ELSE
-      -- One default partition only.
-      EXECUTE FORMAT(
-        'PERFORM %s_table_%s (%s)'::text
-        ,rec.action
-        ,rec.name
-        ,0
-      );
-
-    IF NOT IS NULL num_partitions
+    CASE rec.action
+    -- "create" actions apply to master and partitions
+    WHEN "create"
     THEN
-      IF rec.by_range
+      IF (rec.partitioned AND
+          (num_partitions IS NOT NULL))
       THEN
-        -- range partitions (only create default)
-        -- Create default partition.
+        -- Create master table with partitioning.
         EXECUTE FORMAT(
-           'CREATE TABLE %s_default PARTITION OF %s DEFAULT'
+          'PERFORM %s_table_%s (%s)'::text
+          ,rec.action
           ,rec.name
+          ,0
+        );
+        IF (rec.by_range OR
+            (num_partitions = 0))
+        THEN
+          -- Create default partition.
+          IF (rec.by_range)
+          THEN
+            -- Range partition
+            EXECUTE FORMAT(
+              'CREATE TABLE IF NOT EXISTS %s_default'
+              ' PARTITION OF %s'
+              ' FOR DEFAULT'
+             ,rec.name
+             ,rec.name
+            );
+          ELSE
+            -- Hash partition
+            EXECUTE FORMAT(
+              'CREATE TABLE IF NOT EXISTS %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 IF NOT EXISTS %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(
+          'PERFORM %s_table_%s (%s)'::text
+          ,rec.action
+          ,rec.name
+          ,NULL
+        );
+      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(
+          'PERFORM %s_table_%s (%s)'::text
+          ,rec.action
           ,rec.name
+          ,NULL
         );
       ELSE
-        -- hash partitions
-        IF 0=num_partitions
+        IF (num_partitions = 0)
         THEN
-          -- Create default partition.
-          EXECUTE FORMAT(
-             'CREATE TABLE IF NOT EXISTS %s_default PARTITION OF %s FOR VALUES WITH (MODULUS 1, REMAINDER 0)'
-            ,rec.name
-            ,rec.name
-          );
-        END IF
-        FOR i IN 1..num_partitions LOOP
-          -- Create num_partitions
+          -- Constrain default table
           EXECUTE FORMAT(
-             'CREATE TABLE IF NOT EXISTS %I'
-             ' PARTITION OF %I'
-             ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
-            ,rec.name || '_' || i
+            'PERFORM %s_table_%s (%s)'::text
+            ,rec.action
             ,rec.name
-            ,num_partitions
-            ,i-1
+            ,'default'
           );
-        END LOOP;
+        ELSE
+          -- Constrain each partition
+          FOR i IN 1..num_partitions LOOP
+            EXECUTE FORMAT(
+              'PERFORM %s_table_%s (%s)'::text
+              ,rec.action
+              ,rec.name
+              ,i
+            );
+          END LOOP;
+        END IF;
       END IF;
-    END IF;
-    UPDATE exchange_tables
-       SET finished=TRUE
-     WHERE table_serial_id=rec.table_serial_id;
-  END LOOP; -- create/alter/drop actions
-
-  -- Run constrain actions
-  FOR rec IN ta
-  LOOP
-    IF IS NULL num_partitions
+    -- Foreign actions only apply if partitioning is off
+    WHEN "foreign"
     THEN
-      -- Constrain master
-      EXECUTE FORMAT(
-        'PERFORM %s_table_%s (%s)'::text
-        ,rec.action
-        ,rec.name
-        ,NULL
-      );
-    END IF
-
-    IF 0=num_partitions
+      IF (num_partitions IS NULL)
+      THEN
+        -- Only create master table. No partitions.
+        EXECUTE FORMAT(
+          'PERFORM %s_table_%s (%s)'::text
+          ,rec.action
+          ,rec.name
+          ,NULL
+        );
+      END IF;
+    WHEN "master"
     THEN
-      -- constrain default partition
       EXECUTE FORMAT(
-        'PERFORM %s_table_%s (%s)'::text
+        'PERFORM %s_table_%s'::text
         ,rec.action
         ,rec.name
-        ,0
       );
-    END IF
-    FOR i IN 1..num_partitions LOOP
-      -- constrain each partition
-      EXECUTE FORMAT(
-        'PERFORM %s_table_%s (%s)'::text
-        ,rec.action
-        ,rec.name
-        ,i::varchar
-      );
-    END LOOP;
+    ELSE
+      ASSERT FALSE, 'unsupported action type: ' || rec.action;
+    END CASE;  -- END CASE (rec.action)
+    -- Mark as finished
     UPDATE exchange_tables
        SET finished=TRUE
      WHERE table_serial_id=rec.table_serial_id;
-  END LOOP;
+  END LOOP; -- create/alter/drop actions
+END $$;
 
-  -- run foreign actions
-  FOR rec IN tf
-  LOOP
-    IF IS NULL num_partitions
-    THEN
-      -- Add foreign constraints
-      EXECUTE FORMAT(
-        'PERFORM %s_table_%s ()'::text
-        ,rec.action
-        ,rec.name
-      );
-    END IF
-    UPDATE exchange_tables
-       SET finished=TRUE
-     WHERE table_serial_id=rec.table_serial_id;
-  END LOOP;
+COMMENT ON FUNCTION create_tables
+  IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.';
 
-END
-$$;
 
-COMMENT ON FUNCTION create_tables
-  IS 'Creates all tables for the given number of partitions that need creating.';
+COMMIT;
-- 
cgit v1.2.3

