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 PRIMARY KEY (tournament, player)
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,
124 PRIMARY KEY (tournament, round)
127 CREATE TABLE groups (
128 tournament INTEGER NOT NULL REFERENCES tournaments,
129 round INTEGER NOT NULL,
130 parallel INTEGER NOT NULL,
132 FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
133 PRIMARY KEY (tournament, round, parallel)
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,
142 FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
143 PRIMARY KEY (tournament, round, parallel, song)
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,
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)
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,
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')),
171 score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
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)
178 CREATE TABLE randomsongsused (
179 song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
182 CREATE SCHEMA bigscreen;
184 CREATE TABLE bigscreen.active_tournament (
185 tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
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,
195 PRIMARY KEY ( tournament, round, parallel ),
196 FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
198 CREATE TABLE bigscreen.active_screens (
199 id VARCHAR NOT NULL PRIMARY KEY