commit a5d19156a19c3b4ebaef7f3241c0c4488aa30354
parent 25071b2fb92e4e30c3eb722733cbc3a973fc3e12
Author: Christian Grothoff <christian@grothoff.org>
Date: Tue, 2 Jun 2026 23:48:39 +0200
fixes for triggers
Diffstat:
7 files changed, 482 insertions(+), 258 deletions(-)
diff --git a/src/backenddb/example-statistics-0001.sql b/src/backenddb/example-statistics-0001.sql
@@ -33,6 +33,7 @@ SET search_path TO example_statistics;
-- Setup statistic: what do we want to track for 'deposits'?
-- (Note: this is basically the one "manual" step we might not keep hard-coded)
+-- FIXME: wrong: per-instance SCHEMA table now!
INSERT INTO merchant.merchant_statistic_bucket_meta
(slug
,description
@@ -47,6 +48,7 @@ VALUES
,ARRAY[120, 120, 48, 95, 36, 40, 100] -- track last 120 s, 120 minutes, 48 hours, 95 days, 36 months, 40 quarters & 10 years
);
+-- FIXME: wrong: per-instance SCHEMA table now!
INSERT INTO merchant.merchant_statistic_interval_meta
(slug
,description
@@ -66,6 +68,7 @@ VALUES
-- Setup statistic
-- (Note: this is basically the one "manual" step we might not keep hard-coded)
+-- FIXME: wrong: per-instance SCHEMA table now!
INSERT INTO merchant.merchant_statistic_bucket_meta
(slug
,description
@@ -80,7 +83,7 @@ VALUES
,ARRAY[120, 120, 60, 12, 24, 8, 10] -- track last 120s, 120 minutes, 60 days, 12 weeks, 24 months, 8 quarters and 10 years
);
-CREATE FUNCTION merchant_products_sold_statistics_trigger()
+CREATE OR REPLACE FUNCTION merchant_products_sold_statistics_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
diff --git a/src/backenddb/pg_do_handle_category_changes.sql b/src/backenddb/pg_do_handle_category_changes.sql
@@ -18,21 +18,22 @@
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
+ my_instance_id INT8;
BEGIN
- -- Fetch the merchant_serial directly from the NEW or OLD row
- my_merchant_serial := COALESCE(OLD.merchant_serial, NEW.merchant_serial);
+ SELECT SUBSTRING(current_schema()::TEXT
+ FROM 'merchant_instance_([0-9]+)')::INT8
+ INTO my_instance_id;
-- 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
+ SELECT
+ webhook_serial
+ ,url
+ ,http_method
+ ,body_template
FROM merchant_webhook
WHERE event_type = 'category_added'
LOOP
@@ -49,17 +50,22 @@ BEGIN
NEW.category_name);
resolved_body := merchant.replace_placeholder(resolved_body,
'merchant_serial',
- my_merchant_serial::TEXT);
+ my_instance_id::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);
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id,
+ webhook.webhook_serial,
+ webhook.url,
+ webhook.http_method,
+ resolved_body
+ );
END LOOP;
-- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING
@@ -69,8 +75,8 @@ BEGIN
-- UPDATE case: Add a webhook for category update
IF TG_OP = 'UPDATE' THEN
FOR webhook IN
- SELECT webhook_serial,
- merchant_serial,
+ SELECT
+ webhook_serial,
url,
http_method,
body_template
@@ -100,13 +106,18 @@ BEGIN
-- 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);
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id
+ ,webhook.webhook_serial
+ ,webhook.url
+ ,webhook.http_method
+ ,resolved_body
+ );
END LOOP;
-- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING
@@ -116,11 +127,11 @@ BEGIN
-- 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
+ SELECT
+ webhook_serial
+ ,url
+ ,http_method
+ ,body_template
FROM merchant_webhook
WHERE event_type = 'category_deleted'
LOOP
@@ -138,13 +149,18 @@ BEGIN
-- 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);
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id
+ ,webhook.webhook_serial
+ ,webhook.url
+ ,webhook.http_method
+ ,resolved_body
+ );
END LOOP;
-- Notify the webhook service for the TALER_DBEVENT_MERCHANT_WEBHOOK_PENDING
diff --git a/src/backenddb/pg_do_handle_inventory_changes.sql b/src/backenddb/pg_do_handle_inventory_changes.sql
@@ -20,16 +20,20 @@ CREATE OR REPLACE FUNCTION handle_inventory_changes()
DECLARE
resolved_body TEXT;
webhook RECORD; -- To iterate over all matching webhooks
+ my_instance_id INT8;
BEGIN
+ SELECT SUBSTRING(current_schema()::TEXT
+ FROM 'merchant_instance_([0-9]+)')::INT8
+ INTO my_instance_id;
-- 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
+ SELECT
+ webhook_serial
+ ,url
+ ,http_method
+ ,body_template
FROM merchant_webhook
WHERE event_type = 'inventory_added'
LOOP
@@ -86,13 +90,18 @@ BEGIN
-- 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);
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id
+ ,webhook.webhook_serial
+ ,webhook.url
+ ,webhook.http_method
+ ,resolved_body
+ );
END LOOP;
-- Notify the webhook service
@@ -102,11 +111,11 @@ BEGIN
-- 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
+ SELECT
+ webhook_serial
+ ,url
+ ,http_method
+ ,body_template
FROM merchant_webhook
WHERE event_type = 'inventory_updated'
LOOP
@@ -202,13 +211,18 @@ BEGIN
-- 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);
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id
+ ,webhook.webhook_serial
+ ,webhook.url
+ ,webhook.http_method
+ ,resolved_body
+ );
END LOOP;
-- Notify the webhook service
@@ -218,11 +232,11 @@ BEGIN
-- 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
+ SELECT
+ webhook_serial
+ ,url
+ ,http_method
+ ,body_template
FROM merchant_webhook
WHERE event_type = 'inventory_deleted'
LOOP
@@ -279,13 +293,18 @@ BEGIN
-- 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);
+ (merchant_serial
+ ,webhook_serial
+ ,url
+ ,http_method
+ ,body
+ ) VALUES (
+ my_instance_id
+ ,webhook.webhook_serial
+ ,webhook.url
+ ,webhook.http_method
+ ,resolved_body
+ );
END LOOP;
-- Notify the webhook service
diff --git a/src/backenddb/pg_statistics_examples.sql b/src/backenddb/pg_statistics_examples.sql
@@ -1,182 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 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/>
---
-
--- @file pg_statistics_example.sql
--- @brief example/test case for how to add statistics to the merchant backend, NOT for production!
--- @author Christian Grothoff
-
--- Everything in one big transaction
-BEGIN;
-
--- Setup statistic: what do we want to track for 'deposits'?
--- (Note: this is basically the one "manual" step we might not keep hard-coded)
-INSERT INTO merchant_statistic_bucket_meta
- (slug
- ,description
- ,stype
- ,ranges
- ,ages)
-VALUES
- ('deposits'
- ,'sales (before refunds)'
- ,'amount'
- ,ARRAY['second'::merchant.statistic_range, 'minute', 'day', 'month', 'quarter', 'year']
- ,ARRAY[120, 120, 95, 36, 40, 100] -- track last 120 s, 120 minutes, 95 days, 36 months, 40 quarters & 100 years
- )
-ON CONFLICT DO NOTHING;
-
-INSERT INTO merchant_statistic_interval_meta
- (slug
- ,description
- ,stype
- ,ranges
- ,precisions)
-VALUES
- ('deposits'
- ,'sales (before refunds)'
- ,'amount'
- ,ARRAY(SELECT generate_series (1, 10, 1)) || ARRAY(SELECT generate_series (60, 180, 60))
- ,array_fill (1, ARRAY[10]) || array_fill (5, ARRAY[3])
- ),
- ('products-sold'
- ,'number of products sold'
- ,'number'
- ,ARRAY(SELECT generate_series (1, 10, 1)) || ARRAY[60]
- ,array_fill (1, ARRAY[10]) || ARRAY[10]
- )
-ON CONFLICT DO NOTHING;
-
-DROP FUNCTION IF EXISTS merchant_deposits_statistics_trigger CASCADE;
-CREATE FUNCTION merchant_deposits_statistics_trigger()
-RETURNS trigger
-LANGUAGE plpgsql
-AS $$
-BEGIN
--- SET search_path TO merchant;
- CALL merchant_do_bump_amount_stat
- ('deposits'
- ,CURRENT_TIMESTAMP(0)
- ,NEW.amount_with_fee);
- RETURN NEW;
-END $$;
-COMMENT ON FUNCTION merchant_deposits_statistics_trigger
- IS 'adds the deposited amount to the deposit statistics';
-
-DO $$
-DECLARE
- rec RECORD;
-BEGIN
-FOR rec IN
- SELECT 'deposits' AS in_slug
- ,TO_TIMESTAMP (mdc.deposit_timestamp / 1000.0 / 1000.0)::TIMESTAMP AS in_timestamp
- ,mdc.total_without_fee AS in_delta
- FROM merchant_deposit_confirmations mdc
- JOIN merchant_contract_terms mct
- USING (order_serial)
- WHERE mdc.deposit_timestamp > (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) - 365*24*60*60) * 1000000
-LOOP
- CALL merchant_do_bump_amount_stat (rec.in_slug, rec.in_timestamp, rec.in_delta);
-END LOOP;
-END $$;
-
--- Whenever a deposit is made, call our trigger to bump statistics
-CREATE TRIGGER merchant_deposits_on_insert
- AFTER INSERT
- ON merchant_deposits
- FOR EACH ROW
- EXECUTE FUNCTION merchant_deposits_statistics_trigger();
-
-
-
-
--- Setup statistic
--- (Note: this is basically the one "manual" step we might not keep hard-coded)
-INSERT INTO merchant_statistic_bucket_meta
- (slug
- ,description
- ,stype
- ,ranges
- ,ages)
-VALUES
- ('products-sold'
- ,'products sold (only those tracked in inventory)'
- ,'number'
- ,ARRAY['second'::merchant.statistic_range, 'minute', 'day', 'week', 'month', 'quarter', 'year']
- ,ARRAY[120, 120, 60, 12, 24, 8, 10] -- track last 120s, 120 minutes, 60 days, 12 weeks, 24 months, 8 quarters and 10 years
- )
-ON CONFLICT DO NOTHING;
-
-DROP FUNCTION IF EXISTS merchant_products_sold_statistics_trigger CASCADE;
-CREATE FUNCTION merchant_products_sold_statistics_trigger()
-RETURNS trigger
-LANGUAGE plpgsql
-AS $$
-DECLARE
- my_sold INT8;
-BEGIN
--- SET search_path TO merchant;
- my_sold = NEW.total_sold - OLD.total_sold;
- IF (0 < my_sold)
- THEN
- CALL merchant_do_bump_number_stat
- ('products-sold'
- ,CURRENT_TIMESTAMP(0)
- ,my_sold);
- END IF;
- RETURN NEW;
-END $$;
-
--- Whenever inventory changes, call our trigger to bump statistics
-CREATE TRIGGER merchant_products_on_sold
- AFTER UPDATE
- ON merchant_inventory
- FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger();
-
-DELETE FROM merchant.merchant_statistic_bucket_counter ;
-DELETE FROM merchant.merchant_statistic_bucket_amount ;
-DELETE FROM merchant.merchant_statistic_interval_counter;
-DELETE FROM merchant.merchant_statistic_interval_amount;
-DELETE FROM merchant.merchant_statistic_amount_event;
-DELETE FROM merchant.merchant_statistic_counter_event;
-
-
-call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 minutes', 1);
-call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 minutes', (1,1,'EUR')::taler_amount_currency);
-call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 seconds', 2);
-call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '1 minute', (2,2,'EUR')::taler_amount_currency);
-call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 seconds', (4,4,'EUR')::taler_amount_currency);
-call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '1 second', (8,8,'EUR')::taler_amount_currency);
-call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, 4);
-call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, (16,16,'EUR')::taler_amount_currency);
-
-SELECT * FROM merchant_statistic_interval_number_get ('products-sold', 'default');
-
-SELECT * FROM merchant_statistic_interval_amount_get ('deposits', 'default');
-
-SELECT * FROM merchant.merchant_statistic_amount_event;
-
-SELECT * FROM merchant.merchant_statistic_counter_event;
-
-SELECT * FROM merchant.merchant_statistic_interval_counter;
-
-SELECT * FROM merchant.merchant_statistic_interval_amount;
-
-SELECT * FROM merchant.merchant_statistic_bucket_counter ;
-
-SELECT * FROM merchant.merchant_statistic_bucket_amount ;
-
--- ROLLBACK;
-COMMIT;
diff --git a/src/backenddb/pg_triggers.sql b/src/backenddb/pg_triggers.sql
@@ -0,0 +1,244 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2026 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/>
+--
+
+CREATE OR REPLACE FUNCTION merchant_orders_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_stat
+ ('orders-created'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_contract_terms_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_stat
+ ('orders-claimed'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_contract_terms_update_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_rec RECORD;
+BEGIN
+ IF (NEW.wired AND NOT OLD.wired)
+ THEN
+ CALL merchant_do_bump_number_stat
+ ('orders-settled'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ END IF;
+ IF (NEW.paid AND NOT OLD.paid)
+ THEN
+ CALL merchant_do_bump_number_stat
+ ('orders-paid'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ FOR my_rec IN
+ SELECT total_without_fee
+ FROM merchant_deposit_confirmations
+ WHERE order_serial = NEW.order_serial
+ LOOP
+ CALL merchant_do_bump_amount_stat
+ ('payments-received-after-deposit-fee'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,my_rec.total_without_fee);
+ END LOOP;
+ FOR my_rec IN
+ SELECT deposit_fee
+ FROM merchant_deposits
+ WHERE deposit_confirmation_serial IN
+ (SELECT deposit_confirmation_serial
+ FROM merchant_deposit_confirmations
+ WHERE order_serial = NEW.order_serial)
+ LOOP
+ CALL merchant_do_bump_amount_stat
+ ('total-deposit-fees-paid'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,my_rec.deposit_fee);
+ END LOOP;
+ END IF;
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_refunds_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_amount_stat
+ ('refunds-granted'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.refund_amount);
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_transfer_signatures_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_amount_stat
+ ('wire-fees-paid'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.wire_fee);
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_issued_tokens_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_stat
+ ('tokens-issued'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_used_tokens_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_stat
+ ('tokens-used'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+
+-- Create TRIGGERs to set confirmed/expected status on INSERT.
+CREATE OR REPLACE FUNCTION merchant_expected_transfers_insert_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ UPDATE merchant_transfers
+ SET expected = TRUE
+ WHERE wtid = NEW.wtid
+ AND exchange_url = NEW.exchange_url
+ AND credit_amount = NEW.expected_credit_amount;
+ NEW.confirmed = FOUND;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION merchant_expected_transfers_insert_trigger
+ IS 'Sets "confirmed" to TRUE for the new record if the expected transfer was already confirmed, and updates the already confirmed transfer to "expected"';
+
+CREATE OR REPLACE FUNCTION merchant_transfers_insert_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ UPDATE merchant_expected_transfers
+ SET confirmed = TRUE
+ WHERE wtid = NEW.wtid
+ AND exchange_url = NEW.exchange_url
+ AND expected_credit_amount = NEW.credit_amount;
+ NEW.expected = FOUND;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION merchant_transfers_insert_trigger
+ IS 'Sets "expected" to TRUE for the new record if the transfer was already expected, and updates the already confirmed transfer to "confirmed"';
+
+
+CREATE OR REPLACE FUNCTION merchant_deposits_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_amount_stat
+ ('deposits-received'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.amount_with_fee);
+ CALL merchant_do_bump_amount_stat
+ ('deposits-fees-paid'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.deposit_fee);
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_expected_transfers_insert_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF NEW.wire_fee IS NOT NULL
+ THEN
+ CALL merchant_do_bump_amount_stat
+ ('wire-fees-paid'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.wire_fee);
+ END IF;
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_expected_transfers_update_statistics_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF NEW.wire_fee IS NOT NULL AND OLD.wire_fee IS NULL
+ THEN
+ CALL merchant_do_bump_amount_stat
+ ('wire-fees-paid'
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.wire_fee);
+ END IF;
+ RETURN NEW;
+END $$;
+
+
+CREATE OR REPLACE FUNCTION merchant_kyc_insert_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_send_kyc_notification(NEW.account_serial,
+ NEW.exchange_url);
+ RETURN NEW;
+END $$;
+
+CREATE OR REPLACE FUNCTION merchant_kyc_update_trigger()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_send_kyc_notification(NEW.account_serial,
+ NEW.exchange_url);
+ RETURN NEW;
+END $$;
diff --git a/src/backenddb/sql-schema/merchant-0037.sql b/src/backenddb/sql-schema/merchant-0037.sql
@@ -27,7 +27,7 @@ SET search_path TO merchant;
CREATE PROCEDURE merchant.merchant_0037_init(s TEXT)
LANGUAGE plpgsql
- AS $$
+ AS $OUTER$
BEGIN
EXECUTE format('CREATE TABLE %I.merchant_tos_accepted ('
|| ' tos_accepted_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,'
@@ -41,8 +41,131 @@ BEGIN
s, 'Base URL of the exchange the terms of service were accepted for.');
EXECUTE format('COMMENT ON COLUMN %I.merchant_tos_accepted.tos_version IS %L',
s, 'Accepted Taler-Terms-Version of the exchange terms of service.');
+
+ -- merchant_categories
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.handle_category_changes()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER trigger_category_changes'
+ || ' AFTER INSERT OR UPDATE OR DELETE'
+ || ' ON %I.merchant_categories'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.handle_category_changes()', s, s);
+
+ -- recreate triggers lost in merchant-0036...
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.handle_inventory_changes()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER trigger_inventory_changes'
+ || ' AFTER INSERT OR UPDATE OR DELETE'
+ || ' ON %I.merchant_inventory'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.handle_inventory_changes()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_orders_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_orders_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_orders'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_orders_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_contract_terms_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_contract_terms_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_contract_terms'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_contract_terms_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_contract_terms_update_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_contract_terms_on_update_statistic'
+ || ' AFTER UPDATE'
+ || ' ON %I.merchant_contract_terms'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_contract_terms_update_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_refunds_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_refunds_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_refunds'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_refunds_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_transfer_signatures_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_transfer_signatures_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_transfer_signatures'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_transfer_signatures_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_issued_tokens_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_issued_tokens_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_issued_tokens'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_issued_tokens_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_used_tokens_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_used_tokens_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_used_tokens'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_used_tokens_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_deposits_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_deposits_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_deposits'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_deposits_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_expected_transfers_insert_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_expected_transfers_on_insert_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_expected_transfers'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_expected_transfers_insert_statistics_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_expected_transfers_update_statistics_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_expected_transfers_on_update_statistic'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_expected_transfers'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_expected_transfers_update_statistics_trigger()', s, s);
+
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_expected_transfers_insert_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_expected_transfers_on_insert'
+ || ' BEFORE INSERT'
+ || ' ON %I.merchant_expected_transfers'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_expected_transfers_insert_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_expected_transfers_update_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_expected_transfers_on_update'
+ || ' BEFORE UPDATE'
+ || ' ON %I.merchant_expected_transfers'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_expected_transfers_update_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_transfers_insert_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_transfers_on_insert'
+ || ' BEFORE INSERT'
+ || ' ON %I.merchant_transfers'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_transfers_insert_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_kyc_insert_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_kyc_on_insert'
+ || ' AFTER INSERT'
+ || ' ON %I.merchant_kyc'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_kyc_insert_trigger()', s, s);
+
+ EXECUTE format('CREATE OR REPLACE FUNCTION %I.merchant_kyc_update_trigger()'
+ || ' RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$;', s);
+ EXECUTE format('CREATE TRIGGER merchant_kyc_on_update'
+ || ' AFTER UPDATE'
+ || ' ON %I.merchant_kyc'
+ || ' FOR EACH ROW EXECUTE FUNCTION %I.merchant_kyc_update_trigger()', s, s);
+
END
-$$;
+$OUTER$;
INSERT INTO merchant.instance_fixups
(migration_name
diff --git a/src/backenddb/sql-schema/meson.build b/src/backenddb/sql-schema/meson.build
@@ -60,6 +60,7 @@ sql_instance_procedures = [
'../expire_locks.sql',
'../pg_merchant_send_kyc_notification.sql',
'../pg_merchant_kyc_trigger.sql',
+ '../pg_triggers.sql',
]
iprocedures_sql = custom_target('instance_procedures',
