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-reserves_open_requests.sql | |
parent | be2c11a1797d8d16b86439a80a4f110f82bb5829 (diff) |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-reserves_open_requests.sql')
-rw-r--r-- | src/exchangedb/0002-reserves_open_requests.sql | 71 |
1 files changed, 49 insertions, 22 deletions
diff --git a/src/exchangedb/0002-reserves_open_requests.sql b/src/exchangedb/0002-reserves_open_requests.sql index e56553a5c..96084c1d9 100644 --- a/src/exchangedb/0002-reserves_open_requests.sql +++ b/src/exchangedb/0002-reserves_open_requests.sql @@ -14,8 +14,8 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_reserves_open_requests( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,11 +23,10 @@ AS $$ DECLARE table_name VARCHAR default 'reserves_open_requests'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' - ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + 'CREATE TABLE %I' + '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL' ',request_timestamp INT8 NOT NULL' ',expiration_date INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' @@ -37,42 +36,60 @@ BEGIN ') %s ;' ,table_name ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix + ,partition_suffix ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_open_uuid_index ' - 'ON ' || table_name || ' ' - '(open_request_uuid);' + PERFORM comment_partitioned_table ( + 'requests to keep a reserve open' + ,table_name + ,partition_suffix ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' - 'ON ' || table_name || ' ' - '(reserve_pub);' + PERFORM comment_partitioned_column ( + 'Fee to pay for the request from the reserve balance itself.' + ,'reserve_payment_val' + ,table_name + ,partition_suffix ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( + +CREATE FUNCTION constrain_table_reserves_open_requests( IN partition_suffix VARCHAR ) RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_requests'; BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' + 'ALTER TABLE ' || table_name || ' ' + 'ADD CONSTRAINT ' || table_name || '_by_uuid ' 'PRIMARY KEY (open_request_uuid),' - 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' + 'ADD CONSTRAINT ' || table_name || '_by_time ' 'UNIQUE (reserve_pub,request_timestamp)' ); END $$; +CREATE FUNCTION foreign_table_reserves_open_requests() +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_requests'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || ' ' + 'ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub ' + 'REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ); +END +$$; + + INSERT INTO exchange_tables (name ,version @@ -84,4 +101,14 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('reserves_open_requests' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('reserves_open_requests' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); |