move 'connect' method next to the 'disconnect' method

[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..ace454c5ef1f305a75bbc27b051268a86a592c18 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 {
@@ -249,18 +264,6 @@ SQL
         );
     }
 
-    method connect {
-        return if $db;
-
-        $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
-            $opt->db_user, $opt->db_password,
-            { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
-
-        $log->info( "Connected to database " . $opt->db_path );
-        $generation = $self->get_option('generation');
-        $log->debug("DB generation is $generation");
-    }
-
     method walk_unwanted_artists($callback) {
         $self->connect;
 
@@ -278,6 +281,71 @@ SQL
         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;
+
+        $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
+            $opt->db_user, $opt->db_password,
+            { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );
+
+        $log->info( "Connected to database " . $opt->db_path );
+        $generation = $self->get_option('generation');
+        $log->debug("DB generation is $generation");
+    }
+
     method disconnect {
         return unless $db;