lib/App/MPD/Feeder/DB.pm

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