Debian Codegraph @

qlite/src/query_builder.vala

raw
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 }