tests.d source code

1 module tests.d;
2 
3 version (unittest)  : import d2sqlite3;
4 import std.exception : assertThrown, assertNotThrown;
5 import std.string : format;
6 import std.typecons : Nullable;
7 import std.conv : hexString;
8 
9 unittest  // Test version of SQLite library
10 {
11     import std.string : startsWith;
12 
13     assert(versionString.startsWith("3."));
14     assert(versionNumber >= 3_008_007);
15 }
16 
17 unittest  // COV
18 {
19     auto ts = threadSafe;
20 }
21 
22 unittest  // Configuration logging and db.close()
23 {
24     static extern (C) void loggerCallback(void* arg, int code, const(char)* msg) nothrow {
25         ++*(cast(int*) arg);
26     }
27 
28     int marker = 42;
29 
30     shutdown();
31     config(SQLITE_CONFIG_MULTITHREAD);
32     config(SQLITE_CONFIG_LOG, &loggerCallback, &marker);
33     initialize();
34 
35     {
36         auto db = Database(":memory:");
37         try {
38             db.run("DROP TABLE wtf");
39         } catch (Exception e) {
40         }
41         db.close();
42     }
43     assert(marker == 43);
44 
45     shutdown();
46     config(SQLITE_CONFIG_LOG, null, null);
47     initialize();
48 
49     {
50         auto db = Database(":memory:");
51         try {
52             db.run("DROP TABLE wtf");
53         } catch (Exception e) {
54         }
55     }
56     assert(marker == 43);
57 }
58 
59 unittest  // Database.tableColumnMetadata()
60 {
61     auto db = Database(":memory:");
62     db.run("CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,
63             val FLOAT NOT NULL)");
64     assert(db.tableColumnMetadata("test",
65             "id") == TableColumnMetadata("INTEGER", "BINARY", false, true, true));
66     assert(db.tableColumnMetadata("test", "val") == TableColumnMetadata("FLOAT",
67             "BINARY", true, false, false));
68 }
69 
70 unittest  // Database.run()
71 {
72     auto db = Database(":memory:");
73     int i;
74     db.run(`SELECT 1; SELECT 2;`, (ResultRange r) {
75         i = r.oneValue!int;
76         return false;
77     });
78     assert(i == 1);
79 }
80 
81 unittest  // Database.errorCode()
82 {
83     auto db = Database(":memory:");
84     db.run(`SELECT 1;`);
85     assert(db.errorCode == SQLITE_OK);
86     try
87         db.run(`DROP TABLE non_existent`);
88     catch (SqliteException e)
89         assert(db.errorCode == SQLITE_ERROR);
90 }
91 
92 unittest  // Database.config
93 {
94     auto db = Database(":memory:");
95     db.run(`
96         CREATE TABLE test (val INTEGER);
97         CREATE TRIGGER test_trig BEFORE INSERT ON test
98         BEGIN
99             SELECT RAISE(FAIL, 'Test failed');
100         END;
101     `);
102     int res = 42;
103     db.config(SQLITE_DBCONFIG_ENABLE_TRIGGER, 0, &res);
104     assert(res == 0);
105     db.execute("INSERT INTO test (val) VALUES (1)");
106 }
107 
108 unittest  // Database.createFunction(ColumnData[]...)
109 {
110     string myList(ColumnData[] args...) {
111         import std.array : appender;
112         import std.string : format, join;
113 
114         auto app = appender!(string[]);
115         foreach (arg; args) {
116             if (arg.type == SqliteType.TEXT)
117                 app.put(`"%s"`.format(arg));
118             else
119                 app.put("%s".format(arg));
120         }
121         return app.data.join(", ");
122     }
123 
124     auto db = Database(":memory:");
125     db.createFunction("my_list", &myList);
126     auto list = db.execute("SELECT my_list(42, 3.14, 'text', x'00FF', NULL)").oneValue!string;
127     assert(list == `42, 3.14, "text", [0, 255], null`, list);
128 }
129 
130 unittest  // Database.createFunction() exceptions
131 {
132     import std.exception : assertThrown;
133 
134     int myFun(int a, int b = 1) {
135         return a * b;
136     }
137 
138     auto db = Database(":memory:");
139     db.createFunction("myFun", &myFun);
140     assertThrown!SqliteException(db.execute("SELECT myFun()"));
141     assertThrown!SqliteException(db.execute("SELECT myFun(1, 2, 3)"));
142     assert(db.execute("SELECT myFun(5)").oneValue!int == 5);
143     assert(db.execute("SELECT myFun(5, 2)").oneValue!int == 10);
144 
145     db.createFunction("myFun", null);
146     assertThrown!SqliteException(db.execute("SELECT myFun(5)"));
147     assertThrown!SqliteException(db.execute("SELECT myFun(5, 2)"));
148 }
149 
150 unittest  // Database.setUpdateHook()
151 {
152     int i;
153     auto db = Database(":memory:");
154     db.setUpdateHook((int type, string dbName, string tableName, long rowid) {
155         assert(type == SQLITE_INSERT);
156         assert(dbName == "main");
157         assert(tableName == "test");
158         assert(rowid == 1);
159         i = 42;
160     });
161     db.run("CREATE TABLE test (val INTEGER);
162             INSERT INTO test VALUES (100)");
163     assert(i == 42);
164     db.setUpdateHook(null);
165 }
166 
167 unittest  // Database commit and rollback hooks
168 {
169     int i;
170     auto db = Database(":memory:");
171     db.setCommitHook({ i = 42; return SQLITE_OK; });
172     db.setRollbackHook({ i = 666; });
173     db.begin();
174     db.execute("CREATE TABLE test (val INTEGER)");
175     db.rollback();
176     assert(i == 666);
177     db.begin();
178     db.execute("CREATE TABLE test (val INTEGER)");
179     db.commit();
180     assert(i == 42);
181     db.setCommitHook(null);
182     db.setRollbackHook(null);
183 }
184 
185 unittest  // Miscellaneous functions
186 {
187     auto db = Database(":memory:");
188     assert(db.attachedFilePath("main") is null);
189     assert(!db.isReadOnly);
190     db.close();
191 }
192 
193 unittest  // Execute an SQL statement
194 {
195     auto db = Database(":memory:");
196     db.run("");
197     db.run("-- This is a comment!");
198     db.run(";");
199     db.run("ANALYZE; VACUUM;");
200 }
201 
202 unittest  // Unexpected multiple statements
203 {
204     auto db = Database(":memory:");
205     db.execute("BEGIN; CREATE TABLE test (val INTEGER); ROLLBACK;");
206     assertThrown(db.execute("DROP TABLE test"));
207 
208     db.execute("CREATE TABLE test (val INTEGER); DROP TABLE test;");
209     assertNotThrown(db.execute("DROP TABLE test"));
210 
211     db.execute("SELECT 1; CREATE TABLE test (val INTEGER); DROP TABLE test;");
212     assertThrown(db.execute("DROP TABLE test"));
213 }
214 
215 unittest  // Multiple statements with callback
216 {
217     import std.array : appender;
218 
219     auto db = Database(":memory:");
220     auto test = appender!string;
221     db.run("SELECT 1, 2, 3; SELECT 'A', 'B', 'C';", (ResultRange r) {
222         foreach (col; r.front)
223             test.put(col.as!string);
224         return true;
225     });
226     assert(test.data == "123ABC");
227 }
228 
229 unittest  // Different arguments and result types with createFunction
230 {
231     auto db = Database(":memory:");
232 
233     T display(T)(T value) {
234         return value;
235     }
236 
237     db.createFunction("display_integer", &display!int);
238     db.createFunction("display_float", &display!double);
239     db.createFunction("display_text", &display!string);
240     db.createFunction("display_blob", &display!Blob);
241 
242     assert(db.execute("SELECT display_integer(42)").oneValue!int == 42);
243     assert(db.execute("SELECT display_float(3.14)").oneValue!double == 3.14);
244     assert(db.execute("SELECT display_text('ABC')").oneValue!string == "ABC");
245     assert(db.execute("SELECT display_blob(x'ABCD')").oneValue!Blob == cast(Blob) hexString!"ABCD");
246 
247     assert(db.execute("SELECT display_integer(NULL)").oneValue!int == 0);
248     assert(db.execute("SELECT display_float(NULL)").oneValue!double == 0.0);
249     assert(db.execute("SELECT display_text(NULL)").oneValue!string is null);
250     assert(db.execute("SELECT display_blob(NULL)").oneValue!(Blob) is null);
251 }
252 
253 unittest  // Different Nullable argument types with createFunction
254 {
255     auto db = Database(":memory:");
256 
257     auto display(T : Nullable!U, U...)(T value) {
258         if (value.isNull)
259             return T.init;
260         return value;
261     }
262 
263     db.createFunction("display_integer", &display!(Nullable!int));
264     db.createFunction("display_float", &display!(Nullable!double));
265     db.createFunction("display_text", &display!(Nullable!string));
266     db.createFunction("display_blob", &display!(Nullable!Blob));
267 
268     assert(db.execute("SELECT display_integer(42)").oneValue!(Nullable!int) == 42);
269     assert(db.execute("SELECT display_float(3.14)").oneValue!(Nullable!double) == 3.14);
270     assert(db.execute("SELECT display_text('ABC')").oneValue!(Nullable!string) == "ABC");
271     assert(db.execute("SELECT display_blob(x'ABCD')")
272             .oneValue!(Nullable!Blob) == cast(Blob) hexString!"ABCD");
273 
274     assert(db.execute("SELECT display_integer(NULL)").oneValue!(Nullable!int).isNull);
275     assert(db.execute("SELECT display_float(NULL)").oneValue!(Nullable!double).isNull);
276     assert(db.execute("SELECT display_text(NULL)").oneValue!(Nullable!string).isNull);
277     assert(db.execute("SELECT display_blob(NULL)").oneValue!(Nullable!Blob).isNull);
278 }
279 
280 unittest  // Callable struct with createFunction
281 {
282     import std.functional : toDelegate;
283 
284     struct Fun {
285         int factor;
286 
287         this(int factor) {
288             this.factor = factor;
289         }
290 
291         int opCall(int value) {
292             return value * factor;
293         }
294     }
295 
296     auto f = Fun(2);
297     auto db = Database(":memory:");
298     db.createFunction("my_fun", toDelegate(f));
299     assert(db.execute("SELECT my_fun(4)").oneValue!int == 8);
300 }
301 
302 unittest  // Callbacks
303 {
304     bool wasTraced = false;
305     bool wasProfiled = false;
306     bool hasProgressed = false;
307 
308     auto db = Database(":memory:");
309     db.setTraceCallback((string s) { wasTraced = true; });
310     db.execute("SELECT * FROM sqlite_master;");
311     assert(wasTraced);
312     db.setProfileCallback((string s, ulong t) { wasProfiled = true; });
313     db.execute("SELECT * FROM sqlite_master;");
314     assert(wasProfiled);
315 
316     db.setProgressHandler(1, { hasProgressed = true; return 0; });
317     db.execute("SELECT * FROM sqlite_master;");
318     assert(hasProgressed);
319 }
320 
321 unittest  // Statement.oneValue()
322 {
323     Statement statement;
324     {
325         auto db = Database(":memory:");
326         statement = db.prepare(" SELECT 42 ");
327     }
328     assert(statement.execute.oneValue!int == 42);
329 }
330 
331 unittest  // Statement.finalize()
332 {
333     auto db = Database(":memory:");
334     auto statement = db.prepare(" SELECT 42 ");
335     statement.finalize();
336 }
337 
338 unittest  // Simple parameters binding
339 {
340     auto db = Database(":memory:");
341     db.execute("CREATE TABLE test (val INTEGER)");
342 
343     auto statement = db.prepare("INSERT INTO test (val) VALUES (?)");
344     statement.bind(1, 36);
345     statement.clearBindings();
346     statement.bind(1, 42);
347     statement.execute();
348     statement.reset();
349     statement.bind(1, 42);
350     statement.execute();
351 
352     assert(db.lastInsertRowid == 2);
353     assert(db.changes == 1);
354     assert(db.totalChanges == 2);
355 
356     auto results = db.execute("SELECT * FROM test");
357     foreach (row; results)
358         assert(row.peek!int(0) == 42);
359 }
360 
361 unittest  // Multiple parameters binding
362 {
363     auto db = Database(":memory:");
364     db.execute("CREATE TABLE test (i INTEGER, f FLOAT, t TEXT)");
365     auto statement = db.prepare("INSERT INTO test (i, f, t) VALUES (:i, @f, $t)");
366 
367     assert(statement.parameterCount == 3);
368     assert(statement.parameterName(2) == "@f");
369     assert(statement.parameterIndex("$t") == 3);
370     assert(statement.parameterIndex(":foo") == 0);
371 
372     statement.bind("$t", "TEXT");
373     statement.bind(":i", 42);
374     statement.bind("@f", 3.14);
375     statement.execute();
376     statement.reset();
377     statement.bind(1, 42);
378     statement.bind(2, 3.14);
379     statement.bind(3, "TEXT");
380     statement.execute();
381 
382     auto results = db.execute("SELECT * FROM test");
383     foreach (row; results) {
384         assert(row.length == 3);
385         assert(row.peek!int("i") == 42);
386         assert(row.peek!double("f") == 3.14);
387         assert(row.peek!string("t") == "TEXT");
388     }
389 }
390 
391 unittest  // Multiple parameters binding: tuples
392 {
393     auto db = Database(":memory:");
394     db.execute("CREATE TABLE test (i INTEGER, f FLOAT, t TEXT)");
395     auto statement = db.prepare("INSERT INTO test (i, f, t) VALUES (?, ?, ?)");
396     statement.bindAll(42, 3.14, "TEXT");
397     statement.execute();
398 
399     auto results = db.execute("SELECT * FROM test");
400     foreach (row; results) {
401         assert(row.length == 3);
402         assert(row.peek!int(0) == 42);
403         assert(row.peek!double(1) == 3.14);
404         assert(row.peek!string(2) == "TEXT");
405     }
406 }
407 
408 unittest  // Binding/peeking integral values
409 {
410     auto db = Database(":memory:");
411     db.run("CREATE TABLE test (val INTEGER)");
412 
413     auto statement = db.prepare("INSERT INTO test (val) VALUES (?)");
414     statement.inject(cast(byte) 42);
415     statement.inject(42U);
416     statement.inject(42UL);
417     statement.inject('\x2A');
418 
419     auto results = db.execute("SELECT * FROM test");
420     foreach (row; results)
421         assert(row.peek!long(0) == 42);
422 }
423 
424 void foobar() // Binding/peeking floating point values
425 {
426     auto db = Database(":memory:");
427     db.run("CREATE TABLE test (val FLOAT)");
428 
429     auto statement = db.prepare("INSERT INTO test (val) VALUES (?)");
430     statement.inject(42.0F);
431     statement.inject(42.0);
432     statement.inject(42.0L);
433     statement.inject("42");
434 
435     auto results = db.execute("SELECT * FROM test");
436     foreach (row; results)
437         assert(row.peek!double(0) == 42.0);
438 }
439 
440 unittest  // Binding/peeking text values
441 {
442     auto db = Database(":memory:");
443     db.run("CREATE TABLE test (val TEXT);
444             INSERT INTO test (val) VALUES ('I am a text.')");
445 
446     auto results = db.execute("SELECT * FROM test");
447     assert(results.front.peek!(string, PeekMode.slice)(0) == "I am a text.");
448     assert(results.front.peek!(string, PeekMode.copy)(0) == "I am a text.");
449 
450     import std.exception : assertThrown;
451     import std.variant : VariantException;
452 
453     assertThrown!VariantException(results.front[0].as!Blob);
454 }
455 
456 unittest  // Binding/peeking blob values
457 {
458     auto db = Database(":memory:");
459     db.execute("CREATE TABLE test (val BLOB)");
460 
461     auto statement = db.prepare("INSERT INTO test (val) VALUES (?)");
462     auto array = cast(Blob)[1, 2, 3];
463     statement.inject(array);
464     ubyte[3] sarray = [1, 2, 3];
465     statement.inject(sarray);
466 
467     auto results = db.execute("SELECT * FROM test");
468     foreach (row; results) {
469         assert(row.peek!(Blob, PeekMode.slice)(0) == [1, 2, 3]);
470         assert(row[0].as!Blob == [1, 2, 3]);
471     }
472 }
473 
474 unittest  // Struct injecting
475 {
476     static struct Test {
477         int i;
478         double f;
479         string t;
480     }
481 
482     auto db = Database(":memory:");
483     db.execute("CREATE TABLE test (i INTEGER, f FLOAT, t TEXT)");
484     auto statement = db.prepare("INSERT INTO test (i, f, t) VALUES (?, ?, ?)");
485     auto test = Test(42, 3.14, "TEXT");
486     statement.inject(test);
487     statement.inject(Test(42, 3.14, "TEXT"));
488     auto itest = cast(immutable) Test(42, 3.14, "TEXT");
489     statement.inject(itest);
490 
491     auto results = db.execute("SELECT * FROM test");
492     assert(!results.empty);
493     foreach (row; results) {
494         assert(row.length == 3);
495         assert(row.peek!int(0) == 42);
496         assert(row.peek!double(1) == 3.14);
497         assert(row.peek!string(2) == "TEXT");
498     }
499 }
500 
501 unittest  // Iterable struct injecting
502 {
503     import std.range : iota;
504 
505     auto db = Database(":memory:");
506     db.execute("CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER)");
507     auto statement = db.prepare("INSERT INTO test (a, b, c) VALUES (?, ?, ?)");
508     statement.inject(iota(0, 3));
509 
510     auto results = db.execute("SELECT * FROM test");
511     assert(!results.empty);
512     foreach (row; results) {
513         assert(row.length == 3);
514         assert(row.peek!int(0) == 0);
515         assert(row.peek!int(1) == 1);
516         assert(row.peek!int(2) == 2);
517     }
518 }
519 
520 unittest  // Injecting nullable
521 {
522     import std.algorithm : map;
523     import std.array : array;
524 
525     auto db = Database(":memory:");
526     db.execute("CREATE TABLE test (i INTEGER, s TEXT)");
527     auto statement = db.prepare("INSERT INTO test (i, s) VALUES (?, ?)");
528     statement.inject(Nullable!int(1), "one");
529     statement = db.prepare("INSERT INTO test (i) VALUES (?)");
530     statement.inject(Nullable!int.init);
531 
532     auto results = db.execute("SELECT i FROM test ORDER BY rowid")
533         .map!(a => a.peek!(Nullable!int)(0)).array;
534 
535     assert(results.length == 2);
536     assert(results[0] == 1);
537     assert(results[1].isNull);
538 }
539 
540 unittest  // Injecting tuple
541 {
542     import std.typecons : tuple;
543 
544     auto db = Database(":memory:");
545     db.execute("CREATE TABLE test (i INTEGER, f FLOAT, t TEXT)");
546     auto statement = db.prepare("INSERT INTO test (i, f, t) VALUES (?, ?, ?)");
547     statement.inject(tuple(42, 3.14, "TEXT"));
548 
549     auto results = db.execute("SELECT * FROM test");
550     foreach (row; results) {
551         assert(row.length == 3);
552         assert(row.peek!int(0) == 42);
553         assert(row.peek!double(1) == 3.14);
554         assert(row.peek!string(2) == "TEXT");
555     }
556 }
557 
558 unittest  // Injecting dict
559 {
560     auto db = Database(":memory:");
561     db.execute("CREATE TABLE test (a TEXT, b TEXT, c TEXT)");
562     auto statement = db.prepare("INSERT INTO test (c, b, a) VALUES (:c, :b, :a)");
563     statement.inject([":a": "a", ":b": "b", ":c": "c"]);
564 
565     auto results = db.execute("SELECT * FROM test");
566     foreach (row; results) {
567         assert(row.length == 3);
568         assert(row.peek!string(0) == "a");
569         assert(row.peek!string(1) == "b");
570         assert(row.peek!string(2) == "c");
571     }
572 }
573 
574 unittest  // Binding Nullable
575 {
576     auto db = Database(":memory:");
577     db.execute("CREATE TABLE test (a, b, c, d, e);");
578 
579     auto statement = db.prepare("INSERT INTO test (a,b,c,d,e) VALUES (?,?,?,?,?)");
580     statement.bind(1, Nullable!int(123));
581     statement.bind(2, Nullable!int());
582     statement.bind(3, Nullable!(uint, 0)(42));
583     statement.bind(4, Nullable!(uint, 0)());
584     statement.bind(5, Nullable!bool(false));
585     statement.execute();
586 
587     auto results = db.execute("SELECT * FROM test");
588     foreach (row; results) {
589         assert(row.length == 5);
590         assert(row.peek!int(0) == 123);
591         assert(row.columnType(1) == SqliteType.NULL);
592         assert(row.peek!int(2) == 42);
593         assert(row.columnType(3) == SqliteType.NULL);
594         assert(!row.peek!bool(4));
595     }
596 }
597 
598 unittest  // Peeking Nullable
599 {
600     auto db = Database(":memory:");
601     auto results = db.execute("SELECT 1, NULL, 8.5, NULL");
602     foreach (row; results) {
603         assert(row.length == 4);
604         assert(row.peek!(Nullable!double)(2).get == 8.5);
605         assert(row.peek!(Nullable!double)(3).isNull);
606         assert(row.peek!(Nullable!(int, 0))(0).get == 1);
607         assert(row.peek!(Nullable!(int, 0))(1).isNull);
608     }
609 }
610 
611 unittest  // GC anchoring test
612 {
613     import core.memory : GC;
614 
615     auto db = Database(":memory:");
616     auto stmt = db.prepare("SELECT ?");
617 
618     auto str = ("I am test string").dup;
619     stmt.bind(1, str);
620     str = null;
621 
622     foreach (_; 0 .. 3) {
623         GC.collect();
624         GC.minimize();
625     }
626 
627     ResultRange results = stmt.execute();
628     foreach (row; results) {
629         assert(row.length == 1);
630         assert(row.peek!string(0) == "I am test string");
631     }
632 }
633 
634 version (unittest) // ResultRange is an input range of Row
635 {
636     import std.range.primitives : isInputRange, ElementType;
637 
638     static assert(isInputRange!ResultRange);
639     static assert(is(ElementType!ResultRange == Row));
640 }
641 
642 unittest  // Statement error
643 {
644     auto db = Database(":memory:");
645     db.execute("CREATE TABLE test (val INTEGER NOT NULL)");
646     auto stmt = db.prepare("INSERT INTO test (val) VALUES (?)");
647     stmt.bind(1, null);
648     import std.exception : assertThrown;
649 
650     assertThrown!SqliteException(stmt.execute());
651 }
652 
653 version (unittest) // Row is a random access range of ColumnData
654 {
655     import std.range.primitives : isRandomAccessRange, ElementType;
656 
657     static assert(isRandomAccessRange!Row);
658     static assert(is(ElementType!Row == ColumnData));
659 }
660 
661 unittest  // Row.init
662 {
663     import core.exception : AssertError;
664 
665     Row row;
666     assert(row.empty);
667     assertThrown!AssertError(row.front);
668     assertThrown!AssertError(row.back);
669     assertThrown!AssertError(row.popFront);
670     assertThrown!AssertError(row.popBack);
671     assertThrown!AssertError(row[""]);
672     assertThrown!AssertError(row.peek!long(0));
673 }
674 
675 unittest  // Peek
676 {
677     auto db = Database(":memory:");
678     db.run("CREATE TABLE test (value);
679             INSERT INTO test VALUES (NULL);
680             INSERT INTO test VALUES (42);
681             INSERT INTO test VALUES (3.14);
682             INSERT INTO test VALUES ('ABC');
683             INSERT INTO test VALUES (x'DEADBEEF');");
684 
685     import std.math : isNaN;
686 
687     auto results = db.execute("SELECT * FROM test");
688     auto row = results.front;
689     assert(row.peek!long(0) == 0);
690     assert(row.peek!double(0) == 0);
691     assert(row.peek!string(0) is null);
692     assert(row.peek!Blob(0) is null);
693     results.popFront();
694     row = results.front;
695     assert(row.peek!long(0) == 42);
696     assert(row.peek!double(0) == 42);
697     assert(row.peek!string(0) == "42");
698     assert(row.peek!Blob(0) == cast(Blob) "42");
699     results.popFront();
700     row = results.front;
701     assert(row.peek!long(0) == 3);
702     assert(row.peek!double(0) == 3.14);
703     assert(row.peek!string(0) == "3.14");
704     assert(row.peek!Blob(0) == cast(Blob) "3.14");
705     results.popFront();
706     row = results.front;
707     assert(row.peek!long(0) == 0);
708     assert(row.peek!double(0) == 0.0);
709     assert(row.peek!string(0) == "ABC");
710     assert(row.peek!Blob(0) == cast(Blob) "ABC");
711     results.popFront();
712     row = results.front;
713     assert(row.peek!long(0) == 0);
714     assert(row.peek!double(0) == 0.0);
715     assert(row.peek!string(0) == hexString!"DEADBEEF");
716     assert(row.peek!Blob(0) == cast(Blob) hexString!"DEADBEEF");
717 }
718 
719 unittest  // Peeking NULL values
720 {
721     auto db = Database(":memory:");
722     db.run("CREATE TABLE test (val TEXT);
723             INSERT INTO test (val) VALUES (NULL)");
724 
725     auto results = db.execute("SELECT * FROM test");
726     assert(results.front.peek!bool(0) == false);
727     assert(results.front.peek!long(0) == 0);
728     assert(results.front.peek!double(0) == 0);
729     assert(results.front.peek!string(0) is null);
730     assert(results.front.peek!Blob(0) is null);
731 }
732 
733 unittest  // Row life-time
734 {
735     auto db = Database(":memory:");
736     auto row = db.execute("SELECT 1 AS one").front;
737     assert(row[0].as!long == 1);
738     assert(row["one"].as!long == 1);
739 }
740 
741 unittest  // PeekMode
742 {
743     auto db = Database(":memory:");
744     db.run("CREATE TABLE test (value);
745             INSERT INTO test VALUES (x'01020304');
746             INSERT INTO test VALUES (x'0A0B0C0D');");
747 
748     auto results = db.execute("SELECT * FROM test");
749     auto row = results.front;
750     auto b1 = row.peek!(Blob, PeekMode.copy)(0);
751     auto b2 = row.peek!(Blob, PeekMode.slice)(0);
752     results.popFront();
753     row = results.front;
754     auto b3 = row.peek!(Blob, PeekMode.slice)(0);
755     auto b4 = row.peek!(Nullable!Blob, PeekMode.copy)(0);
756     assert(b1 == cast(Blob) hexString!"01020304");
757     // assert(b2 != cast(Blob) x"01020304"); // PASS if SQLite reuses internal buffer
758     // assert(b2 == cast(Blob) x"0A0B0C0D"); // PASS (idem)
759     assert(b3 == cast(Blob) hexString!"0A0B0C0D");
760     assert(!b4.isNull && b4 == cast(Blob) hexString!"0A0B0C0D");
761 }
762 
763 unittest  // Row random-access range interface
764 {
765     import std.array : front, popFront;
766 
767     auto db = Database(":memory:");
768     db.run("CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER, d INTEGER);
769         INSERT INTO test VALUES (1, 2, 3, 4);
770         INSERT INTO test VALUES (5, 6, 7, 8);");
771 
772     {
773         auto results = db.execute("SELECT * FROM test");
774         auto values = [1, 2, 3, 4, 5, 6, 7, 8];
775         foreach (row; results) {
776             while (!row.empty) {
777                 assert(row.front.as!int == values.front);
778                 row.popFront();
779                 values.popFront();
780             }
781         }
782     }
783 
784     {
785         auto results = db.execute("SELECT * FROM test");
786         auto values = [4, 3, 2, 1, 8, 7, 6, 5];
787         foreach (row; results) {
788             while (!row.empty) {
789                 assert(row.back.as!int == values.front);
790                 row.popBack();
791                 values.popFront();
792             }
793         }
794     }
795 
796     {
797         auto row = db.execute("SELECT * FROM test").front;
798         row.popFront();
799         auto copy = row.save();
800         row.popFront();
801         assert(row.front.as!int == 3);
802         assert(copy.front.as!int == 2);
803     }
804 }
805 
806 unittest  // ColumnData.init
807 {
808     import core.exception : AssertError;
809 
810     ColumnData data;
811     assertThrown!AssertError(data.type);
812     assertThrown!AssertError(data.as!string);
813 }
814 
815 unittest  // ColumnData-compatible types
816 {
817     import std.meta : AliasSeq;
818 
819     alias AllCases = AliasSeq!(bool, true, int, int.max, float, float.epsilon,
820             real, 42.0L, string, "おはよう！", const(ubyte)[], [0x00,
821                 0xFF], string, "", Nullable!byte, 42);
822 
823     void test(Cases...)() {
824         auto cd = ColumnData(Cases[1]);
825         assert(cd.as!(Cases[0]) == Cases[1]);
826         static if (Cases.length > 2)
827             test!(Cases[2 .. $])();
828     }
829 
830     test!AllCases();
831 }
832 
833 unittest  // ColumnData.toString
834 {
835     auto db = Database(":memory:");
836     auto rc = db.execute("SELECT 42, 3.14, 'foo_bar', x'00FF', NULL").cached;
837     assert("%(%s%)".format(rc) == "[42, 3.14, foo_bar, [0, 255], null]");
838 }
839 
840 unittest  // CachedResults copies
841 {
842     auto db = Database(":memory:");
843     db.run("CREATE TABLE test (msg TEXT);
844             INSERT INTO test (msg) VALUES ('ABC')");
845 
846     static getdata(Database db) {
847         return db.execute("SELECT * FROM test").cached;
848     }
849 
850     auto data = getdata(db);
851     assert(data.length == 1);
852     assert(data[0][0].as!string == "ABC");
853 }
854 
855 unittest  // UTF-8
856 {
857     auto db = Database(":memory:");
858     bool ran = false;
859     db.run("SELECT '\u2019\u2019';", (ResultRange r) {
860         assert(r.oneValue!string == "\u2019\u2019");
861         ran = true;
862         return true;
863     });
864     assert(ran);
865 }
866 
867 unittest  // loadExtension failure test
868 {
869     import std.algorithm : canFind;
870     import std.exception : collectExceptionMsg;
871 
872     auto db = Database(":memory:");
873     auto msg = collectExceptionMsg(db.loadExtension("foobar"));
874     //assert(msg.canFind("(not authorized)"));
875 }