exchange

0002-recoup.sql (6705B)

---

 --
 -- This file is part of TALER
 -- Copyright (C) 2025 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/>
 --
 
 CREATE FUNCTION create_table_recoup(
   IN partition_suffix TEXT DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 DECLARE
   table_name TEXT DEFAULT 'recoup';
 BEGIN
 
   PERFORM create_partitioned_table(
     'CREATE TABLE %I'
       '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
       ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
       ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
       ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
       ',amount taler_amount NOT NULL'
       ',recoup_timestamp INT8 NOT NULL'
       ',withdraw_id INT8 NOT NULL'
     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (coin_pub);'
     ,partition_suffix
   );
   PERFORM comment_partitioned_table(
      'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.'
     ,table_name
     ,partition_suffix
   );
   PERFORM comment_partitioned_column(
      'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'
     ,'coin_pub'
     ,table_name
     ,partition_suffix
   );
   PERFORM comment_partitioned_column(
      'Identifies the h_commitment of the recouped coin and provides the link to the credited reserve.'
     ,'withdraw_id'
     ,table_name
     ,partition_suffix
   );
   PERFORM comment_partitioned_column(
      'Signature by the coin affirming the recoup, of type TALER_SIGNATURE_WALLET_COIN_RECOUP'
     ,'coin_sig'
     ,table_name
     ,partition_suffix
   );
   PERFORM comment_partitioned_column(
      'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.'
     ,'coin_blind'
     ,table_name
     ,partition_suffix
   );
 END
 $$;
 
 
 CREATE FUNCTION constrain_table_recoup(
   IN partition_suffix TEXT
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 DECLARE
   table_name TEXT DEFAULT 'recoup';
 BEGIN
   table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
     'CREATE INDEX ' || table_name || '_by_coin_pub_index '
     'ON ' || table_name || ' '
     '(coin_pub);'
   );
   EXECUTE FORMAT (
     'ALTER TABLE ' || table_name ||
     ' ADD CONSTRAINT ' || table_name || '_recoup_uuid_key'
     ' UNIQUE (recoup_uuid) '
   );
 END
 $$;
 
 
 CREATE OR REPLACE FUNCTION foreign_table_recoup()
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 DECLARE
   table_name TEXT DEFAULT 'recoup';
 BEGIN
   EXECUTE FORMAT (
     'ALTER TABLE ' || table_name ||
     ' ADD CONSTRAINT ' || table_name || '_foreign_withdraw'
     ' FOREIGN KEY (withdraw_id) '
     ' REFERENCES withdraw (withdraw_id) ON DELETE CASCADE'
     ',ADD CONSTRAINT ' || table_name || '_foreign_coin_pub'
     ' FOREIGN KEY (coin_pub) '
     ' REFERENCES known_coins (coin_pub)'
   );
 END
 $$;
 
 
 CREATE FUNCTION create_table_recoup_by_reserve(
   IN partition_suffix TEXT DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 DECLARE
   table_name TEXT DEFAULT 'recoup_by_reserve';
 BEGIN
   PERFORM create_partitioned_table(
   'CREATE TABLE %I'
     '(withdraw_id INT8 NOT NULL' -- REFERENCES withdraw (withdraw_id) ON DELETE CASCADE
     ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (withdraw_id)'
     ,partition_suffix
   );
   PERFORM comment_partitioned_table(
      'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by withdraw_id.'
     ,table_name
     ,partition_suffix
   );
 END
 $$;
 
 
 CREATE FUNCTION constrain_table_recoup_by_reserve(
   IN partition_suffix TEXT DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 DECLARE
   table_name TEXT DEFAULT 'recoup_by_reserve';
 BEGIN
   table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
     'CREATE INDEX ' || table_name || '_main_index '
     'ON ' || table_name || ' '
     '(withdraw_id);'
   );
 END
 $$;
 
 
 CREATE OR REPLACE FUNCTION recoup_insert_trigger()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $$
 BEGIN
   INSERT INTO recoup_by_reserve
     (withdraw_id
     ,coin_pub)
   VALUES
     (NEW.withdraw_id
     ,NEW.coin_pub);
   INSERT INTO coin_history
     (coin_pub
     ,table_name
     ,serial_id)
   VALUES
     (NEW.coin_pub
     ,'recoup'
     ,NEW.recoup_uuid);
   INSERT INTO reserve_history
     (reserve_pub
     ,table_name
     ,serial_id)
   SELECT
      res.reserve_pub
     ,'recoup'
     ,NEW.recoup_uuid
   FROM withdraw wd
   JOIN reserves res
     USING (reserve_pub)
     WHERE wd.withdraw_id = NEW.withdraw_id;
   RETURN NEW;
 END $$;
 COMMENT ON FUNCTION recoup_insert_trigger()
   IS 'Replicates recoup inserts into recoup_by_reserve table and updates the coin_history table.';
 
 
 CREATE OR REPLACE FUNCTION recoup_delete_trigger()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $$
 BEGIN
   DELETE FROM recoup_by_reserve
    WHERE withdraw_id = OLD.withdraw_id
      AND coin_pub = OLD.coin_pub;
   RETURN OLD;
 END $$;
 COMMENT ON FUNCTION recoup_delete_trigger()
   IS 'Replicate recoup deletions into recoup_by_reserve table.';
 
 
 CREATE FUNCTION master_table_recoup()
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
 BEGIN
   CREATE TRIGGER recoup_on_insert
     AFTER INSERT
      ON recoup
      FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
   CREATE TRIGGER recoup_on_delete
     AFTER DELETE
       ON recoup
      FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
 END
 $$;
 
 
 INSERT INTO exchange_tables
     (name
     ,version
     ,action
     ,partitioned
     ,by_range)
   VALUES
     ('recoup'
     ,'exchange-0002'
     ,'create'
     ,TRUE
     ,FALSE),
     ('recoup'
     ,'exchange-0002'
     ,'constrain'
     ,TRUE
     ,FALSE),
     ('recoup'
     ,'exchange-0002'
     ,'foreign'
     ,TRUE
     ,FALSE),
     ('recoup_by_reserve'
     ,'exchange-0002'
     ,'create'
     ,TRUE
     ,FALSE),
     ('recoup_by_reserve'
     ,'exchange-0002'
     ,'constrain'
     ,TRUE
     ,FALSE),
     ('recoup'
     ,'exchange-0002'
     ,'master'
     ,TRUE
     ,FALSE);