From: Steinar H. Gunderson Date: Sun, 20 Feb 2005 22:47:27 +0000 (+0000) Subject: Read max score for all players in one shot as well, with a new query and an index... X-Git-Url: https://git.sesse.net/?p=ccbs;a=commitdiff_plain;h=8963fce7337a09bdcfea051a81c0c3d9281acd6e;hp=57c939a53665f808b2429461d547271adf2c5908 Read max score for all players in one shot as well, with a new query and an index which speeds up that query a lot. --- diff --git a/bigscreen/Makefile b/bigscreen/Makefile index cf4fcb9..07177b6 100644 --- a/bigscreen/Makefile +++ b/bigscreen/Makefile @@ -4,7 +4,7 @@ CPPFLAGS=-I/usr/include/postgresql $(shell freetype-config --cflags) -Itinyptc/ CXXFLAGS=-g -Wall LDFLAGS=-L/usr/X11R6/lib LIBS=$(shell freetype-config --libs) $(shell libpq3-config) -lpqxx tinyptc/libtinyptc.a -lX11 -CCBS_BIGSCREEN_OBJS=ccbs_bigscreen.o flagtrigger.o widestring.o fetch_current_tournament.o fetch_list_of_active_groups.o fetch_max_score_for_songs.o fetch_max_score_for_player.o fetch_group.o fetch_needs_update.o fetch_highscore.o fonts.o groupscreen.o splitscreen.o rotatescreen.o screen.o +CCBS_BIGSCREEN_OBJS=ccbs_bigscreen.o flagtrigger.o widestring.o fetch_current_tournament.o fetch_list_of_active_groups.o fetch_max_score_for_songs.o fetch_max_score_for_players.o fetch_group.o fetch_needs_update.o fetch_highscore.o fonts.o groupscreen.o splitscreen.o rotatescreen.o screen.o all: ccbs-bigscreen diff --git a/bigscreen/fetch_max_score_for_player.cpp b/bigscreen/fetch_max_score_for_player.cpp deleted file mode 100644 index f63a582..0000000 --- a/bigscreen/fetch_max_score_for_player.cpp +++ /dev/null @@ -1,14 +0,0 @@ -#include "fetch_max_score_for_player.h" - -FetchMaxScoreForPlayer::FetchMaxScoreForPlayer(unsigned tournament, unsigned player, unsigned round, unsigned *score) - : tournament(tournament), player(player), round(round), score(score) {} - -void FetchMaxScoreForPlayer::operator() (pqxx::transaction<> &t) -{ - pqxx::result res( t.exec("SELECT MAX(feetrating)*1000 AS max_score FROM songratings WHERE " - "machine=( SELECT machine FROM tournaments WHERE tournament=" + pqxx::to_string(tournament) + ") AND " - "song NOT IN ( SELECT song FROM scores WHERE tournament=" + pqxx::to_string(tournament) + " AND song IS NOT NULL AND ( " - "parallel=0 OR ( player=" + pqxx::to_string(player) + " AND ( chosen='t' OR round=" + pqxx::to_string(round) + " ) ) ) )") ); - - *score = res.at(0)["max_score"].as(*score); -} diff --git a/bigscreen/fetch_max_score_for_player.h b/bigscreen/fetch_max_score_for_player.h deleted file mode 100644 index 36122b4..0000000 --- a/bigscreen/fetch_max_score_for_player.h +++ /dev/null @@ -1,16 +0,0 @@ -#ifndef _FETCH_MAX_SCORE_FOR_PLAYER_H -#define _FETCH_MAX_SCORE_FOR_PLAYER_H 1 - -#include - -/* A transactor that fetches the maximum score for a song */ -class FetchMaxScoreForPlayer : public pqxx::transactor<> { -private: - unsigned tournament, player, round, *score; - -public: - FetchMaxScoreForPlayer(unsigned tournament, unsigned player, unsigned round, unsigned *score); - void operator() (pqxx::transaction<> &t); -}; - -#endif /* !defined(_FETCH_MAX_SCORE_FOR_PLAYER_H) */ diff --git a/bigscreen/fetch_max_score_for_players.cpp b/bigscreen/fetch_max_score_for_players.cpp new file mode 100644 index 0000000..9cc9b2a --- /dev/null +++ b/bigscreen/fetch_max_score_for_players.cpp @@ -0,0 +1,38 @@ +#include "fetch_max_score_for_players.h" + +FetchMaxScoreForPlayers::FetchMaxScoreForPlayers(unsigned tournament, unsigned round, std::map *scores) + : tournament(tournament), round(round), scores(scores) {} + +void FetchMaxScoreForPlayers::operator() (pqxx::transaction<> &t) +{ + scores->erase(scores->begin(), scores->end()); + + pqxx::result res( t.exec( + "SELECT player," + " (" + " SELECT feetrating" + " FROM songratings" + " WHERE machine=( SELECT machine FROM tournaments WHERE tournament=" + pqxx::to_string(tournament) + " ) " // only find songs on the machine we use + " AND song NOT IN (" // not a song that has been in elimination or seeding + " SELECT song FROM scores " + " WHERE tournament=" + pqxx::to_string(tournament) + + " AND song IS NOT NULL" + " AND parallel=0" + " )" + " 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=" + pqxx::to_string(tournament) + + " AND song IS NOT NULL" + + " AND ( chosen='t' OR round=" + pqxx::to_string(round) + " )" + " )" + " ORDER BY feetrating DESC LIMIT 1" + " ) * 1000 AS max_score FROM tournamentparticipation") ); + + for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) { + unsigned player, max_score; + player = i["player"].as(player); + max_score = i["max_score"].as(max_score); + + scores->insert(std::make_pair(player, max_score)); + } +} diff --git a/bigscreen/fetch_max_score_for_players.h b/bigscreen/fetch_max_score_for_players.h new file mode 100644 index 0000000..e722d3c --- /dev/null +++ b/bigscreen/fetch_max_score_for_players.h @@ -0,0 +1,18 @@ +#ifndef _FETCH_MAX_SCORE_FOR_PLAYERS_H +#define _FETCH_MAX_SCORE_FOR_PLAYERS_H 1 + +#include +#include + +/* A transactor that fetches the maximum score for each player on chosen songs */ +class FetchMaxScoreForPlayers : public pqxx::transactor<> { +private: + unsigned tournament, round; + std::map *scores; + +public: + FetchMaxScoreForPlayers(unsigned tournament, unsigned round, std::map *scores); + void operator() (pqxx::transaction<> &t); +}; + +#endif /* !defined(_FETCH_MAX_SCORE_FOR_PLAYERS_H) */ diff --git a/bigscreen/groupscreen.cpp b/bigscreen/groupscreen.cpp index fbdcc5e..7b0175a 100644 --- a/bigscreen/groupscreen.cpp +++ b/bigscreen/groupscreen.cpp @@ -5,7 +5,7 @@ #include "groupscreen.h" #include "fetch_group.h" #include "fetch_max_score_for_songs.h" -#include "fetch_max_score_for_player.h" +#include "fetch_max_score_for_players.h" #include "fetch_needs_update.h" #include "fetch_highscore.h" #include "fonts.h" @@ -45,8 +45,9 @@ void GroupScreen::draw(unsigned char *buf) * We'll probably need some values from here later on (although not all), just fetch them * all while we're at it. */ - std::map song_scores; + std::map song_scores, player_scores; conn.perform(FetchMaxScoreForSongs(tournament, &song_scores)); + conn.perform(FetchMaxScoreForPlayers(tournament, round, &player_scores)); Group group; conn.perform(FetchGroup(tournament, round, parallel, &group)); @@ -211,8 +212,6 @@ void GroupScreen::draw(unsigned char *buf) * matter of sorting; the best attainable rank for player X is obtained if * X gets max score and all others get min score, the worst attainable rank * is obtained if X gets min score and all others get max score. - * - * This is a bit SQL-heavy, but heck... */ std::vector max_score, min_score; for (std::vector::const_iterator i = group.players.begin(); i != group.players.end(); ++i) { @@ -228,7 +227,7 @@ void GroupScreen::draw(unsigned char *buf) // random song, or we know what song the player picked max_score_this_song = song_scores[j->song.id]; } else { - conn.perform(FetchMaxScoreForPlayer(tournament, i->id, round, &max_score_this_song)); + max_score_this_song = player_scores[i->id]; } max_score_tp += max_score_this_song; } @@ -360,7 +359,7 @@ void GroupScreen::draw(unsigned char *buf) // random song, or we know what song the player picked max_score_this_song = song_scores[next_song->song.id]; } else { - conn.perform(FetchMaxScoreForPlayer(tournament, next_player->id, round, &max_score_this_song)); + max_score_this_song = player_scores[next_player->id]; } unsigned y = 520; diff --git a/sql/ccbs.sql b/sql/ccbs.sql index 4a9f0a8..9f0cdea 100644 --- a/sql/ccbs.sql +++ b/sql/ccbs.sql @@ -55,6 +55,7 @@ CREATE TABLE songratings ( PRIMARY KEY (song, machine, playmode, difficulty) ); +CREATE INDEX songratings_feetrating ON songratings ( feetrating ); CREATE TABLE players ( player SERIAL PRIMARY KEY,