From 0beb592c5a6a2767e11a892bdb5ac9bcc5283c38 Mon Sep 17 00:00:00 2001 From: fiaxh Date: Wed, 10 Jun 2020 23:40:00 +0200 Subject: Use sqlite UPSERT --- libdino/src/entity/message.vala | 4 +- libdino/src/entity/settings.vala | 20 +++++-- libdino/src/service/roster_manager.vala | 6 +- main/src/ui/util/config.vala | 15 ++++- plugins/omemo/src/logic/manager.vala | 4 +- plugins/omemo/src/logic/pre_key_store.vala | 6 +- plugins/omemo/src/logic/session_store.vala | 8 +-- plugins/omemo/src/logic/signed_pre_key_store.vala | 6 +- plugins/openpgp/CMakeLists.txt | 2 +- plugins/openpgp/src/database.vala | 8 +-- qlite/CMakeLists.txt | 2 +- qlite/src/upsert_builder.vala | 69 ++++++++--------------- 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(); -- cgit v1.2.3-70-g09d2