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 machinesongs (
28 song INTEGER NOT NULL REFERENCES songs,
29 machine INTEGER NOT NULL REFERENCES machines,
31 PRIMARY KEY ( song, machine )
34 CREATE TABLE scoringsystems (
35 scoringsystem SERIAL PRIMARY KEY,
36 scoringsystemname VARCHAR NOT NULL,
38 UNIQUE ( scoringsystemname )
41 CREATE TABLE songratings (
42 song INTEGER NOT NULL REFERENCES songs,
43 machine INTEGER NOT NULL REFERENCES machines,
44 playmode VARCHAR NOT NULL CHECK (playmode IN ('single','double')),
45 difficulty VARCHAR NOT NULL CHECK (difficulty IN ('beginner','standard','difficult','expert','challenge')),
46 feetrating INTEGER NOT NULL CHECK (feetrating >= 0 AND feetrating <= 10),
48 PRIMARY KEY (song, machine, playmode, difficulty)
51 CREATE TABLE players (
52 player SERIAL PRIMARY KEY,
53 nick VARCHAR NOT NULL,
58 CREATE TABLE seasons (
59 season SERIAL PRIMARY KEY,
60 seasonname VARCHAR NOT NULL,
65 CREATE TABLE tournaments (
66 tournament SERIAL PRIMARY KEY,
67 season INTEGER NOT NULL REFERENCES seasons,
68 tournamentname VARCHAR NOT NULL,
69 country INTEGER NOT NULL REFERENCES countries,
70 location VARCHAR NOT NULL,
72 machine INTEGER NOT NULL REFERENCES machines,
73 scoringsystem INTEGER NOT NULL REFERENCES scoringsystems,
75 UNIQUE ( season, tournamentname )
78 CREATE TABLE tournamentparticipation (
79 tournament INTEGER NOT NULL REFERENCES tournaments,
80 player INTEGER NOT NULL REFERENCES players,
81 paid BOOLEAN NOT NULL,
83 PRIMARY KEY ( tournament, player )
86 CREATE TABLE tournamentrankings (
87 tournament INTEGER NOT NULL REFERENCES tournaments,
88 ranking INTEGER NOT NULL,
89 player INTEGER NOT NULL REFERENCES players,
92 UNIQUE (tournament, player),
93 PRIMARY KEY (tournament, ranking)
97 tournament INTEGER NOT NULL REFERENCES tournaments,
98 round INTEGER NOT NULL,
99 randomsongs INTEGER NOT NULL,
100 chosensongs INTEGER NOT NULL,
101 numqualifying INTEGER,
103 PRIMARY KEY (tournament, round)
106 CREATE TABLE groups (
107 tournament INTEGER NOT NULL REFERENCES tournaments,
108 round INTEGER NOT NULL,
109 parallel INTEGER NOT NULL,
111 FOREIGN KEY (tournament, round) REFERENCES rounds (tournament, round),
112 PRIMARY KEY (tournament, round, parallel)
115 CREATE TABLE roundrandomsongs (
116 tournament INTEGER NOT NULL,
117 round INTEGER NOT NULL,
118 parallel INTEGER NOT NULL,
119 song INTEGER NOT NULL REFERENCES songs,
121 FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
122 PRIMARY KEY (tournament, round, parallel, song)
125 CREATE TABLE roundparticipation (
126 tournament INTEGER NOT NULL,
127 round INTEGER NOT NULL,
128 parallel INTEGER NOT NULL,
129 player INTEGER NOT NULL REFERENCES players,
130 position INTEGER NOT NULL,
132 UNIQUE (tournament, round, player),
133 UNIQUE (tournament, round, parallel, position),
134 FOREIGN KEY (tournament, round, parallel) REFERENCES groups (tournament, round, parallel),
135 PRIMARY KEY (tournament, round, parallel, player)
138 CREATE TABLE scores (
139 tournament INTEGER NOT NULL,
140 round INTEGER NOT NULL,
141 parallel INTEGER NOT NULL,
142 player INTEGER NOT NULL REFERENCES players,
143 songnumber INTEGER NOT NULL,
145 song INTEGER REFERENCES songs,
146 playmode VARCHAR CHECK (playmode IS NULL OR playmode IN ('single','double')),
147 difficulty VARCHAR CHECK (difficulty IS NULL OR difficulty IN ('beginner','standard','difficult','expert','challenge')),
150 score INTEGER CHECK (score IS NULL OR (score >= 0 AND score <= 10000)),
152 -- FOREIGN KEY (song, playmode, difficulty) REFERENCES songratings (song, playmode, difficulty),
153 FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
154 PRIMARY KEY (tournament, round, parallel, player, songnumber)
157 CREATE TABLE randomsongsused (
158 song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
161 CREATE SCHEMA bigscreen;
163 CREATE TABLE bigscreen.active_tournament (
164 tournament INTEGER NOT NULL REFERENCES tournaments
168 CREATE FUNCTION plpgsql_call_handler() RETURNS opaque
169 AS '/usr/lib/postgresql/lib/plpgsql.so', 'plpgsql_call_handler'
171 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
173 -- NOTIFY active_tournament when anything has changed
174 CREATE FUNCTION notify_active_tournament() RETURNS trigger
178 NOTIFY bigscreen.active_tournament;