exchange

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

0002-reserves_in.sql (4289B)

      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_reserves_in(
     18   IN partition_suffix TEXT DEFAULT NULL
     19 )
     20 RETURNS VOID
     21 LANGUAGE plpgsql
     22 AS $$
     23 DECLARE
     24   table_name TEXT default 'reserves_in';
     25 BEGIN
     26   PERFORM create_partitioned_table(
     27     'CREATE TABLE %I'
     28       '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
     29       ',reserve_pub BYTEA PRIMARY KEY'
     30       ',wire_reference INT8 NOT NULL'
     31       ',credit taler_amount NOT NULL'
     32       ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)'
     33       ',exchange_account_section TEXT NOT NULL'
     34       ',execution_date INT8 NOT NULL'
     35     ') %s ;'
     36     ,table_name
     37     ,'PARTITION BY HASH (reserve_pub)'
     38     ,partition_suffix
     39   );
     40   PERFORM comment_partitioned_table(
     41      'list of transfers of funds into the reserves, one per incoming wire transfer'
     42     ,table_name
     43     ,partition_suffix
     44   );
     45   PERFORM comment_partitioned_column(
     46      'Identifies the debited bank account and KYC status'
     47     ,'wire_source_h_payto'
     48     ,table_name
     49     ,partition_suffix
     50   );
     51   PERFORM comment_partitioned_column(
     52      'Public key of the reserve. Private key signifies ownership of the remaining balance.'
     53     ,'reserve_pub'
     54     ,table_name
     55     ,partition_suffix
     56   );
     57   PERFORM comment_partitioned_column(
     58      'Amount that was transferred into the reserve'
     59     ,'credit'
     60     ,table_name
     61     ,partition_suffix
     62   );
     63 END $$;
     64 
     65 
     66 CREATE FUNCTION constrain_table_reserves_in(
     67   IN partition_suffix TEXT
     68 )
     69 RETURNS void
     70 LANGUAGE plpgsql
     71 AS $$
     72 DECLARE
     73   table_name TEXT default 'reserves_in';
     74 BEGIN
     75   table_name = concat_ws('_', table_name, partition_suffix);
     76   EXECUTE FORMAT (
     77     'ALTER TABLE ' || table_name ||
     78     ' ADD CONSTRAINT ' || table_name || '_reserve_in_serial_id_key'
     79     ' UNIQUE (reserve_in_serial_id)'
     80   );
     81   EXECUTE FORMAT (
     82     'CREATE INDEX ' || table_name || '_by_reserve_in_serial_id_index '
     83     'ON ' || table_name || ' '
     84     '(reserve_in_serial_id);'
     85   );
     86   EXECUTE FORMAT (
     87     'CREATE INDEX ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx '
     88     'ON ' || table_name || ' '
     89     '(exchange_account_section'
     90     ',reserve_in_serial_id ASC'
     91     ');'
     92   );
     93   EXECUTE FORMAT (
     94     'COMMENT ON INDEX ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx '
     95     'IS ' || quote_literal ('for pg_select_reserves_in_above_serial_id_by_account') || ';'
     96   );
     97 
     98 END
     99 $$;
    100 
    101 CREATE FUNCTION foreign_table_reserves_in()
    102 RETURNS VOID
    103 LANGUAGE plpgsql
    104 AS $$
    105 DECLARE
    106   table_name TEXT DEFAULT 'reserves_in';
    107 BEGIN
    108   EXECUTE FORMAT (
    109     'ALTER TABLE ' || table_name ||
    110     ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub'
    111     ' FOREIGN KEY (reserve_pub) '
    112     ' REFERENCES reserves(reserve_pub) ON DELETE CASCADE'
    113   );
    114   EXECUTE FORMAT (
    115     'ALTER TABLE ' || table_name ||
    116     ' ADD CONSTRAINT ' || table_name || '_wire_target_h_payto_foreign'
    117     ' FOREIGN KEY (wire_source_h_payto)'
    118     ' REFERENCES wire_targets (wire_target_h_payto)'
    119     ' ON DELETE RESTRICT'
    120   );
    121 END $$;
    122 
    123 
    124 CREATE FUNCTION master_table_reserves_in()
    125 RETURNS VOID
    126 LANGUAGE plpgsql
    127 AS $$
    128 BEGIN
    129   CREATE TRIGGER reserves_in_on_insert
    130     AFTER INSERT
    131      ON reserves_in
    132      FOR EACH ROW EXECUTE FUNCTION reserves_in_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     ('reserves_in'
    144     ,'exchange-0002'
    145     ,'create'
    146     ,TRUE
    147     ,FALSE),
    148     ('reserves_in'
    149     ,'exchange-0002'
    150     ,'constrain'
    151     ,TRUE
    152     ,FALSE),
    153     ('reserves_in'
    154     ,'exchange-0002'
    155     ,'foreign'
    156     ,TRUE
    157     ,FALSE),
    158     ('reserves_in'
    159     ,'exchange-0002'
    160     ,'master'
    161     ,TRUE
    162     ,FALSE);