using Sqlite; namespace Qlite { public class QueryBuilder : StatementBuilder { private bool single_result; // SELECT [...] private string column_selector = "*"; private Column[] columns = {}; // FROM [...] protected Table? table; protected string? table_name; // JOIN [...] private string joins = ""; // WHERE [...] protected string selection = "1"; internal StatementBuilder.AbstractField[] selection_args = {}; // ORDER BY [...] private OrderingTerm[]? order_by_terms = {}; // LIMIT [...] OFFSET [...] private int limit_val; private int offset_val; internal QueryBuilder(Database db) { base(db); } public QueryBuilder select(Column[] columns = {}) { this.columns = columns; if (columns.length == 0) { for (int i = 0; i < columns.length; i++) { if (column_selector == "*") { column_selector = columns[0].name; } else { column_selector += ", " + columns[i].name; } } } else { column_selector = "*"; } return this; } public QueryBuilder select_string(string column_selector) { this.columns = {}; this.column_selector = column_selector; return this; } 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 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; foreach (string arg in selection_args) { this.selection_args += new StatementBuilder.StringField(arg); } return this; } public QueryBuilder with(Column column, string comp, T value) { if ((column.unique || column.primary_key) && comp == "=") single_result = true; selection_args += new Field(column, value); selection = @"($selection) AND $table_name.$(column.name) $comp ?"; return this; } public QueryBuilder with_null(Column column) { selection = @"($selection) AND $table_name.$(column.name) ISNULL"; return this; } public QueryBuilder without_null(Column column) { selection = @"($selection) AND $table_name.$(column.name) NOT NULL"; return this; } public QueryBuilder order_by(Column column, string dir = "ASC") { order_by_terms += new OrderingTerm(column, dir); return this; } public QueryBuilder order_by_name(string name, string dir) { order_by_terms += new OrderingTerm.by_name(name, dir); 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; return this; } public QueryBuilder offset(int offset) { if (this.limit_val == 0) error("limit required before offset"); this.offset_val = offset; return this; } public QueryBuilder single() { this.single_result = true; return limit(1); } public int64 count() { this.column_selector = @"COUNT($column_selector) AS count"; this.single_result = true; return row().get_integer("count"); } private Row? row_() { if (!single_result) error("query is not suited to return a single row, but row() was called."); return iterator().get_next(); } public RowOption row() { return new RowOption(row_()); } public T get(Column field, T def = null) { return row().get(field, def); } internal override Statement prepare() { 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); } return stmt; } public RowIterator iterator() { return new RowIterator.from_query_builder(db, this); } class OrderingTerm { Column column; string column_name; string dir; public OrderingTerm(Column column, string dir) { this.column = column; this.column_name = column.name; this.dir = dir; } public OrderingTerm.by_name(string column_name, string dir) { this.column_name = column_name; this.dir = dir; } public string to_string() { return @"$column_name $dir"; } public static string all_to_string(OrderingTerm[]? terms) { if (terms == null || terms.length == 0) return ""; string res = "ORDER BY "+terms[0].to_string(); for (int i = 1; i < terms.length; i++) { res += @", $(terms[i])"; } return res; } } } 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 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; } } }