From 98ef191bfb3cc5694b9d1001e46a9b00abbd7195 Mon Sep 17 00:00:00 2001 From: Marvin W Date: Sun, 23 Aug 2026 19:32:25 +0200 Subject: Move qlite into libdino --- libdino/src/qlite/column.vala | 190 ++++++++++++++++++++++++ libdino/src/qlite/database.vala | 163 +++++++++++++++++++++ libdino/src/qlite/delete_builder.vala | 62 ++++++++ libdino/src/qlite/insert_builder.vala | 84 +++++++++++ libdino/src/qlite/query_builder.vala | 238 +++++++++++++++++++++++++++++++ libdino/src/qlite/row.vala | 147 +++++++++++++++++++ libdino/src/qlite/statement_builder.vala | 58 ++++++++ libdino/src/qlite/table.vala | 218 ++++++++++++++++++++++++++++ libdino/src/qlite/update_builder.vala | 103 +++++++++++++ libdino/src/qlite/upsert_builder.vala | 93 ++++++++++++ 10 files changed, 1356 insertions(+) create mode 100644 libdino/src/qlite/column.vala create mode 100644 libdino/src/qlite/database.vala create mode 100644 libdino/src/qlite/delete_builder.vala create mode 100644 libdino/src/qlite/insert_builder.vala create mode 100644 libdino/src/qlite/query_builder.vala create mode 100644 libdino/src/qlite/row.vala create mode 100644 libdino/src/qlite/statement_builder.vala create mode 100644 libdino/src/qlite/table.vala create mode 100644 libdino/src/qlite/update_builder.vala create mode 100644 libdino/src/qlite/upsert_builder.vala (limited to 'libdino/src/qlite') diff --git a/libdino/src/qlite/column.vala b/libdino/src/qlite/column.vala new file mode 100644 index 00000000..8f45307f --- /dev/null +++ b/libdino/src/qlite/column.vala @@ -0,0 +1,190 @@ +using Sqlite; + +namespace Dino.Qlite { + +public abstract class Column { + public const string DEFAULT_TABLE_NAME = ""; + + public string name { get; private set; } + public string? default { get; set; } + public int sqlite_type { get; private set; } + public bool primary_key { get; set; } + public bool auto_increment { get; set; } + public bool unique { get; set; } + public virtual bool not_null { get; set; } + public long min_version { get; set; default = -1; } + public long max_version { get; set; default = long.MAX; } + internal Table table { get; set; } + + public abstract T get(Row row, string? table_name = DEFAULT_TABLE_NAME); + + public virtual bool is_null(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return false; + } + + internal abstract void bind(Statement stmt, int index, T value); + + public string to_string() { + return table == null ? name : (table.name + "." + name); + } + + public string to_column_definition() { + string res = name; + switch (sqlite_type) { + case INTEGER: + res += " INTEGER"; + break; + case FLOAT: + res += " REAL"; + break; + case TEXT: + res += " TEXT"; + break; + default: + res += " UNKNOWN"; + break; + } + if (primary_key) { + res += " PRIMARY KEY"; + if (auto_increment) res += " AUTOINCREMENT"; + } + if (not_null) res += " NOT NULL"; + if (unique) res += " UNIQUE"; + if (default != null) res += @" DEFAULT $((!) default)"; + + return res; + } + + Column(string name, int type) { + this.name = name; + this.sqlite_type = type; + } + + public class Integer : Column { + public Integer(string name) { + base(name, INTEGER); + } + + public override int get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return (int) row.get_integer(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + public override bool is_null(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return !row.has_integer(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + internal override void bind(Statement stmt, int index, int value) { + stmt.bind_int(index, value); + } + } + + public class Long : Column { + public Long(string name) { + base(name, INTEGER); + } + + public override long get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return (long) row.get_integer(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + public override bool is_null(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return !row.has_integer(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + internal override void bind(Statement stmt, int index, long value) { + stmt.bind_int64(index, value); + } + } + + public class NullableReal : Column { + public NullableReal(string name) { + base(name, FLOAT); + } + + public override bool not_null { get { return false; } set {} } + + public override double? get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return row.get_real(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + public override bool is_null(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return !row.has_real(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + internal override void bind(Statement stmt, int index, double? value) { + stmt.bind_double(index, value); + } + } + + public class Text : Column { + public Text(string name) { + base(name, TEXT); + } + + public override string? get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return row.get_text(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + public override bool is_null(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return get(row, table_name == DEFAULT_TABLE_NAME ? table.name : table_name) == null; + } + + internal override void bind(Statement stmt, int index, string? value) { + if (value != null) { + stmt.bind_text(index, (!) value); + } else { + stmt.bind_null(index); + } + } + } + + public class NonNullText : Column { + public NonNullText(string name) { + base(name, TEXT); + } + + public override bool not_null { get { return true; } set {} } + + public override string get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return (!)row.get_text(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name); + } + + public override bool is_null(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return false; + } + + internal override void bind(Statement stmt, int index, string value) { + stmt.bind_text(index, (!) value); + } + } + + public class BoolText : Column { + public BoolText(string name) { + base(name, TEXT); + } + + public override bool get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return row.get_text(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name) == "1"; + } + + internal override void bind(Statement stmt, int index, bool value) { + stmt.bind_text(index, value ? "1" : "0"); + } + } + + public class BoolInt : Column { + public BoolInt(string name) { + base(name, INTEGER); + } + + public override bool get(Row row, string? table_name = DEFAULT_TABLE_NAME) { + return row.get_integer(name, table_name == DEFAULT_TABLE_NAME ? table.name : table_name) == 1; + } + + internal override void bind(Statement stmt, int index, bool value) { + stmt.bind_int(index, value ? 1 : 0); + } + } +} + +} diff --git a/libdino/src/qlite/database.vala b/libdino/src/qlite/database.vala new file mode 100644 index 00000000..e55d5b59 --- /dev/null +++ b/libdino/src/qlite/database.vala @@ -0,0 +1,163 @@ +using Sqlite; + +namespace Dino.Qlite { + +public class Database { + private string file_name; + private Sqlite.Database db; + private long expected_version; + private Table[]? tables; + + private Column meta_name = new Column.Text("name") { primary_key = true }; + private Column meta_int_val = new Column.Long("int_val"); + private Column meta_text_val = new Column.Text("text_val"); + private Table meta_table; + + public bool debug = false; + + public Database(string file_name, long expected_version) { + this.file_name = file_name; + this.expected_version = expected_version; + meta_table = new Table(this, "_meta"); + meta_table.init({meta_name, meta_int_val, meta_text_val}); + } + + public void init(Table[] tables) { + Sqlite.config(Config.SERIALIZED); + int ec = Sqlite.Database.open_v2(file_name, out db, OPEN_READWRITE | OPEN_CREATE | 0x00010000); + if (ec != Sqlite.OK) { + error(@"SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + this.tables = tables; + if (debug) db.trace((message) => print(@"Qlite trace: $message\n")); + start_migration(); + } + + public void ensure_init() { + if (tables == null) error(@"Database $file_name was not initialized, call init()"); + } + + private void start_migration() { + try { + exec("BEGIN TRANSACTION"); + } catch (Error e) { + error("SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + meta_table.create_table_at_version(expected_version); + long old_version = 0; + old_version = meta_table.row_with(meta_name, "version")[meta_int_val, -1]; + if (old_version == -1) { + foreach (Table t in tables) { + t.create_table_at_version(expected_version); + } + meta_table.insert().value(meta_name, "version").value(meta_int_val, expected_version).perform(); + } else if (expected_version != old_version) { + foreach (Table t in tables) { + t.create_table_at_version(old_version); + } + foreach (Table t in tables) { + t.add_columns_for_version(old_version, expected_version); + } + migrate(old_version); + foreach (Table t in tables) { + t.delete_columns_for_version(old_version, expected_version); + } + if (old_version == -1) { + meta_table.insert().value(meta_name, "version").value(meta_int_val, expected_version).perform(); + } else { + meta_table.update().with(meta_name, "=", "version").set(meta_int_val, expected_version).perform(); + } + } + foreach (Table t in tables) { + t.post(); + } + try { + exec("END TRANSACTION"); + } catch (Error e) { + error("SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + } + + internal int errcode() { + return db.errcode(); + } + + internal string errmsg() { + return db.errmsg(); + } + + internal int64 last_insert_rowid() { + return db.last_insert_rowid(); + } + + // To be implemented by actual implementation if required + // new table columns are added, outdated columns are still present and will be removed afterwards + public virtual void migrate(long old_version) { + } + + public QueryBuilder select(Column[]? columns = null) { + ensure_init(); + return new QueryBuilder(this).select(columns); + } + + internal MatchQueryBuilder match_query(Table table) { + ensure_init(); + return new MatchQueryBuilder(this, table); + } + + public InsertBuilder insert() { + ensure_init(); + return new InsertBuilder(this); + } + + public UpdateBuilder update(Table table) { + ensure_init(); + return new UpdateBuilder(this, table); + } + + public UpsertBuilder upsert(Table table) { + ensure_init(); + return new UpsertBuilder(this, table); + } + + public UpdateBuilder update_named(string table) { + ensure_init(); + return new UpdateBuilder.for_name(this, table); + } + + public DeleteBuilder delete() { + ensure_init(); + return new DeleteBuilder(this); + } + + public RowIterator query_sql(string sql, string[]? args = null) { + ensure_init(); + return new RowIterator(this, sql, args); + } + + internal Statement prepare(string sql) { + ensure_init(); + Sqlite.Statement statement; + if (db.prepare_v2(sql, sql.length, out statement) != OK) { + error("SQLite error: %d - %s: %s", db.errcode(), db.errmsg(), sql); + } + return statement; + } + + public void exec(string sql) throws Error { + ensure_init(); + if (db.exec(sql) != OK) { + throw new Error(-1, 0, "SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + } + + public bool is_known_column(string table, string field) { + ensure_init(); + foreach (Table t in tables) { + if (t.is_known_column(field)) return true; + } + return false; + } +} + +} diff --git a/libdino/src/qlite/delete_builder.vala b/libdino/src/qlite/delete_builder.vala new file mode 100644 index 00000000..df63bba6 --- /dev/null +++ b/libdino/src/qlite/delete_builder.vala @@ -0,0 +1,62 @@ +using Sqlite; + +namespace Dino.Qlite { + +public class DeleteBuilder : StatementBuilder { + + // DELETE FROM [...] + private Table? table; + private string table_name; + + // WHERE [...] + private string selection = "1"; + private StatementBuilder.AbstractField[] selection_args = {}; + + internal DeleteBuilder(Database db) { + base(db); + } + + public DeleteBuilder from(Table table) { + if (this.table != null) error("Qlite Error: ILLEGAL QUERY: cannot use from() multiple times."); + this.table = table; + this.table_name = table.name; + return this; + } + + public DeleteBuilder from_name(string table) { + this.table_name = table; + return this; + } + + public DeleteBuilder where(string selection, string[]? selection_args = null) { + if (this.selection != "1") error("selection was already done, but where() was called."); + this.selection = selection; + foreach (string arg in selection_args) { + this.selection_args += new StatementBuilder.StringField(arg); + } + return this; + } + + public DeleteBuilder with(Column column, string comp, T value) { + selection_args += new Field(column, value); + selection = @"($selection) AND $(column.name) $comp ?"; + return this; + } + + internal override Statement prepare() { + Statement stmt = db.prepare(@"DELETE FROM $table_name WHERE $selection"); + for (int i = 0; i < selection_args.length; i++) { + selection_args[i].bind(stmt, i+1); + } + return stmt; + } + + public void perform() { + if (prepare().step() != DONE) { + critical(@"SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + } + +} + +} diff --git a/libdino/src/qlite/insert_builder.vala b/libdino/src/qlite/insert_builder.vala new file mode 100644 index 00000000..3383f0e2 --- /dev/null +++ b/libdino/src/qlite/insert_builder.vala @@ -0,0 +1,84 @@ +using Sqlite; + +namespace Dino.Qlite { + +public class InsertBuilder : StatementBuilder { + + // INSERT [OR ...] + private bool replace_val; + private string? or_val; + + // INTO [...] + private Table table; + private string table_name; + + // VALUES [...] + private StatementBuilder.AbstractField[] fields = {}; + + internal InsertBuilder(Database db) { + base(db); + } + + public InsertBuilder replace() { + this.replace_val = true; + return this; + } + + public InsertBuilder or(string or) { + this.or_val = or; + return this; + } + + public InsertBuilder into(Table table) { + this.table = table; + this.table_name = table.name; + return this; + } + + public InsertBuilder into_name(string table) { + this.table_name = table; + return this; + } + + public InsertBuilder value(Column column, T value) { + fields += new Field(column, value); + return this; + } + + public InsertBuilder value_null(Column column) { + if (column.not_null) error("Qlite Error: ILLEGAL QUERY: Can't set non-null column %s to null", column.name); + fields += new NullField(column); + return this; + } + + internal override Statement prepare() { + string fields_text = ""; + string value_qs = ""; + for (int i = 0; i < fields.length; i++) { + if (i != 0) { + value_qs += ", "; + fields_text += ", "; + } + fields_text += ((!)fields[i].column).name; + value_qs += "?"; + } + string sql = replace_val ? "REPLACE" : "INSERT"; + if (!replace_val && or_val != null) sql += @" OR $((!)or_val)"; + sql += @" INTO $table_name ( $fields_text ) VALUES ($value_qs)"; + Statement stmt = db.prepare(sql); + for (int i = 0; i < fields.length; i++) { + fields[i].bind(stmt, i+1); + } + return stmt; + } + + public int64 perform() { + if (prepare().step() != DONE) { + critical(@"SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + return db.last_insert_rowid(); + } + +} + +} diff --git a/libdino/src/qlite/query_builder.vala b/libdino/src/qlite/query_builder.vala new file mode 100644 index 00000000..40514fd5 --- /dev/null +++ b/libdino/src/qlite/query_builder.vala @@ -0,0 +1,238 @@ +using Sqlite; + +namespace Dino.Qlite { + +public class QueryBuilder : StatementBuilder { + private bool single_result; + + // SELECT [...] + private string column_selector = "*"; + private Column[] columns = {}; + + // FROM [...] + protected Table? table; + protected string? table_name; + + // JOIN [...] + private string joins = ""; + + // WHERE [...] + protected string selection = "1"; + internal StatementBuilder.AbstractField[] selection_args = {}; + + // ORDER BY [...] + private OrderingTerm[]? order_by_terms = {}; + + // GROUP BY [...] + private string? group_by_term; + + // LIMIT [...] OFFSET [...] + private int limit_val; + private int offset_val; + + internal QueryBuilder(Database db) { + base(db); + } + + public QueryBuilder select(Column[] columns = {}) { + this.columns = columns; + if (columns.length != 0) { + for (int i = 0; i < columns.length; i++) { + if (column_selector == "*") { + column_selector = columns[i].to_string(); + } else { + column_selector += ", " + columns[i].to_string(); + } + } + } else { + column_selector = "*"; + } + return this; + } + + public QueryBuilder select_string(string column_selector) { + this.columns = {}; + this.column_selector = column_selector; + return this; + } + + public virtual QueryBuilder from(Table table) { + if (this.table_name != null) error("cannot use from() multiple times."); + this.table = table; + this.table_name = table.name; + return this; + } + + public virtual QueryBuilder from_name(string table) { + this.table_name = table; + return this; + } + + public QueryBuilder outer_join_with(Table table, Column lhs, Column rhs, string? as = null) { + return outer_join_on(table, @"$lhs = $rhs", as); + } + + public QueryBuilder outer_join_on(Table table, string on, string? as = null) { + if (as == null) as = table.name; + joins += @" LEFT OUTER JOIN $(table.name) AS $as ON $on"; + return this; + } + + public QueryBuilder join_with(Table table, Column lhs, Column rhs, string? as = null) { + return join_on(table, @"$lhs = $rhs", as); + } + + public QueryBuilder join_on(Table table, string on, string? as = null) { + if (as == null) as = table.name; + joins += @" JOIN $(table.name) AS $as ON $on"; + return this; + } + + internal QueryBuilder join_name(string table_name, string on) { + joins += @" JOIN $table_name ON $on"; + return this; + } + + public QueryBuilder where(string selection, string[] selection_args = {}) { + this.selection = @"($(this.selection)) AND ($selection)"; + foreach (string arg in selection_args) { + this.selection_args += new StatementBuilder.StringField(arg); + } + return this; + } + + public QueryBuilder with(Column column, string comp, T value) { + if ((column.unique || column.primary_key) && comp == "=") single_result = true; + selection_args += new Field(column, value); + selection = @"($selection) AND $column $comp ?"; + return this; + } + + public QueryBuilder with_null(Column column) { + selection = @"($selection) AND $column ISNULL"; + return this; + } + + public QueryBuilder without_null(Column column) { + selection = @"($selection) AND $column NOT NULL"; + return this; + } + + public QueryBuilder order_by(Column column, string dir = "ASC") { + order_by_terms += new OrderingTerm(column, dir); + return this; + } + + public QueryBuilder order_by_name(string name, string dir) { + order_by_terms += new OrderingTerm.by_name(name, dir); + return this; + } + + public QueryBuilder group_by(Column[] columns) { + foreach(Column col in columns) { + if (group_by_term == null) { + group_by_term = col.to_string(); + } else { + group_by_term += @", $col"; + } + } + return this; + } + + public QueryBuilder limit(int limit) { + if (this.limit_val != 0 && limit > this.limit_val) error("tried to increase an existing limit"); + this.limit_val = limit; + return this; + } + + public QueryBuilder offset(int offset) { + if (this.limit_val == 0) error("limit required before offset"); + this.offset_val = offset; + return this; + } + + public QueryBuilder single() { + this.single_result = true; + return limit(1); + } + + public int64 count() { + this.column_selector = @"COUNT($column_selector) AS count"; + this.single_result = true; + return row().get_integer("count"); + } + + private Row? row_() { + if (!single_result) error("query is not suited to return a single row, but row() was called."); + return iterator().get_next(); + } + + public RowOption row() { + return new RowOption(row_()); + } + + public T get(Column field, T def = null) { + return row().get(field, def); + } + + internal override Statement prepare() { + Statement stmt = db.prepare(@"SELECT $column_selector $(table_name == null ? "" : @"FROM $((!) table_name)") $joins WHERE $selection $(group_by_term == null ? "" : @"GROUP BY $group_by_term") $(OrderingTerm.all_to_string(order_by_terms)) $(limit_val > 0 ? @" LIMIT $limit_val OFFSET $offset_val" : "")"); + for (int i = 0; i < selection_args.length; i++) { + selection_args[i].bind(stmt, i+1); + } + return stmt; + } + + public RowIterator iterator() { + return new RowIterator.from_query_builder(db, this); + } + + class OrderingTerm { + Column? column; + string column_name; + string dir; + + public OrderingTerm(Column column, string dir) { + this.column = column; + this.column_name = column.to_string(); + this.dir = dir; + } + + public OrderingTerm.by_name(string column_name, string dir) { + this.column_name = column_name; + this.dir = dir; + } + + public string to_string() { + return @"$column_name $dir"; + } + + public static string all_to_string(OrderingTerm[]? terms) { + if (terms == null || terms.length == 0) return ""; + string res = "ORDER BY "+terms[0].to_string(); + for (int i = 1; i < terms.length; i++) { + res += @", $(terms[i])"; + } + return res; + } + } +} + +public class MatchQueryBuilder : QueryBuilder { + internal MatchQueryBuilder(Database db, Table table) { + base(db); + if (table.fts_columns == null) error("MATCH query on non FTS table"); + from(table); + join_name(@"_fts_$table_name", @"_fts_$table_name.docid = $table_name.rowid"); + } + + public MatchQueryBuilder match(Column column, string match) { + if (table == null) error("MATCH must occur after FROM statement"); + if (!(column in table.fts_columns)) error("MATCH selection on non FTS column"); + selection_args += new StatementBuilder.StringField(match); + selection = @"($selection) AND _fts_$table_name.$(column.name) MATCH ?"; + return this; + } +} + +} diff --git a/libdino/src/qlite/row.vala b/libdino/src/qlite/row.vala new file mode 100644 index 00000000..65c80f02 --- /dev/null +++ b/libdino/src/qlite/row.vala @@ -0,0 +1,147 @@ +using Gee; +using Sqlite; + +namespace Dino.Qlite { + +public class Row { + private Map text_map = new HashMap(); + private Map int_map = new HashMap(); + private Map real_map = new HashMap(); + + internal Row(Statement stmt) { + for (int i = 0; i < stmt.column_count(); i++) { + string column_name; + if (stmt.column_origin_name(i) != null) { + column_name = @"$(stmt.column_table_name(i)).$(stmt.column_origin_name(i))"; + } else { + column_name = stmt.column_name(i); + } + switch(stmt.column_type(i)) { + case TEXT: + text_map[column_name] = stmt.column_text(i); + break; + case INTEGER: + int_map[column_name] = (long) stmt.column_int64(i); + break; + case FLOAT: + real_map[column_name] = stmt.column_double(i); + break; + } + } + } + + public T get(Column field) { + return field[this]; + } + + private string field_name(string field, string? table) { + if (table != null) { + return @"$table.$field"; + } else { + return field; + } + } + + public string? get_text(string field, string? table = null) { + if (text_map.has_key(field_name(field, table))) { + return text_map[field_name(field, table)]; + } + return null; + } + + public long get_integer(string field, string? table = null) { + return int_map[field_name(field, table)]; + } + + public bool has_integer(string field, string? table = null) { + return int_map.has_key(field_name(field, table)); + } + + public double get_real(string field, string? table = null, double def = 0) { + return real_map[field_name(field, table)] ?? def; + } + + public bool has_real(string field, string? table = null) { + return real_map.has_key(field_name(field, table)) && real_map[field_name(field, table)] != null; + } + + public string to_string() { + string ret = "{"; + + foreach (string key in text_map.keys) { + if (ret.length > 1) ret += ", "; + ret = @"$ret$key: \"$(text_map[key])\""; + } + foreach (string key in int_map.keys) { + if (ret.length > 1) ret += ", "; + ret = @"$ret$key: $(int_map[key])"; + } + foreach (string key in real_map.keys) { + if (ret.length > 1) ret += ", "; + ret = @"$ret$key: $(real_map[key])"; + } + + return ret + "}"; + } +} + +public class RowIterator { + private Database db; + private Statement stmt; + + public RowIterator.from_query_builder(Database db, QueryBuilder query) { + this.db = db; + this.stmt = query.prepare(); + } + + public RowIterator(Database db, string sql, string[]? args = null) { + this.db = db; + this.stmt = db.prepare(sql); + if (args != null) { + for (int i = 0; i < args.length; i++) { + stmt.bind_text(i, sql, sql.length); + } + } + } + + public bool next() { + int r = stmt.step(); + if (r == Sqlite.ROW) return true; + if (r == Sqlite.DONE) return false; + warning(@"SQLite error: $(db.errcode()) - $(db.errmsg())"); + return false; + } + + public Row get() { + return new Row(stmt); + } + + public Row? get_next() { + if (next()) return get(); + return null; + } +} + +public class RowOption { + public Row? inner { get; private set; } + + public RowOption(Row? row) { + this.inner = row; + } + + public bool is_present() { + return inner != null; + } + + public T get(Column field, T def = null) { + if (inner == null || field.is_null((!)inner)) return def; + return field[(!)inner]; + } + + internal long get_integer(string field, long def = 0) { + if (inner == null || !((!)inner).has_integer(field)) return def; + return ((!)inner).get_integer(field); + } +} + +} diff --git a/libdino/src/qlite/statement_builder.vala b/libdino/src/qlite/statement_builder.vala new file mode 100644 index 00000000..5c756ab3 --- /dev/null +++ b/libdino/src/qlite/statement_builder.vala @@ -0,0 +1,58 @@ +using Sqlite; + +namespace Dino.Qlite { + +public abstract class StatementBuilder { + protected Database db; + + internal StatementBuilder(Database db) { + this.db = db; + } + + internal abstract Statement prepare(); + + internal abstract class AbstractField { + public T value; + public Column? column; + + AbstractField(T value) { + this.value = value; + } + + internal abstract void bind(Statement stmt, int index); + } + + internal class Field : AbstractField { + public Field(Column column, T value) { + base(value); + this.column = column; + } + + internal override void bind(Statement stmt, int index) { + ((!)column).bind(stmt, index, value); + } + } + + internal class NullField : AbstractField { + public NullField(Column column) { + base(null); + this.column = column; + } + + internal override void bind(Statement stmt, int index) { + stmt.bind_null(index); + } + } + + internal class StringField : AbstractField { + public StringField(string value) { + base(value); + } + + internal override void bind(Statement stmt, int index) { + stmt.bind_text(index, value); + } + } +} + +} diff --git a/libdino/src/qlite/table.vala b/libdino/src/qlite/table.vala new file mode 100644 index 00000000..3b75e22e --- /dev/null +++ b/libdino/src/qlite/table.vala @@ -0,0 +1,218 @@ +using Sqlite; + +namespace Dino.Qlite { + +public class Table { + protected Database db; + public string name { get; private set; } + protected Column[]? columns; + private string constraints = ""; + private string[] post_statements = {}; + private string[] create_statements = {}; + internal Column[]? fts_columns; + + public Table(Database db, string name) { + this.db = db; + this.name = name; + } + + public void init(Column[] columns, string constraints = "") { + this.columns = columns; + this.constraints = constraints; + + foreach(Column c in columns) { + c.table = this; + } + } + + public void fts(Column[] columns) { + if (fts_columns != null) error("Only one FTS index may be used per table."); + fts_columns = columns; + string cs = ""; + string cnames = ""; + string cnews = ""; + foreach (Column c in columns) { + cs += @", $(c.to_column_definition())"; + cnames += @", $(c.name)"; + cnews += @", new.$(c.name)"; + } + add_create_statement(@"CREATE VIRTUAL TABLE IF NOT EXISTS _fts_$name USING fts4(tokenize=unicode61, content=\"$name\"$cs)"); + add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_bu_$(name) BEFORE UPDATE ON $name BEGIN DELETE FROM _fts_$name WHERE docid=old.rowid; END"); + add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_bd_$(name) BEFORE DELETE ON $name BEGIN DELETE FROM _fts_$name WHERE docid=old.rowid; END"); + add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_au_$(name) AFTER UPDATE ON $name BEGIN INSERT INTO _fts_$name(docid$cnames) VALUES(new.rowid$cnews); END"); + add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_ai_$(name) AFTER INSERT ON $name BEGIN INSERT INTO _fts_$name(docid$cnames) VALUES(new.rowid$cnews); END"); + } + + public void fts_rebuild() { + if (fts_columns == null) error("FTS not available on this table."); + try { + db.exec(@"INSERT INTO _fts_$name(_fts_$name) VALUES('rebuild');"); + } catch (Error e) { + critical(@"Qlite Error: Rebuilding FTS index: $(e.message)"); + } + } + + public void unique(Column[] columns, string? on_conflict = null) { + constraints += ", UNIQUE ("; + bool first = true; + foreach (Column c in columns) { + if (!first) constraints += ", "; + constraints += c.name; + first = false; + } + constraints += ")"; + if (on_conflict != null) { + constraints += " ON CONFLICT " + (!)on_conflict; + } + } + + public void add_post_statement(string stmt) { + post_statements += stmt; + } + + public void add_create_statement(string stmt) { + create_statements += stmt; + } + + public void index(string index_name, Column[] columns, bool unique = false) { + string stmt = @"CREATE $(unique ? "UNIQUE" : "") INDEX IF NOT EXISTS $index_name ON $name ("; + bool first = true; + foreach (Column c in columns) { + if (!first) stmt += ", "; + stmt += c.name; + first = false; + } + stmt += ")"; + add_post_statement(stmt); + } + + private void ensure_init() { + if (columns == null) error("Table %s was not initialized, call init()", name); + } + + public QueryBuilder select(Column[]? columns = null) { + ensure_init(); + return db.select(columns).from(this); + } + + private MatchQueryBuilder match_query() { + ensure_init(); + return db.match_query(this); + } + + public MatchQueryBuilder match(Column column, string query) { + return match_query().match(column, query); + } + + public InsertBuilder insert() { + ensure_init(); + return db.insert().into(this); + } + + public UpdateBuilder update() { + ensure_init(); + return db.update(this); + } + + public UpsertBuilder upsert() { + ensure_init(); + return db.upsert(this); + } + + public DeleteBuilder delete() { + ensure_init(); + return db.delete().from(this); + } + + public RowOption row_with(Column column, T value) { + ensure_init(); + if (!column.unique && !column.primary_key) error("%s is not suited to identify a row, but used with row_with()", column.name); + return select().with(column, "=", value).row(); + } + + public bool is_known_column(string column) { + ensure_init(); + foreach (Column c in columns) { + if (c.name == column) return true; + } + return false; + } + + public void create_table_at_version(long version) { + ensure_init(); + string sql = @"CREATE TABLE IF NOT EXISTS $name ("; + bool first = true; + for (int i = 0; i < columns.length; i++) { + Column c = columns[i]; + if (c.min_version <= version && c.max_version >= version) { + sql += @"$(!first ? "," : "") $(c.to_column_definition())"; + first = false; + } + } + sql += @"$constraints)"; + try { + db.exec(sql); + } catch (Error e) { + error(@"Qlite Error: Create table at version: $(e.message)"); + } + foreach (string stmt in create_statements) { + try { + db.exec(stmt); + } catch (Error e) { + error(@"Qlite Error: Create table at version: $(e.message)"); + } + } + } + + public void add_columns_for_version(long old_version, long new_version) { + ensure_init(); + foreach (Column c in columns) { + if (c.min_version <= new_version && c.max_version >= new_version && c.min_version > old_version) { + try { + db.exec(@"ALTER TABLE $name ADD COLUMN $(c.to_column_definition())"); + } catch (Error e) { + critical(@"Qlite Error: Add columns for version: $(e.message)"); + } + } + } + } + + public void delete_columns_for_version(long old_version, long new_version) { + bool column_deletion_required = false; + string column_list = ""; + foreach (Column c in columns) { + if (c.min_version <= new_version && c.max_version >= new_version) { + if (column_list == "") { + column_list = c.name; + } else { + column_list += ", " + c.name; + } + } + if (!(c.min_version <= new_version && c.max_version >= new_version) && c.min_version <= old_version && c.max_version >= old_version) { + column_deletion_required = true; + } + } + if (column_deletion_required) { + try { + db.exec(@"ALTER TABLE $name RENAME TO _$(name)_$old_version"); + create_table_at_version(new_version); + db.exec(@"INSERT INTO $name ($column_list) SELECT $column_list FROM _$(name)_$old_version"); + db.exec(@"DROP TABLE _$(name)_$old_version"); + } catch (Error e) { + error(@"Qlite Error: Delete columns for version change: $(e.message)"); + } + } + } + + internal void post() { + foreach (string stmt in post_statements) { + try { + db.exec(stmt); + } catch (Error e) { + error(@"Qlite Error: Post: $(e.message)"); + } + } + } +} + +} diff --git a/libdino/src/qlite/update_builder.vala b/libdino/src/qlite/update_builder.vala new file mode 100644 index 00000000..d371a93a --- /dev/null +++ b/libdino/src/qlite/update_builder.vala @@ -0,0 +1,103 @@ +using Sqlite; + +namespace Dino.Qlite { + +public class UpdateBuilder : StatementBuilder { + + // UPDATE [OR ...] + private string? or_val; + + // [...] + private Table? table; + private string table_name; + + // SET [...] + private StatementBuilder.AbstractField[] fields = {}; + + // WHERE [...] + private string selection = "1"; + private StatementBuilder.AbstractField[] selection_args = {}; + + internal UpdateBuilder(Database db, Table table) { + base(db); + this.table = table; + this.table_name = table.name; + } + + internal UpdateBuilder.for_name(Database db, string table) { + base(db); + this.table_name = table; + } + + public UpdateBuilder or(string or) { + this.or_val = or; + return this; + } + + public UpdateBuilder set(Column column, T value) { + fields += new Field(column, value); + return this; + } + + public UpdateBuilder set_null(Column column) { + if (column.not_null) error("Can't set non-null column %s to null", column.name); + fields += new NullField(column); + return this; + } + + public UpdateBuilder where(string selection, string[] selection_args = {}) { + if (this.selection != "1") error("selection was already done, but where() was called."); + this.selection = selection; + foreach (string arg in selection_args) { + this.selection_args += new StatementBuilder.StringField(arg); + } + return this; + } + + public UpdateBuilder with(Column column, string comp, T value) { + selection_args += new Field(column, value); + selection = @"($selection) AND $(column.name) $comp ?"; + return this; + } + + public UpdateBuilder with_null(Column column) { + selection = @"($selection) AND $(column.name) ISNULL"; + return this; + } + + public UpdateBuilder without_null(Column column) { + selection = @"($selection) AND $(column.name) NOT NULL"; + return this; + } + + internal override Statement prepare() { + string sql = "UPDATE"; + if (or_val != null) sql += @" OR $((!)or_val)"; + sql += @" $table_name SET "; + for (int i = 0; i < fields.length; i++) { + if (i != 0) { + sql += ", "; + } + sql += @"$(((!)fields[i].column).name) = ?"; + } + sql += @" WHERE $selection"; + Statement stmt = db.prepare(sql); + for (int i = 0; i < fields.length; i++) { + fields[i].bind(stmt, i+1); + } + for (int i = 0; i < selection_args.length; i++) { + selection_args[i].bind(stmt, i + fields.length + 1); + } + return stmt; + } + + public void perform() { + if (fields.length == 0) return; + if (prepare().step() != DONE) { + critical("SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + } + +} + +} diff --git a/libdino/src/qlite/upsert_builder.vala b/libdino/src/qlite/upsert_builder.vala new file mode 100644 index 00000000..e9a73e4f --- /dev/null +++ b/libdino/src/qlite/upsert_builder.vala @@ -0,0 +1,93 @@ +using Sqlite; + +namespace Dino.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, bool key = false) { + if (column.not_null) error("Can't set non-null column %s to null", column.name); + if (key) { + keys += new NullField(column); + } else { + fields += new NullField(column); + } + return this; + } + + internal override Statement prepare() { + error("prepare() not available for upsert."); + } + + 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) { + unique_fields.append(", "); + unique_values.append(", "); + } + unique_fields.append(keys[i].column.name); + unique_values.append("?"); + } + + for (int i = 0; i < fields.length; i++) { + if (i != 0) { + update_fields.append(", "); + update_values.append(", "); + update_fields_vals.append(", "); + } + 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 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 < keys.length; i++) { + 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_upsert().step() != DONE) { + critical(@"SQLite error: %d - %s", db.errcode(), db.errmsg()); + } + return db.last_insert_rowid(); + } + +} + +} -- cgit v1.3