[geary/mjog/contacts-upsert] Use SQL UPSERT clause when updating contacts



commit 12d0cced352f3dc5ecda0967dd7c981d20dd24fc
Author: Michael Gratton <mike vee net>
Date:   Fri Oct 25 15:51:13 2019 +1100

    Use SQL UPSERT clause when updating contacts
    
    This saves a round trip in the common case of a contact already
    existing. Requires SQLite 3.24 at a minimum, so bump it in meson.build.

 meson.build                                      |  2 +-
 src/engine/common/common-contact-store-impl.vala | 41 +++++++++---------------
 2 files changed, 17 insertions(+), 26 deletions(-)
---
diff --git a/meson.build b/meson.build
index dfa6e9df..0aa80ab6 100644
--- a/meson.build
+++ b/meson.build
@@ -59,7 +59,7 @@ target_webkit = '2.24'
 glib = dependency('glib-2.0', version: '>=' + target_glib)
 gmime = dependency('gmime-2.6', version: '>= 2.6.17')
 gtk = dependency('gtk+-3.0', version: '>=' + target_gtk)
-sqlite = dependency('sqlite3', version: '>= 3.12')
+sqlite = dependency('sqlite3', version: '>= 3.24')
 webkit2gtk = dependency('webkit2gtk-4.0', version: '>=' + target_webkit)
 
 # Secondary deps - keep sorted alphabetically
diff --git a/src/engine/common/common-contact-store-impl.vala 
b/src/engine/common/common-contact-store-impl.vala
index 8a3745a3..38825a3f 100644
--- a/src/engine/common/common-contact-store-impl.vala
+++ b/src/engine/common/common-contact-store-impl.vala
@@ -140,31 +140,22 @@ internal class Geary.ContactStoreImpl : ContactStore, BaseObject {
                                    Contact updated,
                                    GLib.Cancellable? cancellable)
         throws GLib.Error {
-        Contact? existing = do_fetch_contact(
-            cx, updated.email, cancellable
-        );
-
-        if (existing == null) {
-            // Not found, so just insert it
-            Db.Statement stmt = cx.prepare(
-                "INSERT INTO ContactTable(normalized_email, email, real_name, flags, highest_importance) "
-                + "VALUES(?, ?, ?, ?, ?)");
-            stmt.bind_string(0, updated.normalized_email);
-            stmt.bind_string(1, updated.email);
-            stmt.bind_string(2, updated.real_name);
-            stmt.bind_string(3, updated.flags.serialize());
-            stmt.bind_int(4, updated.highest_importance);
-
-            stmt.exec(cancellable);
-        } else {
-            Db.Statement stmt = cx.prepare(
-                "UPDATE ContactTable SET real_name=?, flags=?, highest_importance=? WHERE email=?");
-            stmt.bind_string(0, updated.real_name);
-            stmt.bind_string(1, updated.flags.serialize());
-            stmt.bind_int(2, updated.highest_importance);
-            stmt.bind_string(3, updated.email);
-            stmt.exec(cancellable);
-        }
+        Db.Statement stmt = cx.prepare("""
+            INSERT INTO ContactTable(
+                normalized_email, email, real_name, flags, highest_importance
+            ) VALUES(?, ?, ?, ?, ?)
+            ON CONFLICT(email) DO UPDATE SET
+              real_name = excluded.real_name,
+              flags = excluded.flags,
+              highest_importance = excluded.highest_importance
+        """);
+        stmt.bind_string(0, updated.normalized_email);
+        stmt.bind_string(1, updated.email.make_valid());
+        stmt.bind_string(2, updated.real_name.make_valid());
+        stmt.bind_string(3, updated.flags.serialize());
+        stmt.bind_int(4, updated.highest_importance);
+
+        stmt.exec(cancellable);
     }
 
 }


[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]