| 1 |
using Sqlite; |
| 2 |
|
| 3 |
namespace Qlite { |
| 4 |
|
| 5 |
public class QueryBuilder : StatementBuilder { |
| 6 |
private bool single_result; |
| 7 |
|
| 8 |
// SELECT [...] |
| 9 |
private string column_selector = "*"; |
| 10 |
private Column[] columns = {}; |
| 11 |
|
| 12 |
// FROM [...] |
| 13 |
protected Table? table; |
| 14 |
protected string? table_name; |
| 15 |
|
| 16 |
// JOIN [...] |
| 17 |
private string joins = ""; |
| 18 |
|
| 19 |
// WHERE [...] |
| 20 |
protected string selection = "1"; |
| 21 |
internal StatementBuilder.AbstractField[] selection_args = {}; |
| 22 |
|
| 23 |
// ORDER BY [...] |
| 24 |
private OrderingTerm[]? order_by_terms = {}; |
| 25 |
|
| 26 |
// GROUP BY [...] |
| 27 |
private string? group_by_term; |
| 28 |
|
| 29 |
// LIMIT [...] OFFSET [...] |
| 30 |
private int limit_val; |
| 31 |
private int offset_val; |
| 32 |
|
| 33 |
internal QueryBuilder(Database db) { |
| 34 |
base(db); |
| 35 |
} |
| 36 |
|
| 37 |
public QueryBuilder select(Column[] columns = {}) { |
| 38 |
this.columns = columns; |
| 39 |
if (columns.length != 0) { |
| 40 |
for (int i = 0; i < columns.length; i++) { |
| 41 |
if (column_selector == "*") { |
| 42 |
column_selector = columns[i].to_string(); |
| 43 |
} else { |
| 44 |
column_selector += ", " + columns[i].to_string(); |
| 45 |
} |
| 46 |
} |
| 47 |
} else { |
| 48 |
column_selector = "*"; |
| 49 |
} |
| 50 |
return this; |
| 51 |
} |
| 52 |
|
| 53 |
public QueryBuilder select_string(string column_selector) { |
| 54 |
this.columns = {}; |
| 55 |
this.column_selector = column_selector; |
| 56 |
return this; |
| 57 |
} |
| 58 |
|
| 59 |
public virtual QueryBuilder from(Table table) { |
| 60 |
if (this.table_name != null) error("cannot use from() multiple times."); |
| 61 |
this.table = table; |
| 62 |
this.table_name = table.name; |
| 63 |
return this; |
| 64 |
} |
| 65 |
|
| 66 |
public virtual QueryBuilder from_name(string table) { |
| 67 |
this.table_name = table; |
| 68 |
return this; |
| 69 |
} |
| 70 |
|
| 71 |
public QueryBuilder outer_join_with<T>(Table table, Column<T> lhs, Column<T> rhs, string? as = null) { |
| 72 |
return outer_join_on(table, @"$lhs = $rhs", as); |
| 73 |
} |
| 74 |
|
| 75 |
public QueryBuilder outer_join_on(Table table, string on, string? as = null) { |
| 76 |
if (as == null) as = table.name; |
| 77 |
joins += @" LEFT OUTER JOIN $(table.name) AS $as ON $on"; |
| 78 |
return this; |
| 79 |
} |
| 80 |
|
| 81 |
public QueryBuilder join_with<T>(Table table, Column<T> lhs, Column<T> rhs, string? as = null) { |
| 82 |
return join_on(table, @"$lhs = $rhs", as); |
| 83 |
} |
| 84 |
|
| 85 |
public QueryBuilder join_on(Table table, string on, string? as = null) { |
| 86 |
if (as == null) as = table.name; |
| 87 |
joins += @" JOIN $(table.name) AS $as ON $on"; |
| 88 |
return this; |
| 89 |
} |
| 90 |
|
| 91 |
internal QueryBuilder join_name(string table_name, string on) { |
| 92 |
joins += @" JOIN $table_name ON $on"; |
| 93 |
return this; |
| 94 |
} |
| 95 |
|
| 96 |
public QueryBuilder where(string selection, string[] selection_args = {}) { |
| 97 |
this.selection = @"($(this.selection)) AND ($selection)"; |
| 98 |
foreach (string arg in selection_args) { |
| 99 |
this.selection_args += new StatementBuilder.StringField(arg); |
| 100 |
} |
| 101 |
return this; |
| 102 |
} |
| 103 |
|
| 104 |
public QueryBuilder with<T>(Column<T> column, string comp, T value) { |
| 105 |
if ((column.unique || column.primary_key) && comp == "=") single_result = true; |
| 106 |
selection_args += new Field<T>(column, value); |
| 107 |
selection = @"($selection) AND $column $comp ?"; |
| 108 |
return this; |
| 109 |
} |
| 110 |
|
| 111 |
public QueryBuilder with_null<T>(Column<T> column) { |
| 112 |
selection = @"($selection) AND $column ISNULL"; |
| 113 |
return this; |
| 114 |
} |
| 115 |
|
| 116 |
public QueryBuilder without_null<T>(Column<T> column) { |
| 117 |
selection = @"($selection) AND $column NOT NULL"; |
| 118 |
return this; |
| 119 |
} |
| 120 |
|
| 121 |
public QueryBuilder order_by(Column column, string dir = "ASC") { |
| 122 |
order_by_terms += new OrderingTerm(column, dir); |
| 123 |
return this; |
| 124 |
} |
| 125 |
|
| 126 |
public QueryBuilder order_by_name(string name, string dir) { |
| 127 |
order_by_terms += new OrderingTerm.by_name(name, dir); |
| 128 |
return this; |
| 129 |
} |
| 130 |
|
| 131 |
public QueryBuilder group_by(Column[] columns) { |
| 132 |
foreach(Column col in columns) { |
| 133 |
if (group_by_term == null) { |
| 134 |
group_by_term = col.to_string(); |
| 135 |
} else { |
| 136 |
group_by_term += @", $col"; |
| 137 |
} |
| 138 |
} |
| 139 |
return this; |
| 140 |
} |
| 141 |
|
| 142 |
public QueryBuilder limit(int limit) { |
| 143 |
if (this.limit_val != 0 && limit > this.limit_val) error("tried to increase an existing limit"); |
| 144 |
this.limit_val = limit; |
| 145 |
return this; |
| 146 |
} |
| 147 |
|
| 148 |
public QueryBuilder offset(int offset) { |
| 149 |
if (this.limit_val == 0) error("limit required before offset"); |
| 150 |
this.offset_val = offset; |
| 151 |
return this; |
| 152 |
} |
| 153 |
|
| 154 |
public QueryBuilder single() { |
| 155 |
this.single_result = true; |
| 156 |
return limit(1); |
| 157 |
} |
| 158 |
|
| 159 |
public int64 count() { |
| 160 |
this.column_selector = @"COUNT($column_selector) AS count"; |
| 161 |
this.single_result = true; |
| 162 |
return row().get_integer("count"); |
| 163 |
} |
| 164 |
|
| 165 |
private Row? row_() { |
| 166 |
if (!single_result) error("query is not suited to return a single row, but row() was called."); |
| 167 |
return iterator().get_next(); |
| 168 |
} |
| 169 |
|
| 170 |
public RowOption row() { |
| 171 |
return new RowOption(row_()); |
| 172 |
} |
| 173 |
|
| 174 |
public T get<T>(Column<T> field, T def = null) { |
| 175 |
return row().get(field, def); |
| 176 |
} |
| 177 |
|
| 178 |
internal override Statement prepare() { |
| 179 |
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" : "")"); |
| 180 |
for (int i = 0; i < selection_args.length; i++) { |
| 181 |
selection_args[i].bind(stmt, i+1); |
| 182 |
} |
| 183 |
return stmt; |
| 184 |
} |
| 185 |
|
| 186 |
public RowIterator iterator() { |
| 187 |
return new RowIterator.from_query_builder(db, this); |
| 188 |
} |
| 189 |
|
| 190 |
class OrderingTerm { |
| 191 |
Column? column; |
| 192 |
string column_name; |
| 193 |
string dir; |
| 194 |
|
| 195 |
public OrderingTerm(Column column, string dir) { |
| 196 |
this.column = column; |
| 197 |
this.column_name = column.to_string(); |
| 198 |
this.dir = dir; |
| 199 |
} |
| 200 |
|
| 201 |
public OrderingTerm.by_name(string column_name, string dir) { |
| 202 |
this.column_name = column_name; |
| 203 |
this.dir = dir; |
| 204 |
} |
| 205 |
|
| 206 |
public string to_string() { |
| 207 |
return @"$column_name $dir"; |
| 208 |
} |
| 209 |
|
| 210 |
public static string all_to_string(OrderingTerm[]? terms) { |
| 211 |
if (terms == null || terms.length == 0) return ""; |
| 212 |
string res = "ORDER BY "+terms[0].to_string(); |
| 213 |
for (int i = 1; i < terms.length; i++) { |
| 214 |
res += @", $(terms[i])"; |
| 215 |
} |
| 216 |
return res; |
| 217 |
} |
| 218 |
} |
| 219 |
} |
| 220 |
|
| 221 |
public class MatchQueryBuilder : QueryBuilder { |
| 222 |
internal MatchQueryBuilder(Database db, Table table) { |
| 223 |
base(db); |
| 224 |
if (table.fts_columns == null) error("MATCH query on non FTS table"); |
| 225 |
from(table); |
| 226 |
join_name(@"_fts_$table_name", @"_fts_$table_name.docid = $table_name.rowid"); |
| 227 |
} |
| 228 |
|
| 229 |
public MatchQueryBuilder match(Column<string> column, string match) { |
| 230 |
if (table == null) error("MATCH must occur after FROM statement"); |
| 231 |
if (!(column in table.fts_columns)) error("MATCH selection on non FTS column"); |
| 232 |
selection_args += new StatementBuilder.StringField(match); |
| 233 |
selection = @"($selection) AND _fts_$table_name.$(column.name) MATCH ?"; |
| 234 |
return this; |
| 235 |
} |
| 236 |
} |
| 237 |
|
| 238 |
} |