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;
252 my $sth = $db->prepare_cached($sql);
254 $sth->execute(@params);
255 my $count = ($sth->fetchrow_array)[0];
258 $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
260 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
272 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
277 $sth = $db->prepare_cached($sql);
278 $sth->execute($opt->min_song_interval);
279 $count = ($sth->fetchrow_array)[0];
280 $total = ($sth->fetchrow_array)[0];
285 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
286 $count, $total, 100.0 * $count / $total
294 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
299 $sth = $db->prepare_cached($sql);
300 $sth->execute($opt->min_artist_interval);
301 $count = ($sth->fetchrow_array)[0];
302 $total = ($sth->fetchrow_array)[0];
305 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
306 $count, $total, 100.0 * $count / $total
314 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
319 $sth = $db->prepare_cached($sql);
320 $sth->execute($opt->min_album_interval);
321 $count = ($sth->fetchrow_array)[0];
322 $total = ($sth->fetchrow_array)[0];
325 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
326 $count, $total, 100.0 * $count / $total
337 method db_add_unwanted_artist($artist) {
343 INSERT INTO unwanted_artists(artist, generation)
346 undef, $artist, $db_generation
353 $log->debug("PostgreSQL error: $err");
354 $log->debug( "SQLSTATE = " . $db->state );
355 return 0 if $db->state eq '23505';
361 method db_del_unwanted_artist($artist) {
366 DELETE FROM unwanted_artists
373 method queue_songs($num = undef, $callback = undef) {
376 $mpd->send('playlist')->on_done(
378 my $present = scalar @{ $_[0] };
380 $log->notice( "Playlist contains $present songs. Wanted: "
381 . $opt->target_queue_length );
382 if ( $present < $opt->target_queue_length ) {
384 $opt->target_queue_length - $present, $callback );
387 $callback->() if $callback;
395 my @list = $self->db_find_suitable_songs($num);
397 die "Found no suitable songs" unless @list;
399 if ( @list < $num ) {
402 'Found only %d suitable songs instead of %d',
408 $log->info("About to add $num songs to the playlist");
411 for my $song (@list) {
412 my $path = $song->{song};
417 $log->debug( "Adding " . join( ', ', map {"«$_»"} @paths ) );
420 push @commands, [ add => "\"$_\"" ];
423 my $f = $mpd->send( \@commands );
424 $f->on_fail( sub { die @_ } );
427 $self->db_note_song_qeued($_) for @list;
428 $callback->(@_) if $callback;
433 method prepare_to_wait_idle {
434 $log->trace('declaring idle mode');
435 $mpd->send('idle database playlist')->on_done(
439 if ( $result->{changed} eq 'database' ) {
440 $db_needs_update = 1;
441 $self->prepare_to_wait_idle;
443 elsif ( $result->{changed} eq 'playlist' ) {
444 $self->queue_songs( undef,
445 sub { $self->prepare_to_wait_idle } );
450 "Unknown result from idle: " . to_json($result) );
451 $self->prepare_to_wait_idle;
460 die "Connection to MPD lost";
464 $self->prepare_to_wait_idle;
471 if ($db->{ActiveKids}) {
472 $log->warn("$db->{ActiveKids} active DB statements");
473 for my $st ( @{ $db->{ChildHandles} } ) {
474 next unless $st->{Active};
475 while(my($k,$v) = each %$st) {
476 $log->debug("$k = ".($v//'<NULL>'));