Read max score for all players in one shot as well, with a new query and an index...
authorSteinar H. Gunderson <sgunderson@bigfoot.com>
Sun, 20 Feb 2005 22:47:27 +0000 (22:47 +0000)
committerSteinar H. Gunderson <sgunderson@bigfoot.com>
Sun, 20 Feb 2005 22:47:27 +0000 (22:47 +0000)
bigscreen/Makefile
bigscreen/fetch_max_score_for_player.cpp [deleted file]
bigscreen/fetch_max_score_for_player.h [deleted file]
bigscreen/fetch_max_score_for_players.cpp [new file with mode: 0644]
bigscreen/fetch_max_score_for_players.h [new file with mode: 0644]
bigscreen/groupscreen.cpp
sql/ccbs.sql

index cf4fcb9..07177b6 100644 (file)
@@ -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 (file)
index f63a582..0000000
+++ /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 (file)
index 36122b4..0000000
+++ /dev/null
@@ -1,16 +0,0 @@
-#ifndef _FETCH_MAX_SCORE_FOR_PLAYER_H
-#define _FETCH_MAX_SCORE_FOR_PLAYER_H 1
-
-#include <pqxx/transactor>
-
-/* 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 (file)
index 0000000..9cc9b2a
--- /dev/null
@@ -0,0 +1,38 @@
+#include "fetch_max_score_for_players.h"
+
+FetchMaxScoreForPlayers::FetchMaxScoreForPlayers(unsigned tournament, unsigned round, std::map<unsigned, unsigned> *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 (file)
index 0000000..e722d3c
--- /dev/null
@@ -0,0 +1,18 @@
+#ifndef _FETCH_MAX_SCORE_FOR_PLAYERS_H
+#define _FETCH_MAX_SCORE_FOR_PLAYERS_H 1
+
+#include <pqxx/transactor>
+#include <map>
+
+/* A transactor that fetches the maximum score for each player on chosen songs */
+class FetchMaxScoreForPlayers : public pqxx::transactor<> {
+private:
+       unsigned tournament, round;
+       std::map<unsigned, unsigned> *scores;
+
+public:
+       FetchMaxScoreForPlayers(unsigned tournament, unsigned round, std::map<unsigned, unsigned> *scores);
+       void operator() (pqxx::transaction<> &t);
+};
+
+#endif /* !defined(_FETCH_MAX_SCORE_FOR_PLAYERS_H) */
index fbdcc5e..7b0175a 100644 (file)
@@ -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<unsigned, unsigned> song_scores;
+       std::map<unsigned, unsigned> 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<unsigned> max_score, min_score;
        for (std::vector<Player>::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;
index 4a9f0a8..9f0cdea 100644 (file)
@@ -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,