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