stasis-0003.sql (2493B)
1 -- 2 -- This file is part of Anastasis 3 -- Copyright (C) 2026 Anastasis SARL 4 -- 5 -- ANASTASIS 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 -- ANASTASIS 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 -- ANASTASIS; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- Everything in one big transaction 18 BEGIN; 19 20 -- Check patch versioning is in place. 21 SELECT _v.register_patch('stasis-0003', NULL, NULL); 22 23 SET search_path TO anastasis; 24 25 -- ANASTASIS_DB_get_recdoc_payment() is a singleton select on 26 -- (user_id, payment_identifier): a second row for the same pair does not make 27 -- it return the first one, it makes it return a hard error, i.e. the account 28 -- can no longer use that identifier at all. Nothing enforced the uniqueness 29 -- the readers assume, and the backend could be talked into inserting a 30 -- duplicate by re-using a payment identifier for an order the merchant 31 -- answers idempotently. The C side no longer does that; this index is what 32 -- makes it impossible rather than merely unlikely. 33 -- 34 -- Duplicates that already exist have to go first, or the index cannot be 35 -- built. Of a duplicate group we keep the paid row if there is one (that is 36 -- the row carrying the account's credit) and otherwise the oldest, which is 37 -- the one the client was told about; the rest are unpaid leftovers of the 38 -- situation described above and are exactly what garbage collection would 39 -- have removed anyway. 40 DELETE FROM anastasis_recdoc_payment 41 WHERE payment_id NOT IN ( 42 SELECT DISTINCT ON (user_id, payment_identifier) payment_id 43 FROM anastasis_recdoc_payment 44 ORDER BY user_id 45 ,payment_identifier 46 ,paid DESC 47 ,creation_date ASC 48 ,payment_id ASC); 49 50 CREATE UNIQUE INDEX anastasis_recdoc_payment_identifier_unique 51 ON anastasis_recdoc_payment 52 (user_id 53 ,payment_identifier); 54 COMMENT ON INDEX anastasis_recdoc_payment_identifier_unique 55 IS 'A payment identifier identifies at most one payment per account; the readers are singleton selects on this pair'; 56 57 COMMIT;