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("SIGTERM received. Stopping loop");
66 $mpd->loop->stop('quit');
72 IO::Async::Signal->new(
75 $log->debug("SIGINT received. Stopping loop");
76 $mpd->loop->stop('quit');
82 IO::Async::Signal->new(
85 $log->debug("SIGHUP received. Stopping loop");
86 $mpd->loop->stop('reload');
92 IO::Async::Signal->new(
95 $log->debug("SIGUSR1 received. Dumping configuration to STDERR");
96 my $old = select \*STDERR;
111 $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
112 $opt->db_user, $opt->db_password,
113 { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
115 $log->info( "Connected to database " . $opt->db_path );
116 $db_generation = $self->db_get_option('generation');
117 $log->debug("DB generation is $db_generation");
121 method db_get_option($name) {
122 my $sth = $db->prepare_cached("select $name from options");
124 my @result = $sth->fetchrow_array;
131 method db_set_option( $name, $value ) {
132 my $sth = $db->prepare_cached("update options set $name = ?");
133 $sth->execute($value);
136 method db_store_song($song, $artist, $album) {
137 return unless length($song) and length($artist) and length($album);
140 <<'SQL')->execute( $song, $artist, $album, $db_generation );
141 INSERT INTO songs(path, artist, album, generation)
142 VALUES($1, $2, $3, $4)
143 ON CONFLICT ON CONSTRAINT songs_pkey DO
144 UPDATE SET artist = $2
148 $db->prepare_cached(<<'SQL')->execute( $artist, $album, $db_generation );
149 INSERT INTO albums(artist, album, generation)
151 ON CONFLICT ON CONSTRAINT albums_pkey DO
152 UPDATE SET generation = $3
154 $db->prepare_cached(<<'SQL')->execute( $artist, $db_generation );
155 INSERT INTO artists(artist, generation)
157 ON CONFLICT ON CONSTRAINT artists_pkey DO
158 UPDATE SET generation = $2
162 method db_remove_stale_entries {
164 $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
165 $sth->execute($db_generation);
166 $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
168 $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
169 $sth->execute($db_generation);
170 $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
173 $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
174 $sth->execute($db_generation);
175 $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
178 method db_note_song_qeued($item) {
180 'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
181 ->execute( $item->{song} );
183 'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
184 ->execute( $item->{artist} );
186 'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
187 )->execute( $item->{artist}, $item->{album} );
190 method update_db($force = undef) {
191 if (!$db_needs_update and !$force) {
192 $log->debug("Skipping DB update");
196 $log->info('Updating song database');
200 my $rows = $mpd->send('listallinfo')->get;
208 foreach my $entry (@$rows) {
209 next unless exists $entry->{file};
210 $self->db_store_song( $entry->{file},
211 $entry->{AlbumArtist} // $entry->{Artist},
216 $log->info("Updated data about $song_count songs");
218 $self->db_remove_stale_entries;
220 $self->db_set_option( generation => $db_generation );
224 $db_needs_update = 0;
237 method db_find_suitable_songs($num) {
243 SELECT s.path, s.artist, s.album
245 JOIN artists ar ON ar.artist=s.artist
246 JOIN albums al ON al.album=s.album AND al.artist=s.artist
247 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
248 AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
249 AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
250 AND NOT EXISTS (SELECT 1 FROM unwanted_artists uar WHERE uar.artist = s.artist)
251 AND NOT EXISTS (SELECT 1 FROM unwanted_albums ual WHERE ual.album = s.album)
256 $opt->min_song_interval, $opt->min_artist_interval,
257 $opt->min_album_interval, $num,
259 my $sth = $db->prepare_cached($sql);
260 $sth->execute(@params);
261 while ( my @row = $sth->fetchrow_array ) {
263 { song => $row[0], artist => $row[1], album => $row[2] };
267 if (scalar(@result) == $num and $log->is_debug) {
268 $sql =~ s/^SELECT .+$/SELECT COUNT(DISTINCT s.path)/m;
269 $sql =~ s/^ORDER BY .+$//m;
270 $sql =~ s/^LIMIT .+$//m;
271 my $sth = $db->prepare_cached($sql);
273 $sth->execute(@params);
274 my $count = ($sth->fetchrow_array)[0];
277 $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
279 my $total = ($sth->fetchrow_array)[0];
283 "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
284 $count, $total, 100.0 * $count / $total
291 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
293 $sth = $db->prepare_cached($sql);
294 $sth->execute($opt->min_song_interval);
295 $count = ($sth->fetchrow_array)[0];
300 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
301 $count, $total, 100.0 * $count / $total
309 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
311 $sth = $db->prepare_cached($sql);
312 $sth->execute($opt->min_artist_interval);
313 $count = ($sth->fetchrow_array)[0];
316 $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
318 $total = ($sth->fetchrow_array)[0];
322 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
323 $count, $total, 100.0 * $count / $total
330 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
332 $sth = $db->prepare_cached($sql);
333 $sth->execute($opt->min_album_interval);
334 $count = ($sth->fetchrow_array)[0];
337 $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
339 $total = ($sth->fetchrow_array)[0];
343 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
344 $count, $total, 100.0 * $count / $total
354 method db_add_unwanted_artist($artist) {
360 INSERT INTO unwanted_artists(artist, generation)
363 undef, $artist, $db_generation
370 $log->debug("PostgreSQL error: $err");
371 $log->debug( "SQLSTATE = " . $db->state );
372 return 0 if $db->state eq '23505';
378 method db_del_unwanted_artist($artist) {
383 DELETE FROM unwanted_artists
390 method queue_songs($num = undef, $callback = undef) {
393 $mpd->send('playlist')->on_done(
395 my $present = scalar @{ $_[0] };
397 $log->notice( "Playlist contains $present songs. Wanted: "
398 . $opt->target_queue_length );
399 if ( $present < $opt->target_queue_length ) {
401 $opt->target_queue_length - $present, $callback );
404 $callback->() if $callback;
412 my @list = $self->db_find_suitable_songs($num);
414 die "Found no suitable songs" unless @list;
416 if ( @list < $num ) {
419 'Found only %d suitable songs instead of %d',
425 $log->info("About to add $num songs to the playlist");
428 for my $song (@list) {
429 my $path = $song->{song};
434 $log->debug( "Adding " . join( ', ', map {"«$_»"} @paths ) );
437 push @commands, [ add => "\"$_\"" ];
440 my $f = $mpd->send( \@commands );
441 $f->on_fail( sub { die @_ } );
444 $self->db_note_song_qeued($_) for @list;
445 $callback->(@_) if $callback;
450 method prepare_to_wait_idle {
451 $log->trace('declaring idle mode');
452 $mpd->send('idle database playlist')->on_done(
456 if ( $result->{changed} eq 'database' ) {
457 $db_needs_update = 1;
458 $self->prepare_to_wait_idle;
460 elsif ( $result->{changed} eq 'playlist' ) {
461 $self->queue_songs( undef,
462 sub { $self->prepare_to_wait_idle } );
467 "Unknown result from idle: " . to_json($result) );
468 $self->prepare_to_wait_idle;
477 die "Connection to MPD lost";
481 $self->prepare_to_wait_idle;
488 if ($db->{ActiveKids}) {
489 $log->warn("$db->{ActiveKids} active DB statements");
490 for my $st ( @{ $db->{ChildHandles} } ) {
491 next unless $st->{Active};
492 while(my($k,$v) = each %$st) {
493 $log->debug("$k = ".($v//'<NULL>'));
507 $self->queue_songs( undef, sub { $self->run } );
509 $log->debug("Entering event loop. PID=$$");
511 my $result = $mpd->loop->run;
512 $log->trace( "Got loop result of " . ( $result // 'undef' ) );
514 if ( 'reload' eq $result ) {
515 $log->notice("disconnecting");
518 exec( "$0", '--config', $self->cfg_file, '--skip-db-update' );
521 if ( 'quit' eq $result ) {
522 $log->trace("quitting because of 'quit' loop result");