0002-policy_details.sql (5601B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2022 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- @author: Özgür Kesim 18 19 CREATE FUNCTION create_table_policy_details( 20 IN partition_suffix TEXT DEFAULT NULL 21 ) 22 RETURNS VOID 23 LANGUAGE plpgsql 24 AS $$ 25 DECLARE 26 table_name TEXT DEFAULT 'policy_details'; 27 BEGIN 28 PERFORM create_partitioned_table( 29 'CREATE TABLE %I ' 30 '(policy_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 31 ',policy_hash_code gnunet_hashcode NOT NULL' 32 ',policy_json TEXT NOT NULL' 33 ',deadline INT8 NOT NULL' 34 ',commitment taler_amount NOT NULL' 35 ',accumulated_total taler_amount NOT NULL' 36 ',fee taler_amount NOT NULL' 37 ',transferable taler_amount NOT NULL' 38 ',fulfillment_state SMALLINT NOT NULL CHECK(fulfillment_state between 0 and 5)' 39 ',h_fulfillment_proof gnunet_hashcode' 40 ') %s;' 41 ,table_name 42 ,'PARTITION BY HASH (h_fulfillment_proof)' 43 ,partition_suffix 44 ); 45 PERFORM comment_partitioned_table( 46 'Policies that were provided with deposits via policy extensions.' 47 ,table_name 48 ,partition_suffix 49 ); 50 PERFORM comment_partitioned_column( 51 'ID (GNUNET_HashCode) that identifies a policy. Will be calculated by the policy extension based on the content' 52 ,'policy_hash_code' 53 ,table_name 54 ,partition_suffix 55 ); 56 PERFORM comment_partitioned_column( 57 '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.' 58 ,'policy_json' 59 ,table_name 60 ,partition_suffix 61 ); 62 PERFORM comment_partitioned_column( 63 'Deadline until the policy must be marked as fulfilled (maybe "forever")' 64 ,'deadline' 65 ,table_name 66 ,partition_suffix 67 ); 68 PERFORM comment_partitioned_column( 69 'The amount that this policy commits to. Invariant: commitment >= fee' 70 ,'commitment' 71 ,table_name 72 ,partition_suffix 73 ); 74 PERFORM comment_partitioned_column( 75 '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' 76 ,'accumulated_total' 77 ,table_name 78 ,partition_suffix 79 ); 80 PERFORM comment_partitioned_column( 81 'The fee for this policy, due when the policy is fulfilled or timed out' 82 ,'fee' 83 ,table_name 84 ,partition_suffix 85 ); 86 PERFORM comment_partitioned_column( 87 '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.' 88 ,'transferable' 89 ,table_name 90 ,partition_suffix 91 ); 92 PERFORM comment_partitioned_column( 93 'State of the fulfillment: 94 - 0 (Failure) 95 - 1 (Insufficient) 96 - 2 (Ready) 97 - 4 (Success) 98 - 5 (Timeout)' 99 ,'fulfillment_state' 100 ,table_name 101 ,partition_suffix 102 ); 103 PERFORM comment_partitioned_column( 104 '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.' 105 ,'h_fulfillment_proof' 106 ,table_name 107 ,partition_suffix 108 ); 109 END 110 $$; 111 112 COMMENT ON FUNCTION create_table_policy_details 113 IS 'Creates the policy_details table'; 114 115 116 117 118 CREATE FUNCTION constrain_table_policy_details( 119 IN partition_suffix TEXT 120 ) 121 RETURNS VOID 122 LANGUAGE plpgsql 123 AS $$ 124 DECLARE 125 partition_name TEXT; 126 BEGIN 127 partition_name = concat_ws('_', 'policy_details', partition_suffix); 128 129 EXECUTE FORMAT ( 130 'ALTER TABLE ' || partition_name || 131 ' ADD CONSTRAINT ' || partition_name || '_unique_serial_id ' 132 ' UNIQUE (policy_details_serial_id)' 133 ); 134 135 EXECUTE FORMAT ( 136 'ALTER TABLE ' || partition_name || 137 ' ADD CONSTRAINT ' || partition_name || '_unique_hash_fulfillment_proof ' 138 ' UNIQUE (policy_hash_code, h_fulfillment_proof)' 139 ); 140 141 EXECUTE FORMAT ( 142 'CREATE INDEX ' || partition_name || '_policy_hash_code' 143 ' ON ' || partition_name || 144 ' (policy_hash_code);' 145 ); 146 END 147 $$; 148 149 CREATE OR REPLACE FUNCTION foreign_table_policy_details() 150 RETURNS VOID 151 LANGUAGE plpgsql 152 AS $$ 153 DECLARE 154 table_name TEXT DEFAULT 'policy_details'; 155 BEGIN 156 EXECUTE FORMAT ( 157 'ALTER TABLE ' || table_name || 158 ' ADD CONSTRAINT ' || table_name || '_foreign_policy_fulfillments' 159 ' FOREIGN KEY (h_fulfillment_proof) ' 160 ' REFERENCES policy_fulfillments (h_fulfillment_proof) ON DELETE RESTRICT' 161 ); 162 END 163 $$; 164 165 166 INSERT INTO exchange_tables 167 (name 168 ,version 169 ,action 170 ,partitioned 171 ,by_range) 172 VALUES 173 ('policy_details', 'exchange-0002', 'create', TRUE ,FALSE), 174 ('policy_details', 'exchange-0002', 'constrain', TRUE ,FALSE), 175 ('policy_details', 'exchange-0002', 'foreign', TRUE ,FALSE);