diff options
Diffstat (limited to 'src/exchangedb/0002-policy_details.sql')
| -rw-r--r-- | src/exchangedb/0002-policy_details.sql | 190 |
1 files changed, 155 insertions, 35 deletions
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); |