pg_statistics_examples.sql (6851B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- @file pg_statistics_example.sql 18 -- @brief example/test case for how to add statistics to the merchant backend, NOT for production! 19 -- @author Christian Grothoff 20 21 -- Everything in one big transaction 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 -- SELECT _v.register_patch('example-statistics-0001', NULL, NULL); 26 27 CREATE SCHEMA IF NOT EXISTS example_statistics; 28 29 SET search_path TO example_statistics,merchant; 30 31 32 -- Setup statistic: what do we want to track for 'deposits'? 33 -- (Note: this is basically the one "manual" step we might not keep hard-coded) 34 INSERT INTO merchant_statistic_bucket_meta 35 (slug 36 ,description 37 ,stype 38 ,ranges 39 ,ages) 40 VALUES 41 ('deposits' 42 ,'sales (before refunds)' 43 ,'amount' 44 ,ARRAY['second'::statistic_range, 'minute', 'day', 'month', 'quarter', 'year'] 45 ,ARRAY[120, 120, 95, 36, 40, 100] -- track last 120 s, 120 minutes, 95 days, 36 months, 40 quarters & 100 years 46 ) 47 ON CONFLICT DO NOTHING; 48 49 INSERT INTO merchant_statistic_interval_meta 50 (slug 51 ,description 52 ,stype 53 ,ranges 54 ,precisions) 55 VALUES 56 ('deposits' 57 ,'sales (before refunds)' 58 ,'amount' 59 ,ARRAY(SELECT generate_series (1, 10, 1)) || ARRAY(SELECT generate_series (60, 180, 60)), 60 ,array_fill (1, ARRAY[10]) || array_fill (5, ARRAY[3]) 61 ), 62 ('products-sold' 63 ,'number of products sold' 64 ,'number' 65 ,ARRAY(SELECT generate_series (1, 10, 1)) || [60], 66 ,array_fill (1, ARRAY[10]) || [10] 67 ) 68 ON CONFLICT DO NOTHING; 69 70 DROP FUNCTION IF EXISTS merchant_deposits_statistics_trigger CASCADE; 71 CREATE FUNCTION merchant_deposits_statistics_trigger() 72 RETURNS trigger 73 LANGUAGE plpgsql 74 AS $$ 75 DECLARE 76 my_instance INT8; 77 BEGIN 78 -- SET search_path TO merchant; 79 SELECT mct.merchant_serial 80 INTO my_instance 81 FROM merchant_contract_terms mct 82 JOIN merchant_deposit_confirmations mdc 83 USING (order_serial) 84 WHERE mdc.deposit_confirmation_serial = NEW.deposit_confirmation_serial; 85 CALL merchant_do_bump_amount_stat 86 ('deposits' 87 ,my_instance 88 ,CURRENT_TIMESTAMP(0) 89 ,NEW.amount_with_fee); 90 RETURN NEW; 91 END $$; 92 COMMENT ON FUNCTION merchant_deposits_statistics_trigger 93 IS 'adds the deposited amount to the deposit statistics'; 94 95 DO $$ 96 DECLARE 97 rec RECORD; 98 BEGIN 99 FOR rec IN 100 SELECT 'deposits' AS in_slug 101 ,mct.merchant_serial AS in_merchant_serial 102 ,TO_TIMESTAMP (mdc.deposit_timestamp / 1000.0 / 1000.0)::TIMESTAMP AS in_timestamp 103 ,mdc.total_without_fee AS in_delta 104 FROM merchant_deposit_confirmations mdc 105 JOIN merchant_contract_terms mct 106 USING (order_serial) 107 WHERE mdc.deposit_timestamp > (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)) - 365*24*60*60) * 1000000 108 LOOP 109 CALL merchant_do_bump_amount_stat (rec.in_slug, rec.in_merchant_serial, rec.in_timestamp, rec.in_delta); 110 END LOOP; 111 END $$; 112 113 -- Whenever a deposit is made, call our trigger to bump statistics 114 CREATE TRIGGER merchant_deposits_on_insert 115 AFTER INSERT 116 ON merchant_deposits 117 FOR EACH ROW EXECUTE FUNCTION merchant_deposits_statistics_trigger(); 118 119 120 121 122 -- Setup statistic 123 -- (Note: this is basically the one "manual" step we might not keep hard-coded) 124 INSERT INTO merchant_statistic_bucket_meta 125 (slug 126 ,description 127 ,stype 128 ,ranges 129 ,ages) 130 VALUES 131 ('products-sold' 132 ,'products sold (only those tracked in inventory)' 133 ,'number' 134 ,ARRAY['second'::statistic_range, 'minute', 'day', 'week', 'month', 'quarter', 'year'] 135 ,ARRAY[120, 120, 60, 12, 24, 8, 10] -- track last 120s, 120 minutes, 60 days, 12 weeks, 24 months, 8 quarters and 10 years 136 ) 137 ON CONFLICT DO NOTHING; 138 139 DROP FUNCTION IF EXISTS merchant_products_sold_statistics_trigger CASCADE; 140 CREATE FUNCTION merchant_products_sold_statistics_trigger() 141 RETURNS trigger 142 LANGUAGE plpgsql 143 AS $$ 144 DECLARE 145 my_sold INT8; 146 BEGIN 147 -- SET search_path TO merchant; 148 my_sold = NEW.total_sold - OLD.total_sold; 149 IF (0 < my_sold) 150 THEN 151 CALL merchant_do_bump_number_stat 152 ('products-sold' 153 ,NEW.merchant_serial 154 ,CURRENT_TIMESTAMP(0) 155 ,my_sold); 156 END IF; 157 RETURN NEW; 158 END $$; 159 160 -- Whenever inventory changes, call our trigger to bump statistics 161 CREATE TRIGGER merchant_products_on_sold 162 AFTER UPDATE 163 ON merchant_inventory 164 FOR EACH ROW EXECUTE FUNCTION merchant_products_sold_statistics_trigger(); 165 166 delete from merchant.merchant_statistic_bucket_counter ; 167 delete from merchant.merchant_statistic_bucket_amount ; 168 delete from merchant.merchant_statistic_interval_counter; 169 delete from merchant.merchant_statistic_interval_amount; 170 delete from merchant.merchant_statistic_amount_event; 171 delete from merchant.merchant_statistic_counter_event; 172 173 174 call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 minutes', 1); 175 call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 minutes', (1,1,'EUR')::taler_amount_currency); 176 call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 seconds', 2); 177 call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '1 minute', (2,2,'EUR')::taler_amount_currency); 178 call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '2 seconds', (4,4,'EUR')::taler_amount_currency); 179 call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP-INTERVAL '1 second', (8,8,'EUR')::taler_amount_currency); 180 call merchant_do_bump_number_stat ('products-sold'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, 4); 181 call merchant_do_bump_amount_stat ('deposits'::text, 6, CURRENT_TIMESTAMP(0)::TIMESTAMP, (16,16,'EUR')::taler_amount_currency); 182 183 select * from merchant_statistic_interval_number_get ('products-sold', 'default'); 184 185 select * from merchant_statistic_interval_amount_get ('deposits', 'default'); 186 187 select * from merchant.merchant_statistic_amount_event; 188 189 select * from merchant.merchant_statistic_counter_event; 190 191 select * from merchant.merchant_statistic_interval_counter; 192 193 select * from merchant.merchant_statistic_interval_amount; 194 195 select * from merchant.merchant_statistic_bucket_counter ; 196 197 select * from merchant.merchant_statistic_bucket_amount ; 198 199 -- ROLLBACK; 200 COMMIT;