exchange

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

0002-legitimization_measures.sql (4363B)

      1 --
      2 -- This file is part of TALER
      3 -- Copyright (C) 2024 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_legitimization_measures(
     18   IN partition_suffix TEXT DEFAULT NULL
     19 )
     20 RETURNS VOID
     21 LANGUAGE plpgsql
     22 AS $$
     23 BEGIN
     24   PERFORM create_partitioned_table(
     25     'CREATE TABLE %I'
     26       '(legitimization_measure_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY'
     27       ',access_token BYTEA NOT NULL CHECK (LENGTH(access_token)=32)'
     28       ',start_time INT8 NOT NULL'
     29       ',jmeasures TEXT NOT NULL'
     30       ',display_priority INT4 NOT NULL' -- DEAD?
     31       ',is_finished BOOL NOT NULL DEFAULT(FALSE)'
     32     ') %s ;'
     33     ,'legitimization_measures'
     34     ,'PARTITION BY HASH (access_token)'
     35     ,partition_suffix
     36   );
     37   PERFORM comment_partitioned_table(
     38      'List of required legitimizations by account'
     39     ,'legitimization_measures'
     40     ,partition_suffix
     41   );
     42   PERFORM comment_partitioned_column(
     43      'unique ID for this legitimization process at the exchange'
     44     ,'legitimization_measure_serial_id'
     45     ,'legitimization_measures'
     46     ,partition_suffix
     47   );
     48   PERFORM comment_partitioned_column(
     49      'foreign key linking the entry to the kyc_targets table, NOT a primary key (multiple legitimizations are possible per account)'
     50     ,'access_token'
     51     ,'legitimization_measures'
     52     ,partition_suffix
     53   );
     54   PERFORM comment_partitioned_column(
     55      'Time when the measure was triggered (by decision or rule)'
     56     ,'start_time'
     57     ,'legitimization_measures'
     58     ,partition_suffix
     59   );
     60   PERFORM comment_partitioned_column(
     61      'JSON object of type LegitimizationMeasures with KYC/AML measures for the account encoded'
     62     ,'jmeasures'
     63     ,'legitimization_measures'
     64     ,partition_suffix
     65   );
     66   PERFORM comment_partitioned_column(
     67      'Display priority of the rule that triggered this measure; if in the meantime another rule also triggers, the measure is only replaced if the new rule has a higher display priority; probably not really useful, as right now there is only ever one set of legitimization_measures active at any time, might be removed in the future'
     68     ,'display_priority'
     69     ,'legitimization_measures'
     70     ,partition_suffix
     71   );
     72   PERFORM comment_partitioned_column(
     73      'Set to TRUE if this set of measures was processed; used to avoid indexing measures that are done'
     74     ,'is_finished'
     75     ,'legitimization_measures'
     76     ,partition_suffix
     77   );
     78 END
     79 $$;
     80 
     81 
     82 
     83 CREATE FUNCTION constrain_table_legitimization_measures(
     84   IN partition_suffix TEXT
     85 )
     86 RETURNS VOID
     87 LANGUAGE plpgsql
     88 AS $$
     89 DECLARE
     90   table_name TEXT DEFAULT 'legitimization_measures';
     91 BEGIN
     92   table_name = concat_ws('_', table_name, partition_suffix);
     93 
     94   EXECUTE FORMAT (
     95     'ALTER TABLE ' || table_name ||
     96     ' ADD CONSTRAINT ' || table_name || '_serial_id_key'
     97     ' UNIQUE (legitimization_measure_serial_id)');
     98   EXECUTE FORMAT (
     99     'CREATE INDEX ' || table_name || '_by_access_token'
    100     ' ON ' || table_name ||
    101     ' (access_token)'
    102     ' WHERE NOT is_finished' ||
    103     ';'
    104   );
    105 END
    106 $$;
    107 
    108 
    109 CREATE FUNCTION foreign_table_legitimization_measures()
    110 RETURNS void
    111 LANGUAGE plpgsql
    112 AS $$
    113 DECLARE
    114   table_name TEXT DEFAULT 'legitimization_measures';
    115 BEGIN
    116   EXECUTE FORMAT (
    117     'ALTER TABLE ' || table_name ||
    118     ' ADD CONSTRAINT ' || table_name || '_foreign_key_access_token'
    119     ' FOREIGN KEY (access_token)'
    120     ' REFERENCES wire_targets (access_token)'
    121     '  ON DELETE CASCADE');
    122 END
    123 $$;
    124 
    125 
    126 INSERT INTO exchange_tables
    127     (name
    128     ,version
    129     ,action
    130     ,partitioned
    131     ,by_range)
    132   VALUES
    133     ('legitimization_measures'
    134     ,'exchange-0002'
    135     ,'create'
    136     ,TRUE
    137     ,FALSE),
    138     ('legitimization_measures'
    139     ,'exchange-0002'
    140     ,'constrain'
    141     ,TRUE
    142     ,FALSE),
    143     ('legitimization_measures'
    144     ,'exchange-0002'
    145     ,'foreign'
    146     ,TRUE
    147     ,FALSE);