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         PRIMARY KEY (tournament, player)
 115 );
 116
 117 CREATE TABLE rounds (
 118         tournament INTEGER NOT NULL REFERENCES tournaments,
 119         round INTEGER NOT NULL,
 120         randomsongs INTEGER NOT NULL,
 121         chosensongs INTEGER NOT NULL,
 122         numqualifying INTEGER,
 123
 124         PRIMARY KEY (tournament, round)
 125 );
 126
 127 CREATE TABLE groups (
 128         tournament INTEGER NOT NULL REFERENCES tournaments,
 129         round INTEGER NOT NULL,
 130         parallel INTEGER NOT NULL,
 131
 132         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
 133         PRIMARY KEY (tournament, round, parallel)
 134 );
 135
 136 CREATE TABLE roundrandomsongs (
 137         tournament INTEGER NOT NULL,
 138         round INTEGER NOT NULL,
 139         parallel INTEGER NOT NULL,
 140         song INTEGER NOT NULL REFERENCES songs,
 141
 142         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 143         PRIMARY KEY (tournament, round, parallel, song)
 144 );
 145
 146 CREATE TABLE roundparticipation (
 147         tournament INTEGER NOT NULL,
 148         round INTEGER NOT NULL,
 149         parallel INTEGER NOT NULL,
 150         player INTEGER NOT NULL REFERENCES players,
 151         position INTEGER NOT NULL,
 152
 153         UNIQUE (tournament, round, player),
 154         UNIQUE (tournament, round, parallel, position),
 155         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 156         PRIMARY KEY (tournament, round, parallel, player)
 157 );
 158
 159 CREATE TABLE scores (
 160         tournament INTEGER NOT NULL,
 161         round INTEGER NOT NULL,
 162         parallel INTEGER NOT NULL,
 163         player INTEGER NOT NULL REFERENCES players,
 164         songnumber INTEGER NOT NULL,
 165
 166         song INTEGER REFERENCES songs,
 167         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
 168         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
 169
 170         chosen BOOLEAN,
 171         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
 172
 173         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
 174         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
 175         PRIMARY KEY (tournament, round, parallel, player, songnumber)
 176 );
 177
 178 CREATE TABLE randomsongsused (
 179         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
 180 );
 181
 182 CREATE SCHEMA bigscreen;
 183
 184 CREATE TABLE bigscreen.active_tournament (
 185         tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
 186 );
 187 CREATE TABLE bigscreen.active_groups (
 188         tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament,
 189         round INTEGER NOT NULL,
 190         parallel INTEGER NOT NULL,
 191         num_machines INTEGER NOT NULL,
 192         players_per_machine INTEGER NOT NULL CHECK ( players_per_machine IN (1, 2) ),
 193         last_updated TIMESTAMP NOT NULL,
 194
 195         PRIMARY KEY ( tournament, round, parallel ),
 196         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
 197 );
 198 CREATE TABLE bigscreen.active_screens (
 199         id VARCHAR NOT NULL PRIMARY KEY
 200 );