3 CREATE TABLE machines (
4 machine SERIAL PRIMARY KEY,
5 machinename VARCHAR NOT NULL,
10 CREATE TABLE countries (
11 country SERIAL PRIMARY KEY,
12 countryname VARCHAR NOT NULL,
13 countrycode CHAR(3) NOT NULL, -- IOC country code
15 UNIQUE ( countryname ),
16 UNIQUE ( countrycode )
20 club SERIAL PRIMARY KEY,
21 clubname VARCHAR NOT NULL,
22 clubcode CHAR(3) NOT NULL,
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,
38 CREATE TABLE songshorttitles (
39 song INTEGER NOT NULL REFERENCES songs,
40 shorttitle VARCHAR NOT NULL,
42 PRIMARY KEY ( song, shorttitle ),
46 CREATE TABLE machinesongs (
47 song INTEGER NOT NULL REFERENCES songs,
48 machine INTEGER NOT NULL REFERENCES machines,
50 PRIMARY KEY ( song, machine )
53 CREATE TABLE scoringsystems (
54 scoringsystem SERIAL PRIMARY KEY,
55 scoringsystemname VARCHAR NOT NULL,
57 UNIQUE ( scoringsystemname )
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),
67 PRIMARY KEY (song, machine, playmode, difficulty)
69 CREATE INDEX songratings_feetrating ON songratings ( feetrating );
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,
80 CREATE TABLE seasons (
81 season SERIAL PRIMARY KEY,
82 seasonname VARCHAR NOT NULL,
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,
94 machine INTEGER NOT NULL REFERENCES machines,
95 scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
97 UNIQUE ( season, tournamentname )
100 CREATE TABLE tournamentparticipation (
101 tournament INTEGER NOT NULL REFERENCES tournaments,
102 player INTEGER NOT NULL REFERENCES players,
103 paid BOOLEAN NOT NULL,
105 PRIMARY KEY ( tournament, player )
108 CREATE TABLE tournamentrankings (
109 tournament INTEGER NOT NULL REFERENCES tournaments,
110 ranking INTEGER NOT NULL,
111 player INTEGER NOT NULL REFERENCES players,
114 UNIQUE (tournament, player),
115 PRIMARY KEY (tournament, ranking)
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,
125 PRIMARY KEY (tournament, round)
128 CREATE TABLE groups (
129 tournament INTEGER NOT NULL REFERENCES tournaments,
130 round INTEGER NOT NULL,
131 parallel INTEGER NOT NULL,
133 FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
134 PRIMARY KEY (tournament, round, parallel)
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,
143 FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
144 PRIMARY KEY (tournament, round, parallel, song)
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,
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)
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,
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')),
172 score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
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)
179 CREATE TABLE randomsongsused (
180 song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
183 CREATE SCHEMA bigscreen;
185 CREATE TABLE bigscreen.active_tournament (
186 tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
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,
196 PRIMARY KEY ( tournament, round, parallel ),
197 FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
199 CREATE TABLE bigscreen.active_screens (
200 id VARCHAR NOT NULL PRIMARY KEY