Use the UPSERT functionality from PostgreSQL 9.5.
authorSteinar H. Gunderson <sgunderson@bigfoot.com>
Wed, 20 Jan 2016 22:32:34 +0000 (23:32 +0100)
committerSteinar H. Gunderson <sgunderson@bigfoot.com>
Wed, 20 Jan 2016 22:32:34 +0000 (23:32 +0100)
remoteglot.pl

index a0c1bf1..c35a051 100755 (executable)
@@ -898,12 +898,16 @@ sub output_json {
                    ($new_depth == $old_depth && $new_nodes >= $old_nodes)) {
                        atomic_set_contents($filename, $encoded);
                        if (defined($json->{'plot_score'})) {
                    ($new_depth == $old_depth && $new_nodes >= $old_nodes)) {
                        atomic_set_contents($filename, $encoded);
                        if (defined($json->{'plot_score'})) {
-                               local $dbh->{AutoCommit} = 0;
-                               $dbh->do('DELETE FROM scores WHERE id=?', undef, $id);
-                               $dbh->do('INSERT INTO scores (id, plot_score, short_score, engine, depth, nodes) VALUES (?,?,?,?,?,?)', undef,
+                               $dbh->do('INSERT INTO scores (id, plot_score, short_score, engine, depth, nodes) VALUES (?,?,?,?,?,?) ' .
+                                        '    ON CONFLICT (id) DO UPDATE SET ' .
+                                        '        plot_score=EXCLUDED.plot_score, ' .
+                                        '        short_score=EXCLUDED.short_score, ' .
+                                        '        engine=EXCLUDED.engine, ' .
+                                        '        depth=EXCLUDED.depth, ' .
+                                        '        nodes=EXCLUDED.nodes',
+                                       undef,
                                        $id, $json->{'plot_score'}, $json->{'short_score'},
                                        $json->{'engine'}{'name'}, $new_depth, $new_nodes);
                                        $id, $json->{'plot_score'}, $json->{'short_score'},
                                        $json->{'engine'}{'name'}, $new_depth, $new_nodes);
-                               $dbh->commit;
                        }
                }
        }
                        }
                }
        }