merchant-0025.sql (4835B)
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 17 -- @file merchant-0025.sql 18 -- @brief Expand settings to cover new default_wire_transfer_rounding_interval 19 -- @author Christian Grothoff 20 21 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 SELECT _v.register_patch('merchant-0025', NULL, NULL); 26 27 SET search_path TO public; 28 29 -- We will need this for LIKE-style pattern matching "gin" indices. 30 CREATE EXTENSION IF NOT EXISTS pg_trgm; 31 32 SET search_path TO merchant, public; 33 34 35 -- See enum GNUNET_TIME_RounderInterval 36 CREATE TYPE time_rounder_interval 37 AS ENUM ('NONE', 'SECOND', 'MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR'); 38 39 ALTER TABLE merchant_instances 40 ADD COLUMN default_wire_transfer_rounding_interval time_rounder_interval 41 NOT NULL DEFAULT 'NONE'::time_rounder_interval; 42 43 COMMENT ON COLUMN merchant_instances.default_wire_transfer_rounding_interval 44 IS 'To what round value do we round up wire transfer deadlines computed on the basis of the default_wire_transfer_delay.'; 45 46 ALTER TABLE merchant_contract_terms 47 ALTER COLUMN contract_terms 48 TYPE JSONB 49 USING convert_from(contract_terms, 'utf-8')::JSONB; 50 51 ALTER TABLE merchant_orders 52 ALTER COLUMN contract_terms 53 TYPE JSONB 54 USING convert_from(contract_terms, 'utf-8')::JSONB; 55 56 ALTER TABLE merchant_inventory 57 ALTER COLUMN description_i18n 58 TYPE JSONB 59 USING convert_from(description_i18n, 'utf-8')::JSONB, 60 ALTER COLUMN taxes 61 TYPE JSONB 62 USING convert_from(taxes, 'utf-8')::JSONB, 63 ALTER COLUMN address 64 TYPE JSONB 65 USING convert_from(address, 'utf-8')::JSONB; 66 67 ALTER TABLE merchant_instances 68 ALTER COLUMN jurisdiction 69 TYPE JSONB 70 USING convert_from(jurisdiction, 'utf-8')::JSONB, 71 ALTER COLUMN address 72 TYPE JSONB 73 USING convert_from(address, 'utf-8')::JSONB; 74 75 ALTER TABLE merchant_categories 76 ALTER COLUMN category_name_i18n 77 TYPE JSONB 78 USING convert_from(category_name_i18n, 'utf-8')::JSONB; 79 80 ALTER TABLE merchant_token_families 81 ALTER COLUMN description_i18n 82 TYPE JSONB 83 USING convert_from(description_i18n, 'utf-8')::JSONB, 84 ALTER COLUMN extra_data 85 TYPE JSONB 86 USING extra_data::JSONB; 87 88 ALTER TABLE merchant_exchange_keys 89 ALTER COLUMN keys_json 90 TYPE JSONB 91 USING keys_json::JSONB; 92 93 ALTER TABLE merchant_donau_keys 94 ALTER COLUMN keys_json 95 TYPE JSONB 96 USING keys_json::JSONB; 97 98 ALTER TABLE merchant_kyc 99 ALTER COLUMN jaccount_limits 100 TYPE JSONB 101 USING jaccount_limits::JSONB; 102 103 ALTER TABLE merchant_template 104 ALTER COLUMN editable_defaults 105 TYPE JSONB 106 USING editable_defaults::JSONB, 107 ALTER COLUMN template_contract 108 TYPE JSONB 109 USING template_contract::JSONB; 110 111 ALTER TABLE merchant_exchange_accounts 112 ALTER COLUMN debit_restrictions 113 TYPE JSONB 114 USING debit_restrictions::JSONB, 115 ALTER COLUMN credit_restrictions 116 TYPE JSONB 117 USING credit_restrictions::JSONB; 118 119 ALTER TABLE merchant_accounts 120 ALTER COLUMN credit_facade_credentials 121 TYPE JSONB 122 USING credit_facade_credentials::JSONB; 123 124 -- The following indices are added to support efficient product filtering. 125 -- Use a Generalized Inverted Index (GIN) on the respective columns. 126 -- We use "LOWER" so we can do case-insensitive searches. 127 -- "gin_trgm_ops" is the operator class for the index, specifically "trigram indexing" from pg_trgm 128 CREATE INDEX trgm_idx_products_by_name 129 ON merchant_inventory 130 USING gin (LOWER(product_name) gin_trgm_ops); 131 132 CREATE INDEX trgm_idx_products_by_description 133 ON merchant_inventory 134 USING gin (LOWER(description) gin_trgm_ops); 135 136 CREATE INDEX trgm_idx_categories_by_name 137 ON merchant_categories 138 USING gin (LOWER(category_name) gin_trgm_ops); 139 140 CREATE INDEX trgm_idx_contract_summaries 141 ON merchant_contract_terms 142 USING gin (LOWER((contract_terms ->> 'summary')) gin_trgm_ops); 143 -- NOTE: Query must use exactly 144 -- WHERE LOWER(contract_terms ->> 'summary') LIKE LOWER($1); 145 146 CREATE INDEX trgm_idx_order_summaries 147 ON merchant_orders 148 USING gin (LOWER((contract_terms ->> 'summary')) gin_trgm_ops); 149 -- NOTE: Query must use exactly 150 -- WHERE LOWER(contract_terms ->> 'summary') LIKE LOWER($1); 151 152 -- FIXME: consider 153 -- --- alas, requires modifications across the plugin... 154 155 COMMIT;