rework DB update using COPY and a PostgreSQL function for maximum speed

[mpd-feeder.git] / lib / App / MPD / Feeder / DB.pm

diff --git a/lib/App/MPD/Feeder/DB.pm b/lib/App/MPD/Feeder/DB.pm
index e38279a127c4dd53c3892bf8711b4ccb1edb993c..62164364e167f6415d4c0538d0443cd2b8240687 100644 (file)
--- a/lib/App/MPD/Feeder/DB.pm
+++ b/lib/App/MPD/Feeder/DB.pm
@@ -29,46 +29,61 @@ class App::MPD::Feeder::DB {
     }
 
     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 {