X-Git-Url: https://git.ktnx.net/?a=blobdiff_plain;f=lib%2FApp%2FMPD%2FFeeder%2FDB.pm;h=466b4926641b63c4f6e50648ee4d275b56942e48;hb=841c96b547cd0896e244747857af7556b2a98e38;hp=62164364e167f6415d4c0538d0443cd2b8240687;hpb=77d28a4deff0003254af5bd402cdaa4ad5470b30;p=mpd-feeder.git

diff --git a/lib/App/MPD/Feeder/DB.pm b/lib/App/MPD/Feeder/DB.pm
index 6216436..466b492 100644
--- a/lib/App/MPD/Feeder/DB.pm
+++ b/lib/App/MPD/Feeder/DB.pm
@@ -1,312 +1,363 @@
-package App::MPD::Feeder::DB;
-
-use strict;
-use warnings;
+use v5.28;
 use utf8;
+use Object::Pad;
+class App::MPD::Feeder::DB;
 
 use Log::Any qw($log);
-use Object::Pad;
 use Syntax::Keyword::Try;
 
-class App::MPD::Feeder::DB {
-    has $opt :param;
-    has $db;
-    has $generation;
+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;
+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];
-    }
+    return $result[0];
+}
 
-    method set_option( $name, $value ) {
-        my $sth = $db->prepare_cached("update options set $name = ?");
-        $sth->execute($value);
-    }
+method set_option( $name, $value ) {
+    my $sth = $db->prepare_cached("update options set $name = ?");
+    $sth->execute($value);
+}
 
-    method start_update {
-        $log->trace('starting DB update');
-        $db->begin_work;
-        $db->do(<<SQL);
+method start_update {
+    $log->trace('starting DB update');
+    $db->begin_work;
+    $db->do(<<SQL);
 create temporary table tmp_songs(
-    path text not null,
-    artist text not null,
-    album text not null)
+path text not null,
+artist text not null,
+album text not null)
 on commit drop
 SQL
-        $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
-        $generation++;
-    }
+    $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
+    $generation++;
+}
 
-    method finish_update {
-        $log->trace('finishing DB update');
-        $db->pg_putcopyend;
-        my $sth = $db->prepare_cached(<<'SQL');
+method finish_update {
+    $log->trace('finishing DB update');
+    $db->pg_putcopyend;
+    my $sth = $db->prepare_cached(<<'SQL');
 SELECT total_songs, total_artists, total_albums
-     , new_songs, new_artists, new_albums
+ , new_songs, new_artists, new_albums
 FROM update_song_data($1)
 SQL
-        $sth->execute($generation);
-        my @update_result = $sth->fetchrow_array();
-        $sth->finish;
-
-        $self->remove_stale_entries;
+    $sth->execute($generation);
+    my @update_result = $sth->fetchrow_array();
+    $sth->finish;
 
-        $self->set_option(generation => $generation);
-        $db->commit;
+    $self->remove_stale_entries;
 
-        $log->trace('DB update finished');
+    $self->set_option(generation => $generation);
+    $db->commit;
 
-        return @update_result;
-    }
+    $log->trace('DB update finished');
 
-    method cancel_update {
-        $db->pg_putcopyend;
-        $generation--;
-        $db->rollback;
-    }
+    return @update_result;
+}
 
-    method store_song($song, $artist, $album) {
-        return
-            unless length($song)
-            and length($artist)
-            and length($album);
-
-        for ($song, $artist, $album) {
-            utf8::decode($_);
-            s/\\/\\\\/g;
-            s/\t/\\\t/g;
-            s/\n/\\\n/g;
-        }
+method cancel_update {
+    $db->pg_putcopyend;
+    $generation--;
+    $db->rollback;
+}
 
-        $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
+method store_song($song, $artist, $album) {
+    return
+        unless length($song)
+        and length($artist)
+        and length($album);
+
+    for ($song, $artist, $album) {
+        utf8::decode($_);
+        s/\\/\\\\/g;
+        s/\t/\\\t/g;
+        s/\n/\\\n/g;
     }
 
-    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 ) );
+    $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
+}
 
-        $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
-        $sth->execute($generation);
-        $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
+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 artists WHERE generation <> ?');
-        $sth->execute($generation);
-        $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
-    }
+    $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
+    $sth->execute($generation);
+    $log->debug( sprintf( "Deleted %d stale albums", $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} );
-    }
+    $sth =
+        $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
+    $sth->execute($generation);
+    $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
+}
 
-    method find_suitable_songs($num) {
-        my @result;
-        my $sql = <<SQL;
+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 = <<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 AND al.artist=s.artist
 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 unwanted_artists uar WHERE uar.artist = s.artist)
