merchant-0013.sql (5009B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024 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 -- @file merchant-0013.sql 18 -- @brief Creating trigger for the category change webhook 19 -- @author Bohdan Potuzhnyi 20 -- @author Vlada Svirsh 21 22 23 BEGIN; 24 25 -- Check patch versioning is in place. 26 SELECT _v.register_patch('merchant-0013', NULL, NULL); 27 28 SET search_path TO merchant; 29 30 -- Slug was incorrectly set to be globally unique, is only 31 -- unique per instance! Drop the constraint and add correct one. 32 ALTER TABLE merchant_token_families 33 DROP CONSTRAINT merchant_token_families_slug_key, 34 ADD UNIQUE (merchant_serial,slug), 35 DROP COLUMN rounding, -- replaced by validity_granularity 36 ADD validity_granularity INT8 NOT NULL CHECK 37 (validity_granularity IN (60000000,3600000000,86400000000,604800000000,2592000000000,7776000000000,31536000000000)) 38 DEFAULT (2592000000000), -- 30 days 39 ADD start_offset INT8 NOT NULL DEFAULT (0), 40 ADD cipher_choice TEXT NOT NULL DEFAULT ('rsa(2048)'), 41 ADD extra_data TEXT DEFAULT NULL; 42 43 COMMENT ON COLUMN merchant_token_families.validity_granularity 44 IS 'To compute key lifetimes, we first round the payment deadline down to a multiple of this time; supported values are one minute, one hour, a day, a week, 30 days, 90 days or a year (indicatited using 365 days); adding the start_offset gets the start validity time; adding the duration to get the signature_valid_until value for the key'; 45 COMMENT ON COLUMN merchant_token_families.start_offset 46 IS 'This allows shifting the validity period of signatures to start a bit before the time rounded to the precision. For example, Swiss vignettes are valid for 14 months, from December of year X to January of year X+2. This can be achieve by setting a start_offset of 30 days, and a duration of 14 months and a precision of 1 year. The value given is in microseconds (but will be rounded to seconds).'; 47 COMMENT ON COLUMN merchant_token_families.cipher_choice 48 IS 'Specifies the type of cipher that should be used for this token family. Currently supported values are "cs" and "rsa($LEN)" where $LEN is the key length in bits.'; 49 COMMENT ON COLUMN merchant_token_families.extra_data 50 IS 'JSON field with family-specific meta data, such as the trusted_domains for subscriptions or expected_domains for discount tokens'; 51 52 53 -- Keep proper periods for token keys. 54 ALTER TABLE merchant_token_family_keys 55 DROP valid_before, 56 DROP valid_after, 57 ADD signature_validity_start INT8 NOT NULL DEFAULT (0), 58 ADD signature_validity_end INT8 NOT NULL DEFAULT (0), 59 ADD private_key_deleted_at INT8 NOT NULL DEFAULT (0), 60 ADD private_key_created_at INT8 NOT NULL DEFAULT (0); 61 62 COMMENT ON COLUMN merchant_token_family_keys.signature_validity_start 63 IS 'Specifies the earliest time at which tokens signed with this key can be considered valid. Allows tokens to be issued way in advance of their validity.'; 64 COMMENT ON COLUMN merchant_token_family_keys.signature_validity_end 65 IS 'Specifies when the tokens signed by this key expire.'; 66 COMMENT ON COLUMN merchant_token_family_keys.private_key_deleted_at 67 IS 'Specifies how long tokens signed by this key can be created, that is the point at which the private key may be deleted. Computed by determining when the *next* validity period starts, or when the overall token family validity period ends.'; 68 COMMENT ON COLUMN merchant_token_family_keys.private_key_created_at 69 IS 'Specifies when the private key was created. Not terribly useful, mostly for debugging.'; 70 71 CREATE FUNCTION handle_inventory_changes() 72 RETURNS TRIGGER AS $$ 73 BEGIN 74 -- This is just a dummy function, the actual 75 -- implementation lives in pg_do_handle_inventory_changes.sql 76 -- and is loaded later! 77 RETURN NULL; 78 END; 79 $$ LANGUAGE plpgsql; 80 81 CREATE FUNCTION handle_category_changes() 82 RETURNS TRIGGER AS $$ 83 BEGIN 84 -- This is just a dummy function, the actual 85 -- implementation lives in pg_do_handle_category_changes.sql 86 -- and is loaded later! 87 RETURN NULL; 88 END; 89 $$ LANGUAGE plpgsql; 90 91 -- Trigger to invoke the trigger function 92 CREATE TRIGGER trigger_category_changes 93 AFTER INSERT OR UPDATE OR DELETE 94 ON merchant_categories 95 FOR EACH ROW 96 EXECUTE FUNCTION handle_category_changes(); 97 98 -- Trigger to invoke the trigger function 99 CREATE TRIGGER trigger_inventory_changes 100 AFTER INSERT OR UPDATE OR DELETE 101 ON merchant_inventory 102 FOR EACH ROW 103 EXECUTE FUNCTION handle_inventory_changes(); 104 105 106 COMMIT;