Add state to the random song chooser, so it doesn't choose the same songs twice....

diff --git a/html/do-start-round.pl b/html/do-start-round.pl
index ff0a4178068b46e1273794bb2d1d5981d9984e78..2637076503eddb76584864307c6f6555323f4a92 100755 (executable)
--- a/html/do-start-round.pl
+++ b/html/do-start-round.pl
@@ -49,10 +49,16 @@ for my $p (@$people) {
        }
 }
 
-# Pick random songs for the groups (FIXME: add state to the random generator later)
+# Pick random songs for the groups
 for my $g (1..$num_groups) {
        for my $s (1..$num_random) {
-               my $ref = $dbh->selectrow_hashref('SELECT * FROM songs ORDER BY random() LIMIT 1');
+               my $ref = $dbh->selectrow_hashref('SELECT * FROM machinesongs WHERE song NOT IN ( SELECT song FROM randomsongsused ) AND machine=( SELECT machine FROM tournaments WHERE tournament=? ) ORDER BY random() LIMIT 1',
+                       undef, $tournament);
+               if (!defined($ref)) {
+                       ccbs::user_error('Det er ikke flere sanger igjen i sangvelgeren!');
+               }
+               $dbh->do('INSERT INTO randomsongsused (song) VALUES (?)',
+                       undef, $ref->{'song'});
                $dbh->do('INSERT INTO roundrandomsongs (tournament, round, parallel, song) VALUES (?,?,?,?)',
                        undef, $tournament, $round, $g, $ref->{'song'});
 

diff --git a/sql/ccbs.sql b/sql/ccbs.sql
index 19eeb0e98ae55e5d70a941f8327fd0a2409e6640..d7d461fa205aa7eb8a54e97f5b9c912721132932 100644 (file)
--- a/sql/ccbs.sql
+++ b/sql/ccbs.sql
@@ -152,3 +152,7 @@ CREATE TABLE scores (
        FOREIGN KEY (tournament, round, parallel, player) REFERENCES roundparticipation (tournament, round, parallel, player),
        PRIMARY KEY (tournament, round, parallel, player, songnumber)
 );
+
+CREATE TABLE randomsongsused (
+       song INTEGER NOT NULL PRIMARY KEY REFERENCES songs
+);