From ad033beea82a4ba20da71220966b80d5f674428f Mon Sep 17 00:00:00 2001 From: Marvin W Date: Sat, 26 Aug 2017 00:05:36 +0200 Subject: qlite: add upsert support --- qlite/CMakeLists.txt | 5 +- qlite/src/database.vala | 7 ++- qlite/src/table.vala | 5 ++ qlite/src/upsert_builder.vala | 110 ++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 124 insertions(+), 3 deletions(-) create mode 100644 qlite/src/upsert_builder.vala diff --git a/qlite/CMakeLists.txt b/qlite/CMakeLists.txt index 199f06df..782e44ee 100644 --- a/qlite/CMakeLists.txt +++ b/qlite/CMakeLists.txt @@ -13,10 +13,11 @@ SOURCES "src/row.vala" "src/statement_builder.vala" - "src/query_builder.vala" + "src/delete_builder.vala" "src/insert_builder.vala" + "src/query_builder.vala" "src/update_builder.vala" - "src/delete_builder.vala" + "src/upsert_builder.vala" PACKAGES ${QLITE_PACKAGES} GENERATE_VAPI diff --git a/qlite/src/database.vala b/qlite/src/database.vala index cc46ee21..ac53d51c 100644 --- a/qlite/src/database.vala +++ b/qlite/src/database.vala @@ -41,6 +41,7 @@ public class Database { } this.tables = tables; start_migration(); + if (debug) db.trace((message) => print(@"Qlite trace: $message\n")); } public void ensure_init() throws DatabaseError { @@ -114,6 +115,11 @@ public class Database { return new UpdateBuilder(this, table); } + public UpsertBuilder upsert(Table table) throws DatabaseError { + ensure_init(); + return new UpsertBuilder(this, table); + } + public UpdateBuilder update_named(string table) throws DatabaseError { ensure_init(); return new UpdateBuilder.for_name(this, table); @@ -131,7 +137,6 @@ public class Database { internal Statement prepare(string sql) throws DatabaseError { ensure_init(); - if (debug) print(@"prepare: $sql\n"); Sqlite.Statement statement; if (db.prepare_v2(sql, sql.length, out statement) != OK) { throw new DatabaseError.PREPARE_ERROR(@"SQLite error: $(db.errcode()) - $(db.errmsg())"); diff --git a/qlite/src/table.vala b/qlite/src/table.vala index 6e7e1290..82759dd1 100644 --- a/qlite/src/table.vala +++ b/qlite/src/table.vala @@ -68,6 +68,11 @@ public class Table { return db.update(this); } + public UpsertBuilder upsert() throws DatabaseError { + ensure_init(); + return db.upsert(this); + } + public DeleteBuilder delete() throws DatabaseError { ensure_init(); return db.delete().from(this); diff --git a/qlite/src/upsert_builder.vala b/qlite/src/upsert_builder.vala new file mode 100644 index 00000000..eb835027 --- /dev/null +++ b/qlite/src/upsert_builder.vala @@ -0,0 +1,110 @@ +using Sqlite; + +namespace Qlite { + +public class UpsertBuilder : StatementBuilder { + // INTO [...] + private Table table; + private string table_name; + + // VALUES [...] + private StatementBuilder.AbstractField[] keys = {}; + private StatementBuilder.AbstractField[] fields = {}; + + internal UpsertBuilder(Database db, Table table) { + base(db); + this.table = table; + this.table_name = table.name; + } + + public UpsertBuilder value(Column column, T value, bool key = false) { + if (key) { + keys += new Field(column, value); + } else { + fields += new Field(column, value); + } + return this; + } + + public UpsertBuilder value_null(Column column) throws DatabaseError { + if (column.not_null) throw new DatabaseError.ILLEGAL_QUERY(@"Can't set non-null column $(column.name) to null"); + fields += new NullField(column); + return this; + } + + internal override Statement prepare() throws DatabaseError { + throw new DatabaseError.NOT_SUPPORTED("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) = ?"; + } + for (int i = 0; i < keys.length; i++) { + if (i != 0) { + update_where_list += " AND "; + } + 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); + } + + 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 += ", "; + } + 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 += "?"; + } + + string sql = @"INSERT OR IGNORE INTO $table_name ($insert_field_list) VALUES ($insert_value_qs)"; + + 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); + } + + return stmt; + } + + public int64 perform() throws DatabaseError { + if (prepare_update().step() != DONE || prepare_insert().step() != DONE) { + throw new DatabaseError.EXEC_ERROR(@"SQLite error: $(db.errcode()) - $(db.errmsg())"); + } + return db.last_insert_rowid(); + } + +} + +} \ No newline at end of file -- cgit v1.2.3-70-g09d2