commit 937440b96e4b577bab420261b562ba0875362467
parent 921569827c0c44581ddb9ce7ddeb68c263f109e9
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Sun, 23 Mar 2025 15:42:42 +0100
implement statistics for #9613
Diffstat:
3 files changed, 329 insertions(+), 16 deletions(-)
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql
@@ -408,6 +408,14 @@ COMMENT ON TABLE merchant_deposit_confirmations
IS 'Table with the deposit confirmations for each coin we deposited at the exchange';
COMMENT ON COLUMN merchant_deposit_confirmations.signkey_serial
IS 'Online signing key of the exchange on the deposit confirmation';
+COMMENT ON COLUMN merchant_deposit_confirmations.account_serial
+ IS 'Identifies the bank account of the merchant that will receive the payment';
+COMMENT ON COLUMN merchant_deposit_confirmations.exchange_url
+ IS 'Which exchange received the deposit; a single contract may be paid by multiple (batch) deposits to multiple exchanges';
+COMMENT ON COLUMN merchant_deposit_confirmations.order_serial
+ IS 'Identifies the entry in the contract_terms table that was paid by this deposit';
+COMMENT ON COLUMN merchant_deposit_confirmations.total_without_fee
+ IS 'Total amount that was (batch) deposited, excluding deposit fees (irrespective of who had to pay the fees); so this is the amount the merchant will be credited for this deposit (minus applicable wire fees after aggregation)';
COMMENT ON COLUMN merchant_deposit_confirmations.deposit_timestamp
IS 'Time when the exchange generated the deposit confirmation';
COMMENT ON COLUMN merchant_deposit_confirmations.exchange_sig
diff --git a/src/backenddb/merchant-0014.sql b/src/backenddb/merchant-0014.sql
@@ -269,4 +269,321 @@ CREATE TYPE merchant_statistic_interval_amount_get_return_value
COMMENT ON TYPE merchant_statistic_interval_amount_get_return_value
IS 'Return type for merchant_statistic_interval_amount_get stored procedure';
+-- ---------------- Actual statistics below ---------------------
+
+
+CREATE FUNCTION merchant_orders_insert_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_stat
+ ('orders-created'
+ ,NEW.merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+-- Whenever an order is created, call our trigger to bump statistics
+CREATE TRIGGER merchant_orders_on_insert_statistic
+ AFTER INSERT
+ ON merchant_orders
+ FOR EACH ROW EXECUTE FUNCTION merchant_orders_insert_statistics_trigger();
+
+
+CREATE FUNCTION merchant_contract_terms_insert_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL merchant_do_bump_number_stat
+ ('orders-claimed'
+ ,NEW.merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+-- Whenever an order is created, call our trigger to bump statistics
+CREATE TRIGGER merchant_contract_terms_on_insert_statistic
+ AFTER INSERT
+ ON merchant_contract_terms
+ FOR EACH ROW EXECUTE FUNCTION merchant_contract_terms_insert_statistics_trigger();
+
+
+CREATE FUNCTION merchant_contract_terms_update_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_rec RECORD;
+BEGIN
+ IF (NEW.wired AND NOT OLD.wired)
+ THEN
+ CALL merchant_do_bump_number_stat
+ ('orders-settled'
+ ,NEW.merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ END IF;
+ IF (NEW.paid AND NOT OLD.paid)
+ THEN
+ CALL merchant_do_bump_number_stat
+ ('orders-paid'
+ ,NEW.merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ FOR my_rec IN
+ SELECT total_without_fee
+ FROM merchant_deposit_confirmations
+ WHERE order_serial = NEW.order_serial
+ LOOP
+ CALL merchant_do_bump_amount_stat
+ ('payments-received-after-deposit-fee'
+ ,NEW.merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,my_rec.total_without_fee);
+ END LOOP;
+ FOR my_rec IN
+ SELECT deposit_fee
+ FROM merchant_deposits
+ WHERE deposit_confirmation_serial IN
+ (SELECT deposit_confirmation_serial
+ FROM merchant_deposit_confirmations
+ WHERE order_serial = NEW.order_serial)
+ LOOP
+ CALL merchant_do_bump_amount_stat
+ ('total-deposit-fees-paid'
+ ,NEW.merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,my_rec.deposit_fee);
+ END LOOP;
+ END IF;
+ RETURN NEW;
+END $$;
+
+-- Whenever a contract is updated, call our trigger to bump statistics
+CREATE TRIGGER merchant_contract_terms_on_update_statistic
+ AFTER UPDATE
+ ON merchant_contract_terms
+ FOR EACH ROW EXECUTE FUNCTION merchant_contract_terms_update_statistics_trigger();
+
+
+CREATE FUNCTION merchant_refunds_insert_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_merchant_serial INT8;
+BEGIN
+ SELECT merchant_serial
+ INTO my_merchant_serial
+ FROM merchant_contract_terms
+ WHERE order_serial = NEW.order_serial;
+ CALL merchant_do_bump_amount_stat
+ ('refunds-granted'
+ ,my_merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.refund_amount);
+ RETURN NEW;
+END $$;
+
+-- Whenever a refund is granted created, call our trigger to bump statistics
+CREATE TRIGGER merchant_refunds_on_insert_statistic
+ AFTER INSERT
+ ON merchant_refunds
+ FOR EACH ROW EXECUTE FUNCTION merchant_refunds_insert_statistics_trigger();
+
+
+CREATE FUNCTION merchant_transfer_signatures_insert_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_merchant_serial INT8;
+BEGIN
+ SELECT merchant_serial
+ INTO my_merchant_serial
+ FROM merchant_accounts
+ WHERE account_serial =
+ (SELECT account_serial
+ FROM merchant_transfers
+ WHERE credit_serial = NEW.credit_serial);
+ CALL merchant_do_bump_amount_stat
+ ('wire-fees-paid'
+ ,my_merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,NEW.wire_fee);
+ RETURN NEW;
+END $$;
+
+-- Whenever a refund is granted created, call our trigger to bump statistics
+CREATE TRIGGER merchant_transfer_signatures_on_insert_statistic
+ AFTER INSERT
+ ON merchant_transfer_signatures
+ FOR EACH ROW EXECUTE FUNCTION merchant_transfer_signatures_insert_statistics_trigger();
+
+
+CREATE FUNCTION merchant_issued_tokens_insert_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_merchant_serial INT8;
+BEGIN
+ SELECT merchant_serial
+ INTO my_merchant_serial
+ FROM merchant_token_families
+ WHERE token_family_serial =
+ (SELECT token_family_serial
+ FROM merchant_token_family_keys
+ WHERE token_family_key_serial = NEW.token_family_key_serial);
+ CALL merchant_do_bump_number_stat
+ ('tokens-issued'
+ ,my_merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+-- Whenever a token is issued, call our trigger to bump statistics
+CREATE TRIGGER merchant_issued_tokens_on_insert_statistic
+ AFTER INSERT
+ ON merchant_issued_tokens
+ FOR EACH ROW EXECUTE FUNCTION merchant_issued_tokens_insert_statistics_trigger();
+
+
+CREATE FUNCTION merchant_used_tokens_insert_statistics_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_merchant_serial INT8;
+BEGIN
+ SELECT merchant_serial
+ INTO my_merchant_serial
+ FROM merchant_token_families
+ WHERE token_family_serial =
+ (SELECT token_family_serial
+ FROM merchant_token_family_keys
+ WHERE token_family_key_serial = NEW.token_family_key_serial);
+ CALL merchant_do_bump_number_stat
+ ('tokens-used'
+ ,my_merchant_serial
+ ,CURRENT_TIMESTAMP(0)::TIMESTAMP
+ ,1);
+ RETURN NEW;
+END $$;
+
+-- Whenever a token is used, call our trigger to bump statistics
+CREATE TRIGGER merchant_used_tokens_on_insert_statistic
+ AFTER INSERT
+ ON merchant_used_tokens
+ FOR EACH ROW EXECUTE FUNCTION merchant_used_tokens_insert_statistics_trigger();
+
+-- Enable interval statistics
+INSERT INTO merchant_statistic_interval_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,precisions)
+VALUES
+ ('orders-created'
+ ,'number of orders created (but not necessarily claimed by wallets)'
+ ,'number'
+ ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute
+ || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour
+ ,array_fill (5, ARRAY[60]) -- precision: 5s
+ || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes
+ ),
+ ('orders-claimed'
+ ,'number of orders claimed by a wallet (but not necessarily paid)'
+ ,'number'
+ ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute
+ || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour
+ ,array_fill (5, ARRAY[60]) -- precision: 5s
+ || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes
+ ),
+ ('orders-paid'
+ ,'number of orders paid (but not necessarily settled by the exchange)'
+ ,'number'
+ ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute
+ || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour
+ ,array_fill (5, ARRAY[60]) -- precision: 5s
+ || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes
+ ),
+ ('orders-settled'
+ ,'number of orders settled'
+ ,'number'
+ ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute
+ || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour
+ ,array_fill (5, ARRAY[60]) -- precision: 5s
+ || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes
+ ),
+ ('tokens-issued'
+ ,'number of tokens issued to customers'
+ ,'number'
+ ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute
+ || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour
+ ,array_fill (5, ARRAY[60]) -- precision: 5s
+ || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes
+ ),
+ ('tokens-used'
+ ,'number of tokens used by customers'
+ ,'number'
+ ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute
+ || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour
+ ,array_fill (5, ARRAY[60]) -- precision: 5s
+ || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes
+ );
+
+-- Enable bucket statistics
+INSERT INTO merchant_statistic_bucket_meta
+ (slug
+ ,description
+ ,stype
+ ,ranges
+ ,ages)
+VALUES
+ ('payments-received-after-deposit-fee'
+ ,'amount customers paid to us (excluded deposit fees paid by us or customers, wire fees are still deducted by the exchange)'
+ ,'amount'
+ ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year']
+ ,ARRAY[72, 14, 12, 24, 12, 10]
+ ),
+ ('total-deposit-fees-paid'
+ ,'deposit fees we or our customers paid to the exchange (includes those waived on refunds)'
+ ,'amount'
+ ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year']
+ ,ARRAY[72, 14, 12, 24, 12, 10]
+ ),
+ ('total-wire-fees-paid'
+ ,'wire fees we paid to the exchange'
+ ,'amount'
+ ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year']
+ ,ARRAY[72, 12, 12, 24, 12, 10]
+ ),
+ ('refunds-granted'
+ ,'refunds granted by us to our customers'
+ ,'amount'
+ ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year']
+ ,ARRAY[72, 14, 12, 24, 12, 10]
+ ),
+ ('tokens-issued'
+ ,'number of tokens issued to customers'
+ ,'number'
+ ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year']
+ ,ARRAY[72, 14, 12, 24, 12, 10]
+ ),
+ ('tokens-used'
+ ,'number of tokens used by customers'
+ ,'number'
+ ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year']
+ ,ARRAY[72, 14, 12, 24, 12, 10]
+ );
+
+
+
COMMIT;
diff --git a/src/backenddb/pg_statistics_examples.sql b/src/backenddb/pg_statistics_examples.sql
@@ -56,26 +56,14 @@ VALUES
('deposits'
,'sales (before refunds)'
,'amount'
- ,ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
- 60, 120 -- , 180, 240, 300,
--- 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60
-] -- second, minute, day, month, year
- ,ARRAY[1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
- 10, 10 --, 5, 5, 5,
--- 60, 60 * 60, 24 * 60 * 60
- ]
+ ,generate_series (1, 10, 1) || generate_series (60, 180, 60),
+ ,array_fill (1, 10) || array_fill (5, 3)]
),
('products-sold'
,'number of products sold'
,'number'
- ,ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
- 60 -- , 120, 180, 240, 300,
--- 24 * 60 * 60, 30 * 24 * 60 * 60, 365 * 24 * 60 * 60
-] -- second, minute, day, month, year
- ,ARRAY[1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
- 10 --, 5, 5, 5, 5,
--- 60, 60 * 60, 24 * 60 * 60
- ]
+ ,generate_series (1, 10, 1) || [60],
+ ,array_fill (1, 10) || [10]
)
ON CONFLICT DO NOTHING;
