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