diff options
author | Christian Grothoff <christian@grothoff.org> | 2022-11-27 14:05:47 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2022-11-27 14:05:47 +0100 |
commit | a322770d290cae69e7d2f7629ee575e068254428 (patch) | |
tree | 75a80ac74d165fa0dd00df6095ad0c482d706da5 /src/exchangedb/0002-refunds.sql | |
parent | be2c11a1797d8d16b86439a80a4f110f82bb5829 (diff) | |
download | exchange-a322770d290cae69e7d2f7629ee575e068254428.tar.xz |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-refunds.sql')
-rw-r--r-- | src/exchangedb/0002-refunds.sql | 79 |
1 files changed, 58 insertions, 21 deletions
diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql index 509cc7d00..823466942 100644 --- a/src/exchangedb/0002-refunds.sql +++ b/src/exchangedb/0002-refunds.sql @@ -14,8 +14,8 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_refunds( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_refunds( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -24,46 +24,78 @@ DECLARE table_name VARCHAR DEFAULT 'refunds'; BEGIN PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' + 'CREATE TABLE %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8 NOT NULL' ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)' ',rtransaction_id INT8 NOT NULL' ',amount_with_fee_val INT8 NOT NULL' ',amount_with_fee_frac INT4 NOT NULL' - -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! ') %s ;' ,table_name ,'PARTITION BY HASH (coin_pub)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.' + ,'deposit_serial_id' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund' + ,'rtransaction_id' + ,table_name + ,partition_suffix ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_refunds ( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; +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 || '_refund_serial_id_key' + ' UNIQUE (refund_serial_id) ' + ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + ); END $$; -CREATE OR REPLACE FUNCTION constrain0002_table_refunds ( - IN partition_suffix VARCHAR DEFAULT NULL -) + +CREATE FUNCTION foreign_table_refunds () RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; BEGIN EXECUTE FORMAT ( - -- FIXME: '_' issue if partition_suffix is NULL - -- => solve with general ALTER TABLE helper function! - 'ALTER TABLE refunds_' || partition_suffix || ' ' - 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' - 'UNIQUE (refund_serial_id) ' - ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' + ' REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',ADD CONSTRAINT ' || table_name || '_foreign_deposit' + ' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' ); END $$; @@ -83,6 +115,11 @@ INSERT INTO exchange_tables ,FALSE), ('refunds' ,'exchange-0002' - ,'constrain0002' + ,'constrain' + ,TRUE + ,FALSE), + ('refunds' + ,'exchange-0002' + ,'foreign' ,TRUE ,FALSE); |