Use sqlite UPSERT

12 files changed, 75 insertions, 75 deletions

diff --git a/libdino/src/entity/message.vala b/libdino/src/entity/message.vala
index bce3bec4..89ad241a 100644
--- a/libdino/src/entity/message.vala
+++ b/libdino/src/entity/message.vala
@@ -196,8 +196,8 @@ public class Message : Object {
         update_builder.perform();
 
         if (sp.get_name() == "real-jid") {
-            db.real_jid.insert().or("REPLACE")
-                .value(db.real_jid.message_id, id)
+            db.real_jid.upsert()
+                .value(db.real_jid.message_id, id, true)
                 .value(db.real_jid.real_jid, real_jid.to_string())
                 .perform();
         }
diff --git a/libdino/src/entity/settings.vala b/libdino/src/entity/settings.vala
index bf1ebed4..2aae6055 100644
--- a/libdino/src/entity/settings.vala
+++ b/libdino/src/entity/settings.vala
@@ -22,7 +22,10 @@ public class Settings : Object {
     public bool send_typing {
         get { return send_typing_; }
         set {
-            db.settings.insert().or("REPLACE").value(db.settings.key, "send_typing").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "send_typing", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             send_typing_ = value;
         }
     }
@@ -31,7 +34,10 @@ public class Settings : Object {
     public bool send_marker {
         get { return send_marker_; }
         set {
-            db.settings.insert().or("REPLACE").value(db.settings.key, "send_marker").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "send_marker", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             send_marker_ = value;
         }
     }
@@ -40,7 +46,10 @@ public class Settings : Object {
     public bool notifications {
         get { return notifications_; }
         set {
-            db.settings.insert().or("REPLACE").value(db.settings.key, "notifications").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "notifications", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             notifications_ = value;
         }
     }
@@ -49,7 +58,10 @@ public class Settings : Object {
     public bool convert_utf8_smileys {
         get { return convert_utf8_smileys_; }
         set {
-            db.settings.insert().or("REPLACE").value(db.settings.key, "convert_utf8_smileys").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "convert_utf8_smileys", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             convert_utf8_smileys_ = value;
         }
     }
diff --git a/libdino/src/service/roster_manager.vala b/libdino/src/service/roster_manager.vala
index 62428882..8d9d91fb 100644
--- a/libdino/src/service/roster_manager.vala
+++ b/libdino/src/service/roster_manager.vala
@@ -128,9 +128,9 @@ public class RosterStoreImpl : Roster.Storage, Object {
 
     public void set_item(Roster.Item item) {
         items[item.jid] = item;
-        db.roster.insert().or("REPLACE")
-            .value(db.roster.account_id, account.id)
-            .value(db.roster.jid, item.jid.to_string())
+        db.roster.upsert()
+            .value(db.roster.account_id, account.id, true)
+            .value(db.roster.jid, item.jid.to_string(), true)
             .value(db.roster.handle, item.name)
             .value(db.roster.subscription, item.subscription)
             .perform();
diff --git a/main/src/ui/util/config.vala b/main/src/ui/util/config.vala
index 4ca0d8a0..ce5a3c59 100644
--- a/main/src/ui/util/config.vala
+++ b/main/src/ui/util/config.vala
@@ -22,7 +22,10 @@ public class Config : Object {
         get { return window_maximize_; }
         set {
             if (value == window_maximize_) return;
-            db.settings.insert().or("REPLACE").value(db.settings.key, "window_maximized").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "window_maximized", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             window_maximize_ = value;
         }
     }
@@ -32,7 +35,10 @@ public class Config : Object {
         get { return window_height_; }
         set {
             if (value == window_height_) return;
-            db.settings.insert().or("REPLACE").value(db.settings.key, "window_height").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "window_height", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             window_height_ = value;
         }
     }
@@ -42,7 +48,10 @@ public class Config : Object {
         get { return window_width_; }
         set {
             if (value == window_width_) return;
-            db.settings.insert().or("REPLACE").value(db.settings.key, "window_width").value(db.settings.value, value.to_string()).perform();
+            db.settings.upsert()
+                    .value(db.settings.key, "window_width", true)
+                    .value(db.settings.value, value.to_string())
+                    .perform();
             window_width_ = value;
         }
     }
diff --git a/plugins/omemo/src/logic/manager.vala b/plugins/omemo/src/logic/manager.vala
index 4bb85d59..e561bd90 100644
--- a/plugins/omemo/src/logic/manager.vala
+++ b/plugins/omemo/src/logic/manager.vala
@@ -352,8 +352,8 @@ public class Manager : StreamInteractionModule, Object {
                 store.identity_key_store.identity_key_private = new Bytes(key_pair.private.serialize());
                 store.identity_key_store.identity_key_public = new Bytes(key_pair.public.serialize());
 
-                identity_id = (int) db.identity.insert().or("REPLACE")
-                        .value(db.identity.account_id, account.id)
+                identity_id = (int) db.identity.upsert()
+                        .value(db.identity.account_id, account.id, true)
                         .value(db.identity.device_id, (int) store.local_registration_id)
                         .value(db.identity.identity_key_private_base64, Base64.encode(store.identity_key_store.identity_key_private.get_data()))
                         .value(db.identity.identity_key_public_base64, Base64.encode(store.identity_key_store.identity_key_public.get_data()))
diff --git a/plugins/omemo/src/logic/pre_key_store.vala b/plugins/omemo/src/logic/pre_key_store.vala
index 716fd32f..27e5c01d 100644
--- a/plugins/omemo/src/logic/pre_key_store.vala
+++ b/plugins/omemo/src/logic/pre_key_store.vala
@@ -27,9 +27,9 @@ private class BackedPreKeyStore : SimplePreKeyStore {
     }
 
     public void on_pre_key_stored(PreKeyStore.Key key) {
-        db.pre_key.insert().or("REPLACE")
-                .value(db.pre_key.identity_id, identity_id)
-                .value(db.pre_key.pre_key_id, (int) key.key_id)
+        db.pre_key.upsert()
+                .value(db.pre_key.identity_id, identity_id, true)
+                .value(db.pre_key.pre_key_id, (int) key.key_id, true)
                 .value(db.pre_key.record_base64, Base64.encode(key.record))
                 .perform();
     }
diff --git a/plugins/omemo/src/logic/session_store.vala b/plugins/omemo/src/logic/session_store.vala
index 654591d1..2c7ef642 100644
--- a/plugins/omemo/src/logic/session_store.vala
+++ b/plugins/omemo/src/logic/session_store.vala
@@ -29,10 +29,10 @@ private class BackedSessionStore : SimpleSessionStore {
     }
 
     public void on_session_stored(SessionStore.Session session) {
-        db.session.insert().or("REPLACE")
-                .value(db.session.identity_id, identity_id)
-                .value(db.session.address_name, session.name)
-                .value(db.session.device_id, session.device_id)
+        db.session.upsert()
+                .value(db.session.identity_id, identity_id, true)
+                .value(db.session.address_name, session.name, true)
+                .value(db.session.device_id, session.device_id, true)
                 .value(db.session.record_base64, Base64.encode(session.record))
                 .perform();
     }
diff --git a/plugins/omemo/src/logic/signed_pre_key_store.vala b/plugins/omemo/src/logic/signed_pre_key_store.vala
index 8ff54a93..08f115c9 100644
--- a/plugins/omemo/src/logic/signed_pre_key_store.vala
+++ b/plugins/omemo/src/logic/signed_pre_key_store.vala
@@ -27,9 +27,9 @@ private class BackedSignedPreKeyStore : SimpleSignedPreKeyStore {
     }
 
     public void on_signed_pre_key_stored(SignedPreKeyStore.Key key) {
-        db.signed_pre_key.insert().or("REPLACE")
-                .value(db.signed_pre_key.identity_id, identity_id)
-                .value(db.signed_pre_key.signed_pre_key_id, (int) key.key_id)
+        db.signed_pre_key.upsert()
+                .value(db.signed_pre_key.identity_id, identity_id, true)
+                .value(db.signed_pre_key.signed_pre_key_id, (int) key.key_id, true)
                 .value(db.signed_pre_key.record_base64, Base64.encode(key.record))
                 .perform();
     }
diff --git a/plugins/openpgp/CMakeLists.txt b/plugins/openpgp/CMakeLists.txt
index 3f7c1974..769d517d 100644
--- a/plugins/openpgp/CMakeLists.txt
+++ b/plugins/openpgp/CMakeLists.txt
@@ -6,7 +6,7 @@ gettext_compile(${GETTEXT_PACKAGE} SOURCE_DIR ${CMAKE_CURRENT_SOURCE_DIR}/po TAR
 
 find_packages(OPENPGP_PACKAGES REQUIRED
     Gee
-    GLib>=2.38
+    GLib
     GModule
     GObject
     GTK3
diff --git a/plugins/openpgp/src/database.vala b/plugins/openpgp/src/database.vala
index 52005651..2bda06af 100644
--- a/plugins/openpgp/src/database.vala
+++ b/plugins/openpgp/src/database.vala
@@ -39,8 +39,8 @@ public class Database : Qlite.Database {
     }
 
     public void set_contact_key(Jid jid, string key) {
-        contact_key_table.insert().or("REPLACE")
-                .value(contact_key_table.jid, jid.to_string())
+        contact_key_table.upsert()
+                .value(contact_key_table.jid, jid.to_string(), true)
                 .value(contact_key_table.key, key)
                 .perform();
     }
@@ -51,8 +51,8 @@ public class Database : Qlite.Database {
     }
 
     public void set_account_key(Account account, string key) {
-        account_setting_table.insert().or("REPLACE")
-                .value(account_setting_table.account_id, account.id)
+        account_setting_table.upsert()
+                .value(account_setting_table.account_id, account.id, true)
                 .value(account_setting_table.key, key)
                 .perform();
     }
diff --git a/qlite/CMakeLists.txt b/qlite/CMakeLists.txt
index 782e44ee..70ec0e47 100644
--- a/qlite/CMakeLists.txt
+++ b/qlite/CMakeLists.txt
@@ -2,7 +2,7 @@ find_packages(QLITE_PACKAGES REQUIRED
     Gee
     GLib
     GObject
-    SQLite3
+    SQLite3>=3.24
 )
 
 vala_precompile(QLITE_VALA_C
diff --git a/qlite/src/upsert_builder.vala b/qlite/src/upsert_builder.vala
index 6d29b288..7daf7109 100644
--- a/qlite/src/upsert_builder.vala
+++ b/qlite/src/upsert_builder.vala
@@ -36,70 +36,49 @@ public class UpsertBuilder : StatementBuilder {
         error("prepare() not available for upsert.");
     }
 
-    internal Statement prepare_update() {
-        string update_set_list = "";
-        string update_where_list = "";
-        for (int i = 0; i < fields.length; i++) {
-            if (i != 0) {
-                update_set_list += ", ";
-            }
-            update_set_list += @"$(((!)fields[i].column).name) = ?";
-        }
+    internal Statement prepare_upsert() {
+        var unique_fields = new StringBuilder();
+        var unique_values = new StringBuilder();
+        var update_fields = new StringBuilder();
+        var update_values = new StringBuilder();
+        var update_fields_vals = new StringBuilder();
+
         for (int i = 0; i < keys.length; i++) {
             if (i != 0) {
-                update_where_list += " AND ";
+                unique_fields.append(", ");
+                unique_values.append(", ");
             }
-            update_where_list += @"$(((!)keys[i].column).name) = ?";
-        }
-
-        string sql = @"UPDATE $table_name SET $update_set_list WHERE $update_where_list";
-
-        Statement stmt = db.prepare(sql);
-        for (int i = 0; i < fields.length; i++) {
-            fields[i].bind(stmt, i + 1);
-        }
-        for (int i = 0; i < keys.length; i++) {
-            keys[i].bind(stmt, i + fields.length + 1);
+            unique_fields.append(keys[i].column.name);
+            unique_values.append("?");
         }
 
-        return stmt;
-    }
-
-    internal Statement prepare_insert() {
-        string insert_field_list = "";
-        string insert_value_qs = "";
         for (int i = 0; i < fields.length; i++) {
             if (i != 0) {
-                insert_value_qs += ", ";
-                insert_field_list += ", ";
+                update_fields.append(", ");
+                update_values.append(", ");
+                update_fields_vals.append(", ");
             }
-            insert_field_list += ((!)fields[i].column).name;
-            insert_value_qs += "?";
-        }
-        for (int i = 0; i < keys.length; i++) {
-            if (i != 0 || fields.length > 0) {
-                insert_value_qs += ", ";
-                insert_field_list += ", ";
-            }
-            insert_field_list += ((!)keys[i].column).name;
-            insert_value_qs += "?";
+            update_fields.append(fields[i].column.name);
+            update_values.append("?");
+            update_fields_vals.append(fields[i].column.name).append("=excluded.").append(fields[i].column.name);
         }
 
-        string sql = @"INSERT OR IGNORE INTO $table_name ($insert_field_list) VALUES ($insert_value_qs)";
+        string sql = @"INSERT INTO $table_name ($(unique_fields.str), $(update_fields.str)) VALUES ($(unique_values.str), $(update_values.str)) " +
+                @"ON CONFLICT ($(unique_fields.str)) DO UPDATE SET $(update_fields_vals.str)";
 
         Statement stmt = db.prepare(sql);
-        for (int i = 0; i < fields.length; i++) {
-            fields[i].bind(stmt, i + 1);
-        }
         for (int i = 0; i < keys.length; i++) {
-            keys[i].bind(stmt, i + fields.length + 1);
+            keys[i].bind(stmt, i + 1);
+        }
+        for (int i = 0; i < fields.length; i++) {
+            fields[i].bind(stmt, i + keys.length + 1);
         }
 
         return stmt;
     }
 
     public int64 perform() {
-        if (prepare_update().step() != DONE || prepare_insert().step() != DONE) {
+        if (prepare_upsert().step() != DONE) {
             critical(@"SQLite error: %d - %s", db.errcode(), db.errmsg());
         }
         return db.last_insert_rowid();