commit 79f095f2c3f125276664ddfe9d92d54996a2a182
parent 27e96db63c75da07ae1f1eb95a8c49618aa9e5f4
Author: Christian Grothoff <christian@grothoff.org>
Date: Sat, 29 Mar 2025 14:53:11 +0100
-fix misc statistics issues
Diffstat:
8 files changed, 135 insertions(+), 79 deletions(-)
diff --git a/src/exchange-tools/taler-exchange-dbinit.c b/src/exchange-tools/taler-exchange-dbinit.c
@@ -114,10 +114,8 @@ run (void *cls,
{
fprintf (stderr,
"Failed to initialize database.\n");
- TALER_EXCHANGEDB_plugin_unload (plugin);
- plugin = NULL;
global_ret = EXIT_NOPERMISSION;
- return;
+ goto exit;
}
if (gc_db || clear_shards)
{
@@ -126,10 +124,8 @@ run (void *cls,
{
fprintf (stderr,
"Failed to prepare database.\n");
- TALER_EXCHANGEDB_plugin_unload (plugin);
- plugin = NULL;
global_ret = EXIT_NOPERMISSION;
- return;
+ goto exit;
}
if (clear_shards)
{
@@ -167,7 +163,7 @@ run (void *cls,
fprintf (stderr,
"'exchange' is not a customization rule set!\n");
global_ret = EXIT_INVALIDARGUMENT;
- return;
+ goto exit;
}
if (GNUNET_OK !=
plugin->preflight (plugin->cls))
@@ -175,7 +171,7 @@ run (void *cls,
fprintf (stderr,
"Preflight check failed!\n");
global_ret = EXIT_FAILURE;
- return;
+ goto exit;
}
switch (plugin->disable_rules (plugin->cls,
disable_rules))
@@ -184,15 +180,15 @@ run (void *cls,
fprintf (stderr,
"Hard DB error trying to disable customization!\n");
global_ret = EXIT_FAILURE;
- return;
+ goto exit;
case GNUNET_DB_STATUS_SOFT_ERROR:
/* single call, should not be possible */
GNUNET_break (0);
global_ret = EXIT_FAILURE;
- return;
+ goto exit;
case GNUNET_DB_STATUS_SUCCESS_NO_RESULTS:
GNUNET_log (GNUNET_ERROR_TYPE_INFO,
- "Did not find customization schema `%s'\n",
+ "Nothing to do to disable customization schema `%s'\n",
disable_rules);
break;
case GNUNET_DB_STATUS_SUCCESS_ONE_RESULT:
@@ -201,13 +197,15 @@ run (void *cls,
}
if (NULL != enable_rules)
{
+ enum GNUNET_GenericReturnValue rv;
+
if (0 == strcasecmp (enable_rules,
"exchange"))
{
fprintf (stderr,
"'exchange' is not a customization rule set!\n");
global_ret = EXIT_INVALIDARGUMENT;
- return;
+ goto exit;
}
if (GNUNET_OK !=
plugin->enable_rules (plugin->cls,
@@ -217,9 +215,10 @@ run (void *cls,
"Enabling customization `%s' failed!\n",
enable_rules);
global_ret = EXIT_FAILURE;
- return;
+ goto exit;
}
}
+exit:
TALER_EXCHANGEDB_plugin_unload (plugin);
plugin = NULL;
}
diff --git a/src/exchangedb/0009-statistics.sql b/src/exchangedb/0009-statistics.sql
@@ -131,6 +131,7 @@ BEGIN
',bucket_start INT8 NOT NULL'
',bucket_range statistic_range NOT NULL'
',cumulative_value taler_amount NOT NULL'
+ ',CHECK ((cumulative_value).val IS NOT NULL AND (cumulative_value).frac IS NOT NULL)'
',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)'
') %s;'
,'exchange_statistic_bucket_amount'
@@ -379,6 +380,7 @@ BEGIN
',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
',slot INT8 NOT NULL'
',delta taler_amount NOT NULL'
+ ',CHECK ((delta).val IS NOT NULL AND (delta).frac IS NOT NULL)'
',CONSTRAINT event_key UNIQUE (h_payto,imeta_serial_id,slot)'
') %s ;'
,'exchange_statistic_amount_event'
@@ -457,6 +459,7 @@ BEGIN
',event_delimiter INT8 NOT NULL'
',range INT8 NOT NULL'
',cumulative_value taler_amount NOT NULL'
+ ',CHECK ((cumulative_value).val IS NOT NULL AND (cumulative_value).frac IS NOT NULL)'
',UNIQUE (h_payto,imeta_serial_id,range)'
') %s ;'
,'exchange_statistic_interval_amount'
diff --git a/src/exchangedb/drop.sql b/src/exchangedb/drop.sql
@@ -23,16 +23,15 @@ WITH xpatches AS (
WHERE starts_with(patch_name,'exchange-')
)
SELECT _v.unregister_patch(xpatches.patch_name)
- FROM xpatches;
-
+ FROM xpatches;
WITH xpatches AS (
SELECT patch_name
- FROM _v.patches
- WHERE starts_with(patch_name,'auditor-triggers-')
+ FROM _v.patches
+ WHERE starts_with(patch_name,'auditor-triggers-')
)
SELECT _v.unregister_patch(xpatches.patch_name)
- FROM xpatches;
+ FROM xpatches;
DROP SCHEMA exchange CASCADE;
diff --git a/src/exchangedb/exchange_statistics_helpers.sql b/src/exchangedb/exchange_statistics_helpers.sql
@@ -14,8 +14,6 @@
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
--- FIXME: add helper function to drop data based on new 'origin' field in _meta
-
SET search_path TO exchange;
DROP FUNCTION IF EXISTS interval_to_start;
CREATE OR REPLACE FUNCTION interval_to_start (
@@ -148,7 +146,6 @@ BEGIN
END
WHERE bmeta_serial_id=my_meta
AND h_payto=in_h_payto
- AND curr=(in_delta).curr
AND bucket_start=my_bucket_start
AND bucket_range=my_range;
IF NOT FOUND
@@ -330,28 +327,24 @@ BEGIN
(imeta_serial_id
,h_payto
,slot
- ,delta_curr
- ,delta_value
- ,delta_frac
+ ,delta
) VALUES (
my_meta
,in_h_payto
,my_start
- ,(in_delta).curr
- ,(in_delta).val
- ,(in_delta).frac
+ ,in_delta
)
- ON CONFLICT (imeta_serial_id, h_payto, slot, delta_curr)
+ ON CONFLICT (imeta_serial_id, h_payto, slot)
DO UPDATE SET
- delta_value = msae.delta_value + (in_delta).val
+ delta.val = (msae.delta).val + (in_delta).val
+ CASE
- WHEN (in_delta).frac + msae.delta_frac >= 100000000
+ WHEN (in_delta).frac + (msae.delta).frac >= 100000000
THEN 1
ELSE 0
END,
- delta_frac = msae.delta_frac + (in_delta).frac
+ delta.frac = (msae.delta).frac + (in_delta).frac
- CASE
- WHEN (in_delta).frac + msae.delta_frac >= 100000000
+ WHEN (in_delta).frac + (msae.delta).frac >= 100000000
THEN 100000000
ELSE 0
END
@@ -628,10 +621,10 @@ BEGIN
END IF;
-- Check if we have events that left the applicable range
- SELECT SUM(delta_value) AS value_sum
- ,SUM(delta_frac) AS frac_sum
+ SELECT SUM((esae.delta).val) AS value_sum
+ ,SUM((esae.delta).frac) AS frac_sum
INTO my_jrec
- FROM exchange_statistic_amount_event
+ FROM exchange_statistic_amount_event esae
WHERE imeta_serial_id = my_meta
AND h_payto = in_h_payto
AND slot < my_time - my_range
@@ -1008,27 +1001,34 @@ COMMENT ON PROCEDURE exchange_statistic_bucket_gc
-DROP FUNCTIOn IF EXISTS exchange_drop_customization;
+DROP FUNCTION IF EXISTS exchange_drop_customization;
CREATE OR REPLACE FUNCTION exchange_drop_customization (
IN in_schema TEXT,
OUT out_found BOOLEAN
)
LANGUAGE plpgsql
AS $$
+DECLARE
+ my_xpatches TEXT;
BEGIN
-- Update DB versioning table.
- WITH xpatches AS (
+ out_found = FALSE;
+ FOR my_xpatches IN
SELECT patch_name
- FROM _v.patches
- WHERE starts_with(patch_name, in_schema || '-')
- )
- SELECT _v.unregister_patch(xpatches.patch_name)
- FROM xpatches;
- out_found = FOUND;
-
- -- Drop the schema with all stored procedures/functions.
- -- This also removes all associated triggers, hence CASCADE.
- DROP SCHEMA in_schema CASCADE;
+ FROM _v.patches
+ WHERE starts_with(patch_name, in_schema || '-')
+ LOOP
+ PERFORM _v.unregister_patch(my_xpatches);
+ out_found = TRUE;
+ END LOOP;
+
+ IF out_found
+ THEN
+ -- Drop the schema with all stored procedures/functions.
+ -- This also removes all associated triggers, hence CASCADE.
+ EXECUTE FORMAT('DROP SCHEMA %s CASCADE'
+ ,in_schema);
+ END IF;
-- Finally, need to also remove entries from the statistics meta-tables.
-- Doing so also DELETEs the associated statistics, hence CASCADE.
diff --git a/src/exchangedb/pg_disable_rules.c b/src/exchangedb/pg_disable_rules.c
@@ -38,7 +38,7 @@ TEH_PG_disable_rules (
};
bool found;
struct GNUNET_PQ_ResultSpec rs[] = {
- GNUNET_PQ_result_spec_bool ("found",
+ GNUNET_PQ_result_spec_bool ("out_found",
&found),
GNUNET_PQ_result_spec_end
};
@@ -46,9 +46,8 @@ TEH_PG_disable_rules (
PREPARE (pg,
"call_exchange_drop_customization",
- "SELECT "
- " out_found AS found"
- " FROM exchange_drop_customization"
+ "SELECT out_found"
+ " FROM exchange_drop_customization"
" ($1);");
qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
"call_exchange_drop_customization",
diff --git a/src/exchangedb/pg_enable_rules.c b/src/exchangedb/pg_enable_rules.c
@@ -69,6 +69,8 @@ TEH_PG_enable_rules (
procfile);
/* $SCHEMA-procedures MAY not exist, so only check for hard error */
GNUNET_break (GNUNET_SYSERR != ret);
+ if (GNUNET_NO == ret)
+ ret = GNUNET_OK;
GNUNET_free (procfile);
}
GNUNET_PQ_disconnect (conn);
diff --git a/src/exchangedb/tops-0001.sql b/src/exchangedb/tops-0001.sql
@@ -43,28 +43,56 @@ VALUES
,'deposit-transactions'
,'number of (batch) deposits performed by this merchant, used to detect sudden increase in number of transactions'
,'number'
- ,generate_series (60*60*24*7, 60*60*24*7*52, 60*60*24*7) -- weekly volume over the last year
- ,array_fill (60*60*24, 52) -- precision is per day
+ ,ARRAY(SELECT generate_series (60*60*24*7, 60*60*24*7*52, 60*60*24*7)) -- weekly volume over the last year
+ ,array_fill (60*60*24, ARRAY[52]) -- precision is per day
),
('tops' -- must match schema!
,'deposit-volume'
- ,'total amount deposited by this merchant in (batch) deposits, used to detect sudden increase in transaction volume'
+ ,'total amount deposited by this merchant in (batch) deposits including deposit fees, used to detect sudden increase in transaction volume'
,'amount'
- ,generate_series (60*60*24*7, 60*60*24*7*52, 60*60*24*7) -- weekly volume over the last year
- ,array_fill (60*60*24, 52) -- precision is per day
+ ,ARRAY(SELECT generate_series (60*60*24*7, 60*60*24*7*52, 60*60*24*7)) -- weekly volume over the last year
+ ,array_fill (60*60*24, ARRAY[52]) -- precision is per day
)
ON CONFLICT DO NOTHING;
-DROP FUNCTION IF EXISTS exchange_deposit_statistics_trigger CASCADE;
-CREATE FUNCTION exchange_deposit_statistics_trigger()
+INSERT INTO exchange_statistic_bucket_meta
+ (origin
+ ,slug
+ ,description
+ ,stype
+ ,ranges
+ ,ages)
+VALUES
+-- this first one is just for testing right now
+ ('tops' -- must match schema!
+ ,'deposit-transactions'
+ ,'number of (batch) deposits performed by this merchant, used to detect sudden increase in number of transactions'
+ ,'number'
+ ,ARRAY['day'::statistic_range,'week']
+ ,ARRAY[5,5]
+ ),
+ ('tops' -- must match schema!
+ ,'deposit-volume'
+ ,'total amount deposited by this merchant in (batch) deposits including deposit fees, used to detect sudden increase in transaction volume'
+ ,'amount'
+ ,ARRAY['day'::statistic_range,'week']
+ ,ARRAY[5,5]
+ )
+ON CONFLICT DO NOTHING;
+
+DROP FUNCTION IF EXISTS tops_deposit_statistics_trigger CASCADE;
+CREATE FUNCTION tops_deposit_statistics_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
my_h_payto BYTEA; -- normalized h_payto of target account
my_rec RECORD;
- my_last_year taler_amount;
- my_last_month taler_amount;
+ my_vsum INT8;
+ my_fsum INT8;
+ my_batch_amount taler_amount; -- sum of all coins (incl. fees)
+ my_last_year taler_amount; -- sum of deposits this year
+ my_last_month taler_amount; -- sum of deposits this month
my_old_rules RECORD;
my_properties TEXT;
my_measure_name TEXT;
@@ -74,22 +102,45 @@ BEGIN
INTO my_h_payto
FROM wire_targets wt
WHERE wire_target_h_payto = NEW.wire_target_h_payto;
--- FIXME: we're hard-coding an amount for testing; the
--- final logic will be written once we have for Oec's
--- unified deposit table patch, that'll make this WAY simpler
--- as we should have the amount without having to iterate.
+
+ -- FIXME: this does not work, coin_deposits is only initialized LATER
+ -- in exchange_do_deposit.sql;
+ -- Solution: add a new column with the sum?
+ FOR my_rec IN
+ SELECT amount_with_fee
+ FROM coin_deposits cdeps
+ WHERE batch_deposit_serial_id = NEW.batch_deposit_serial_id
+ LOOP
+ RAISE NOTICE 'XXXX has %', my_rec;
+ END LOOP;
+ SELECT SUM((cdeps.amount_with_fee).val) AS vsum,
+ SUM((cdeps.amount_with_fee).frac) AS fsum
+ INTO my_rec
+ FROM coin_deposits cdeps
+ WHERE batch_deposit_serial_id = NEW.batch_deposit_serial_id;
+ IF NOT FOUND
+ THEN
+ RAISE NOTICE 'Invariant failed, no coin deposits for batch deposit';
+ RETURN NEW;
+ END IF;
+ RAISE NOTICE 'XXXZ SELECT yielded % - %',FOUND,my_rec;
+ my_vsum = my_rec.vsum;
+ my_fsum = my_rec.fsum;
+ RAISE NOTICE 'XXXY Batch deposit of %.%',my_vsum,my_fsum;
+ my_batch_amount.val = my_vsum + my_fsum / 100000000;
+ my_batch_amount.frac = my_fsum % 100000000;
CALL exchange_do_bump_amount_stat
('deposit-volume'
- ,h_payto
- ,CURRENT_TIMESTAMP(0)
- ,(1,1):taler_amount) -- should be: NEW.amount_with_fee);
+ ,my_h_payto
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,my_batch_amount);
-- FIXME: this is just for testing, I want to also check
-- the 'counter'-based functions.
- CALL exchange_do_bump_amount_stat
+ CALL exchange_do_bump_number_stat
('deposit-transactions'
- ,h_payto
- ,CURRENT_TIMESTAMP(0)
+ ,my_h_payto
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
,1);
-- Get historical deposit volumes and extract the yearly and monthly
@@ -103,19 +154,19 @@ BEGIN
LOOP
IF (my_rec.range = 60*60*24*7*52)
THEN
- last_year = my_rec.rvalue;
+ my_last_year = my_rec.rvalue;
END IF;
IF (my_rec.range = 60*60*24*7*4)
THEN
- last_month = my_rec.rvalue;
+ my_last_month = my_rec.rvalue;
END IF;
- END LOOP
+ END LOOP;
-- Note: it is OK to ignore '.frac', as that cannot be significant.
-- Also, we effectively exclude the current month's revenue from
-- "last year" as otherwise the rule makes no sense.
-- Finally, we define the "current month" always as the last 4 weeks,
-- just like the "last year" is the last 52 weeks.
- IF last_year.val < last_month.val * 2
+ IF (my_last_year.val < my_last_month.val * 2)
THEN
-- This is suspicious. => Flag account for AML review!
--
@@ -167,7 +218,7 @@ BEGIN
,is_active
,jnew_rules
) VALUES (
- ,my_h_payto
+ my_h_payto
,my_now
,my_now + 366*24*60*60
,my_properties
@@ -180,14 +231,14 @@ BEGIN
END IF;
RETURN NEW;
END $$;
-COMMENT ON FUNCTION exchange_deposit_statistics_trigger
+COMMENT ON FUNCTION tops_deposit_statistics_trigger
IS 'creates deposit statistics';
-- Whenever a deposit is made, call our trigger to bump statistics
-CREATE TRIGGER exchange_batch_deposits_on_insert
+CREATE TRIGGER tops_batch_deposits_on_insert
AFTER INSERT
ON batch_deposits
- FOR EACH ROW EXECUTE FUNCTION exchange_deposit_statistics_trigger();
+ FOR EACH ROW EXECUTE FUNCTION tops_deposit_statistics_trigger();
diff --git a/src/testing/taler-unified-setup.sh b/src/testing/taler-unified-setup.sh
@@ -509,7 +509,10 @@ then
echo -n " patching master_pub ($MASTER_PUB)..."
taler-exchange-config -c "$CONF" -s exchange -o MASTER_PUBLIC_KEY -V "$MASTER_PUB"
fi
- taler-exchange-dbinit -c "$CONF" --reset
+ taler-exchange-dbinit \
+ -c "$CONF" \
+ --reset
+# -e tops
$USE_VALGRIND taler-exchange-secmod-eddsa \
-c "$CONF" \
-L "$LOGLEVEL" \
