initial source import

option/config parsing works, perhaps the db interactions too

diff --git a/bin/mpd-feeder b/bin/mpd-feeder
new file mode 100755 (executable)
index 0000000..6cc9c19
--- /dev/null
+++ b/bin/mpd-feeder
@@ -0,0 +1,358 @@
+#!/usr/bin/perl
+
+use v5.32;
+
+use Getopt::Long ();
+use Object::Pad;
+use Syntax::Keyword::Try;
+
+class Options {
+    use Time::Duration qw(duration_exact);
+    use Time::Duration::Parse qw(parse_duration);
+    has $target_queue_length :reader = 10;
+    has $mpd_host            :reader = undef;
+    has $mpd_port            :reader = undef;
+    has $db_path             :reader = 'mpd-feeder';
+    has $db_user             :reader = undef;
+    has $db_password         :reader = undef;
+    has $min_album_interval  :reader = parse_duration('5h');
+    has $min_song_interval   :reader = parse_duration('13d');
+    has $min_artist_interval :reader = parse_duration('1h 15m');
+    has $verbose             :reader = 0;
+    has $single              :reader = 0;
+    has $one_shot            :reader = 0;
+    has $dump_config         :reader = 0;
+
+    method verb($message) {
+        return unless $self->opt->verbose;
+        warn "$message\n";
+    }
+    method dbg($message) {
+        return unless $self->opt->verbose > 1;
+        warn "$message\n";
+    }
+
+    method parse_command_line {
+        Getopt::Long::GetOptions(
+            'v|verbose+'                => \$verbose,
+            'dump-config!'              => \$dump_config,
+            's|single!'                 => \$single,
+            'one-shot!'                 => \$one_shot,
+            'tql|target-queue-length=n' => \$target_queue_length,
+            'mpd-host=s'                => \$mpd_host,
+            'mpd-port=s'                => \$mpd_port,
+            'db-path=s'                 => \$db_path,
+            'db-user=s'                 => \$db_user,
+            'min-album-interval=s'        => sub {
+                $min_album_interval = parse_duration(pop);
+            },
+            'min-sing-interval=s' => sub {
+                $min_song_interval = parse_duration(pop);
+            },
+            'min-artist-interval=s' => sub {
+                $min_artist_interval = parse_duration(pop);
+            },
+        ) or exit 1;
+    }
+
+    sub handle_config_option( $ini, $section, $option, $target_ref,
+        $converter = undef )
+    {
+        return undef unless exists $ini->{$section}{$option};
+
+        my $value = $ini->{$section}{$option};
+
+        $value = $converter->($value) if $converter;
+
+        $$target_ref = $value;
+    }
+
+    method dump {
+        say "[mpd-feeder]";
+        say "verbose = $verbose";
+        say "";
+        say "[mpd]";
+        say "host = " . ( $mpd_host // '' );
+        say "port = " . ( $mpd_port // '' );
+        say "target-queue-length = $target_queue_length";
+        say "";
+        say "[queue]";
+        say "target-length = $target_queue_length";
+        say "min-song-interval = " . duration_exact($min_song_interval);
+        say "min-album-interval = " . duration_exact($min_album_interval);
+        say "min-artist-interval = " . duration_exact($min_artist_interval);
+        say "";
+        say "[db]";
+        say "path = " .     ( $db_path     // '' );
+        say "user = " .     ( $db_user     // '' );
+        say "password = " . ( $db_password // '' );
+    }
+
+    method parse_config_file($path) {
+        use Config::INI::Reader;
+        my $ini = Config::INI::Reader->read_file($path);
+
+        handle_config_option( $ini => mpd => host => \$mpd_host );
+        handle_config_option( $ini => mpd => port => \$mpd_port );
+
+        handle_config_option( $ini => 'mpd-feeder' => verbose => \$verbose );
+
+        handle_config_option(
+            $ini => queue => 'target-length' => \$target_queue_length );
+        handle_config_option(
+            $ini => queue => 'min-song-interval' => \$min_song_interval,
+            \&parse_duration
+        );
+        handle_config_option(
+            $ini => queue => 'min-album-interval' => \$min_album_interval,
+            \&parse_duration
+        );
+        handle_config_option(
+            $ini => queue => 'min-artist-interval' => \$min_artist_interval,
+            \&parse_duration
+        );
+
+        handle_config_option( $ini => db => path     => \$db_path );
+        handle_config_option( $ini => db => user     => \$db_user );
+        handle_config_option( $ini => db => password => \$db_password );
+
+        # FIXME: complain about unknown sections/parameters
+    }
+}
+
+class Feeder {
+    has $opt :reader;
+    has $db;
+    has $db_generation;
+    has $mpd;
+
+use constant DEFAULT_CONFIG_FILE => '/etc/mpd-feeder/mpd-feeder.conf';
+
+    ADJUST {
+        $opt = Options->new;
+
+        {
+            my $cfg_file;
+            Getopt::Long::Configure('pass_through');
+            Getopt::Long::GetOptions('cfg|config=s' => \$cfg_file);
+            Getopt::Long::Configure('no_pass_through');
+
+            $cfg_file //= DEFAULT_CONFIG_FILE if -e DEFAULT_CONFIG_FILE;
+
+            $opt->parse_config_file($cfg_file) if $cfg_file;
+        }
+
+        $opt->parse_command_line;
+
+        unless ($opt->dump_config) {
+            $mpd = Net::Async::MPD->new(
+                host         => $opt->mpd_host,
+                port         => $opt->mpd_port,
+                auto_connect => 1,
+            );
+
+            $self->connect_db;
+            $self->update_db;
+        }
+    }
+
+    method connect_db {
+        return if $db;
+
+        $db = DBI->connect( "dbi:" . $opt->db_path,
+            $opt->db_user, $opt->db_password,
+            { RaiseError => 1, AutoCommit => 1 } );
+
+        $db_generation = $self->db_get_option('generation');
+    }
+
+    method db_get_option($name) {
+        my $sth = $db->prepare_cached("select $name from options");
+        $sth->execute;
+        my @result = $sth->fetchrow_array;
+
+        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, $3)
+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 {
+        $db->prepare_cached('DELETE FROM songs WHERE generation <> ?')
+            ->execute($db_generation);
+        $db->prepare_cached('DELETE FROM albums WHERE generation <> ?')
+            ->execute($db_generation);
+        $db->prepare_cached('DELETE FROM artists WHERE generation <> ?')
+            ->execute($db_generation);
+    }
+
+    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() {
+        $mpd->send('listallinfo')->on_done(
+            sub {
+                try {
+                    $db->begin;
+
+                    $db_generation++;
+
+                    my ($song, $artist, $album);
+
+                    foreach my $row (@_) {
+                        chomp($row);
+
+                        if ($row =~ s/^file:\s*//) {
+                            $self->db_store_song( $song, $artist, $album );
+                            $song = $row;
+                            $artist = $album = undef;
+                        }
+                        elsif ( $row =~ s/^Artist:\s*// ) {
+                            $artist = $row;
+                        }
+                        elsif ( $row =~ s/^Album:\s*// ) {
+                            $album = $row;
+                        }
+                    }
+
+                    $self->db_store_song($song, $artist, $album);
+
+                    $self->db_remove_stale_entries;
+
+                    $self->db_set_option( generation => $db_generation );
+
+                    $db->commit;
+                }
+                catch {
+                    my $err = $@;
+
+                    $db_generation--;
+
+                    $db->rollback;
+
+                    die $err;
+                }
+            }
+        );
+    }
+
+    method db_find_suitable_songs($num) {
+        my @result;
+        my $sth = $self->db->prepare_cached(<<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
+WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - CAST(? AS float))
+  AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - CAST(? AS float))
+  AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - CAST(? AS float))
+  AND NOT EXISTS (SELECT 1 FROM blacklisted_artists bar WHERE bar.artist = s.artist)
+  AND NOT EXISTS (SELECT 1 FROM blacklisted_albums  bal WHERE bal.album  = s.album)
+ORDER BY random()
+LIMIT ?
+SQL
+        $sth->execute(
+            $self->opt->min_song_interval / 3600.0 / 24.0,
+            $self->opt->min_artist_interval / 3600.0 / 24.0,
+            $self->opt->min_album_interval / 3600.0 / 24.0,
+            $num,
+        );
+        while ( my @row = $sth->fetchrow_array ) {
+            push @result,
+                { song => $row[0], artist => $row[1], album => $row[2] };
+        }
+
+        return @result;
+    }
+
+    method queue_songs($num = undef) {
+        if (!defined $num) {
+            $mpd->send('playlist')->on_done( sub {
+                    my $present = scalar @_;
+
+                    $self->queue_songs( $opt->target_queue_length - $present )
+                        if $present < $opt->target_queue_length;
+                } );
+        }
+        else {
+            my @list = $self->db_find_suitable_songs($num);
+
+            if (@list < $num) {
+                $mpd->loop->add(
+                    IO::Async::Timer::Countdown->new(
+                        delay     => 15,
+                        on_expire => sub { $self->queue_songs($num) },
+                    )
+                );
+            }
+            else {
+                $mpd->send( [ map {"add $_->{song}"} @list ] );
+                $self->db_note_song_qeued($_) for @list;
+            }
+        }
+    }
+}
+
+my $feeder = Feeder->new();
+
+$feeder->opt->dump, exit if $feeder->opt->dump_config;
+
+$feeder->queue_songs(1), exit if $feeder->opt->single;
+
+# FIXME: handle blacklist manipulation
+
+$feeder->queue_songs;
+
+exit if $feeder->opt->one_shot;
+
+$feeder->mpd->on(
+    database => sub {
+        $feeder->update_db;
+    }
+);
+
+$feeder->mpd->on(
+    playlist => sub {
+        $feeder->queue_songs;
+    }
+);
+
+$feeder->mpd->idle(qw(database playlist));
+$feeder->mpd->get;

diff --git a/sql/pgsql/init.sql b/sql/pgsql/init.sql
new file mode 100644 (file)
index 0000000..74598fd
--- /dev/null
+++ b/sql/pgsql/init.sql
@@ -0,0 +1,42 @@
+begin transaction;
+
+create table songs(
+    path text not null primary key,
+    artist text,
+    album text,
+    last_queued timestamp with time zone,
+    generation bigint not null);
+
+create index songs_artist_idx on songs(artist);
+create index songs_album_idx on songs(album);
+
+create table albums(
+    artist text not null,
+    album text not null,
+    last_queued timestamp with time zone,
+    generation bigint not null,
+    primary key(album,artist));
+
+create table artists(
+    artist text not null primary key,
+    last_queued timestamp with time zone,
+    generation bigint not null);
+
+create table blacklisted_albums(
+    artist text not null,
+    album text not null,
+    generation bigint not null,
+    primary key(album,artist));
+
+create table blacklisted_artists(
+    artist text not null primary key,
+    generation bigint not null
+);
+
+create table options(
+    generation bigint not null
+);
+
+insert into options(generation) values(0);
+
+commit;