From af06ddc8e833d6f4bbfd49585a750227b05c056d Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Wed, 11 Jan 2023 16:43:22 +0100 Subject: -add a few missing indices/foreign key constraints --- src/exchangedb/0002-history_requests.sql | 27 +++++++++++++++++++++- src/exchangedb/0002-legitimization_processes.sql | 2 +- .../0002-legitimization_requirements.sql | 2 +- src/exchangedb/0002-refresh_commitments.sql | 2 +- src/exchangedb/0002-reserves_in.sql | 24 +++++++++++++++++-- src/exchangedb/0003-withdraw_age_commitments.sql | 2 +- 6 files changed, 52 insertions(+), 7 deletions(-) (limited to 'src/exchangedb') diff --git a/src/exchangedb/0002-history_requests.sql b/src/exchangedb/0002-history_requests.sql index 5cd5c7b7b..853a435d4 100644 --- a/src/exchangedb/0002-history_requests.sql +++ b/src/exchangedb/0002-history_requests.sql @@ -26,7 +26,7 @@ DECLARE BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I ' - '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',request_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' @@ -64,6 +64,26 @@ BEGIN END $$; +CREATE FUNCTION constrain_table_history_requests( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + partition_name = concat_ws('_', 'history_requests', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name || + ' ADD CONSTRAINT ' || partition_name || '_serial_id' + ' UNIQUE (history_request_serial_id)' + ); +END +$$; + + CREATE FUNCTION foreign_table_history_requests() RETURNS VOID LANGUAGE plpgsql @@ -94,6 +114,11 @@ INSERT INTO exchange_tables ,FALSE), ('history_requests' ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('history_requests' + ,'exchange-0002' ,'foreign' ,TRUE ,FALSE); diff --git a/src/exchangedb/0002-legitimization_processes.sql b/src/exchangedb/0002-legitimization_processes.sql index 6248da1f0..4544a02ea 100644 --- a/src/exchangedb/0002-legitimization_processes.sql +++ b/src/exchangedb/0002-legitimization_processes.sql @@ -23,7 +23,7 @@ AS $$ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I' - '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' ',expiration_time INT8 NOT NULL DEFAULT (0)' ',provider_section VARCHAR NOT NULL' diff --git a/src/exchangedb/0002-legitimization_requirements.sql b/src/exchangedb/0002-legitimization_requirements.sql index 7aaf7b799..4879b7a27 100644 --- a/src/exchangedb/0002-legitimization_requirements.sql +++ b/src/exchangedb/0002-legitimization_requirements.sql @@ -23,7 +23,7 @@ AS $$ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I' - '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' ',required_checks VARCHAR NOT NULL' ',UNIQUE (h_payto, required_checks)' diff --git a/src/exchangedb/0002-refresh_commitments.sql b/src/exchangedb/0002-refresh_commitments.sql index c63995c74..328dad5ce 100644 --- a/src/exchangedb/0002-refresh_commitments.sql +++ b/src/exchangedb/0002-refresh_commitments.sql @@ -25,7 +25,7 @@ DECLARE BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I' - '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' ',old_coin_pub BYTEA NOT NULL' ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' diff --git a/src/exchangedb/0002-reserves_in.sql b/src/exchangedb/0002-reserves_in.sql index d722a49e8..71ec3768a 100644 --- a/src/exchangedb/0002-reserves_in.sql +++ b/src/exchangedb/0002-reserves_in.sql @@ -25,8 +25,8 @@ DECLARE BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I' - '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY' ',wire_reference INT8 NOT NULL' ',credit_val INT8 NOT NULL' ',credit_frac INT4 NOT NULL' @@ -103,6 +103,21 @@ BEGIN END $$; +CREATE FUNCTION foreign_table_reserves_in() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves_in'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' + ' FOREIGN KEY (reserve_pub) ' + ' REFERENCES reserves(reserve_pub) ON DELETE CASCADE' + ); +END $$; + INSERT INTO exchange_tables (name @@ -120,4 +135,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'constrain' ,TRUE + ,FALSE), + ('reserves_in' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); diff --git a/src/exchangedb/0003-withdraw_age_commitments.sql b/src/exchangedb/0003-withdraw_age_commitments.sql index e2205b123..f6f8b44d7 100644 --- a/src/exchangedb/0003-withdraw_age_commitments.sql +++ b/src/exchangedb/0003-withdraw_age_commitments.sql @@ -25,7 +25,7 @@ DECLARE BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I' - '(withdraw_age_commitment_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + '(withdraw_age_commitment_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_commitment BYTEA PRIMARY KEY CHECK (LENGTH(h_commitment)=64)' ',amount_with_fee_val INT8 NOT NULL' ',amount_with_fee_frac INT4 NOT NULL' -- cgit v1.2.3