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 }