diff options
| -rw-r--r-- | qlite/src/database.vala | 5 | ||||
| -rw-r--r-- | qlite/src/query_builder.vala | 45 | ||||
| -rw-r--r-- | qlite/src/table.vala | 49 |
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) { |