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 TABLE players (
  72         player SERIAL PRIMARY KEY,
  73         nick VARCHAR NOT NULL,
  74         country INTEGER NOT NULL REFERENCES countries,
  75         club INTEGER REFERENCES clubs,
  76
  77         UNIQUE ( nick )
  78 );
  79
  80 CREATE TABLE seasons (
  81         season SERIAL PRIMARY KEY,
  82         seasonname VARCHAR NOT NULL,
  83
  84         UNIQUE ( seasonname )
  85 );
  86
  87 CREATE TABLE tournaments (
  88         tournament SERIAL PRIMARY KEY,
  89         season INTEGER NOT NULL REFERENCES seasons,
  90         tournamentname VARCHAR NOT NULL,
  91         country INTEGER NOT NULL REFERENCES countries,
  92         location VARCHAR NOT NULL,
  93         "date" DATE NOT NULL,
  94         machine INTEGER NOT NULL REFERENCES machines,
  95         scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
  96
  97         UNIQUE ( season, tournamentname )
  98 );
  99
 100 CREATE TABLE tournamentparticipation (
 101         tournament INTEGER NOT NULL REFERENCES tournaments,
 102         player INTEGER NOT NULL REFERENCES players,
 103         paid BOOLEAN NOT NULL,
 104
 105         PRIMARY KEY ( tournament, player )
 106 );
 107
 108 CREATE TABLE tournamentrankings (
 109         tournament INTEGER NOT NULL REFERENCES tournaments,
 110         ranking INTEGER NOT NULL,
 111         player INTEGER NOT NULL REFERENCES players,
 112         points INTEGER,
 113
 114         UNIQUE (tournament, player),
 115         PRIMARY KEY (tournament, ranking)
 116 );
 117
 118 CREATE TABLE rounds (
 119         tournament INTEGER NOT NULL REFERENCES tournaments,
 120         round INTEGER NOT NULL,
 121         randomsongs INTEGER NOT NULL,
 122         chosensongs INTEGER NOT NULL,
 123         numqualifying INTEGER,
 124
 125         PRIMARY KEY (tournament, round)
 126 );
 127
 128 CREATE TABLE groups (
 129         tournament INTEGER NOT NULL REFERENCES tournaments,
 130         round INTEGER NOT NULL,
 131         parallel INTEGER NOT NULL,
 132
 133         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
 134         PRIMARY KEY (tournament, round, parallel)
 135 );
 136
 137 CREATE TABLE roundrandomsongs (
 138         tournament INTEGER NOT NULL,
 139         round INTEGER NOT NULL,
 140         parallel INTEGER NOT NULL,
 141         song INTEGER NOT NULL REFERENCES songs,
 142
 143         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 144         PRIMARY KEY (tournament, round, parallel, song)
 145 );
 146
 147 CREATE TABLE roundparticipation (
 148         tournament INTEGER NOT NULL,
 149         round INTEGER NOT NULL,
 150         parallel INTEGER NOT NULL,
 151         player INTEGER NOT NULL REFERENCES players,
 152         position INTEGER NOT NULL,
 153
 154         UNIQUE (tournament, round, player),
 155         UNIQUE (tournament, round, parallel, position),
 156         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 157         PRIMARY KEY (tournament, round, parallel, player)
 158 );
 159
 160 CREATE TABLE scores (
 161         tournament INTEGER NOT NULL,
 162         round INTEGER NOT NULL,
 163         parallel INTEGER NOT NULL,
 164         player INTEGER NOT NULL REFERENCES players,
 165         songnumber INTEGER NOT NULL,
 166
 167         song INTEGER REFERENCES songs,
 168         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
 169         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
 170
 171         chosen BOOLEAN,
 172         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
 173
 174         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
 175         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
 176         PRIMARY KEY (tournament, round, parallel, player, songnumber)
 177 );
 178
 179 CREATE TABLE randomsongsused (
 180         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
 181 );
 182
 183 CREATE SCHEMA bigscreen;
 184
 185 CREATE TABLE bigscreen.active_tournament (
 186         tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
 187 );
 188 CREATE TABLE bigscreen.active_groups (
 189         tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament,
 190         round INTEGER NOT NULL,
 191         parallel INTEGER NOT NULL,
 192         num_machines INTEGER NOT NULL,
 193         players_per_machine INTEGER NOT NULL CHECK ( players_per_machine IN (1, 2) ),
 194         last_updated TIMESTAMP NOT NULL,
 195
 196         PRIMARY KEY ( tournament, round, parallel ),
 197         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
 198 );
 199 CREATE TABLE bigscreen.active_screens (
 200         id VARCHAR NOT NULL PRIMARY KEY
 201 );