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
  14         UNIQUE ( countryname )
  15 );
  16
  17 CREATE TABLE songs (
  18         song SERIAL PRIMARY KEY,
  19         title VARCHAR NOT NULL,
  20         artist VARCHAR NOT NULL,
  21         minbpm INTEGER NOT NULL,
  22         maxbpm INTEGER NOT NULL,
  23
  24         UNIQUE ( title )
  25 );
  26
  27 CREATE TABLE songshorttitles (
  28         song INTEGER NOT NULL REFERENCES songs,
  29         shorttitle VARCHAR NOT NULL,
  30
  31         PRIMARY KEY ( song, shorttitle ),
  32         UNIQUE ( shorttitle )
  33 );
  34
  35 CREATE TABLE machinesongs (
  36         song INTEGER NOT NULL REFERENCES songs,
  37         machine INTEGER NOT NULL REFERENCES machines,
  38
  39         PRIMARY KEY ( song, machine )
  40 );
  41
  42 CREATE TABLE scoringsystems (
  43         scoringsystem SERIAL PRIMARY KEY,
  44         scoringsystemname VARCHAR NOT NULL,
  45
  46         UNIQUE ( scoringsystemname )
  47 );
  48
  49 CREATE TABLE songratings (
  50         song INTEGER NOT NULL REFERENCES songs,
  51         machine INTEGER NOT NULL REFERENCES machines,
  52         playmode VARCHAR NOT NULL CHECK (playmode IN ('single','double')),
  53         difficulty VARCHAR NOT NULL CHECK (difficulty IN ('beginner','standard','difficult','expert','challenge')),
  54         feetrating INTEGER NOT NULL CHECK (feetrating >= 0 AND feetrating <= 10),
  55
  56         PRIMARY KEY (song, machine, playmode, difficulty)
  57 );
  58 CREATE INDEX songratings_feetrating ON songratings ( feetrating );
  59
  60 CREATE TABLE players (
  61         player SERIAL PRIMARY KEY,
  62         nick VARCHAR NOT NULL,
  63
  64         UNIQUE ( nick )
  65 );
  66
  67 CREATE TABLE seasons (
  68         season SERIAL PRIMARY KEY,
  69         seasonname VARCHAR NOT NULL,
  70
  71         UNIQUE ( seasonname )
  72 );
  73
  74 CREATE TABLE tournaments (
  75         tournament SERIAL PRIMARY KEY,
  76         season INTEGER NOT NULL REFERENCES seasons,
  77         tournamentname VARCHAR NOT NULL,
  78         country INTEGER NOT NULL REFERENCES countries,
  79         location VARCHAR NOT NULL,
  80         "date" DATE NOT NULL,
  81         machine INTEGER NOT NULL REFERENCES machines,
  82         scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
  83
  84         UNIQUE ( season, tournamentname )
  85 );
  86
  87 CREATE TABLE tournamentparticipation (
  88         tournament INTEGER NOT NULL REFERENCES tournaments,
  89         player INTEGER NOT NULL REFERENCES players,
  90         paid BOOLEAN NOT NULL,
  91
  92         PRIMARY KEY ( tournament, player )
  93 );
  94
  95 CREATE TABLE tournamentrankings (
  96         tournament INTEGER NOT NULL REFERENCES tournaments,
  97         ranking INTEGER NOT NULL,
  98         player INTEGER NOT NULL REFERENCES players,
  99         points INTEGER,
 100
 101         UNIQUE (tournament, player),
 102         PRIMARY KEY (tournament, ranking)
 103 );
 104
 105 CREATE TABLE rounds (
 106         tournament INTEGER NOT NULL REFERENCES tournaments,
 107         round INTEGER NOT NULL,
 108         randomsongs INTEGER NOT NULL,
 109         chosensongs INTEGER NOT NULL,
 110         numqualifying INTEGER,
 111
 112         PRIMARY KEY (tournament, round)
 113 );
 114
 115 CREATE TABLE groups (
 116         tournament INTEGER NOT NULL REFERENCES tournaments,
 117         round INTEGER NOT NULL,
 118         parallel INTEGER NOT NULL,
 119
 120         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
 121         PRIMARY KEY (tournament, round, parallel)
 122 );
 123
 124 CREATE TABLE roundrandomsongs (
 125         tournament INTEGER NOT NULL,
 126         round INTEGER NOT NULL,
 127         parallel INTEGER NOT NULL,
 128         song INTEGER NOT NULL REFERENCES songs,
 129
 130         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 131         PRIMARY KEY (tournament, round, parallel, song)
 132 );
 133
 134 CREATE TABLE roundparticipation (
 135         tournament INTEGER NOT NULL,
 136         round INTEGER NOT NULL,
 137         parallel INTEGER NOT NULL,
 138         player INTEGER NOT NULL REFERENCES players,
 139         position INTEGER NOT NULL,
 140
 141         UNIQUE (tournament, round, player),
 142         UNIQUE (tournament, round, parallel, position),
 143         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 144         PRIMARY KEY (tournament, round, parallel, player)
 145 );
 146
 147 CREATE TABLE scores (
 148         tournament INTEGER NOT NULL,
 149         round INTEGER NOT NULL,
 150         parallel INTEGER NOT NULL,
 151         player INTEGER NOT NULL REFERENCES players,
 152         songnumber INTEGER NOT NULL,
 153
 154         song INTEGER REFERENCES songs,
 155         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
 156         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
 157
 158         chosen BOOLEAN,
 159         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
 160
 161         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
 162         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
 163         PRIMARY KEY (tournament, round, parallel, player, songnumber)
 164 );
 165
 166 CREATE TABLE randomsongsused (
 167         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
 168 );
 169
 170 CREATE SCHEMA bigscreen;
 171
 172 CREATE TABLE bigscreen.active_tournament (
 173         tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
 174 );
 175 CREATE TABLE bigscreen.active_groups (
 176         tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament,
 177         round INTEGER NOT NULL,
 178         parallel INTEGER NOT NULL,
 179         num_machines INTEGER NOT NULL,
 180         players_per_machine INTEGER NOT NULL CHECK ( players_per_machine IN (1, 2) ),
 181         last_updated TIMESTAMP NOT NULL,
 182
 183         PRIMARY KEY ( tournament, round, parallel ),
 184         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
 185 );
 186 CREATE TABLE bigscreen.active_screens (
 187         id VARCHAR NOT NULL PRIMARY KEY
 188 );