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
 259 -- Find out, for each player in the group, what the maximum remaining score
 260 -- possibly can be, using get_max_score_for_{songs,players} (the first for
 261 -- selected but not played songs, the second for random songs that have not
 262 -- been chosen yet).
 263 --
 264 -- This doesn't solve problems we'd face with more than one chosen song, but it
 265 -- should be good enough.
 266
 267 CREATE TYPE minmax_score AS (
 268         player INTEGER,
 269         min_score INTEGER,
 270         max_score INTEGER
 271 );
 272
 273 -- get_minmax_score_for_players(tournament, round, parallel, playmode)
 274 CREATE FUNCTION get_minmax_score_for_players(integer, integer, integer, varchar) RETURNS SETOF minmax_score
 275     AS $$
 276 DECLARE
 277         ret minmax_score;
 278         tp RECORD;
 279         ts RECORD;
 280 BEGIN
 281         FOR tp IN SELECT player FROM roundparticipation WHERE tournament=$1 AND round=$2 AND parallel=$3 LOOP
 282                 ret.player := tp.player;
 283                 ret.min_score := 0;
 284                 ret.max_score := 0;
 285
 286                 FOR ts IN SELECT song,score FROM scores WHERE tournament=$1 AND round=$2 AND parallel=$3 AND player=tp.player LOOP
 287                         IF ts.score IS NOT NULL THEN
 288                                 -- score is already given
 289                                 ret.min_score := ret.min_score + ts.score;
 290                                 ret.max_score := ret.max_score + ts.score;
 291                         ELSE
 292                                 IF ts.song IS NOT NULL THEN
 293                                         ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_songs($1, $4) WHERE song=ts.song );
 294                                 ELSE
 295                                         ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_players($1, $4) WHERE song=tp.player );
 296                                 END IF;
 297                         END IF;
 298                 END LOOP;
 299
 300                 RETURN NEXT ret;
 301         END LOOP;
 302         RETURN;
 303 END;
 304 $$
 305     LANGUAGE plpgsql
 306     STABLE
 307     RETURNS NULL ON NULL INPUT;
 308
 309 -- use the minmax_score as a basis for finding best and worst possible ranks
 310 CREATE TYPE minmax_rank AS (
 311         player INTEGER,
 312         best_rank INTEGER,
 313         worst_rank INTEGER
 314 );
 315
 316 -- get_minmax_score_for_players(tournament, round, parallel, playmode)
 317 CREATE TABLE temp_minmax (
 318         player INTEGER,
 319         min_score INTEGER,
 320         max_score INTEGER
 321 );
 322
 323 CREATE FUNCTION get_minmax_rank_for_players(integer, integer, integer, varchar) RETURNS SETOF minmax_rank
 324     AS $$
 325 DECLARE
 326         ret minmax_rank;
 327         tp RECORD;
 328 BEGIN
 329         INSERT INTO temp_minmax SELECT * FROM get_minmax_score_for_players($1, $2, $3, $4);
 330
 331         FOR tp IN SELECT * FROM temp_minmax LOOP
 332                 ret.player = tp.player;
 333                 ret.best_rank = 1 + ( SELECT COUNT(*) FROM temp_minmax WHERE min_score >= tp.max_score AND player<>tp.player);
 334                 ret.worst_rank = 1 + ( SELECT COUNT(*) FROM temp_minmax WHERE max_score > tp.min_score AND player<>tp.player );
 335
 336                 RETURN NEXT ret;
 337         END LOOP;
 338         TRUNCATE temp_minmax;
 339         RETURN;
 340 END;
 341     $$
 342     LANGUAGE plpgsql
 343     VOLATILE    -- not really, but needed for the TEMPORARY TABLE
 344     RETURNS NULL ON NULL INPUT;
 345