Added a primary key for the "active screens" table.
[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 CREATE INDEX songratings_feetrating ON songratings ( feetrating );
59
60 CREATE TABLE players (
61         player SERIAL PRIMARY KEY,
62         nick VARCHAR NOT NULL,
63
64         UNIQUE ( nick )
65 );
66
67 CREATE TABLE seasons (
68         season SERIAL PRIMARY KEY,
69         seasonname VARCHAR NOT NULL,
70
71         UNIQUE ( seasonname )
72 );
73
74 CREATE TABLE tournaments (
75         tournament SERIAL PRIMARY KEY,
76         season INTEGER NOT NULL REFERENCES seasons,
77         tournamentname VARCHAR NOT NULL,
78         country INTEGER NOT NULL REFERENCES countries,
79         location VARCHAR NOT NULL,
80         "date" DATE NOT NULL,
81         machine INTEGER NOT NULL REFERENCES machines,
82         scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
83
84         UNIQUE ( season, tournamentname )
85 );
86
87 CREATE TABLE tournamentparticipation (
88         tournament INTEGER NOT NULL REFERENCES tournaments,
89         player INTEGER NOT NULL REFERENCES players,
90         paid BOOLEAN NOT NULL,
91
92         PRIMARY KEY ( tournament, player )
93 );
94
95 CREATE TABLE tournamentrankings (
96         tournament INTEGER NOT NULL REFERENCES tournaments,
97         ranking INTEGER NOT NULL,
98         player INTEGER NOT NULL REFERENCES players,
99         points INTEGER,
100
101         UNIQUE (tournament, player),
102         PRIMARY KEY (tournament, ranking)
103 );
104
105 CREATE TABLE rounds (
106         tournament INTEGER NOT NULL REFERENCES tournaments,
107         round INTEGER NOT NULL,
108         randomsongs INTEGER NOT NULL,
109         chosensongs INTEGER NOT NULL,
110         numqualifying INTEGER,
111
112         PRIMARY KEY (tournament, round)
113 );
114
115 CREATE TABLE groups (
116         tournament INTEGER NOT NULL REFERENCES tournaments,
117         round INTEGER NOT NULL,
118         parallel INTEGER NOT NULL,
119
120         FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
121         PRIMARY KEY (tournament, round, parallel)
122 );
123
124 CREATE TABLE roundrandomsongs (
125         tournament INTEGER NOT NULL,
126         round INTEGER NOT NULL,
127         parallel INTEGER NOT NULL,
128         song INTEGER NOT NULL REFERENCES songs,
129
130         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
131         PRIMARY KEY (tournament, round, parallel, song)
132 );
133
134 CREATE TABLE roundparticipation (
135         tournament INTEGER NOT NULL,
136         round INTEGER NOT NULL,
137         parallel INTEGER NOT NULL,
138         player INTEGER NOT NULL REFERENCES players,
139         position INTEGER NOT NULL,
140
141         UNIQUE (tournament, round, player),
142         UNIQUE (tournament, round, parallel, position),
143         FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
144         PRIMARY KEY (tournament, round, parallel, player)
145 );
146
147 CREATE TABLE scores (
148         tournament INTEGER NOT NULL,
149         round INTEGER NOT NULL,
150         parallel INTEGER NOT NULL,
151         player INTEGER NOT NULL REFERENCES players,
152         songnumber INTEGER NOT NULL,
153         
154         song INTEGER REFERENCES songs,
155         playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
156         difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
157         
158         chosen BOOLEAN,
159         score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
160         
161         -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
162         FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
163         PRIMARY KEY (tournament, round, parallel, player, songnumber)
164 );
165
166 CREATE TABLE randomsongsused (
167         song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
168 );
169
170 CREATE SCHEMA bigscreen;
171
172 CREATE TABLE bigscreen.active_tournament (
173         tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
174 );
175 CREATE TABLE bigscreen.active_groups (
176         tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament,
177         round INTEGER NOT NULL,
178         parallel INTEGER NOT NULL,
179         num_machines INTEGER NOT NULL,
180         last_updated TIMESTAMP NOT NULL,
181
182         PRIMARY KEY ( tournament, round, parallel ),
183         FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
184 );
185 CREATE TABLE bigscreen.active_screens (
186         id VARCHAR NOT NULL PRIMARY KEY
187 );