diff options
Diffstat (limited to 'src/exchangedb/0002-deposits.sql')
-rw-r--r-- | src/exchangedb/0002-deposits.sql | 419 |
1 files changed, 0 insertions, 419 deletions
diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql deleted file mode 100644 index d8afdac84..000000000 --- a/src/exchangedb/0002-deposits.sql +++ /dev/null @@ -1,419 +0,0 @@ --- --- 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_deposits( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE %I' - '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',shard INT8 NOT NULL' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' - ',known_coin_id INT8 NOT NULL' -- FIXME: column needed!? - ',amount_with_fee_val INT8 NOT NULL' - ',amount_with_fee_frac INT4 NOT NULL' - ',wallet_timestamp INT8 NOT NULL' - ',exchange_timestamp INT8 NOT NULL' - ',refund_deadline INT8 NOT NULL' - ',wire_deadline INT8 NOT NULL' - ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' - ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' - ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' - ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' - ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' - ',done BOOLEAN NOT NULL DEFAULT FALSE' - ',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE' - ',policy_details_serial_id INT8' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (coin_pub)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Used for load sharding in the materialized indices. Should be set based on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.' - ,'shard' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Used for garbage collection' - ,'known_coin_id' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Identifies the target bank account and KYC status' - ,'wire_target_h_payto' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Salt used when hashing the payto://-URI to get the h_wire' - ,'wire_salt' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Set to TRUE once we have included this deposit in some aggregate wire transfer to the merchant' - ,'done' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'True if the aggregation of the deposit is currently blocked by some policy extension mechanism. Used to filter out deposits that must not be processed by the canonical deposit logic.' - ,'policy_blocked' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'References policy extensions table, NULL if extensions are not used' - ,'policy_details_serial_id' - ,table_name - ,partition_suffix - ); -END -$$; - - -CREATE FUNCTION constrain_table_deposits( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_deposit_serial_id_pkey' - ' PRIMARY KEY (deposit_serial_id) ' - ',ADD CONSTRAINT ' || table_name || '_coin_pub_merchant_pub_h_contract_terms_key' - ' UNIQUE (coin_pub, merchant_pub, h_contract_terms)' - ); - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_by_ready ' - 'ON ' || table_name || ' ' - '(wire_deadline ASC' - ',shard ASC' - ',coin_pub' - ') WHERE NOT (done OR policy_blocked);' - ); - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_for_matching ' - 'ON ' || table_name || ' ' - '(refund_deadline ASC' - ',merchant_pub' - ',coin_pub' - ') WHERE NOT (done OR policy_blocked);' - ); -END -$$; - - -CREATE FUNCTION foreign_table_deposits() -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits'; -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' - ' FOREIGN KEY (coin_pub) ' - ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE' - ',ADD CONSTRAINT ' || table_name || '_foreign_coin_id' - ' FOREIGN KEY (known_coin_id) ' - ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' - ',ADD CONSTRAINT ' || table_name || '_foreign_policy_details' - ' FOREIGN KEY (policy_details_serial_id) ' - ' REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE' - ); -END -$$; - - -CREATE FUNCTION create_table_deposits_by_ready( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits_by_ready'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE %I' - '(wire_deadline INT8 NOT NULL' - ',shard INT8 NOT NULL' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' - ',deposit_serial_id INT8' - ') %s ;' - ,table_name - ,'PARTITION BY RANGE (wire_deadline)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER below' - ,table_name - ,partition_suffix - ); -END -$$; - - -CREATE FUNCTION constrain_table_deposits_by_ready( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits_by_ready'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' - '(wire_deadline ASC, shard ASC, coin_pub);' - ); -END -$$; - - -CREATE FUNCTION create_table_deposits_for_matching( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits_for_matching'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE %I' - '(refund_deadline INT8 NOT NULL' - ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ',deposit_serial_id INT8' - ') %s ;' - ,table_name - ,'PARTITION BY RANGE (refund_deadline)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'Enables fast lookups for deposits_iterate_matching, auto-populated via TRIGGER below' - ,table_name - ,partition_suffix - ); -END -$$; - - -CREATE FUNCTION constrain_table_deposits_for_matching( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'deposits_for_matching'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_main_index' - ' ON ' || table_name || ' ' - '(refund_deadline ASC, merchant_pub, coin_pub);' - ); -END -$$; - - -CREATE OR REPLACE FUNCTION deposits_insert_trigger() - RETURNS trigger - LANGUAGE plpgsql - AS $$ -DECLARE - is_ready BOOLEAN; -BEGIN - is_ready = NOT (NEW.done OR NEW.policy_blocked); - - IF (is_ready) - THEN - INSERT INTO exchange.deposits_by_ready - (wire_deadline - ,shard - ,coin_pub - ,deposit_serial_id) - VALUES - (NEW.wire_deadline - ,NEW.shard - ,NEW.coin_pub - ,NEW.deposit_serial_id); - INSERT INTO exchange.deposits_for_matching - (refund_deadline - ,merchant_pub - ,coin_pub - ,deposit_serial_id) - VALUES - (NEW.refund_deadline - ,NEW.merchant_pub - ,NEW.coin_pub - ,NEW.deposit_serial_id); - END IF; - RETURN NEW; -END $$; -COMMENT ON FUNCTION deposits_insert_trigger() - IS 'Replicate deposit inserts into materialized indices.'; - - -CREATE OR REPLACE FUNCTION deposits_update_trigger() - RETURNS trigger - LANGUAGE plpgsql - AS $$ -DECLARE - was_ready BOOLEAN; -DECLARE - is_ready BOOLEAN; -BEGIN - was_ready = NOT (OLD.done OR OLD.policy_blocked); - is_ready = NOT (NEW.done OR NEW.policy_blocked); - IF (was_ready AND NOT is_ready) - THEN - DELETE FROM exchange.deposits_by_ready - WHERE wire_deadline = OLD.wire_deadline - AND shard = OLD.shard - AND coin_pub = OLD.coin_pub - AND deposit_serial_id = OLD.deposit_serial_id; - DELETE FROM exchange.deposits_for_matching - WHERE refund_deadline = OLD.refund_deadline - AND merchant_pub = OLD.merchant_pub - AND coin_pub = OLD.coin_pub - AND deposit_serial_id = OLD.deposit_serial_id; - END IF; - IF (is_ready AND NOT was_ready) - THEN - INSERT INTO exchange.deposits_by_ready - (wire_deadline - ,shard - ,coin_pub - ,deposit_serial_id) - VALUES - (NEW.wire_deadline - ,NEW.shard - ,NEW.coin_pub - ,NEW.deposit_serial_id); - INSERT INTO exchange.deposits_for_matching - (refund_deadline - ,merchant_pub - ,coin_pub - ,deposit_serial_id) - VALUES - (NEW.refund_deadline - ,NEW.merchant_pub - ,NEW.coin_pub - ,NEW.deposit_serial_id); - END IF; - RETURN NEW; -END $$; -COMMENT ON FUNCTION deposits_update_trigger() - IS 'Replicate deposits changes into materialized indices.'; - - -CREATE OR REPLACE FUNCTION deposits_delete_trigger() - RETURNS trigger - LANGUAGE plpgsql - AS $$ -DECLARE - was_ready BOOLEAN; -BEGIN - was_ready = NOT (OLD.done OR OLD.policy_blocked); - - IF (was_ready) - THEN - DELETE FROM exchange.deposits_by_ready - WHERE wire_deadline = OLD.wire_deadline - AND shard = OLD.shard - AND coin_pub = OLD.coin_pub - AND deposit_serial_id = OLD.deposit_serial_id; - DELETE FROM exchange.deposits_for_matching - WHERE refund_deadline = OLD.refund_deadline - AND merchant_pub = OLD.merchant_pub - AND coin_pub = OLD.coin_pub - AND deposit_serial_id = OLD.deposit_serial_id; - END IF; - RETURN NEW; -END $$; -COMMENT ON FUNCTION deposits_delete_trigger() - IS 'Replicate deposit deletions into materialized indices.'; - - -CREATE FUNCTION master_table_deposits() -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - CREATE TRIGGER deposits_on_insert - AFTER INSERT - ON deposits - FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger(); - CREATE TRIGGER deposits_on_update - AFTER UPDATE - ON deposits - FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger(); - CREATE TRIGGER deposits_on_delete - AFTER DELETE - ON deposits - FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger(); -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('deposits' - ,'exchange-0002' - ,'create' - ,TRUE - ,FALSE), - ('deposits' - ,'exchange-0002' - ,'constrain' - ,TRUE - ,FALSE), - ('deposits' - ,'exchange-0002' - ,'foreign' - ,TRUE - ,FALSE) - ; |