From 0b034aaa0bb72b6f8caf0bec7957340caa7bdc00 Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Tue, 21 Aug 2012 17:40:16 -0400 Subject: [PATCH] Add players to ladder associative table, with ranks. --- build/build_full.sql | 4 ++++ tables/player_ladder_ranks.tab | 24 ++++++++++++++++++++++++ 2 files changed, 28 insertions(+) create mode 100644 tables/player_ladder_ranks.tab diff --git a/build/build_full.sql b/build/build_full.sql index d8ad15e..7766c8f 100755 --- a/build/build_full.sql +++ b/build/build_full.sql @@ -1,4 +1,6 @@ -- drop tables first in reverse order +drop table if exists player_ladder_ranks; +drop table if exists cd_ladder cascade; drop table if exists player_ranks_history cascade; drop table if exists player_ranks cascade; drop table if exists player_elos cascade; @@ -39,6 +41,8 @@ drop table if exists players cascade; \i tables/player_elos.tab \i tables/player_ranks.tab \i tables/player_ranks_history.tab +\i tables/cd_ladder.tab +\i tables/player_ladder_ranks.tab begin; diff --git a/tables/player_ladder_ranks.tab b/tables/player_ladder_ranks.tab new file mode 100644 index 0000000..ec8b1a0 --- /dev/null +++ b/tables/player_ladder_ranks.tab @@ -0,0 +1,24 @@ +CREATE TABLE xonstat.player_ladder_ranks +( + player_ladder_rank_id serial NOT NULL, + player_id integer NOT NULL, + ladder_cd integer NOT NULL, + points integer NOT NULL default 0, + rank integer NOT NULL default 1, + games integer NOT NULL default 0, + wins integer NOT NULL default 0, + active_ind boolean NOT NULL default true, + create_dt timestamp without time zone NOT NULL DEFAULT (current_timestamp at time zone 'UTC'), + CONSTRAINT player_ladder_ranks_pk PRIMARY KEY (player_ladder_rank_id), + CONSTRAINT player_ladder_ranks_uk01 UNIQUE (player_id, ladder_cd), + CONSTRAINT player_ladder_ranks_fk01 FOREIGN KEY (player_id) + REFERENCES xonstat.players (player_id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT player_ladder_ranks_fk02 FOREIGN KEY (ladder_cd) + REFERENCES xonstat.cd_ladder (ladder_cd) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); +ALTER TABLE xonstat.player_ladder_ranks OWNER TO xonstat; -- 2.39.2