commit a834f02ed08d9e32b3535069733ee6e83b43c1a9
parent 257a54ddbc6755fb7ce9e11a0f7d3d588e4f3098
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Tue, 25 Mar 2025 02:57:13 +0100
enable new statistics tables, not yet tested enough but does not seem to break stuff either
Diffstat:
5 files changed, 576 insertions(+), 559 deletions(-)
diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am
@@ -37,7 +37,8 @@ sqlinputs = \
exchange-0006.sql.in \
exchange-0007.sql.in \
exchange-0008.sql.in \
- exchange-0009.sql
+ exchange-0009.sql \
+ exchange_statistics_*.sql
sql_DATA = \
benchmark-0001.sql \
diff --git a/src/exchangedb/exchange-0009.sql b/src/exchangedb/exchange-0009.sql
@@ -78,4 +78,576 @@ INSERT INTO exchange_tables
,TRUE
,FALSE);
+-- 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 exchange_statistic_bucket_meta
+ (bmeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,origin TEXT NOT NULL
+ ,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 exchange_statistic_bucket_meta
+ IS 'meta data about a statistic with events falling into buckets we are tracking';
+COMMENT ON COLUMN exchange_statistic_bucket_meta.bmeta_serial_id
+ IS 'unique identifier for this type of bucket statistic we are tracking';
+COMMENT ON COLUMN exchange_statistic_bucket_meta.origin
+ IS 'which customization schema does this statistic originate from (used for easy deletion)';
+COMMENT ON COLUMN exchange_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 exchange_statistic_bucket_meta.description
+ IS 'description of the statistic being tracked';
+COMMENT ON COLUMN exchange_statistic_bucket_meta.stype
+ IS 'statistic type, what kind of data is being tracked, amount or number';
+COMMENT ON COLUMN exchange_statistic_bucket_meta.ranges
+ IS 'size of the buckets that are being kept for this statistic';
+COMMENT ON COLUMN exchange_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 INDEX exchange_statistic_bucket_meta_by_origin
+ ON exchange_statistic_bucket_meta
+ (origin);
+
+
+CREATE FUNCTION create_table_exchange_statistic_bucket_counter (
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ PERFORM create_partitioned_table (
+ 'CREATE TABLE %I'
+ '(bmeta_serial_id INT8 NOT NULL'
+ ' REFERENCES exchange_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE'
+ ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
+ ',bucket_start INT8 NOT NULL'
+ ',bucket_range statistic_range NOT NULL'
+ ',cumulative_number INT8 NOT NULL'
+ ',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)'
+ ') %s;'
+ ,'exchange_statistic_bucket_counter'
+ ,'PARTITION BY HASH (h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'various numeric statistics (cumulative counters) being tracked by bucket into which they fall'
+ ,'exchange_statistic_bucket_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies what the statistic is about'
+ ,'bmeta_serial_id'
+ ,'exchange_statistic_bucket_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
+ ,'h_payto'
+ ,'exchange_statistic_bucket_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'start date for the bucket in seconds since the epoch'
+ ,'bucket_start'
+ ,'exchange_statistic_bucket_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'range of the bucket'
+ ,'bucket_range'
+ ,'exchange_statistic_bucket_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword'
+ ,'cumulative_number'
+ ,'exchange_statistic_bucket_counter'
+ ,partition_suffix
+ );
+END $$;
+
+
+CREATE FUNCTION create_table_exchange_statistic_bucket_amount (
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ PERFORM create_partitioned_table (
+ 'CREATE TABLE %I'
+ '(bmeta_serial_id INT8 NOT NULL'
+ ' REFERENCES exchange_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE'
+ ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
+ ',bucket_start INT8 NOT NULL'
+ ',bucket_range statistic_range NOT NULL'
+ ',cumulative_value taler_amount NOT NULL'
+ ',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)'
+ ') %s;'
+ ,'exchange_statistic_bucket_amount'
+ ,'PARTITION BY HASH(h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table (
+ 'various amount statistics being tracked'
+ ,'exchange_statistic_bucket_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies what the statistic is about'
+ ,'bmeta_serial_id'
+ ,'exchange_statistic_bucket_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column (
+ 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
+ ,'h_payto'
+ ,'exchange_statistic_bucket_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'start date for the bucket in seconds since the epoch'
+ ,'bucket_start'
+ ,'exchange_statistic_bucket_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'range of the bucket'
+ ,'bucket_range'
+ ,'exchange_statistic_bucket_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'amount being tracked'
+ ,'cumulative_value'
+ ,'exchange_statistic_bucket_amount'
+ ,partition_suffix
+ );
+END $$;
+
+
+-- -------------- Interval statistics ---------------------
+
+
+CREATE TABLE exchange_statistic_interval_meta
+ (imeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+ ,origin TEXT NOT NULL
+ ,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 exchange_statistic_interval_meta
+ IS 'meta data about an interval statistic we are tracking';
+COMMENT ON COLUMN exchange_statistic_interval_meta.imeta_serial_id
+ IS 'unique identifier for this type of interval statistic we are tracking';
+COMMENT ON COLUMN exchange_statistic_interval_meta.origin
+ IS 'which customization schema does this statistic originate from (used for easy deletion)';
+COMMENT ON COLUMN exchange_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 exchange_statistic_interval_meta.description
+ IS 'description of the statistic being tracked';
+COMMENT ON COLUMN exchange_statistic_interval_meta.stype
+ IS 'statistic type, what kind of data is being tracked, amount or number';
+COMMENT ON COLUMN exchange_statistic_interval_meta.ranges
+ IS 'range of values that is being kept for this statistic, in seconds, must be monotonically increasing';
+COMMENT ON COLUMN exchange_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 INDEX exchange_statistic_interval_meta_by_origin
+ ON exchange_statistic_interval_meta
+ (origin);
+
+
+CREATE FUNCTION create_table_exchange_statistic_counter_event (
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE %I'
+ '(nevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY'
+ ',imeta_serial_id INT8'
+ ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
+ ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
+ ',slot INT8 NOT NULL'
+ ',delta INT8 NOT NULL'
+ ',UNIQUE (h_payto,imeta_serial_id,slot)'
+ ') %s ;'
+ ,'exchange_statistic_counter_event'
+ ,'PARTITION BY HASH(h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'number to decrement an interval statistic by when a certain time value is reached'
+ ,'exchange_statistic_counter_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'unique identifier for this number event'
+ ,'nevent_serial_id'
+ ,'exchange_statistic_counter_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies what the statistic is about; must be of stype number'
+ ,'imeta_serial_id'
+ ,'exchange_statistic_counter_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
+ ,'h_payto'
+ ,'exchange_statistic_counter_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies the time slot at which the given event(s) happened, rounded down by the respective precisions value'
+ ,'slot'
+ ,'exchange_statistic_counter_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'total cumulative number that was added at the time identified by slot'
+ ,'delta'
+ ,'exchange_statistic_counter_event'
+ ,partition_suffix
+ );
+END $$;
+
+
+CREATE FUNCTION constrain_table_exchange_statistic_counter_event(
+ IN partition_suffix TEXT
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT default 'exchange_statistic_counter_event';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_nevent_serial_id_key'
+ ' UNIQUE (nevent_serial_id)'
+ );
+END $$;
+
+
+CREATE FUNCTION create_table_exchange_statistic_interval_counter (
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE %I'
+ '(imeta_serial_id INT8 NOT NULL'
+ ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
+ ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
+ ',range INT8 NOT NULL'
+ ',event_delimiter INT8 NOT NULL'
+ ',cumulative_number INT8 NOT NULL'
+ ',UNIQUE (h_payto,imeta_serial_id,range)'
+ ') %s ;'
+ ,'exchange_statistic_interval_counter'
+ ,'PARTITION BY HASH(h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'various numeric statistics (cumulative counters) being tracked'
+ ,'exchange_statistic_interval_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies what the statistic is about'
+ ,'imeta_serial_id'
+ ,'exchange_statistic_interval_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
+ ,'h_payto'
+ ,'exchange_statistic_interval_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges'
+ ,'range'
+ ,'exchange_statistic_interval_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'determines the last event currently included in the interval'
+ ,'event_delimiter'
+ ,'exchange_statistic_interval_counter'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword'
+ ,'cumulative_number'
+ ,'exchange_statistic_interval_counter'
+ ,partition_suffix
+ );
+END $$;
+
+
+CREATE FUNCTION foreign_table_exchange_statistic_interval_counter()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT DEFAULT 'exchange_statistic_interval_counter';
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_event_delimiter_foreign_key'
+ ' FOREIGN KEY (event_delimiter) '
+ ' REFERENCES exchange_statistic_counter_event (nevent_serial_id) ON DELETE RESTRICT'
+ );
+END $$;
+
+
+CREATE FUNCTION create_table_exchange_statistic_amount_event (
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE %I'
+ '(aevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY'
+ ',imeta_serial_id INT8'
+ ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
+ ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
+ ',slot INT8 NOT NULL'
+ ',delta taler_amount NOT NULL'
+ ',CONSTRAINT event_key UNIQUE (h_payto,imeta_serial_id,slot)'
+ ') %s ;'
+ ,'exchange_statistic_amount_event'
+ ,'PARTITION BY HASH(h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'amount to decrement an interval statistic by when a certain time value is reached'
+ ,'exchange_statistic_amount_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'unique identifier for this amount event'
+ ,'aevent_serial_id'
+ ,'exchange_statistic_amount_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies what the statistic is about; must be of clazz interval and of stype amount'
+ ,'imeta_serial_id'
+ ,'exchange_statistic_amount_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
+ ,'h_payto'
+ ,'exchange_statistic_amount_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies the time slot at which the given event(s) happened'
+ ,'slot'
+ ,'exchange_statistic_amount_event'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'total cumulative amount that was added at the time identified by slot'
+ ,'delta'
+ ,'exchange_statistic_amount_event'
+ ,partition_suffix
+ );
+END $$;
+
+
+CREATE FUNCTION constrain_table_exchange_statistic_amount_event(
+ IN partition_suffix TEXT
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT default 'exchange_statistic_amount_event';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_aevent_serial_id_key'
+ ' UNIQUE (aevent_serial_id)'
+ );
+END $$;
+
+
+
+CREATE FUNCTION create_table_exchange_statistic_interval_amount (
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE %I'
+ '(imeta_serial_id INT8 NOT NULL'
+ ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
+ ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
+ ',event_delimiter INT8 NOT NULL'
+ ',range INT8 NOT NULL'
+ ',cumulative_value taler_amount NOT NULL'
+ ',UNIQUE (h_payto,imeta_serial_id,range)'
+ ') %s ;'
+ ,'exchange_statistic_interval_amount'
+ ,'PARTITION BY HASH(h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'various amount statistics being tracked'
+ ,'exchange_statistic_interval_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies what the statistic is about'
+ ,'imeta_serial_id'
+ ,'exchange_statistic_interval_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
+ ,'h_payto'
+ ,'exchange_statistic_interval_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges'
+ ,'range'
+ ,'exchange_statistic_interval_amount'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'amount affected by the event'
+ ,'cumulative_value'
+ ,'exchange_statistic_interval_amount'
+ ,partition_suffix
+ );
+END $$;
+
+
+CREATE FUNCTION foreign_table_exchange_statistic_interval_amount()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT DEFAULT 'exchange_statistic_interval_amount';
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_event_delimiter_foreign_key'
+ ' FOREIGN KEY (event_delimiter) '
+ ' REFERENCES exchange_statistic_amount_event (aevent_serial_id) ON DELETE RESTRICT'
+ );
+END $$;
+
+
+CREATE TYPE exchange_statistic_interval_number_get_return_value
+ AS
+ (range INT8
+ ,rvalue INT8
+ );
+COMMENT ON TYPE exchange_statistic_interval_number_get_return_value
+ IS 'Return type for exchange_statistic_interval_number_get stored procedure';
+
+CREATE TYPE exchange_statistic_interval_amount_get_return_value
+ AS
+ (range INT8
+ ,rvalue taler_amount
+ );
+COMMENT ON TYPE exchange_statistic_interval_amount_get_return_value
+ IS 'Return type for exchange_statistic_interval_amount_get stored procedure';
+
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('exchange_statistic_bucket_counter'
+ ,'exchange-0009'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_bucket_amount'
+ ,'exchange-0009'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_counter_event'
+ ,'exchange-0009'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_counter_event'
+ ,'exchange-0009'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_interval_counter'
+ ,'exchange-0009'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_interval_counter'
+ ,'exchange-0009'
+ ,'foreign'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_amount_event'
+ ,'exchange-0009'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_amount_event'
+ ,'exchange-0009'
+ ,'constrain'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_interval_amount'
+ ,'exchange-0009'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('exchange_statistic_interval_amount'
+ ,'exchange-0009'
+ ,'foreign'
+ ,TRUE
+ ,FALSE);
+
COMMIT;
diff --git a/src/exchangedb/exchange-xx10.sql b/src/exchangedb/exchange-xx10.sql
@@ -1,557 +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 exchange-xx10.sql
--- @brief Tables for statistics
--- @author Christian Grothoff
-
--- FIXME: figure out if we do need NULL fo h_payto!
-
-BEGIN;
-
--- Check patch versioning is in place.
-SELECT _v.register_patch('exchange-xx10', NULL, NULL);
-
-SET search_path TO exchange;
-
--- 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 exchange_statistic_bucket_meta
- (bmeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
- ,origin TEXT NOT NULL
- ,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 exchange_statistic_bucket_meta
- IS 'meta data about a statistic with events falling into buckets we are tracking';
-COMMENT ON COLUMN exchange_statistic_bucket_meta.bmeta_serial_id
- IS 'unique identifier for this type of bucket statistic we are tracking';
-COMMENT ON COLUMN exchange_statistic_bucket_meta.origin
- IS 'which customization schema does this statistic originate from (used for easy deletion)';
-COMMENT ON COLUMN exchange_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 exchange_statistic_bucket_meta.description
- IS 'description of the statistic being tracked';
-COMMENT ON COLUMN exchange_statistic_bucket_meta.stype
- IS 'statistic type, what kind of data is being tracked, amount or number';
-COMMENT ON COLUMN exchange_statistic_bucket_meta.ranges
- IS 'size of the buckets that are being kept for this statistic';
-COMMENT ON COLUMN exchange_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 INDEX exchange_statistic_bucket_meta_by_origin
- ON exchange_statistic_bucket_meta
- (origin);
-
-
-CREATE FUNCTION create_table_exchange_statistic_bucket_counter (
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table (
- 'CREATE TABLE %I'
- '(bmeta_serial_id INT8 NOT NULL'
- ' REFERENCES exchange_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE'
- ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',bucket_start INT8 NOT NULL'
- ',bucket_range statistic_range NOT NULL'
- ',cumulative_number INT8 NOT NULL'
- ',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)'
- ') %s;'
- ,'exchange_statistic_bucket_counter'
- ,'PARTITION BY HASH (h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table(
- 'various numeric statistics (cumulative counters) being tracked by bucket into which they fall'
- ,'exchange_statistic_bucket_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies what the statistic is about'
- ,'bmeta_serial_id'
- ,'exchange_statistic_bucket_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
- ,'h_payto'
- ,'exchange_statistic_bucket_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'start date for the bucket in seconds since the epoch'
- ,'bucket_start'
- ,'exchange_statistic_bucket_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'range of the bucket'
- ,'bucket_range'
- ,'exchange_statistic_bucket_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword'
- ,'cumulative_number'
- ,'exchange_statistic_bucket_counter'
- ,partition_suffix
- );
-END $$;
-
-
-CREATE FUNCTION create_table_exchange_statistic_bucket_amount (
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table (
- 'CREATE TABLE %I'
- '(bmeta_serial_id INT8 NOT NULL'
- ' REFERENCES exchange_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE'
- ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',bucket_start INT8 NOT NULL'
- ',bucket_range statistic_range NOT NULL'
- ',cumulative_value taler_amount NOT NULL'
- ',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)'
- ') %s;'
- ,'exchange_statistic_bucket_amount'
- ,'PARTITION BY HASH(h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table (
- 'various amount statistics being tracked',
- ,'exchange_statistic_bucket_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies what the statistic is about'
- ,'bmeta_serial_id'
- ,'exchange_statistic_bucket_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column (
- 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics',
- ,'h_payto'
- ,'exchange_statistic_bucket_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'start date for the bucket in seconds since the epoch'
- ,'bucket_start'
- ,'exchange_statistic_bucket_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'range of the bucket'
- ,'bucket_range'
- ,'exchange_statistic_bucket_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'amount being tracked'
- ,'cumulative_value'
- ,'exchange_statistic_bucket_amount'
- ,partition_suffix
- );
-END $$;
-
-
--- -------------- Interval statistics ---------------------
-
-
-CREATE TABLE exchange_statistic_interval_meta
- (imeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
- ,origin TEXT NOT NULL
- ,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 exchange_statistic_interval_meta
- IS 'meta data about an interval statistic we are tracking';
-COMMENT ON COLUMN exchange_statistic_interval_meta.imeta_serial_id
- IS 'unique identifier for this type of interval statistic we are tracking';
-COMMENT ON COLUMN exchange_statistic_interval_meta.origin
- IS 'which customization schema does this statistic originate from (used for easy deletion)';
-COMMENT ON COLUMN exchange_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 exchange_statistic_interval_meta.description
- IS 'description of the statistic being tracked';
-COMMENT ON COLUMN exchange_statistic_interval_meta.stype
- IS 'statistic type, what kind of data is being tracked, amount or number';
-COMMENT ON COLUMN exchange_statistic_interval_meta.ranges
- IS 'range of values that is being kept for this statistic, in seconds, must be monotonically increasing';
-COMMENT ON COLUMN exchange_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 INDEX exchange_statistic_interval_meta_by_origin
- ON exchange_statistic_interval_meta
- (origin);
-
-
-CREATE FUNCTION create_table_exchange_statistic_counter_event (
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE %I'
- '(nevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
- ',imeta_serial_id INT8'
- ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
- ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',slot INT8 NOT NULL'
- ',delta INT8 NOT NULL'
- ',UNIQUE (h_payto,imeta_serial_id,slot)'
- ') %s ;'
- ,'exchange_statistic_counter_event'
- ,'PARTITION BY HASH(h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table(
- 'number to decrement an interval statistic by when a certain time value is reached';
- ,'exchange_statistic_counter_event'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'unique identifier for this number event'
- ,'nevent_serial_id'
- ,'exchange_statistic_counter_event'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies what the statistic is about; must be of stype number'
- ,'imeta_serial_id'
- ,'exchange_statistic_counter_event'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
- ,'h_payto'
- ,'exchange_statistic_counter_event'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies the time slot at which the given event(s) happened, rounded down by the respective precisions value'
- ,'slot'
- ,'exchange_statistic_counter_event'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'total cumulative number that was added at the time identified by slot'
- ,'delta'
- ,'exchange_statistic_counter_event'
- ,partition_suffix
- );
-END $$;
-
-
-CREATE FUNCTION create_table_exchange_statistic_interval_counter (
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE %I'
- '(imeta_serial_id INT8 NOT NULL'
- ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
- ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',range INT8 NOT NULL'
- ',event_delimiter INT8 NOT NULL'
- ',cumulative_number INT8 NOT NULL'
- ',UNIQUE (h_payto,imeta_serial_id,range)'
- ') %s ;'
- ,'exchange_statistic_interval_counter'
- ,'PARTITION BY HASH(h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table(
- 'various numeric statistics (cumulative counters) being tracked'
- ,'exchange_statistic_interval_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies what the statistic is about'
- ,'imeta_serial_id'
- ,'exchange_statistic_interval_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
- ,'h_payto'
- ,'exchange_statistic_interval_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges'
- ,'range'
- ,'exchange_statistic_interval_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'determines the last event currently included in the interval'
- ,'event_delimiter'
- ,'exchange_statistic_interval_counter'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword'
- ,'cumulative_number'
- ,'exchange_statistic_interval_counter'
- ,partition_suffix
- );
-END $$;
-
-
-CREATE FUNCTION foreign_table_exchange_statistic_interval_counter()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'exchange_statistic_interval_counter';
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name ||
- ' ADD CONSTRAINT ' || table_name || '_event_delimiter_foreign_key'
- ' FOREIGN KEY (event_delimiter) '
- ' REFERENCES exchange_statistic_counter_event (nevent_serial_id) ON DELETE RESTRICT'
- );
-END $$;
-
-
-CREATE FUNCTION create_table_exchange_statistic_amount_event (
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE %I'
- '(aevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
- ',imeta_serial_id INT8'
- ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
- ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',slot INT8 NOT NULL'
- ',delta taler_amount NOT NULL'
- ',CONSTRAINT event_key UNIQUE (h_payto,imeta_serial_id,slot)'
- ') %s ;'
- ,'exchange_statistic_amount_event'
- ,'PARTITION BY HASH(h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table(
- 'amount to decrement an interval statistic by when a certain time value is reached'
- ,'exchange_statistic_amount_event'
- ,partition_suffix
- );
-PERFORM comment_partitioned_column(
- 'unique identifier for this amount event'
- ,'aevent_serial_id'
- ,'exchange_statistic_amount_event'
- ,partition_suffix
- );
-PERFORM comment_partitioned_column
- 'identifies what the statistic is about; must be of clazz interval and of stype amount'
- ,'imeta_serial_id'
- ,'exchange_statistic_amount_event'
- ,partition_suffix
- );
-PERFORM comment_partitioned_column
- 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
- ,'h_payto'
- ,'exchange_statistic_amount_event'
- ,partition_suffix
- );
-PERFORM comment_partitioned_column
- 'identifies the time slot at which the given event(s) happened'
- ,'slot'
- ,'exchange_statistic_amount_event'
- ,partition_suffix
- );
-PERFORM comment_partitioned_column
- 'total cumulative amount that was added at the time identified by slot'
- ,'delta'
- ,'exchange_statistic_amount_event'
- ,partition_suffix
- );
-END $$;
-
-
-
-CREATE FUNCTION create_table_exchange_statistic_interval_amount (
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE %I'
- '(imeta_serial_id INT8 NOT NULL'
- ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE'
- ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',event_delimiter INT8 NOT NULL'
- ',range INT8 NOT NULL'
- ',cumulative_value taler_amount NOT NULL'
- ',UNIQUE (h_payto,imeta_serial_id,range)'
- ') %s ;'
- ,'exchange_statistic_interval_amount'
- ,'PARTITION BY HASH(h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table(
- 'various amount statistics being tracked'
- ,'exchange_statistic_interval_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies what the statistic is about'
- ,'imeta_serial_id'
- ,'exchange_statistic_interval_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics'
- ,'h_payto'
- ,'exchange_statistic_interval_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges'
- ,'range'
- ,'exchange_statistic_interval_amount'
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'amount affected by the event'
- ,'cumulative_value'
- ,'exchange_statistic_interval_amount'
- ,partition_suffix
- );
-END $$;
-
-
-CREATE FUNCTION foreign_table_exchange_statistic_interval_amount()
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'exchange_statistic_interval_amount';
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name ||
- ' ADD CONSTRAINT ' || table_name || '_event_delimiter_foreign_key'
- ' FOREIGN KEY (event_delimiter) '
- ' REFERENCES exchange_statistic_amount_event (aevent_serial_id) ON DELETE RESTRICT'
- );
-END $$;
-
-
-CREATE TYPE exchange_statistic_interval_number_get_return_value
- AS
- (range INT8
- ,rvalue INT8
- );
-COMMENT ON TYPE exchange_statistic_interval_number_get_return_value
- 'Return type for exchange_statistic_interval_number_get stored procedure';
-
-CREATE TYPE exchange_statistic_interval_amount_get_return_value
- AS
- (range INT8
- ,rvalue taler_amount
- );
-COMMENT ON TYPE exchange_statistic_interval_amount_get_return_value
- 'Return type for exchange_statistic_interval_amount_get stored procedure';
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('exchange_statistic_bucket_counter'
- ,'exchange-xx10'
- ,'create'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_bucket_amount'
- ,'exchange-xx10'
- ,'create'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_counter_event'
- ,'exchange-xx10'
- ,'create'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_interval_counter'
- ,'exchange-xx10'
- ,'create'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_interval_counter'
- ,'exchange-xx10'
- ,'foreign'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_amount_event'
- ,'exchange-xx10'
- ,'create'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_interval_amount'
- ,'exchange-xx10'
- ,'create'
- ,TRUE
- ,FALSE),
- ('exchange_statistic_interval_amount'
- ,'exchange-xx10'
- ,'foreign'
- ,TRUE
- ,FALSE);
-
-
-COMMIT;
diff --git a/src/exchangedb/exchange_statistics_helpers.sql b/src/exchangedb/exchange_statistics_helpers.sql
@@ -432,7 +432,7 @@ COMMENT ON PROCEDURE exchange_do_bump_amount_stat
DROP FUNCTION IF EXISTS exchange_statistic_interval_number_get;
CREATE OR REPLACE FUNCTION exchange_statistic_interval_number_get (
IN in_slug TEXT,
- IN in_h_payto BYTA
+ IN in_h_payto BYTEA
)
RETURNS SETOF exchange_statistic_interval_number_get_return_value
LANGUAGE plpgsql
diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in
@@ -59,5 +59,6 @@ SET search_path TO exchange;
#include "exchange_do_insert_aml_program_failure.sql"
#include "exchange_do_set_aml_lock.sql"
#include "exchange_do_insert_sanction_list_hit.sql"
+#include "exchange_statistics_helpers.sql"
COMMIT;
