commit 8d8bd0f085fd2e8385dbfaad498b1fdd920f35df parent e8990e5b404a43ca612cd6c6e41e3d3fdac74221 Author: Christian Grothoff <christian@grothoff.org> Date: Thu, 30 Oct 2025 17:56:48 +0100 migrate DB to use JSONB values for JSON data Diffstat:
58 files changed, 659 insertions(+), 539 deletions(-)
diff --git a/src/backend/taler-merchant-httpd_private-get-orders.c b/src/backend/taler-merchant-httpd_private-get-orders.c @@ -880,10 +880,10 @@ TMH_private_get_orders (const struct TMH_RequestHandler *rh, TALER_EC_GENERIC_PARAMETER_MALFORMED, "offset"); } - pf->summary_filter = tr (MHD_lookup_connection_value (connection, + po->summary_filter = tr (MHD_lookup_connection_value (connection, MHD_GET_ARGUMENT_KIND, "summary_filter")); - pf->of.summary_filter = pf->summary_filter; /* just an alias! */ + po->of.summary_filter = po->summary_filter; /* just an (read-only) alias! */ po->of.session_id = MHD_lookup_connection_value (connection, MHD_GET_ARGUMENT_KIND, diff --git a/src/backenddb/merchant-0013.sql b/src/backenddb/merchant-0013.sql @@ -84,154 +84,25 @@ BEGIN END; $$ LANGUAGE plpgsql; -CREATE FUNCTION handle_category_changes() -RETURNS TRIGGER AS $$ -DECLARE - my_merchant_serial BIGINT; - resolved_body TEXT; - webhook RECORD; -- To iterate over all webhooks matching the event type +CREATE FUNCTION handle_inventory_changes() + RETURNS TRIGGER AS $$ BEGIN - -- Fetch the merchant_serial directly from the NEW or OLD row - my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial); - - -- INSERT case: Add a webhook for category addition - IF TG_OP = 'INSERT' THEN - FOR webhook IN - SELECT webhook_serial, - merchant_serial, - url, - http_method, - body_template - FROM merchant.merchant_webhook - WHERE event_type = 'category_added' - AND merchant_serial = my_merchant_serial - LOOP - -- Resolve placeholders for the current webhook - resolved_body := webhook.body_template; - resolved_body := replace_placeholder(resolved_body, - 'webhook_type', - 'category_added'); - resolved_body := replace_placeholder(resolved_body, - 'category_serial', - NEW.category_serial::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'category_name', - NEW.category_name); - resolved_body := replace_placeholder(resolved_body, - 'merchant_serial', - my_merchant_serial::TEXT); - - -- Insert into pending webhooks for this webhook - INSERT INTO merchant.merchant_pending_webhooks - (merchant_serial, webhook_serial, url, http_method, body) - VALUES - (webhook.merchant_serial, - webhook.webhook_serial, - webhook.url, - webhook.http_method, - resolved_body); - END LOOP; - - -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING - NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; - END IF; - - -- UPDATE case: Add a webhook for category update - IF TG_OP = 'UPDATE' THEN - FOR webhook IN - SELECT webhook_serial, - merchant_serial, - url, - http_method, - body_template - FROM merchant.merchant_webhook - WHERE event_type = 'category_updated' - AND merchant_serial = my_merchant_serial - LOOP - -- Resolve placeholders for the current webhook - resolved_body := webhook.body_template; - resolved_body := replace_placeholder(resolved_body, - 'webhook_type', - 'category_updated'); - resolved_body := replace_placeholder(resolved_body, - 'category_serial', - NEW.category_serial::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'old_category_name', - OLD.category_name); - resolved_body := replace_placeholder(resolved_body, - 'category_name', - NEW.category_name); - resolved_body := replace_placeholder(resolved_body, - 'category_name_i18n', - encode(NEW.category_name_i18n, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'old_category_name_i18n', - encode(OLD.category_name_i18n, - 'escape')); - - -- Insert into pending webhooks for this webhook - INSERT INTO merchant.merchant_pending_webhooks - (merchant_serial, webhook_serial, url, http_method, body) - VALUES - (webhook.merchant_serial, - webhook.webhook_serial, - webhook.url, - webhook.http_method, - resolved_body); - END LOOP; - - -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING - NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; - END IF; - - -- DELETE case: Add a webhook for category deletion - IF TG_OP = 'DELETE' THEN - FOR webhook IN - SELECT webhook_serial, - merchant_serial, - url, - http_method, - body_template - FROM merchant.merchant_webhook - WHERE event_type = 'category_deleted' - AND merchant_serial = my_merchant_serial - LOOP - -- Resolve placeholders for the current webhook - resolved_body := webhook.body_template; - resolved_body := replace_placeholder(resolved_body, - 'webhook_type', - 'category_deleted'); - resolved_body := replace_placeholder(resolved_body, - 'category_serial', - OLD.category_serial::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'category_name', - OLD.category_name); - - -- Insert into pending webhooks for this webhook - INSERT INTO merchant.merchant_pending_webhooks - (merchant_serial, webhook_serial, url, http_method, body) - VALUES - (webhook.merchant_serial, - webhook.webhook_serial, - webhook.url, - webhook.http_method, - resolved_body); - END LOOP; - - -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING - NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; - END IF; - + -- This is just a dummy function, the actual + -- implementation lives in pg_do_handle_inventory_changes.sql + -- and is loaded later! RETURN NULL; END; $$ LANGUAGE plpgsql; -COMMENT ON FUNCTION handle_category_changes - IS 'Trigger function to handle pending webhooks for category changes'; - +CREATE FUNCTION handle_category_changes() + RETURNS TRIGGER AS $$ +BEGIN + -- This is just a dummy function, the actual + -- implementation lives in pg_do_handle_category_changes.sql + -- and is loaded later! + RETURN NULL; +END; +$$ LANGUAGE plpgsql; -- Trigger to invoke the trigger function CREATE TRIGGER trigger_category_changes @@ -240,297 +111,6 @@ ON merchant_categories FOR EACH ROW EXECUTE FUNCTION handle_category_changes(); -CREATE FUNCTION handle_inventory_changes() - RETURNS TRIGGER AS $$ -DECLARE - my_merchant_serial BIGINT; - resolved_body TEXT; - webhook RECORD; -- To iterate over all matching webhooks -BEGIN - -- Fetch the merchant_serial directly from the NEW or OLD row - my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial); - - -- INSERT case: Notify webhooks for inventory addition - IF TG_OP = 'INSERT' THEN - FOR webhook IN - SELECT webhook_serial, - merchant_serial, - url, - http_method, - body_template - FROM merchant.merchant_webhook - WHERE event_type = 'inventory_added' - AND merchant_serial = my_merchant_serial - LOOP - -- Resolve placeholders for the current webhook - resolved_body := webhook.body_template; - resolved_body := replace_placeholder(resolved_body, - 'webhook_type', - 'inventory_added'); - resolved_body := replace_placeholder(resolved_body, - 'product_serial', - NEW.product_serial::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'product_id', - NEW.product_id); - resolved_body := replace_placeholder(resolved_body, - 'description', - NEW.description); - resolved_body := replace_placeholder(resolved_body, - 'description_i18n', - encode(NEW.description_i18n, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'unit', - NEW.unit); - resolved_body := replace_placeholder(resolved_body, - 'image', - NEW.image); - resolved_body := replace_placeholder(resolved_body, - 'taxes', - encode(NEW.taxes, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'price', - NEW.price::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_stock', - NEW.total_stock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_sold', - NEW.total_sold::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_lost', - NEW.total_lost::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'address', - encode(NEW.address, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'next_restock', - NEW.next_restock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'minimum_age', - NEW.minimum_age::TEXT); - - -- Insert into pending webhooks for this webhook - INSERT INTO merchant.merchant_pending_webhooks - (merchant_serial, webhook_serial, url, http_method, body) - VALUES - (webhook.merchant_serial, - webhook.webhook_serial, - webhook.url, - webhook.http_method, - resolved_body); - END LOOP; - - -- Notify the webhook service - NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; - END IF; - - -- UPDATE case: Notify webhooks for inventory update - IF TG_OP = 'UPDATE' THEN - FOR webhook IN - SELECT webhook_serial, - merchant_serial, - url, - http_method, - body_template - FROM merchant.merchant_webhook - WHERE event_type = 'inventory_updated' - AND merchant_serial = my_merchant_serial - LOOP - -- Resolve placeholders for the current webhook - resolved_body := webhook.body_template; - resolved_body := replace_placeholder(resolved_body, - 'webhook_type', - 'inventory_updated'); - resolved_body := replace_placeholder(resolved_body, - 'product_serial', - NEW.product_serial::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'product_id', - NEW.product_id); - resolved_body := replace_placeholder(resolved_body, - 'old_description', - OLD.description); - resolved_body := replace_placeholder(resolved_body, - 'description', - NEW.description); - resolved_body := replace_placeholder(resolved_body, - 'old_description_i18n', - encode(OLD.description_i18n, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'description_i18n', - encode(NEW.description_i18n, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'old_unit', - OLD.unit); - resolved_body := replace_placeholder(resolved_body, - 'unit', - NEW.unit); - resolved_body := replace_placeholder(resolved_body, - 'old_image', - OLD.image); - resolved_body := replace_placeholder(resolved_body, - 'image', - NEW.image); - resolved_body := replace_placeholder(resolved_body, - 'old_taxes', - encode(OLD.taxes, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'taxes', - encode(NEW.taxes, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'old_price', - OLD.price::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'price', - NEW.price::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'old_total_stock', - OLD.total_stock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_stock', - NEW.total_stock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'old_total_sold', - OLD.total_sold::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_sold', - NEW.total_sold::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'old_total_lost', - OLD.total_lost::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_lost', - NEW.total_lost::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'old_address', - encode(OLD.address, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'address', - encode(NEW.address, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'old_next_restock', - OLD.next_restock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'next_restock', - NEW.next_restock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'old_minimum_age', - OLD.minimum_age::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'minimum_age', - NEW.minimum_age::TEXT); - - -- Insert into pending webhooks for this webhook - INSERT INTO merchant.merchant_pending_webhooks - (merchant_serial, webhook_serial, url, http_method, body) - VALUES - (webhook.merchant_serial, - webhook.webhook_serial, - webhook.url, - webhook.http_method, - resolved_body); - END LOOP; - - -- Notify the webhook service - NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; - END IF; - - -- DELETE case: Notify webhooks for inventory deletion - IF TG_OP = 'DELETE' THEN - FOR webhook IN - SELECT webhook_serial, - merchant_serial, - url, - http_method, - body_template - FROM merchant.merchant_webhook - WHERE event_type = 'inventory_deleted' - AND merchant_serial = my_merchant_serial - LOOP - -- Resolve placeholders for the current webhook - resolved_body := webhook.body_template; - resolved_body := replace_placeholder(resolved_body, - 'webhook_type', - 'inventory_deleted'); - resolved_body := replace_placeholder(resolved_body, - 'product_serial', - OLD.product_serial::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'product_id', - OLD.product_id); - resolved_body := replace_placeholder(resolved_body, - 'description', - OLD.description); - resolved_body := replace_placeholder(resolved_body, - 'description_i18n', - encode(OLD.description_i18n, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'unit', - OLD.unit); - resolved_body := replace_placeholder(resolved_body, - 'image', - OLD.image); - resolved_body := replace_placeholder(resolved_body, - 'taxes', - encode(OLD.taxes, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'price', - OLD.price::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_stock', - OLD.total_stock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_sold', - OLD.total_sold::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'total_lost', - OLD.total_lost::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'address', - encode(OLD.address, - 'escape')); - resolved_body := replace_placeholder(resolved_body, - 'next_restock', - OLD.next_restock::TEXT); - resolved_body := replace_placeholder(resolved_body, - 'minimum_age', - OLD.minimum_age::TEXT); - - -- Insert into pending webhooks for this webhook - INSERT INTO merchant.merchant_pending_webhooks - (merchant_serial, webhook_serial, url, http_method, body) - VALUES - (webhook.merchant_serial, - webhook.webhook_serial, - webhook.url, - webhook.http_method, - resolved_body); - END LOOP; - - -- Notify the webhook service - NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; - END IF; - - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -COMMENT ON FUNCTION handle_inventory_changes - IS 'Function to handle inventory changes and notify webhooks'; - - -- Trigger to invoke the trigger function CREATE TRIGGER trigger_inventory_changes AFTER INSERT OR UPDATE OR DELETE diff --git a/src/backenddb/merchant-0025.sql b/src/backenddb/merchant-0025.sql @@ -34,11 +34,90 @@ CREATE TYPE time_rounder_interval AS ENUM ('NONE', 'SECOND', 'MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR'); ALTER TABLE merchant_instances - ADD COLUMN default_wire_transfer_rounding_interval time_rounder_interval NOT NULL DEFAULT 'NONE'::time_rounder_interval; + ADD COLUMN default_wire_transfer_rounding_interval time_rounder_interval + NOT NULL DEFAULT 'NONE'::time_rounder_interval; COMMENT ON COLUMN merchant_instances.default_wire_transfer_rounding_interval IS 'To what round value do we round up wire transfer deadlines computed on the basis of the default_wire_transfer_delay.'; +ALTER TABLE merchant_contract_terms + ALTER COLUMN contract_terms + TYPE JSONB + USING convert_from(contract_terms, 'utf-8')::JSONB; + +ALTER TABLE merchant_orders + ALTER COLUMN contract_terms + TYPE JSONB + USING convert_from(contract_terms, 'utf-8')::JSONB; + +ALTER TABLE merchant_inventory + ALTER COLUMN description_i18n + TYPE JSONB + USING convert_from(description_i18n, 'utf-8')::JSONB, + ALTER COLUMN taxes + TYPE JSONB + USING convert_from(taxes, 'utf-8')::JSONB, + ALTER COLUMN address + TYPE JSONB + USING convert_from(address, 'utf-8')::JSONB; + +ALTER TABLE merchant_instances + ALTER COLUMN jurisdiction + TYPE JSONB + USING convert_from(jurisdiction, 'utf-8')::JSONB, + ALTER COLUMN address + TYPE JSONB + USING convert_from(address, 'utf-8')::JSONB; + +ALTER TABLE merchant_categories + ALTER COLUMN category_name_i18n + TYPE JSONB + USING convert_from(category_name_i18n, 'utf-8')::JSONB; + +ALTER TABLE merchant_token_families + ALTER COLUMN description_i18n + TYPE JSONB + USING convert_from(description_i18n, 'utf-8')::JSONB, + ALTER COLUMN extra_data + TYPE JSONB + USING extra_data::JSONB; + +ALTER TABLE merchant_exchange_keys + ALTER COLUMN keys_json + TYPE JSONB + USING keys_json::JSONB; + +ALTER TABLE merchant_donau_keys + ALTER COLUMN keys_json + TYPE JSONB + USING keys_json::JSONB; + +ALTER TABLE merchant_kyc + ALTER COLUMN jaccount_limits + TYPE JSONB + USING jaccount_limits::JSONB; + +ALTER TABLE merchant_template + ALTER COLUMN editable_defaults + TYPE JSONB + USING editable_defaults::JSONB, + ALTER COLUMN template_contract + TYPE JSONB + USING template_contract::JSONB; + +ALTER TABLE merchant_exchange_accounts + ALTER COLUMN debit_restrictions + TYPE JSONB + USING debit_restrictions::JSONB, + ALTER COLUMN credit_restrictions + TYPE JSONB + USING credit_restrictions::JSONB; + +ALTER TABLE merchant_accounts + ALTER COLUMN credit_facade_credentials + TYPE JSONB + USING credit_facade_credentials::JSONB; + -- The following indices are added to support efficient product filtering. -- Use a Generalized Inverted Index (GIN) on the respective columns. -- We use "LOWER" so we can do case-insensitive searches. @@ -57,19 +136,17 @@ CREATE INDEX trgm_idx_categories_by_name CREATE INDEX trgm_idx_contract_summaries ON merchant_contract_terms - USING gin (LOWER((contract_terms::jsonb ->> 'summary')) gin_trgm_ops); + USING gin (LOWER((contract_terms ->> 'summary')) gin_trgm_ops); -- NOTE: Query must use exactly --- WHERE LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($1); +-- WHERE LOWER(contract_terms ->> 'summary') LIKE LOWER($1); CREATE INDEX trgm_idx_order_summaries ON merchant_orders - USING gin (LOWER((contract_terms::jsonb ->> 'summary')) gin_trgm_ops); + USING gin (LOWER((contract_terms ->> 'summary')) gin_trgm_ops); -- NOTE: Query must use exactly --- WHERE LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($1); +-- WHERE LOWER(contract_terms ->> 'summary') LIKE LOWER($1); -- FIXME: consider --- ALTER TABLE merchant_contract_terms ALTER COLUMN contract_terms TYPE JSONB USING contract_terms::jsonb; --- ALTER TABLE merchant_orders ALTER COLUMN contract_terms TYPE JSONB USING contract_terms::jsonb; -- --- alas, requires modifications across the plugin... COMMIT; diff --git a/src/backenddb/pg_account_kyc_get_status.c b/src/backenddb/pg_account_kyc_get_status.c @@ -182,7 +182,7 @@ TMH_PG_account_kyc_get_status ( ",mk.exchange_http_status" ",mk.exchange_ec_code" ",mk.aml_review" - ",mk.jaccount_limits" + ",mk.jaccount_limits::TEXT" " FROM merchant_instances mi" " JOIN merchant_accounts ma" " USING (merchant_serial)" diff --git a/src/backenddb/pg_account_kyc_set_status.c b/src/backenddb/pg_account_kyc_set_status.c @@ -96,7 +96,8 @@ TMH_PG_account_kyc_set_status ( " out_no_instance AS no_instance" " ,out_no_account AS no_account" " FROM merchant_do_account_kyc_set_status" - "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);"); + "($1, $2, $3, $4, $5, $6, $7, $8::TEXT::JSONB" + ",$9, $10, $11, $12, $13);"); qs = GNUNET_PQ_eval_prepared_singleton_select ( pg->conn, "account_kyc_set_status", diff --git a/src/backenddb/pg_account_kyc_set_status.sql b/src/backenddb/pg_account_kyc_set_status.sql @@ -25,7 +25,7 @@ CREATE FUNCTION merchant_do_account_kyc_set_status ( IN in_exchange_http_status INT4, IN in_exchange_ec_code INT4, IN in_access_token BYTEA, -- can be NULL - IN in_jlimits TEXT, + IN in_jlimits JSONB, IN in_aml_active BOOL, IN in_kyc_ok BOOL, IN in_notify_str TEXT, diff --git a/src/backenddb/pg_do_handle_category_changes.sql b/src/backenddb/pg_do_handle_category_changes.sql @@ -0,0 +1,162 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2024, 2025 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +-- NOTE: do not use DROP, that would remove the TRIGGER! +CREATE OR REPLACE FUNCTION handle_category_changes() +RETURNS TRIGGER AS $$ +DECLARE + my_merchant_serial BIGINT; + resolved_body TEXT; + webhook RECORD; -- To iterate over all webhooks matching the event type +BEGIN + -- Fetch the merchant_serial directly from the NEW or OLD row + my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial); + + -- INSERT case: Add a webhook for category addition + IF TG_OP = 'INSERT' THEN + FOR webhook IN + SELECT webhook_serial, + merchant_serial, + url, + http_method, + body_template + FROM merchant.merchant_webhook + WHERE event_type = 'category_added' + AND merchant_serial = my_merchant_serial + LOOP + -- Resolve placeholders for the current webhook + resolved_body := webhook.body_template; + resolved_body := replace_placeholder(resolved_body, + 'webhook_type', + 'category_added'); + resolved_body := replace_placeholder(resolved_body, + 'category_serial', + NEW.category_serial::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'category_name', + NEW.category_name); + resolved_body := replace_placeholder(resolved_body, + 'merchant_serial', + my_merchant_serial::TEXT); + + -- Insert into pending webhooks for this webhook + INSERT INTO merchant.merchant_pending_webhooks + (merchant_serial, webhook_serial, url, http_method, body) + VALUES + (webhook.merchant_serial, + webhook.webhook_serial, + webhook.url, + webhook.http_method, + resolved_body); + END LOOP; + + -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING + NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; + END IF; + + -- UPDATE case: Add a webhook for category update + IF TG_OP = 'UPDATE' THEN + FOR webhook IN + SELECT webhook_serial, + merchant_serial, + url, + http_method, + body_template + FROM merchant.merchant_webhook + WHERE event_type = 'category_updated' + AND merchant_serial = my_merchant_serial + LOOP + -- Resolve placeholders for the current webhook + resolved_body := webhook.body_template; + resolved_body := replace_placeholder(resolved_body, + 'webhook_type', + 'category_updated'); + resolved_body := replace_placeholder(resolved_body, + 'category_serial', + NEW.category_serial::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_category_name', + OLD.category_name); + resolved_body := replace_placeholder(resolved_body, + 'category_name', + NEW.category_name); + resolved_body := replace_placeholder(resolved_body, + 'category_name_i18n', + NEW.category_name_i18n::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_category_name_i18n', + OLD.category_name_i18n::TEXT); + + -- Insert into pending webhooks for this webhook + INSERT INTO merchant.merchant_pending_webhooks + (merchant_serial, webhook_serial, url, http_method, body) + VALUES + (webhook.merchant_serial, + webhook.webhook_serial, + webhook.url, + webhook.http_method, + resolved_body); + END LOOP; + + -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING + NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; + END IF; + + -- DELETE case: Add a webhook for category deletion + IF TG_OP = 'DELETE' THEN + FOR webhook IN + SELECT webhook_serial, + merchant_serial, + url, + http_method, + body_template + FROM merchant.merchant_webhook + WHERE event_type = 'category_deleted' + AND merchant_serial = my_merchant_serial + LOOP + -- Resolve placeholders for the current webhook + resolved_body := webhook.body_template; + resolved_body := replace_placeholder(resolved_body, + 'webhook_type', + 'category_deleted'); + resolved_body := replace_placeholder(resolved_body, + 'category_serial', + OLD.category_serial::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'category_name', + OLD.category_name); + + -- Insert into pending webhooks for this webhook + INSERT INTO merchant.merchant_pending_webhooks + (merchant_serial, webhook_serial, url, http_method, body) + VALUES + (webhook.merchant_serial, + webhook.webhook_serial, + webhook.url, + webhook.http_method, + resolved_body); + END LOOP; + + -- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING + NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; + END IF; + + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +COMMENT ON FUNCTION handle_category_changes + IS 'Trigger function to handle pending webhooks for category changes'; diff --git a/src/backenddb/pg_do_handle_inventory_changes.sql b/src/backenddb/pg_do_handle_inventory_changes.sql @@ -0,0 +1,294 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2024, 2025 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +-- NOTE: do not use DROP, that would remove the TRIGGER! +CREATE OR REPLACE FUNCTION handle_inventory_changes() + RETURNS TRIGGER AS $$ +DECLARE + my_merchant_serial BIGINT; + resolved_body TEXT; + webhook RECORD; -- To iterate over all matching webhooks +BEGIN + -- Fetch the merchant_serial directly from the NEW or OLD row + my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial); + + -- INSERT case: Notify webhooks for inventory addition + IF TG_OP = 'INSERT' THEN + FOR webhook IN + SELECT webhook_serial, + merchant_serial, + url, + http_method, + body_template + FROM merchant.merchant_webhook + WHERE event_type = 'inventory_added' + AND merchant_serial = my_merchant_serial + LOOP + -- Resolve placeholders for the current webhook + resolved_body := webhook.body_template; + resolved_body := replace_placeholder(resolved_body, + 'webhook_type', + 'inventory_added'); + resolved_body := replace_placeholder(resolved_body, + 'product_serial', + NEW.product_serial::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'product_id', + NEW.product_id); + resolved_body := replace_placeholder(resolved_body, + 'description', + NEW.description); + resolved_body := replace_placeholder(resolved_body, + 'description_i18n', + NEW.description_i18n::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'unit', + NEW.unit); + resolved_body := replace_placeholder(resolved_body, + 'image', + NEW.image); + resolved_body := replace_placeholder(resolved_body, + 'taxes', + NEW.taxes::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'price', + NEW.price::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_stock', + NEW.total_stock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_sold', + NEW.total_sold::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_lost', + NEW.total_lost::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'address', + NEW.address::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'next_restock', + NEW.next_restock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'minimum_age', + NEW.minimum_age::TEXT); + + -- Insert into pending webhooks for this webhook + INSERT INTO merchant.merchant_pending_webhooks + (merchant_serial, webhook_serial, url, http_method, body) + VALUES + (webhook.merchant_serial, + webhook.webhook_serial, + webhook.url, + webhook.http_method, + resolved_body); + END LOOP; + + -- Notify the webhook service + NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; + END IF; + + -- UPDATE case: Notify webhooks for inventory update + IF TG_OP = 'UPDATE' THEN + FOR webhook IN + SELECT webhook_serial, + merchant_serial, + url, + http_method, + body_template + FROM merchant.merchant_webhook + WHERE event_type = 'inventory_updated' + AND merchant_serial = my_merchant_serial + LOOP + -- Resolve placeholders for the current webhook + resolved_body := webhook.body_template; + resolved_body := replace_placeholder(resolved_body, + 'webhook_type', + 'inventory_updated'); + resolved_body := replace_placeholder(resolved_body, + 'product_serial', + NEW.product_serial::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'product_id', + NEW.product_id); + resolved_body := replace_placeholder(resolved_body, + 'old_description', + OLD.description); + resolved_body := replace_placeholder(resolved_body, + 'description', + NEW.description); + resolved_body := replace_placeholder(resolved_body, + 'old_description_i18n', + OLD.description_i18n::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'description_i18n', + NEW.description_i18n::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_unit', + OLD.unit); + resolved_body := replace_placeholder(resolved_body, + 'unit', + NEW.unit); + resolved_body := replace_placeholder(resolved_body, + 'old_image', + OLD.image); + resolved_body := replace_placeholder(resolved_body, + 'image', + NEW.image); + resolved_body := replace_placeholder(resolved_body, + 'old_taxes', + OLD.taxes::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'taxes', + NEW.taxes::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_price', + OLD.price::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'price', + NEW.price::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_total_stock', + OLD.total_stock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_stock', + NEW.total_stock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_total_sold', + OLD.total_sold::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_sold', + NEW.total_sold::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_total_lost', + OLD.total_lost::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_lost', + NEW.total_lost::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_address', + OLD.address::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'address', + NEW.address::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_next_restock', + OLD.next_restock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'next_restock', + NEW.next_restock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'old_minimum_age', + OLD.minimum_age::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'minimum_age', + NEW.minimum_age::TEXT); + + -- Insert into pending webhooks for this webhook + INSERT INTO merchant.merchant_pending_webhooks + (merchant_serial, webhook_serial, url, http_method, body) + VALUES + (webhook.merchant_serial, + webhook.webhook_serial, + webhook.url, + webhook.http_method, + resolved_body); + END LOOP; + + -- Notify the webhook service + NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; + END IF; + + -- DELETE case: Notify webhooks for inventory deletion + IF TG_OP = 'DELETE' THEN + FOR webhook IN + SELECT webhook_serial, + merchant_serial, + url, + http_method, + body_template + FROM merchant.merchant_webhook + WHERE event_type = 'inventory_deleted' + AND merchant_serial = my_merchant_serial + LOOP + -- Resolve placeholders for the current webhook + resolved_body := webhook.body_template; + resolved_body := replace_placeholder(resolved_body, + 'webhook_type', + 'inventory_deleted'); + resolved_body := replace_placeholder(resolved_body, + 'product_serial', + OLD.product_serial::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'product_id', + OLD.product_id); + resolved_body := replace_placeholder(resolved_body, + 'description', + OLD.description); + resolved_body := replace_placeholder(resolved_body, + 'description_i18n', + OLD.description_i18n::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'unit', + OLD.unit); + resolved_body := replace_placeholder(resolved_body, + 'image', + OLD.image); + resolved_body := replace_placeholder(resolved_body, + 'taxes', + OLD.taxes::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'price', + OLD.price::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_stock', + OLD.total_stock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_sold', + OLD.total_sold::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'total_lost', + OLD.total_lost::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'address', + OLD.address::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'next_restock', + OLD.next_restock::TEXT); + resolved_body := replace_placeholder(resolved_body, + 'minimum_age', + OLD.minimum_age::TEXT); + + -- Insert into pending webhooks for this webhook + INSERT INTO merchant.merchant_pending_webhooks + (merchant_serial, webhook_serial, url, http_method, body) + VALUES + (webhook.merchant_serial, + webhook.webhook_serial, + webhook.url, + webhook.http_method, + resolved_body); + END LOOP; + + -- Notify the webhook service + NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; + END IF; + + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +COMMENT ON FUNCTION handle_inventory_changes + IS 'Function to handle inventory changes and notify webhooks'; diff --git a/src/backenddb/pg_get_kyc_limits.c b/src/backenddb/pg_get_kyc_limits.c @@ -59,7 +59,7 @@ TMH_PG_get_kyc_limits ( "get_kyc_limits", "SELECT" " mk.kyc_ok" - ",mk.jaccount_limits" + ",mk.jaccount_limits::TEXT" ",mk.access_token IS NULL AS no_access_token" " FROM merchant_kyc mk" " WHERE mk.exchange_url=$3" diff --git a/src/backenddb/pg_get_kyc_status.c b/src/backenddb/pg_get_kyc_status.c @@ -88,7 +88,7 @@ TMH_PG_get_kyc_status ( ",mk.last_rule_gen" ",mk.kyc_timestamp" ",mk.aml_review" - ",mk.jaccount_limits" + ",mk.jaccount_limits::TEXT" " FROM merchant_kyc mk" " WHERE mk.exchange_url=$3" " AND mk.account_serial=" diff --git a/src/backenddb/pg_insert_account.c b/src/backenddb/pg_insert_account.c @@ -58,7 +58,7 @@ TMH_PG_insert_account ( ",credit_facade_url" ",credit_facade_credentials" ",active)" - " SELECT merchant_serial, $2, $3, $4, $5, $6, $7" + " SELECT merchant_serial, $2, $3, $4, $5, $6::TEXT::JSONB, $7" " FROM merchant_instances" " WHERE merchant_id=$1" " ON CONFLICT(merchant_serial,payto_uri)" diff --git a/src/backenddb/pg_insert_category.c b/src/backenddb/pg_insert_category.c @@ -55,7 +55,7 @@ TMH_PG_insert_category (void *cls, ",category_name_i18n" ")" " SELECT merchant_serial," - " $2, $3" + " $2, $3::TEXT::JSONB" " FROM merchant_instances" " WHERE merchant_id=$1" " RETURNING category_serial"); diff --git a/src/backenddb/pg_insert_contract_terms.c b/src/backenddb/pg_insert_contract_terms.c @@ -111,7 +111,7 @@ TMH_PG_insert_contract_terms ( " mo.order_serial" ",mo.merchant_serial" ",mo.order_id" - ",$3" /* contract_terms */ + ",$3::TEXT::JSONB" /* contract_terms */ ",$4" /* h_contract_terms */ ",mo.creation_time" ",$5" /* pay_deadline */ diff --git a/src/backenddb/pg_insert_exchange_account.c b/src/backenddb/pg_insert_exchange_account.c @@ -59,7 +59,7 @@ TMH_PG_insert_exchange_account ( ",debit_restrictions" ",credit_restrictions" ",master_sig)" - " VALUES ($1, $2, $3, $4, $5, $6);"); + " VALUES ($1, $2, $3, $4::TEXT::JSONB, $5::TEXT::JSONB, $6);"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, "insert_exchange_account", params); diff --git a/src/backenddb/pg_insert_exchange_keys.c b/src/backenddb/pg_insert_exchange_keys.c @@ -51,11 +51,11 @@ TMH_PG_insert_exchange_keys ( ",first_retry" ",expiration_time" ",exchange_url" - ") VALUES ($1, $2, $3, $4);"); + ") VALUES ($1::TEXT::JSONB, $2, $3, $4);"); PREPARE (pg, "update_exchange_keys", "UPDATE merchant_exchange_keys SET" - " keys_json=$1" + " keys_json=$1::TEXT::JSONB" ",first_retry=$2" ",expiration_time=$3" " WHERE" diff --git a/src/backenddb/pg_insert_instance.c b/src/backenddb/pg_insert_instance.c @@ -100,7 +100,7 @@ TMH_PG_insert_instance ( ",validation_needed" ",default_wire_transfer_rounding_interval)" "VALUES" - "($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11," + "($1,$2,$3,$4,$5,$6::TEXT::JSONB,$7::TEXT::JSONB,$8,$9,$10,$11," "$12,$13,$14,$15,$16,$17,$18,$19::time_rounder_interval)"); PREPARE (pg, "insert_keys", diff --git a/src/backenddb/pg_insert_order.c b/src/backenddb/pg_insert_order.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022, 2023 Taler Systems SA + Copyright (C) 2022, 2023, 2025 Taler Systems SA TALER is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -86,7 +86,7 @@ TMH_PG_insert_order (void *cls, ",session_id" ",fulfillment_url)" " SELECT merchant_serial," - " $2, $3, $4, $5, $6, $7, $8, $9, $10, $11" + " $2, $3, $4, $5, $6, $7::TEXT::JSONB, $8, $9, $10, $11" " FROM merchant_instances" " WHERE merchant_id=$1"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, diff --git a/src/backenddb/pg_insert_product.c b/src/backenddb/pg_insert_product.c @@ -82,7 +82,8 @@ TMH_PG_insert_product (void *cls, ",out_no_cat AS no_cat" ",out_no_instance AS no_instance" " FROM merchant_do_insert_product" - "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14);"); + "($1, $2, $3, $4::TEXT::JSONB, $5, $6, $7::TEXT::JSONB, $8" + ",$9, $10::TEXT::JSONB, $11, $12, $13, $14);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "insert_product", params, diff --git a/src/backenddb/pg_insert_product.sql b/src/backenddb/pg_insert_product.sql @@ -20,13 +20,13 @@ CREATE FUNCTION merchant_do_insert_product ( IN in_instance_id TEXT, IN in_product_id TEXT, IN in_description TEXT, - IN in_description_i18n BYTEA, + IN in_description_i18n JSONB, IN in_unit TEXT, IN in_image TEXT, - IN in_taxes BYTEA, + IN in_taxes JSONB, IN in_price taler_amount_currency, IN in_total_stock INT8, - IN in_address BYTEA, + IN in_address JSONB, IN in_next_restock INT8, IN in_minimum_age INT4, IN ina_categories INT8[], diff --git a/src/backenddb/pg_insert_template.c b/src/backenddb/pg_insert_template.c @@ -60,7 +60,7 @@ TMH_PG_insert_template (void *cls, ",editable_defaults" ")" " SELECT merchant_serial," - " $2, $3, $4, $5, $6" + " $2, $3, $4, $5::TEXT::JSONB, $6::TEXT::JSONB" " FROM merchant_instances" " WHERE merchant_id=$1"); return GNUNET_PQ_eval_prepared_non_select (pg->conn, diff --git a/src/backenddb/pg_insert_token_family.c b/src/backenddb/pg_insert_token_family.c @@ -64,7 +64,8 @@ TMH_PG_insert_token_family ( ",validity_granularity" ",start_offset" ",kind)" - " SELECT merchant_serial, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12" + " SELECT merchant_serial, $2, $3, $4, $5::TEXT::JSONB," + " $6::TEXT::JSONB, $7, $8, $9, $10, $11, $12" " FROM merchant_instances" " WHERE merchant_id=$1" " ON CONFLICT DO NOTHING;"); diff --git a/src/backenddb/pg_lookup_all_products.c b/src/backenddb/pg_lookup_all_products.c @@ -158,16 +158,16 @@ TMH_PG_lookup_all_products (void *cls, "lookup_all_products", "SELECT" " description" - ",description_i18n" + ",description_i18n::TEXT" ",product_name" ",unit" ",price" - ",taxes" + ",taxes::TEXT" ",total_stock" ",total_sold" ",total_lost" ",image" - ",minv.address" + ",minv.address::TEXT" ",next_restock" ",minimum_age" ",product_id" diff --git a/src/backenddb/pg_lookup_categories.c b/src/backenddb/pg_lookup_categories.c @@ -126,7 +126,7 @@ TMH_PG_lookup_categories (void *cls, "SELECT" " mc.category_serial" ",mc.category_name" - ",mc.category_name_i18n" + ",mc.category_name_i18n::TEXT" ",COALESCE(COUNT(mpc.product_serial),0)" " AS product_count" " FROM merchant_categories mc" diff --git a/src/backenddb/pg_lookup_contract_terms.c b/src/backenddb/pg_lookup_contract_terms.c @@ -59,7 +59,7 @@ TMH_PG_lookup_contract_terms ( PREPARE (pg, "lookup_contract_terms", "SELECT" - " contract_terms" + " contract_terms::TEXT" ",order_serial" ",claim_token" " FROM merchant_contract_terms" diff --git a/src/backenddb/pg_lookup_contract_terms2.c b/src/backenddb/pg_lookup_contract_terms2.c @@ -71,7 +71,7 @@ TMH_PG_lookup_contract_terms2 ( PREPARE (pg, "lookup_contract_terms2", "SELECT" - " contract_terms" + " contract_terms::TEXT" ",order_serial" ",claim_token" ",paid" diff --git a/src/backenddb/pg_lookup_contract_terms3.c b/src/backenddb/pg_lookup_contract_terms3.c @@ -83,7 +83,7 @@ TMH_PG_lookup_contract_terms3 ( PREPARE (pg, "lookup_contract_terms3", "SELECT" - " contract_terms" + " contract_terms::TEXT" ",order_serial" ",claim_token" ",paid" diff --git a/src/backenddb/pg_lookup_donau_keys.c b/src/backenddb/pg_lookup_donau_keys.c @@ -53,7 +53,7 @@ TMH_PG_lookup_donau_keys (void *cls, "lookup_donau_keys", "SELECT" " first_retry" - ",keys_json" + ",keys_json::TEXT" " FROM merchant_donau_keys" " WHERE donau_url=$1;"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, @@ -70,4 +70,4 @@ TMH_PG_lookup_donau_keys (void *cls, return GNUNET_DB_STATUS_HARD_ERROR; } return qs; -} -\ No newline at end of file +} diff --git a/src/backenddb/pg_lookup_instances.c b/src/backenddb/pg_lookup_instances.c @@ -200,8 +200,8 @@ TMH_PG_lookup_instances (void *cls, ",mi.auth_salt" ",mi.merchant_id" ",mi.merchant_name" - ",mi.address" - ",mi.jurisdiction" + ",mi.address::TEXT" + ",mi.jurisdiction::TEXT" ",mi.use_stefan" ",mi.default_wire_transfer_delay" ",mi.default_pay_delay" @@ -226,8 +226,8 @@ TMH_PG_lookup_instances (void *cls, ",mi.auth_salt" ",mi.merchant_id" ",mi.merchant_name" - ",mi.address" - ",mi.jurisdiction" + ",mi.address::TEXT" + ",mi.jurisdiction::TEXT" ",mi.use_stefan" ",mi.default_wire_transfer_delay" ",mi.default_pay_delay" @@ -286,8 +286,8 @@ TMH_PG_lookup_instance (void *cls, ",mi.auth_salt" ",mi.merchant_id" ",mi.merchant_name" - ",mi.address" - ",mi.jurisdiction" + ",mi.address::TEXT" + ",mi.jurisdiction::TEXT" ",mi.use_stefan" ",mi.default_wire_transfer_delay" ",mi.default_pay_delay" @@ -313,8 +313,8 @@ TMH_PG_lookup_instance (void *cls, ",mi.auth_salt" ",mi.merchant_id" ",mi.merchant_name" - ",mi.address" - ",mi.jurisdiction" + ",mi.address::TEXT" + ",mi.jurisdiction::TEXT" ",mi.use_stefan" ",mi.default_wire_transfer_delay" ",mi.default_pay_delay" diff --git a/src/backenddb/pg_lookup_order.c b/src/backenddb/pg_lookup_order.c @@ -60,7 +60,7 @@ TMH_PG_lookup_order (void *cls, PREPARE (pg, "lookup_order", "SELECT" - " contract_terms" + " contract_terms::TEXT" ",claim_token" ",h_post_data" ",pos_key" diff --git a/src/backenddb/pg_lookup_order_charity.c b/src/backenddb/pg_lookup_order_charity.c @@ -75,7 +75,7 @@ TMH_PG_lookup_order_charity ( " di.donau_instances_serial" " ,di.charity_id" " ,k.merchant_priv" - " ,dk.keys_json" + " ,dk.keys_json::TEXT" " ,di.charity_max_per_year" " ,di.charity_receipts_to_date" " FROM merchant_donau_instances di" @@ -92,4 +92,4 @@ TMH_PG_lookup_order_charity ( "lookup_donau_charity", params, rs); -} -\ No newline at end of file +} diff --git a/src/backenddb/pg_lookup_orders.c b/src/backenddb/pg_lookup_orders.c @@ -177,8 +177,8 @@ TMH_PG_lookup_orders (void *cls, " ($12 = session_id))" " AND ($13 OR " " ($14 = fulfillment_url))" - " AND ( ($15 IS NULL) OR " - " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )" + " AND ( ($15::TEXT IS NULL) OR " + " (LOWER(contract_terms ->> 'summary') LIKE LOWER($15)) )" " ORDER BY order_serial DESC" " LIMIT $2)" "UNION " /* union ensures elements are distinct! */ @@ -207,8 +207,8 @@ TMH_PG_lookup_orders (void *cls, " ($12 = session_id))" " AND ($13 OR " " ($14 = fulfillment_url))" - " AND ( ($15 IS NULL) OR " - " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )" + " AND ( ($15::TEXT IS NULL) OR " + " (LOWER(contract_terms ->> 'summary') LIKE LOWER($15)) )" " ORDER BY order_serial DESC" " LIMIT $2)" " ORDER BY order_serial DESC" @@ -243,8 +243,8 @@ TMH_PG_lookup_orders (void *cls, " ($12 = session_id))" " AND ($13 OR " " ($14 = fulfillment_url))" - " AND ( ($15 IS NULL) OR " - " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )" + " AND ( ($15::TEXT IS NULL) OR " + " (LOWER(contract_terms ->> 'summary') LIKE LOWER($15)) )" " ORDER BY order_serial ASC" " LIMIT $2)" "UNION " /* union ensures elements are distinct! */ @@ -273,8 +273,8 @@ TMH_PG_lookup_orders (void *cls, " ($12 = session_id))" " AND ($13 OR " " ($14 = fulfillment_url))" - " AND ( ($15 IS NULL) OR " - " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )" + " AND ( ($15::TEXT IS NULL) OR " + " (LOWER(contract_terms ->> 'summary') LIKE LOWER($15)) )" " ORDER BY order_serial ASC" " LIMIT $2)" " ORDER BY order_serial ASC" diff --git a/src/backenddb/pg_lookup_product.c b/src/backenddb/pg_lookup_product.c @@ -45,16 +45,16 @@ TMH_PG_lookup_product (void *cls, "lookup_product", "SELECT" " mi.description" - ",mi.description_i18n" + ",mi.description_i18n::TEXT" ",mi.product_name" ",mi.unit" ",mi.price" - ",mi.taxes" + ",mi.taxes::TEXT" ",mi.total_stock" ",mi.total_sold" ",mi.total_lost" ",mi.image" - ",mi.address" + ",mi.address::TEXT" ",mi.next_restock" ",mi.minimum_age" ",t.category_array AS categories" diff --git a/src/backenddb/pg_lookup_products.c b/src/backenddb/pg_lookup_products.c @@ -113,7 +113,6 @@ TMH_PG_lookup_products (void *cls, struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_string (instance_id), GNUNET_PQ_query_param_uint64 (&offset), - GNUNET_PQ_query_param_uint64 (&plimit), NULL == category_filter ? GNUNET_PQ_query_param_null () : GNUNET_PQ_query_param_string (category_filter), @@ -123,6 +122,7 @@ TMH_PG_lookup_products (void *cls, NULL == description_filter ? GNUNET_PQ_query_param_null () : GNUNET_PQ_query_param_string (description_filter), + GNUNET_PQ_query_param_uint64 (&plimit), GNUNET_PQ_query_param_end }; enum GNUNET_DB_QueryStatus qs; @@ -138,7 +138,7 @@ TMH_PG_lookup_products (void *cls, " USING (merchant_serial)" " WHERE merchant_instances.merchant_id=$1" " AND product_serial > $2" - " AND ( ($3 IS NULL) OR" + " AND ( ($3::TEXT IS NULL) OR" " (product_serial IN" " (SELECT product_serial" " FROM merchant_product_categories" @@ -146,9 +146,9 @@ TMH_PG_lookup_products (void *cls, " (SELECT category_serial" " FROM merchant_categories" " WHERE category_name LIKE LOWER($3)))) )" - " AND ( ($4 IS NULL) OR" + " AND ( ($4::TEXT IS NULL) OR" " (product_name LIKE LOWER($4)) )" - " AND ( ($5 IS NULL) OR" + " AND ( ($5::TEXT IS NULL) OR" " (description LIKE LOWER($5)) )" " ORDER BY product_serial ASC" " LIMIT $6"); @@ -162,7 +162,7 @@ TMH_PG_lookup_products (void *cls, " USING (merchant_serial)" " WHERE merchant_instances.merchant_id=$1" " AND product_serial < $2" - " AND ( ($3 IS NULL) OR" + " AND ( ($3::TEXT IS NULL) OR" " (product_serial IN" " (SELECT product_serial" " FROM merchant_product_categories" @@ -170,9 +170,9 @@ TMH_PG_lookup_products (void *cls, " (SELECT category_serial" " FROM merchant_categories" " WHERE category_name LIKE LOWER($3)))) )" - " AND ( ($4 IS NULL) OR" + " AND ( ($4::TEXT IS NULL) OR" " (product_name LIKE LOWER($4)) )" - " AND ( ($5 IS NULL) OR" + " AND ( ($5::TEXT IS NULL) OR" " (description LIKE LOWER($5)) )" " ORDER BY product_serial DESC" " LIMIT $6"); diff --git a/src/backenddb/pg_lookup_template.c b/src/backenddb/pg_lookup_template.c @@ -55,8 +55,8 @@ TMH_PG_lookup_template (void *cls, "SELECT" " mt.template_description" ",mod.otp_id" - ",mt.template_contract" - ",mt.editable_defaults" + ",mt.template_contract::TEXT" + ",mt.editable_defaults::TEXT" " FROM merchant_template mt" " JOIN merchant_instances mi" " ON (mi.merchant_serial = mt.merchant_serial)" diff --git a/src/backenddb/pg_lookup_token_family.c b/src/backenddb/pg_lookup_token_family.c @@ -98,8 +98,8 @@ TMH_PG_lookup_token_family ( ",name" ",cipher_choice" ",description" - ",description_i18n" - ",extra_data" + ",description_i18n::TEXT" + ",extra_data::TEXT" ",valid_after" ",valid_before" ",duration" diff --git a/src/backenddb/pg_lookup_token_family_key.c b/src/backenddb/pg_lookup_token_family_key.c @@ -61,7 +61,7 @@ TMH_PG_lookup_token_family_key ( ",slug" ",name" ",description" - ",description_i18n" + ",description_i18n::TEXT" ",mtf.valid_after" ",mtf.valid_before" ",duration" diff --git a/src/backenddb/pg_lookup_token_family_keys.c b/src/backenddb/pg_lookup_token_family_keys.c @@ -194,7 +194,7 @@ TMH_PG_lookup_token_family_keys ( ",slug" ",name" ",description" - ",description_i18n" + ",description_i18n::TEXT" ",mtf.valid_after" ",mtf.valid_before" ",duration" diff --git a/src/backenddb/pg_select_account.c b/src/backenddb/pg_select_account.c @@ -65,7 +65,7 @@ TMH_PG_select_account (void *cls, " salt" ",payto_uri" ",credit_facade_url" - ",credit_facade_credentials" + ",credit_facade_credentials::TEXT" ",active" " FROM merchant_accounts" " WHERE merchant_serial=" diff --git a/src/backenddb/pg_select_account_by_uri.c b/src/backenddb/pg_select_account_by_uri.c @@ -68,7 +68,7 @@ TMH_PG_select_account_by_uri (void *cls, " salt" ",h_wire" ",credit_facade_url" - ",credit_facade_credentials" + ",credit_facade_credentials::TEXT" ",active" " FROM merchant_accounts" " WHERE merchant_serial=" diff --git a/src/backenddb/pg_select_accounts.c b/src/backenddb/pg_select_accounts.c @@ -154,7 +154,7 @@ TMH_PG_select_accounts (void *cls, ",ma.salt" ",ma.payto_uri" ",ma.credit_facade_url" - ",ma.credit_facade_credentials" + ",ma.credit_facade_credentials::TEXT" ",ma.active" ",mk.merchant_priv" ",mi.merchant_id" diff --git a/src/backenddb/pg_select_accounts_by_exchange.c b/src/backenddb/pg_select_accounts_by_exchange.c @@ -129,8 +129,8 @@ TMH_PG_select_accounts_by_exchange ( "SELECT" " payto_uri" ",conversion_url" - ",debit_restrictions" - ",credit_restrictions" + ",debit_restrictions::TEXT" + ",credit_restrictions::TEXT" ",master_sig" " FROM merchant_exchange_accounts" " WHERE master_pub=$1;"); diff --git a/src/backenddb/pg_select_all_donau_instances.c b/src/backenddb/pg_select_all_donau_instances.c @@ -153,7 +153,7 @@ TMH_PG_select_all_donau_instances (void *cls, ",di.charity_max_per_year" ",di.charity_receipts_to_date" ",di.current_year" - ",dk.keys_json" + ",dk.keys_json::TEXT" " FROM merchant_donau_instances di" " LEFT JOIN merchant_donau_keys dk" " ON di.donau_url = dk.donau_url" diff --git a/src/backenddb/pg_select_category.c b/src/backenddb/pg_select_category.c @@ -73,7 +73,7 @@ TMH_PG_select_category ( "select_category", "SELECT" " category_name" - ",category_name_i18n" + ",category_name_i18n::TEXT" ",t.product_array AS products" " FROM merchant_categories mc" " JOIN merchant_instances inst" diff --git a/src/backenddb/pg_select_category_by_name.c b/src/backenddb/pg_select_category_by_name.c @@ -53,7 +53,7 @@ TMH_PG_select_category_by_name ( "select_category_by_name", "SELECT" " category_serial" - ",category_name_i18n" + ",category_name_i18n::TEXT" " FROM merchant_categories mc" " JOIN merchant_instances mi" " USING (merchant_serial)" diff --git a/src/backenddb/pg_select_donau_instances.c b/src/backenddb/pg_select_donau_instances.c @@ -154,7 +154,7 @@ TMH_PG_select_donau_instances (void *cls, ",di.charity_max_per_year" ",di.charity_receipts_to_date" ",di.current_year" - ",dk.keys_json" + ",dk.keys_json::TEXT" " FROM merchant_donau_instances di" " LEFT JOIN merchant_donau_keys dk" " ON di.donau_url = dk.donau_url" diff --git a/src/backenddb/pg_select_exchange_keys.c b/src/backenddb/pg_select_exchange_keys.c @@ -52,7 +52,7 @@ TMH_PG_select_exchange_keys (void *cls, "select_exchange_keys", "SELECT" " first_retry" - ",keys_json" + ",keys_json::TEXT" " FROM merchant_exchange_keys" " WHERE exchange_url=$1;"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, diff --git a/src/backenddb/pg_select_wirewatch_accounts.c b/src/backenddb/pg_select_wirewatch_accounts.c @@ -129,7 +129,7 @@ TMH_PG_select_wirewatch_accounts ( ",merchant_id" ",payto_uri" ",credit_facade_url" - ",credit_facade_credentials" + ",credit_facade_credentials::TEXT" " FROM merchant_accounts" " JOIN merchant_instances" " USING (merchant_serial)" diff --git a/src/backenddb/pg_update_account.c b/src/backenddb/pg_update_account.c @@ -52,7 +52,8 @@ TMH_PG_update_account ( "update_account", "UPDATE merchant_accounts SET" " credit_facade_url=$3" - ",credit_facade_credentials=COALESCE($4,credit_facade_credentials)" + ",credit_facade_credentials=" + " COALESCE($4::TEXT::JSONB, credit_facade_credentials)" " WHERE h_wire=$2" " AND merchant_serial=" " (SELECT merchant_serial" diff --git a/src/backenddb/pg_update_category.c b/src/backenddb/pg_update_category.c @@ -47,7 +47,7 @@ TMH_PG_update_category (void *cls, "update_category", "UPDATE merchant_categories SET" " category_name=$3" - ",category_name_i18n=$4" + ",category_name_i18n=$4::TEXT::JSONB" " WHERE merchant_serial=" " (SELECT merchant_serial" " FROM merchant_instances" diff --git a/src/backenddb/pg_update_contract_terms.c b/src/backenddb/pg_update_contract_terms.c @@ -89,7 +89,7 @@ TMH_PG_update_contract_terms (void *cls, PREPARE (pg, "update_contract_terms", "UPDATE merchant_contract_terms SET" - " contract_terms=$3" + " contract_terms=$3::TEXT::JSONB" ",h_contract_terms=$4" ",pay_deadline=$5" ",refund_deadline=$6" diff --git a/src/backenddb/pg_update_instance.c b/src/backenddb/pg_update_instance.c @@ -69,8 +69,8 @@ TMH_PG_update_instance (void *cls, "update_instance", "UPDATE merchant_instances SET" " merchant_name=$2" - ",address=$3" - ",jurisdiction=$4" + ",address=$3::TEXT::JSONB" + ",jurisdiction=$4::TEXT::JSONB" ",use_stefan=$5" ",default_wire_transfer_delay=$6" ",default_pay_delay=$7" diff --git a/src/backenddb/pg_update_product.c b/src/backenddb/pg_update_product.c @@ -102,7 +102,8 @@ TMH_PG_update_product (void *cls, ",out_no_cat AS no_cat" ",out_no_instance AS no_instance" " FROM merchant_do_update_product" - "($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15);"); + "($1,$2,$3,$4::TEXT::JSONB,$5,$6,$7::TEXT::JSONB,$8,$9" + ",$10,$11::TEXT::JSONB,$12,$13,$14,$15);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "update_product", params, diff --git a/src/backenddb/pg_update_product.sql b/src/backenddb/pg_update_product.sql @@ -20,14 +20,14 @@ CREATE FUNCTION merchant_do_update_product ( IN in_instance_id TEXT, IN in_product_id TEXT, IN in_description TEXT, - IN in_description_i18n BYTEA, + IN in_description_i18n JSONB, IN in_unit TEXT, IN in_image TEXT, - IN in_taxes BYTEA, + IN in_taxes JSONB, IN in_price taler_amount_currency, IN in_total_stock INT8, IN in_total_lost INT8, - IN in_address BYTEA, + IN in_address JSONB, IN in_next_restock INT8, IN in_minimum_age INT4, IN ina_categories INT8[], diff --git a/src/backenddb/pg_update_template.c b/src/backenddb/pg_update_template.c @@ -64,8 +64,8 @@ TMH_PG_update_template (void *cls, ",otp_device_id=" " COALESCE((SELECT otp_serial" " FROM otp), NULL)" - ",template_contract=$5" - ",editable_defaults=$6" + ",template_contract=$5::TEXT::JSONB" + ",editable_defaults=$6::TEXT::JSONB" " WHERE merchant_serial=" " (SELECT merchant_serial" " FROM mid)" diff --git a/src/backenddb/pg_update_token_family.c b/src/backenddb/pg_update_token_family.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2023, 2024 Taler Systems SA + Copyright (C) 2023, 2024, 2025 Taler Systems SA TALER is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -55,8 +55,8 @@ TMH_PG_update_token_family ( "UPDATE merchant_token_families SET" " name=$3" ",description=$4" - ",description_i18n=$5" - ",extra_data=$6" + ",description_i18n=$5::TEXT::JSONB" + ",extra_data=$6::TEXT::JSONB" ",valid_after=$7" ",valid_before=$8" " WHERE merchant_serial=" diff --git a/src/backenddb/pg_upsert_donau_keys.c b/src/backenddb/pg_upsert_donau_keys.c @@ -55,11 +55,11 @@ TMH_PG_upsert_donau_keys ( "(keys_json" ",first_retry" ",donau_url" - ") VALUES ($1, $2, $3);"); + ") VALUES ($1::TEXT::JSONB, $2, $3);"); PREPARE (pg, "update_donau_keys", "UPDATE merchant_donau_keys SET" - " keys_json=$1," + " keys_json=$1::TEXT::JSONB," " first_retry=$2" " WHERE" " donau_url=$3;"); @@ -74,4 +74,4 @@ TMH_PG_upsert_donau_keys ( json_decref (jkeys); return qs; -} -\ No newline at end of file +} diff --git a/src/backenddb/procedures.sql.in b/src/backenddb/procedures.sql.in @@ -28,7 +28,8 @@ SET search_path TO merchant; #include "pg_account_kyc_set_status.sql" #include "pg_account_kyc_set_failed.sql" #include "pg_statistics_helpers.sql" - +#include "pg_do_handle_inventory_changes.sql" +#include "pg_do_handle_category_changes.sql" DROP PROCEDURE IF EXISTS merchant_do_gc; CREATE PROCEDURE merchant_do_gc(in_now INT8) diff --git a/src/backenddb/test_merchantdb.c b/src/backenddb/test_merchantdb.c @@ -1454,6 +1454,10 @@ make_order (const char *order_id, json_string ("a"))); GNUNET_assert (0 == json_object_set_new (order->contract, + "summary", + json_string ("Test order"))); + GNUNET_assert (0 == + json_object_set_new (order->contract, "order_id", json_string (order_id))); GNUNET_assert (0 ==