From a6c715b5975da4c3c6e235b635d5737268d1e10a Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Thu, 7 Jul 2011 16:04:29 -0400 Subject: [PATCH] Add triggers for partitioning functionality. --- build/build_full.sql | 5 +++++ triggers/games_ins_trg.sql | 28 ++++++++++++++++++++++++ triggers/player_game_stats_ins_trg.sql | 28 ++++++++++++++++++++++++ triggers/player_weapon_stats_ins_trg.sql | 28 ++++++++++++++++++++++++ 4 files changed, 89 insertions(+) mode change 100644 => 100755 build/build_full.sql create mode 100755 triggers/games_ins_trg.sql create mode 100755 triggers/player_game_stats_ins_trg.sql create mode 100755 triggers/player_weapon_stats_ins_trg.sql diff --git a/build/build_full.sql b/build/build_full.sql old mode 100644 new mode 100755 index 14662b5..43ab506 --- a/build/build_full.sql +++ b/build/build_full.sql @@ -166,6 +166,11 @@ values(15, 'g_jetpack'); insert into players (nick) values ('Bot'); insert into players (nick) values ('Untracked Player'); +-- triggers +\i triggers/games_ins_trg.sql +\i triggers/player_game_stats_ins_trg.sql +\i triggers/player_weapon_stats_ins_trg.sql + -- version tracking insert into db_version(version, descr) values('1.0.0', 'Initial build'); diff --git a/triggers/games_ins_trg.sql b/triggers/games_ins_trg.sql new file mode 100755 index 0000000..17e7c08 --- /dev/null +++ b/triggers/games_ins_trg.sql @@ -0,0 +1,28 @@ +CREATE OR REPLACE FUNCTION games_ins() +RETURNS TRIGGER AS $$ +BEGIN + IF (create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01') THEN + INSERT INTO games_2011Q2 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01') THEN + INSERT INTO games_2011Q3 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01') THEN + INSERT INTO games_2011Q4 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01') THEN + INSERT INTO games_2012Q1 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01') THEN + INSERT INTO games_2012Q2 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01') THEN + INSERT INTO games_2012Q3 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01') THEN + INSERT INTO games_2012Q4 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01') THEN + INSERT INTO games_2013Q1 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01') THEN + INSERT INTO games_2013Q2 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the games_ins() trigger!'; + END IF; + RETURN NULL; +END; +$$ +LANGUAGE plpgsql; diff --git a/triggers/player_game_stats_ins_trg.sql b/triggers/player_game_stats_ins_trg.sql new file mode 100755 index 0000000..8c818f9 --- /dev/null +++ b/triggers/player_game_stats_ins_trg.sql @@ -0,0 +1,28 @@ +CREATE OR REPLACE FUNCTION player_game_stats_ins() +RETURNS TRIGGER AS $$ +BEGIN + IF (create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01') THEN + INSERT INTO player_game_stats_2011Q2 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01') THEN + INSERT INTO player_game_stats_2011Q3 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01') THEN + INSERT INTO player_game_stats_2011Q4 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01') THEN + INSERT INTO player_game_stats_2012Q1 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01') THEN + INSERT INTO player_game_stats_2012Q2 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01') THEN + INSERT INTO player_game_stats_2012Q3 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01') THEN + INSERT INTO player_game_stats_2012Q4 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01') THEN + INSERT INTO player_game_stats_2013Q1 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01') THEN + INSERT INTO player_game_stats_2013Q2 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the player_game_stats_ins() trigger!'; + END IF; + RETURN NULL; +END; +$$ +LANGUAGE plpgsql; diff --git a/triggers/player_weapon_stats_ins_trg.sql b/triggers/player_weapon_stats_ins_trg.sql new file mode 100755 index 0000000..f209b01 --- /dev/null +++ b/triggers/player_weapon_stats_ins_trg.sql @@ -0,0 +1,28 @@ +CREATE OR REPLACE FUNCTION player_weapon_stats_ins() +RETURNS TRIGGER AS $$ +BEGIN + IF (create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01') THEN + INSERT INTO player_weapon_stats_2011Q2 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01') THEN + INSERT INTO player_weapon_stats_2011Q3 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01') THEN + INSERT INTO player_weapon_stats_2011Q4 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01') THEN + INSERT INTO player_weapon_stats_2012Q1 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01') THEN + INSERT INTO player_weapon_stats_2012Q2 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01') THEN + INSERT INTO player_weapon_stats_2012Q3 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01') THEN + INSERT INTO player_weapon_stats_2012Q4 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01') THEN + INSERT INTO player_weapon_stats_2013Q1 VALUES (NEW.*); + ELSIF (create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01') THEN + INSERT INTO player_weapon_stats_2013Q2 VALUES (NEW.*); + ELSE + RAISE EXCEPTION 'Date out of range. Fix the player_weapon_stats_ins() trigger!'; + END IF; + RETURN NULL; +END; +$$ +LANGUAGE plpgsql; -- 2.39.2