exchange

Base system with REST service to issue digital coins, run by the payment service provider
Log | Files | Refs | Submodules | README | LICENSE

0002-refunds.sql (4350B)

      1 --
      2 -- This file is part of TALER
      3 -- Copyright (C) 2014--2023 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 CREATE FUNCTION create_table_refunds(
     18   IN partition_suffix TEXT DEFAULT NULL
     19 )
     20 RETURNS VOID
     21 LANGUAGE plpgsql
     22 AS $$
     23 DECLARE
     24   table_name TEXT DEFAULT 'refunds';
     25 BEGIN
     26   PERFORM create_partitioned_table(
     27     'CREATE TABLE %I'
     28       '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
     29       ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
     30       ',batch_deposit_serial_id INT8 NOT NULL'
     31       ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
     32       ',rtransaction_id INT8 NOT NULL'
     33       ',amount_with_fee taler_amount NOT NULL'
     34     ') %s ;'
     35     ,table_name
     36     ,'PARTITION BY HASH (coin_pub)'
     37     ,partition_suffix
     38   );
     39   PERFORM comment_partitioned_table(
     40      'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.'
     41     ,table_name
     42     ,partition_suffix
     43   );
     44   PERFORM comment_partitioned_column(
     45      'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.'
     46     ,'batch_deposit_serial_id'
     47     ,table_name
     48     ,partition_suffix
     49   );
     50   PERFORM comment_partitioned_column(
     51      'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund'
     52     ,'rtransaction_id'
     53     ,table_name
     54     ,partition_suffix
     55   );
     56 END
     57 $$;
     58 
     59 
     60 CREATE FUNCTION constrain_table_refunds (
     61   IN partition_suffix TEXT DEFAULT NULL
     62 )
     63 RETURNS void
     64 LANGUAGE plpgsql
     65 AS $$
     66 DECLARE
     67   table_name TEXT DEFAULT 'refunds';
     68 BEGIN
     69   table_name = concat_ws('_', table_name, partition_suffix);
     70   EXECUTE FORMAT (
     71     'CREATE INDEX ' || table_name || '_by_coin_pub_index '
     72     'ON ' || table_name || ' '
     73     '(coin_pub);'
     74   );
     75   EXECUTE FORMAT (
     76     'ALTER TABLE ' || table_name ||
     77     ' ADD CONSTRAINT ' || table_name || '_refund_serial_id_key'
     78     ' UNIQUE (refund_serial_id) '
     79     ',ADD PRIMARY KEY (batch_deposit_serial_id, coin_pub, rtransaction_id) '
     80   );
     81 END
     82 $$;
     83 
     84 
     85 CREATE FUNCTION foreign_table_refunds ()
     86 RETURNS void
     87 LANGUAGE plpgsql
     88 AS $$
     89 DECLARE
     90   table_name TEXT DEFAULT 'refunds';
     91 BEGIN
     92   EXECUTE FORMAT (
     93     'ALTER TABLE ' || table_name ||
     94     ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
     95     ' FOREIGN KEY (coin_pub) '
     96     ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
     97     ',ADD CONSTRAINT ' || table_name || '_foreign_deposit'
     98     ' FOREIGN KEY (batch_deposit_serial_id) '
     99     ' REFERENCES batch_deposits (batch_deposit_serial_id) ON DELETE CASCADE'
    100   );
    101 END
    102 $$;
    103 
    104 
    105 CREATE OR REPLACE FUNCTION refunds_insert_trigger()
    106   RETURNS trigger
    107   LANGUAGE plpgsql
    108   AS $$
    109 BEGIN
    110   INSERT INTO exchange.coin_history
    111     (coin_pub
    112     ,table_name
    113     ,serial_id)
    114  VALUES
    115      (NEW.coin_pub
    116     ,'refunds'
    117     ,NEW.refund_serial_id);
    118   RETURN NEW;
    119 END $$;
    120 COMMENT ON FUNCTION refunds_insert_trigger()
    121   IS 'Automatically generate coin history entry.';
    122 
    123 
    124 CREATE FUNCTION master_table_refunds()
    125 RETURNS VOID
    126 LANGUAGE plpgsql
    127 AS $$
    128 BEGIN
    129   CREATE TRIGGER refunds_on_insert
    130     AFTER INSERT
    131      ON refunds
    132      FOR EACH ROW EXECUTE FUNCTION refunds_insert_trigger();
    133 END $$;
    134 
    135 
    136 INSERT INTO exchange_tables
    137     (name
    138     ,version
    139     ,action
    140     ,partitioned
    141     ,by_range)
    142   VALUES
    143     ('refunds'
    144     ,'exchange-0002'
    145     ,'create'
    146     ,TRUE
    147     ,FALSE),
    148     ('refunds'
    149     ,'exchange-0002'
    150     ,'constrain'
    151     ,TRUE
    152     ,FALSE),
    153     ('refunds'
    154     ,'exchange-0002'
    155     ,'foreign'
    156     ,TRUE
    157     ,FALSE),
    158     ('refunds'
    159     ,'exchange-0002'
    160     ,'master'
    161     ,TRUE
    162     ,FALSE);