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)
58 CREATE INDEX songratings_feetrating ON songratings ( feetrating );
60 CREATE TABLE players (
61 player SERIAL PRIMARY KEY,
62 nick VARCHAR NOT NULL,
67 CREATE TABLE seasons (
68 season SERIAL PRIMARY KEY,
69 seasonname VARCHAR NOT NULL,
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,
81 machine INTEGER NOT NULL REFERENCES machines,
82 scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
84 UNIQUE ( season, tournamentname )
87 CREATE TABLE tournamentparticipation (
88 tournament INTEGER NOT NULL REFERENCES tournaments,
89 player INTEGER NOT NULL REFERENCES players,
90 paid BOOLEAN NOT NULL,
92 PRIMARY KEY ( tournament, player )
95 CREATE TABLE tournamentrankings (
96 tournament INTEGER NOT NULL REFERENCES tournaments,
97 ranking INTEGER NOT NULL,
98 player INTEGER NOT NULL REFERENCES players,
101 UNIQUE (tournament, player),
102 PRIMARY KEY (tournament, ranking)
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,
112 PRIMARY KEY (tournament, round)
115 CREATE TABLE groups (
116 tournament INTEGER NOT NULL REFERENCES tournaments,
117 round INTEGER NOT NULL,
118 parallel INTEGER NOT NULL,
120 FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
121 PRIMARY KEY (tournament, round, parallel)
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,
130 FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
131 PRIMARY KEY (tournament, round, parallel, song)
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,
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)
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,
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')),
159 score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
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)
166 CREATE TABLE randomsongsused (
167 song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
170 CREATE SCHEMA bigscreen;
172 CREATE TABLE bigscreen.active_tournament (
173 tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments
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,
182 PRIMARY KEY ( tournament, round, parallel ),
183 FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups
185 CREATE TABLE bigscreen.active_screens (
186 id VARCHAR NOT NULL PRIMARY KEY