diff options
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/0002-batch_deposits.sql | 10 | ||||
-rw-r--r-- | src/exchangedb/0002-policy_details.sql | 190 | ||||
-rw-r--r-- | src/exchangedb/0002-policy_fulfillments.sql | 100 | ||||
-rw-r--r-- | src/exchangedb/exchange-0002.sql.in | 8 |
4 files changed, 251 insertions, 57 deletions
diff --git a/src/exchangedb/0002-batch_deposits.sql b/src/exchangedb/0002-batch_deposits.sql index af0764aa7..57a476a97 100644 --- a/src/exchangedb/0002-batch_deposits.sql +++ b/src/exchangedb/0002-batch_deposits.sql @@ -111,9 +111,13 @@ BEGIN ' PRIMARY KEY (batch_deposit_serial_id) ' ',ADD CONSTRAINT ' || table_name || '_merchant_pub_h_contract_terms' ' UNIQUE (shard, merchant_pub, h_contract_terms)' - ',ADD CONSTRAINT ' || table_name || '_foreign_policy_details' - ' FOREIGN KEY (policy_details_serial_id) ' - ' REFERENCES policy_details (policy_details_serial_id) ON DELETE RESTRICT' + -- The policy_details_serial_id is a foreign key. + -- But, due to partitioning its table by a different column, we can not + -- simply reference policy_details_serial_id of the policy_details. Thus, + -- the following is commented out: + -- ',ADD CONSTRAINT ' || table_name || '_foreign_policy_details' + -- ' FOREIGN KEY (policy_details_serial_id) ' + -- ' REFERENCES policy_details (policy_details_serial_id) ON DELETE RESTRICT' ); EXECUTE FORMAT ( 'CREATE INDEX ' || table_name || '_by_ready ' diff --git a/src/exchangedb/0002-policy_details.sql b/src/exchangedb/0002-policy_details.sql index 6972dd7b0..3acbb5c10 100644 --- a/src/exchangedb/0002-policy_details.sql +++ b/src/exchangedb/0002-policy_details.sql @@ -14,42 +14,162 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- --- FIXME-Oec: this table should be sharded! - -CREATE TABLE policy_details - (policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,policy_hash_code BYTEA PRIMARY KEY CHECK(LENGTH(policy_hash_code)=16) - ,policy_json TEXT - ,deadline INT8 NOT NULL - ,commitment taler_amount NOT NULL - ,accumulated_total taler_amount NOT NULL - ,fee taler_amount NOT NULL - ,transferable taler_amount NOT NULL - ,fulfillment_state SMALLINT NOT NULL CHECK(fulfillment_state between 0 and 5) - ,fulfillment_id BIGINT NULL REFERENCES policy_fulfillments (fulfillment_id) ON DELETE CASCADE - ); -COMMENT ON TABLE policy_details - IS 'Policies that were provided with deposits via policy extensions.'; -COMMENT ON COLUMN policy_details.policy_hash_code - IS 'ID (GNUNET_HashCode) that identifies a policy. Will be calculated by the policy extension based on the content'; -COMMENT ON COLUMN policy_details.policy_json - IS 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the policy extensions supported by the exchange.'; -COMMENT ON COLUMN policy_details.deadline - IS 'Deadline until the policy must be marked as fulfilled (maybe "forever")'; -COMMENT ON COLUMN policy_details.commitment - IS 'The amount that this policy commits to. Invariant: commitment >= fee'; -COMMENT ON COLUMN policy_details.accumulated_total - IS 'The sum of all contributions of all deposit that reference this policy. Invariant: The fulfilment_state must be Insufficient as long as accumulated_total < commitment'; -COMMENT ON COLUMN policy_details.fee - IS 'The fee for this policy, due when the policy is fulfilled or timed out'; -COMMENT ON COLUMN policy_details.transferable - IS 'The amount that on fulfillment or timeout will be transferred to the payto-URI''s of the corresponding deposit''s. The policy fees must have been already deducted from it. Invariant: fee+transferable <= accumulated_total. The remaining amount (accumulated_total - fee - transferable) can be refreshed by the owner of the coins when the state is Timeout or Success.'; -COMMENT ON COLUMN policy_details.fulfillment_state - IS 'State of the fulfillment: +-- @author: Özgür Kesim + +CREATE FUNCTION create_table_policy_details( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'policy_details'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE %I ' + '(policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',policy_hash_code gnunet_hashcode NOT NULL' + ',policy_json TEXT NOT NULL' + ',deadline INT8 NOT NULL' + ',commitment taler_amount NOT NULL' + ',accumulated_total taler_amount NOT NULL' + ',fee taler_amount NOT NULL' + ',transferable taler_amount NOT NULL' + ',fulfillment_state SMALLINT NOT NULL CHECK(fulfillment_state between 0 and 5)' + ',h_fulfillment_proof gnunet_hashcode' + ') %s;' + ,table_name + ,'PARTITION BY HASH (h_fulfillment_proof)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'Policies that were provided with deposits via policy extensions.' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'ID (GNUNET_HashCode) that identifies a policy. Will be calculated by the policy extension based on the content' + ,'policy_hash_code' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the policy extensions supported by the exchange.' + ,'policy_json' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Deadline until the policy must be marked as fulfilled (maybe "forever")' + ,'deadline' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'The amount that this policy commits to. Invariant: commitment >= fee' + ,'commitment' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'The sum of all contributions of all deposit that reference this policy. Invariant: The fulfilment_state must be Insufficient as long as accumulated_total < commitment' + ,'accumulated_total' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'The fee for this policy, due when the policy is fulfilled or timed out' + ,'fee' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'The amount that on fulfillment or timeout will be transferred to the payto-URI''s of the corresponding deposit''s. The policy fees must have been already deducted from it. Invariant: fee+transferable <= accumulated_total. The remaining amount (accumulated_total - fee - transferable) can be refreshed by the owner of the coins when the state is Timeout or Success.' + ,'transferable' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'State of the fulfillment: - 0 (Failure) - 1 (Insufficient) - 2 (Ready) - 4 (Success) - - 5 (Timeout)'; -COMMENT ON COLUMN policy_details.fulfillment_id - IS 'Reference to the proof of the fulfillment of this policy, if it exists. Invariant: If not NULL, this entry''s .hash_code MUST be part of the corresponding policy_fulfillments.policy_hash_codes array.'; + - 5 (Timeout)' + ,'fulfillment_state' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Reference to the proof of the fulfillment of this policy, if it exists. Invariant: If not NULL, this entry''s .hash_code MUST be part of the corresponding policy_fulfillments.policy_hash_codes array.' + ,'h_fulfillment_proof' + ,table_name + ,partition_suffix + ); +END +$$; + +COMMENT ON FUNCTION create_table_policy_details + IS 'Creates the policy_details table'; + + + + +CREATE FUNCTION constrain_table_policy_details( + IN partition_suffix TEXT +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name TEXT; +BEGIN + partition_name = concat_ws('_', 'policy_details', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name || + ' ADD CONSTRAINT ' || partition_name || '_unique_serial_id ' + ' UNIQUE (policy_details_serial_id)' + ); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name || + ' ADD CONSTRAINT ' || partition_name || '_unique_hash_fulfillment_proof ' + ' UNIQUE (policy_hash_code, h_fulfillment_proof)' + ); + + EXECUTE FORMAT ( + 'CREATE INDEX ' || partition_name || '_policy_hash_code' + ' ON ' || partition_name || + ' (policy_hash_code);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION foreign_table_policy_details() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'policy_details'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_policy_fulfillments' + ' FOREIGN KEY (h_fulfillment_proof) ' + ' REFERENCES policy_fulfillments (h_fulfillment_proof) ON DELETE RESTRICT' + ); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) +VALUES + ('policy_details', 'exchange-0002', 'create', TRUE ,FALSE), + ('policy_details', 'exchange-0002', 'constrain', TRUE ,FALSE), + ('policy_details', 'exchange-0002', 'foreign', TRUE ,FALSE); diff --git a/src/exchangedb/0002-policy_fulfillments.sql b/src/exchangedb/0002-policy_fulfillments.sql index 28dd619e0..c00947019 100644 --- a/src/exchangedb/0002-policy_fulfillments.sql +++ b/src/exchangedb/0002-policy_fulfillments.sql @@ -14,22 +14,88 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- --- FIXME-Oec: this table should be sharded! +-- @author: Özgür Kesim -CREATE TABLE policy_fulfillments - (fulfillment_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE PRIMARY KEY - ,fulfillment_timestamp INT8 NOT NULL - ,fulfillment_proof TEXT - ,h_fulfillment_proof BYTEA NOT NULL CHECK(LENGTH(h_fulfillment_proof) = 64) UNIQUE - ,policy_hash_codes BYTEA NOT NULL CHECK(0 = MOD(LENGTH(policy_hash_codes), 16)) +CREATE FUNCTION create_table_policy_fulfillments( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'policy_fulfillments'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE %I ' + '(h_fulfillment_proof gnunet_hashcode PRIMARY KEY' + ',fulfillment_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',fulfillment_timestamp INT8 NOT NULL' + ',fulfillment_proof TEXT' + ',policy_hash_codes gnunet_hashcode[] NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (h_fulfillment_proof)' + ,partition_suffix ); -COMMENT ON TABLE policy_fulfillments - IS 'Proofs of fulfillment of policies that were set in deposits'; -COMMENT ON COLUMN policy_fulfillments.fulfillment_timestamp - IS 'Timestamp of the arrival of a proof of fulfillment'; -COMMENT ON COLUMN policy_fulfillments.fulfillment_proof - IS 'JSON object with a proof of the fulfillment of a policy. Supported details depend on the policy extensions supported by the exchange.'; -COMMENT ON COLUMN policy_fulfillments.h_fulfillment_proof - IS 'Hash of the fulfillment_proof'; -COMMENT ON COLUMN policy_fulfillments.policy_hash_codes - IS 'Concatenation of the policy_hash_code of all policy_details that are fulfilled by this proof'; + PERFORM comment_partitioned_table( + 'Proofs of fulfillment of policies that were set in deposits' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Timestamp of the arrival of a proof of fulfillment' + ,'fulfillment_timestamp' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'JSON object with a proof of the fulfillment of a policy. Supported details depend on the policy extensions supported by the exchange.' + ,'fulfillment_proof' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Hash of the fulfillment_proof' + ,'h_fulfillment_proof' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Array of the policy_hash_code''s of all policy_details that are fulfilled by this proof' + ,'policy_hash_codes' + ,table_name + ,partition_suffix + ); +END +$$; + +COMMENT ON FUNCTION create_table_policy_fulfillments + IS 'Creates the policy_fulfillments table'; + +CREATE FUNCTION constrain_table_policy_fulfillments( + IN partition_suffix TEXT +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name TEXT; +BEGIN + partition_name = concat_ws('_', 'policy_fulfillments', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name || + ' ADD CONSTRAINT ' || partition_name || '_serial_id ' + ' UNIQUE (h_fulfillment_proof, fulfillment_id)' + ); +END +$$; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) +VALUES + ('policy_fulfillments', 'exchange-0002', 'create', TRUE ,FALSE), + ('policy_fulfillments', 'exchange-0002', 'constrain', TRUE ,FALSE); diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in index e209d5504..ab13b28af 100644 --- a/src/exchangedb/exchange-0002.sql.in +++ b/src/exchangedb/exchange-0002.sql.in @@ -19,6 +19,10 @@ BEGIN; SELECT _v.register_patch('exchange-0002', NULL, NULL); SET search_path TO exchange; +CREATE DOMAIN gnunet_hashcode + AS BYTEA + CHECK(LENGTH(VALUE) = 32); + CREATE TYPE taler_amount AS (val INT8 @@ -59,6 +63,8 @@ COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type #include "0002-exchange_sign_keys.sql" #include "0002-signkey_revocations.sql" #include "0002-extensions.sql" +#include "0002-policy_fulfillments.sql" +#include "0002-policy_details.sql" #include "0002-profit_drains.sql" #include "0002-legitimization_processes.sql" #include "0002-legitimization_requirements.sql" @@ -96,8 +102,6 @@ COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type #include "0002-wad_in_entries.sql" #include "0002-wads_out.sql" #include "0002-wad_out_entries.sql" -#include "0002-policy_fulfillments.sql" -#include "0002-policy_details.sql" #include "0002-work_shards.sql" #include "0002-revolving_work_shards.sql" #include "0002-partners.sql" |