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);
36 method finish_update {
37 $self->set_option(generation => $generation);
41 method cancel_update {
46 method store_song($song, $artist, $album) {
47 return unless length($song) and length($artist) and length($album);
49 utf8::decode($_) for $song, $artist, $album;
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
60 $db->prepare_cached(<<'SQL')->execute( $artist, $album, $generation );
61 INSERT INTO albums(artist, album, generation)
63 ON CONFLICT ON CONSTRAINT albums_pkey DO
64 UPDATE SET generation = $3
66 $db->prepare_cached(<<'SQL')->execute( $artist, $generation );
67 INSERT INTO artists(artist, generation)
69 ON CONFLICT ON CONSTRAINT artists_pkey DO
70 UPDATE SET generation = $2
74 method remove_stale_entries {
76 $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
77 $sth->execute($generation);
78 $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );
80 $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
81 $sth->execute($generation);
82 $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );
85 $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
86 $sth->execute($generation);
87 $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
90 method note_song_qeued($item) {
92 'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
93 ->execute( $item->{song} );
95 'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
96 ->execute( $item->{artist} );
98 'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
99 )->execute( $item->{artist}, $item->{album} );
102 method find_suitable_songs($num) {
105 SELECT s.path, s.artist, s.album
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)
118 $opt->min_song_interval, $opt->min_artist_interval,
119 $opt->min_album_interval, $num,
121 my $sth = $db->prepare_cached($sql);
122 $sth->execute(@params);
123 while ( my @row = $sth->fetchrow_array ) {
125 { song => $row[0], artist => $row[1], album => $row[2] };
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);
135 $sth->execute(@params);
136 my $count = ($sth->fetchrow_array)[0];
139 $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
141 my $total = ($sth->fetchrow_array)[0];
145 "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
146 $count, $total, 100.0 * $count / $total
153 WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
155 $sth = $db->prepare_cached($sql);
156 $sth->execute($opt->min_song_interval);
157 $count = ($sth->fetchrow_array)[0];
162 "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
163 $count, $total, 100.0 * $count / $total
171 WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
173 $sth = $db->prepare_cached($sql);
174 $sth->execute($opt->min_artist_interval);
175 $count = ($sth->fetchrow_array)[0];
178 $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
180 $total = ($sth->fetchrow_array)[0];
184 "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
185 $count, $total, 100.0 * $count / $total
192 WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
194 $sth = $db->prepare_cached($sql);
195 $sth->execute($opt->min_album_interval);
196 $count = ($sth->fetchrow_array)[0];
199 $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
201 $total = ($sth->fetchrow_array)[0];
205 "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
206 $count, $total, 100.0 * $count / $total
216 method add_unwanted_artist($artist) {
222 INSERT INTO unwanted_artists(artist, generation)
225 undef, $artist, $generation
232 $log->debug("PostgreSQL error: $err");
233 $log->debug( "SQLSTATE = " . $db->state );
234 return 0 if $db->state eq '23505';
240 method del_unwanted_artist($artist) {
245 DELETE FROM unwanted_artists
255 $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
256 $opt->db_user, $opt->db_password,
257 { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
259 $log->info( "Connected to database " . $opt->db_path );
260 $generation = $self->get_option('generation');
261 $log->debug("DB generation is $generation");
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>'));