miniorm.queries source code

1 module miniorm.queries;
2 
3 import std.algorithm : joiner, map;
4 import std.conv : to;
5 import std.exception : enforce;
6 import std.format : format;
7 import std..string : join;
8 
9 import d2sqlite3;
10 import sumtype;
11 
12 import miniorm.api : Miniorm;
13 import miniorm.exception;
14 import miniorm.schema : tableName, fieldToCol, fieldToCol, ColumnName;
15 
16 public import miniorm.query_ast : OrderingTermSort, InsertOpt;
17 
18 debug (miniorm) import std.stdio : stderr;
19 
20 version (unittest) {
21     import unit_threaded.assertions;
22 }
23 
24 auto select(T)() {
25     return Select!T(tableName!T);
26 }
27 
28 struct Select(T) {
29     import miniorm.query_ast;
30 
31     miniorm.query_ast.Select query;
32 
33     this(miniorm.query_ast.Select q) {
34         this.query = q;
35     }
36 
37     this(string from) {
38         this.query.from = Blob(from).From;
39     }
40 
41     /// Convert to a SQL statement that can e.g. be pretty printed.
42     Sql toSql() {
43         return query.Query.Sql;
44     }
45 
46     /// Count the number of matching rows.
47     auto count() @safe pure {
48         miniorm.query_ast.Select rval = query;
49         rval.columns.required = ResultColumn(ResultColumnExpr(Blob("count(*)")));
50         return Select!T(rval);
51     }
52 
53     /// Order the result by `s` in the order the fields are defined in `T`.
54     auto orderBy(OrderingTermSort s, string[] fields = null) @trusted pure {
55         OrderingTerm required;
56         OrderingTerm[] optional;
57 
58         if (fields) {
59             required = OrderingTerm(Blob("'" ~ fields[0] ~ "'"), s);
60             foreach (f; fields[1 .. $])
61                 optional ~= OrderingTerm(Blob("'" ~ f ~ "'"), s);
62         } else {
63             enum fields_ = fieldToCol!("", T);
64             static foreach (i, field; fields_) {
65                 static if (i == 0)
66                     required = OrderingTerm(Blob(field.quoteColumnName), s);
67                 else
68                     optional ~= OrderingTerm(Blob(field.quoteColumnName), s);
69             }
70         }
71 
72         miniorm.query_ast.Select rval = query;
73         rval.orderBy = OrderBy(required, optional);
74         return Select!T(rval);
75     }
76 
77     /// Limit the query to this number of answers
78     auto limit(long value) @trusted pure {
79         import std.conv : to;
80 
81         miniorm.query_ast.Select rval = query;
82         rval.limit = Limit(Blob(value.to!string));
83         return Select!T(rval);
84     }
85 
86     mixin WhereMixin!(T, typeof(this), miniorm.query_ast.Select);
87 }
88 
89 unittest {
90     static struct Foo {
91         ulong id;
92         string text;
93         ulong ts;
94     }
95 
96     select!Foo.where("foo = :bar", Bind("bar")).or("batman IS NULL")
97         .and("batman = :hero", Bind("hero")).toSql.toString.shouldEqual(
98                 "SELECT * FROM Foo WHERE foo = :bar OR batman IS NULL AND batman = :hero;");
99 }
100 
101 @("shall be possible to have a member of enum type")
102 unittest {
103     static struct Foo {
104         enum MyEnum : string {
105             foo = "batman",
106             bar = "robin",
107         }
108 
109         ulong id;
110         MyEnum enum_;
111     }
112 
113     select!Foo.where("enum_ = 'robin'")
114         .toSql.toString.shouldEqual("SELECT * FROM Foo WHERE enum_ = 'robin';");
115 }
116 
117 auto insert(T)() {
118     return Insert!T(tableName!T).insert;
119 }
120 
121 auto insertOrReplace(T)() {
122     return Insert!T(tableName!T).insertOrReplace;
123 }
124 
125 auto insertOrIgnore(T)() {
126     return Insert!T(tableName!T).insertOrIgnore;
127 }
128 
129 struct Insert(T) {
130     import miniorm.query_ast;
131 
132     miniorm.query_ast.Insert query;
133 
134     this(miniorm.query_ast.Insert q) {
135         this.query = q;
136     }
137 
138     this(string tableName) {
139         this.query.table = TableRef(tableName);
140     }
141 
142     /// Convert to a SQL statement that can e.g. be pretty printed.
143     Sql toSql() {
144         return query.Query.Sql;
145     }
146 
147     void run(ref Miniorm db) {
148         db.run(toSql.toString);
149     }
150 
151     auto op(InsertOpt o) @safe pure nothrow const @nogc {
152         miniorm.query_ast.Insert rval = query;
153         rval.opt = o;
154         return Insert!T(rval);
155     }
156 
157     /// Returns: number of values that the query is sized for.
158     size_t getValues() {
159         return query.values.value.match!((Values v) => 1 + v.optional.length, _ => 0);
160     }
161 
162     /// Returns: number of columns to insert per value.
163     size_t getColumns() {
164         return query.columns.value.match!((ColumnNames v) => 1 + v.optional.length, (None v) => 0);
165     }
166 
167     /// Number of values the user wants to insert.
168     auto values(size_t cnt)
169     in(cnt >= 1, "values must be >=1") {
170         import std.array : array;
171         import std.range : repeat;
172 
173         Value val;
174         val.required = Expr("?");
175         val.optional = query.columns.value.match!((ColumnNames v) => Expr("?")
176                 .repeat(v.optional.length).array, (None v) => null);
177 
178         Values values;
179         foreach (i; 0 .. cnt) {
180             if (i == 0)
181                 values.required = val;
182             else
183                 values.optional ~= val;
184         }
185 
186         miniorm.query_ast.Insert rval = query;
187         rval.values = InsertValues(values);
188         return Insert!T(rval);
189     }
190 
191     /// Insert a new row.
192     auto insert() @safe pure nothrow const {
193         return op(InsertOpt.Insert).setColumns(true);
194     }
195 
196     /// Insert or replace an existing row.
197     auto insertOrReplace() @safe pure nothrow const {
198         return op(InsertOpt.InsertOrReplace).setColumns(false);
199     }
200 
201     auto insertOrIgnore() @safe pure nothrow const {
202         return op(InsertOpt.InsertOrIgnore).setColumns(false);
203     }
204 
205     // TODO the name is bad.
206     /// Specify columns to insert/replace values in.
207     private auto setColumns(bool insert_) @safe pure const {
208         enum fields = fieldToCol!("", T);
209 
210         ColumnNames columns;
211         bool addRequired = true;
212         foreach (field; fields) {
213             if (field.isPrimaryKey && insert_)
214                 continue;
215 
216             if (addRequired) {
217                 columns.required = miniorm.query_ast.ColumnName(field.columnName);
218                 addRequired = false;
219             } else
220                 columns.optional ~= miniorm.query_ast.ColumnName(field.columnName);
221         }
222 
223         miniorm.query_ast.Insert rval = query;
224         rval.columns = InsertColumns(columns);
225         return Insert!T(rval);
226     }
227 }
228 
229 unittest {
230     static struct Foo {
231         ulong id;
232         string text;
233         float val;
234         ulong ts;
235 
236         @ColumnName("version")
237         string version_;
238     }
239 
240     insertOrReplace!Foo.values(1).toSql.toString.shouldEqual(
241             "INSERT OR REPLACE INTO Foo ('id','text','val','ts','version') VALUES (?,?,?,?,?);");
242     insert!Foo.values(1).toSql.toString.shouldEqual(
243             "INSERT INTO Foo ('text','val','ts','version') VALUES (?,?,?,?);");
244 
245     insertOrReplace!Foo.values(2).toSql.toString.shouldEqual(
246             "INSERT OR REPLACE INTO Foo ('id','text','val','ts','version') VALUES (?,?,?,?,?),(?,?,?,?,?);");
247 
248     insertOrIgnore!Foo.values(2).toSql.toString.shouldEqual(
249             "INSERT OR IGNORE INTO Foo ('id','text','val','ts','version') VALUES (?,?,?,?,?),(?,?,?,?,?);");
250 
251     insert!Foo.values(2).toSql.toString.shouldEqual(
252             "INSERT INTO Foo ('text','val','ts','version') VALUES (?,?,?,?),(?,?,?,?);");
253 }
254 
255 unittest {
256     static struct Foo {
257         ulong id;
258         string text;
259         float val;
260         ulong ts;
261     }
262 
263     static struct Bar {
264         ulong id;
265         float value;
266         Foo foo;
267     }
268 
269     insertOrReplace!Bar.values(1).toSql.toString.shouldEqual(
270             "INSERT OR REPLACE INTO Bar ('id','value','foo.id','foo.text','foo.val','foo.ts') VALUES (?,?,?,?,?,?);");
271     insertOrIgnore!Bar.values(1).toSql.toString.shouldEqual(
272             "INSERT OR IGNORE INTO Bar ('id','value','foo.id','foo.text','foo.val','foo.ts') VALUES (?,?,?,?,?,?);");
273     insert!Bar.values(1).toSql.toString.shouldEqual(
274             "INSERT INTO Bar ('value','foo.id','foo.text','foo.val','foo.ts') VALUES (?,?,?,?,?);");
275     insert!Bar.values(3).toSql.toString.shouldEqual(
276             "INSERT INTO Bar ('value','foo.id','foo.text','foo.val','foo.ts') VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?);");
277 }
278 
279 unittest {
280     struct Foo {
281         string text;
282         float val;
283         ulong ts;
284     }
285 
286     struct Bar {
287         float v;
288         Foo foo;
289     }
290 
291     struct Baz {
292         ulong id;
293         float v;
294         Bar xyz;
295         float w;
296     }
297 
298     insertOrReplace!Baz.values(1).toSql.toString.shouldEqual("INSERT OR REPLACE INTO Baz ('id','v','xyz.v','xyz.foo.text','xyz.foo.val','xyz.foo.ts','w') VALUES (?,?,?,?,?,?,?);");
299 }
300 
301 auto delete_(T)() {
302     return Delete!T(tableName!T);
303 }
304 
305 struct Delete(T) {
306     import miniorm.query_ast;
307 
308     miniorm.query_ast.Delete query;
309 
310     this(miniorm.query_ast.Delete q) {
311         this.query = q;
312     }
313 
314     this(string tableName) {
315         this.query.table = TableRef(tableName);
316     }
317 
318     /// Convert to a SQL statement that can e.g. be pretty printed.
319     Sql toSql() {
320         return query.Query.Sql;
321     }
322 
323     void run(ref Miniorm db) {
324         db.run(toSql.toString);
325     }
326 
327     mixin WhereMixin!(T, typeof(this), miniorm.query_ast.Delete);
328 }
329 
330 mixin template WhereMixin(T, QueryT, AstT) {
331     import std.datetime : SysTime;
332     import std.traits : isNumeric, isSomeString;
333 
334     Bind[] binds;
335 
336     /// Add a WHERE clause with a bound value.
337     auto where(string lhs, Bind[] b = null) {
338         binds ~= b;
339         return this.where(lhs);
340     }
341 
342     auto where(string lhs, Bind b) {
343         return this.where(lhs, [b]);
344     }
345 
346     /// Add a WHERE condition.
347     private auto where(string condition) @trusted pure {
348         import miniorm.query_ast;
349 
350         static struct WhereOptional {
351             QueryT value;
352             alias value this;
353 
354             private auto where(string condition, WhereOp op, Bind[] b) @trusted pure {
355                 QueryT rval = value;
356                 rval.binds ~= b;
357 
358                 Where w = value.query.where.tryMatch!((Where v) => v);
359                 WhereExpr we = w.tryMatch!((WhereExpr v) => v);
360                 we.optional ~= WhereExpr.Opt(op, Expr(condition));
361                 rval.query.where = Where(we);
362                 return WhereOptional(rval);
363             }
364 
365             WhereOptional and(string lhs, Bind[] b = null) @safe pure {
366                 return where(lhs, WhereOp.AND, b);
367             }
368 
369             WhereOptional and(string lhs, Bind b) @safe pure {
370                 return where(lhs, WhereOp.AND, [b]);
371             }
372 
373             WhereOptional or(string lhs, Bind[] b = null) @safe pure {
374                 return where(lhs, WhereOp.OR, b);
375             }
376 
377             WhereOptional or(string lhs, Bind b) @safe pure {
378                 return where(lhs, WhereOp.OR, [b]);
379             }
380         }
381 
382         AstT ast = query;
383         ast.where = WhereExpr(Expr(condition)).Where;
384 
385         auto rval = QueryT(ast);
386         rval.binds = binds;
387 
388         return WhereOptional(rval);
389     }
390 }
391 
392 struct Bind {
393     import std.range : isOutputRange, put;
394 
395     alias Key = SumType!(string, int);
396 
397     Key key;
398 
399     this(string k) {
400         key = Key(k);
401     }
402 
403     this(int k) {
404         key = Key(k);
405     }
406 
407     string toString() @safe pure const {
408         import std.array : appender;
409 
410         auto buf = appender!string;
411         toString(buf);
412         return buf.data;
413     }
414 
415     void toString(Writer)(ref Writer w) const if (isOutputRange!(Writer, char)) {
416         key.match!((string k) { put(w, ":"); put(w, k); }, (int k) {
417             put(w, k.to!string);
418         });
419     }
420 }
421 
422 unittest {
423     static struct Foo {
424         ulong id;
425         string text;
426         ulong ts;
427     }
428 
429     delete_!Foo.where("text = privet").and("ts > 123")
430         .toSql.toString.shouldEqual("DELETE FROM Foo WHERE text = privet AND ts > 123;");
431 }
432 
433 auto count(T)() {
434     return Count!T(tableName!T);
435 }
436 
437 struct Count(T) {
438     import miniorm.query_ast : Sql;
439 
440     Select!T query_;
441 
442     this(miniorm.query_ast.Select q) {
443         this.query_ = Select!T(q);
444     }
445 
446     this(string from) {
447         this.query_ = Select!T(from).count;
448     }
449 
450     /// Convert to a SQL statement that can e.g. be pretty printed.
451     Sql toSql() {
452         return query_.toSql;
453     }
454 
455     private ref miniorm.query_ast.Select query() @safe pure nothrow @nogc {
456         return query_.query;
457     }
458 
459     mixin WhereMixin!(T, typeof(this), miniorm.query_ast.Select);
460 }
461 
462 unittest {
463     static struct Foo {
464         ulong id;
465         string text;
466         ulong ts;
467     }
468 
469     count!Foo.where("text = privet").and("ts > 123").toSql.toString.shouldEqual(
470             "SELECT count(*) FROM Foo WHERE text = privet AND ts > 123;");
471 }