Add information to the SQL schema saying when the group was last updated. Make the...
[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
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 songs,
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         last_updated TIMESTAMP NOT NULL,
179
180         PRIMARY KEY ( tournament, round, parallel ),
181         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
182 );