lib/App/MPD/Feeder/DB.pm

   1 use v5.28;
   2 use utf8;
   3 use Object::Pad;
   4 class App::MPD::Feeder::DB;
   5
   6 use Log::Any qw($log);
   7 use Syntax::Keyword::Try;
   8
   9 has $opt :param;
  10 has $db;
  11 has $generation;
  12
  13 method get_option($name) {
  14     my $sth = $db->prepare_cached("select $name from options");
  15     $sth->execute;
  16     my @result = $sth->fetchrow_array;
  17     $sth->finish;
  18     undef $sth;
  19
  20     return $result[0];
  21 }
  22
  23 method set_option( $name, $value ) {
  24     my $sth = $db->prepare_cached("update options set $name = ?");
  25     $sth->execute($value);
  26 }
  27
  28 method start_update {
  29     $log->trace('starting DB update');
  30     $db->begin_work;
  31     $db->do(<<SQL);
  32 create temporary table tmp_songs(
  33 path text not null,
  34 artist text not null,
  35 album text not null)
  36 on commit drop
  37 SQL
  38     $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
  39     $generation++;
  40 }
  41
  42 method finish_update {
  43     $log->trace('finishing DB update');
  44     $db->pg_putcopyend;
  45     my $sth = $db->prepare_cached(<<'SQL');
  46 SELECT total_songs, total_artists, total_albums
  47  , new_songs, new_artists, new_albums
  48 FROM update_song_data($1)
  49 SQL
  50     $sth->execute($generation);
  51     my @update_result = $sth->fetchrow_array();
  52     $sth->finish;
  53
  54     $self->remove_stale_entries;
  55
  56     $self->set_option(generation => $generation);
  57     $db->commit;
  58
  59     $log->trace('DB update finished');
  60
  61     return @update_result;
  62 }
  63
  64 method cancel_update {
  65     $db->pg_putcopyend;
  66     $generation--;
  67     $db->rollback;
  68 }
  69
  70 method store_song($song, $artist, $album) {
  71     return
  72         unless length($song)
  73         and length($artist)
  74         and length($album);
  75
  76     for ($song, $artist, $album) {
  77         utf8::decode($_);
  78         s/\\/\\\\/g;
  79         s/\t/\\\t/g;
  80         s/\n/\\\n/g;
  81     }
  82
  83     $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
  84 }
  85
  86 method remove_stale_entries {
  87     my $sth =
  88         $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
  89     $sth->execute($generation);
  90     $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
  91
  92     $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
  93     $sth->execute($generation);
  94     $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
  95
  96     $sth =
  97         $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
  98     $sth->execute($generation);
  99     $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
 100 }
 101
 102 method note_song_qeued($item) {
 103     $db->prepare_cached(
 104         'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
 105         ->execute( $item->{song} );
 106     $db->prepare_cached(
 107         'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
 108         ->execute( $item->{artist} );
 109     $db->prepare_cached(
 110         'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
 111     )->execute( $item->{artist}, $item->{album} );
 112 }
 113
 114 method find_suitable_songs($num) {
 115     my @result;
 116     my $sql = <<SQL;
 117 SELECT s.path, s.artist, s.album
 118 FROM songs s
 119 JOIN artists ar ON ar.artist=s.artist
 120 JOIN albums al ON al.album=s.album AND al.artist=s.artist
 121 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 122 AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 123 AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 124 AND NOT EXISTS (SELECT 1 FROM unwanted_artists uar WHERE uar.artist = s.artist)
 125 AND NOT EXISTS (SELECT 1 FROM unwanted_albums  ual WHERE ual.album  = s.album)
 126 ORDER BY random()
 127 LIMIT ?
 128 SQL
 129     my @params = (
 130         $opt->min_song_interval,  $opt->min_artist_interval,
 131         $opt->min_album_interval, $num,
 132     );
 133     my $sth = $db->prepare_cached($sql);
 134     $sth->execute(@params);
 135     while ( my @row = $sth->fetchrow_array ) {
 136         push @result,
 137             { song => $row[0], artist => $row[1], album => $row[2] };
 138     }
 139     undef $sth;
 140
 141     if (scalar(@result) == $num and  $log->is_debug) {
 142         $sql =~ s/^SELECT .+$/SELECT COUNT(DISTINCT s.path)/m;
 143         $sql =~ s/^ORDER BY .+$//m;
 144         $sql =~ s/^LIMIT .+$//m;
 145         my $sth = $db->prepare_cached($sql);
 146         pop @params;
 147         $sth->execute(@params);
 148         my $count = ($sth->fetchrow_array)[0];
 149         $sth->finish;
 150
 151         $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
 152         $sth->execute;
 153         my $total = ($sth->fetchrow_array)[0];
 154         $sth->finish;
 155         $log->debug(
 156             sprintf(
 157                 "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
 158                 $count, $total, 100.0 * $count / $total
 159             )
 160         );
 161
 162         $sql = <<SQL;
 163 SELECT COUNT(*)
 164 FROM songs s
 165 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 166 SQL
 167         $sth = $db->prepare_cached($sql);
 168         $sth->execute($opt->min_song_interval);
 169         $count = ($sth->fetchrow_array)[0];
 170         $sth->finish;
 171
 172         $log->debug(
 173             sprintf(
 174                 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
 175                 $count, $total, 100.0 * $count / $total
 176             )
 177         );
 178         $sth->finish;
 179
 180         $sql = <<SQL;
 181 SELECT COUNT(*)
 182 FROM artists ar
 183 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 184 SQL
 185         $sth = $db->prepare_cached($sql);
 186         $sth->execute($opt->min_artist_interval);
 187         $count = ($sth->fetchrow_array)[0];
 188         $sth->finish;
 189
 190         $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
 191         $sth->execute;
 192         $total = ($sth->fetchrow_array)[0];
 193         $sth->finish;
 194         $log->debug(
 195             sprintf(
 196                 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
 197                 $count, $total, 100.0 * $count / $total
 198             )
 199         );
 200
 201         $sql = <<SQL;
 202 SELECT COUNT(*)
 203 FROM albums al
 204 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 205 SQL
 206         $sth = $db->prepare_cached($sql);
 207         $sth->execute($opt->min_album_interval);
 208         $count = ($sth->fetchrow_array)[0];
 209         $sth->finish;
 210
 211         $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
 212         $sth->execute;
 213         $total = ($sth->fetchrow_array)[0];
 214         $sth->finish;
 215         $log->debug(
 216             sprintf(
 217                 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
 218                 $count, $total, 100.0 * $count / $total
 219             )
 220         );
 221
 222         undef $sth;
 223     }
 224
 225     return @result;
 226 }
 227
 228 method add_unwanted_artist($artist) {
 229     $self->connect;
 230
 231     try {
 232         $db->do(
 233             <<'SQL',
 234 INSERT INTO unwanted_artists(artist, generation)
 235 VALUES($1, $2)
 236 SQL
 237             undef, $artist, $generation
 238         );
 239         return 1;
 240     }
 241     catch {
 242         my $err = $@;
 243
 244         $log->debug("PostgreSQL error: $err");
 245         $log->debug( "SQLSTATE = " . $db->state );
 246         return 0 if $db->state eq '23505';
 247
 248         die $err;
 249     }
 250 }
 251
 252 method del_unwanted_artist($artist) {
 253     $self->connect;
 254
 255     return 1 == $db->do(
 256         <<'SQL',
 257 DELETE FROM unwanted_artists
 258 WHERE artist = $1
 259 SQL
 260         undef, $artist
 261     );
 262 }
 263
 264 method walk_unwanted_artists($callback) {
 265     $self->connect;
 266
 267     my $count = 0;
 268
 269     my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
 270     my $artist;
 271     $sth->execute;
 272     $sth->bind_columns(\$artist);
 273     while ( $sth->fetchrow_arrayref ) {
 274         $count++;
 275         $callback->($artist);
 276     }
 277
 278     return $count;
 279 }
 280
 281 method add_unwanted_album($album, $artist) {
 282     $self->connect;
 283
 284     try {
 285         $db->do(
 286             <<'SQL',
 287 INSERT INTO unwanted_albums(album, artist, generation)
 288 VALUES($1, $2, $3)
 289 SQL
 290             undef, $album, $artist, $generation
 291         );
 292         return 1;
 293     }
 294     catch {
 295         my $err = $@;
 296
 297         $log->debug("PostgreSQL error: $err");
 298         $log->debug( "SQLSTATE = " . $db->state );
 299         return 0 if $db->state eq '23505';
 300
 301         die $err;
 302     }
 303 }
 304
 305 method del_unwanted_album($album, $artist) {
 306     $self->connect;
 307
 308     return 1 == $db->do(
 309         <<'SQL',
 310 DELETE FROM unwanted_albums
 311 WHERE album = $1 AND artist = $2
 312 SQL
 313         undef, $album, $artist
 314     );
 315 }
 316
 317 method walk_unwanted_albums($callback) {
 318     $self->connect;
 319
 320     my $count = 0;
 321
 322     my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
 323     my ( $album, $artist );
 324     $sth->execute;
 325     $sth->bind_columns( \$album, \$artist );
 326     while ( $sth->fetchrow_arrayref ) {
 327         $count++;
 328         $callback->($album, $artist);
 329     }
 330
 331     return $count;
 332 }
 333
 334 method connect {
 335     return if $db;
 336
 337     $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
 338         $opt->db_user, $opt->db_password,
 339         { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
 340
 341     $log->info( "Connected to database " . $opt->db_path );
 342     $generation = $self->get_option('generation');
 343     $log->debug("DB generation is $generation");
 344 }
 345
 346 method disconnect {
 347     return unless $db;
 348
 349     if ($db->{ActiveKids}) {
 350         $log->warn("$db->{ActiveKids} active DB statements");
 351         for my $st ( @{ $db->{ChildHandles} } ) {
 352             next unless $st->{Active};
 353             while(my($k,$v) = each %$st) {
 354                 $log->debug("$k = ".($v//'<NULL>'));
 355             }
 356         }
 357     }
 358
 359     $db->disconnect;
 360     undef $db;
 361 }
 362
 363 1;