1 /**
2 Copyright: Copyright (c) 2018-2019, Joakim Brännström. All rights reserved.
3 License: MIT
4 Author: Joakim Brännström (joakim.brannstrom@gmx.com)
5 
6 This module contains an AST representation of a database query. The intention
7 is to encode the structure in the types so errors in the SQL statement are
8 detected at compile time.
9 
10 # Grammar
11 
12 The grammar is expressed in PEG form using the same terminology as the dub
13 package Pegged. It has not been verified to be correct so may contain errors.
14 
15 This is simplified to only contain those parts of the grammar that is needed to
16 pretty print constructed SQL queries. It is not intended to be a grammar that
17 can correctly parse SQL. Thus it contains "blobs of data" that the grammar do
18 not care about. The important part is to keep the structure.  Where to insert
19 "blobs".
20 
21 A secondary purpose is to give some compile time guarantees of the constructed
22 SQL queries. If it compiles it is reasonably assured to be correct.
23 
24 When not necessary for pretty printing as a valid SQL blanks and such are
25 skipped in the grammar definition.
26 
27 ```sql
28 SQL         <- blank* Query (spacing / eoi)
29 
30 Query       <- Select
31 
32 # --- SELECT START ---
33 Select      <- "SELECT" :blank+ ResultColumns :blank+ From? Where? GroupBy? (Window :blank+)? OrderBy? Values? Limit?
34 
35 ResultColumns       <- ResultColumn ("," ResultColumn)*
36 ResultColumn        <- Star / ResultColumnExpr
37 ResultColumnExpr    <- Query / Blob
38 
39 From    <- :blank+ "FROM" :blank+ (TableOrSubQueries / Blob)
40 Where   <- :blank+ "WHERE" :blank+ WhereExpr*
41 GroupBy <- :blank+ "GROUP BY" :blank+ Expr ("," Expr)* (:blank+ "HAVING" Expr)?
42 Window  <- Blob
43 
44 WhereExpr   <- Expr (:blank+ WhereOp :blank+ Expr)?
45 WhereOp     <- "AND" / "OR"
46 
47 OrderBy         <- :blank+ "ORDER BY" :blank+ OrderingTerm ("," OrderingTerm)
48 OrderingTerm    <- Expr :blank+ OrderingTermSort?
49 OrderingTermSort <- "ASC" / "DESC" / ""
50 
51 Limit <- "LIMIT" :blank+ Expr :blank+ (("OFFSET" :blank+ Expr) / ("," Expr))?
52 
53 TableOrSubQueries       <- TableOrQuery ("," TableOrSubQuery)*
54 TableOrSubQuery         <- TableOrSubQuerySelect / ("(" TableOrSubQueries ")") / (TableRef Blob*) / Blob
55 TableOrSubQuerySelect   <- "(" Select ")" TableAlias?
56 
57 # --- SELECT END ---
58 
59 # --- INSERT START ---
60 Insert          <- InsertOpt :blank+ "INTO" :blank+ TableRef TableAlias? InsertColumns? InsertValues
61 InsertOpt       <- "INSERT" / "REPLACE" / "INSERT OR REPLACE" / "INSERT OR ROLLBACK" / "INSERT OR ABORT" / "INSERT OR FAIL" / "INSERT OR IGNORE"
62 InsertColumns    <- :blank+ "(" ColumnName ("," ColumnName)* ")"
63 InsertValues    <- :blank+ (Values / Select / "DEFAULT VALUES")
64 ColumnName      <- identifier
65 
66 # --- INSERT END ---
67 
68 # --- DELETE START ---
69 Delete          <- "DELETE FROM" :blank+ TableRef Where?
70 # --- DELETE END ---
71 
72 # Reference an existing table
73 TableRef            <- SchemaName? TableName TableAlias?
74 
75 Values  <- :blank+ "VALUES" "(" Value ")" ("(" Value ")")*
76 Value   <- Expr ("," Expr)*
77 
78 TableAlias  <- :blank+ "AS" :blank+ identifier
79 
80 Expr        <- Blob
81 # Not representable in the grammar because it can be anything without a formal
82 # terminator. Its purpose is to be an injection point of user data.
83 Blob        <- ""
84 
85 SchemaName  <- identifier "."
86 TableName   <- identifier
87 Star        <- "*"
88 ```
89 
90 ## Grammar Encoding
91 
92  * `SumType` is used when possible.
93  * `None` is the first member of a `SumType` when the node is optional.
94  * Literals are used as is.
95  * All nodes have a `toString`.
96 */
97 module miniorm.query_ast;
98 
99 import std.array : empty;
100 import std.conv : to;
101 import std.format : formattedWrite, format;
102 import std.meta : AliasSeq;
103 import std.range.primitives : put, isOutputRange;
104 import std.traits : TemplateArgsOf;
105 
106 import sumtype;
107 
108 @safe:
109 
110 /// A SQL statement.
111 struct Sql {
112     Query query;
113     alias query this;
114 
115     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
116         query.toString(w);
117         put(w, ";");
118     }
119 
120     string toString()() {
121         import std.array : appender;
122 
123         auto app = appender!string();
124         this.toString(app);
125         return app.data;
126     }
127 }
128 
129 /** A SQL query.
130  *
131  * The differents between Sql and Query is that this may be nested in other nodes.
132  */
133 struct Query {
134     SumType!(Select, Insert, Delete) value;
135     alias value this;
136 
137     static foreach (T; TemplateArgsOf!(typeof(value))) {
138         this(T n) {
139             value = typeof(value)(n);
140         }
141     }
142 
143     mixin ToStringSumType!value;
144 }
145 
146 // #########################################################################
147 /// # Select START
148 // #########################################################################
149 
150 /// A Select statement.
151 struct Select {
152     ResultColumns columns;
153 
154     /// Optional parts of the statement. At least one must in the end be active.
155     SumType!(None, From) from;
156     SumType!(None, Where) where;
157     //SumType!(None, Window) window_;
158     SumType!(None, OrderBy) orderBy;
159     SumType!(None, Limit) limit;
160 
161     mixin(makeAssign!(typeof(this))(["columns", "from", "where", "orderBy", "limit"]));
162 
163     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
164         put(w, "SELECT ");
165         columns.toString(w);
166 
167         put(w, " ");
168 
169         // TODO: add an assert that at least one of opts is not None?
170         from.match!((None v) {}, (From v) { v.toString(w); });
171         where.match!((None v) {}, (Where v) { v.toString(w); });
172         //window.match!((None v) {}, (Window v) { v.toString(w); });
173         orderBy.match!((None v) {}, (OrderBy v) { v.toString(w); });
174         limit.match!((None v) {}, (Limit v) { v.toString(w); });
175     }
176 }
177 
178 struct ResultColumns {
179     ResultColumn required;
180     ResultColumn[] optional;
181 
182     this(ResultColumn r, ResultColumn[] o = null) {
183         required = r;
184         optional = o;
185     }
186 
187     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
188         required.toString(w);
189         foreach (v; optional) {
190             put(w, ",");
191             v.toString(w);
192         }
193     }
194 }
195 
196 struct ResultColumn {
197     SumType!(Star, ResultColumnExpr) value;
198     mixin ToStringSumType!(value);
199     mixin(makeCtor!(typeof(value))("value"));
200     mixin(makeAssign!(typeof(this))(["value"]));
201 }
202 
203 struct ResultColumnExpr {
204     SumType!(Blob, Query*) value;
205     mixin ToStringSumType!value;
206     mixin(makeCtor!(typeof(value))("value"));
207     mixin(makeAssign!(typeof(this))(["value"]));
208 }
209 
210 struct From {
211     SumType!(TableOrSubQueries, Blob) value;
212     alias value this;
213     mixin(makeCtor!(typeof(value))("value"));
214     mixin(makeAssign!(typeof(this))(["value"]));
215 
216     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
217         put(w, "FROM ");
218         value.match!((TableOrSubQueries v) => v.toString(w), (Blob v) {
219             v.toString(w);
220         });
221     }
222 }
223 
224 struct Where {
225     SumType!(None, WhereExpr) value;
226     alias value this;
227     mixin(makeCtor!(typeof(value))("value"));
228     mixin(makeAssign!(typeof(this))(["value"]));
229 
230     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
231         // TODO: should it quote strings?
232         put(w, " WHERE ");
233         value.match!((None v) {}, (WhereExpr v) => v.toString(w));
234     }
235 }
236 
237 struct WhereExpr {
238     static struct Opt {
239         WhereOp op;
240         Expr expr;
241     }
242 
243     Expr required;
244     Opt[] optional;
245 
246     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
247         required.toString(w);
248         foreach (v; optional) {
249             put(w, " ");
250             put(w, v.op.to!string);
251             put(w, " ");
252             v.expr.toString(w);
253         }
254     }
255 }
256 
257 enum WhereOp {
258     AND,
259     OR
260 }
261 
262 struct TableOrSubQueries {
263     TableOrQuery required;
264     TableOrQuery[] optional;
265     mixin(makeAssign!(typeof(this))(["required", "optional"]));
266 
267     ///
268     this(TableOrQuery r, TableOrQuery[] o = null) @safe pure nothrow @nogc {
269         required = r;
270         optional = o;
271     }
272 
273     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
274         required.toString(w);
275         foreach (v; optional) {
276             put(w, ",");
277             v.toString(w);
278         }
279     }
280 }
281 
282 struct TableOrQuery {
283     SumType!(TableOrSubQuerySelect*, TableOrSubQueries*, TableRef, Blob) value;
284     alias value this;
285     mixin(makeCtor!(typeof(value))("value"));
286     mixin(makeAssign!(typeof(this))(["value"]));
287 
288     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
289         value.match!((TableOrSubQuerySelect* v) { v.toString(w); }, (TableOrSubQueries* v) {
290             put(w, "(");
291             v.toString(w);
292             put(w, ")");
293         }, (TableRef v) { v.toString(w); }, (Blob v) { v.toString(w); });
294     }
295 }
296 
297 struct TableOrSubQuerySelect {
298     Select select;
299     TableAlias alias_;
300     mixin(makeAssign!(typeof(this))(["select", "alias_"]));
301 
302     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
303         put(w, "(");
304         select.toString(w);
305         put(w, ")");
306         alias_.toString(w);
307     }
308 }
309 
310 struct OrderBy {
311     OrderingTerm required;
312     OrderingTerm[] optional;
313     mixin(makeAssign!(typeof(this))(["required", "optional"]));
314 
315     this(typeof(required) r, typeof(optional) o = null) @safe pure nothrow @nogc {
316         required = r;
317         optional = o;
318     }
319 
320     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
321         put(w, " ORDER BY ");
322         required.toString(w);
323         foreach (v; optional) {
324             put(w, ",");
325             v.toString(w);
326         }
327     }
328 }
329 
330 struct OrderingTerm {
331     SumType!(None, Blob) expr;
332     SumType!(None, OrderingTermSort) sortTerm;
333     mixin(makeCtor!(typeof(expr))("expr"));
334     mixin(makeAssign!(typeof(this))(["expr", "sortTerm"]));
335 
336     this(Blob expr, OrderingTermSort sortTerm) @safe pure nothrow @nogc {
337         this.expr = expr;
338         this.sortTerm = sortTerm;
339     }
340 
341     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
342         expr.match!((None n) {}, (Blob v) { v.toString(w); });
343         sortTerm.match!((None n) {}, (OrderingTermSort v) {
344             put(w, " ");
345             put(w, v.to!string);
346         });
347     }
348 }
349 
350 enum OrderingTermSort {
351     ASC,
352     DESC,
353 }
354 
355 struct Limit {
356     SumType!(None, Blob) expr;
357     SumType!(None, LimitOffset, Blob) optional;
358     mixin(makeCtor!(typeof(expr))("expr"));
359     mixin(makeAssign!(typeof(this))(["expr", "optional"]));
360 
361     this(Blob expr, LimitOffset l) @safe pure nothrow @nogc {
362         this.expr = expr;
363         this.optional = l;
364     }
365 
366     this(Blob expr, Blob l) @safe pure nothrow @nogc {
367         this.expr = expr;
368         this.optional = l;
369     }
370 
371     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
372         put(w, " LIMIT ");
373         expr.match!((None n) {}, (Blob v) { v.toString(w); });
374         optional.match!((None n) {}, (LimitOffset v) {
375             put(w, " OFFSET ");
376             v.toString(w);
377         }, (Blob v) { put(w, ","); v.toString(w); });
378     }
379 }
380 
381 struct LimitOffset {
382     Blob expr;
383     alias expr this;
384 }
385 
386 // #########################################################################
387 /// # Select END
388 // #########################################################################
389 
390 // #########################################################################
391 /// # Insert START
392 // #########################################################################
393 
394 struct Insert {
395     /// Type of operation to perform.
396     InsertOpt opt;
397     /// Table to operate on.
398     TableRef table;
399     TableAlias alias_;
400     ///
401     InsertColumns columns;
402     ///
403     InsertValues values;
404 
405     ///
406     this(InsertOpt opt, TableRef tbl) @safe pure nothrow @nogc {
407         this.opt = opt;
408         this.table = tbl;
409     }
410 
411     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
412         final switch (opt) with (InsertOpt) {
413         case Insert:
414             put(w, "INSERT");
415             break;
416         case Replace:
417             put(w, "REPLACE");
418             break;
419         case InsertOrReplace:
420             put(w, "INSERT OR REPLACE");
421             break;
422         case InsertOrRollback:
423             put(w, "INSERT OR ROLLBACK");
424             break;
425         case InsertOrAbort:
426             put(w, "INSERT OR ABORT");
427             break;
428         case InsertOrFail:
429             put(w, "INSERT OR FAIL");
430             break;
431         case InsertOrIgnore:
432             put(w, "INSERT OR IGNORE");
433             break;
434         }
435         put(w, " INTO ");
436         table.toString(w);
437         alias_.toString(w);
438         columns.toString(w);
439         values.toString(w);
440     }
441 }
442 
443 struct InsertColumns {
444     SumType!(None, ColumnNames) value;
445     mixin(makeCtor!(typeof(value))("value"));
446     mixin(makeAssign!(typeof(this))(["value"]));
447 
448     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
449         value.match!((None v) {}, (ColumnNames v) => v.toString(w));
450     }
451 }
452 
453 struct ColumnNames {
454     ColumnName required;
455     ColumnName[] optional;
456 
457     this(ColumnName r, ColumnName[] o = null) {
458         required = r;
459         optional = o;
460     }
461 
462     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
463         put(w, " ('");
464         required.toString(w);
465         foreach (v; optional) {
466             put(w, "','");
467             v.toString(w);
468         }
469         put(w, "')");
470     }
471 }
472 
473 alias ColumnName = Blob;
474 
475 struct InsertValues {
476     SumType!(None, Select, Values, InsertDefaultValue) value;
477     mixin(makeCtor!(typeof(value))("value"));
478 
479     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
480         value.match!((None v) {}, (Select v) { put(w, " "); v.toString(w); }, (Values v) {
481             v.toString(w);
482         }, (InsertDefaultValue v) { put(w, " "); v.toString(w); });
483     }
484 }
485 
486 struct Values {
487     Value required;
488     Value[] optional;
489 
490     this(Value r, Value[] o = null) {
491         required = r;
492         optional = o;
493     }
494 
495     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
496         put(w, " VALUES (");
497         required.toString(w);
498         put(w, ")");
499         foreach (v; optional) {
500             put(w, ",(");
501             v.toString(w);
502             put(w, ")");
503         }
504     }
505 }
506 
507 struct Value {
508     Expr required;
509     Expr[] optional;
510 
511     this(Expr r, Expr[] o = null) {
512         required = r;
513         optional = o;
514     }
515 
516     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
517         required.toString(w);
518         foreach (v; optional) {
519             put(w, ",");
520             v.toString(w);
521         }
522     }
523 }
524 
525 alias InsertDefaultValue = Constant!"DEFAULT VALUES";
526 
527 /// Based on those that are valid in SQLite.
528 enum InsertOpt {
529     Insert,
530     Replace,
531     InsertOrReplace,
532     InsertOrRollback,
533     InsertOrAbort,
534     InsertOrFail,
535     InsertOrIgnore,
536 }
537 
538 // #########################################################################
539 /// # Insert END
540 // #########################################################################
541 
542 // #########################################################################
543 /// # Delete START
544 // #########################################################################
545 
546 struct Delete {
547     TableRef table;
548     SumType!(None, Where) where;
549 
550     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
551         put(w, "DELETE FROM ");
552         table.toString(w);
553         where.match!((None v) {}, (Where v) { v.toString(w); });
554     }
555 }
556 
557 // #########################################################################
558 /// # Delete END
559 // #########################################################################
560 
561 struct TableAlias {
562     string value;
563     alias value this;
564 
565     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
566         if (value.empty)
567             return;
568         put(w, " AS ");
569         put(w, value);
570     }
571 }
572 
573 /// Reference to a table with options to reference another schema and/or create an alias.
574 struct TableRef {
575     SumType!(None, SchemaName) schemaName;
576     string tableName;
577     SumType!(None, TableAlias) tableAlias;
578 
579     this(SchemaName schema, string name, TableAlias alias_) {
580         schemaName = schema;
581         tableName = name;
582         tableAlias = alias_;
583     }
584 
585     /// A ref to a table that rename it via an "AS" to `alias_`.
586     this(string name, TableAlias alias_) {
587         tableName = name;
588         tableAlias = alias_;
589     }
590 
591     /// A simple ref to a table.
592     this(string tableName) {
593         this.tableName = tableName;
594     }
595 
596     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
597         schemaName.match!((auto ref v) => v.toString(w));
598         put(w, tableName);
599         tableAlias.match!((auto ref v) => v.toString(w));
600     }
601 }
602 
603 struct SchemaName {
604     string value;
605     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
606         put(w, value);
607         put(w, ".");
608     }
609 }
610 
611 struct Blob {
612     string value;
613 
614     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
615         put(w, value);
616     }
617 }
618 
619 alias Expr = Blob;
620 alias None = Constant!(string.init);
621 alias Star = Constant!"*";
622 alias Window = Blob;
623 
624 /// A node representing a constant value.
625 struct Constant(string s) {
626     string value = s;
627     alias value this;
628 
629     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
630         put(w, value);
631     }
632 }
633 
634 private:
635 
636 /// Create a match that calls `.toString(w)` on all matches of the SumType.
637 mixin template ToStringSumType(alias member) {
638     void toString(Writer)(ref Writer w) if (isOutputRange!(Writer, char)) {
639         static string autoMatch(alias member)() {
640             string rval = q{%s.match!(}.format(member.stringof);
641             static foreach (T; TemplateArgsOf!(typeof(member))) {
642                 rval ~= q{(%s v) => v.toString(w),}.format(T.stringof);
643             }
644             rval ~= ");";
645             return rval;
646         }
647 
648         mixin(autoMatch!member);
649     }
650 }
651 
652 string makeBuild(ArgT, string member, string funcName)() {
653     string rval = q{auto %s(%s v)}.format(funcName, ArgT.stringof);
654     rval ~= "{";
655     rval ~= member ~ " = v;";
656     rval ~= q{return this;};
657     rval ~= "}";
658     return rval;
659 }
660 
661 /// ctors for all template arguments of member.
662 string makeCtor(SumT)(string var) {
663     string rval;
664     static foreach (T; TemplateArgsOf!SumT) {
665         rval ~= q{this(%1$s n) @safe pure nothrow {
666             this.%2$s = n;
667         }}.format(T.stringof, var);
668         rval ~= "\n";
669     }
670     return rval;
671 }
672 
673 /// an opAssign that assign to `var` of type `SumT`.
674 string makeAssign(T)(string[] members) {
675     string rval = format(`void opAssign(%1$s rhs) @trusted pure nothrow @nogc {`, T.stringof);
676     foreach (m; members) {
677         rval ~= format("%1$s = rhs.%1$s;", m);
678     }
679     rval ~= "}\n";
680     return rval;
681 }
682 
683 /// Returns: a string that can be mixed in to create a setter for the member
684 mixin template makeBuilder(members...) {
685     static string buildMember(alias member)() {
686         enum memberStr = member.stringof;
687         static assert(memberStr[$ - 1] == '_', "member must end with '_': " ~ memberStr);
688 
689         enum Type = typeof(member).stringof;
690         string rval = q{auto %s(%s v)}.format(member.stringof[0 .. $ - 1], Type);
691         rval ~= "{";
692         rval ~= memberStr ~ " = v;";
693         rval ~= q{return this;};
694         rval ~= "}";
695         return rval;
696     }
697 
698     static foreach (member; members) {
699         mixin(buildMember!member);
700     }
701 }
702 
703 version (unittest) {
704     import unit_threaded.assertions : shouldEqual;
705 }
706 
707 // TODO: investigate why this is needed to be system.
708 @system:
709 
710 @("shall convert a query at compile time to SQL")
711 unittest {
712     enum q = Select().Query.Sql.toString;
713     q.shouldEqual("SELECT * ;");
714 }
715 
716 @("shall convert a Select using From to SQL")
717 unittest {
718     // arrange
719     Select qblob, qtblRef, q;
720     // act
721     qblob.from = Blob("foo").From;
722     qtblRef.from = TableOrSubQueries(TableOrQuery(TableRef("foo"))).From;
723     // assert
724     immutable expected = "SELECT * FROM foo;";
725     foreach (s; [qblob, qtblRef])
726         s.Query.Sql.toString.shouldEqual(expected);
727 }
728 
729 @("shall convert a Select using a subquery in FROM to SQL")
730 unittest {
731     // arrange
732     Select qblob, qAlias, qRef, qsubBlob;
733     // act
734     qsubBlob.from = Blob("foo I dance").From;
735     qblob.from = TableOrSubQueries(TableOrQuery(new TableOrSubQuerySelect(qsubBlob))).From;
736     qAlias.from = TableOrSubQueries(TableOrQuery(new TableOrSubQuerySelect(qsubBlob,
737             TableAlias("bar")))).From;
738     qRef.from = TableOrSubQueries(TableOrQuery(new TableOrSubQueries(TableRef("foo")
739             .TableOrQuery, [TableRef("smurf").TableOrQuery]))).From;
740     // assert
741     // a subquery as a blob that should be represented as-is.
742     qblob.Query.Sql.toString.shouldEqual("SELECT * FROM (SELECT * FROM foo I dance);");
743     // a subquery as a named select.
744     qAlias.Query.Sql.toString.shouldEqual("SELECT * FROM (SELECT * FROM foo I dance) AS bar;");
745     // multiple table refs.
746     qRef.Query.Sql.toString.shouldEqual("SELECT * FROM (foo,smurf);");
747 }
748 
749 @("shall convert a Select using an OrderBy to SQL")
750 unittest {
751     // arrange
752     Select q;
753     q.from = Blob("foo").From;
754     // act
755     q.orderBy = OrderBy(OrderingTerm(Blob("bar")));
756     // assert
757     q.Query.Sql.toString.shouldEqual("SELECT * FROM foo ORDER BY bar;");
758 }
759 
760 @("shall convert a Select using Where to SQL")
761 unittest {
762     // arrange
763     Select q;
764     // act
765     q.from = Blob("foo").From;
766     q.where = WhereExpr(Expr("foo = bar"), [
767             WhereExpr.Opt(WhereOp.OR, Expr("batman NOT NULL"))
768             ]).Where;
769     // assert
770     q.Query.Sql.toString.shouldEqual("SELECT * FROM foo WHERE foo = bar OR batman NOT NULL;");
771 }
772 
773 @("shall convert an Insert using default values to SQL")
774 unittest {
775     // act
776     auto q = Insert(InsertOpt.Insert, TableRef("foo"));
777     q.values = InsertValues(InsertDefaultValue.init);
778     // assert
779     q.Query.Sql.toString.shouldEqual("INSERT INTO foo DEFAULT VALUES;");
780 }
781 
782 @("shall convert an Insert using specific values to SQL")
783 unittest {
784     // act
785     auto q = Insert(InsertOpt.Insert, TableRef("foo"));
786     q.values = InsertValues(Values(Value(Expr("1"), [Expr("2")]), [
787                 Value(Expr("4"), [Expr("5")])
788             ]));
789     // assert
790     q.Query.Sql.toString.shouldEqual("INSERT INTO foo VALUES (1,2),(4,5);");
791 }
792 
793 @("shall convert an Insert using select stmt to SQL")
794 unittest {
795     // act
796     Select s;
797     s.from = Blob("bar").From;
798     auto q = Insert(InsertOpt.Insert, TableRef("foo"));
799     q.values = InsertValues(s);
800     // assert
801     q.Query.Sql.toString.shouldEqual("INSERT INTO foo SELECT * FROM bar;");
802 }
803 
804 @("shall convert a Select with a limit to SQL")
805 unittest {
806     // arrange
807     Select q;
808     q.from = Blob("foo").From;
809     // act
810     q.limit = Limit(Blob("10"), LimitOffset(Blob("42")));
811     // assert
812     q.Query.Sql.toString.shouldEqual("SELECT * FROM foo LIMIT 10 OFFSET 42;");
813 }