commit 9dd99be8e97e12021326983dc4127dc8f7398e85
parent 7f55a69d3c4cf54d8f6df16081b966f6a8b5bf7c
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Fri, 21 Mar 2025 15:58:24 +0100
first, very preliminary code for tracking statistics in the merchant backend --- incomplete, but compiles and doesn't seem to break anything
Diffstat:
7 files changed, 1202 insertions(+), 6 deletions(-)
diff --git a/src/backenddb/Makefile.am b/src/backenddb/Makefile.am
@@ -30,6 +30,7 @@ sql_DATA = \
merchant-0011.sql \
merchant-0012.sql \
merchant-0013.sql \
+ merchant-0014.sql \
drop.sql
BUILT_SOURCES = \
diff --git a/src/backenddb/example-statistics-0001.sql b/src/backenddb/example-statistics-0001.sql
@@ -0,0 +1,140 @@
+--
+-- 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 example-statistics-0001.sql
+-- @brief examples for how to add statistics to the merchant backend
+-- @author Christian Grothoff
+
+-- Everything in one big transaction
+BEGIN;
+
+-- Check patch versioning is in place.
+SELECT _v.register_patch('example-statistics-0001', NULL, NULL);
+
+CREATE SCHEMA example_statistics;
+
+SET search_path TO example_statistics;
+
+
+-- This is for now just an example for how to use the API. --- BEGIN EXAMPLE
+
+-- 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.merchant_statistic_bucket_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,ages)
+VALUES
+ ('deposits'
+ ,'sales (before refunds)'
+ ,'amount'
+ ,ARRAY['second'::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
+ );
+
+INSERT INTO merchant.merchant_statistic_interval_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,precisions)
+VALUES
+ ('deposits'
+ ,'sales (before refunds)'
+ ,'amount'
+ ,ARRAY[1,60, 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60] -- second, minute, day, month, year
+ ,ARRAY[1,1, 60, 60 * 60, 24 * 60 * 60] -- second, second, minute, hour, day
+ );
+
+CREATE FUNCTION merchant_deposits_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_instance INT8;
+BEGIN
+ SELECT mct.merchant_serial
+ INTO my_instance
+ FROM merchant_contract_terms mct
+ JOIN merchant_deposit_confirmations mdc
+ USING (order_serial)
+ WHERE mdc.deposit_confirmation_serial = NEW.deposit_confirmation_serial;
+ CALL merchant_do_bump_amount_stat
+ ('deposits'
+ ,my_instance
+ ,NEW.amount_with_fee);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION merchant_deposits_statistics_trigger
+ IS 'adds the deposited amount to the deposit statistics';
+
+-- Whenever a deposit is made, call our trigger to bump statistics
+CREATE TRIGGER merchant_deposits_on_insert
+ AFTER INSERT
+ ON merchant.merchant_deposits
+ FOR EACH ROW EXECUTE FUNCTION merchant_deposits_statistics_trigger();
+
+-- This is for now just an example for how to use the API. --- END EXAMPLE
+
+
+
+-- This is just another example for how to use the API. -- BEGIN EXAMPLE
+
+-- Setup statistic
+-- (Note: this is basically the one "manual" step we might not keep hard-coded)
+INSERT INTO merchant.merchant_statistic_bucket_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,ages)
+VALUES
+ ('products-sold'
+ ,'products sold (only those tracked in inventory)'
+ ,'number'
+ ,ARRAY['second'::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
+ );
+
+CREATE FUNCTION merchant_products_sold_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_sold INT8;
+BEGIN
+ my_sold = NEW.total_sold - OLD.total_sold;
+ IF (0 < my_sold)
+ THEN
+ CALL merchant_do_bump_number_stat
+ ('products-sold'
+ ,NEW.merchant_serial
+ ,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.merchant_inventory
+ FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger();
+
+-- This is for now just an example for how to use the API. --- END EXAMPLE
+
+COMMIT;
diff --git a/src/backenddb/merchant-0013.sql b/src/backenddb/merchant-0013.sql
@@ -69,7 +69,7 @@ COMMENT ON COLUMN merchant_token_family_keys.private_key_created_at
IS 'Specifies when the private key was created. Not terribly useful, mostly for debugging.';
-- Function to replace placeholders in a string with a given value
-CREATE OR REPLACE FUNCTION replace_placeholder(
+CREATE FUNCTION replace_placeholder(
template TEXT,
key TEXT,
value TEXT
@@ -84,8 +84,7 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
--- Trigger function to handle pending webhooks for category changes
-CREATE OR REPLACE FUNCTION handle_category_changes()
+CREATE FUNCTION handle_category_changes()
RETURNS TRIGGER AS $$
DECLARE
my_merchant_serial BIGINT;
@@ -230,6 +229,10 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
+COMMENT ON FUNCTION handle_category_changes
+ IS 'Trigger function to handle pending webhooks for category changes';
+
+
-- Trigger to invoke the trigger function
CREATE TRIGGER trigger_category_changes
AFTER INSERT OR UPDATE OR DELETE
@@ -237,8 +240,7 @@ ON merchant_categories
FOR EACH ROW
EXECUTE FUNCTION handle_category_changes();
--- Function to handle inventory changes and notify webhooks
-CREATE OR REPLACE FUNCTION handle_inventory_changes()
+CREATE FUNCTION handle_inventory_changes()
RETURNS TRIGGER AS $$
DECLARE
my_merchant_serial BIGINT;
@@ -525,6 +527,10 @@ BEGIN
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-0014.sql b/src/backenddb/merchant-0014.sql
@@ -0,0 +1,260 @@
+--
+-- 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 merchant-0014.sql
+-- @brief Tables for statistics
+-- @author Christian Grothoff
+
+
+BEGIN;
+
+-- Check patch versioning is in place.
+SELECT _v.register_patch('merchant-0014', NULL, NULL);
+
+SET search_path TO merchant;
+
+-- Ranges given here must be supported by the date_trunc function of Postgresql!
+CREATE TYPE statistic_range AS
+ ENUM('century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second');
+
+CREATE TYPE statistic_type AS
+ ENUM('amount', 'number');
+
+-- -------------- Bucket statistics ---------------------
+
+CREATE TABLE merchant_statistic_bucket_meta
+ (bmeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,slug TEXT NOT NULL
+ ,description TEXT NOT NULL
+ ,stype statistic_type NOT NULL
+ ,ranges statistic_range[] NOT NULL
+ ,ages INT4[] NOT NULL
+ ,UNIQUE(slug,stype)
+ ,CONSTRAINT equal_array_length
+ CHECK (array_length(ranges,1) =
+ array_length(ages,1))
+ );
+COMMENT ON TABLE merchant_statistic_bucket_meta
+ IS 'meta data about a statistic with events falling into buckets we are tracking';
+COMMENT ON COLUMN merchant_statistic_bucket_meta.bmeta_serial_id
+ IS 'unique identifier for this type of bucket statistic we are tracking';
+COMMENT ON COLUMN merchant_statistic_bucket_meta.slug
+ IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path';
+COMMENT ON COLUMN merchant_statistic_bucket_meta.description
+ IS 'description of the statistic being tracked';
+COMMENT ON COLUMN merchant_statistic_bucket_meta.stype
+ IS 'statistic type, what kind of data is being tracked, amount or number';
+COMMENT ON COLUMN merchant_statistic_bucket_meta.ranges
+ IS 'size of the buckets that are being kept for this statistic';
+COMMENT ON COLUMN merchant_statistic_bucket_meta.ages
+ IS 'determines how long into the past we keep buckets for the range at the given index around (in generations)';
+
+
+CREATE TABLE merchant_statistic_bucket_counter
+ (bmeta_serial_id INT8 NOT NULL
+ REFERENCES merchant_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE
+ ,merchant_serial BIGINT NOT NULL
+ REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+ ,bucket_start INT8 NOT NULL
+ ,bucket_range statistic_range NOT NULL
+ ,cumulative_number INT8 NOT NULL
+ ,UNIQUE (bmeta_serial_id,merchant_serial,bucket_start,bucket_range)
+ );
+COMMENT ON TABLE merchant_statistic_bucket_counter
+ IS 'various numeric statistics (cumulative counters) being tracked by bucket into which they fall';
+COMMENT ON COLUMN merchant_statistic_bucket_counter.bmeta_serial_id
+ IS 'identifies what the statistic is about';
+COMMENT ON COLUMN merchant_statistic_bucket_counter.merchant_serial
+ IS 'identifies the instance for which the statistic is kept';
+COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_start
+ IS 'start date for the bucket';
+COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_range
+ IS 'range of the bucket';
+COMMENT ON COLUMN merchant_statistic_bucket_counter.cumulative_number
+ IS 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword';
+
+
+CREATE TABLE merchant_statistic_bucket_amount
+ (bmeta_serial_id INT8 NOT NULL
+ REFERENCES merchant_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE
+ ,merchant_serial BIGINT NOT NULL
+ REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+ ,bucket_start INT8 NOT NULL
+ ,bucket_range statistic_range NOT NULL
+ ,curr VARCHAR(12) NOT NULL
+ ,cumulative_value INT8 NOT NULL
+ ,cumulative_frac INT4 NOT NULL
+ ,UNIQUE (bmeta_serial_id,merchant_serial,curr,bucket_start,bucket_range)
+ );
+COMMENT ON TABLE merchant_statistic_bucket_amount
+ IS 'various amount statistics (in various currencies) being tracked';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.bmeta_serial_id
+ IS 'identifies what the statistic is about';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.merchant_serial
+ IS 'identifies the instance for which the statistic is kept';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_start
+ IS 'start date for the bucket';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_range
+ IS 'range of the bucket';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.curr
+ IS 'currency which this statistic is tracking the amount for';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.cumulative_value
+ IS 'amount in the respective currency, non-fractional amount value';
+COMMENT ON COLUMN merchant_statistic_bucket_amount.cumulative_frac
+ IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value';
+
+
+-- -------------- Interval statistics ---------------------
+
+
+CREATE TABLE merchant_statistic_interval_meta
+ (imeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,slug TEXT NOT NULL
+ ,description TEXT NOT NULL
+ ,stype statistic_type NOT NULL
+ ,ranges INT8[] NOT NULL CHECK (array_length(ranges,1) > 0)
+ ,precisions INT8[] NOT NULL CHECK (array_length(precisions,1) > 0)
+ ,UNIQUE(slug,stype)
+ ,CONSTRAINT equal_array_length
+ CHECK (array_length(ranges,1) =
+ array_length(precisions,1))
+ );
+COMMENT ON TABLE merchant_statistic_interval_meta
+ IS 'meta data about an interval statistic we are tracking';
+COMMENT ON COLUMN merchant_statistic_interval_meta.imeta_serial_id
+ IS 'unique identifier for this type of interval statistic we are tracking';
+COMMENT ON COLUMN merchant_statistic_interval_meta.slug
+ IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path';
+COMMENT ON COLUMN merchant_statistic_interval_meta.description
+ IS 'description of the statistic being tracked';
+COMMENT ON COLUMN merchant_statistic_interval_meta.stype
+ IS 'statistic type, what kind of data is being tracked, amount or number';
+COMMENT ON COLUMN merchant_statistic_interval_meta.ranges
+ IS 'range of values that is being kept for this statistic, in seconds, must be monotonically increasing';
+COMMENT ON COLUMN merchant_statistic_interval_meta.precisions
+ IS 'determines how precisely we track which events fall into the range at the same index (allowing us to coalesce events with timestamps in proximity close to the given precision), in seconds, 0 is not allowed';
+
+CREATE TABLE merchant_statistic_counter_event
+ (nevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,imeta_serial_id INT8
+ REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE
+ ,merchant_serial BIGINT NOT NULL
+ REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+ ,slot INT8 NOT NULL
+ ,delta INT8 NOT NULL
+ ,UNIQUE (imeta_serial_id, slot)
+ );
+COMMENT ON TABLE merchant_statistic_counter_event
+ IS 'number to decrement an interval statistic by when a certain time value is reached';
+COMMENT ON COLUMN merchant_statistic_counter_event.nevent_serial_id
+ IS 'unique identifier for this number event';
+COMMENT ON COLUMN merchant_statistic_counter_event.imeta_serial_id
+ IS 'identifies what the statistic is about; must be of stype number';
+COMMENT ON COLUMN merchant_statistic_counter_event.merchant_serial
+ IS 'identifies which merchant instance the event is about';
+COMMENT ON COLUMN merchant_statistic_counter_event.slot
+ IS 'identifies the time slot at which the given event(s) happened, rounded down by the respective precisions value';
+COMMENT ON COLUMN merchant_statistic_counter_event.delta
+ IS 'total cumulative number that was added at the time identified by slot';
+
+CREATE TABLE merchant_statistic_interval_counter
+ (imeta_serial_id INT8 NOT NULL
+ REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE
+ ,merchant_serial BIGINT NOT NULL
+ REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+ ,range INT8 NOT NULL
+ ,event_delimiter INT8 NOT NULL
+ REFERENCES merchant_statistic_counter_event (nevent_serial_id) ON DELETE RESTRICT
+ ,cumulative_number INT8 NOT NULL
+ ,UNIQUE (imeta_serial_id,merchant_serial,range)
+ );
+COMMENT ON TABLE merchant_statistic_interval_counter
+ IS 'various numeric statistics (cumulative counters) being tracked';
+COMMENT ON COLUMN merchant_statistic_interval_counter.imeta_serial_id
+ IS 'identifies what the statistic is about';
+COMMENT ON COLUMN merchant_statistic_interval_counter.merchant_serial
+ IS 'identifies the instance for which the statistic is kept';
+COMMENT ON COLUMN merchant_statistic_interval_counter.range
+ IS 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges';
+COMMENT ON COLUMN merchant_statistic_interval_counter.event_delimiter
+ IS 'determines the last event currently included in the interval';
+COMMENT ON COLUMN merchant_statistic_interval_counter.cumulative_number
+ IS 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword';
+
+
+CREATE TABLE merchant_statistic_amount_event
+ (aevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,imeta_serial_id INT8
+ REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE
+ ,merchant_serial BIGINT NOT NULL
+ REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+ ,slot INT8 NOT NULL
+ ,delta_curr VARCHAR(12) NOT NULL
+ ,delta_value INT8 NOT NULL
+ ,delta_frac INT4 NOT NULL
+ ,CONSTRAINT event_key UNIQUE (imeta_serial_id, merchant_serial, slot, delta_curr)
+ );
+COMMENT ON TABLE merchant_statistic_amount_event
+ IS 'amount to decrement an interval statistic by when a certain time value is reached';
+COMMENT ON COLUMN merchant_statistic_amount_event.aevent_serial_id
+ IS 'unique identifier for this amount event';
+COMMENT ON COLUMN merchant_statistic_amount_event.imeta_serial_id
+ IS 'identifies what the statistic is about; must be of clazz interval and of stype amount';
+COMMENT ON COLUMN merchant_statistic_amount_event.merchant_serial
+ IS 'identifies which merchant instance the event is about';
+COMMENT ON COLUMN merchant_statistic_amount_event.slot
+ IS 'identifies the time slot at which the given event(s) happened';
+COMMENT ON COLUMN merchant_statistic_amount_event.delta_curr
+ IS 'currency of the total cumulative amount that was added at the time identified by slot';
+COMMENT ON COLUMN merchant_statistic_amount_event.delta_value
+ IS 'total cumulative amount (value) that was added at the time identified by slot';
+COMMENT ON COLUMN merchant_statistic_amount_event.delta_frac
+ IS 'total cumulative amount (fraction) that was added at the time identified by slot';
+
+CREATE TABLE merchant_statistic_interval_amount
+ (imeta_serial_id INT8 NOT NULL
+ REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE
+ ,merchant_serial BIGINT NOT NULL
+ REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+ ,aevent_serial_id INT8 NOT NULL
+ REFERENCES merchant_statistic_amount_event (aevent_serial_id) ON DELETE RESTRICT
+ ,range INT8 NOT NULL
+ ,curr VARCHAR(12) NOT NULL
+ ,cumulative_value INT8 NOT NULL
+ ,cumulative_frac INT4 NOT NULL
+ ,UNIQUE (imeta_serial_id,merchant_serial,curr)
+ );
+COMMENT ON TABLE merchant_statistic_interval_amount
+ IS 'various amount statistics (in various currencies) being tracked';
+COMMENT ON COLUMN merchant_statistic_interval_amount.imeta_serial_id
+ IS 'identifies what the statistic is about';
+COMMENT ON COLUMN merchant_statistic_interval_amount.merchant_serial
+ IS 'identifies the instance for which the statistic is kept';
+COMMENT ON COLUMN merchant_statistic_interval_amount.range
+ IS 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges';
+COMMENT ON COLUMN merchant_statistic_interval_amount.curr
+ IS 'currency which this statistic is tracking the amount for';
+COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_value
+ IS 'amount in the respective currency, non-fractional amount value';
+COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_frac
+ IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value';
+
+
+-- FIXME: logic for statistics garbage collection (delete ancient buckets based on 'ages') is missing! (but not urgent at all).
+-- Note: calling merchant_deposit_statistic_get() on each statistic to delete events should be part of the GC
+-- (as otherwise events are kept around if the respective statistic is never requested).
+
+COMMIT;
diff --git a/src/backenddb/pg_statistics_examples.sql b/src/backenddb/pg_statistics_examples.sql
@@ -0,0 +1,150 @@
+--
+-- 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 example-statistics-0001.sql
+-- @brief examples for how to add statistics to the merchant backend
+-- @author Christian Grothoff
+
+-- Everything in one big transaction
+BEGIN;
+
+-- Check patch versioning is in place.
+SELECT _v.register_patch('example-statistics-0001', NULL, NULL);
+
+CREATE SCHEMA example_statistics;
+
+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)
+INSERT INTO merchant.merchant_statistic_bucket_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,ages)
+VALUES
+ ('deposits'
+ ,'sales (before refunds)'
+ ,'amount'
+ ,ARRAY['second'::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
+ );
+
+INSERT INTO merchant.merchant_statistic_interval_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,precisions)
+VALUES
+ ('deposits'
+ ,'sales (before refunds)'
+ ,'amount'
+ ,ARRAY[1,60, 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60] -- second, minute, day, month, year
+ ,ARRAY[1,1, 60, 60 * 60, 24 * 60 * 60] -- second, second, minute, hour, day
+ );
+
+CREATE FUNCTION merchant_deposits_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_instance INT8;
+BEGIN
+ SELECT mct.merchant_serial
+ INTO my_instance
+ FROM merchant_contract_terms mct
+ JOIN merchant_deposit_confirmations mdc
+ USING (order_serial)
+ WHERE mdc.deposit_confirmation_serial = NEW.deposit_confirmation_serial;
+ CALL merchant_do_bump_amount_stat
+ ('deposits'
+ ,my_instance
+ ,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';
+
+-- Import all existing deposits from the last year into the table.
+PERFORM FROM merchant_do_bump_amount_stat
+ (in_slug
+ ,in_merchant_serial
+ ,in_timestamp
+ ,in_delta)
+ SELECT 'deposits'
+ ,mct.merchant_serial
+ ,TO_TIMESTAMP (mdc.deposit_timestamp / 1000.0 / 1000.0)
+ ,mdc.amount_with_fee
+ FROM merchant_deposit_confirmations
+ JOIN merchant_contract_terms
+ USING (order_serial)
+ WHERE mdc.deposit_timestamp > (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) - 365*24*60*60) * 1000000;
+
+-- Whenever a deposit is made, call our trigger to bump statistics
+CREATE TRIGGER merchant_deposits_on_insert
+ AFTER INSERT
+ ON merchant.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.merchant_statistic_bucket_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,ages)
+VALUES
+ ('products-sold'
+ ,'products sold (only those tracked in inventory)'
+ ,'number'
+ ,ARRAY['second'::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
+ );
+
+CREATE FUNCTION merchant_products_sold_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_sold INT8;
+BEGIN
+ my_sold = NEW.total_sold - OLD.total_sold;
+ IF (0 < my_sold)
+ THEN
+ CALL merchant_do_bump_number_stat
+ ('products-sold'
+ ,NEW.merchant_serial
+ ,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.merchant_inventory
+ FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger();
+
+
+COMMIT;
diff --git a/src/backenddb/pg_statistics_helpers.sql b/src/backenddb/pg_statistics_helpers.sql
@@ -0,0 +1,638 @@
+--
+-- 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/>
+--
+
+
+DROP FUNCTION IF EXISTS interval_to_start;
+CREATE OR REPLACE FUNCTION interval_to_start (
+ IN in_timestamp TIMESTAMP,
+ IN in_range statistic_range,
+ OUT out_bucket_start INT8
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ out_bucket_start = EXTRACT(EPOCH FROM DATE_TRUNC(in_range::text, in_timestamp));
+END $$;
+COMMENT ON FUNCTION interval_to_start
+ IS 'computes the start time of the bucket for an event at the current time given the desired bucket range';
+
+
+DROP PROCEDURE IF EXISTS merchant_do_bump_number_bucket_stat;
+CREATE OR REPLACE PROCEDURE merchant_do_bump_number_bucket_stat(
+ in_slug TEXT,
+ in_merchant_serial BIGINT,
+ in_timestamp TIMESTAMP,
+ in_delta INT8
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_meta INT8;
+ my_range statistic_range;
+ my_bucket_start INT8;
+ my_curs CURSOR (arg_slug TEXT)
+ FOR SELECT UNNEST(ranges)
+ FROM merchant_statistic_bucket_meta
+ WHERE slug=arg_slug;
+BEGIN
+ SELECT bmeta_serial_id
+ INTO my_meta
+ FROM merchant_statistic_bucket_meta
+ WHERE slug=in_slug
+ AND stype='number';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ OPEN my_curs (arg_slug:=in_slug);
+ LOOP
+ FETCH NEXT
+ FROM my_curs
+ INTO my_range;
+ EXIT WHEN NOT FOUND;
+ SELECT *
+ INTO my_bucket_start
+ FROM interval_to_start (in_timestamp, my_range);
+
+ UPDATE merchant_statistic_bucket_counter
+ SET cumulative_number = cumulative_number + in_delta
+ WHERE bmeta_serial_id=my_meta
+ AND merchant_serial=in_merchant_serial
+ AND bucket_start=my_bucket_start
+ AND bucket_range=my_range;
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_bucket_counter
+ (bmeta_serial_id
+ ,merchant_serial
+ ,bucket_start
+ ,bucket_range
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_bucket_start
+ ,my_range
+ ,in_delta);
+ END IF;
+ END LOOP;
+ CLOSE my_curs;
+END $$;
+
+
+DROP PROCEDURE IF EXISTS merchant_do_bump_amount_bucket_stat;
+CREATE OR REPLACE PROCEDURE merchant_do_bump_amount_bucket_stat(
+ in_slug TEXT,
+ in_merchant_serial BIGINT,
+ in_timestamp TIMESTAMP,
+ in_delta taler_amount_currency
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_meta INT8;
+ my_range statistic_range;
+ my_bucket_start INT8;
+ my_curs CURSOR (arg_slug TEXT)
+ FOR SELECT UNNEST(ranges)
+ FROM merchant_statistic_bucket_meta
+ WHERE slug=arg_slug;
+BEGIN
+ SELECT bmeta_serial_id
+ INTO my_meta
+ FROM merchant_statistic_bucket_meta
+ WHERE slug=in_slug
+ AND stype='amount';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ OPEN my_curs (arg_slug:=in_slug);
+ LOOP
+ FETCH NEXT
+ FROM my_curs
+ INTO my_range;
+ EXIT WHEN NOT FOUND;
+ SELECT *
+ INTO my_bucket_start
+ FROM interval_to_start (in_timestamp, my_range);
+
+ UPDATE merchant_statistic_bucket_amount
+ SET
+ cumulative_value = cumulative_value + (in_delta).val
+ + CASE
+ WHEN (in_delta).frac + cumulative_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_frac = cumulative_frac + (in_delta).frac
+ - CASE
+ WHEN (in_delta).frac + cumulative_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ WHERE bmeta_serial_id=my_meta
+ AND merchant_serial=in_merchant_serial
+ AND curr=(in_delta).curr
+ AND bucket_start=my_bucket_start
+ AND bucket_range=my_range;
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_bucket_amount
+ (bmeta_serial_id
+ ,merchant_serial
+ ,bucket_start
+ ,bucket_range
+ ,curr
+ ,cumulative_value
+ ,cumulative_frac
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_bucket_start
+ ,my_range
+ ,(in_delta).curr
+ ,(in_delta).val
+ ,(in_delta).frac);
+ END IF;
+ END LOOP;
+ CLOSE my_curs;
+END $$;
+
+COMMENT ON PROCEDURE merchant_do_bump_amount_bucket_stat
+ IS 'Updates an amount statistic tracked over buckets';
+
+
+DROP PROCEDURE IF EXISTS merchant_do_bump_number_interval_stat;
+CREATE OR REPLACE PROCEDURE merchant_do_bump_number_interval_stat(
+ in_slug TEXT,
+ in_merchant_serial BIGINT,
+ in_timestamp TIMESTAMP,
+ in_delta INT8
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_now INT8;
+ my_record RECORD;
+ my_meta INT8;
+ my_range0 INT8;
+ my_precision0 INT8;
+ my_start INT8;
+ my_event INT8;
+BEGIN
+ my_now = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8;
+ SELECT imeta_serial_id
+ ,ranges[1] AS range0
+ ,precisions[1] AS precision0
+ INTO my_record
+ FROM merchant_statistic_interval_meta
+ WHERE slug=in_slug
+ AND stype='number';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+
+ my_meta = my_record.imeta_serial_id;
+ my_precision0 = my_record.precision0;
+ my_start = my_now / 1000 / 1000; -- convert to seconds
+ my_start = my_start - my_start % my_precision0; -- round down
+
+ INSERT INTO metchant_statistic_counter_event
+ (imeta_serial_id
+ ,merchant_serial
+ ,slot
+ ,delta)
+ VALUES
+ (my_meta
+ ,in_merchant_serial
+ ,my_start
+ ,in_delta)
+ ON CONFLICT (imeta_serial_id, merchant_serial, slot)
+ DO UPDATE SET
+ delta = delta + in_delta
+ RETURNING nevent_serial_id
+ INTO my_event;
+
+ UPDATE merchant_statistic_interval_counter
+ SET cumulative_number = cumulative_number + in_delta
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = in_merchant_serial
+ AND range=my_range0;
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_interval_counter
+ (imeta_serial_id
+ ,merchant_serial
+ ,range
+ ,event_delimiter
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_range0
+ ,my_event
+ ,in_delta);
+ END IF;
+END $$;
+
+COMMENT ON PROCEDURE merchant_do_bump_number_interval_stat
+ IS 'Updates a numeric statistic tracked over an interval';
+
+
+DROP PROCEDURE IF EXISTS merchant_do_bump_amount_interval_stat;
+CREATE OR REPLACE PROCEDURE merchant_do_bump_amount_interval_stat(
+ in_slug TEXT,
+ in_merchant_serial BIGINT,
+ in_timestamp TIMESTAMP,
+ in_delta taler_amount_currency -- new amount in table that we should add to the tracker
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_now INT8;
+ my_record RECORD;
+ my_meta INT8;
+ my_precision0 INT8;
+ my_start INT8;
+ my_range0 INT8;
+ my_event INT8;
+BEGIN
+ my_now = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8;
+ SELECT imeta_serial_id
+ ,ranges[1] AS range0
+ ,precisions[1] AS precision0
+ INTO my_record
+ FROM merchant_statistic_interval_meta
+ WHERE slug=in_slug
+ AND stype='amount';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+
+ my_meta = my_record.imeta_serial_id;
+ my_precision0 = my_record.precision0;
+ my_range0 = my_record.range0;
+ my_start = my_now / 1000 / 1000; -- convert to seconds
+ my_start = my_start - my_start % my_precision0; -- round down
+
+ INSERT INTO merchant_statistic_amount_event AS msae
+ (imeta_serial_id
+ ,merchant_serial
+ ,slot
+ ,delta_curr
+ ,delta_value
+ ,delta_frac
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_start
+ ,(in_delta).curr
+ ,(in_delta).val
+ ,(in_delta).frac
+ )
+ ON CONFLICT (imeta_serial_id, merchant_serial, slot, delta_curr)
+ DO UPDATE SET
+ delta_value = msae.delta_value + (in_delta).val
+ + CASE
+ WHEN (in_delta).frac + msae.delta_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ delta_frac = msae.delta_frac + (in_delta).frac
+ - CASE
+ WHEN (in_delta).frac + msae.delta_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ RETURNING aevent_serial_id
+ INTO my_event;
+
+ UPDATE merchant_statistic_interval_amount
+ SET
+ cumulative_value = cumulative_value + (in_delta).val
+ + CASE
+ WHEN (in_delta).frac + cumulative_frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_frac = cumulative_frac + (in_delta).frac
+ - CASE
+ WHEN (in_delta).frac + cumulative_frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ WHERE imeta_serial_id=my_meta
+ AND merchant_serial=in_merchant_serial
+ AND range=my_range0
+ AND curr=(in_delta).curr;
+ IF NOT FOUND
+ THEN
+ INSERT INTO merchant_statistic_interval_amount
+ (imeta_serial_id
+ ,merchant_serial
+ ,range
+ ,aevent_serial_id
+ ,curr
+ ,cumulative_value
+ ,cumulative_frac
+ ) VALUES (
+ my_meta
+ ,in_merchant_serial
+ ,my_range0
+ ,my_event
+ ,(in_delta).curr
+ ,(in_delta).val
+ ,(in_delta).frac);
+ END IF;
+END $$;
+COMMENT ON PROCEDURE merchant_do_bump_amount_interval_stat
+ IS 'Updates an amount statistic tracked over an interval';
+
+
+DROP PROCEDURE IF EXISTS merchant_do_bump_number_stat;
+CREATE OR REPLACE PROCEDURE merchant_do_bump_number_stat(
+ in_slug TEXT,
+ in_merchant_serial BIGINT,
+ in_timestamp TIMESTAMP,
+ in_delta INT8
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_bucket_stat (in_slug, in_merchant_serial, in_timestamp, in_delta);
+ CALL merchant_do_bump_number_interval_stat (in_slug, in_merchant_serial, in_timestamp, in_delta);
+END $$;
+COMMENT ON PROCEDURE merchant_do_bump_number_stat
+ IS 'Updates a numeric statistic (bucket or interval)';
+
+
+DROP PROCEDURE IF EXISTS merchant_do_bump_amount_stat;
+CREATE OR REPLACE PROCEDURE merchant_do_bump_amount_stat(
+ in_slug TEXT,
+ in_merchant_serial BIGINT,
+ in_timestamp TIMESTAMP,
+ in_delta taler_amount_currency
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_amount_bucket_stat (in_slug, in_merchant_serial, in_timestamp, in_delta);
+ CALL merchant_do_bump_amount_interval_stat (in_slug, in_merchant_serial, in_timestamp, in_delta);
+END $$;
+COMMENT ON PROCEDURE merchant_do_bump_amount_stat
+ IS 'Updates an amount statistic (bucket or interval)';
+
+
+-- FIXME: rewrite to return values for ALL ranges! (more sane!)
+DROP FUNCTION IF EXISTS merchant_statistic_interval_number_get;
+CREATE OR REPLACE FUNCTION merchant_statistic_interval_number_get (
+ IN in_slug TEXT,
+ IN in_instance_id TEXT,
+ IN in_range INT8,
+ OUT out_value INT8
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM NOW()) * 1000000)::INT8;
+ my_ranges INT8[];
+ my_range INT8;
+ my_delta INT8;
+ my_meta INT8;
+ my_instance_id INT8;
+ my_curs CURSOR (meta_serial_id INT8,instid INT8,range INT8,min_serial INT8) FOR
+ SELECT nevent_serial_id
+ ,delta
+ FROM merchant_statistic_counter_event
+ WHERE imeta_serial_id = meta_serial_id
+ AND instance_id = instid
+ AND slot < my_time - range
+ AND imeta_serial_id >= min_serial
+ ORDER BY slot ASC;
+ my_rec RECORD;
+ my_irec RECORD;
+ my_i INT;
+ my_max_serial INT8 DEFAULT NULL;
+ my_rval INT8 DEFAULT 0;
+BEGIN
+ SELECT merchant_serial
+ INTO my_instance_id
+ FROM merchant_instances
+ WHERE merchant_id=in_instance_id;
+ IF NOT FOUND
+ THEN
+ out_value = 0;
+ RETURN;
+ END IF;
+
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ INTO my_rec
+ FROM merchant_statistic_interval_meta
+ WHERE slug=in_slug;
+ IF NOT FOUND
+ THEN
+ out_value = 0;
+ RETURN;
+ END IF;
+
+ my_ranges = my_rec.ranges;
+ my_meta = my_rec.imeta_serial_id;
+ -- my_precisions = my_rec.precisions;
+
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ my_range = my_ranges[my_i];
+ -- my_precision = my_precisions[my_i];
+ SELECT event_delimiter
+ ,cumulative_number
+ INTO my_irec
+ FROM merchant_statistic_interval_counter
+ WHERE imeta_serial_id = my_meta
+ AND range = my_range
+ AND merchant_serial = in_merchant_serial;
+ my_max_serial = my_irec.event_delimiter;
+ my_rval = my_rval + my_irec.cumulative_number;
+
+ my_delta = 0;
+ OPEN my_curs (meta_serial_id := my_meta, range := my_range, instid := my_instance_id, min_serial := my_max_serial);
+ LOOP
+ FETCH NEXT FROM my_curs INTO my_irec;
+ EXIT WHEN NOT FOUND;
+ my_delta = my_delta + my_irec.delta;
+ my_max_serial = my_irec.nevent_serial_id;
+ END LOOP;
+
+ IF (0 != my_delta)
+ THEN
+ -- remove expired events from the sum of the current slot
+ my_rval = my_rval - my_delta;
+ UPDATE merchant_statistic_interval_counter
+ SET cumulative_number = cumulative_number - my_delta,
+ event_delimiter = my_max_serial + 1
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = in_merchant_serial
+ AND range = my_range;
+ IF (my_i < array_length(my_ranges,1))
+ THEN
+ -- carry over events into the next slot
+ UPDATE merchant_statistic_interval_counter
+ SET cumulative_number = cumulative_number + my_delta
+ WHERE imeta_serial_id = my_meta
+ AND merchant_serial = in_merchant_serial
+ AND range=my_ranges[my_i+1];
+ -- FIXME: here we should possibly combine multiple events by rounding down to my_precision!
+ ELSE
+ -- events are obsolete, delete them
+ DELETE FROM merchant_statistic_counter_event
+ WHERE imeta_serial_id = my_meta
+ AND instance_id = my_instance_id
+ AND slot < my_time - my_range;
+ END IF;
+ END IF;
+
+ IF (my_range = in_range)
+ THEN
+ out_value = my_rval;
+ END IF;
+ END LOOP;
+ CLOSE my_curs;
+END $$;
+
+COMMENT ON FUNCTION merchant_statistic_interval_number_get
+ IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value';
+
+
+-- FIXME: create dual of this function to GC the amount_events! (but needs additional loop per currency, and fancy amount handling!)
+DROP PROCEDURE IF EXISTS merchant_statistic_counter_gc;
+CREATE OR REPLACE PROCEDURE merchant_statistic_counter_gc ()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_instance INT8;
+ my_rec RECORD;
+ my_sum RECORD;
+ my_meta INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_precision INT4;
+ my_i INT4;
+ min_slot INT8;
+ max_slot INT8;
+ end_slot INT8;
+ my_total INT8;
+BEGIN
+ -- GC for all instances
+ FOR my_instance IN
+ SELECT DISTINCT merchant_serial
+ FROM merchant_statistic_counter_event
+ LOOP
+ -- Do combination work for all numeric statistic events
+ FOR my_rec IN
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ FROM merchant_statistic_interval_meta
+ LOOP
+ my_meta = my_rec.imeta_serial_id;
+ my_ranges = my_rec.ranges;
+ my_precisions = my_rec.precisions;
+
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1)-1,0)
+ LOOP
+ min_slot = my_ranges[my_i];
+ end_slot = my_ranges[my_i + 1];
+ my_precision = my_precisions[my_i];
+ LOOP
+ EXIT WHEN min_slot >= end_slot;
+ max_slot = min_slot + my_precision;
+ SELECT SUM(delta) AS total,
+ COUNT(*) AS matches
+ INTO my_sum
+ FROM merchant_statistic_counter_event
+ WHERE merchant_serial=my_instance
+ AND imeta_serial_id=my_meta
+ AND slot >= min_slot
+ AND slot < max_slot;
+ -- we only proceed if we had more then one match (optimization)
+ IF FOUND AND my_sum.matches > 1
+ THEN
+ my_total = my_sum.total;
+ -- combine entries
+ DELETE FROM merchant_statistic_counter_event
+ WHERE merchant_serial=my_instance
+ AND imeta_serial_id=my_meta
+ AND slot >= min_slot
+ AND slot < max_slot;
+ -- The previous delete ensures that this is a conflict-free INSERT
+ INSERT INTO merchant_statistic_counter_event
+ (imeta_serial_id
+ ,merchant_serial
+ ,slot
+ ,delta
+ ) VALUES
+ (my_meta
+ ,my_instance
+ ,min_slot
+ ,my_total);
+ END IF;
+ min_slot = min_slot + my_precision;
+ END LOOP; -- min_slot to end_slot by precision loop
+ END LOOP; -- my_i loop
+ -- Finally, delete all events beyond the range we care about
+ DELETE FROM merchant_statistic_counter_event
+ WHERE merchant_serial=my_instance
+ AND imeta_serial_id=my_meta
+ AND slot > my_ranges[array_length(my_ranges,1)];
+ END LOOP; -- my_rec loop
+ END LOOP; -- my_instance loop
+END $$;
+COMMENT ON PROCEDURE merchant_statistic_counter_gc
+ IS 'Performs garbage collection and compaction of the merchant_statistic_counter_event table';
+
+
+DROP PROCEDURE IF EXISTS merchant_statistic_bucket_gc;
+CREATE OR REPLACE PROCEDURE merchant_statistic_bucket_gc ()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME: GC buckets!
+END $$;
+COMMENT ON PROCEDURE merchant_statistic_counter_gc
+ IS 'Performs garbage collection of the merchant_statistic_bucket_counter and merchant_statistic_bucket_amount tables';
+
+
+DROP FUNCTION IF EXISTS merchant_statistic_interval_amount_get;
+CREATE OR REPLACE FUNCTION merchant_statistic_interval_amount_get (
+ IN in_slug TEXT,
+ IN in_instance INT8,
+ IN in_range INT8
+)
+RETURNS SETOF taler_amount_currency
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME: implement!
+END $$;
+
+COMMENT ON FUNCTION merchant_statistic_interval_amount_get
+ IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value; multiple values are returned, one per currency';
+
+
diff --git a/src/backenddb/procedures.sql.in b/src/backenddb/procedures.sql.in
@@ -1,6 +1,6 @@
--
-- This file is part of TALER
--- Copyright (C) 2024 Taler Systems SA
+-- 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
@@ -24,5 +24,6 @@ SET search_path TO merchant;
#include "pg_update_product.sql"
#include "pg_account_kyc_set_status.sql"
#include "pg_account_kyc_set_failed.sql"
+#include "pg_statistics_helpers.sql"
COMMIT;
