-
- $db_generation--;
-
- $db->rollback;
-
- die $err;
- }
- }
-
- method db_find_suitable_songs($num) {
- $self->connect_db;
- $self->update_db;
-
- 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;
- }
-
- return @result;
- }
-
- method db_add_unwanted_artist($artist) {
- $self->connect_db;
-
- try {
- $db->do(
- <<'SQL',
-INSERT INTO unwanted_artists(artist, generation)
-VALUES($1, $2)
-SQL
- undef, $artist, $db_generation
- );
- return 1;
- }
- catch {
- my $err = $@;
-
- $log->debug("PostgreSQL error: $err");
- $log->debug( "SQLSTATE = " . $db->state );
- return 0 if $db->state eq '23505';
-