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.sql | |
parent | be2c11a1797d8d16b86439a80a4f110f82bb5829 (diff) | |
download | exchange-a322770d290cae69e7d2f7629ee575e068254428.tar.xz |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-reserves.sql')
-rw-r--r-- | src/exchangedb/0002-reserves.sql | 75 |
1 files changed, 65 insertions, 10 deletions
diff --git a/src/exchangedb/0002-reserves.sql b/src/exchangedb/0002-reserves.sql index 5f3b46040..e5db97fe5 100644 --- a/src/exchangedb/0002-reserves.sql +++ b/src/exchangedb/0002-reserves.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( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_reserves( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -23,9 +23,8 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'reserves'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + 'CREATE TABLE %I' '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' ',current_balance_val INT8 NOT NULL DEFAULT(0)' @@ -38,13 +37,65 @@ BEGIN ') %s ;' ,table_name ,'PARTITION BY HASH (reserve_pub)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'EdDSA public key of the reserve. Knowledge of the private key implies ownership over the balance.' + ,'reserve_pub' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Current balance remaining with the reserve.' + ,'current_balance_val' + ,table_name + ,partition_suffix ); + PERFORM comment_partitioned_column( + 'Number of purses that were created by this reserve that are not expired and not fully paid.' + ,'purses_active' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Number of purses that this reserve is allowed to have active at most.' + ,'purses_allowed' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Used to trigger closing of reserves that have not been drained after some time' + ,'expiration_date' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Used to forget all information about a reserve during garbage collection' + ,'gc_date' + ,table_name + ,partition_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_reserves( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' + 'CREATE INDEX ' || table_name || '_by_expiration_index ' 'ON ' || table_name || ' ' '(expiration_date' ',current_balance_val' @@ -56,12 +107,12 @@ BEGIN 'IS ' || quote_literal('used in get_expired_reserves') || ';' ); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' + 'CREATE INDEX ' || table_name || '_by_reserve_uuid_index ' 'ON ' || table_name || ' ' '(reserve_uuid);' ); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' + 'CREATE INDEX ' || table_name || '_by_gc_date_index ' 'ON ' || table_name || ' ' '(gc_date);' ); @@ -69,7 +120,6 @@ BEGIN 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' 'IS ' || quote_literal('for reserve garbage collection') || ';' ); - END $$; @@ -85,4 +135,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('reserves' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); |