X-Git-Url: https://git.sesse.net/?p=ccbs;a=blobdiff_plain;f=sql%2Fccbs.sql;h=6a96a208b218c3f0921255f5c892d742ab08ca40;hp=bc4b0e45287b35cf3af4a1a11dd6e503898d29ae;hb=e94a90a317b44352b4e282a9ce6290e698ef87ff;hpb=6c49eba2219d2af1a5ffd918ab853443d9c48b42 diff --git a/sql/ccbs.sql b/sql/ccbs.sql index bc4b0e4..6a96a20 100644 --- a/sql/ccbs.sql +++ b/sql/ccbs.sql @@ -10,8 +10,19 @@ CREATE TABLE machines ( CREATE TABLE countries ( country SERIAL PRIMARY KEY, countryname VARCHAR NOT NULL, + countrycode CHAR(3) NOT NULL, -- IOC country code - UNIQUE ( countryname ) + UNIQUE ( countryname ), + UNIQUE ( countrycode ) +); + +CREATE TABLE clubs ( + club SERIAL PRIMARY KEY, + clubname VARCHAR NOT NULL, + clubcode CHAR(3) NOT NULL, + + UNIQUE ( clubname ), + UNIQUE ( clubcode ) ); CREATE TABLE songs ( @@ -24,6 +35,14 @@ CREATE TABLE songs ( UNIQUE ( title ) ); +CREATE TABLE songshorttitles ( + song INTEGER NOT NULL REFERENCES songs, + shorttitle VARCHAR NOT NULL, + + PRIMARY KEY ( song, shorttitle ), + UNIQUE ( shorttitle ) +); + CREATE TABLE machinesongs ( song INTEGER NOT NULL REFERENCES songs, machine INTEGER NOT NULL REFERENCES machines, @@ -47,10 +66,15 @@ CREATE TABLE songratings ( PRIMARY KEY (song, machine, playmode, difficulty) ); +CREATE INDEX songratings_feetrating ON songratings ( feetrating ); + +CREATE VIEW max_single_feetrating AS SELECT machine,song,MAX(feetrating) AS feetrating FROM songratings WHERE playmode='single' GROUP BY machine,song; CREATE TABLE players ( player SERIAL PRIMARY KEY, nick VARCHAR NOT NULL, + country INTEGER NOT NULL REFERENCES countries, + club INTEGER REFERENCES clubs, UNIQUE ( nick ) ); @@ -89,8 +113,7 @@ CREATE TABLE tournamentrankings ( player INTEGER NOT NULL REFERENCES players, points INTEGER, - UNIQUE (tournament, player), - PRIMARY KEY (tournament, ranking) + PRIMARY KEY (tournament, player) ); CREATE TABLE rounds ( @@ -157,3 +180,166 @@ CREATE TABLE scores ( CREATE TABLE randomsongsused ( song INTEGER NOT NULL PRIMARY KEY REFERENCES songs ); + +CREATE SCHEMA bigscreen; + +CREATE TABLE bigscreen.active_tournament ( + tournament INTEGER NOT NULL PRIMARY KEY REFERENCES tournaments +); +CREATE TABLE bigscreen.active_groups ( + tournament INTEGER NOT NULL REFERENCES bigscreen.active_tournament, + round INTEGER NOT NULL, + parallel INTEGER NOT NULL, + num_machines INTEGER NOT NULL, + players_per_machine INTEGER NOT NULL CHECK ( players_per_machine IN (1, 2) ), + last_updated TIMESTAMP NOT NULL, + + PRIMARY KEY ( tournament, round, parallel ), + FOREIGN KEY ( tournament, round, parallel ) REFERENCES groups +); +CREATE TABLE bigscreen.active_screens ( + id VARCHAR NOT NULL PRIMARY KEY +); + +-- get_max_score_for_songs(tournament, playmode) +CREATE TYPE max_score AS ( + song INTEGER, + max_score INTEGER +); + +CREATE FUNCTION get_max_score_for_songs(integer, varchar) RETURNS SETOF max_score + AS $$ + SELECT + song, + MAX(feetrating)*1000 AS max_score + FROM songratings + WHERE + machine=( + SELECT machine FROM tournaments WHERE tournament=$1 + ) + AND playmode=$2 + GROUP BY song + ; +$$ + LANGUAGE SQL + STABLE + RETURNS NULL ON NULL INPUT; + +-- get_max_score_for_players(tournament, round, playmode) +CREATE FUNCTION get_max_score_for_players(integer, integer, varchar) RETURNS SETOF max_score + AS $$ + SELECT player, + ( + SELECT feetrating + FROM songratings + WHERE machine=( -- only find songs on the machine we use + SELECT machine FROM tournaments WHERE tournament=$1 + ) + AND song NOT IN ( -- not a song that has been in elimination or seeding + SELECT song FROM scores + WHERE tournament=$1 + AND song IS NOT NULL + AND parallel=0 + AND chosen='f' + ) + AND (player,song) NOT IN ( -- not a song the player has chosen before, or is a random song in this round + SELECT player,song FROM scores + WHERE tournament=$1 + AND song IS NOT NULL + AND ( chosen='t' OR round=$2 ) + ) + AND playmode=$3 + ORDER BY feetrating DESC LIMIT 1 + ) * 1000 AS max_score FROM tournamentparticipation; +$$ + LANGUAGE SQL + STABLE + RETURNS NULL ON NULL INPUT; + +-- Find out, for each player in the group, what the maximum remaining score +-- possibly can be, using get_max_score_for_{songs,players} (the first for +-- selected but not played songs, the second for random songs that have not +-- been chosen yet). +-- +-- This doesn't solve problems we'd face with more than one chosen song, but it +-- should be good enough. + +CREATE TYPE minmax_score AS ( + player INTEGER, + min_score INTEGER, + max_score INTEGER +); + +-- get_minmax_score_for_players(tournament, round, playmode) +CREATE FUNCTION get_minmax_score_for_players(integer, integer, varchar) RETURNS SETOF minmax_score + AS $$ +DECLARE + ret minmax_score; + tp RECORD; + ts RECORD; +BEGIN + FOR tp IN SELECT player FROM roundparticipation WHERE tournament=$1 AND round=$2 LOOP + ret.player := tp.player; + ret.min_score := 0; + ret.max_score := 0; + + FOR ts IN SELECT song,score FROM scores WHERE tournament=$1 and round=$2 and player=tp.player LOOP + IF ts.score IS NOT NULL THEN + -- score is already given + ret.min_score := ret.min_score + ts.score; + ret.max_score := ret.max_score + ts.score; + ELSE + IF ts.song IS NOT NULL THEN + ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_songs($1, $3) WHERE song=ts.song ); + ELSE + ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_players($1, $3) WHERE song=tp.player ); + END IF; + END IF; + END LOOP; + + RETURN NEXT ret; + END LOOP; + RETURN; +END; +$$ + LANGUAGE plpgsql + STABLE + RETURNS NULL ON NULL INPUT; + +-- use the minmax_score as a basis for finding best and worst possible ranks +CREATE TYPE minmax_rank AS ( + player INTEGER, + best_rank INTEGER, + worst_rank INTEGER +); + +-- get_minmax_score_for_players(tournament, round, playmode) +CREATE TABLE temp_minmax ( + player INTEGER, + min_score INTEGER, + max_score INTEGER +); + +CREATE FUNCTION get_minmax_rank_for_players(integer, integer, varchar) RETURNS SETOF minmax_rank + AS $$ +DECLARE + ret minmax_rank; + tp RECORD; +BEGIN + INSERT INTO temp_minmax SELECT * FROM get_minmax_score_for_players($1, $2, $3); + + FOR tp IN SELECT * FROM temp_minmax LOOP + ret.player = tp.player; + ret.best_rank = 1 + ( SELECT COUNT(*) FROM temp_minmax WHERE min_score >= tp.max_score AND player<>tp.player); + ret.worst_rank = 1 + ( SELECT COUNT(*) FROM temp_minmax WHERE max_score > tp.min_score AND player<>tp.player ); + + RETURN NEXT ret; + END LOOP; + TRUNCATE temp_minmax; + RETURN; +END; + $$ + LANGUAGE plpgsql + VOLATILE -- not really, but needed for the TEMPORARY TABLE + RETURNS NULL ON NULL INPUT; +