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         $generation++;
  33         $db->begin_work;
  34     }
  35
  36     method finish_update {
  37         $self->set_option(generation => $generation);
  38         $db->commit;
  39     }
  40
  41     method cancel_update {
  42         $generation--;
  43         $db->rollback;
  44     }
  45
  46     method store_song($song, $artist, $album) {
  47         return unless length($song) and length($artist) and length($album);
  48
  49         utf8::decode($_) for $song, $artist, $album;
  50
  51         $db->prepare_cached(
  52             <<'SQL')->execute( $song, $artist, $album, $generation );
  53 INSERT INTO songs(path, artist, album, generation)
  54 VALUES($1, $2, $3, $4)
  55 ON CONFLICT ON CONSTRAINT songs_pkey DO
  56 UPDATE SET artist = $2
  57          , album = $3
  58          , generation = $4
  59 SQL
  60         $db->prepare_cached(<<'SQL')->execute( $artist, $album, $generation );
  61 INSERT INTO albums(artist, album, generation)
  62 VALUES($1, $2, $3)
  63 ON CONFLICT ON CONSTRAINT albums_pkey DO
  64 UPDATE SET generation = $3
  65 SQL
  66         $db->prepare_cached(<<'SQL')->execute( $artist, $generation );
  67 INSERT INTO artists(artist, generation)
  68 VALUES($1, $2)
  69 ON CONFLICT ON CONSTRAINT artists_pkey DO
  70 UPDATE SET generation = $2
  71 SQL
  72     }
  73
  74     method remove_stale_entries {
  75         my $sth =
  76             $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
  77         $sth->execute($generation);
  78         $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
  79
  80         $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
  81         $sth->execute($generation);
  82         $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
  83
  84         $sth =
  85             $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
  86         $sth->execute($generation);
  87         $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
  88     }
  89
  90     method note_song_qeued($item) {
  91         $db->prepare_cached(
  92             'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
  93             ->execute( $item->{song} );
  94         $db->prepare_cached(
  95             'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
  96             ->execute( $item->{artist} );
  97         $db->prepare_cached(
  98             'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
  99         )->execute( $item->{artist}, $item->{album} );
 100     }
 101
 102     method find_suitable_songs($num) {
 103         my @result;
 104         my $sql = <<SQL;
 105 SELECT s.path, s.artist, s.album
 106 FROM songs s
 107 JOIN artists ar ON ar.artist=s.artist
 108 JOIN albums al ON al.album=s.album AND al.artist=s.artist
 109 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 110   AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 111   AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 112   AND NOT EXISTS (SELECT 1 FROM unwanted_artists uar WHERE uar.artist = s.artist)
 113   AND NOT EXISTS (SELECT 1 FROM unwanted_albums  ual WHERE ual.album  = s.album)
 114 ORDER BY random()
 115 LIMIT ?
 116 SQL
 117         my @params = (
 118             $opt->min_song_interval,  $opt->min_artist_interval,
 119             $opt->min_album_interval, $num,
 120         );
 121         my $sth = $db->prepare_cached($sql);
 122         $sth->execute(@params);
 123         while ( my @row = $sth->fetchrow_array ) {
 124             push @result,
 125                 { song => $row[0], artist => $row[1], album => $row[2] };
 126         }
 127         undef $sth;
 128
 129         if (scalar(@result) == $num and  $log->is_debug) {
 130             $sql =~ s/^SELECT .+$/SELECT COUNT(DISTINCT s.path)/m;
 131             $sql =~ s/^ORDER BY .+$//m;
 132             $sql =~ s/^LIMIT .+$//m;
 133             my $sth = $db->prepare_cached($sql);
 134             pop @params;
 135             $sth->execute(@params);
 136             my $count = ($sth->fetchrow_array)[0];
 137             $sth->finish;
 138
 139             $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
 140             $sth->execute;
 141             my $total = ($sth->fetchrow_array)[0];
 142             $sth->finish;
 143             $log->debug(
 144                 sprintf(
 145                     "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
 146                     $count, $total, 100.0 * $count / $total
 147                 )
 148             );
 149
 150             $sql = <<SQL;
 151 SELECT COUNT(*)
 152 FROM songs s
 153 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 154 SQL
 155             $sth = $db->prepare_cached($sql);
 156             $sth->execute($opt->min_song_interval);
 157             $count = ($sth->fetchrow_array)[0];
 158             $sth->finish;
 159
 160             $log->debug(
 161                 sprintf(
 162                     "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
 163                     $count, $total, 100.0 * $count / $total
 164                 )
 165             );
 166             $sth->finish;
 167
 168             $sql = <<SQL;
 169 SELECT COUNT(*)
 170 FROM artists ar
 171 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 172 SQL
 173             $sth = $db->prepare_cached($sql);
 174             $sth->execute($opt->min_artist_interval);
 175             $count = ($sth->fetchrow_array)[0];
 176             $sth->finish;
 177
 178             $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
 179             $sth->execute;
 180             $total = ($sth->fetchrow_array)[0];
 181             $sth->finish;
 182             $log->debug(
 183                 sprintf(
 184                     "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
 185                     $count, $total, 100.0 * $count / $total
 186                 )
 187             );
 188
 189             $sql = <<SQL;
 190 SELECT COUNT(*)
 191 FROM albums al
 192 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
 193 SQL
 194             $sth = $db->prepare_cached($sql);
 195             $sth->execute($opt->min_album_interval);
 196             $count = ($sth->fetchrow_array)[0];
 197             $sth->finish;
 198
 199             $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
 200             $sth->execute;
 201             $total = ($sth->fetchrow_array)[0];
 202             $sth->finish;
 203             $log->debug(
 204                 sprintf(
 205                     "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
 206                     $count, $total, 100.0 * $count / $total
 207                 )
 208             );
 209
 210             undef $sth;
 211         }
 212
 213         return @result;
 214     }
 215
 216     method add_unwanted_artist($artist) {
 217         $self->connect;
 218
 219         try {
 220             $db->do(
 221                 <<'SQL',
 222 INSERT INTO unwanted_artists(artist, generation)
 223 VALUES($1, $2)
 224 SQL
 225                 undef, $artist, $generation
 226             );
 227             return 1;
 228         }
 229         catch {
 230             my $err = $@;
 231
 232             $log->debug("PostgreSQL error: $err");
 233             $log->debug( "SQLSTATE = " . $db->state );
 234             return 0 if $db->state eq '23505';
 235
 236             die $err;
 237         }
 238     }
 239
 240     method del_unwanted_artist($artist) {
 241         $self->connect;
 242
 243         return 1 == $db->do(
 244             <<'SQL',
 245 DELETE FROM unwanted_artists
 246 WHERE artist = $1
 247 SQL
 248             undef, $artist
 249         );
 250     }
 251
 252     method connect {
 253         return if $db;
 254
 255         $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
 256             $opt->db_user, $opt->db_password,
 257             { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
 258
 259         $log->info( "Connected to database " . $opt->db_path );
 260         $generation = $self->get_option('generation');
 261         $log->debug("DB generation is $generation");
 262     }
 263
 264     method disconnect {
 265         return unless $db;
 266
 267         if ($db->{ActiveKids}) {
 268             $log->warn("$db->{ActiveKids} active DB statements");
 269             for my $st ( @{ $db->{ChildHandles} } ) {
 270                 next unless $st->{Active};
 271                 while(my($k,$v) = each %$st) {
 272                     $log->debug("$k = ".($v//'<NULL>'));
 273                 }
 274             }
 275         }
 276
 277         $db->disconnect;
 278         undef $db;
 279     }
 280 }