From e97cbaa1c55d1932d50af142418467178d838b29 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Fri, 16 Oct 2020 18:13:04 -0400 Subject: [PATCH] Add a table for non-participants. --- build/build_full.sql | 3 + scripts/gen_partitions.shl | 2 +- tables/player_game_nonparticipants.tab | 195 ++++++++++++++++++ .../player_game_nonparticipants_ins_trg.sql | 63 ++++++ 4 files changed, 262 insertions(+), 1 deletion(-) create mode 100755 tables/player_game_nonparticipants.tab create mode 100644 triggers/player_game_nonparticipants_ins_trg.sql diff --git a/build/build_full.sql b/build/build_full.sql index 1601619..a9072c4 100755 --- a/build/build_full.sql +++ b/build/build_full.sql @@ -1,4 +1,5 @@ -- drop tables first in reverse order +drop table if exists player_game_nonparticipants; drop table if exists player_game_frag_matrix; drop table if exists player_agg_stats_mv; drop table if exists merged_servers; @@ -69,6 +70,7 @@ drop table if exists players cascade; \i tables/merged_servers.tab \i tables/player_agg_stats_mv.tab \i tables/player_game_frag_matrix.tab +\i tables/player_game_nonparticipants.tab begin; @@ -224,6 +226,7 @@ insert into players (nick) values ('Untracked Player'); \i triggers/player_weapon_stats_ins_trg.sql \i triggers/team_game_stats_ins_trg.sql \i triggers/player_game_frag_matrix_ins_trg.sql +\i triggers/player_game_nonparticipants_ins_trg.sql -- version tracking insert into db_version(version, descr) values('1.0.0', 'Initial build'); diff --git a/scripts/gen_partitions.shl b/scripts/gen_partitions.shl index 1b68000..a189064 100755 --- a/scripts/gen_partitions.shl +++ b/scripts/gen_partitions.shl @@ -37,7 +37,7 @@ do printf "ALTER TABLE %s_%s%s ADD PRIMARY KEY USING INDEX %s_%s%s_pk;\n" $table $year $qtr $table $year $qtr fi - if [[ $table = "player_game_stats" || $table = "player_weapon_stats" ]] + if [[ $table = "player_game_stats" || $table = "player_weapon_stats" || $table = "player_game_nonparticipants" ]] then printf "CREATE INDEX %s_%s%s_ix002 on %s_%s%s(game_id);\n" $table $year $qtr $table $year $qtr printf "CREATE INDEX %s_%s%s_ix003 on %s_%s%s(player_id);\n" $table $year $qtr $table $year $qtr diff --git a/tables/player_game_nonparticipants.tab b/tables/player_game_nonparticipants.tab new file mode 100755 index 0000000..36cb80b --- /dev/null +++ b/tables/player_game_nonparticipants.tab @@ -0,0 +1,195 @@ +CREATE TABLE xonstat.player_game_nonparticipants +( + player_game_nonparticipants_id bigserial NOT NULL, + player_id integer NOT NULL, + game_id bigint NOT NULL, + nick character varying(128), + stripped_nick character varying(128), + alivetime interval, + score integer, + create_dt timestamp without time zone NOT NULL DEFAULT (current_timestamp at time zone 'UTC'), + CONSTRAINT player_game_nonparticipants_pk PRIMARY KEY (player_game_nonparticipants_id), + CONSTRAINT player_game_nonparticipants_fk001 FOREIGN KEY (player_id) + REFERENCES xonstat.players (player_id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT player_game_nonparticipants_fk002 FOREIGN KEY (game_id) + REFERENCES xonstat.games (game_id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); + +CREATE INDEX player_game_nonparticipant_ix01 on player_game_nonparticipantss(create_dt); +CREATE INDEX player_game_nonparticipant_ix02 on player_game_nonparticipantss(game_id); +CREATE INDEX player_game_nonparticipant_ix03 on player_game_nonparticipantss(player_id); +ALTER TABLE xonstat.player_game_stats OWNER TO xonstat; + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2020q4 ( + CHECK ( create_dt >= DATE '2020-10-01' AND create_dt < DATE '2021-01-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2020q4_ix001 on player_game_nonparticipants_2020q4(create_dt); +CREATE INDEX player_game_nonparticipants_2020q4_ix002 on player_game_nonparticipants_2020q4(game_id); +CREATE INDEX player_game_nonparticipants_2020q4_ix003 on player_game_nonparticipants_2020q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2021q1 ( + CHECK ( create_dt >= DATE '2021-01-01' AND create_dt < DATE '2021-04-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2021q1_ix001 on player_game_nonparticipants_2021q1(create_dt); +CREATE INDEX player_game_nonparticipants_2021q1_ix002 on player_game_nonparticipants_2021q1(game_id); +CREATE INDEX player_game_nonparticipants_2021q1_ix003 on player_game_nonparticipants_2021q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2021q2 ( + CHECK ( create_dt >= DATE '2021-04-01' AND create_dt < DATE '2021-07-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2021q2_ix001 on player_game_nonparticipants_2021q2(create_dt); +CREATE INDEX player_game_nonparticipants_2021q2_ix002 on player_game_nonparticipants_2021q2(game_id); +CREATE INDEX player_game_nonparticipants_2021q2_ix003 on player_game_nonparticipants_2021q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2021q3 ( + CHECK ( create_dt >= DATE '2021-07-01' AND create_dt < DATE '2021-10-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2021q3_ix001 on player_game_nonparticipants_2021q3(create_dt); +CREATE INDEX player_game_nonparticipants_2021q3_ix002 on player_game_nonparticipants_2021q3(game_id); +CREATE INDEX player_game_nonparticipants_2021q3_ix003 on player_game_nonparticipants_2021q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2021q4 ( + CHECK ( create_dt >= DATE '2021-10-01' AND create_dt < DATE '2022-01-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2021q4_ix001 on player_game_nonparticipants_2021q4(create_dt); +CREATE INDEX player_game_nonparticipants_2021q4_ix002 on player_game_nonparticipants_2021q4(game_id); +CREATE INDEX player_game_nonparticipants_2021q4_ix003 on player_game_nonparticipants_2021q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2022q1 ( + CHECK ( create_dt >= DATE '2022-01-01' AND create_dt < DATE '2022-04-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2022q1_ix001 on player_game_nonparticipants_2022q1(create_dt); +CREATE INDEX player_game_nonparticipants_2022q1_ix002 on player_game_nonparticipants_2022q1(game_id); +CREATE INDEX player_game_nonparticipants_2022q1_ix003 on player_game_nonparticipants_2022q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2022q2 ( + CHECK ( create_dt >= DATE '2022-04-01' AND create_dt < DATE '2022-07-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2022q2_ix001 on player_game_nonparticipants_2022q2(create_dt); +CREATE INDEX player_game_nonparticipants_2022q2_ix002 on player_game_nonparticipants_2022q2(game_id); +CREATE INDEX player_game_nonparticipants_2022q2_ix003 on player_game_nonparticipants_2022q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2022q3 ( + CHECK ( create_dt >= DATE '2022-07-01' AND create_dt < DATE '2022-10-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2022q3_ix001 on player_game_nonparticipants_2022q3(create_dt); +CREATE INDEX player_game_nonparticipants_2022q3_ix002 on player_game_nonparticipants_2022q3(game_id); +CREATE INDEX player_game_nonparticipants_2022q3_ix003 on player_game_nonparticipants_2022q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2022q4 ( + CHECK ( create_dt >= DATE '2022-10-01' AND create_dt < DATE '2023-01-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2022q4_ix001 on player_game_nonparticipants_2022q4(create_dt); +CREATE INDEX player_game_nonparticipants_2022q4_ix002 on player_game_nonparticipants_2022q4(game_id); +CREATE INDEX player_game_nonparticipants_2022q4_ix003 on player_game_nonparticipants_2022q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2023q1 ( + CHECK ( create_dt >= DATE '2023-01-01' AND create_dt < DATE '2023-04-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2023q1_ix001 on player_game_nonparticipants_2023q1(create_dt); +CREATE INDEX player_game_nonparticipants_2023q1_ix002 on player_game_nonparticipants_2023q1(game_id); +CREATE INDEX player_game_nonparticipants_2023q1_ix003 on player_game_nonparticipants_2023q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2023q2 ( + CHECK ( create_dt >= DATE '2023-04-01' AND create_dt < DATE '2023-07-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2023q2_ix001 on player_game_nonparticipants_2023q2(create_dt); +CREATE INDEX player_game_nonparticipants_2023q2_ix002 on player_game_nonparticipants_2023q2(game_id); +CREATE INDEX player_game_nonparticipants_2023q2_ix003 on player_game_nonparticipants_2023q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2023q3 ( + CHECK ( create_dt >= DATE '2023-07-01' AND create_dt < DATE '2023-10-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2023q3_ix001 on player_game_nonparticipants_2023q3(create_dt); +CREATE INDEX player_game_nonparticipants_2023q3_ix002 on player_game_nonparticipants_2023q3(game_id); +CREATE INDEX player_game_nonparticipants_2023q3_ix003 on player_game_nonparticipants_2023q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2023q4 ( + CHECK ( create_dt >= DATE '2023-10-01' AND create_dt < DATE '2024-01-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2023q4_ix001 on player_game_nonparticipants_2023q4(create_dt); +CREATE INDEX player_game_nonparticipants_2023q4_ix002 on player_game_nonparticipants_2023q4(game_id); +CREATE INDEX player_game_nonparticipants_2023q4_ix003 on player_game_nonparticipants_2023q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2024q1 ( + CHECK ( create_dt >= DATE '2024-01-01' AND create_dt < DATE '2024-04-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2024q1_ix001 on player_game_nonparticipants_2024q1(create_dt); +CREATE INDEX player_game_nonparticipants_2024q1_ix002 on player_game_nonparticipants_2024q1(game_id); +CREATE INDEX player_game_nonparticipants_2024q1_ix003 on player_game_nonparticipants_2024q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2024q2 ( + CHECK ( create_dt >= DATE '2024-04-01' AND create_dt < DATE '2024-07-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2024q2_ix001 on player_game_nonparticipants_2024q2(create_dt); +CREATE INDEX player_game_nonparticipants_2024q2_ix002 on player_game_nonparticipants_2024q2(game_id); +CREATE INDEX player_game_nonparticipants_2024q2_ix003 on player_game_nonparticipants_2024q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2024q3 ( + CHECK ( create_dt >= DATE '2024-07-01' AND create_dt < DATE '2024-10-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2024q3_ix001 on player_game_nonparticipants_2024q3(create_dt); +CREATE INDEX player_game_nonparticipants_2024q3_ix002 on player_game_nonparticipants_2024q3(game_id); +CREATE INDEX player_game_nonparticipants_2024q3_ix003 on player_game_nonparticipants_2024q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2024q4 ( + CHECK ( create_dt >= DATE '2024-10-01' AND create_dt < DATE '2025-01-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2024q4_ix001 on player_game_nonparticipants_2024q4(create_dt); +CREATE INDEX player_game_nonparticipants_2024q4_ix002 on player_game_nonparticipants_2024q4(game_id); +CREATE INDEX player_game_nonparticipants_2024q4_ix003 on player_game_nonparticipants_2024q4(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2025q1 ( + CHECK ( create_dt >= DATE '2025-01-01' AND create_dt < DATE '2025-04-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2025q1_ix001 on player_game_nonparticipants_2025q1(create_dt); +CREATE INDEX player_game_nonparticipants_2025q1_ix002 on player_game_nonparticipants_2025q1(game_id); +CREATE INDEX player_game_nonparticipants_2025q1_ix003 on player_game_nonparticipants_2025q1(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2025q2 ( + CHECK ( create_dt >= DATE '2025-04-01' AND create_dt < DATE '2025-07-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2025q2_ix001 on player_game_nonparticipants_2025q2(create_dt); +CREATE INDEX player_game_nonparticipants_2025q2_ix002 on player_game_nonparticipants_2025q2(game_id); +CREATE INDEX player_game_nonparticipants_2025q2_ix003 on player_game_nonparticipants_2025q2(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2025q3 ( + CHECK ( create_dt >= DATE '2025-07-01' AND create_dt < DATE '2025-10-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2025q3_ix001 on player_game_nonparticipants_2025q3(create_dt); +CREATE INDEX player_game_nonparticipants_2025q3_ix002 on player_game_nonparticipants_2025q3(game_id); +CREATE INDEX player_game_nonparticipants_2025q3_ix003 on player_game_nonparticipants_2025q3(player_id); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_nonparticipants_2025q4 ( + CHECK ( create_dt >= DATE '2025-10-01' AND create_dt < DATE '2026-01-01' ) +) INHERITS (player_game_nonparticipants); + +CREATE INDEX player_game_nonparticipants_2025q4_ix001 on player_game_nonparticipants_2025q4(create_dt); +CREATE INDEX player_game_nonparticipants_2025q4_ix002 on player_game_nonparticipants_2025q4(game_id); +CREATE INDEX player_game_nonparticipants_2025q4_ix003 on player_game_nonparticipants_2025q4(player_id); + diff --git a/triggers/player_game_nonparticipants_ins_trg.sql b/triggers/player_game_nonparticipants_ins_trg.sql new file mode 100644 index 0000000..be24a43 --- /dev/null +++ b/triggers/player_game_nonparticipants_ins_trg.sql @@ -0,0 +1,63 @@ +CREATE OR REPLACE FUNCTION player_game_nonparticipants_ins() +RETURNS TRIGGER AS $$ +BEGIN + IF (NEW.create_dt >= DATE '2020-01-01' AND NEW.create_dt < DATE '2020-04-01') THEN + INSERT INTO player_game_nonparticipants_2020Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-04-01' AND NEW.create_dt < DATE '2020-07-01') THEN + INSERT INTO player_game_nonparticipants_2020Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-07-01' AND NEW.create_dt < DATE '2020-10-01') THEN + INSERT INTO player_game_nonparticipants_2020Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-10-01' AND NEW.create_dt < DATE '2021-01-01') THEN + INSERT INTO player_game_nonparticipants_2020Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2021-01-01' AND NEW.create_dt < DATE '2021-04-01') THEN + INSERT INTO player_game_nonparticipants_2021Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2021-04-01' AND NEW.create_dt < DATE '2021-07-01') THEN + INSERT INTO player_game_nonparticipants_2021Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2021-07-01' AND NEW.create_dt < DATE '2021-10-01') THEN + INSERT INTO player_game_nonparticipants_2021Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2021-10-01' AND NEW.create_dt < DATE '2022-01-01') THEN + INSERT INTO player_game_nonparticipants_2021Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2022-01-01' AND NEW.create_dt < DATE '2022-04-01') THEN + INSERT INTO player_game_nonparticipants_2022Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2022-04-01' AND NEW.create_dt < DATE '2022-07-01') THEN + INSERT INTO player_game_nonparticipants_2022Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2022-07-01' AND NEW.create_dt < DATE '2022-10-01') THEN + INSERT INTO player_game_nonparticipants_2022Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2022-10-01' AND NEW.create_dt < DATE '2023-01-01') THEN + INSERT INTO player_game_nonparticipants_2022Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2023-01-01' AND NEW.create_dt < DATE '2023-04-01') THEN + INSERT INTO player_game_nonparticipants_2023Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2023-04-01' AND NEW.create_dt < DATE '2023-07-01') THEN + INSERT INTO player_game_nonparticipants_2023Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2023-07-01' AND NEW.create_dt < DATE '2023-10-01') THEN + INSERT INTO player_game_nonparticipants_2023Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2023-10-01' AND NEW.create_dt < DATE '2024-01-01') THEN + INSERT INTO player_game_nonparticipants_2023Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2024-01-01' AND NEW.create_dt < DATE '2024-04-01') THEN + INSERT INTO player_game_nonparticipants_2024Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2024-04-01' AND NEW.create_dt < DATE '2024-07-01') THEN + INSERT INTO player_game_nonparticipants_2024Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2024-07-01' AND NEW.create_dt < DATE '2024-10-01') THEN + INSERT INTO player_game_nonparticipants_2024Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2024-10-01' AND NEW.create_dt < DATE '2025-01-01') THEN + INSERT INTO player_game_nonparticipants_2024Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2025-01-01' AND NEW.create_dt < DATE '2025-04-01') THEN + INSERT INTO player_game_nonparticipants_2025Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2025-04-01' AND NEW.create_dt < DATE '2025-07-01') THEN + INSERT INTO player_game_nonparticipants_2025Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2025-07-01' AND NEW.create_dt < DATE '2025-10-01') THEN + INSERT INTO player_game_nonparticipants_2025Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2025-10-01' AND NEW.create_dt < DATE '2026-01-01') THEN + INSERT INTO player_game_nonparticipants_2025Q4 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the player_game_nonparticipants_ins() trigger!'; + END IF; + RETURN NULL; +END +$$ +LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS player_game_nonparticipants_ins_trg ON xonstat.player_game_nonparticipants; +CREATE TRIGGER player_game_nonparticipants_ins_trg +BEFORE INSERT on xonstat.player_game_nonparticipants +FOR EACH ROW EXECUTE PROCEDURE player_game_nonparticipants_ins(); -- 2.39.2