Add team scores table. Partition till 2015.

diff --git a/build/build_full.sql b/build/build_full.sql
index 7a03ac708cb659ebfd092f63431dfd6df448d6bf..708e2cf1ed457b3055d04d79f296947c820794ff 100755 (executable)
--- a/build/build_full.sql
+++ b/build/build_full.sql
@@ -1,4 +1,5 @@
 -- drop tables first in reverse order
+drop table if exists team_game_stats;
 drop table if exists summary_stats cascade;
 drop table if exists player_map_captimes;
 drop table if exists player_ladder_ranks;
@@ -47,6 +48,7 @@ drop table if exists players cascade;
 \i tables/player_ladder_ranks.tab
 \i tables/player_map_captimes.tab
 \i tables/summary_stats.tab
+\i tables/team_game_stats.tab
 
 begin;
 
@@ -188,6 +190,7 @@ insert into players (nick) values ('Untracked Player');
 \i triggers/games_ins_trg.sql
 \i triggers/player_game_stats_ins_trg.sql
 \i triggers/player_weapon_stats_ins_trg.sql
+\i triggers/team_game_stats_ins_trg.sql
 
 -- version tracking
 insert into db_version(version, descr) values('1.0.0', 'Initial build');

diff --git a/tables/team_game_stats.tab b/tables/team_game_stats.tab
new file mode 100755 (executable)
index 0000000..77c2c17
--- /dev/null
+++ b/tables/team_game_stats.tab
@@ -0,0 +1,138 @@
+CREATE TABLE xonstat.team_game_stats
+(
+  team_game_stat_id bigserial NOT NULL,
+  game_id bigint NOT NULL,
+  team integer NOT NULL,
+  score integer,
+  create_dt timestamp without time zone NOT NULL DEFAULT (current_timestamp at time zone 'UTC'),
+  CONSTRAINT team_game_stats_pk PRIMARY KEY (team_game_stat_id),
+  CONSTRAINT team_game_stats_uk001 UNIQUE (game_id, team),
+  CONSTRAINT team_game_stats_fk001 FOREIGN KEY (game_id)
+      REFERENCES xonstat.games (game_id) MATCH SIMPLE
+      ON UPDATE NO ACTION ON DELETE CASCADE
+)
+WITH (
+  OIDS=FALSE
+);
+
+CREATE INDEX team_game_stats_ix01 on team_game_stats(game_id);
+ALTER TABLE xonstat.team_game_stats OWNER TO xonstat;
+
+CREATE TABLE xonstat.team_game_stats_2011Q2 (
+    CHECK ( create_dt >= DATE '2011-04-01' AND create_dt < DATE '2011-07-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2011Q2_ix01 on team_game_stats_2011Q2(game_id);
+ALTER TABLE xonstat.team_game_stats_2011Q2 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2011Q3 ( 
+    CHECK ( create_dt >= DATE '2011-07-01' AND create_dt < DATE '2011-10-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2011Q3_ix01 on team_game_stats_2011Q3(game_id);
+ALTER TABLE xonstat.team_game_stats_2011Q3 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2011Q4 ( 
+    CHECK ( create_dt >= DATE '2011-10-01' AND create_dt < DATE '2012-01-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2011Q4_ix01 on team_game_stats_2011Q4(game_id);
+ALTER TABLE xonstat.team_game_stats_2011Q4 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2012Q1 ( 
+    CHECK ( create_dt >= DATE '2012-01-01' AND create_dt < DATE '2012-04-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2012Q1_ix01 on team_game_stats_2012Q1(game_id);
+ALTER TABLE xonstat.team_game_stats_2012Q1 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2012Q2 ( 
+    CHECK ( create_dt >= DATE '2012-04-01' AND create_dt < DATE '2012-07-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2012Q2_ix01 on team_game_stats_2012Q2(game_id);
+ALTER TABLE xonstat.team_game_stats_2012Q2 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2012Q3 ( 
+    CHECK ( create_dt >= DATE '2012-07-01' AND create_dt < DATE '2012-10-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2012Q3_ix01 on team_game_stats_2012Q3(game_id);
+ALTER TABLE xonstat.team_game_stats_2012Q3 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2012Q4 ( 
+    CHECK ( create_dt >= DATE '2012-10-01' AND create_dt < DATE '2013-01-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2012Q4_ix01 on team_game_stats_2012Q4(game_id);
+ALTER TABLE xonstat.team_game_stats_2012Q4 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2013Q1 ( 
+    CHECK ( create_dt >= DATE '2013-01-01' AND create_dt < DATE '2013-04-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2013Q1_ix01 on team_game_stats_2013Q1(game_id);
+ALTER TABLE xonstat.team_game_stats_2013Q1 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2013Q2 ( 
+    CHECK ( create_dt >= DATE '2013-04-01' AND create_dt < DATE '2013-07-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2013Q2_ix01 on team_game_stats_2013Q2(game_id);
+ALTER TABLE xonstat.team_game_stats_2013Q2 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2013Q3 ( 
+    CHECK ( create_dt >= DATE '2013-07-01' AND create_dt < DATE '2013-10-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2013Q3_ix01 on team_game_stats_2013Q3(game_id);
+ALTER TABLE xonstat.team_game_stats_2013Q3 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2013Q4 ( 
+    CHECK ( create_dt >= DATE '2013-10-01' AND create_dt < DATE '2014-01-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2013Q4_ix01 on team_game_stats_2013Q4(game_id);
+ALTER TABLE xonstat.team_game_stats_2013Q4 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2014Q1 ( 
+    CHECK ( create_dt >= DATE '2014-01-01' AND create_dt < DATE '2014-04-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2014Q1_ix01 on team_game_stats_2014Q1(game_id);
+ALTER TABLE xonstat.team_game_stats_2014Q1 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2014Q2 ( 
+    CHECK ( create_dt >= DATE '2014-04-01' AND create_dt < DATE '2014-07-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2014Q2_ix01 on team_game_stats_2014Q2(game_id);
+ALTER TABLE xonstat.team_game_stats_2014Q2 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2014Q3 ( 
+    CHECK ( create_dt >= DATE '2014-07-01' AND create_dt < DATE '2014-10-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2014Q3_ix01 on team_game_stats_2014Q3(game_id);
+ALTER TABLE xonstat.team_game_stats_2014Q3 OWNER TO xonstat;
+
+
+CREATE TABLE xonstat.team_game_stats_2014Q4 ( 
+    CHECK ( create_dt >= DATE '2014-10-01' AND create_dt < DATE '2015-01-01' ) 
+) INHERITS (team_game_stats);
+
+CREATE INDEX team_game_stats_2014Q4_ix01 on team_game_stats_2014Q4(game_id);
+ALTER TABLE xonstat.team_game_stats_2014Q4 OWNER TO xonstat;

diff --git a/triggers/team_game_stats_ins_trg.sql b/triggers/team_game_stats_ins_trg.sql
new file mode 100644 (file)
index 0000000..6664003
--- /dev/null
+++ b/triggers/team_game_stats_ins_trg.sql
@@ -0,0 +1,33 @@
+CREATE OR REPLACE FUNCTION team_game_stats_ins()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF (NEW.create_dt >= DATE '2011-04-01' AND NEW.create_dt < DATE '2011-07-01') THEN
+        INSERT INTO team_game_stats_2011Q2 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2011-07-01' AND NEW.create_dt < DATE '2011-10-01') THEN
+        INSERT INTO team_game_stats_2011Q3 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2011-10-01' AND NEW.create_dt < DATE '2012-01-01') THEN
+        INSERT INTO team_game_stats_2011Q4 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2012-01-01' AND NEW.create_dt < DATE '2012-04-01') THEN
+        INSERT INTO team_game_stats_2012Q1 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2012-04-01' AND NEW.create_dt < DATE '2012-07-01') THEN
+        INSERT INTO team_game_stats_2012Q2 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2012-07-01' AND NEW.create_dt < DATE '2012-10-01') THEN
+        INSERT INTO team_game_stats_2012Q3 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2012-10-01' AND NEW.create_dt < DATE '2013-01-01') THEN
+        INSERT INTO team_game_stats_2012Q4 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2013-01-01' AND NEW.create_dt < DATE '2013-04-01') THEN
+        INSERT INTO team_game_stats_2013Q1 VALUES (NEW.*);
+    ELSIF (NEW.create_dt >= DATE '2013-04-01' AND NEW.create_dt < DATE '2013-07-01') THEN
+        INSERT INTO team_game_stats_2013Q2 VALUES (NEW.*);
+    ELSE
+        RAISE EXCEPTION 'Date out of range.  Fix the team_game_stats_ins() trigger!';
+    END IF;
+    RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS team_game_stats_ins_trg ON xonstat.team_game_stats;
+CREATE TRIGGER team_game_stats_ins_trg
+    BEFORE INSERT on xonstat.team_game_stats
+    FOR EACH ROW EXECUTE PROCEDURE team_game_stats_ins();