aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--qlite/src/database.vala5
-rw-r--r--qlite/src/query_builder.vala45
-rw-r--r--qlite/src/table.vala49
3 files changed, 89 insertions, 10 deletions
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..9ca5bf25 100644
--- a/qlite/src/query_builder.vala
+++ b/qlite/src/query_builder.vala
@@ -10,12 +10,15 @@ 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 = {};
@@ -50,18 +53,23 @@ 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 join(string table, string on) {
+ joins += @"JOIN $table 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;
@@ -74,17 +82,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 $table_name.$(column.name) $comp ?";
return this;
}
public QueryBuilder with_null<T>(Column<T> column) {
- selection = @"($selection) AND $(column.name) ISNULL";
+ selection = @"($selection) AND $table_name.$(column.name) ISNULL";
return this;
}
public QueryBuilder without_null<T>(Column<T> column) {
- selection = @"($selection) AND $(column.name) NOT NULL";
+ selection = @"($selection) AND $table_name.$(column.name) NOT NULL";
return this;
}
@@ -135,7 +143,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 $(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);
}
@@ -177,4 +185,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(@"_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/table.vala b/qlite/src/table.vala
index 00b4ef00..8725c4c9 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;
@@ -19,6 +21,33 @@ public class Table {
this.constraints = constraints;
}
+ 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";
+ 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");
+ }
+ }
+
public void unique(Column[] columns, string? on_conflict = null) {
constraints += ", UNIQUE (";
bool first = true;
@@ -37,6 +66,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 +91,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);
@@ -107,6 +149,13 @@ public class Table {
} catch (Error e) {
error("Qlite Error: Create table at version");
}
+ foreach (string stmt in create_statements) {
+ try {
+ db.exec(stmt);
+ } catch (Error e) {
+ error("Qlite Error: Create table at version");
+ }
+ }
}
public void add_columns_for_version(long old_version, long new_version) {