merchant-0043.sql (4832B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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 -- @file merchant-0043.sql 17 -- @brief add missing primary key constraint on merchant_product_categories 18 19 BEGIN; 20 21 SELECT _v.register_patch('merchant-0043', NULL, NULL); 22 23 SET search_path TO merchant; 24 25 CREATE PROCEDURE merchant.merchant_0043_init(s TEXT) 26 LANGUAGE plpgsql 27 AS $OUTER$ 28 BEGIN 29 30 EXECUTE format('SET LOCAL search_path TO %I', s); 31 32 -- Add primary constraint, delete constraint-violating values first 33 34 DELETE FROM merchant_product_categories f 35 USING merchant_product_categories d 36 WHERE f.product_serial = d.product_serial 37 AND f.category_serial = d.category_serial 38 AND f.ctid > d.ctid; 39 ALTER TABLE merchant_product_categories 40 ADD PRIMARY KEY(product_serial, category_serial); 41 42 -- Add unique constraint, delete constraint-violating values first. 43 -- Without this constraint the 'ON CONFLICT' in 44 -- merchant_do_insert_issued_token had no arbiter it could ever match 45 -- (the only unique index was the primary key on the GENERATED ... 46 -- AS IDENTITY column, which the INSERT never supplies), so a replayed 47 -- issuance duplicated the row and re-incremented the 'issued' counter. 48 49 DELETE FROM merchant_issued_tokens f 50 USING merchant_issued_tokens d 51 WHERE f.token_family_key_serial = d.token_family_key_serial 52 AND f.h_contract_terms = d.h_contract_terms 53 AND f.blind_sig = d.blind_sig 54 AND f.ctid > d.ctid; 55 ALTER TABLE merchant_issued_tokens 56 ADD CONSTRAINT merchant_issued_tokens_token_key 57 UNIQUE (token_family_key_serial 58 ,h_contract_terms 59 ,blind_sig); 60 61 -- A lock is identified by its UUID: re-posting a lock with a different 62 -- duration or quantity must update the existing lock for the same UUID 63 -- instead of stacking a second one. Without a unique constraint the 64 -- locks accumulated and the AFTER INSERT trigger counted each of them 65 -- towards merchant_inventory.total_locked. 66 -- Delete constraint-violating values first, keeping the most recently 67 -- written row (that is the quantity the client last asked for); the 68 -- AFTER DELETE trigger corrects total_locked for the removed rows. 69 70 DELETE FROM merchant_inventory_locks f 71 USING merchant_inventory_locks d 72 WHERE f.product_serial = d.product_serial 73 AND f.lock_uuid = d.lock_uuid 74 AND f.ctid < d.ctid; 75 ALTER TABLE merchant_inventory_locks 76 ADD CONSTRAINT merchant_inventory_locks_product_lock_key 77 UNIQUE (product_serial, lock_uuid); 78 79 -- merchant_deposits_insert_statistics_trigger() bumps 'deposits-received' 80 -- and 'deposits-fees-paid', and GET /private/statistics-report/transactions 81 -- reads 'deposits-received'. Both slugs were registered by merchant-0028, 82 -- but the default registration for per-instance schemata introduced with 83 -- merchant-0037 was copied from merchant-0014 and hence predates them, so 84 -- no instance created since then collects deposit statistics at all (the 85 -- bump procedures return silently for an unregistered slug). 86 -- Note that 'deposits-fees-paid' overlaps with 'total-deposit-fees-paid': 87 -- the former is collected when the deposit is recorded, the latter when the 88 -- order is marked as paid, so the two do not necessarily agree. 89 INSERT INTO merchant_statistic_bucket_meta 90 (slug 91 ,description 92 ,stype 93 ,ranges 94 ,ages) 95 VALUES 96 ('deposits-received' 97 ,'total amount customers deposited to us (including deposit fees)' 98 ,'amount' 99 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 100 ,ARRAY[72, 14, 12, 24, 12, 10] 101 ), 102 ('deposits-fees-paid' 103 ,'total amount in deposit fees paid by us or our customers' 104 ,'amount' 105 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 106 ,ARRAY[72, 14, 12, 24, 12, 10] 107 ) 108 ON CONFLICT DO NOTHING; 109 110 SET LOCAL search_path TO merchant; 111 112 END 113 $OUTER$; 114 115 INSERT INTO merchant.instance_fixups 116 (migration_name 117 ,version) 118 VALUES 119 ('merchant_0043_init' 120 ,43); 121 -- Apply new fix-up to existing instances 122 CALL merchant.fixup_instance_schema (43::INT8); 123 124 COMMIT;