merchant-0013.sql (5363B)
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 -- Function to replace placeholders in a string with a given value 72 CREATE FUNCTION replace_placeholder( 73 template TEXT, 74 key TEXT, 75 value TEXT 76 ) RETURNS TEXT AS $$ 77 BEGIN 78 RETURN regexp_replace( 79 template, 80 '{{\s*' || key || '\s*}}', -- Match the key with optional spaces 81 value, 82 'g' -- Global replacement 83 ); 84 END; 85 $$ LANGUAGE plpgsql; 86 87 CREATE FUNCTION handle_inventory_changes() 88 RETURNS TRIGGER AS $$ 89 BEGIN 90 -- This is just a dummy function, the actual 91 -- implementation lives in pg_do_handle_inventory_changes.sql 92 -- and is loaded later! 93 RETURN NULL; 94 END; 95 $$ LANGUAGE plpgsql; 96 97 CREATE FUNCTION handle_category_changes() 98 RETURNS TRIGGER AS $$ 99 BEGIN 100 -- This is just a dummy function, the actual 101 -- implementation lives in pg_do_handle_category_changes.sql 102 -- and is loaded later! 103 RETURN NULL; 104 END; 105 $$ LANGUAGE plpgsql; 106 107 -- Trigger to invoke the trigger function 108 CREATE TRIGGER trigger_category_changes 109 AFTER INSERT OR UPDATE OR DELETE 110 ON merchant_categories 111 FOR EACH ROW 112 EXECUTE FUNCTION handle_category_changes(); 113 114 -- Trigger to invoke the trigger function 115 CREATE TRIGGER trigger_inventory_changes 116 AFTER INSERT OR UPDATE OR DELETE 117 ON merchant_inventory 118 FOR EACH ROW 119 EXECUTE FUNCTION handle_inventory_changes(); 120 121 122 COMMIT;