- method db_store_song($song, $artist, $album) {
- return unless length($song) and length($artist) and length($album);
-
- $db->prepare_cached(
- <<'SQL')->execute( $song, $artist, $album, $db_generation );
-INSERT INTO songs(path, artist, album, generation)
-VALUES($1, $2, $3, $4)
-ON CONFLICT ON CONSTRAINT songs_pkey DO
-UPDATE SET artist = $2
- , album = $3
- , generation = $4
-SQL
- $db->prepare_cached(<<'SQL')->execute( $artist, $album, $db_generation );
-INSERT INTO albums(artist, album, generation)
-VALUES($1, $2, $3)
-ON CONFLICT ON CONSTRAINT albums_pkey DO
-UPDATE SET generation = $3
-SQL
- $db->prepare_cached(<<'SQL')->execute( $artist, $db_generation );
-INSERT INTO artists(artist, generation)
-VALUES($1, $2)
-ON CONFLICT ON CONSTRAINT artists_pkey DO
-UPDATE SET generation = $2
-SQL
- }
-
- method db_remove_stale_entries {
- $db->prepare_cached('DELETE FROM songs WHERE generation <> ?')
- ->execute($db_generation);
- $db->prepare_cached('DELETE FROM albums WHERE generation <> ?')
- ->execute($db_generation);
- $db->prepare_cached('DELETE FROM artists WHERE generation <> ?')
- ->execute($db_generation);
- }
-
- method db_note_song_qeued($item) {
- $db->prepare_cached(
- 'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
- ->execute( $item->{song} );
- $db->prepare_cached(
- 'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
- ->execute( $item->{artist} );
- $db->prepare_cached(
- 'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
- )->execute( $item->{artist}, $item->{album} );
- }
-
- method update_db() {
- $mpd->send('listallinfo')->on_done(
- sub {
- try {
- $db->begin;
-
- $db_generation++;
-
- my ($song, $artist, $album);
-
- foreach my $row (@_) {
- chomp($row);
-
- if ($row =~ s/^file:\s*//) {
- $self->db_store_song( $song, $artist, $album );
- $song = $row;
- $artist = $album = undef;
- }
- elsif ( $row =~ s/^Artist:\s*// ) {
- $artist = $row;
- }
- elsif ( $row =~ s/^Album:\s*// ) {
- $album = $row;
- }
- }
-
- $self->db_store_song($song, $artist, $album);
-
- $self->db_remove_stale_entries;
-
- $self->db_set_option( generation => $db_generation );
-
- $db->commit;
- }
- catch {
- my $err = $@;
-
- $db_generation--;
-
- $db->rollback;
-
- die $err;
- }
- }
- );
- }
-
- method db_find_suitable_songs($num) {
- my @result;
- my $sth = $db->prepare_cached(<<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
-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 blacklisted_artists bar WHERE bar.artist = s.artist)
- AND NOT EXISTS (SELECT 1 FROM blacklisted_albums bal WHERE bal.album = s.album)
-ORDER BY random()
-LIMIT ?
-SQL
- $sth->execute(
- $self->opt->min_song_interval,
- $self->opt->min_artist_interval,
- $self->opt->min_album_interval,
- $num,
- );
- while ( my @row = $sth->fetchrow_array ) {
- push @result,
- { song => $row[0], artist => $row[1], album => $row[2] };
- }
-
- return @result;
- }
-
- method queue_songs($num = undef) {
- if (!defined $num) {
- $mpd->send('playlist')->on_done( sub {
- my $present = scalar @_;
-
- $self->queue_songs( $opt->target_queue_length - $present )
- if $present < $opt->target_queue_length;
- } );
- }
- else {
- my @list = $self->db_find_suitable_songs($num);
-
- if (@list < $num) {
- $mpd->loop->add(
- IO::Async::Timer::Countdown->new(
- delay => 15,
- on_expire => sub { $self->queue_songs($num) },
- )
- );
- }
- else {
- $mpd->send( [ map {"add $_->{song}"} @list ] );
- $self->db_note_song_qeued($_) for @list;
- }
- }
- }