aboutsummaryrefslogtreecommitdiff
path: root/qlite/src/query_builder.vala
diff options
context:
space:
mode:
authorfiaxh <fiaxh@users.noreply.github.com>2018-08-31 16:25:51 +0200
committerGitHub <noreply@github.com>2018-08-31 16:25:51 +0200
commitecb18afdb51b3230ea451a27a5b345cf5100f02e (patch)
tree44bae666c4cc31afce3a5d6891f6b4f65dfcebb1 /qlite/src/query_builder.vala
parent9e93a77a624aed24402cf1ece69c05aaa0aab600 (diff)
parentf5547076d2397cec5c9d7374bd161f93327488c4 (diff)
downloaddino-ecb18afdb51b3230ea451a27a5b345cf5100f02e.tar.gz
dino-ecb18afdb51b3230ea451a27a5b345cf5100f02e.zip
Merge pull request #415 from bobufa/message-search
Message search
Diffstat (limited to 'qlite/src/query_builder.vala')
-rw-r--r--qlite/src/query_builder.vala92
1 files changed, 75 insertions, 17 deletions
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;
+ }
+}
+
}