git.sesse.net Git - ccbs/blob - sql/ccbs.sql

   1 -- vim:set tw=0:
   2
   3 CREATE TABLE machines (
   4         machine SERIAL PRIMARY KEY,
   5         machinename VARCHAR NOT NULL,
   6
   7         UNIQUE ( machinename )
   8 );
   9
  10 CREATE TABLE countries (
  11         country SERIAL PRIMARY KEY,
  12         countryname VARCHAR NOT NULL,
  13         countrycode CHAR(3) NOT NULL,   -- IOC country code
  14
  15         UNIQUE ( countryname ),
  16         UNIQUE ( countrycode )
  17 );
  18
  19 CREATE TABLE clubs (
  20         club SERIAL PRIMARY KEY,
  21         clubname VARCHAR NOT NULL,
  22         clubcode CHAR(3) NOT NULL,
  23
  24         UNIQUE ( clubname ),
  25         UNIQUE ( clubcode )
  26 );
  27
  28 CREATE TABLE songs (
  29         song SERIAL PRIMARY KEY,
  30         title VARCHAR NOT NULL,
  31         artist VARCHAR NOT NULL,
  32         minbpm INTEGER NOT NULL,
  33         maxbpm INTEGER NOT NULL,
  34
  35         UNIQUE ( title )
  36 );
  37
  38 CREATE TABLE songshorttitles (
  39         song INTEGER NOT NULL REFERENCES songs,
  40         shorttitle VARCHAR NOT NULL,
  41
  42         PRIMARY KEY ( song, shorttitle ),
  43         UNIQUE ( shorttitle )
  44 );
  45
  46 CREATE TABLE machinesongs (
  47         song INTEGER NOT NULL REFERENCES songs,
  48         machine INTEGER NOT NULL REFERENCES machines,
  49
  50         PRIMARY KEY ( song, machine )
  51 );
  52
  53 CREATE TABLE scoringsystems (
  54         scoringsystem SERIAL PRIMARY KEY,
  55         scoringsystemname VARCHAR NOT NULL,
  56
  57         UNIQUE ( scoringsystemname )
  58 );
  59
  60 CREATE TABLE songratings (
  61         song INTEGER NOT NULL REFERENCES songs,
  62         machine INTEGER NOT NULL REFERENCES machines,
  63         playmode VARCHAR NOT NULL CHECK (playmode IN ('single','double')),
  64         difficulty VARCHAR NOT NULL CHECK (difficulty IN ('beginner','standard','difficult','expert','challenge')),
  65         feetrating INTEGER NOT NULL CHECK (feetrating >= 0 AND feetrating <= 10),
  66
  67         PRIMARY KEY (song, machine, playmode, difficulty)
  68 );
  69 CREATE INDEX songratings_feetrating ON songratings ( feetrating );
  70
  71 CREATE VIEW max_single_feetrating AS SELECT machine,song,MAX(feetrating) AS feetrating FROM songratings WHERE playmode='single' GROUP BY machine,song;
  72
  73 CREATE TABLE players (
  74         player SERIAL PRIMARY KEY,
  75         nick VARCHAR NOT NULL,
  76         country INTEGER NOT NULL REFERENCES countries,
  77         club INTEGER REFERENCES clubs,
  78
  79         UNIQUE ( nick )
  80 );
  81
  82 CREATE TABLE seasons (
  83         season SERIAL PRIMARY KEY,
  84         seasonname VARCHAR NOT NULL,
  85
  86         UNIQUE ( seasonname )
  87 );
  88
  89 CREATE TABLE tournaments (
  90         tournament SERIAL PRIMARY KEY,
  91         season INTEGER NOT NULL REFERENCES seasons,
  92         tournamentname VARCHAR NOT NULL,
  93         country INTEGER NOT NULL REFERENCES countries,
  94         location VARCHAR NOT NULL,
  95         "date" DATE NOT NULL,
  96         machine INTEGER NOT NULL REFERENCES machines,
  97         scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
  98
  99         UNIQUE ( season, tournamentname )
 100 );
 101
 102 CREATE TABLE tournamentparticipation (
 103         tournament INTEGER NOT NULL REFERENCES tournaments,
 104         player INTEGER NOT NULL REFERENCES players,
 105         paid BOOLEAN NOT NULL,
 106
 107         PRIMARY KEY ( tournament, player )
 108 );
 109
 110 CREATE TABLE tournamentrankings (
 111         tournament INTEGER NOT NULL REFERENCES tournaments,
 112         ranking INTEGER NOT NULL,
 113         player INTEGER NOT NULL REFERENCES players,
 114         points INTEGER,
 115
 116         PRIMARY KEY (tournament, player)
 117 );
 118
 119 CREATE TABLE rounds (
 120         tournament INTEGER NOT NULL REFERENCES tournaments,
 121         round INTEGER NOT NULL,
 122         randomsongs INTEGER NOT NULL,
 123         chosensongs INTEGER NOT NULL,
 124         numqualifying INTEGER,
 125
 126         PRIMARY KEY (tournament, round)
 127 );
 128
 129 CREATE TABLE groups (
 130         tournament INTEGER NOT NULL REFERENCES tournaments,
 131         round INTEGER NOT NULL,
 132         parallel INTEGER NOT NULL,
 133
 134         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
 135         PRIMARY KEY (tournament, round, parallel)
 136 );
 137
 138 CREATE TABLE roundrandomsongs (
 139         tournament INTEGER NOT NULL,
 140         round INTEGER NOT NULL,
 141         parallel INTEGER NOT NULL,
 142         song INTEGER NOT NULL REFERENCES songs,
 143
 144         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 145         PRIMARY KEY (tournament, round, parallel, song)
 146 );
 147
 148 CREATE TABLE roundparticipation (
 149         tournament INTEGER NOT NULL,
 150         round INTEGER NOT NULL,
 151         parallel INTEGER NOT NULL,
 152         player INTEGER NOT NULL REFERENCES players,
 153         position INTEGER NOT NULL,
 154
 155         UNIQUE (tournament, round, player),
 156         UNIQUE (tournament, round, parallel, position),
 157         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 158         PRIMARY KEY (tournament, round, parallel, player)
 159 );
 160
 161 CREATE TABLE scores (
 162         tournament INTEGER NOT NULL,
 163         round INTEGER NOT NULL,
 164         parallel INTEGER NOT NULL,
 165         player INTEGER NOT NULL REFERENCES players,
 166         songnumber INTEGER NOT NULL,
 167
 168         song INTEGER REFERENCES songs,
 169         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
 170         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
 171
 172         chosen BOOLEAN,
 173         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
 174
 175         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
 176         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
 177         PRIMARY KEY (tournament, round, parallel, player, songnumber)
 178 );
 179
 180 CREATE TABLE randomsongsused (
 181         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
 182 );
 183
 184 CREATE SCHEMA bigscreen;
 185
 186 CREATE TABLE bigscreen.active_tournament (
 187         tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
 188 );
 189 CREATE TABLE bigscreen.active_groups (
 190         tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament,
 191         round INTEGER NOT NULL,
 192         parallel INTEGER NOT NULL,
 193         num_machines INTEGER NOT NULL,
 194         players_per_machine INTEGER NOT NULL CHECK ( players_per_machine IN (1, 2) ),
 195         last_updated TIMESTAMP NOT NULL,
 196
 197         PRIMARY KEY ( tournament, round, parallel ),
 198         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
 199 );
 200 CREATE TABLE bigscreen.active_screens (
 201         id VARCHAR NOT NULL PRIMARY KEY
 202 );
 203
 204 -- get_max_score_for_songs(tournament, playmode)
 205 CREATE TYPE max_score AS (
 206         song INTEGER,
 207         max_score INTEGER
 208 );
 209
 210 CREATE FUNCTION get_max_score_for_songs(integer, varchar) RETURNS SETOF max_score
 211     AS $$
 212         SELECT
 213             song,
 214             MAX(feetrating)*1000 AS max_score
 215         FROM songratings
 216         WHERE
 217             machine=(
 218                 SELECT machine FROM tournaments WHERE tournament=$1
 219             )
 220             AND playmode=$2
 221         GROUP BY song
 222         ;
 223 $$
 224     LANGUAGE SQL
 225     STABLE
 226     RETURNS NULL ON NULL INPUT;
 227
 228 -- get_max_score_for_players(tournament, round, playmode)
 229 CREATE FUNCTION get_max_score_for_players(integer, integer, varchar) RETURNS SETOF max_score
 230     AS $$
 231         SELECT player,
 232           (
 233              SELECT feetrating
 234              FROM songratings
 235              WHERE machine=(         -- only find songs on the machine we use
 236                SELECT machine FROM tournaments WHERE tournament=$1
 237              )
 238              AND song NOT IN (       -- not a song that has been in elimination or seeding
 239                SELECT song FROM scores
 240                WHERE tournament=$1
 241                  AND song IS NOT NULL
 242                  AND parallel=0
 243                  AND chosen='f'
 244              )
 245              AND (player,song) NOT IN (  -- not a song the player has chosen before, or is a random song in this round
 246                SELECT player,song FROM scores
 247                WHERE tournament=$1
 248                  AND song IS NOT NULL
 249                  AND ( chosen='t' OR round=$2 )
 250              )
 251              AND playmode=$3
 252              ORDER BY feetrating DESC LIMIT 1
 253           ) * 1000 AS max_score FROM tournamentparticipation;
 254 $$
 255     LANGUAGE SQL
 256     STABLE
 257     RETURNS NULL ON NULL INPUT;
 258