1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
|
using Sqlite;
namespace Qlite {
public class QueryBuilder : StatementBuilder {
private bool single_result;
// SELECT [...]
private string column_selector = "*";
private Column[] columns;
// FROM [...]
private Table table;
private string table_name;
// WHERE [...]
private string selection;
private StatementBuilder.Field[] selection_args;
// ORDER BY [...]
private OrderingTerm[] order_by_terms;
// LIMIT [...]
private int limit_val;
protected QueryBuilder(Database db) {
base(db);
}
public QueryBuilder select(Column[]? columns = null) {
this.columns = columns;
if (columns != null) {
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 = null;
this.column_selector = column_selector;
return this;
}
public QueryBuilder from(Table table) throws DatabaseError {
if (this.table_name != null) throw new DatabaseError.ILLEGAL_QUERY("cannot use from() multiple times.");
this.table = table;
this.table_name = table.name;
return this;
}
public QueryBuilder from_name(string table) {
this.table_name = table;
return this;
}
public QueryBuilder where(string selection, string[]? selection_args = null) throws DatabaseError {
if (this.selection != null) throw new DatabaseError.ILLEGAL_QUERY("selection was already done, but where() was called.");
this.selection = selection;
if (selection_args != null) {
this.selection_args = new StatementBuilder.Field[selection_args.length];
for (int i = 0; i < selection_args.length; i++) {
this.selection_args[i] = new StatementBuilder.StringField(selection_args[i]);
}
}
return this;
}
public QueryBuilder with<T>(Column<T> column, string comp, T value) {
if ((column.unique || column.primary_key) && comp == "=") single_result = true;
if (selection == null) {
selection = @"$(column.name) $comp ?";
selection_args = { new StatementBuilder.Field<T>(column, value) };
} else {
selection = @"($selection) AND $(column.name) $comp ?";
StatementBuilder.Field[] selection_args_new = new StatementBuilder.Field[selection_args.length+1];
for (int i = 0; i < selection_args.length; i++) {
selection_args_new[i] = selection_args[i];
}
selection_args_new[selection_args.length] = new Field<T>(column, value);
selection_args = selection_args_new;
}
return this;
}
public QueryBuilder with_null<T>(Column<T> column) {
selection = @"($selection) AND $(column.name) ISNULL";
return this;
}
public QueryBuilder without_null<T>(Column<T> column) {
selection = @"($selection) AND $(column.name) NOT NULL";
return this;
}
private void add_order_by(OrderingTerm term) {
if (order_by_terms == null) {
order_by_terms = { term };
} else {
OrderingTerm[] order_by_terms_new = new OrderingTerm[order_by_terms.length+1];
for (int i = 0; i < order_by_terms.length; i++) {
order_by_terms_new[i] = order_by_terms[i];
}
order_by_terms_new[order_by_terms.length] = term;
order_by_terms = order_by_terms_new;
}
}
public QueryBuilder order_by(Column column, string dir = "ASC") {
add_order_by(new OrderingTerm(column, dir));
return this;
}
public QueryBuilder order_by_name(string name, string dir) {
add_order_by(new OrderingTerm.by_name(name, dir));
return this;
}
public QueryBuilder limit(int limit) {
this.limit_val = limit;
return this;
}
public int64 count() throws DatabaseError {
this.column_selector = @"COUNT($column_selector) AS count";
this.single_result = true;
return row().get_integer("count");
}
public Row? row() throws DatabaseError {
if (!single_result) throw new DatabaseError.NON_UNIQUE("query is not suited to return a single row, but row() was called.");
return iterator().next_value();
}
public T get<T>(Column<T> field) throws DatabaseError {
Row row = row();
if (row != null) {
return row[field];
}
return null;
}
public override Statement prepare() throws DatabaseError {
Statement stmt = db.prepare(@"SELECT $column_selector FROM $table_name $(selection != null ? @"WHERE $selection" : "") $(order_by_terms != null ? OrderingTerm.all_to_string(order_by_terms) : "") $(limit_val > 0 ? @" LIMIT $limit_val" : "")");
for (int i = 0; i < selection_args.length; i++) {
selection_args[i].bind(stmt, i+1);
}
return stmt;
}
public Row.RowIterator iterator() throws DatabaseError {
return new Row.RowIterator.from_query_builder(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.length == 0) return "";
string res = "ORDER BY "+terms[0].to_string();
for (int i = 1; i < terms.length; i++) {
res += @", $(terms[i])";
}
return res;
}
}
}
}
|