-  AND NOT EXISTS (SELECT 1 FROM unwanted_albums  ual WHERE ual.album  = s.album)
+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 unwanted_artists uar WHERE uar.artist = s.artist)
+AND NOT EXISTS (SELECT 1 FROM unwanted_albums  ual WHERE ual.album  = s.album)
 ORDER BY random()
 LIMIT ?
 SQL
-        my @params = (
-            $opt->min_song_interval,  $opt->min_artist_interval,
-            $opt->min_album_interval, $num,
-        );
+    my @params = (
+        $opt->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);
-        while ( my @row = $sth->fetchrow_array ) {
-            push @result,
-                { song => $row[0], artist => $row[1], album => $row[2] };
-        }
-        undef $sth;
+        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
+            )
+        );
 
-        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 = <<SQL;
+        $sql = <<SQL;
 SELECT COUNT(*)
 FROM songs s
 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 SQL
-            $sth = $db->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 = <<SQL;
+        $sth = $db->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 = <<SQL;
 SELECT COUNT(*)
 FROM artists ar
 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 SQL
-            $sth = $db->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 = <<SQL;
+        $sth = $db->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 = <<SQL;
 SELECT COUNT(*)
 FROM albums al
 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 SQL
-            $sth = $db->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;
-        }
+        $sth = $db->prepare_cached($sql);
+        $sth->execute($opt->min_album_interval);
+        $count = ($sth->fetchrow_array)[0];
+        $sth->finish;
 
-        return @result;
+        $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;
     }
 
-    method add_unwanted_artist($artist) {
-        $self->connect;
+    return @result;
+}
 
-        try {
-            $db->do(
-                <<'SQL',
+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 = $@;
+            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';
+        $log->debug("PostgreSQL error: $err");
+        $log->debug( "SQLSTATE = " . $db->state );
+        return 0 if $db->state eq '23505';
 
-            die $err;
-        }
+        die $err;
     }
+}
 
-    method del_unwanted_artist($artist) {
-        $self->connect;
+method del_unwanted_artist($artist) {
+    $self->connect;
 
-        return 1 == $db->do(
-            <<'SQL',
+    return 1 == $db->do(
+        <<'SQL',
 DELETE FROM unwanted_artists
 WHERE artist = $1
 SQL
-            undef, $artist
-        );
+        undef, $artist
+    );
+}
+
+method walk_unwanted_artists($callback) {
+    $self->connect;
+
+    my $count = 0;
+
+    my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
+    my $artist;
+    $sth->execute;
+    $sth->bind_columns(\$artist);
+    while ( $sth->fetchrow_arrayref ) {
+        $count++;
+        $callback->($artist);
     }
 
-    method connect {
-        return if $db;
+    return $count;
+}
 
-        $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
-            $opt->db_user, $opt->db_password,
-            { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
+method add_unwanted_album($album, $artist) {
+    $self->connect;
 
-        $log->info( "Connected to database " . $opt->db_path );
-        $generation = $self->get_option('generation');
-        $log->debug("DB generation is $generation");
+    try {
+        $db->do(
+            <<'SQL',
+INSERT INTO unwanted_albums(album, artist, generation)
+VALUES($1, $2, $3)
+SQL
+            undef, $album, $artist, $generation
+        );
+        return 1;
     }
+    catch {
+        my $err = $@;
 
-    method walk_unwanted_artists($callback) {
-        $self->connect;
+        $log->debug("PostgreSQL error: $err");
+        $log->debug( "SQLSTATE = " . $db->state );
+        return 0 if $db->state eq '23505';
 
-        my $count = 0;
+        die $err;
+    }
+}
 
-        my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
-        my $artist;
-        $sth->execute;
-        $sth->bind_columns(\$artist);
-        while ( $sth->fetchrow_arrayref ) {
-            $count++;
-            $callback->($artist);
-        }
+method del_unwanted_album($album, $artist) {
+    $self->connect;
+
+    return 1 == $db->do(
+        <<'SQL',
+DELETE FROM unwanted_albums
+WHERE album = $1 AND artist = $2
+SQL
+        undef, $album, $artist
+    );
+}
 
-        return $count;
+method walk_unwanted_albums($callback) {
+    $self->connect;
+
+    my $count = 0;
+
+    my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
+    my ( $album, $artist );
+    $sth->execute;
+    $sth->bind_columns( \$album, \$artist );
+    while ( $sth->fetchrow_arrayref ) {
+        $count++;
+        $callback->($album, $artist);
     }
 
-    method disconnect {
-        return unless $db;
+    return $count;
+}
+
+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 } );
 
-        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//'<NULL>'));
-                }
+    $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//'<NULL>'));
             }
         }
-
-        $db->disconnect;
-        undef $db;
     }
+
+    $db->disconnect;
+    undef $db;
 }
+
+1;