- my @result;
- my $sql = <<SQL;
-SELECT s.path, s.artist, s.album
-FROM songs s
-JOIN artists ar ON ar.artist=s.artist
-JOIN albums al ON al.album=s.album AND al.artist=s.artist
-WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
- AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
- AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
- AND NOT EXISTS (SELECT 1 FROM unwanted_artists uar WHERE uar.artist = s.artist)
- AND NOT EXISTS (SELECT 1 FROM unwanted_albums ual WHERE ual.album = s.album)
-ORDER BY random()
-LIMIT ?
-SQL
- my @params = (
- $opt->min_song_interval, $opt->min_artist_interval,
- $opt->min_album_interval, $num,
- );
- my $sth = $db->prepare_cached($sql);
- $sth->execute(@params);
- while ( my @row = $sth->fetchrow_array ) {
- push @result,
- { song => $row[0], artist => $row[1], album => $row[2] };
- }
- undef $sth;
-
- if (scalar(@result) == $num and $log->is_debug) {
- $sql =~ s/^SELECT .+$/SELECT COUNT(DISTINCT s.path)/m;
- $sql =~ s/^ORDER BY .+$//m;
- $sql =~ s/^LIMIT .+$//m;
- my $sth = $db->prepare_cached($sql);
- pop @params;
- $sth->execute(@params);
- my $count = ($sth->fetchrow_array)[0];
- $sth->finish;
-
- $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
- $sth->execute;
- my $total = ($sth->fetchrow_array)[0];
- $sth->finish;
- $log->debug(
- sprintf(
- "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
- $count, $total, 100.0 * $count / $total
- )
- );
-
- $sql = <<SQL;
-SELECT COUNT(*)
-FROM songs s
-WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
-SQL
- $sth = $db->prepare_cached($sql);
- $sth->execute($opt->min_song_interval);
- $count = ($sth->fetchrow_array)[0];
- $sth->finish;
-
- $log->debug(
- sprintf(
- "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
- $count, $total, 100.0 * $count / $total
- )
- );
- $sth->finish;
-
- $sql = <<SQL;
-SELECT COUNT(*)
-FROM artists ar
-WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
-SQL
- $sth = $db->prepare_cached($sql);
- $sth->execute($opt->min_artist_interval);
- $count = ($sth->fetchrow_array)[0];
- $sth->finish;
-
- $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
- $sth->execute;
- $total = ($sth->fetchrow_array)[0];
- $sth->finish;
- $log->debug(
- sprintf(
- "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
- $count, $total, 100.0 * $count / $total
- )
- );
-
- $sql = <<SQL;
-SELECT COUNT(*)
-FROM albums al
-WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
-SQL
- $sth = $db->prepare_cached($sql);
- $sth->execute($opt->min_album_interval);
- $count = ($sth->fetchrow_array)[0];
- $sth->finish;
-
- $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
- $sth->execute;
- $total = ($sth->fetchrow_array)[0];
- $sth->finish;
- $log->debug(
- sprintf(
- "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
- $count, $total, 100.0 * $count / $total
- )
- );
-
- undef $sth;