aboutsummaryrefslogtreecommitdiff
path: root/qlite
diff options
context:
space:
mode:
Diffstat (limited to 'qlite')
-rw-r--r--qlite/src/column.vala57
-rw-r--r--qlite/src/database.vala5
-rw-r--r--qlite/src/query_builder.vala92
-rw-r--r--qlite/src/row.vala61
-rw-r--r--qlite/src/table.vala65
5 files changed, 218 insertions, 62 deletions
diff --git a/qlite/src/column.vala b/qlite/src/column.vala
index 9c201885..daa6a59f 100644
--- a/qlite/src/column.vala
+++ b/qlite/src/column.vala
@@ -3,6 +3,8 @@ using Sqlite;
namespace Qlite {
public abstract class Column<T> {
+ public const string DEFALT_TABLE_NAME = "";
+
public string name { get; private set; }
public string? default { get; set; }
public int sqlite_type { get; private set; }
@@ -12,16 +14,21 @@ public abstract class Column<T> {
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);
+ public abstract T get(Row row, string? table_name = DEFALT_TABLE_NAME);
- public virtual bool is_null(Row row) {
+ public virtual bool is_null(Row row, string? table_name = DEFALT_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:
@@ -58,12 +65,12 @@ public abstract class Column<T> {
base(name, INTEGER);
}
- public override int get(Row row) {
- return (int) row.get_integer(name);
+ public override int get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return (int) row.get_integer(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
- public override bool is_null(Row row) {
- return !row.has_integer(name);
+ public override bool is_null(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return !row.has_integer(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
internal override void bind(Statement stmt, int index, int value) {
@@ -76,12 +83,12 @@ public abstract class Column<T> {
base(name, INTEGER);
}
- public override long get(Row row) {
- return (long) row.get_integer(name);
+ public override long get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return (long) row.get_integer(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
- public override bool is_null(Row row) {
- return !row.has_integer(name);
+ public override bool is_null(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return !row.has_integer(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
internal override void bind(Statement stmt, int index, long value) {
@@ -94,12 +101,12 @@ public abstract class Column<T> {
base(name, FLOAT);
}
- public override double get(Row row) {
- return row.get_real(name);
+ public override double get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return row.get_real(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
- public override bool is_null(Row row) {
- return !row.has_real(name);
+ public override bool is_null(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return !row.has_real(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
internal override void bind(Statement stmt, int index, double value) {
@@ -112,12 +119,12 @@ public abstract class Column<T> {
base(name, TEXT);
}
- public override string? get(Row row) {
- return row.get_text(name);
+ public override string? get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return row.get_text(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
- public override bool is_null(Row row) {
- return get(row) == null;
+ public override bool is_null(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return get(row, table_name == DEFALT_TABLE_NAME ? table.name : table_name) == null;
}
internal override void bind(Statement stmt, int index, string? value) {
@@ -136,11 +143,11 @@ public abstract class Column<T> {
public override bool not_null { get { return true; } set {} }
- public override string get(Row row) {
- return (!)row.get_text(name);
+ public override string get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return (!)row.get_text(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name);
}
- public override bool is_null(Row row) {
+ public override bool is_null(Row row, string? table_name = DEFALT_TABLE_NAME) {
return false;
}
@@ -154,8 +161,8 @@ public abstract class Column<T> {
base(name, TEXT);
}
- public override bool get(Row row) {
- return row.get_text(name) == "1";
+ public override bool get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return row.get_text(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name) == "1";
}
internal override void bind(Statement stmt, int index, bool value) {
@@ -168,8 +175,8 @@ public abstract class Column<T> {
base(name, INTEGER);
}
- public override bool get(Row row) {
- return row.get_integer(name) == 1;
+ public override bool get(Row row, string? table_name = DEFALT_TABLE_NAME) {
+ return row.get_integer(name, table_name == DEFALT_TABLE_NAME ? table.name : table_name) == 1;
}
internal override void bind(Statement stmt, int index, bool value) {
diff --git a/qlite/src/database.vala b/qlite/src/database.vala
index 37a7b7f7..d13b9bc4 100644
--- a/qlite/src/database.vala
+++ b/qlite/src/database.vala
@@ -90,6 +90,11 @@ public class Database {
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);
diff --git a/qlite/src/query_builder.vala b/qlite/src/query_builder.vala
index 915e2d2d..88f05e04 100644
--- a/qlite/src/query_builder.vala
+++ b/qlite/src/query_builder.vala
@@ -10,16 +10,22 @@ public class QueryBuilder : StatementBuilder {
private Column[] columns = {};
// FROM [...]
- private Table? table;
- private string? table_name;
+ protected Table? table;
+ protected string? table_name;
+
+ // JOIN [...]
+ private string joins = "";
// WHERE [...]
- private string selection = "1";
- private StatementBuilder.AbstractField[] selection_args = {};
+ 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;
@@ -30,12 +36,12 @@ public class QueryBuilder : StatementBuilder {
public QueryBuilder select(Column[] columns = {}) {
this.columns = columns;
- if (columns.length == 0) {
+ if (columns.length != 0) {
for (int i = 0; i < columns.length; i++) {
if (column_selector == "*") {
- column_selector = columns[0].name;
+ column_selector = columns[i].to_string();
} else {
- column_selector += ", " + columns[i].name;
+ column_selector += ", " + columns[i].to_string();
}
}
} else {
@@ -50,21 +56,45 @@ public class QueryBuilder : StatementBuilder {
return this;
}
- public QueryBuilder from(Table table) {
+ 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 QueryBuilder from_name(string table) {
+ public virtual QueryBuilder from_name(string table) {
this.table_name = table;
return this;
}
+ public QueryBuilder outer_join_with<T>(Table table, Column<T> lhs, Column<T> 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<T>(Table table, Column<T> lhs, Column<T> 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 = {}) {
- if (this.selection != "1") error("selection was already done, but where() was called.");
- this.selection = selection;
+ this.selection = @"($(this.selection)) AND ($selection)";
foreach (string arg in selection_args) {
this.selection_args += new StatementBuilder.StringField(arg);
}
@@ -74,17 +104,17 @@ public class QueryBuilder : StatementBuilder {
public QueryBuilder with<T>(Column<T> column, string comp, T value) {
if ((column.unique || column.primary_key) && comp == "=") single_result = true;
selection_args += new Field<T>(column, value);
- selection = @"($selection) AND $(column.name) $comp ?";
+ selection = @"($selection) AND $column $comp ?";
return this;
}
public QueryBuilder with_null<T>(Column<T> column) {
- selection = @"($selection) AND $(column.name) ISNULL";
+ selection = @"($selection) AND $column ISNULL";
return this;
}
public QueryBuilder without_null<T>(Column<T> column) {
- selection = @"($selection) AND $(column.name) NOT NULL";
+ selection = @"($selection) AND $column NOT NULL";
return this;
}
@@ -98,6 +128,17 @@ public class QueryBuilder : StatementBuilder {
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;
@@ -135,7 +176,7 @@ public class QueryBuilder : StatementBuilder {
}
internal override Statement prepare() {
- Statement stmt = db.prepare(@"SELECT $column_selector $(table_name == null ? "" : @"FROM $((!) table_name)") WHERE $selection $(OrderingTerm.all_to_string(order_by_terms)) $(limit_val > 0 ? @" LIMIT $limit_val OFFSET $offset_val" : "")");
+ 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);
}
@@ -147,13 +188,13 @@ public class QueryBuilder : StatementBuilder {
}
class OrderingTerm {
- Column column;
+ Column? column;
string column_name;
string dir;
public OrderingTerm(Column column, string dir) {
this.column = column;
- this.column_name = column.name;
+ this.column_name = column.to_string();
this.dir = dir;
}
@@ -177,4 +218,21 @@ public class QueryBuilder : StatementBuilder {
}
}
+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<string> 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/qlite/src/row.vala b/qlite/src/row.vala
index be459719..d3807f41 100644
--- a/qlite/src/row.vala
+++ b/qlite/src/row.vala
@@ -10,15 +10,21 @@ public class Row {
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[stmt.column_name(i)] = stmt.column_text(i);
+ text_map[column_name] = stmt.column_text(i);
break;
case INTEGER:
- int_map[stmt.column_name(i)] = (long) stmt.column_int64(i);
+ int_map[column_name] = (long) stmt.column_int64(i);
break;
case FLOAT:
- real_map[stmt.column_name(i)] = stmt.column_double(i);
+ real_map[column_name] = stmt.column_double(i);
break;
}
}
@@ -28,27 +34,54 @@ public class Row {
return field[this];
}
- public string? get_text(string field) {
- if (text_map.has_key(field)) {
- return text_map[field];
+ 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) {
- return int_map[field];
+ 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 bool has_integer(string field) {
- return int_map.has_key(field);
+ public double get_real(string field, string? table = null, double def = 0) {
+ return real_map[field_name(field, table)] ?? def;
}
- public double get_real(string field, double def = 0) {
- return real_map[field] ?? 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 bool has_real(string field) {
- return real_map.has_key(field) && real_map[field] != 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 + "}";
}
}
diff --git a/qlite/src/table.vala b/qlite/src/table.vala
index 7fa2fc62..6c6ef9de 100644
--- a/qlite/src/table.vala
+++ b/qlite/src/table.vala
@@ -8,6 +8,8 @@ public class Table {
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;
@@ -17,6 +19,37 @@ public class Table {
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) {
+ error(@"Qlite Error: Rebuilding FTS index: $(e.message)");
+ }
}
public void unique(Column[] columns, string? on_conflict = null) {
@@ -37,6 +70,10 @@ public class Table {
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;
@@ -58,6 +95,15 @@ public class Table {
return db.select(columns).from(this);
}
+ private MatchQueryBuilder match_query() {
+ ensure_init();
+ return db.match_query(this);
+ }
+
+ public MatchQueryBuilder match(Column<string> column, string query) {
+ return match_query().match(column, query);
+ }
+
public InsertBuilder insert() {
ensure_init();
return db.insert().into(this);
@@ -99,7 +145,7 @@ public class Table {
for (int i = 0; i < columns.length; i++) {
Column c = columns[i];
if (c.min_version <= version && c.max_version >= version) {
- sql += @"$(!first ? "," : "") $c";
+ sql += @"$(!first ? "," : "") $(c.to_column_definition())";
first = false;
}
}
@@ -107,7 +153,14 @@ public class Table {
try {
db.exec(sql);
} catch (Error e) {
- error("Qlite Error: Create table at version");
+ 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)");
+ }
}
}
@@ -116,9 +169,9 @@ public class Table {
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");
+ db.exec(@"ALTER TABLE $name ADD COLUMN $(c.to_column_definition())");
} catch (Error e) {
- error("Qlite Error: Add columns for version");
+ error(@"Qlite Error: Add columns for version: $(e.message)");
}
}
}
@@ -146,7 +199,7 @@ public class Table {
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 volumns for version change");
+ error(@"Qlite Error: Delete columns for version change: $(e.message)");
}
}
}
@@ -156,7 +209,7 @@ public class Table {
try {
db.exec(stmt);
} catch (Error e) {
- error("Qlite Error: Post");
+ error(@"Qlite Error: Post: $(e.message)");
}
}
}
generated by cgit v1.2.3-70-g09d2 (git 2.47.0) at 2024-11-10 22:55:32 +0000