diff options
author | Christian Grothoff <christian@grothoff.org> | 2022-11-27 15:31:39 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2022-11-27 15:31:39 +0100 |
commit | 2eff222c524fa3b5ce2dd4a636aaec8dfb0862c7 (patch) | |
tree | fa730c5e13b1e5914c57770a431b6485ca6807fd /src/exchangedb/0002-close_requests.sql | |
parent | 85ce53a49b23fd651b07cea9b39fa0a830b3c9a0 (diff) | |
download | exchange-2eff222c524fa3b5ce2dd4a636aaec8dfb0862c7.tar.xz |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-close_requests.sql')
-rw-r--r-- | src/exchangedb/0002-close_requests.sql | 83 |
1 files changed, 63 insertions, 20 deletions
diff --git a/src/exchangedb/0002-close_requests.sql b/src/exchangedb/0002-close_requests.sql index 103342c2a..751518986 100644 --- a/src/exchangedb/0002-close_requests.sql +++ b/src/exchangedb/0002-close_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_close_requests( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_close_requests( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,11 +23,10 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'close_requests'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + 'CREATE TABLE %I ' + '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',close_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',close_val INT8 NOT NULL' @@ -40,13 +39,41 @@ BEGIN ') %s ;' ,table_name ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix + ,partition_suffix ); -END -$$; + PERFORM comment_partitioned_table( + 'Explicit requests by a reserve owner to close a reserve immediately' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'When the request was created by the client' + ,'close_timestamp' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Signature affirming that the reserve is to be closed' + ,'reserve_sig' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Balance of the reserve at the time of closing, to be wired to the associated bank account (minus the closing fee)' + ,'close_val' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Identifies the credited bank account. Optional.' + ,'payto_uri' + ,table_name + ,partition_suffix + ); +END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_close_requests( +CREATE FUNCTION constrain_table_close_requests( IN partition_suffix VARCHAR ) RETURNS VOID @@ -55,31 +82,37 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'close_requests'; BEGIN - + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_uuid_index ' + 'CREATE INDEX ' || table_name || '_by_close_request_uuid_index ' 'ON ' || table_name || ' ' '(close_request_serial_id);' ); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_done_index ' + 'CREATE INDEX ' || table_name || '_by_close_request_done_index ' 'ON ' || table_name || ' ' '(done);' ); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_close_request_uuid_pkey' + ' UNIQUE (close_request_serial_id)' + ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_close_requests_partition( - IN partition_suffix VARCHAR -) -RETURNS void + +CREATE FUNCTION foreign_table_close_requests() +RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE close_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT close_requests_' || partition_suffix || '_close_request_uuid_pkey ' - 'UNIQUE (close_request_serial_id)' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_reserve_pub' + ' REFERENCES reserves(reserve_pub) ON DELETE CASCADE' ); END $$; @@ -96,4 +129,14 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('close_requests' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('close_requests' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); |