diff options
| -rw-r--r-- | src/exchangedb/0002-aggregation_tracking.sql | 68 | ||||
| -rw-r--r-- | src/exchangedb/0002-aggregation_transient.sql | 41 | ||||
| -rw-r--r-- | src/exchangedb/0002-cs_nonce_locks.sql | 52 | ||||
| -rw-r--r-- | src/exchangedb/0002-prewire.sql | 55 | ||||
| -rw-r--r-- | src/exchangedb/0002-purse_decision.sql | 37 | ||||
| -rw-r--r-- | src/exchangedb/0002-purse_requests.sql | 110 | ||||
| -rw-r--r-- | src/exchangedb/0002-recoup.sql | 182 | ||||
| -rw-r--r-- | src/exchangedb/0002-recoup_refresh.sql | 106 | ||||
| -rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 176 |
9 files changed, 523 insertions, 304 deletions
diff --git a/src/exchangedb/0002-aggregation_tracking.sql b/src/exchangedb/0002-aggregation_tracking.sql index 25c394d26..d5c852e87 100644 --- a/src/exchangedb/0002-aggregation_tracking.sql +++ b/src/exchangedb/0002-aggregation_tracking.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( +CREATE FUNCTION create_table_aggregation_tracking( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -23,22 +23,43 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'aggregation_tracking'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient depost -- or something else ??? - ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + 'CREATE TABLE %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',deposit_serial_id INT8 PRIMARY KEY' + ',wtid_raw BYTEA NOT NULL' ') %s ;' ,table_name ,'PARTITION BY HASH (deposit_serial_id)' ,shard_suffix ); + PERFORM comment_partitioned_table( + 'mapping from wire transfer identifiers (WTID) to deposits (and back)' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'identifier of the wire transfer' + ,'wtid_raw' + ,table_name + ,shard_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_aggregation_tracking( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + table_name = concat_ws('_', table_name, shard_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'CREATE INDEX ' || table_name || '_by_wtid_raw_index ' 'ON ' || table_name || ' ' '(wtid_raw);' ); @@ -46,21 +67,28 @@ BEGIN 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' 'IS ' || quote_literal('for lookup_transactions') || ';' ); - + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_aggregation_serial_id_key' + ' UNIQUE (aggregation_serial_id) ' + ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( - IN partition_suffix VARCHAR -) + +CREATE FUNCTION foreign_table_aggregation_tracking() RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' - 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' - 'UNIQUE (aggregation_serial_id) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_deposit' + ' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient deposit??? + ',ADD CONSTRAINT ' || table_name || '_foreign_wtid_raw' + ' REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' ); END $$; @@ -77,4 +105,14 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('aggregation_tracking' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('aggregation_tracking' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); diff --git a/src/exchangedb/0002-aggregation_transient.sql b/src/exchangedb/0002-aggregation_transient.sql index 4739379e8..2d77e63ca 100644 --- a/src/exchangedb/0002-aggregation_transient.sql +++ b/src/exchangedb/0002-aggregation_transient.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_aggregation_transient( +CREATE FUNCTION create_table_aggregation_transient( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -23,22 +23,37 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'aggregation_transient'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(amount_val INT8 NOT NULL' - ',amount_frac INT4 NOT NULL' - ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' - ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)' - ',exchange_account_section TEXT NOT NULL' - ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' - ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' - ') %s ;' + 'CREATE TABLE %I ' + '(amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)' + ',exchange_account_section TEXT NOT NULL' + ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + PERFORM comment_partitioned_table( + 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Sum of all of the aggregated deposits (without deposit fees)' + ,'amount_val' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'identifier of the wire transfer' + ,'wtid_raw' ,table_name - ,'PARTITION BY HASH (wire_target_h_payto)' ,shard_suffix ); - END $$; diff --git a/src/exchangedb/0002-cs_nonce_locks.sql b/src/exchangedb/0002-cs_nonce_locks.sql index d34a84c8a..effc0045f 100644 --- a/src/exchangedb/0002-cs_nonce_locks.sql +++ b/src/exchangedb/0002-cs_nonce_locks.sql @@ -14,40 +14,65 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks( - shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_cs_nonce_locks( + partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + 'CREATE TABLE %I' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' ',max_denomination_serial INT8 NOT NULL' ') %s ;' ,'cs_nonce_locks' ,'PARTITION BY HASH (nonce)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash' + ,'cs_nonce_locks' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'actual nonce submitted by the client' + ,'nonce' + ,'cs_nonce_locks' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be used with' + ,'op_hash' + ,'cs_nonce_locks' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Maximum number of a CS denomination serial the nonce could be used with, for GC' + ,'max_denomination_serial' + ,'cs_nonce_locks' + ,partition_suffix ); - END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( + +CREATE FUNCTION constrain_table_cs_nonce_locks( IN partition_suffix VARCHAR ) RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'cs_nonce_locks'; BEGIN + table_name = concat_ws('_', table_name, shard_suffix); EXECUTE FORMAT ( - 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' - 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' - 'UNIQUE (cs_nonce_lock_serial_id)' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_cs_nonce_lock_serial_id_key' + ' UNIQUE (cs_nonce_lock_serial_id)' ); END $$; @@ -64,4 +89,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('cs_nonce_locks' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); diff --git a/src/exchangedb/0002-prewire.sql b/src/exchangedb/0002-prewire.sql index e26475c12..fb8dc2212 100644 --- a/src/exchangedb/0002-prewire.sql +++ b/src/exchangedb/0002-prewire.sql @@ -14,8 +14,8 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_prewire( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_prewire( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,9 +23,8 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'prewire'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + 'CREATE TABLE %I' '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' ',wire_method TEXT NOT NULL' ',finished BOOLEAN NOT NULL DEFAULT false' @@ -34,13 +33,47 @@ BEGIN ') %s ;' ,table_name ,'PARTITION BY HASH (prewire_uuid)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'pre-commit data for wire transfers we are about to execute' + ,table_name + ,partition_suffix ); + PERFORM comment_partitioned_column( + 'set to TRUE if the bank responded with a non-transient failure to our transfer request' + ,'failed' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'set to TRUE once bank confirmed receiving the wire transfer request' + ,'finished' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'serialized data to send to the bank to execute the wire transfer' + ,'buf' + ,table_name + ,partition_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_prewire( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'prewire'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' + 'CREATE INDEX ' || table_name || '_by_finished_index ' 'ON ' || table_name || ' ' '(finished);' ); @@ -50,7 +83,7 @@ BEGIN ); -- FIXME: find a way to combine these two indices? EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' + 'CREATE INDEX ' || table_name || '_by_failed_finished_index ' 'ON ' || table_name || ' ' '(failed,finished);' ); @@ -58,7 +91,6 @@ BEGIN 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' 'IS ' || quote_literal('for wire_prepare_data_get') || ';' ); - END $$; @@ -74,4 +106,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('prewire' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); diff --git a/src/exchangedb/0002-purse_decision.sql b/src/exchangedb/0002-purse_decision.sql index 2039cd931..f7a82810b 100644 --- a/src/exchangedb/0002-purse_decision.sql +++ b/src/exchangedb/0002-purse_decision.sql @@ -15,7 +15,7 @@ -- -CREATE OR REPLACE FUNCTION create_table_purse_decision( +CREATE FUNCTION create_table_purse_decision( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -24,10 +24,9 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'purse_decision'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + 'CREATE TABLE %I ' + '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',action_timestamp INT8 NOT NULL' ',refunded BOOL NOT NULL' @@ -37,23 +36,34 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - - table_name = concat_ws('_', table_name, shard_suffix); - + PERFORM comment_partitioned_table( + 'Purses that were decided upon (refund or merge)' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Public key of the purse' + ,'purse_pub' + ,table_name + ,shard_suffix + ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition( +CREATE FUNCTION constrain_table_purse_decision( IN partition_suffix VARCHAR ) RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_decision'; BEGIN + table_name = concat_ws('_', table_name, shard_suffix); EXECUTE FORMAT ( - 'ALTER TABLE purse_decision_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_decision_' || partition_suffix || '_purse_action_serial_id_key ' - 'UNIQUE (purse_decision_serial_id) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_purse_action_serial_id_key' + ' UNIQUE (purse_decision_serial_id) ' ); END $$; @@ -70,4 +80,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('purse_decision' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); diff --git a/src/exchangedb/0002-purse_requests.sql b/src/exchangedb/0002-purse_requests.sql index 9f0aef067..666546346 100644 --- a/src/exchangedb/0002-purse_requests.sql +++ b/src/exchangedb/0002-purse_requests.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_purse_requests( +CREATE FUNCTION create_table_purse_requests( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -23,10 +23,9 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'purse_requests'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + 'CREATE TABLE %I ' + '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' ',purse_creation INT8 NOT NULL' @@ -48,38 +47,102 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); + PERFORM comment_partitioned_table( + 'Requests establishing purses, associating them with a contract but without a target reserve' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Public key of the purse' + ,'purse_pub' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Local time when the purse was created. Determines applicable purse fees.' + ,'purse_creation' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'When the purse is set to expire' + ,'purse_expiration' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Hash of the contract the parties are to agree to' + ,'h_contract_terms' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'see the enum TALER_WalletAccountMergeFlags' + ,'flags' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'set to TRUE if this purse currently counts against the number of free purses in the respective reserve' + ,'in_reserve_quota' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Total amount expected to be in the purse' + ,'amount_with_fee_val' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Purse fee the client agreed to pay from the reserve (accepted by the exchange at the time the purse was created). Zero if in_reserve_quota is TRUE.' + ,'purse_fee_val' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Total amount actually in the purse (updated)' + ,'balance_val' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST' + ,'purse_sig' + ,table_name + ,shard_suffix + ); +END +$$; +CREATE FUNCTION constrain_table_purse_requests( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_requests'; +BEGIN table_name = concat_ws('_', table_name, shard_suffix); -- FIXME: change to materialized index by merge_pub! EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' + 'CREATE INDEX ' || table_name || '_merge_pub ' 'ON ' || table_name || ' ' '(merge_pub);' ); - -- FIXME: drop index on master (crosses shards)? -- Or use materialized index? (needed?) EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration ' + 'CREATE INDEX ' || table_name || '_purse_expiration ' 'ON ' || table_name || ' ' '(purse_expiration);' ); - -END -$$; - -CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN EXECUTE FORMAT ( - 'ALTER TABLE purse_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' - 'UNIQUE (purse_requests_serial_id) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_purse_requests_serial_id_key' + ' UNIQUE (purse_requests_serial_id) ' ); END $$; @@ -96,4 +159,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('purse_requests' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); 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 <http://www.gnu.org/licenses/> -- -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); diff --git a/src/exchangedb/0002-recoup_refresh.sql b/src/exchangedb/0002-recoup_refresh.sql index 9e6361a16..a5ca69a64 100644 --- a/src/exchangedb/0002-recoup_refresh.sql +++ b/src/exchangedb/0002-recoup_refresh.sql @@ -15,7 +15,7 @@ -- -CREATE OR REPLACE FUNCTION create_table_recoup_refresh( +CREATE FUNCTION create_table_recoup_refresh( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -24,53 +24,101 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'recoup_refresh'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) - ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE - ',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' - ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE' + 'CREATE TABLE %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',known_coin_id BIGINT NOT NULL' + ',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' + ',rrc_serial INT8 NOT NULL' ') %s ;' ,table_name ,'PARTITION BY HASH (coin_pub)' ,shard_suffix ); + PERFORM comment_partitioned_table( + 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!' + ,'coin_pub' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'FIXME: (To be) used for garbage collection (in the absence of foreign constraints, in the future)' + ,'known_coin_id' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).' + ,'rrc_serial' + ,table_name + ,shard_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 refresh operation.' + ,'coin_blind' + ,table_name + ,shard_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_recoup_refresh( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; +BEGIN + table_name = concat_ws('_', table_name, shard_suffix); -- FIXME: any query using this index will be slow. Materialize index or change query? -- Also: which query uses this index? EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' - 'ON ' || table_name || ' ' + 'CREATE INDEX ' || table_name || '_by_rrc_serial_index' + ' ON ' || table_name || ' ' '(rrc_serial);' ); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' - 'ON ' || table_name || ' ' + 'CREATE INDEX ' || table_name || '_by_coin_pub_index' + ' ON ' || table_name || ' ' '(coin_pub);' ); - + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_recoup_refresh_uuid_key' + ' UNIQUE (recoup_refresh_uuid) ' + ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( - IN partition_suffix VARCHAR -) + +CREATE FUNCTION foreign_table_recoup_refresh() RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' - 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' - 'UNIQUE (recoup_refresh_uuid) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_coin_pub' + ' REFERENCES known_coins (coin_pub)' + ' ADD CONSTRAINT ' || table_name || '_foreign_known_coin_id' + ' REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' + ' ADD CONSTRAINT ' || table_name || '_foreign_rrc_serial' + ' REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE' ); END $$; @@ -87,4 +135,14 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('recoup_refresh' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('recoup_refresh' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index 29412ca75..85f6c3e76 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -14,182 +14,6 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- - --- ------------------------------ aggregation_transient ---------------------------------------- - -SELECT create_table_aggregation_transient(); - -COMMENT ON TABLE aggregation_transient - IS 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated'; -COMMENT ON COLUMN aggregation_transient.amount_val - IS 'Sum of all of the aggregated deposits (without deposit fees)'; -COMMENT ON COLUMN aggregation_transient.wtid_raw - IS 'identifier of the wire transfer'; - --- ------------------------------ aggregation_tracking ---------------------------------------- - -SELECT create_table_aggregation_tracking(); - -COMMENT ON TABLE aggregation_tracking - IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)'; -COMMENT ON COLUMN aggregation_tracking.wtid_raw - IS 'identifier of the wire transfer'; - -SELECT add_constraints_to_aggregation_tracking_partition('default'); - - --- ------------------------------ recoup ---------------------------------------- - -SELECT create_table_recoup(); - -COMMENT ON TABLE recoup - IS '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.'; -COMMENT ON COLUMN recoup.coin_pub - IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'; -COMMENT ON COLUMN recoup.reserve_out_serial_id - IS 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.'; -COMMENT ON COLUMN recoup.coin_sig - IS 'Signature by the coin affirming the recoup, of type TALER_SIGNATURE_WALLET_COIN_RECOUP'; -COMMENT ON COLUMN recoup.coin_blind - IS '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.'; - -SELECT add_constraints_to_recoup_partition('default'); - - -SELECT create_table_recoup_by_reserve(); - -COMMENT ON TABLE recoup_by_reserve - IS '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.'; - -CREATE OR REPLACE 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 TRIGGER recoup_on_insert - AFTER INSERT - ON recoup - FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger(); - -CREATE OR REPLACE 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 TRIGGER recoup_on_delete - AFTER DELETE - ON recoup - FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger(); - - --- ------------------------------ recoup_refresh ---------------------------------------- - -SELECT create_table_recoup_refresh(); - -COMMENT ON TABLE recoup_refresh - IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.'; -COMMENT ON COLUMN recoup_refresh.coin_pub - IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'; -COMMENT ON COLUMN recoup_refresh.known_coin_id - IS 'FIXME: (To be) used for garbage collection (in the future)'; -COMMENT ON COLUMN recoup_refresh.rrc_serial - IS 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).'; -COMMENT ON COLUMN recoup_refresh.coin_blind - IS '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 refresh operation.'; - -SELECT add_constraints_to_recoup_refresh_partition('default'); - - --- ------------------------------ prewire ---------------------------------------- - -SELECT create_table_prewire(); - -COMMENT ON TABLE prewire - IS 'pre-commit data for wire transfers we are about to execute'; -COMMENT ON COLUMN prewire.failed - IS 'set to TRUE if the bank responded with a non-transient failure to our transfer request'; -COMMENT ON COLUMN prewire.finished - IS 'set to TRUE once bank confirmed receiving the wire transfer request'; -COMMENT ON COLUMN prewire.buf - IS 'serialized data to send to the bank to execute the wire transfer'; - --- ------------------------------ cs_nonce_locks ---------------------------------------- - -SELECT create_table_cs_nonce_locks(); - -COMMENT ON TABLE cs_nonce_locks - IS 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash'; -COMMENT ON COLUMN cs_nonce_locks.nonce - IS 'actual nonce submitted by the client'; -COMMENT ON COLUMN cs_nonce_locks.op_hash - IS 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be used with'; -COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial - IS 'Maximum number of a CS denomination serial the nonce could be used with, for GC'; - -SELECT add_constraints_to_cs_nonce_locks_partition('default'); - - --- ------------------------------ purse_requests ---------------------------------------- - -SELECT create_table_purse_requests(); - -COMMENT ON TABLE purse_requests - IS 'Requests establishing purses, associating them with a contract but without a target reserve'; -COMMENT ON COLUMN purse_requests.purse_pub - IS 'Public key of the purse'; -COMMENT ON COLUMN purse_requests.purse_creation - IS 'Local time when the purse was created. Determines applicable purse fees.'; -COMMENT ON COLUMN purse_requests.purse_expiration - IS 'When the purse is set to expire'; -COMMENT ON COLUMN purse_requests.h_contract_terms - IS 'Hash of the contract the parties are to agree to'; -COMMENT ON COLUMN purse_requests.flags - IS 'see the enum TALER_WalletAccountMergeFlags'; -COMMENT ON COLUMN purse_requests.in_reserve_quota - IS 'set to TRUE if this purse currently counts against the number of free purses in the respective reserve'; -COMMENT ON COLUMN purse_requests.amount_with_fee_val - IS 'Total amount expected to be in the purse'; -COMMENT ON COLUMN purse_requests.purse_fee_val - IS 'Purse fee the client agreed to pay from the reserve (accepted by the exchange at the time the purse was created). Zero if in_reserve_quota is TRUE.'; -COMMENT ON COLUMN purse_requests.balance_val - IS 'Total amount actually in the purse'; -COMMENT ON COLUMN purse_requests.purse_sig - IS 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST'; - -SELECT add_constraints_to_purse_requests_partition('default'); - - --- ------------------------------ purse_decisions ---------------------------------------- - -SELECT create_table_purse_decision(); - -COMMENT ON TABLE purse_decision - IS 'Purses that were decided upon (refund or merge)'; -COMMENT ON COLUMN purse_decision.purse_pub - IS 'Public key of the purse'; - -SELECT add_constraints_to_purse_decision_partition('default'); - - -- ------------------------------ purse_merges ---------------------------------------- SELECT create_table_purse_merges(); |