merchant-0027.sql (7272B)
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 COMMIT;