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-legitimization_requirements.sql | |
parent | be2c11a1797d8d16b86439a80a4f110f82bb5829 (diff) | |
download | exchange-a322770d290cae69e7d2f7629ee575e068254428.tar.xz |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-legitimization_requirements.sql')
-rw-r--r-- | src/exchangedb/0002-legitimization_requirements.sql | 49 |
1 files changed, 36 insertions, 13 deletions
diff --git a/src/exchangedb/0002-legitimization_requirements.sql b/src/exchangedb/0002-legitimization_requirements.sql index c58d24318..7aaf7b799 100644 --- a/src/exchangedb/0002-legitimization_requirements.sql +++ b/src/exchangedb/0002-legitimization_requirements.sql @@ -14,16 +14,15 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_legitimization_requirements( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_legitimization_requirements( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' + 'CREATE TABLE %I' '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' ',required_checks VARCHAR NOT NULL' @@ -31,15 +30,37 @@ BEGIN ') %s ;' ,'legitimization_requirements' ,'PARTITION BY HASH (h_payto)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'List of required legitimizations by account' + ,'legitimization_requirements' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'unique ID for this legitimization requirement at the exchange' + ,'legitimization_requirement_serial_id' + ,'legitimization_requirements' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'foreign key linking the entry to the wire_targets table, NOT a primary key (multiple legitimizations are possible per wire target)' + ,'h_payto' + ,'legitimization_requirements' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'space-separated list of required checks' + ,'required_checks' + ,'legitimization_requirements' + ,partition_suffix ); - END $$; -- We need a separate function for this, as we call create_table only once but need to add -- those constraints to each partition which gets created -CREATE OR REPLACE FUNCTION add_constraints_to_legitimization_requirements_partition( +CREATE FUNCTION constrain_table_legitimization_requirements( IN partition_suffix VARCHAR ) RETURNS void @@ -48,14 +69,11 @@ AS $$ DECLARE partition_name VARCHAR; BEGIN - partition_name = concat_ws('_', 'legitimization_requirements', partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || partition_name - || ' ' - 'ADD CONSTRAINT ' || partition_name || '_serial_id_key ' - 'UNIQUE (legitimization_requirement_serial_id)'); + 'ALTER TABLE ' || partition_name || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_id_key ' + 'UNIQUE (legitimization_requirement_serial_id)'); END $$; @@ -71,4 +89,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('legitimization_requirements' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); |