do_insert_receipt_issued.sql (4575B)
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 DROP FUNCTION IF EXISTS do_insert_issued_receipts; 18 CREATE FUNCTION do_insert_issued_receipts ( 19 IN in_charity_id BIGINT -- charity id which made the issue receipts request 20 ,IN in_blinded_sig BYTEA[] -- blinded signatures 21 ,IN in_receipt_hash BYTEA -- hash over all budi key pairs (primary key) 22 ,IN in_amount taler_amount -- total amount of the requested receipts 23 ,IN in_year INT4 24 ,OUT out_smaller_than_max_per_year BOOLEAN 25 ,OUT out_charity_unknown BOOLEAN 26 ) 27 LANGUAGE plpgsql 28 AS $$ 29 DECLARE 30 old_receipts_to_date taler_amount; 31 new_receipts_to_date taler_amount; 32 prior_receipts_to_date taler_amount; 33 max_per_year taler_amount; 34 my_year INT4; 35 rolled_over BOOLEAN DEFAULT FALSE; 36 BEGIN 37 out_charity_unknown = FALSE; 38 out_smaller_than_max_per_year = FALSE; 39 40 -- Get charity values 41 SELECT 42 (chari.receipts_to_date).val 43 ,(chari.receipts_to_date).frac 44 ,(chari.max_per_year).val 45 ,(chari.max_per_year).frac 46 ,current_year 47 INTO 48 old_receipts_to_date.val 49 ,old_receipts_to_date.frac 50 ,max_per_year.val 51 ,max_per_year.frac 52 ,my_year 53 FROM charities chari 54 WHERE charity_id=in_charity_id 55 -- The annual limit is a read-decide-write on this row. Take the row 56 -- lock here so that the decision is atomic at ANY isolation level; do 57 -- not rely on the session default being SERIALIZABLE (that default is 58 -- installed best-effort and DONAUDB_start_read_committed() exists). 59 FOR UPDATE OF chari; 60 61 IF NOT FOUND 62 THEN 63 -- Without this arm every local stays NULL, every comparison below is 64 -- NULL (i.e. false), and the function falls through to the final ELSE 65 -- and answers FALSE -- which the handler reports as "annual limit 66 -- exceeded" for a charity that does not exist. 67 out_charity_unknown = TRUE; 68 RETURN; 69 END IF; 70 71 IF (my_year > in_year) 72 THEN 73 -- server travelled back in time? wild, but 74 -- we closed the books on the previous year, 75 -- so assume balance was exceeded. 76 out_smaller_than_max_per_year = FALSE; 77 RETURN; 78 END IF; 79 IF (my_year < in_year) 80 THEN 81 -- bump current year; the closing total of the year we are leaving 82 -- must be preserved in `history' before it is reset (see the comment 83 -- on charities.receipts_to_date). The row is only written if the 84 -- request actually succeeds, see below. 85 rolled_over = TRUE; 86 prior_receipts_to_date = old_receipts_to_date; 87 old_receipts_to_date.val = 0; 88 old_receipts_to_date.frac = 0; 89 END IF; 90 91 -- calculate sum of the recent amount of receipts and the issued amount 92 SELECT * 93 FROM amount_add(old_receipts_to_date, in_amount) 94 INTO new_receipts_to_date; 95 -- check if the new receipts to date is below or equal the limit for the charity 96 IF ( (max_per_year.val > new_receipts_to_date.val) OR 97 ( (max_per_year.val = new_receipts_to_date.val) AND 98 (max_per_year.frac >= new_receipts_to_date.frac) ) ) 99 THEN 100 out_smaller_than_max_per_year=TRUE; 101 IF (rolled_over) 102 THEN 103 INSERT INTO history 104 (charity_id 105 ,final_amount 106 ,donation_year 107 ) VALUES ( 108 in_charity_id 109 ,prior_receipts_to_date 110 ,my_year 111 ) ON CONFLICT (charity_id, donation_year) DO NOTHING; 112 END IF; 113 UPDATE charities 114 SET receipts_to_date=new_receipts_to_date, 115 current_year=in_year 116 WHERE charity_id=in_charity_id; 117 INSERT INTO receipts_issued ( 118 blinded_sig 119 ,charity_id 120 ,receipt_hash 121 ,amount 122 ) VALUES ( 123 in_blinded_sig 124 ,in_charity_id 125 ,in_receipt_hash 126 ,in_amount 127 ); 128 ELSE 129 out_smaller_than_max_per_year=FALSE; 130 END IF; 131 END $$; 132 133 COMMENT ON FUNCTION do_insert_issued_receipts 134 IS 'This is a transaction for updating the current amount of receipts of a year of a charity and saves the receipts request what makes it idempotent';