}
method start_update {
- $generation++;
+ $log->trace('starting DB update');
$db->begin_work;
+ $db->do(<<SQL);
+create temporary table tmp_songs(
+ path text not null,
+ artist text not null,
+ album text not null)
+on commit drop
+SQL
+ $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
+ $generation++;
}
method finish_update {
+ $log->trace('finishing DB update');
+ $db->pg_putcopyend;
+ my $sth = $db->prepare_cached(<<'SQL');
+SELECT total_songs, total_artists, total_albums
+ , new_songs, new_artists, new_albums
+FROM update_song_data($1)
+SQL
+ $sth->execute($generation);
+ my @update_result = $sth->fetchrow_array();
+ $sth->finish;
+
+ $self->remove_stale_entries;
+
$self->set_option(generation => $generation);
$db->commit;
+
+ $log->trace('DB update finished');
+
+ return @update_result;
}
method cancel_update {
+ $db->pg_putcopyend;
$generation--;
$db->rollback;
}
method store_song($song, $artist, $album) {
- return unless length($song) and length($artist) and length($album);
-
- utf8::decode($_) for $song, $artist, $album;
+ return
+ unless length($song)
+ and length($artist)
+ and length($album);
+
+ for ($song, $artist, $album) {
+ utf8::decode($_);
+ s/\\/\\\\/g;
+ s/\t/\\\t/g;
+ s/\n/\\\n/g;
+ }
- $db->prepare_cached(
- <<'SQL')->execute( $song, $artist, $album, $generation );
-INSERT INTO songs(path, artist, album, generation)
-VALUES($1, $2, $3, $4)
-ON CONFLICT ON CONSTRAINT songs_pkey DO
-UPDATE SET artist = $2
- , album = $3
- , generation = $4
-SQL
- $db->prepare_cached(<<'SQL')->execute( $artist, $album, $generation );
-INSERT INTO albums(artist, album, generation)
-VALUES($1, $2, $3)
-ON CONFLICT ON CONSTRAINT albums_pkey DO
-UPDATE SET generation = $3
-SQL
- $db->prepare_cached(<<'SQL')->execute( $artist, $generation );
-INSERT INTO artists(artist, generation)
-VALUES($1, $2)
-ON CONFLICT ON CONSTRAINT artists_pkey DO
-UPDATE SET generation = $2
-SQL
+ $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
}
method remove_stale_entries {
);
}
+ method walk_unwanted_artists($callback) {
+ $self->connect;
+
+ my $count = 0;
+
+ my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
+ my $artist;
+ $sth->execute;
+ $sth->bind_columns(\$artist);
+ while ( $sth->fetchrow_arrayref ) {
+ $count++;
+ $callback->($artist);
+ }
+
+ return $count;
+ }
+
+ method add_unwanted_album($album, $artist) {
+ $self->connect;
+
+ try {
+ $db->do(
+ <<'SQL',
+INSERT INTO unwanted_albums(album, artist, generation)
+VALUES($1, $2, $3)
+SQL
+ undef, $album, $artist, $generation
+ );
+ return 1;
+ }
+ catch {
+ my $err = $@;
+
+ $log->debug("PostgreSQL error: $err");
+ $log->debug( "SQLSTATE = " . $db->state );
+ return 0 if $db->state eq '23505';
+
+ die $err;
+ }
+ }
+
+ method del_unwanted_album($album, $artist) {
+ $self->connect;
+
+ return 1 == $db->do(
+ <<'SQL',
+DELETE FROM unwanted_albums
+WHERE album = $1 AND artist = $2
+SQL
+ undef, $album, $artist
+ );
+ }
+
+ method walk_unwanted_albums($callback) {
+ $self->connect;
+
+ my $count = 0;
+
+ my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
+ my ( $album, $artist );
+ $sth->execute;
+ $sth->bind_columns( \$album, \$artist );
+ while ( $sth->fetchrow_arrayref ) {
+ $count++;
+ $callback->($album, $artist);
+ }
+
+ return $count;
+ }
+
method connect {
return if $db;