0002-statistics.sql (19305B)
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 -- Ranges given here must be supported by the date_trunc function of Postgresql! 18 CREATE TYPE statistic_range AS 19 ENUM('century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'); 20 21 CREATE TYPE statistic_type AS 22 ENUM('amount', 'number'); 23 24 -- -------------- Bucket statistics --------------------- 25 26 CREATE TABLE exchange_statistic_bucket_meta 27 (bmeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 28 ,origin TEXT NOT NULL 29 ,slug TEXT NOT NULL 30 ,description TEXT NOT NULL 31 ,stype statistic_type NOT NULL 32 ,ranges statistic_range[] NOT NULL 33 ,ages INT4[] NOT NULL 34 ,UNIQUE(slug,stype) 35 ,CONSTRAINT equal_array_length 36 CHECK (array_length(ranges,1) = 37 array_length(ages,1)) 38 ); 39 COMMENT ON TABLE exchange_statistic_bucket_meta 40 IS 'meta data about a statistic with events falling into buckets we are tracking'; 41 COMMENT ON COLUMN exchange_statistic_bucket_meta.bmeta_serial_id 42 IS 'unique identifier for this type of bucket statistic we are tracking'; 43 COMMENT ON COLUMN exchange_statistic_bucket_meta.origin 44 IS 'which customization schema does this statistic originate from (used for easy deletion)'; 45 COMMENT ON COLUMN exchange_statistic_bucket_meta.slug 46 IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path'; 47 COMMENT ON COLUMN exchange_statistic_bucket_meta.description 48 IS 'description of the statistic being tracked'; 49 COMMENT ON COLUMN exchange_statistic_bucket_meta.stype 50 IS 'statistic type, what kind of data is being tracked, amount or number'; 51 COMMENT ON COLUMN exchange_statistic_bucket_meta.ranges 52 IS 'size of the buckets that are being kept for this statistic'; 53 COMMENT ON COLUMN exchange_statistic_bucket_meta.ages 54 IS 'determines how long into the past we keep buckets for the range at the given index around (in generations)'; 55 CREATE INDEX exchange_statistic_bucket_meta_by_origin 56 ON exchange_statistic_bucket_meta 57 (origin); 58 59 60 CREATE FUNCTION create_table_exchange_statistic_bucket_counter ( 61 IN partition_suffix TEXT DEFAULT NULL 62 ) 63 RETURNS VOID 64 LANGUAGE plpgsql 65 AS $$ 66 BEGIN 67 PERFORM create_partitioned_table ( 68 'CREATE TABLE %I' 69 '(bmeta_serial_id INT8 NOT NULL' 70 ' REFERENCES exchange_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE' 71 ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' 72 ',bucket_start INT8 NOT NULL' 73 ',bucket_range statistic_range NOT NULL' 74 ',cumulative_number INT8 NOT NULL' 75 ',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)' 76 ') %s;' 77 ,'exchange_statistic_bucket_counter' 78 ,'PARTITION BY HASH (h_payto)' 79 ,partition_suffix 80 ); 81 PERFORM comment_partitioned_table( 82 'various numeric statistics (cumulative counters) being tracked by bucket into which they fall' 83 ,'exchange_statistic_bucket_counter' 84 ,partition_suffix 85 ); 86 PERFORM comment_partitioned_column( 87 'identifies what the statistic is about' 88 ,'bmeta_serial_id' 89 ,'exchange_statistic_bucket_counter' 90 ,partition_suffix 91 ); 92 PERFORM comment_partitioned_column( 93 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics' 94 ,'h_payto' 95 ,'exchange_statistic_bucket_counter' 96 ,partition_suffix 97 ); 98 PERFORM comment_partitioned_column( 99 'start date for the bucket in seconds since the epoch' 100 ,'bucket_start' 101 ,'exchange_statistic_bucket_counter' 102 ,partition_suffix 103 ); 104 PERFORM comment_partitioned_column( 105 'range of the bucket' 106 ,'bucket_range' 107 ,'exchange_statistic_bucket_counter' 108 ,partition_suffix 109 ); 110 PERFORM comment_partitioned_column( 111 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword' 112 ,'cumulative_number' 113 ,'exchange_statistic_bucket_counter' 114 ,partition_suffix 115 ); 116 END $$; 117 118 119 CREATE FUNCTION create_table_exchange_statistic_bucket_amount ( 120 IN partition_suffix TEXT DEFAULT NULL 121 ) 122 RETURNS VOID 123 LANGUAGE plpgsql 124 AS $$ 125 BEGIN 126 PERFORM create_partitioned_table ( 127 'CREATE TABLE %I' 128 '(bmeta_serial_id INT8 NOT NULL' 129 ' REFERENCES exchange_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE' 130 ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' 131 ',bucket_start INT8 NOT NULL' 132 ',bucket_range statistic_range NOT NULL' 133 ',cumulative_value taler_amount NOT NULL' 134 ',CHECK ((cumulative_value).val IS NOT NULL AND (cumulative_value).frac IS NOT NULL)' 135 ',UNIQUE (h_payto,bmeta_serial_id,bucket_start,bucket_range)' 136 ') %s;' 137 ,'exchange_statistic_bucket_amount' 138 ,'PARTITION BY HASH(h_payto)' 139 ,partition_suffix 140 ); 141 PERFORM comment_partitioned_table ( 142 'various amount statistics being tracked' 143 ,'exchange_statistic_bucket_amount' 144 ,partition_suffix 145 ); 146 PERFORM comment_partitioned_column( 147 'identifies what the statistic is about' 148 ,'bmeta_serial_id' 149 ,'exchange_statistic_bucket_amount' 150 ,partition_suffix 151 ); 152 PERFORM comment_partitioned_column ( 153 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics' 154 ,'h_payto' 155 ,'exchange_statistic_bucket_amount' 156 ,partition_suffix 157 ); 158 PERFORM comment_partitioned_column( 159 'start date for the bucket in seconds since the epoch' 160 ,'bucket_start' 161 ,'exchange_statistic_bucket_amount' 162 ,partition_suffix 163 ); 164 PERFORM comment_partitioned_column( 165 'range of the bucket' 166 ,'bucket_range' 167 ,'exchange_statistic_bucket_amount' 168 ,partition_suffix 169 ); 170 PERFORM comment_partitioned_column( 171 'amount being tracked' 172 ,'cumulative_value' 173 ,'exchange_statistic_bucket_amount' 174 ,partition_suffix 175 ); 176 END $$; 177 178 179 -- -------------- Interval statistics --------------------- 180 181 182 CREATE TABLE exchange_statistic_interval_meta 183 (imeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 184 ,origin TEXT NOT NULL 185 ,slug TEXT NOT NULL 186 ,description TEXT NOT NULL 187 ,stype statistic_type NOT NULL 188 ,ranges INT8[] NOT NULL CHECK (array_length(ranges,1) > 0) 189 ,precisions INT8[] NOT NULL CHECK (array_length(precisions,1) > 0) 190 ,UNIQUE(slug,stype) 191 ,CONSTRAINT equal_array_length 192 CHECK (array_length(ranges,1) = 193 array_length(precisions,1)) 194 ); 195 COMMENT ON TABLE exchange_statistic_interval_meta 196 IS 'meta data about an interval statistic we are tracking'; 197 COMMENT ON COLUMN exchange_statistic_interval_meta.imeta_serial_id 198 IS 'unique identifier for this type of interval statistic we are tracking'; 199 COMMENT ON COLUMN exchange_statistic_interval_meta.origin 200 IS 'which customization schema does this statistic originate from (used for easy deletion)'; 201 COMMENT ON COLUMN exchange_statistic_interval_meta.slug 202 IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path'; 203 COMMENT ON COLUMN exchange_statistic_interval_meta.description 204 IS 'description of the statistic being tracked'; 205 COMMENT ON COLUMN exchange_statistic_interval_meta.stype 206 IS 'statistic type, what kind of data is being tracked, amount or number'; 207 COMMENT ON COLUMN exchange_statistic_interval_meta.ranges 208 IS 'range of values that is being kept for this statistic, in seconds, must be monotonically increasing'; 209 COMMENT ON COLUMN exchange_statistic_interval_meta.precisions 210 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'; 211 CREATE INDEX exchange_statistic_interval_meta_by_origin 212 ON exchange_statistic_interval_meta 213 (origin); 214 215 216 CREATE FUNCTION create_table_exchange_statistic_counter_event ( 217 IN partition_suffix TEXT DEFAULT NULL 218 ) 219 RETURNS VOID 220 LANGUAGE plpgsql 221 AS $$ 222 BEGIN 223 PERFORM create_partitioned_table( 224 'CREATE TABLE %I' 225 '(nevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY' 226 ',imeta_serial_id INT8' 227 ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE' 228 ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' 229 ',slot INT8 NOT NULL' 230 ',delta INT8 NOT NULL' 231 ',UNIQUE (h_payto,imeta_serial_id,slot)' 232 ') %s ;' 233 ,'exchange_statistic_counter_event' 234 ,'PARTITION BY HASH(h_payto)' 235 ,partition_suffix 236 ); 237 PERFORM comment_partitioned_table( 238 'number to decrement an interval statistic by when a certain time value is reached' 239 ,'exchange_statistic_counter_event' 240 ,partition_suffix 241 ); 242 PERFORM comment_partitioned_column( 243 'unique identifier for this number event' 244 ,'nevent_serial_id' 245 ,'exchange_statistic_counter_event' 246 ,partition_suffix 247 ); 248 PERFORM comment_partitioned_column( 249 'identifies what the statistic is about; must be of stype number' 250 ,'imeta_serial_id' 251 ,'exchange_statistic_counter_event' 252 ,partition_suffix 253 ); 254 PERFORM comment_partitioned_column( 255 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics' 256 ,'h_payto' 257 ,'exchange_statistic_counter_event' 258 ,partition_suffix 259 ); 260 PERFORM comment_partitioned_column( 261 'identifies the time slot at which the given event(s) happened, rounded down by the respective precisions value' 262 ,'slot' 263 ,'exchange_statistic_counter_event' 264 ,partition_suffix 265 ); 266 PERFORM comment_partitioned_column( 267 'total cumulative number that was added at the time identified by slot' 268 ,'delta' 269 ,'exchange_statistic_counter_event' 270 ,partition_suffix 271 ); 272 END $$; 273 274 275 CREATE FUNCTION constrain_table_exchange_statistic_counter_event( 276 IN partition_suffix TEXT 277 ) 278 RETURNS void 279 LANGUAGE plpgsql 280 AS $$ 281 DECLARE 282 table_name TEXT default 'exchange_statistic_counter_event'; 283 BEGIN 284 table_name = concat_ws('_', table_name, partition_suffix); 285 EXECUTE FORMAT ( 286 'ALTER TABLE ' || table_name || 287 ' ADD CONSTRAINT ' || table_name || '_nevent_serial_id_key' 288 ' UNIQUE (nevent_serial_id)' 289 ); 290 END $$; 291 292 293 CREATE FUNCTION create_table_exchange_statistic_interval_counter ( 294 IN partition_suffix TEXT DEFAULT NULL 295 ) 296 RETURNS VOID 297 LANGUAGE plpgsql 298 AS $$ 299 BEGIN 300 PERFORM create_partitioned_table( 301 'CREATE TABLE %I' 302 '(imeta_serial_id INT8 NOT NULL' 303 ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE' 304 ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' 305 ',range INT8 NOT NULL' 306 ',event_delimiter INT8 NOT NULL' 307 ',cumulative_number INT8 NOT NULL' 308 ',UNIQUE (h_payto,imeta_serial_id,range)' 309 ') %s ;' 310 ,'exchange_statistic_interval_counter' 311 ,'PARTITION BY HASH(h_payto)' 312 ,partition_suffix 313 ); 314 PERFORM comment_partitioned_table( 315 'various numeric statistics (cumulative counters) being tracked' 316 ,'exchange_statistic_interval_counter' 317 ,partition_suffix 318 ); 319 PERFORM comment_partitioned_column( 320 'identifies what the statistic is about' 321 ,'imeta_serial_id' 322 ,'exchange_statistic_interval_counter' 323 ,partition_suffix 324 ); 325 PERFORM comment_partitioned_column( 326 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics' 327 ,'h_payto' 328 ,'exchange_statistic_interval_counter' 329 ,partition_suffix 330 ); 331 PERFORM comment_partitioned_column( 332 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges' 333 ,'range' 334 ,'exchange_statistic_interval_counter' 335 ,partition_suffix 336 ); 337 PERFORM comment_partitioned_column( 338 'determines the last event currently included in the interval' 339 ,'event_delimiter' 340 ,'exchange_statistic_interval_counter' 341 ,partition_suffix 342 ); 343 PERFORM comment_partitioned_column( 344 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword' 345 ,'cumulative_number' 346 ,'exchange_statistic_interval_counter' 347 ,partition_suffix 348 ); 349 END $$; 350 351 352 CREATE FUNCTION foreign_table_exchange_statistic_interval_counter() 353 RETURNS VOID 354 LANGUAGE plpgsql 355 AS $$ 356 DECLARE 357 table_name TEXT DEFAULT 'exchange_statistic_interval_counter'; 358 BEGIN 359 EXECUTE FORMAT ( 360 'ALTER TABLE ' || table_name || 361 ' ADD CONSTRAINT ' || table_name || '_event_delimiter_foreign_key' 362 ' FOREIGN KEY (event_delimiter) ' 363 ' REFERENCES exchange_statistic_counter_event (nevent_serial_id) ON DELETE RESTRICT' 364 ); 365 END $$; 366 367 368 CREATE FUNCTION create_table_exchange_statistic_amount_event ( 369 IN partition_suffix TEXT DEFAULT NULL 370 ) 371 RETURNS VOID 372 LANGUAGE plpgsql 373 AS $$ 374 BEGIN 375 PERFORM create_partitioned_table( 376 'CREATE TABLE %I' 377 '(aevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY' 378 ',imeta_serial_id INT8' 379 ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE' 380 ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' 381 ',slot INT8 NOT NULL' 382 ',delta taler_amount NOT NULL' 383 ',CHECK ((delta).val IS NOT NULL AND (delta).frac IS NOT NULL)' 384 ',CONSTRAINT event_key UNIQUE (h_payto,imeta_serial_id,slot)' 385 ') %s ;' 386 ,'exchange_statistic_amount_event' 387 ,'PARTITION BY HASH(h_payto)' 388 ,partition_suffix 389 ); 390 PERFORM comment_partitioned_table( 391 'amount to decrement an interval statistic by when a certain time value is reached' 392 ,'exchange_statistic_amount_event' 393 ,partition_suffix 394 ); 395 PERFORM comment_partitioned_column( 396 'unique identifier for this amount event' 397 ,'aevent_serial_id' 398 ,'exchange_statistic_amount_event' 399 ,partition_suffix 400 ); 401 PERFORM comment_partitioned_column( 402 'identifies what the statistic is about; must be of clazz interval and of stype amount' 403 ,'imeta_serial_id' 404 ,'exchange_statistic_amount_event' 405 ,partition_suffix 406 ); 407 PERFORM comment_partitioned_column( 408 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics' 409 ,'h_payto' 410 ,'exchange_statistic_amount_event' 411 ,partition_suffix 412 ); 413 PERFORM comment_partitioned_column( 414 'identifies the time slot at which the given event(s) happened' 415 ,'slot' 416 ,'exchange_statistic_amount_event' 417 ,partition_suffix 418 ); 419 PERFORM comment_partitioned_column( 420 'total cumulative amount that was added at the time identified by slot' 421 ,'delta' 422 ,'exchange_statistic_amount_event' 423 ,partition_suffix 424 ); 425 END $$; 426 427 428 CREATE FUNCTION constrain_table_exchange_statistic_amount_event( 429 IN partition_suffix TEXT 430 ) 431 RETURNS void 432 LANGUAGE plpgsql 433 AS $$ 434 DECLARE 435 table_name TEXT default 'exchange_statistic_amount_event'; 436 BEGIN 437 table_name = concat_ws('_', table_name, partition_suffix); 438 EXECUTE FORMAT ( 439 'ALTER TABLE ' || table_name || 440 ' ADD CONSTRAINT ' || table_name || '_aevent_serial_id_key' 441 ' UNIQUE (aevent_serial_id)' 442 ); 443 END $$; 444 445 446 447 CREATE FUNCTION create_table_exchange_statistic_interval_amount ( 448 IN partition_suffix TEXT DEFAULT NULL 449 ) 450 RETURNS VOID 451 LANGUAGE plpgsql 452 AS $$ 453 BEGIN 454 PERFORM create_partitioned_table( 455 'CREATE TABLE %I' 456 '(imeta_serial_id INT8 NOT NULL' 457 ' REFERENCES exchange_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE' 458 ',h_payto BYTEA CHECK (LENGTH(h_payto)=32)' 459 ',event_delimiter INT8 NOT NULL' 460 ',range INT8 NOT NULL' 461 ',cumulative_value taler_amount NOT NULL' 462 ',CHECK ((cumulative_value).val IS NOT NULL AND (cumulative_value).frac IS NOT NULL)' 463 ',UNIQUE (h_payto,imeta_serial_id,range)' 464 ') %s ;' 465 ,'exchange_statistic_interval_amount' 466 ,'PARTITION BY HASH(h_payto)' 467 ,partition_suffix 468 ); 469 PERFORM comment_partitioned_table( 470 'various amount statistics being tracked' 471 ,'exchange_statistic_interval_amount' 472 ,partition_suffix 473 ); 474 PERFORM comment_partitioned_column( 475 'identifies what the statistic is about' 476 ,'imeta_serial_id' 477 ,'exchange_statistic_interval_amount' 478 ,partition_suffix 479 ); 480 PERFORM comment_partitioned_column( 481 'identifies an account (hash of normalized payto) for which the statistic is kept, NULL for global statistics' 482 ,'h_payto' 483 ,'exchange_statistic_interval_amount' 484 ,partition_suffix 485 ); 486 PERFORM comment_partitioned_column( 487 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges' 488 ,'range' 489 ,'exchange_statistic_interval_amount' 490 ,partition_suffix 491 ); 492 PERFORM comment_partitioned_column( 493 'amount affected by the event' 494 ,'cumulative_value' 495 ,'exchange_statistic_interval_amount' 496 ,partition_suffix 497 ); 498 END $$; 499 500 501 CREATE FUNCTION foreign_table_exchange_statistic_interval_amount() 502 RETURNS VOID 503 LANGUAGE plpgsql 504 AS $$ 505 DECLARE 506 table_name TEXT DEFAULT 'exchange_statistic_interval_amount'; 507 BEGIN 508 EXECUTE FORMAT ( 509 'ALTER TABLE ' || table_name || 510 ' ADD CONSTRAINT ' || table_name || '_event_delimiter_foreign_key' 511 ' FOREIGN KEY (event_delimiter) ' 512 ' REFERENCES exchange_statistic_amount_event (aevent_serial_id) ON DELETE RESTRICT' 513 ); 514 END $$; 515 516 517 CREATE TYPE exchange_statistic_interval_number_get_return_value 518 AS 519 (range INT8 520 ,rvalue INT8 521 ); 522 COMMENT ON TYPE exchange_statistic_interval_number_get_return_value 523 IS 'Return type for exchange_statistic_interval_number_get stored procedure'; 524 525 CREATE TYPE exchange_statistic_interval_amount_get_return_value 526 AS 527 (range INT8 528 ,rvalue taler_amount 529 ); 530 COMMENT ON TYPE exchange_statistic_interval_amount_get_return_value 531 IS 'Return type for exchange_statistic_interval_amount_get stored procedure'; 532 533 534 INSERT INTO exchange_tables 535 (name 536 ,version 537 ,action 538 ,partitioned 539 ,by_range) 540 VALUES 541 ('exchange_statistic_bucket_counter' 542 ,'exchange-0009' 543 ,'create' 544 ,TRUE 545 ,FALSE), 546 ('exchange_statistic_bucket_amount' 547 ,'exchange-0009' 548 ,'create' 549 ,TRUE 550 ,FALSE), 551 ('exchange_statistic_counter_event' 552 ,'exchange-0009' 553 ,'create' 554 ,TRUE 555 ,FALSE), 556 ('exchange_statistic_counter_event' 557 ,'exchange-0009' 558 ,'constrain' 559 ,TRUE 560 ,FALSE), 561 ('exchange_statistic_interval_counter' 562 ,'exchange-0009' 563 ,'create' 564 ,TRUE 565 ,FALSE), 566 ('exchange_statistic_interval_counter' 567 ,'exchange-0009' 568 ,'foreign' 569 ,TRUE 570 ,FALSE), 571 ('exchange_statistic_amount_event' 572 ,'exchange-0009' 573 ,'create' 574 ,TRUE 575 ,FALSE), 576 ('exchange_statistic_amount_event' 577 ,'exchange-0009' 578 ,'constrain' 579 ,TRUE 580 ,FALSE), 581 ('exchange_statistic_interval_amount' 582 ,'exchange-0009' 583 ,'create' 584 ,TRUE 585 ,FALSE), 586 ('exchange_statistic_interval_amount' 587 ,'exchange-0009' 588 ,'foreign' 589 ,TRUE 590 ,FALSE);