path: root/src/exchangedb/exchange-0001.sql

--
-- 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('exchange-0001', NULL, NULL);

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

SET search_path TO exchange;

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

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


CREATE FUNCTION create_partitioned_table(
   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 (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 VARCHAR
  ,IN table_name VARCHAR
  ,IN partition_suffix VARCHAR 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 VARCHAR
  ,IN column_name VARCHAR
  ,IN table_name VARCHAR
  ,IN partition_suffix VARCHAR 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.';


--------------------------------------------------------------
-- Taler amounts and helper functiosn
-------------------------------------------------------------

DO $$
BEGIN
  CREATE TYPE TALER_AMOUNT
    AS (val  INT8
       ,frac INT4);

  COMMENT ON TYPE TALER_AMOUNT
    IS 'Type to store a TALER-amount as (val, frac) pair.';
EXCEPTION
  WHEN duplicate_object THEN null;
END
$$;

CREATE PROCEDURE amount_normalize(
    IN amount TALER_AMOUNT
  ,OUT normalized TALER_AMOUNT
)
LANGUAGE plpgsql
AS $$
BEGIN
  normalized.val = amount.val + amount.frac / 100000000;
  normalized.frac = amount.frac % 100000000;
END $$;

COMMENT ON PROCEDURE amount_normalize
  IS 'Returns the normalized amount by adding to the .val the value of (.frac / 100000000) and removing the modulus 100000000 from .frac.';

CREATE PROCEDURE amount_add(
   IN a TALER_AMOUNT
  ,IN b TALER_AMOUNT
  ,OUT sum TALER_AMOUNT
)
LANGUAGE plpgsql
AS $$
BEGIN
  sum = (a.val + b.val, a.frac + b.frac);
  CALL amount_normalize(sum ,sum);

  IF (sum.val > (1<<52))
  THEN
    RAISE EXCEPTION 'addition overflow';
  END IF;
END $$;

COMMENT ON PROCEDURE amount_add
  IS 'Returns the normalized sum of two amounts. It raises an exception when the resulting .val is larger than 2^52';

CREATE FUNCTION amount_left_minus_right(
  IN  l TALER_AMOUNT
 ,IN  r TALER_AMOUNT
 ,OUT diff TALER_AMOUNT
 ,OUT ok BOOLEAN
)
LANGUAGE plpgsql
AS $$
BEGIN

IF (l.val > r.val)
THEN
  ok = TRUE;
  IF (l.frac >= r.frac)
  THEN
    diff.val  = l.val  - r.val;
    diff.frac = l.frac - r.frac;
  ELSE
    diff.val  = l.val  - r.val - 1;
    diff.frac = l.frac + 100000000 - r.frac;
  END IF;
ELSE
  IF (l.val = r.val) AND (l.frac >= r.frac)
  THEN
    diff.val  = 0;
    diff.frac = l.frac - r.frac;
    ok = TRUE;
  ELSE
    diff = (-1, -1);
    ok = FALSE;
  END IF;
END IF;

RETURN;
END $$;

COMMENT ON FUNCTION amount_left_minus_right
  IS 'Subtracts the right amount from the left and returns the difference and TRUE, if the left amount is larger than the right, or an invalid amount and FALSE otherwise.';


---------------------------------------------------------------------------
--                   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 exchange.exchange_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 exchange.%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 exchange.%s_default'
              ' PARTITION OF %s'
              ' DEFAULT'
             ,rec.name
             ,rec.name
            );
          ELSE
            -- Hash partition
            EXECUTE FORMAT(
              'CREATE TABLE exchange.%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 exchange.%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 exchange.%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 exchange.%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 exchange.%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 exchange.%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 exchange.%s_table_%s (%s)'::text
          ,rec.action
          ,rec.name
          ,NULL
        );
      END IF;
    WHEN 'master'
    THEN
      EXECUTE FORMAT(
        'SELECT exchange.%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 exchange.exchange_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;