Move FetchMaxScoreForSongs into a stored procedure.

diff --git a/bigscreen/fetch_max_score_for_songs.cpp b/bigscreen/fetch_max_score_for_songs.cpp
index 4170e33ea552f3c525680ecf3657022fb231ce44..c493a67b134632077bb752de0c5ea0e4ed1e554e 100644 (file)
--- a/bigscreen/fetch_max_score_for_songs.cpp
+++ b/bigscreen/fetch_max_score_for_songs.cpp
@@ -7,9 +7,9 @@ void FetchMaxScoreForSongs::operator() (pqxx::transaction<> &t)
 {
        score->erase(score->begin(), score->end());
        
-       pqxx::result res( t.exec("SELECT song,MAX(feetrating)*1000 AS max_score FROM songratings WHERE " 
-               " machine=( SELECT machine FROM tournaments WHERE tournament=" + pqxx::to_string(tournament) + ") AND playmode='single' GROUP BY song") );
-       
+       pqxx::result res( t.exec("SELECT get_max_score_for_songs("
+               + pqxx::to_string(tournament) + ", 'single')") );
+
        for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) {
                unsigned song, max_score;
                song = i["song"].as(song);

diff --git a/sql/ccbs.sql b/sql/ccbs.sql
index 756f54c4e5c35f09eb092e7b29ee7908a396e303..4c42d56187a4c01f185ead62367b10f081503ff5 100644 (file)
--- a/sql/ccbs.sql
+++ b/sql/ccbs.sql
@@ -200,3 +200,27 @@ CREATE TABLE bigscreen.active_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;