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

[mpd-feeder.git] / sql / pgsql / init.sql

diff --git a/sql/pgsql/init.sql b/sql/pgsql/init.sql
index 8cdce3d7c593c12ace119eb99815c89bfae85ebf..9f8df5bfb83719f93b24ebcfc1639e77a792e800 100644 (file)
--- a/sql/pgsql/init.sql
+++ b/sql/pgsql/init.sql
@@ -39,4 +39,68 @@ create table options(
 
 insert into options(generation) values(0);
 
+CREATE FUNCTION update_song_data(
+    new_generation bigint
+  , OUT total_songs bigint
+  , OUT total_artists bigint
+  , OUT total_albums bigint
+  , OUT new_songs bigint
+  , OUT new_artists bigint
+  , OUT new_albums bigint)
+AS
+$$
+DECLARE
+    song_data RECORD;
+BEGIN
+    total_songs = 0;
+    new_songs = 0;
+    new_artists = 0;
+    new_albums = 0;
+
+    FOR song_data IN
+        SELECT path, artist, album
+        FROM tmp_songs
+    LOOP
+        total_songs = total_songs + 1;
+
+        UPDATE songs
+        SET generation = new_generation
+        WHERE path = song_data.path;
+
+        IF ( NOT FOUND ) THEN
+            new_songs = new_songs + 1;
+            INSERT INTO songs(path, artist, album, generation)
+            values(song_data.path, song_data.artist,
+                song_data.album, new_generation);
+        END IF;
+        -----------------------------
+        UPDATE artists
+        SET generation = new_generation
+        WHERE artist = song_data.artist;
+
+        IF ( NOT FOUND ) THEN
+            new_artists = new_artists + 1;
+            INSERT INTO artists(artist, generation)
+            VALUES(song_data.artist, new_generation);
+        END IF;
+        -----------------------------
+        UPDATE albums
+        SET generation = new_generation
+        WHERE album = song_data.album
+          AND artist = song_data.artist;
+
+        IF ( NOT FOUND ) THEN
+            new_albums = new_albums + 1;
+            INSERT INTO albums(album, artist, generation)
+            VALUES(song_data.album, song_data.artist, new_generation);
+        END IF;
+    END LOOP;
+
+    SELECT COUNT(*) INTO total_artists FROM artists WHERE generation = new_generation;
+    SELECT COUNT(*) INTO total_albums  FROM albums  WHERE generation = new_generation;
+
+    RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
 commit;