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_deposits.sql | |
parent | be2c11a1797d8d16b86439a80a4f110f82bb5829 (diff) |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-reserves_open_deposits.sql')
-rw-r--r-- | src/exchangedb/0002-reserves_open_deposits.sql | 56 |
1 files changed, 36 insertions, 20 deletions
diff --git a/src/exchangedb/0002-reserves_open_deposits.sql b/src/exchangedb/0002-reserves_open_deposits.sql index 132a123f0..35605d360 100644 --- a/src/exchangedb/0002-reserves_open_deposits.sql +++ b/src/exchangedb/0002-reserves_open_deposits.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_deposits( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_reserves_open_deposits( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,10 +23,9 @@ AS $$ DECLARE table_name VARCHAR default 'reserves_open_deposits'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + 'CREATE TABLE %I' + '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' @@ -36,36 +35,48 @@ BEGIN ') %s ;' ,table_name ,'PARTITION BY HASH (coin_pub)' - ,shard_suffix + ,partition_suffix ); - - table_name = concat_ws('_', table_name, shard_suffix); - - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_uuid ' - 'ON ' || table_name || ' ' - '(reserve_open_deposit_uuid);' + PERFORM comment_partitioned_table( + 'coin contributions paying for a reserve to remain open' + ,table_name + ,partition_suffix ); - EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' - 'ON ' || table_name || ' ' - '(reserve_pub);' + PERFORM comment_partitioned_column( + 'Identifies the specific reserve being paid for (possibly together with reserve_sig).' + ,'reserve_pub' + ,table_name + ,partition_suffix ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition( + +CREATE FUNCTION constrain_table_reserves_open_deposits( IN partition_suffix VARCHAR ) RETURNS void LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_deposits'; BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' + 'ALTER TABLE ' || table_name || ' ' + 'ADD CONSTRAINT ' || table_name || '_coin_unique ' 'PRIMARY KEY (coin_pub,coin_sig)' ); + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_by_uuid ' + 'ON ' || table_name || ' ' + '(reserve_open_deposit_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_by_reserve ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); END $$; @@ -81,4 +92,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('reserves_open_deposits' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); |