diff options
Diffstat (limited to 'src/exchangedb/0002-deposits.sql')
| -rw-r--r-- | src/exchangedb/0002-deposits.sql | 342 |
1 files changed, 298 insertions, 44 deletions
diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql index 35210443e..874b33ccc 100644 --- a/src/exchangedb/0002-deposits.sql +++ b/src/exchangedb/0002-deposits.sql @@ -14,8 +14,8 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_deposits( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_deposits( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,13 +23,12 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'deposits'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' - ',shard INT8 NOT NULL' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed??? + 'CREATE TABLE %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',partition 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' @@ -43,43 +42,106 @@ BEGIN ',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' -- REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE' + ',policy_details_serial_id INT8' ') %s ;' ,table_name ,'PARTITION BY HASH (coin_pub)' - ,shard_suffix + ,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 +$$; - table_name = concat_ws('_', table_name, shard_suffix); +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 ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' - 'ON ' || table_name || ' ' - '(coin_pub);' + '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)' ); - END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( - IN partition_suffix VARCHAR -) + +CREATE FUNCTION foreign_table_deposits() RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' - 'PRIMARY KEY (deposit_serial_id) ' - ',ADD CONSTRAINT deposits_' || partition_suffix || '_coin_pub_merchant_pub_h_contract_terms_key ' - 'UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' + ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',ADD CONSTRAINT ' || table_name || '_foreign_coin_id' + ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' + ',ADD CONSTRAINT ' || table_name || '_foreign_policy_details' + ' REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE' ); END $$; -CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( - IN shard_suffix VARCHAR DEFAULT NULL + +CREATE FUNCTION create_table_deposits_by_ready( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -87,33 +149,47 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'deposits_by_ready'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + 'CREATE TABLE %I' '(wire_deadline INT8 NOT NULL' - ',shard INT8 NOT NULL' + ',partition 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)' - ,shard_suffix + ,partition_suffix ); + PERFORM comment_partitioned_table( + 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER below' + ,table_name + ,partition_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +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 IF NOT EXISTS ' || table_name || '_main_index ' + 'CREATE INDEX ' || table_name || '_main_index ' 'ON ' || table_name || ' ' - '(wire_deadline ASC, shard ASC, coin_pub);' + '(wire_deadline ASC, partition ASC, coin_pub);' ); - END $$; -CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_deposits_for_matching( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -121,9 +197,8 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'deposits_for_matching'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + '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 @@ -131,21 +206,175 @@ BEGIN ') %s ;' ,table_name ,'PARTITION BY RANGE (refund_deadline)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'Enables fast lookups for deposits_iterate_matching, auto-populated via TRIGGER below' + ,table_name + ,partition_suffix ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +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 IF NOT EXISTS ' || table_name || '_main_index ' - 'ON ' || table_name || ' ' + '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 @@ -158,13 +387,38 @@ INSERT INTO exchange_tables ,'create' ,TRUE ,FALSE), - ('deposits_by_ready' -- FIXME: do this? + ('deposits' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('deposits' + ,'exchange-0002' + ,'foreign' + ,TRUE + ,FALSE), + ('deposits_by_ready' ,'exchange-0002' ,'create' ,TRUE ,FALSE), - ('deposits_for_matching' -- FIXME: do this? + ('deposits_by_ready' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('deposits_for_matching' ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('deposits_for_matching' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('deposits' + ,'exchange-0002' + ,'master' + ,TRUE ,FALSE); |