From a322770d290cae69e7d2f7629ee575e068254428 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sun, 27 Nov 2022 14:05:47 +0100 Subject: more work on SQL refactoring --- src/exchangedb/0002-refresh_revealed_coins.sql | 119 ++++++++++++++++++++----- 1 file changed, 96 insertions(+), 23 deletions(-) (limited to 'src/exchangedb/0002-refresh_revealed_coins.sql') diff --git a/src/exchangedb/0002-refresh_revealed_coins.sql b/src/exchangedb/0002-refresh_revealed_coins.sql index e4b44557b..a7d4d4395 100644 --- a/src/exchangedb/0002-refresh_revealed_coins.sql +++ b/src/exchangedb/0002-refresh_revealed_coins.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see -- -CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( +CREATE FUNCTION create_table_refresh_revealed_coins( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -23,52 +23,115 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'refresh_revealed_coins'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + 'CREATE TABLE %I' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',melt_serial_id INT8 NOT NULL' ',freshcoin_index INT4 NOT NULL' ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' - ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' - ',coin_ev BYTEA NOT NULL' -- UNIQUE' - ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' + ',denominations_serial INT8 NOT NULL' + ',coin_ev BYTEA NOT NULL' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' ',ev_sig BYTEA NOT NULL' ',ewv BYTEA NOT NULL' - -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard ') %s ;' ,table_name ,'PARTITION BY HASH (melt_serial_id)' ,shard_suffix ); + PEFORM comment_partitioned_table( + 'Revelations about the new coins that are to be created during a melting session.' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'needed for exchange-auditor replication logic' + ,'rrc_serial' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'Identifies the refresh commitment (rc) of the melt operation.' + ,'melt_serial_id' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'index of the fresh coin being created (one melt operation may result in multiple fresh coins)' + ,'freshcoin_index' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'envelope of the new coin to be signed' + ,'coin_ev' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'exchange contributed values in the creation of the fresh coin (see /csr)' + ,'ewv' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'hash of the envelope of the new coin to be signed (for lookups)' + ,'h_coin_ev' + ,table_name + ,shard_suffix + ); + PEFORM comment_partitioned_column( + 'exchange signature over the envelope' + ,'ev_sig' + ,table_name + ,shard_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_refresh_revealed_coins( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; +BEGIN + table_name = concat_ws('_', table_name, shard_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' + 'CREATE INDEX ' || table_name || '_coins_by_melt_serial_id_index ' 'ON ' || table_name || ' ' '(melt_serial_id);' ); - + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_rrc_serial_key' + ' UNIQUE (rrc_serial) ' + ',ADD CONSTRAINT ' || table_name || '_coin_ev_key' + ' UNIQUE (coin_ev) ' + ',ADD CONSTRAINT ' || table_name || '_h_coin_ev_key' + ' UNIQUE (h_coin_ev) ' + ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index)' + ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( - IN partition_suffix VARCHAR -) + +CREATE FUNCTION foreign_table_refresh_revealed_coins() RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' - 'UNIQUE (rrc_serial) ' - ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' - 'UNIQUE (coin_ev) ' - ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' - 'UNIQUE (h_coin_ev) ' - ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_melt' + ' REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',ADD CONSTRAINT ' || table_name || '_foreign_denom' + ' REFERENCES denominations (denominations_serial) ON DELETE CASCADE' ); END $$; @@ -85,4 +148,14 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('refresh_revealed_coins' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('refresh_revealed_coins' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); -- cgit v1.2.3