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 walk_unwanted_artists($callback) {
 268         $self->connect;
 269
 270         my $count = 0;
 271
 272         my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
 273         my $artist;
 274         $sth->execute;
 275         $sth->bind_columns(\$artist);
 276         while ( $sth->fetchrow_arrayref ) {
 277             $count++;
 278             $callback->($artist);
 279         }
 280
 281         return $count;
 282     }
 283
 284     method add_unwanted_album($album, $artist) {
 285         $self->connect;
 286
 287         try {
 288             $db->do(
 289                 <<'SQL',
 290 INSERT INTO unwanted_albums(album, artist, generation)
 291 VALUES($1, $2, $3)
 292 SQL
 293                 undef, $album, $artist, $generation
 294             );
 295             return 1;
 296         }
 297         catch {
 298             my $err = $@;
 299
 300             $log->debug("PostgreSQL error: $err");
 301             $log->debug( "SQLSTATE = " . $db->state );
 302             return 0 if $db->state eq '23505';
 303
 304             die $err;
 305         }
 306     }
 307
 308     method del_unwanted_album($album, $artist) {
 309         $self->connect;
 310
 311         return 1 == $db->do(
 312             <<'SQL',
 313 DELETE FROM unwanted_albums
 314 WHERE album = $1 AND artist = $2
 315 SQL
 316             undef, $album, $artist
 317         );
 318     }
 319
 320     method walk_unwanted_albums($callback) {
 321         $self->connect;
 322
 323         my $count = 0;
 324
 325         my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
 326         my ( $album, $artist );
 327         $sth->execute;
 328         $sth->bind_columns( \$album, \$artist );
 329         while ( $sth->fetchrow_arrayref ) {
 330             $count++;
 331             $callback->($album, $artist);
 332         }
 333
 334         return $count;
 335     }
 336
 337     method connect {
 338         return if $db;
 339
 340         $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
 341             $opt->db_user, $opt->db_password,
 342             { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
 343
 344         $log->info( "Connected to database " . $opt->db_path );
 345         $generation = $self->get_option('generation');
 346         $log->debug("DB generation is $generation");
 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 }