1 package App::MPD::Feeder;
7 use App::MPD::Feeder::Options;
11 use IO::Async::Signal;
12 use Log::Any qw($log);
15 use Syntax::Keyword::Try;
18 class App::MPD::Feeder {
19 has $cfg_file :reader;
23 has $db_needs_update :writer = 1;
26 use constant DEFAULT_CONFIG_FILE => '/etc/mpd-feeder/mpd-feeder.conf';
29 Getopt::Long::Configure('pass_through');
30 Getopt::Long::GetOptions('cfg|config=s' => \$cfg_file);
31 Getopt::Long::Configure('no_pass_through');
33 $cfg_file //= DEFAULT_CONFIG_FILE if -e DEFAULT_CONFIG_FILE;
37 $db_needs_update = 0 if $opt->skip_db_update;
41 my $new_opt = App::MPD::Feeder::Options->new;
43 $new_opt->parse_config_file($cfg_file) if $cfg_file;
45 $new_opt->parse_command_line;
47 Log::Any::Adapter->set( Stderr => log_level => $new_opt->log_level );
55 my %conn = ( auto_connect => 1 );
56 $conn{host} = $opt->mpd_host if $opt->mpd_host;
57 $conn{port} = $opt->mpd_port if $opt->mpd_port;
59 $mpd = Net::Async::MPD->new(%conn);
62 IO::Async::Signal->new(
65 $log->debug("SIGHUP received. Stopping loop");
66 $mpd->loop->stop('reload');
72 IO::Async::Signal->new(
75 $log->debug("SIGUSR1 received. Dumping configuration to STDERR");
76 my $old = select \*STDERR;
91 $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
92 $opt->db_user, $opt->db_password,
93 { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
95 $log->info( "Connected to database " . $opt->db_path );
96 $db_generation = $self->db_get_option('generation');
97 $log->debug("DB generation is $db_generation");
101 method db_get_option($name) {
102 my $sth = $db->prepare_cached("select $name from options");
104 my @result = $sth->fetchrow_array;
111 method db_set_option( $name, $value ) {
112 my $sth = $db->prepare_cached("update options set $name = ?");
113 $sth->execute($value);
116 method db_store_song($song, $artist, $album) {
117 return unless length($song) and length($artist) and length($album);
120 <<'SQL')->execute( $song, $artist, $album, $db_generation );
121 INSERT INTO songs(path, artist, album, generation)
122 VALUES($1, $2, $3, $4)
123 ON CONFLICT ON CONSTRAINT songs_pkey DO
124 UPDATE SET artist = $2
128 $db->prepare_cached(<<'SQL')->execute( $artist, $album, $db_generation );
129 INSERT INTO albums(artist, album, generation)
131 ON CONFLICT ON CONSTRAINT albums_pkey DO
132 UPDATE SET generation = $3
134 $db->prepare_cached(<<'SQL')->execute( $artist, $db_generation );
135 INSERT INTO artists(artist, generation)
137 ON CONFLICT ON CONSTRAINT artists_pkey DO
138 UPDATE SET generation = $2
142 method db_remove_stale_entries {
144 $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
145 $sth->execute($db_generation);
146 $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
148 $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
149 $sth->execute($db_generation);
150 $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
153 $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
154 $sth->execute($db_generation);
155 $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
158 method db_note_song_qeued($item) {
160 'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
161 ->execute( $item->{song} );
163 'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
164 ->execute( $item->{artist} );
166 'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
167 )->execute( $item->{artist}, $item->{album} );
170 method update_db($force = undef) {
171 if (!$db_needs_update and !$force) {
172 $log->debug("Skipping DB update");
176 $log->info('Updating song database');
180 my $rows = $mpd->send('listallinfo')->get;
188 foreach my $entry (@$rows) {
189 next unless exists $entry->{file};
190 $self->db_store_song( $entry->{file},
191 $entry->{AlbumArtist} // $entry->{Artist},
196 $log->info("Updated data about $song_count songs");
198 $self->db_remove_stale_entries;
200 $self->db_set_option( generation => $db_generation );
204 $db_needs_update = 0;
217 method db_find_suitable_songs($num) {
223 SELECT s.path, s.artist, s.album
225 JOIN artists ar ON ar.artist=s.artist
226 JOIN albums al ON al.album=s.album AND al.artist=s.artist
227 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
228 AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
229 AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
230 AND NOT EXISTS (SELECT 1 FROM unwanted_artists uar WHERE uar.artist = s.artist)
231 AND NOT EXISTS (SELECT 1 FROM unwanted_albums ual WHERE ual.album = s.album)
236 $opt->min_song_interval, $opt->min_artist_interval,
237 $opt->min_album_interval, $num,
239 my $sth = $db->prepare_cached($sql);
240 $sth->execute(@params);
241 while ( my @row = $sth->fetchrow_array ) {
243 { song => $row[0], artist => $row[1], album => $row[2] };
247 if (scalar(@result) == $num and $log->is_debug) {
248 $sql =~ s/^SELECT .+$/SELECT COUNT(DISTINCT s.path)/m;
249 $sql =~ s/^ORDER BY .+$//m;
250 $sql =~ s/^LIMIT .+$//m;
251 my $sth = $db->prepare_cached($sql);
253 $sth->execute(@params);
254 my $count = ($sth->fetchrow_array)[0];
257 $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
259 my $total = ($sth->fetchrow_array)[0];
263 "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
264 $count, $total, 100.0 * $count / $total
271 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
273 $sth = $db->prepare_cached($sql);
274 $sth->execute($opt->min_song_interval);
275 $count = ($sth->fetchrow_array)[0];
280 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
281 $count, $total, 100.0 * $count / $total
289 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
291 $sth = $db->prepare_cached($sql);
292 $sth->execute($opt->min_artist_interval);
293 $count = ($sth->fetchrow_array)[0];
296 $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
298 $total = ($sth->fetchrow_array)[0];
302 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
303 $count, $total, 100.0 * $count / $total
310 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
312 $sth = $db->prepare_cached($sql);
313 $sth->execute($opt->min_album_interval);
314 $count = ($sth->fetchrow_array)[0];
317 $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
319 $total = ($sth->fetchrow_array)[0];
323 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
324 $count, $total, 100.0 * $count / $total
334 method db_add_unwanted_artist($artist) {
340 INSERT INTO unwanted_artists(artist, generation)
343 undef, $artist, $db_generation
350 $log->debug("PostgreSQL error: $err");
351 $log->debug( "SQLSTATE = " . $db->state );
352 return 0 if $db->state eq '23505';
358 method db_del_unwanted_artist($artist) {
363 DELETE FROM unwanted_artists
370 method queue_songs($num = undef, $callback = undef) {
373 $mpd->send('playlist')->on_done(
375 my $present = scalar @{ $_[0] };
377 $log->notice( "Playlist contains $present songs. Wanted: "
378 . $opt->target_queue_length );
379 if ( $present < $opt->target_queue_length ) {
381 $opt->target_queue_length - $present, $callback );
384 $callback->() if $callback;
392 my @list = $self->db_find_suitable_songs($num);
394 die "Found no suitable songs" unless @list;
396 if ( @list < $num ) {
399 'Found only %d suitable songs instead of %d',
405 $log->info("About to add $num songs to the playlist");
408 for my $song (@list) {
409 my $path = $song->{song};
414 $log->debug( "Adding " . join( ', ', map {"«$_»"} @paths ) );
417 push @commands, [ add => "\"$_\"" ];
420 my $f = $mpd->send( \@commands );
421 $f->on_fail( sub { die @_ } );
424 $self->db_note_song_qeued($_) for @list;
425 $callback->(@_) if $callback;
430 method prepare_to_wait_idle {
431 $log->trace('declaring idle mode');
432 $mpd->send('idle database playlist')->on_done(
436 if ( $result->{changed} eq 'database' ) {
437 $db_needs_update = 1;
438 $self->prepare_to_wait_idle;
440 elsif ( $result->{changed} eq 'playlist' ) {
441 $self->queue_songs( undef,
442 sub { $self->prepare_to_wait_idle } );
447 "Unknown result from idle: " . to_json($result) );
448 $self->prepare_to_wait_idle;
457 die "Connection to MPD lost";
461 $self->prepare_to_wait_idle;
468 if ($db->{ActiveKids}) {
469 $log->warn("$db->{ActiveKids} active DB statements");
470 for my $st ( @{ $db->{ChildHandles} } ) {
471 next unless $st->{Active};
472 while(my($k,$v) = each %$st) {
473 $log->debug("$k = ".($v//'<NULL>'));
487 $self->queue_songs( undef, sub { $self->run } );
489 $log->debug("Entering event loop. PID=$$");
491 my $result = $mpd->loop->run;
492 $log->trace( "Got loop result of " . ( $result // 'undef' ) );
494 if ( 'reload' eq $result ) {
495 $log->notice("disconnecting");
498 exec( "$0", '--config', $self->cfg_file, '--skip-db-update' );