commit 5a75a057c2209500b72e9fd6dd6087f81af7e9f6
parent d1c267b6f4d3306f32a64dc56b8be910895d4611
Author: bohdan-potuzhnyi <bohdan.potuzhnyi@gmail.com>
Date: Thu, 21 Nov 2024 15:54:07 +0100
update of the templating
Diffstat:
2 files changed, 117 insertions(+), 59 deletions(-)
diff --git a/src/backenddb/Makefile.am b/src/backenddb/Makefile.am
@@ -29,6 +29,7 @@ sql_DATA = \
merchant-0010.sql \
merchant-0011.sql \
merchant-0012.sql \
+ merchant-0013.sql \
drop.sql
BUILT_SOURCES = \
diff --git a/src/backenddb/merchant-0013.sql b/src/backenddb/merchant-0013.sql
@@ -27,83 +27,140 @@ SELECT _v.register_patch('merchant-0013', NULL, NULL);
SET search_path TO merchant;
+-- Function to replace placeholders in a string with a given value
+CREATE OR REPLACE FUNCTION replace_placeholder(
+ template TEXT,
+ key TEXT,
+ value TEXT
+) RETURNS TEXT AS $$
+BEGIN
+ RETURN regexp_replace(
+ template,
+ '{{\s*' || key || '\s*}}', -- Match the key with optional spaces
+ value,
+ 'g' -- Global replacement
+ );
+END;
+$$ LANGUAGE plpgsql;
+
-- Trigger function to handle pending webhooks for category changes
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);
- -- Check if there are webhooks listening for this event
- PERFORM 1
- FROM merchant_webhook mw
- WHERE mw.event_type IN ('category_added', 'category_updated', 'category_deleted')
- AND mw.merchant_serial = my_merchant_serial;
+ -- INSERT case: Add a webhook for category addition
+ IF TG_OP = 'INSERT' THEN
+ FOR webhook IN
+ SELECT * FROM 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);
- IF FOUND THEN
- -- INSERT case: Add a webhook for category addition
- IF TG_OP = 'INSERT' THEN
+ -- Insert into pending webhooks for this webhook
INSERT INTO merchant_pending_webhooks
(merchant_serial, webhook_serial, url, http_method, body)
- SELECT mw.merchant_serial,
- mw.webhook_serial,
- mw.url,
- mw.http_method,
- json_build_object(
- 'webhook_type', 'category_added',
- 'category_serial', NEW.category_serial,
- 'category_name', NEW.category_name,
- 'category_name_i18n', NEW.category_name_i18n
- )::TEXT
- FROM merchant_webhook mw
- WHERE mw.event_type = 'category_added'
- AND mw.merchant_serial = my_merchant_serial;
- END IF;
-
- -- UPDATE case: Add a webhook for category update
- IF TG_OP = 'UPDATE' THEN
+ 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 * FROM 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_pending_webhooks
(merchant_serial, webhook_serial, url, http_method, body)
- SELECT mw.merchant_serial,
- mw.webhook_serial,
- mw.url,
- mw.http_method,
- json_build_object(
- 'webhook_type', 'category_updated',
- 'category_serial', NEW.category_serial,
- 'old_category_name', OLD.category_name,
- 'new_category_name', NEW.category_name,
- 'old_category_name_i18n', OLD.category_name_i18n,
- 'new_category_name_i18n', NEW.category_name_i18n
- )::TEXT
- FROM merchant_webhook mw
- WHERE mw.event_type = 'category_updated'
- AND mw.merchant_serial = my_merchant_serial;
- END IF;
-
- -- DELETE case: Add a webhook for category deletion
- IF TG_OP = 'DELETE' THEN
+ 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 * FROM 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_pending_webhooks
(merchant_serial, webhook_serial, url, http_method, body)
- SELECT mw.merchant_serial,
- mw.webhook_serial,
- mw.url,
- mw.http_method,
- json_build_object(
- 'webhook_type', 'category_deleted',
- 'category_serial', OLD.category_serial,
- 'category_name', OLD.category_name,
- 'category_name_i18n', OLD.category_name_i18n
- )::TEXT
- FROM merchant_webhook mw
- WHERE mw.event_type = 'category_deleted'
- AND mw.merchant_serial = my_merchant_serial;
- END IF;
+ 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; -- Triggers that fire AFTER must return NULL
+ RETURN NULL;
END;
$$ LANGUAGE plpgsql;
