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,
14 UNIQUE ( countryname )
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,
27 CREATE TABLE songshorttitles (
28 song INTEGER NOT NULL REFERENCES songs,
29 shorttitle VARCHAR NOT NULL,
31 PRIMARY KEY ( song, shorttitle ),
35 CREATE TABLE machinesongs (
36 song INTEGER NOT NULL REFERENCES songs,
37 machine INTEGER NOT NULL REFERENCES machines,
39 PRIMARY KEY ( song, machine )
42 CREATE TABLE scoringsystems (
43 scoringsystem SERIAL PRIMARY KEY,
44 scoringsystemname VARCHAR NOT NULL,
46 UNIQUE ( scoringsystemname )
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),
56 PRIMARY KEY (song, machine, playmode, difficulty)
59 CREATE TABLE players (
60 player SERIAL PRIMARY KEY,
61 nick VARCHAR NOT NULL,
66 CREATE TABLE seasons (
67 season SERIAL PRIMARY KEY,
68 seasonname VARCHAR NOT NULL,
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,
80 machine INTEGER NOT NULL REFERENCES machines,
81 scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
83 UNIQUE ( season, tournamentname )
86 CREATE TABLE tournamentparticipation (
87 tournament INTEGER NOT NULL REFERENCES tournaments,
88 player INTEGER NOT NULL REFERENCES players,
89 paid BOOLEAN NOT NULL,
91 PRIMARY KEY ( tournament, player )
94 CREATE TABLE tournamentrankings (
95 tournament INTEGER NOT NULL REFERENCES tournaments,
96 ranking INTEGER NOT NULL,
97 player INTEGER NOT NULL REFERENCES players,
100 UNIQUE (tournament, player),
101 PRIMARY KEY (tournament, ranking)
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,
111 PRIMARY KEY (tournament, round)
114 CREATE TABLE groups (
115 tournament INTEGER NOT NULL REFERENCES tournaments,
116 round INTEGER NOT NULL,
117 parallel INTEGER NOT NULL,
119 FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
120 PRIMARY KEY (tournament, round, parallel)
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,
129 FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
130 PRIMARY KEY (tournament, round, parallel, song)
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,
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)
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,
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')),
158 score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
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)
165 CREATE TABLE randomsongsused (
166 song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
169 CREATE SCHEMA bigscreen;
171 CREATE TABLE bigscreen.active_tournament (
172 tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
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,
180 PRIMARY KEY ( tournament, round, parallel ),
181 FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups