From b4924aa496dece6edca9d4c3eb598b1850b6499f Mon Sep 17 00:00:00 2001 From: Damyan Ivanov Date: Fri, 12 Nov 2021 08:26:48 +0000 Subject: [PATCH] split out DB operations in a module --- lib/App/MPD/Feeder.pm | 284 +++------------------------------------ lib/App/MPD/Feeder/DB.pm | 280 ++++++++++++++++++++++++++++++++++++++ 2 files changed, 299 insertions(+), 265 deletions(-) create mode 100644 lib/App/MPD/Feeder/DB.pm diff --git a/lib/App/MPD/Feeder.pm b/lib/App/MPD/Feeder.pm index dfb83c7..1b5ed30 100644 --- a/lib/App/MPD/Feeder.pm +++ b/lib/App/MPD/Feeder.pm @@ -5,6 +5,7 @@ use warnings; use utf8; use App::MPD::Feeder::Options; +use App::MPD::Feeder::DB; use DBD::Pg; use DBI; use Getopt::Long; @@ -18,9 +19,8 @@ use Syntax::Keyword::Try; class App::MPD::Feeder { has $cfg_file :reader; has $opt :reader; - has $db; - has $db_generation; - has $db_needs_update :writer = 1; + has $db :reader; + has $db_needs_update = 1; has $mpd :reader; use constant DEFAULT_CONFIG_FILE => '/etc/mpd-feeder/mpd-feeder.conf'; @@ -47,6 +47,8 @@ use constant DEFAULT_CONFIG_FILE => '/etc/mpd-feeder/mpd-feeder.conf'; Log::Any::Adapter->set( Stderr => log_level => $new_opt->log_level ); $opt = $new_opt; + + $db = App::MPD::Feeder::DB->new( opt => $opt ); } method connect_mpd { @@ -106,87 +108,10 @@ use constant DEFAULT_CONFIG_FILE => '/etc/mpd-feeder/mpd-feeder.conf'; } method connect_db { - return if $db; - - $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path, - $opt->db_user, $opt->db_password, - { RaiseError => 1, PrintError => 0, AutoCommit => 1 } ); - - $log->info( "Connected to database " . $opt->db_path ); - $db_generation = $self->db_get_option('generation'); - $log->debug("DB generation is $db_generation"); + $db->connect($opt); $self->update_db; } - method db_get_option($name) { - my $sth = $db->prepare_cached("select $name from options"); - $sth->execute; - my @result = $sth->fetchrow_array; - $sth->finish; - undef $sth; - - return $result[0]; - } - - method db_set_option( $name, $value ) { - my $sth = $db->prepare_cached("update options set $name = ?"); - $sth->execute($value); - } - - 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 { - my $sth = - $db->prepare_cached('DELETE FROM songs WHERE generation <> ?'); - $sth->execute($db_generation); - $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) ); - - $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?'); - $sth->execute($db_generation); - $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) ); - - $sth = - $db->prepare_cached('DELETE FROM artists WHERE generation <> ?'); - $sth->execute($db_generation); - $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) ); - } - - 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($force = undef) { if (!$db_needs_update and !$force) { $log->debug("Skipping DB update"); @@ -195,19 +120,17 @@ SQL $log->info('Updating song database'); $self->connect_mpd; - $self->connect_db; my $rows = $mpd->send('listallinfo')->get; - try { - $db->begin_work; - - $db_generation++; + $db->start_update; + try { my $song_count; foreach my $entry (@$rows) { next unless exists $entry->{file}; - $self->db_store_song( $entry->{file}, + + $self->db->store_song( $entry->{file}, $entry->{AlbumArtist} // $entry->{Artist}, $entry->{Album} ); $song_count++; @@ -215,179 +138,21 @@ SQL $log->info("Updated data about $song_count songs"); - $self->db_remove_stale_entries; - - $self->db_set_option( generation => $db_generation ); + $self->db->remove_stale_entries; - $db->commit; + $self->db->finish_update; $db_needs_update = 0; } catch { my $err = $@; - - $db_generation--; - - $db->rollback; - + $self->db->cancel_update; die $err; } } - method db_find_suitable_songs($num) { - $self->connect_db; - $self->update_db; - - my @result; - my $sql = <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 = <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 = <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 = <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'; - - die $err; - } - } - - method db_del_unwanted_artist($artist) { - $self->connect_db; - - return 1 == $db->do( - <<'SQL', -DELETE FROM unwanted_artists -WHERE artist = $1 -SQL - undef, $artist - ); - } - method queue_songs($num = undef, $callback = undef) { + $self->connect_db; if (!defined $num) { $self->connect_mpd; $mpd->send('playlist')->on_done( @@ -409,7 +174,7 @@ SQL return; } - my @list = $self->db_find_suitable_songs($num); + my @list = $self->db->find_suitable_songs($num); die "Found no suitable songs" unless @list; @@ -432,6 +197,8 @@ SQL } $log->debug( "Adding " . join( ', ', map {"«$_»"} @paths ) ); + # MPD needs raw bytes + utf8::encode($_) for @paths; my @commands; for (@paths) { push @commands, [ add => "\"$_\"" ]; @@ -441,7 +208,7 @@ SQL $f->on_fail( sub { die @_ } ); $f->on_done( sub { - $self->db_note_song_qeued($_) for @list; + $self->db->note_song_qeued($_) for @list; $callback->(@_) if $callback; } ); @@ -484,20 +251,7 @@ SQL method stop { undef $mpd; - if ($db) { - if ($db->{ActiveKids}) { - $log->warn("$db->{ActiveKids} active DB statements"); - for my $st ( @{ $db->{ChildHandles} } ) { - next unless $st->{Active}; - while(my($k,$v) = each %$st) { - $log->debug("$k = ".($v//'')); - } - } - } - - $db->disconnect; - undef $db; - } + $db->disconnect; } method run_loop { diff --git a/lib/App/MPD/Feeder/DB.pm b/lib/App/MPD/Feeder/DB.pm new file mode 100644 index 0000000..ce0bcdd --- /dev/null +++ b/lib/App/MPD/Feeder/DB.pm @@ -0,0 +1,280 @@ +package App::MPD::Feeder::DB; + +use strict; +use warnings; +use utf8; + +use Log::Any qw($log); +use Object::Pad; +use Syntax::Keyword::Try; + +class App::MPD::Feeder::DB { + has $opt :param; + has $db; + has $generation; + + method get_option($name) { + my $sth = $db->prepare_cached("select $name from options"); + $sth->execute; + my @result = $sth->fetchrow_array; + $sth->finish; + undef $sth; + + return $result[0]; + } + + method set_option( $name, $value ) { + my $sth = $db->prepare_cached("update options set $name = ?"); + $sth->execute($value); + } + + method start_update { + $generation++; + $db->begin_work; + } + + method finish_update { + $self->set_option(generation => $generation); + $db->commit; + } + + method cancel_update { + $generation--; + $db->rollback; + } + + method store_song($song, $artist, $album) { + return unless length($song) and length($artist) and length($album); + + utf8::decode($_) for $song, $artist, $album; + + $db->prepare_cached( + <<'SQL')->execute( $song, $artist, $album, $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, $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, $generation ); +INSERT INTO artists(artist, generation) +VALUES($1, $2) +ON CONFLICT ON CONSTRAINT artists_pkey DO +UPDATE SET generation = $2 +SQL + } + + method remove_stale_entries { + my $sth = + $db->prepare_cached('DELETE FROM songs WHERE generation <> ?'); + $sth->execute($generation); + $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) ); + + $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?'); + $sth->execute($generation); + $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) ); + + $sth = + $db->prepare_cached('DELETE FROM artists WHERE generation <> ?'); + $sth->execute($generation); + $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) ); + } + + method 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 find_suitable_songs($num) { + my @result; + my $sql = <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 = <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 = <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 = <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 add_unwanted_artist($artist) { + $self->connect; + + try { + $db->do( + <<'SQL', +INSERT INTO unwanted_artists(artist, generation) +VALUES($1, $2) +SQL + undef, $artist, $generation + ); + return 1; + } + catch { + my $err = $@; + + $log->debug("PostgreSQL error: $err"); + $log->debug( "SQLSTATE = " . $db->state ); + return 0 if $db->state eq '23505'; + + die $err; + } + } + + method del_unwanted_artist($artist) { + $self->connect; + + return 1 == $db->do( + <<'SQL', +DELETE FROM unwanted_artists +WHERE artist = $1 +SQL + undef, $artist + ); + } + + method connect { + return if $db; + + $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path, + $opt->db_user, $opt->db_password, + { RaiseError => 1, PrintError => 0, AutoCommit => 1 } ); + + $log->info( "Connected to database " . $opt->db_path ); + $generation = $self->get_option('generation'); + $log->debug("DB generation is $generation"); + } + + method disconnect { + return unless $db; + + if ($db->{ActiveKids}) { + $log->warn("$db->{ActiveKids} active DB statements"); + for my $st ( @{ $db->{ChildHandles} } ) { + next unless $st->{Active}; + while(my($k,$v) = each %$st) { + $log->debug("$k = ".($v//'')); + } + } + } + + $db->disconnect; + undef $db; + } +} -- 2.39.2