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 machinesongs (
  28         song INTEGER NOT NULL REFERENCES songs,
  29         machine INTEGER NOT NULL REFERENCES machines,
  30
  31         PRIMARY KEY ( song, machine )
  32 );
  33
  34 CREATE TABLE scoringsystems (
  35         scoringsystem SERIAL PRIMARY KEY,
  36         scoringsystemname VARCHAR NOT NULL,
  37
  38         UNIQUE ( scoringsystemname )
  39 );
  40
  41 CREATE TABLE songratings (
  42         song INTEGER NOT NULL REFERENCES songs,
  43         machine INTEGER NOT NULL REFERENCES machines,
  44         playmode VARCHAR NOT NULL CHECK (playmode IN ('single','double')),
  45         difficulty VARCHAR NOT NULL CHECK (difficulty IN ('beginner','standard','difficult','expert','challenge')),
  46         feetrating INTEGER NOT NULL CHECK (feetrating >= 0 AND feetrating <= 10),
  47
  48         PRIMARY KEY (song, machine, playmode, difficulty)
  49 );
  50
  51 CREATE TABLE players (
  52         player SERIAL PRIMARY KEY,
  53         nick VARCHAR NOT NULL,
  54
  55         UNIQUE ( nick )
  56 );
  57
  58 CREATE TABLE seasons (
  59         season SERIAL PRIMARY KEY,
  60         seasonname VARCHAR NOT NULL,
  61
  62         UNIQUE ( seasonname )
  63 );
  64
  65 CREATE TABLE tournaments (
  66         tournament SERIAL PRIMARY KEY,
  67         season INTEGER NOT NULL REFERENCES seasons,
  68         tournamentname VARCHAR NOT NULL,
  69         country INTEGER NOT NULL REFERENCES countries,
  70         location VARCHAR NOT NULL,
  71         "date" DATE NOT NULL,
  72         machine INTEGER NOT NULL REFERENCES machines,
  73         scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
  74
  75         UNIQUE ( season, tournamentname )
  76 );
  77
  78 CREATE TABLE tournamentparticipation (
  79         tournament INTEGER NOT NULL REFERENCES tournaments,
  80         player INTEGER NOT NULL REFERENCES players,
  81         paid BOOLEAN NOT NULL,
  82
  83         PRIMARY KEY ( tournament, player )
  84 );
  85
  86 CREATE TABLE tournamentrankings (
  87         tournament INTEGER NOT NULL REFERENCES tournaments,
  88         ranking INTEGER NOT NULL,
  89         player INTEGER NOT NULL REFERENCES players,
  90         points INTEGER,
  91
  92         UNIQUE (tournament, player),
  93         PRIMARY KEY (tournament, ranking)
  94 );
  95
  96 CREATE TABLE rounds (
  97         tournament INTEGER NOT NULL REFERENCES tournaments,
  98         round INTEGER NOT NULL,
  99         randomsongs INTEGER NOT NULL,
 100         chosensongs INTEGER NOT NULL,
 101         numqualifying INTEGER,
 102
 103         PRIMARY KEY (tournament, round)
 104 );
 105
 106 CREATE TABLE groups (
 107         tournament INTEGER NOT NULL REFERENCES tournaments,
 108         round INTEGER NOT NULL,
 109         parallel INTEGER NOT NULL,
 110
 111         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
 112         PRIMARY KEY (tournament, round, parallel)
 113 );
 114
 115 CREATE TABLE roundrandomsongs (
 116         tournament INTEGER NOT NULL,
 117         round INTEGER NOT NULL,
 118         parallel INTEGER NOT NULL,
 119         song INTEGER NOT NULL REFERENCES songs,
 120
 121         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 122         PRIMARY KEY (tournament, round, parallel, song)
 123 );
 124
 125 CREATE TABLE roundparticipation (
 126         tournament INTEGER NOT NULL,
 127         round INTEGER NOT NULL,
 128         parallel INTEGER NOT NULL,
 129         player INTEGER NOT NULL REFERENCES players,
 130         position INTEGER NOT NULL,
 131
 132         UNIQUE (tournament, round, player),
 133         UNIQUE (tournament, round, parallel, position),
 134         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
 135         PRIMARY KEY (tournament, round, parallel, player)
 136 );
 137
 138 CREATE TABLE scores (
 139         tournament INTEGER NOT NULL,
 140         round INTEGER NOT NULL,
 141         parallel INTEGER NOT NULL,
 142         player INTEGER NOT NULL REFERENCES players,
 143         songnumber INTEGER NOT NULL,
 144
 145         song INTEGER REFERENCES songs,
 146         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
 147         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
 148
 149         chosen BOOLEAN,
 150         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
 151
 152         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
 153         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
 154         PRIMARY KEY (tournament, round, parallel, player, songnumber)
 155 );
 156
 157 CREATE TABLE randomsongsused (
 158         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
 159 );
 160
 161 CREATE SCHEMA bigscreen;
 162
 163 CREATE TABLE bigscreen.active_tournament (
 164         tournament INTEGER NOT NULL REFERENCES tournaments
 165 );
 166
 167 -- install PL/PgSQL
 168 CREATE FUNCTION plpgsql_call_handler() RETURNS opaque
 169     AS '/usr/lib/postgresql/lib/plpgsql.so', 'plpgsql_call_handler'
 170     LANGUAGE "C";
 171 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
 172
 173 -- NOTIFY active_tournament when anything has changed
 174 CREATE FUNCTION notify_active_tournament() RETURNS trigger
 175 AS '
 176     DECLARE
 177     BEGIN
 178         NOTIFY bigscreen.active_tournament;
 179     END;'
 180 LANGUAGE plpgsql;
 181