From bf9387900ec73b0703128d8d2aa11996509006b8 Mon Sep 17 00:00:00 2001
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
Date: Sat, 11 Jun 2005 22:40:33 +0000
Subject: [PATCH] Move FetchMaxScoreForSongs into a stored procedure.

---
 bigscreen/fetch_max_score_for_songs.cpp |  6 +++---
 sql/ccbs.sql                            | 24 ++++++++++++++++++++++++
 2 files changed, 27 insertions(+), 3 deletions(-)

diff --git a/bigscreen/fetch_max_score_for_songs.cpp b/bigscreen/fetch_max_score_for_songs.cpp
index 4170e33..c493a67 100644
--- 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 756f54c..4c42d56 100644
--- 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;
-- 
2.39.2