From bfee932cf6103a884980c66563f0ff4050000ab9 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Tue, 3 Oct 2017 20:16:41 -0400 Subject: [PATCH] Partition the frag matrix table. --- build/build_full.sql | 1 + tables/player_game_frag_matrix.tab | 87 +++++++++++++++++++- triggers/player_game_frag_matrix_ins_trg.sql | 41 +++++++++ 3 files changed, 127 insertions(+), 2 deletions(-) create mode 100644 triggers/player_game_frag_matrix_ins_trg.sql diff --git a/build/build_full.sql b/build/build_full.sql index 6ae4ebe..e34cbb7 100755 --- a/build/build_full.sql +++ b/build/build_full.sql @@ -218,6 +218,7 @@ insert into players (nick) values ('Untracked Player'); \i triggers/player_game_stats_ins_trg.sql \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 -- version tracking insert into db_version(version, descr) values('1.0.0', 'Initial build'); diff --git a/tables/player_game_frag_matrix.tab b/tables/player_game_frag_matrix.tab index e7fd157..fd302dc 100755 --- a/tables/player_game_frag_matrix.tab +++ b/tables/player_game_frag_matrix.tab @@ -21,7 +21,90 @@ WITH ( OIDS=FALSE ); --- TODO: determine if this is needed. Assume no for now. --- CREATE INDEX player_game_frag_matrix_ix01 on player_game_frag_matrix(game_id); +CREATE INDEX player_game_frag_matrix_ix01 on player_game_frag_matrix(create_dt); ALTER TABLE xonstat.player_game_frag_matrix OWNER TO xonstat; + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2017q3 ( + CHECK ( create_dt >= DATE '2017-07-01' AND create_dt < DATE '2017-10-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2017q3_ix001 on player_game_frag_matrix_2017q3(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2017q4 ( + CHECK ( create_dt >= DATE '2017-10-01' AND create_dt < DATE '2018-01-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2017q4_ix001 on player_game_frag_matrix_2017q4(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2018q1 ( + CHECK ( create_dt >= DATE '2018-01-01' AND create_dt < DATE '2018-04-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2018q1_ix001 on player_game_frag_matrix_2018q1(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2018q2 ( + CHECK ( create_dt >= DATE '2018-04-01' AND create_dt < DATE '2018-07-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2018q2_ix001 on player_game_frag_matrix_2018q2(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2018q3 ( + CHECK ( create_dt >= DATE '2018-07-01' AND create_dt < DATE '2018-10-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2018q3_ix001 on player_game_frag_matrix_2018q3(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2018q4 ( + CHECK ( create_dt >= DATE '2018-10-01' AND create_dt < DATE '2019-01-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2018q4_ix001 on player_game_frag_matrix_2018q4(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2019q1 ( + CHECK ( create_dt >= DATE '2019-01-01' AND create_dt < DATE '2019-04-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2019q1_ix001 on player_game_frag_matrix_2019q1(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2019q2 ( + CHECK ( create_dt >= DATE '2019-04-01' AND create_dt < DATE '2019-07-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2019q2_ix001 on player_game_frag_matrix_2019q2(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2019q3 ( + CHECK ( create_dt >= DATE '2019-07-01' AND create_dt < DATE '2019-10-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2019q3_ix001 on player_game_frag_matrix_2019q3(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2019q4 ( + CHECK ( create_dt >= DATE '2019-10-01' AND create_dt < DATE '2020-01-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2019q4_ix001 on player_game_frag_matrix_2019q4(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2020q1 ( + CHECK ( create_dt >= DATE '2020-01-01' AND create_dt < DATE '2020-04-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2020q1_ix001 on player_game_frag_matrix_2020q1(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2020q2 ( + CHECK ( create_dt >= DATE '2020-04-01' AND create_dt < DATE '2020-07-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2020q2_ix001 on player_game_frag_matrix_2020q2(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2020q3 ( + CHECK ( create_dt >= DATE '2020-07-01' AND create_dt < DATE '2020-10-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2020q3_ix001 on player_game_frag_matrix_2020q3(create_dt); + +CREATE TABLE IF NOT EXISTS xonstat.player_game_frag_matrix_2020q4 ( + CHECK ( create_dt >= DATE '2020-10-01' AND create_dt < DATE '2021-01-01' ) +) INHERITS (player_game_frag_matrix); + +CREATE INDEX player_game_frag_matrix_2020q4_ix001 on player_game_frag_matrix_2020q4(create_dt); diff --git a/triggers/player_game_frag_matrix_ins_trg.sql b/triggers/player_game_frag_matrix_ins_trg.sql new file mode 100644 index 0000000..1b9f117 --- /dev/null +++ b/triggers/player_game_frag_matrix_ins_trg.sql @@ -0,0 +1,41 @@ +CREATE OR REPLACE FUNCTION player_game_frag_matrix_ins() +RETURNS TRIGGER AS $$ +BEGIN + IF (NEW.create_dt >= DATE '2017-10-01' AND NEW.create_dt < DATE '2018-01-01') THEN + INSERT INTO player_game_frag_matrix_2017Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-01-01' AND NEW.create_dt < DATE '2018-04-01') THEN + INSERT INTO player_game_frag_matrix_2018Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-04-01' AND NEW.create_dt < DATE '2018-07-01') THEN + INSERT INTO player_game_frag_matrix_2018Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-07-01' AND NEW.create_dt < DATE '2018-10-01') THEN + INSERT INTO player_game_frag_matrix_2018Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2018-10-01' AND NEW.create_dt < DATE '2019-01-01') THEN + INSERT INTO player_game_frag_matrix_2018Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-01-01' AND NEW.create_dt < DATE '2019-04-01') THEN + INSERT INTO player_game_frag_matrix_2019Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-04-01' AND NEW.create_dt < DATE '2019-07-01') THEN + INSERT INTO player_game_frag_matrix_2019Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-07-01' AND NEW.create_dt < DATE '2019-10-01') THEN + INSERT INTO player_game_frag_matrix_2019Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2019-10-01' AND NEW.create_dt < DATE '2020-01-01') THEN + INSERT INTO player_game_frag_matrix_2019Q4 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-01-01' AND NEW.create_dt < DATE '2020-04-01') THEN + INSERT INTO player_game_frag_matrix_2020Q1 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-04-01' AND NEW.create_dt < DATE '2020-07-01') THEN + INSERT INTO player_game_frag_matrix_2020Q2 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-07-01' AND NEW.create_dt < DATE '2020-10-01') THEN + INSERT INTO player_game_frag_matrix_2020Q3 VALUES (NEW.*); + ELSIF (NEW.create_dt >= DATE '2020-10-01' AND NEW.create_dt < DATE '2021-01-01') THEN + INSERT INTO player_game_frag_matrix_2020Q4 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the player_game_frag_matrix_ins() trigger!'; + END IF; + RETURN NULL; +END +$$ +LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS player_game_frag_matrix_ins_trg ON xonstat.player_game_frag_matrix; +CREATE TRIGGER player_game_frag_matrix_ins_trg + BEFORE INSERT on xonstat.player_game_frag_matrix + FOR EACH ROW EXECUTE PROCEDURE player_game_frag_matrix_ins(); -- 2.39.2