1 package App::MPD::Feeder::DB;
9 use Syntax::Keyword::Try;
11 class App::MPD::Feeder::DB {
16 method get_option($name) {
17 my $sth = $db->prepare_cached("select $name from options");
19 my @result = $sth->fetchrow_array;
26 method set_option( $name, $value ) {
27 my $sth = $db->prepare_cached("update options set $name = ?");
28 $sth->execute($value);
32 $log->trace('starting DB update');
35 create temporary table tmp_songs(
41 $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
45 method finish_update {
46 $log->trace('finishing DB update');
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)
53 $sth->execute($generation);
54 my @update_result = $sth->fetchrow_array();
57 $self->remove_stale_entries;
59 $self->set_option(generation => $generation);
62 $log->trace('DB update finished');
64 return @update_result;
67 method cancel_update {
73 method store_song($song, $artist, $album) {
79 for ($song, $artist, $album) {
86 $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
89 method remove_stale_entries {
91 $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
92 $sth->execute($generation);
93 $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
95 $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
96 $sth->execute($generation);
97 $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
100 $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
101 $sth->execute($generation);
102 $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
105 method note_song_qeued($item) {
107 'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
108 ->execute( $item->{song} );
110 'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
111 ->execute( $item->{artist} );
113 'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
114 )->execute( $item->{artist}, $item->{album} );
117 method find_suitable_songs($num) {
120 SELECT s.path, s.artist, s.album
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)
133 $opt->min_song_interval, $opt->min_artist_interval,
134 $opt->min_album_interval, $num,
136 my $sth = $db->prepare_cached($sql);
137 $sth->execute(@params);
138 while ( my @row = $sth->fetchrow_array ) {
140 { song => $row[0], artist => $row[1], album => $row[2] };
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);
150 $sth->execute(@params);
151 my $count = ($sth->fetchrow_array)[0];
154 $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
156 my $total = ($sth->fetchrow_array)[0];
160 "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
161 $count, $total, 100.0 * $count / $total
168 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
170 $sth = $db->prepare_cached($sql);
171 $sth->execute($opt->min_song_interval);
172 $count = ($sth->fetchrow_array)[0];
177 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
178 $count, $total, 100.0 * $count / $total
186 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
188 $sth = $db->prepare_cached($sql);
189 $sth->execute($opt->min_artist_interval);
190 $count = ($sth->fetchrow_array)[0];
193 $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
195 $total = ($sth->fetchrow_array)[0];
199 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
200 $count, $total, 100.0 * $count / $total
207 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
209 $sth = $db->prepare_cached($sql);
210 $sth->execute($opt->min_album_interval);
211 $count = ($sth->fetchrow_array)[0];
214 $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
216 $total = ($sth->fetchrow_array)[0];
220 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
221 $count, $total, 100.0 * $count / $total
231 method add_unwanted_artist($artist) {
237 INSERT INTO unwanted_artists(artist, generation)
240 undef, $artist, $generation
247 $log->debug("PostgreSQL error: $err");
248 $log->debug( "SQLSTATE = " . $db->state );
249 return 0 if $db->state eq '23505';
255 method del_unwanted_artist($artist) {
260 DELETE FROM unwanted_artists
267 method walk_unwanted_artists($callback) {
272 my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
275 $sth->bind_columns(\$artist);
276 while ( $sth->fetchrow_arrayref ) {
278 $callback->($artist);
284 method add_unwanted_album($album, $artist) {
290 INSERT INTO unwanted_albums(album, artist, generation)
293 undef, $album, $artist, $generation
300 $log->debug("PostgreSQL error: $err");
301 $log->debug( "SQLSTATE = " . $db->state );
302 return 0 if $db->state eq '23505';
308 method del_unwanted_album($album, $artist) {
313 DELETE FROM unwanted_albums
314 WHERE album = $1 AND artist = $2
316 undef, $album, $artist
320 method walk_unwanted_albums($callback) {
325 my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
326 my ( $album, $artist );
328 $sth->bind_columns( \$album, \$artist );
329 while ( $sth->fetchrow_arrayref ) {
331 $callback->($album, $artist);
340 $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
341 $opt->db_user, $opt->db_password,
342 { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
344 $log->info( "Connected to database " . $opt->db_path );
345 $generation = $self->get_option('generation');
346 $log->debug("DB generation is $generation");
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>'));