X-Git-Url: https://git.ktnx.net/?a=blobdiff_plain;f=lib%2FApp%2FMPD%2FFeeder%2FDB.pm;fp=lib%2FApp%2FMPD%2FFeeder%2FDB.pm;h=ce0bcdd643f3c18ac02f974ef073bc92ccb4cd0f;hb=b4924aa496dece6edca9d4c3eb598b1850b6499f;hp=0000000000000000000000000000000000000000;hpb=9cdc96f44f4de5d4044d4d66b710444f2878b3b5;p=mpd-feeder.git

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 = <<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)
+ORDER BY random()
+LIMIT ?
+SQL
+        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);
+            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;
+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;
+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;
+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;
+        }
+
+        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//'<NULL>'));
+                }
+            }
+        }
+
+        $db->disconnect;
+        undef $db;
+    }
+}