merchant-0027.sql (17055B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 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-0027.sql 17 -- @brief Add fractional stock support to merchant_inventory 18 -- @author Bohdan Potuzhnyi 19 20 BEGIN; 21 22 -- Check patch versioning is in place. 23 SELECT _v.register_patch('merchant-0027', NULL, NULL); 24 25 SET search_path TO merchant; 26 27 ALTER TABLE merchant_inventory 28 ADD COLUMN price_array taler_amount_currency[] 29 NOT NULL 30 DEFAULT ARRAY[]::taler_amount_currency[], 31 ADD COLUMN total_stock_frac INT4 NOT NULL DEFAULT 0, 32 ADD COLUMN total_sold_frac INT4 NOT NULL DEFAULT 0, 33 ADD COLUMN total_lost_frac INT4 NOT NULL DEFAULT 0, 34 ADD COLUMN allow_fractional_quantity BOOL NOT NULL DEFAULT FALSE, 35 ADD COLUMN fractional_precision_level INT4 NOT NULL DEFAULT 0; 36 COMMENT ON COLUMN merchant_inventory.price_array 37 IS 'List of unit prices available for the product (multiple tiers supported).'; 38 39 UPDATE merchant_inventory 40 SET price_array = ARRAY[price]::taler_amount_currency[] 41 WHERE price IS NOT NULL; -- theoretically all objects, but just to be sure 42 43 -- Note: price column is dropped in merchant-0028.sql 44 45 COMMENT ON COLUMN merchant_inventory.total_stock_frac 46 IS 'Fractional part of stock in units of 1/1000000 of the base value'; 47 COMMENT ON COLUMN merchant_inventory.total_sold_frac 48 IS 'Fractional part of units sold in units of 1/1000000 of the base value'; 49 COMMENT ON COLUMN merchant_inventory.total_lost_frac 50 IS 'Fractional part of units lost in units of 1/1000000 of the base value'; 51 COMMENT ON COLUMN merchant_inventory.allow_fractional_quantity 52 IS 'Whether fractional stock (total_stock_frac) should be honored for this product'; 53 COMMENT ON COLUMN merchant_inventory.fractional_precision_level 54 IS 'Preset number of decimal places for fractional quantities'; 55 56 ALTER TABLE merchant_inventory_locks 57 ADD COLUMN total_locked_frac INT4 NOT NULL DEFAULT 0; 58 COMMENT ON COLUMN merchant_inventory_locks.total_locked_frac 59 IS 'Fractional part of locked stock in units of 1/1000000 of the base value'; 60 61 ALTER TABLE merchant_order_locks 62 ADD COLUMN total_locked_frac INT4 NOT NULL DEFAULT 0; 63 COMMENT ON COLUMN merchant_order_locks.total_locked_frac 64 IS 'Fractional part of locked stock associated with orders in units of 1/1000000 of the base value'; 65 66 CREATE TABLE merchant_builtin_units 67 ( 68 unit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 69 unit TEXT NOT NULL UNIQUE, 70 unit_name_long TEXT NOT NULL, 71 unit_name_short TEXT NOT NULL, 72 unit_name_long_i18n BYTEA NOT NULL DEFAULT convert_to('{}','UTF8'), 73 unit_name_short_i18n BYTEA NOT NULL DEFAULT convert_to('{}','UTF8'), 74 unit_allow_fraction BOOLEAN NOT NULL DEFAULT FALSE, 75 unit_precision_level INT4 NOT NULL DEFAULT 0 CHECK (unit_precision_level BETWEEN 0 AND 6), 76 unit_active BOOLEAN NOT NULL DEFAULT TRUE 77 ); 78 COMMENT ON TABLE merchant_builtin_units 79 IS 'Global catalogue of builtin measurement units.'; 80 COMMENT ON COLUMN merchant_builtin_units.unit_active 81 IS 'Default visibility for the builtin unit; instances may override.'; 82 83 CREATE TABLE merchant_custom_units 84 ( 85 unit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 86 merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE, 87 unit TEXT NOT NULL, 88 unit_name_long TEXT NOT NULL, 89 unit_name_short TEXT NOT NULL, 90 unit_name_long_i18n BYTEA NOT NULL DEFAULT convert_to('{}','UTF8'), 91 unit_name_short_i18n BYTEA NOT NULL DEFAULT convert_to('{}','UTF8'), 92 unit_allow_fraction BOOLEAN NOT NULL DEFAULT FALSE, 93 unit_precision_level INT4 NOT NULL DEFAULT 0 CHECK (unit_precision_level BETWEEN 0 AND 6), 94 unit_active BOOLEAN NOT NULL DEFAULT TRUE, 95 UNIQUE (merchant_serial, unit) 96 ); 97 COMMENT ON TABLE merchant_custom_units 98 IS 'Per-instance custom measurement units.'; 99 100 CREATE TABLE merchant_builtin_unit_overrides 101 ( 102 merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE, 103 builtin_unit_serial BIGINT NOT NULL REFERENCES merchant_builtin_units (unit_serial) ON DELETE CASCADE, 104 override_allow_fraction BOOLEAN, 105 override_precision_level INT4 CHECK (override_precision_level BETWEEN 0 AND 6), 106 override_active BOOLEAN, 107 PRIMARY KEY (merchant_serial, builtin_unit_serial) 108 ); 109 COMMENT ON TABLE merchant_builtin_unit_overrides 110 IS 'Per-instance overrides for builtin units (fraction policy and visibility).'; 111 112 INSERT INTO merchant_builtin_units (unit, unit_name_long, unit_name_short, unit_allow_fraction, unit_precision_level, unit_active) 113 VALUES 114 ('Piece', 'piece', 'pc', FALSE, 0, TRUE), 115 ('Set', 'set', 'set', FALSE, 0, TRUE), 116 ('SizeUnitCm', 'centimetre', 'cm', TRUE, 1, TRUE), 117 ('SizeUnitDm', 'decimetre', 'dm', TRUE, 3, TRUE), 118 ('SizeUnitFoot', 'foot', 'ft', TRUE, 3, TRUE), 119 ('SizeUnitInch', 'inch', 'in', TRUE, 2, TRUE), 120 ('SizeUnitM', 'metre', 'm', TRUE, 3, TRUE), 121 ('SizeUnitMm', 'millimetre', 'mm', FALSE, 0, TRUE), 122 ('SurfaceUnitCm2', 'square centimetre', 'cm²', TRUE, 2, TRUE), 123 ('SurfaceUnitDm2', 'square decimetre', 'dm²', TRUE, 3, TRUE), 124 ('SurfaceUnitFoot2', 'square foot', 'ft²', TRUE, 3, TRUE), 125 ('SurfaceUnitInch2', 'square inch', 'in²', TRUE, 4, TRUE), 126 ('SurfaceUnitM2', 'square metre', 'm²', TRUE, 4, TRUE), 127 ('SurfaceUnitMm2', 'square millimetre', 'mm²', TRUE, 1, TRUE), 128 ('TimeUnitDay', 'day', 'd', TRUE, 3, TRUE), 129 ('TimeUnitHour', 'hour', 'h', TRUE, 2, TRUE), 130 ('TimeUnitMinute', 'minute', 'min', TRUE, 3, TRUE), 131 ('TimeUnitMonth', 'month', 'mo', TRUE, 2, TRUE), 132 ('TimeUnitSecond', 'second', 's', TRUE, 3, TRUE), 133 ('TimeUnitWeek', 'week', 'wk', TRUE, 3, TRUE), 134 ('TimeUnitYear', 'year', 'yr', TRUE, 4, TRUE), 135 ('VolumeUnitCm3', 'cubic centimetre', 'cm³', TRUE, 3, TRUE), 136 ('VolumeUnitDm3', 'cubic decimetre', 'dm³', TRUE, 5, TRUE), 137 ('VolumeUnitFoot3', 'cubic foot', 'ft³', TRUE, 5, TRUE), 138 ('VolumeUnitGallon', 'gallon', 'gal', TRUE, 3, TRUE), 139 ('VolumeUnitInch3', 'cubic inch', 'in³', TRUE, 2, TRUE), 140 ('VolumeUnitLitre', 'litre', 'L', TRUE, 3, TRUE), 141 ('VolumeUnitM3', 'cubic metre', 'm³', TRUE, 6, TRUE), 142 ('VolumeUnitMm3', 'cubic millimetre', 'mm³', TRUE, 1, TRUE), 143 ('VolumeUnitOunce', 'fluid ounce', 'fl oz', TRUE, 2, TRUE), 144 ('WeightUnitG', 'gram', 'g', TRUE, 1, TRUE), 145 ('WeightUnitKg', 'kilogram', 'kg', TRUE, 3, TRUE), 146 ('WeightUnitMg', 'milligram', 'mg', FALSE, 0, TRUE), 147 ('WeightUnitOunce', 'ounce', 'oz', TRUE, 2, TRUE), 148 ('WeightUnitPound', 'pound', 'lb', TRUE, 3, TRUE), 149 ('WeightUnitTon', 'metric tonne', 't', TRUE, 3, TRUE); 150 151 DROP FUNCTION IF EXISTS merchant_do_insert_unit; 152 CREATE FUNCTION merchant_do_insert_unit ( 153 IN in_instance_id TEXT, 154 IN in_unit TEXT, 155 IN in_unit_name_long TEXT, 156 IN in_unit_name_short TEXT, 157 IN in_unit_name_long_i18n BYTEA, 158 IN in_unit_name_short_i18n BYTEA, 159 IN in_unit_allow_fraction BOOL, 160 IN in_unit_precision_level INT4, 161 IN in_unit_active BOOL, 162 OUT out_no_instance BOOL, 163 OUT out_conflict BOOL, 164 OUT out_unit_serial INT8) 165 LANGUAGE plpgsql 166 AS $$ 167 DECLARE 168 my_merchant_id INT8; 169 BEGIN 170 SELECT merchant_serial 171 INTO my_merchant_id 172 FROM merchant_instances 173 WHERE merchant_id = in_instance_id; 174 175 IF NOT FOUND THEN 176 out_no_instance := TRUE; 177 out_conflict := FALSE; 178 out_unit_serial := NULL; 179 RETURN; 180 END IF; 181 182 out_no_instance := FALSE; 183 184 -- Reject attempts to shadow builtin identifiers. 185 IF EXISTS ( 186 SELECT 1 FROM merchant_builtin_units bu WHERE bu.unit = in_unit 187 ) THEN 188 out_conflict := TRUE; 189 out_unit_serial := NULL; 190 RETURN; 191 END IF; 192 193 INSERT INTO merchant_custom_units ( 194 merchant_serial, 195 unit, 196 unit_name_long, 197 unit_name_short, 198 unit_name_long_i18n, 199 unit_name_short_i18n, 200 unit_allow_fraction, 201 unit_precision_level, 202 unit_active) 203 VALUES ( 204 my_merchant_id, 205 in_unit, 206 in_unit_name_long, 207 in_unit_name_short, 208 in_unit_name_long_i18n, 209 in_unit_name_short_i18n, 210 in_unit_allow_fraction, 211 in_unit_precision_level, 212 in_unit_active) 213 ON CONFLICT (merchant_serial, unit) DO NOTHING 214 RETURNING unit_serial 215 INTO out_unit_serial; 216 217 IF FOUND THEN 218 out_conflict := FALSE; 219 RETURN; 220 END IF; 221 222 -- Conflict: custom unit already exists. 223 SELECT unit_serial 224 INTO out_unit_serial 225 FROM merchant_custom_units 226 WHERE merchant_serial = my_merchant_id 227 AND unit = in_unit; 228 229 out_conflict := TRUE; 230 END $$; 231 232 DROP FUNCTION IF EXISTS merchant_do_update_unit; 233 CREATE FUNCTION merchant_do_update_unit ( 234 IN in_instance_id TEXT, 235 IN in_unit_id TEXT, 236 IN in_unit_name_long TEXT, 237 IN in_unit_name_long_i18n BYTEA, 238 IN in_unit_name_short TEXT, 239 IN in_unit_name_short_i18n BYTEA, 240 IN in_unit_allow_fraction BOOL, 241 IN in_unit_precision_level INT4, 242 IN in_unit_active BOOL, 243 OUT out_no_instance BOOL, 244 OUT out_no_unit BOOL, 245 OUT out_builtin_conflict BOOL) 246 LANGUAGE plpgsql 247 AS $$ 248 DECLARE 249 my_merchant_id INT8; 250 my_custom merchant_custom_units%ROWTYPE; 251 my_builtin merchant_builtin_units%ROWTYPE; 252 my_override merchant_builtin_unit_overrides%ROWTYPE; 253 new_unit_name_long TEXT; 254 new_unit_name_short TEXT; 255 new_unit_name_long_i18n BYTEA; 256 new_unit_name_short_i18n BYTEA; 257 new_unit_allow_fraction BOOL; 258 new_unit_precision_level INT4; 259 new_unit_active BOOL; 260 old_unit_allow_fraction BOOL; 261 old_unit_precision_level INT4; 262 old_unit_active BOOL; 263 BEGIN 264 out_no_instance := FALSE; 265 out_no_unit := FALSE; 266 out_builtin_conflict := FALSE; 267 268 SELECT merchant_serial 269 INTO my_merchant_id 270 FROM merchant_instances 271 WHERE merchant_id = in_instance_id; 272 273 IF NOT FOUND THEN 274 out_no_instance := TRUE; 275 RETURN; 276 END IF; 277 278 SELECT * 279 INTO my_custom 280 FROM merchant_custom_units 281 WHERE merchant_serial = my_merchant_id 282 AND unit = in_unit_id 283 FOR UPDATE; 284 285 IF FOUND THEN 286 old_unit_allow_fraction := my_custom.unit_allow_fraction; 287 old_unit_precision_level := my_custom.unit_precision_level; 288 old_unit_active := my_custom.unit_active; 289 290 new_unit_name_long := COALESCE (in_unit_name_long, my_custom.unit_name_long); 291 new_unit_name_short := COALESCE (in_unit_name_short, my_custom.unit_name_short); 292 new_unit_name_long_i18n := COALESCE (in_unit_name_long_i18n, 293 my_custom.unit_name_long_i18n); 294 new_unit_name_short_i18n := COALESCE (in_unit_name_short_i18n, 295 my_custom.unit_name_short_i18n); 296 new_unit_allow_fraction := COALESCE (in_unit_allow_fraction, 297 my_custom.unit_allow_fraction); 298 new_unit_precision_level := COALESCE (in_unit_precision_level, 299 my_custom.unit_precision_level); 300 IF NOT new_unit_allow_fraction THEN 301 new_unit_precision_level := 0; 302 END IF; 303 304 new_unit_active := COALESCE (in_unit_active, my_custom.unit_active); 305 306 UPDATE merchant_custom_units SET 307 unit_name_long = new_unit_name_long 308 ,unit_name_long_i18n = new_unit_name_long_i18n 309 ,unit_name_short = new_unit_name_short 310 ,unit_name_short_i18n = new_unit_name_short_i18n 311 ,unit_allow_fraction = new_unit_allow_fraction 312 ,unit_precision_level = new_unit_precision_level 313 ,unit_active = new_unit_active 314 WHERE unit_serial = my_custom.unit_serial; 315 316 ASSERT FOUND,'SELECTED it earlier, should UPDATE it now'; 317 318 IF old_unit_allow_fraction IS DISTINCT FROM new_unit_allow_fraction 319 OR old_unit_precision_level IS DISTINCT FROM new_unit_precision_level 320 THEN 321 UPDATE merchant_inventory SET 322 allow_fractional_quantity = new_unit_allow_fraction 323 , fractional_precision_level = new_unit_precision_level 324 WHERE merchant_serial = my_merchant_id 325 AND unit = in_unit_id 326 AND allow_fractional_quantity = old_unit_allow_fraction 327 AND fractional_precision_level = old_unit_precision_level; 328 END IF; 329 RETURN; 330 END IF; 331 332 -- Try builtin with overrides. 333 SELECT * 334 INTO my_builtin 335 FROM merchant_builtin_units 336 WHERE unit = in_unit_id; 337 338 IF NOT FOUND THEN 339 out_no_unit := TRUE; 340 RETURN; 341 END IF; 342 343 SELECT * 344 INTO my_override 345 FROM merchant_builtin_unit_overrides 346 WHERE merchant_serial = my_merchant_id 347 AND builtin_unit_serial = my_builtin.unit_serial 348 FOR UPDATE; 349 350 old_unit_allow_fraction := COALESCE (my_override.override_allow_fraction, 351 my_builtin.unit_allow_fraction); 352 old_unit_precision_level := COALESCE (my_override.override_precision_level, 353 my_builtin.unit_precision_level); 354 old_unit_active := COALESCE (my_override.override_active, 355 my_builtin.unit_active); 356 357 -- Only policy flags can change for builtin units. 358 IF in_unit_name_long IS NOT NULL 359 OR in_unit_name_short IS NOT NULL 360 OR in_unit_name_long_i18n IS NOT NULL 361 OR in_unit_name_short_i18n IS NOT NULL THEN 362 out_builtin_conflict := TRUE; 363 RETURN; 364 END IF; 365 366 new_unit_allow_fraction := COALESCE (in_unit_allow_fraction, 367 old_unit_allow_fraction); 368 new_unit_precision_level := COALESCE (in_unit_precision_level, 369 old_unit_precision_level); 370 IF NOT new_unit_allow_fraction THEN 371 new_unit_precision_level := 0; 372 END IF; 373 new_unit_active := COALESCE (in_unit_active, old_unit_active); 374 375 INSERT INTO merchant_builtin_unit_overrides ( 376 merchant_serial, 377 builtin_unit_serial, 378 override_allow_fraction, 379 override_precision_level, 380 override_active) 381 VALUES (my_merchant_id, 382 my_builtin.unit_serial, 383 new_unit_allow_fraction, 384 new_unit_precision_level, 385 new_unit_active) 386 ON CONFLICT (merchant_serial, builtin_unit_serial) 387 DO UPDATE SET override_allow_fraction = EXCLUDED.override_allow_fraction 388 , override_precision_level = EXCLUDED.override_precision_level 389 , override_active = EXCLUDED.override_active; 390 391 IF old_unit_allow_fraction IS DISTINCT FROM new_unit_allow_fraction 392 OR old_unit_precision_level IS DISTINCT FROM new_unit_precision_level 393 THEN 394 UPDATE merchant_inventory SET 395 allow_fractional_quantity = new_unit_allow_fraction 396 , fractional_precision_level = new_unit_precision_level 397 WHERE merchant_serial = my_merchant_id 398 AND unit = in_unit_id 399 AND allow_fractional_quantity = old_unit_allow_fraction 400 AND fractional_precision_level = old_unit_precision_level; 401 END IF; 402 403 RETURN; 404 END $$; 405 406 DROP FUNCTION IF EXISTS merchant_do_delete_unit; 407 CREATE FUNCTION merchant_do_delete_unit ( 408 IN in_instance_id TEXT, 409 IN in_unit_id TEXT, 410 OUT out_no_instance BOOL, 411 OUT out_no_unit BOOL, 412 OUT out_builtin_conflict BOOL) 413 LANGUAGE plpgsql 414 AS $$ 415 DECLARE 416 my_merchant_id INT8; 417 my_unit merchant_custom_units%ROWTYPE; 418 BEGIN 419 out_no_instance := FALSE; 420 out_no_unit := FALSE; 421 out_builtin_conflict := FALSE; 422 423 SELECT merchant_serial 424 INTO my_merchant_id 425 FROM merchant_instances 426 WHERE merchant_id = in_instance_id; 427 428 IF NOT FOUND THEN 429 out_no_instance := TRUE; 430 RETURN; 431 END IF; 432 433 SELECT * 434 INTO my_unit 435 FROM merchant_custom_units 436 WHERE merchant_serial = my_merchant_id 437 AND unit = in_unit_id 438 FOR UPDATE; 439 440 IF NOT FOUND THEN 441 IF EXISTS (SELECT 1 FROM merchant_builtin_units bu WHERE bu.unit = in_unit_id) THEN 442 out_builtin_conflict := TRUE; 443 ELSE 444 out_no_unit := TRUE; 445 END IF; 446 RETURN; 447 END IF; 448 449 DELETE FROM merchant_custom_units 450 WHERE unit_serial = my_unit.unit_serial; 451 452 RETURN; 453 END $$; 454 455 COMMIT;