insert_inventory_lock.sql (4274B)
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 17 18 DROP FUNCTION IF EXISTS merchant_do_insert_inventory_lock; 19 CREATE FUNCTION merchant_do_insert_inventory_lock ( 20 IN in_product_id TEXT, 21 IN in_lock_uuid BYTEA, 22 IN in_total_locked INT8, 23 IN in_total_locked_frac INT4, 24 IN in_expiration INT8, 25 OUT out_no_product BOOL, 26 OUT out_insufficient_stock BOOL) 27 LANGUAGE plpgsql 28 AS $$ 29 DECLARE 30 my_rec RECORD; 31 my_requested NUMERIC; 32 my_locked NUMERIC; 33 my_available NUMERIC; 34 BEGIN 35 36 SELECT product_serial 37 ,total_stock 38 ,total_stock_frac 39 ,total_sold 40 ,total_sold_frac 41 ,total_lost 42 ,total_lost_frac 43 ,allow_fractional_quantity 44 INTO my_rec 45 FROM merchant_inventory 46 WHERE product_id = in_product_id; 47 48 IF NOT FOUND 49 THEN 50 out_no_product = TRUE; 51 out_insufficient_stock = FALSE; 52 RETURN; 53 END IF; 54 out_no_product = FALSE; 55 56 IF ( (NOT my_rec.allow_fractional_quantity) AND 57 (in_total_locked_frac <> 0) ) 58 THEN 59 -- Product is not sold in fractional quantities. 60 out_insufficient_stock = TRUE; 61 RETURN; 62 END IF; 63 64 -- Per the API specification, locking a quantity of zero releases 65 -- the lock held under this UUID. 66 IF ( (in_total_locked = 0) AND 67 (in_total_locked_frac = 0) ) 68 THEN 69 DELETE FROM merchant_inventory_locks 70 WHERE product_serial = my_rec.product_serial 71 AND lock_uuid = in_lock_uuid; 72 out_insufficient_stock = FALSE; 73 RETURN; 74 END IF; 75 76 -- INT64_MAX in total_stock is the sentinel for "unlimited stock", 77 -- for which no availability check is made. 78 IF (my_rec.total_stock <> 9223372036854775807) 79 THEN 80 my_requested = in_total_locked::NUMERIC * 1000000 81 + in_total_locked_frac::NUMERIC; 82 -- Note that a lock already held under _this_ UUID is replaced and 83 -- thus must not count against the available stock. 84 SELECT COALESCE(SUM(total_locked::NUMERIC * 1000000 85 + total_locked_frac::NUMERIC), 0) 86 INTO my_locked 87 FROM merchant_inventory_locks 88 WHERE product_serial = my_rec.product_serial 89 AND lock_uuid <> in_lock_uuid; 90 my_locked = my_locked 91 + (SELECT COALESCE(SUM(total_locked::NUMERIC * 1000000 92 + total_locked_frac::NUMERIC), 0) 93 FROM merchant_order_locks 94 WHERE product_serial = my_rec.product_serial); 95 my_available = (my_rec.total_stock::NUMERIC * 1000000 96 + my_rec.total_stock_frac::NUMERIC) 97 - (my_rec.total_sold::NUMERIC * 1000000 98 + my_rec.total_sold_frac::NUMERIC) 99 - (my_rec.total_lost::NUMERIC * 1000000 100 + my_rec.total_lost_frac::NUMERIC); 101 IF (my_available < my_requested + my_locked) 102 THEN 103 out_insufficient_stock = TRUE; 104 RETURN; 105 END IF; 106 END IF; 107 out_insufficient_stock = FALSE; 108 109 INSERT INTO merchant_inventory_locks 110 (product_serial 111 ,lock_uuid 112 ,total_locked 113 ,total_locked_frac 114 ,expiration) 115 VALUES 116 (my_rec.product_serial 117 ,in_lock_uuid 118 ,in_total_locked 119 ,in_total_locked_frac 120 ,in_expiration) 121 ON CONFLICT (product_serial 122 ,lock_uuid) 123 DO UPDATE 124 SET total_locked = EXCLUDED.total_locked 125 ,total_locked_frac = EXCLUDED.total_locked_frac 126 ,expiration = EXCLUDED.expiration; 127 128 END $$; 129 130 COMMENT ON FUNCTION merchant_do_insert_inventory_lock 131 IS 'Locks in_total_locked(_frac) units of the given product under the' 132 ' given lock UUID. A lock already held under the same UUID is' 133 ' replaced (not added to), a quantity of zero releases it. Sets' 134 ' out_no_product if the product does not exist and' 135 ' out_insufficient_stock if not enough unlocked stock remains.';