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-contracts.sql | |
parent | 85ce53a49b23fd651b07cea9b39fa0a830b3c9a0 (diff) | |
download | exchange-2eff222c524fa3b5ce2dd4a636aaec8dfb0862c7.tar.xz |
more work on SQL refactoring
Diffstat (limited to 'src/exchangedb/0002-contracts.sql')
-rw-r--r-- | src/exchangedb/0002-contracts.sql | 72 |
1 files changed, 54 insertions, 18 deletions
diff --git a/src/exchangedb/0002-contracts.sql b/src/exchangedb/0002-contracts.sql index 224430c95..409653060 100644 --- a/src/exchangedb/0002-contracts.sql +++ b/src/exchangedb/0002-contracts.sql @@ -15,8 +15,8 @@ -- -CREATE OR REPLACE FUNCTION create_table_contracts( - IN shard_suffix VARCHAR DEFAULT NULL +CREATE FUNCTION create_table_contracts( + IN partition_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql @@ -24,36 +24,67 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'contracts'; BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I ' - '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' - ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' - ',e_contract BYTEA NOT NULL' - ',purse_expiration INT8 NOT NULL' - ',PRIMARY KEY (purse_pub)' - ') %s ;' + 'CREATE TABLE %I ' + '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' + ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' + ',e_contract BYTEA NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' ,table_name ,'PARTITION BY HASH (purse_pub)' - ,shard_suffix + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'encrypted contracts associated with purses' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'public key of the purse that the contract is associated with' + ,'purse_pub' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'signature over the encrypted contract by the purse contract key' + ,'contract_sig' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Public ECDH key used to encrypt the contract, to be used with the purse private key for decryption' + ,'pub_ckey' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'AES-GCM encrypted contract terms (contains gzip compressed JSON after decryption)' + ,'e_contract' + ,table_name + ,partition_suffix ); - END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( + +CREATE FUNCTION constrain_table_contracts( IN partition_suffix VARCHAR ) RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'contracts'; BEGIN + table_name = concat_ws('_', table_name, partition_suffix); EXECUTE FORMAT ( - 'ALTER TABLE contracts_' || partition_suffix || ' ' - 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' - 'UNIQUE (contract_serial_id) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_contract_serial_id_key' + ' UNIQUE (contract_serial_id) ' ); END $$; @@ -70,4 +101,9 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('contracts' + ,'exchange-0002' + ,'constrain' + ,TRUE ,FALSE); |