-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;
+
+ $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
+ )
+ );
- return @result;
+ undef $sth;
}
- method add_unwanted_artist($artist) {
- $self->connect;
+ return @result;
+}
+
+method add_unwanted_artist($artist) {
+ $self->connect;
- try {
- $db->do(
- <<'SQL',
+ 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
- );
- }
-
- method walk_unwanted_artists($callback) {
- $self->connect;
+ undef, $artist
+ );
+}
- my $count = 0;
+method walk_unwanted_artists($callback) {
+ $self->connect;
- 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);
- }
+ my $count = 0;
- return $count;
+ 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 add_unwanted_album($album, $artist) {
- $self->connect;
+ return $count;
+}
+
+method add_unwanted_album($album, $artist) {
+ $self->connect;
- try {
- $db->do(
- <<'SQL',
+ 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 = $@;
+ undef, $album, $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_album($album, $artist) {
- $self->connect;
+method del_unwanted_album($album, $artist) {
+ $self->connect;
- return 1 == $db->do(
- <<'SQL',
+ return 1 == $db->do(
+ <<'SQL',
DELETE FROM unwanted_albums
WHERE album = $1 AND artist = $2
SQL
- undef, $album, $artist
- );
- }
-
- method walk_unwanted_albums($callback) {
- $self->connect;
+ undef, $album, $artist
+ );
+}
- my $count = 0;
+method walk_unwanted_albums($callback) {
+ $self->connect;
- 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);
- }
+ my $count = 0;
- return $count;
+ 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 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 connect {
+ return if $db;
- $log->info( "Connected to database " . $opt->db_path );
- $generation = $self->get_option('generation');
- $log->debug("DB generation is $generation");
- }
+ $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
+ $opt->db_user, $opt->db_password,
+ { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
- method disconnect {
- return unless $db;
+ $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>'));
- }
+ 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;
projects / mpd-feeder.git / blobdiff