diff options
Diffstat (limited to 'src/exchangedb/0002-recoup.sql')
-rw-r--r-- | src/exchangedb/0002-recoup.sql | 267 |
1 files changed, 267 insertions, 0 deletions
diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql new file mode 100644 index 000000000..4b3452498 --- /dev/null +++ b/src/exchangedb/0002-recoup.sql @@ -0,0 +1,267 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2023 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' + ',reserve_out_serial_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_blind_ev of the recouped coin and provides the link to the credited reserve.' + ,'reserve_out_serial_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 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_reserves_out' + ' FOREIGN KEY (reserve_out_serial_id) ' + ' REFERENCES reserves_out (reserve_out_serial_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' + '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_out_serial_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 reserve_out_serial_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 || ' ' + '(reserve_out_serial_id);' + ); +END +$$; + + +CREATE FUNCTION recoup_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO recoup_by_reserve + (reserve_out_serial_id + ,coin_pub) + VALUES + (NEW.reserve_out_serial_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 reserves_out rout + JOIN reserves res + USING (reserve_uuid) + WHERE rout.reserve_out_serial_id = NEW.reserve_out_serial_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 FUNCTION recoup_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM recoup_by_reserve + WHERE reserve_out_serial_id = OLD.reserve_out_serial_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); |