tests.d source code

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