my $sth = $db->prepare_cached("select $name from options");
$sth->execute;
my @result = $sth->fetchrow_array;
+ $sth->finish;
+ undef $sth;
return $result[0];
}
foreach my $entry (@$rows) {
next unless exists $entry->{file};
$self->db_store_song( $entry->{file},
- $entry->{AlbumArtist}, $entry->{Album} );
+ $entry->{AlbumArtist} // $entry->{Artist},
+ $entry->{Album} );
$song_count++;
}
$self->update_db;
my @result;
- my $sth = $db->prepare_cached(<<SQL);
+ 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
+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)
ORDER BY random()
LIMIT ?
SQL
- $sth->execute(
- $opt->min_song_interval,
- $opt->min_artist_interval,
- $opt->min_album_interval,
- $num,
+ 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;
+ $log->debug($sql);
+ 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];
+ $log->debug(
+ sprintf(
+ "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
+ $count, $total, 100.0 * $count / $total
+ )
+ );
+ $sth->finish;
+
+ $sql = <<SQL;
+SELECT COUNT(*)
+FROM songs s
+WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
+UNION
+SELECT COUNT(*)
+FROM songs
+SQL
+ $sth = $db->prepare_cached($sql);
+ $sth->execute($opt->min_song_interval);
+ $count = ($sth->fetchrow_array)[0];
+ $total = ($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)
+UNION
+SELECT COUNT(*)
+FROM artists
+SQL
+ $sth = $db->prepare_cached($sql);
+ $sth->execute($opt->min_artist_interval);
+ $count = ($sth->fetchrow_array)[0];
+ $total = ($sth->fetchrow_array)[0];
+ $log->debug(
+ sprintf(
+ "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
+ $count, $total, 100.0 * $count / $total
+ )
+ );
+ $sth->finish;
+
+ $sql = <<SQL;
+SELECT COUNT(*)
+FROM albums al
+WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
+UNION
+SELECT COUNT(*)
+FROM albums
+SQL
+ $sth = $db->prepare_cached($sql);
+ $sth->execute($opt->min_album_interval);
+ $count = ($sth->fetchrow_array)[0];
+ $total = ($sth->fetchrow_array)[0];
+ $log->debug(
+ sprintf(
+ "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
+ $count, $total, 100.0 * $count / $total
+ )
+ );
+ $sth->finish;
+
+ undef $sth;
+ }
return @result;
}
}
}
+$feeder->connect_db;
+
for ( ;; ) {
$feeder->queue_songs( undef, sub { $feeder->run } );