4 class App::MPD::Feeder::DB;
7 use Syntax::Keyword::Try;
13 method get_option($name) {
14 my $sth = $db->prepare_cached("select $name from options");
16 my @result = $sth->fetchrow_array;
23 method set_option( $name, $value ) {
24 my $sth = $db->prepare_cached("update options set $name = ?");
25 $sth->execute($value);
29 $log->trace('starting DB update');
32 create temporary table tmp_songs(
38 $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
42 method finish_update {
43 $log->trace('finishing DB update');
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)
50 $sth->execute($generation);
51 my @update_result = $sth->fetchrow_array();
54 $self->remove_stale_entries;
56 $self->set_option(generation => $generation);
59 $log->trace('DB update finished');
61 return @update_result;
64 method cancel_update {
70 method store_song($song, $artist, $album) {
76 for ($song, $artist, $album) {
83 $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
86 method remove_stale_entries {
88 $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
89 $sth->execute($generation);
90 $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
92 $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
93 $sth->execute($generation);
94 $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
97 $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
98 $sth->execute($generation);
99 $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
102 method note_song_qeued($item) {
104 'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
105 ->execute( $item->{song} );
107 'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
108 ->execute( $item->{artist} );
110 'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
111 )->execute( $item->{artist}, $item->{album} );
114 method find_suitable_songs($num) {
117 SELECT s.path, s.artist, s.album
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)
130 $opt->min_song_interval, $opt->min_artist_interval,
131 $opt->min_album_interval, $num,
133 my $sth = $db->prepare_cached($sql);
134 $sth->execute(@params);
135 while ( my @row = $sth->fetchrow_array ) {
137 { song => $row[0], artist => $row[1], album => $row[2] };
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);
147 $sth->execute(@params);
148 my $count = ($sth->fetchrow_array)[0];
151 $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
153 my $total = ($sth->fetchrow_array)[0];
157 "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
158 $count, $total, 100.0 * $count / $total
165 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
167 $sth = $db->prepare_cached($sql);
168 $sth->execute($opt->min_song_interval);
169 $count = ($sth->fetchrow_array)[0];
174 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
175 $count, $total, 100.0 * $count / $total
183 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
185 $sth = $db->prepare_cached($sql);
186 $sth->execute($opt->min_artist_interval);
187 $count = ($sth->fetchrow_array)[0];
190 $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
192 $total = ($sth->fetchrow_array)[0];
196 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
197 $count, $total, 100.0 * $count / $total
204 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
206 $sth = $db->prepare_cached($sql);
207 $sth->execute($opt->min_album_interval);
208 $count = ($sth->fetchrow_array)[0];
211 $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
213 $total = ($sth->fetchrow_array)[0];
217 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
218 $count, $total, 100.0 * $count / $total
228 method add_unwanted_artist($artist) {
234 INSERT INTO unwanted_artists(artist, generation)
237 undef, $artist, $generation
244 $log->debug("PostgreSQL error: $err");
245 $log->debug( "SQLSTATE = " . $db->state );
246 return 0 if $db->state eq '23505';
252 method del_unwanted_artist($artist) {
257 DELETE FROM unwanted_artists
264 method walk_unwanted_artists($callback) {
269 my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
272 $sth->bind_columns(\$artist);
273 while ( $sth->fetchrow_arrayref ) {
275 $callback->($artist);
281 method add_unwanted_album($album, $artist) {
287 INSERT INTO unwanted_albums(album, artist, generation)
290 undef, $album, $artist, $generation
297 $log->debug("PostgreSQL error: $err");
298 $log->debug( "SQLSTATE = " . $db->state );
299 return 0 if $db->state eq '23505';
305 method del_unwanted_album($album, $artist) {
310 DELETE FROM unwanted_albums
311 WHERE album = $1 AND artist = $2
313 undef, $album, $artist
317 method walk_unwanted_albums($callback) {
322 my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
323 my ( $album, $artist );
325 $sth->bind_columns( \$album, \$artist );
326 while ( $sth->fetchrow_arrayref ) {
328 $callback->($album, $artist);
337 $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
338 $opt->db_user, $opt->db_password,
339 { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
341 $log->info( "Connected to database " . $opt->db_path );
342 $generation = $self->get_option('generation');
343 $log->debug("DB generation is $generation");
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>'));