aboutsummaryrefslogtreecommitdiff
path: root/qlite
diff options
context:
space:
mode:
authorfiaxh <git@lightrise.org>2020-06-10 23:40:00 +0200
committerfiaxh <git@lightrise.org>2020-06-10 23:40:00 +0200
commit0beb592c5a6a2767e11a892bdb5ac9bcc5283c38 (patch)
treebea2894d322472292590ec126dc1b52543a10a7e /qlite
parent50c55c7f55aff6622d242bdcf2b58d5f7956f28e (diff)
downloaddino-0beb592c5a6a2767e11a892bdb5ac9bcc5283c38.tar.gz
dino-0beb592c5a6a2767e11a892bdb5ac9bcc5283c38.zip
Use sqlite UPSERT
Diffstat (limited to 'qlite')
-rw-r--r--qlite/CMakeLists.txt2
-rw-r--r--qlite/src/upsert_builder.vala69
2 files changed, 25 insertions, 46 deletions
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();