tests.d source code

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