From 4f75bcdca35b1ce8aa1f3db444c63f4763e28301 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sun, 27 Nov 2022 14:45:01 +0100 Subject: more work on SQL refactoring --- src/exchangedb/0002-recoup.sql | 182 +++++++++++++++++++++++++++++++++++------ 1 file changed, 158 insertions(+), 24 deletions(-) (limited to 'src/exchangedb/0002-recoup.sql') diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql index a3183610a..b8f4f4cca 100644 --- a/src/exchangedb/0002-recoup.sql +++ b/src/exchangedb/0002-recoup.sql @@ -14,8 +14,8 @@ -- TALER; see the file COPYING. If not, see -- -CREATE OR REPLACE FUNCTION create_table_recoup( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_recoup( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,51 +23,98 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'recoup'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + '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_val INT8 NOT NULL' ',amount_frac INT4 NOT NULL' ',recoup_timestamp INT8 NOT NULL' - ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' + ',reserve_out_serial_id INT8 NOT NULL' ') %s ;' ,table_name ,'PARTITION BY HASH (coin_pub);' - ,shard_suffix + ,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 +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_recoup( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + '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 add_constraints_to_recoup_partition( - IN partition_suffix VARCHAR -) + +CREATE FUNCTION foreign_table_recoup() RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE recoup_' || partition_suffix || ' ' - 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' - 'UNIQUE (recoup_uuid) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_reserves_out' + ' REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' + ',ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' + ' REFERENCES known_coins (coin_pub)' ); END $$; -CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( - IN shard_suffix VARCHAR DEFAULT NULL + +CREATE FUNCTION create_table_recoup_by_reserve( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -75,25 +122,87 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'recoup_by_reserve'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + '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)' - ,shard_suffix + ,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 +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_recoup_by_reserve( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_by_reserve'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + '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 exchange.recoup_by_reserve + (reserve_out_serial_id + ,coin_pub) + VALUES + (NEW.reserve_out_serial_id + ,NEW.coin_pub); + RETURN NEW; +END $$; +COMMENT ON FUNCTION recoup_insert_trigger() + IS 'Replicate recoup inserts into recoup_by_reserve table.'; + + +CREATE FUNCTION recoup_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM exchange.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 $$; @@ -109,4 +218,29 @@ INSERT INTO exchange_tables ,'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); -- cgit v1.2.3