Make the rank function work more than once. :-)
[ccbs] / 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         countrycode CHAR(3) NOT NULL,   -- IOC country code
14
15         UNIQUE ( countryname ),
16         UNIQUE ( countrycode )
17 );
18
19 CREATE TABLE clubs (
20         club SERIAL PRIMARY KEY,
21         clubname VARCHAR NOT NULL,
22         clubcode CHAR(3) NOT NULL,
23
24         UNIQUE ( clubname ),
25         UNIQUE ( clubcode )
26 );
27
28 CREATE TABLE songs (
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,
34
35         UNIQUE ( title )
36 );
37
38 CREATE TABLE songshorttitles (
39         song INTEGER NOT NULL REFERENCES songs,
40         shorttitle VARCHAR NOT NULL,
41
42         PRIMARY KEY ( song, shorttitle ),
43         UNIQUE ( shorttitle )
44 );
45
46 CREATE TABLE machinesongs (
47         song INTEGER NOT NULL REFERENCES songs,
48         machine INTEGER NOT NULL REFERENCES machines,
49
50         PRIMARY KEY ( song, machine )
51 );
52
53 CREATE TABLE scoringsystems (
54         scoringsystem SERIAL PRIMARY KEY,
55         scoringsystemname VARCHAR NOT NULL,
56         
57         UNIQUE ( scoringsystemname )
58 );
59
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),
66
67         PRIMARY KEY (song, machine, playmode, difficulty)
68 );
69 CREATE INDEX songratings_feetrating ON songratings ( feetrating );
70
71 CREATE VIEW max_single_feetrating AS SELECT machine,song,MAX(feetrating) AS feetrating FROM songratings WHERE playmode='single' GROUP BY machine,song;
72
73 CREATE TABLE players (
74         player SERIAL PRIMARY KEY,
75         nick VARCHAR NOT NULL,
76         country INTEGER NOT NULL REFERENCES countries,
77         club INTEGER REFERENCES clubs,
78
79         UNIQUE ( nick )
80 );
81
82 CREATE TABLE seasons (
83         season SERIAL PRIMARY KEY,
84         seasonname VARCHAR NOT NULL,
85
86         UNIQUE ( seasonname )
87 );
88
89 CREATE TABLE tournaments (
90         tournament SERIAL PRIMARY KEY,
91         season INTEGER NOT NULL REFERENCES seasons,
92         tournamentname VARCHAR NOT NULL,
93         country INTEGER NOT NULL REFERENCES countries,
94         location VARCHAR NOT NULL,
95         "date" DATE NOT NULL,
96         machine INTEGER NOT NULL REFERENCES machines,
97         scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
98
99         UNIQUE ( season, tournamentname )
100 );
101
102 CREATE TABLE tournamentparticipation (
103         tournament INTEGER NOT NULL REFERENCES tournaments,
104         player INTEGER NOT NULL REFERENCES players,
105         paid BOOLEAN NOT NULL,
106
107         PRIMARY KEY ( tournament, player )
108 );
109
110 CREATE TABLE tournamentrankings (
111         tournament INTEGER NOT NULL REFERENCES tournaments,
112         ranking INTEGER NOT NULL,
113         player INTEGER NOT NULL REFERENCES players,
114         points INTEGER,
115
116         PRIMARY KEY (tournament, player)
117 );
118
119 CREATE TABLE rounds (
120         tournament INTEGER NOT NULL REFERENCES tournaments,
121         round INTEGER NOT NULL,
122         randomsongs INTEGER NOT NULL,
123         chosensongs INTEGER NOT NULL,
124         numqualifying INTEGER,
125
126         PRIMARY KEY (tournament, round)
127 );
128
129 CREATE TABLE groups (
130         tournament INTEGER NOT NULL REFERENCES tournaments,
131         round INTEGER NOT NULL,
132         parallel INTEGER NOT NULL,
133
134         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
135         PRIMARY KEY (tournament, round, parallel)
136 );
137
138 CREATE TABLE roundrandomsongs (
139         tournament INTEGER NOT NULL,
140         round INTEGER NOT NULL,
141         parallel INTEGER NOT NULL,
142         song INTEGER NOT NULL REFERENCES songs,
143
144         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
145         PRIMARY KEY (tournament, round, parallel, song)
146 );
147
148 CREATE TABLE roundparticipation (
149         tournament INTEGER NOT NULL,
150         round INTEGER NOT NULL,
151         parallel INTEGER NOT NULL,
152         player INTEGER NOT NULL REFERENCES players,
153         position INTEGER NOT NULL,
154
155         UNIQUE (tournament, round, player),
156         UNIQUE (tournament, round, parallel, position),
157         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
158         PRIMARY KEY (tournament, round, parallel, player)
159 );
160
161 CREATE TABLE scores (
162         tournament INTEGER NOT NULL,
163         round INTEGER NOT NULL,
164         parallel INTEGER NOT NULL,
165         player INTEGER NOT NULL REFERENCES players,
166         songnumber INTEGER NOT NULL,
167         
168         song INTEGER REFERENCES songs,
169         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
170         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
171         
172         chosen BOOLEAN,
173         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
174         
175         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
176         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
177         PRIMARY KEY (tournament, round, parallel, player, songnumber)
178 );
179
180 CREATE TABLE randomsongsused (
181         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
182 );
183
184 CREATE SCHEMA bigscreen;
185
186 CREATE TABLE bigscreen.active_tournament (
187         tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
188 );
189 CREATE TABLE bigscreen.active_groups (
190         tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament,
191         round INTEGER NOT NULL,
192         parallel INTEGER NOT NULL,
193         num_machines INTEGER NOT NULL,
194         players_per_machine INTEGER NOT NULL CHECK ( players_per_machine IN (1, 2) ),
195         last_updated TIMESTAMP NOT NULL,
196
197         PRIMARY KEY ( tournament, round, parallel ),
198         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
199 );
200 CREATE TABLE bigscreen.active_screens (
201         id VARCHAR NOT NULL PRIMARY KEY
202 );
203
204 -- get_max_score_for_songs(tournament, playmode)
205 CREATE TYPE max_score AS (
206         song INTEGER,
207         max_score INTEGER
208 );
209
210 CREATE FUNCTION get_max_score_for_songs(integer, varchar) RETURNS SETOF max_score
211     AS $$
212         SELECT
213             song,
214             MAX(feetrating)*1000 AS max_score
215         FROM songratings
216         WHERE
217             machine=(
218                 SELECT machine FROM tournaments WHERE tournament=$1
219             )
220             AND playmode=$2
221         GROUP BY song
222         ;
223 $$
224     LANGUAGE SQL
225     STABLE
226     RETURNS NULL ON NULL INPUT;
227
228 -- get_max_score_for_players(tournament, round, playmode)
229 CREATE FUNCTION get_max_score_for_players(integer, integer, varchar) RETURNS SETOF max_score
230     AS $$
231         SELECT player,
232           (
233              SELECT feetrating
234              FROM songratings
235              WHERE machine=(         -- only find songs on the machine we use
236                SELECT machine FROM tournaments WHERE tournament=$1
237              )
238              AND song NOT IN (       -- not a song that has been in elimination or seeding
239                SELECT song FROM scores 
240                WHERE tournament=$1
241                  AND song IS NOT NULL
242                  AND parallel=0
243                  AND chosen='f'
244              )
245              AND (player,song) NOT IN (  -- not a song the player has chosen before, or is a random song in this round
246                SELECT player,song FROM scores
247                WHERE tournament=$1
248                  AND song IS NOT NULL
249                  AND ( chosen='t' OR round=$2 )
250              )
251              AND playmode=$3
252              ORDER BY feetrating DESC LIMIT 1
253           ) * 1000 AS max_score FROM tournamentparticipation;
254 $$
255     LANGUAGE SQL
256     STABLE
257     RETURNS NULL ON NULL INPUT;
258
259 -- Find out, for each player in the group, what the maximum remaining score
260 -- possibly can be, using get_max_score_for_{songs,players} (the first for
261 -- selected but not played songs, the second for random songs that have not
262 -- been chosen yet).
263 --
264 -- This doesn't solve problems we'd face with more than one chosen song, but it
265 -- should be good enough.
266
267 CREATE TYPE minmax_score AS (
268         player INTEGER,
269         min_score INTEGER,
270         max_score INTEGER
271 );
272
273 -- get_minmax_score_for_players(tournament, round, playmode)
274 CREATE FUNCTION get_minmax_score_for_players(integer, integer, varchar) RETURNS SETOF minmax_score
275     AS $$
276 DECLARE
277         ret minmax_score;
278         tp RECORD;
279         ts RECORD;
280 BEGIN
281         FOR tp IN SELECT player FROM roundparticipation WHERE tournament=$1 AND round=$2 LOOP
282                 ret.player := tp.player;
283                 ret.min_score := 0;
284                 ret.max_score := 0;
285
286                 FOR ts IN SELECT song,score FROM scores WHERE tournament=$1 and round=$2 and player=tp.player LOOP
287                         IF ts.score IS NOT NULL THEN
288                                 -- score is already given
289                                 ret.min_score := ret.min_score + ts.score;
290                                 ret.max_score := ret.max_score + ts.score;
291                         ELSE
292                                 IF ts.song IS NOT NULL THEN
293                                         ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_songs($1, $3) WHERE song=ts.song );
294                                 ELSE
295                                         ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_players($1, $3) WHERE song=tp.player );
296                                 END IF;
297                         END IF;
298                 END LOOP;
299
300                 RETURN NEXT ret;
301         END LOOP;
302         RETURN;
303 END;
304 $$
305     LANGUAGE plpgsql
306     STABLE
307     RETURNS NULL ON NULL INPUT;
308
309 -- use the minmax_score as a basis for finding best and worst possible ranks
310 CREATE TYPE minmax_rank AS (
311         player INTEGER,
312         best_rank INTEGER,
313         worst_rank INTEGER
314 );
315
316 -- get_minmax_score_for_players(tournament, round, playmode)
317 CREATE TABLE temp_minmax (
318         player INTEGER,
319         min_score INTEGER,
320         max_score INTEGER
321 );
322
323 CREATE FUNCTION get_minmax_rank_for_players(integer, integer, varchar) RETURNS SETOF minmax_rank
324     AS $$
325 DECLARE
326         ret minmax_rank;
327         tp RECORD;
328 BEGIN
329         INSERT INTO temp_minmax SELECT * FROM get_minmax_score_for_players($1, $2, $3);
330         
331         FOR tp IN SELECT * FROM temp_minmax LOOP
332                 ret.player = tp.player;
333                 ret.best_rank = 1 + ( SELECT COUNT(*) FROM temp_minmax WHERE min_score >= tp.max_score AND player<>tp.player);
334                 ret.worst_rank = 1 + ( SELECT COUNT(*) FROM temp_minmax WHERE max_score > tp.min_score AND player<>tp.player );
335
336                 RETURN NEXT ret;
337         END LOOP;
338         TRUNCATE temp_minmax;
339         RETURN;
340 END;
341     $$
342     LANGUAGE plpgsql
343     VOLATILE    -- not really, but needed for the TEMPORARY TABLE
344     RETURNS NULL ON NULL INPUT;
345