1 /++
2 Managing SQLite3 database connections.
3 
4 Authors:
5     Nicolas Sicard (biozic) and other contributors at $(LINK https://github.com/biozic/d2sqlite3)
6 
7 Copyright:
8     Copyright 2011-18 Nicolas Sicard.
9 
10 License:
11     $(LINK2 http://www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
12 +/
13 module d2sqlite3.database;
14 
15 import d2sqlite3.statement;
16 import d2sqlite3.results;
17 import d2sqlite3.sqlite3;
18 import d2sqlite3.internal.memory;
19 import d2sqlite3.internal.util;
20 
21 import std.conv : to;
22 import std.exception : enforce;
23 import std.string : format, toStringz;
24 import std.typecons : Nullable;
25 
26 /// Set _UnlockNotify version if compiled with SqliteEnableUnlockNotify or SqliteFakeUnlockNotify
27 version (SqliteEnableUnlockNotify)
28     version = _UnlockNotify;
29 else version (SqliteFakeUnlockNotify)
30     version = _UnlockNotify;
31 
32 /// Type for the internal representation of blobs
33 alias Blob = immutable(ubyte)[];
34 
35 /// SQLite type codes
36 enum SqliteType {
37     INTEGER = SQLITE_INTEGER, ///
38     FLOAT = SQLITE_FLOAT, ///
39     TEXT = SQLITE3_TEXT, ///
40     BLOB = SQLITE_BLOB, ///
41     NULL = SQLITE_NULL ///
42 }
43 
44 /++
45 A caracteristic of user-defined functions or aggregates.
46 +/
47 enum Deterministic {
48     /++
49     The returned value is the same if the function is called with the same parameters.
50     +/
51     yes = 0x800,
52 
53     /++
54     The returned value can vary even if the function is called with the same parameters.
55     +/
56     no = 0
57 }
58 
59 /++
60 An database connection.
61 
62 This struct is a reference-counted wrapper around a `sqlite3*` pointer.
63 +/
64 struct Database {
65     import std.traits : isFunctionPointer, isDelegate;
66     import std.typecons : RefCounted, RefCountedAutoInitialize;
67 
68 private:
69     struct Payload {
70         sqlite3* handle;
71         void* updateHook;
72         void* commitHook;
73         void* rollbackHook;
74         void* progressHandler;
75         void* traceCallback;
76         void* profileCallback;
77         version (_UnlockNotify) IUnlockNotifyHandler unlockNotifyHandler;
78         debug string filename;
79 
80         this(sqlite3* handle) nothrow {
81             this.handle = handle;
82         }
83 
84         ~this() nothrow {
85             debug ensureNotInGC!Database(filename);
86             ptrFree(updateHook);
87             ptrFree(commitHook);
88             ptrFree(rollbackHook);
89             ptrFree(progressHandler);
90             ptrFree(traceCallback);
91             ptrFree(profileCallback);
92 
93             if (!handle)
94                 return;
95             sqlite3_progress_handler(handle, 0, null, null);
96             sqlite3_close(handle);
97         }
98     }
99 
100     RefCounted!(Payload, RefCountedAutoInitialize.no) p;
101 
102     void check(int result) {
103         enforce(result == SQLITE_OK, new SqliteException(errmsg(p.handle), result));
104     }
105 
106 public:
107     /++
108     Opens a database connection.
109 
110     Params:
111         path = The path to the database file. In recent versions of SQLite, the path can be
112         an URI with options.
113 
114         flags = Options flags.
115 
116     See_Also: $(LINK http://www.sqlite.org/c3ref/open.html) to know how to use the flags
117     parameter or to use path as a file URI if the current configuration allows it.
118     +/
119     this(string path, int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE) {
120         sqlite3* hdl;
121         auto result = sqlite3_open_v2(path.toStringz, &hdl, flags, null);
122         enforce(result == SQLITE_OK, new SqliteException(hdl ? errmsg(hdl)
123                 : "Error opening the database", result));
124         p = Payload(hdl);
125         debug p.filename = path;
126     }
127 
128     /++
129     Explicitly closes the database connection.
130 
131     After a successful call to `close()`, using the database connection or one of its prepared
132     statement is an error. The `Database` object is destroyed and cannot be used any more.
133     +/
134     void close() {
135         auto result = sqlite3_close(p.handle);
136         enforce(result == SQLITE_OK, new SqliteException(errmsg(handle), result));
137         destroy(p);
138     }
139 
140     /++
141     Gets the SQLite internal _handle of the database connection.
142     +/
143     sqlite3* handle() @property nothrow {
144         return p.handle;
145     }
146 
147     /++
148     Gets the path associated with an attached database.
149 
150     Params:
151         database = The name of an attached database.
152 
153     Returns: The absolute path of the attached database.
154         If there is no attached database, or if database is a temporary or
155         in-memory database, then null is returned.
156     +/
157     string attachedFilePath(string database = "main") {
158         assert(p.handle);
159         return sqlite3_db_filename(p.handle, database.toStringz).to!string;
160     }
161 
162     /++
163     Gets the read-only status of an attached database.
164 
165     Params:
166         database = The name of an attached database.
167     +/
168     bool isReadOnly(string database = "main") {
169         assert(p.handle);
170         immutable ret = sqlite3_db_readonly(p.handle, database.toStringz);
171         enforce(ret >= 0, new SqliteException("Database not found: %s".format(database)));
172         return ret == 1;
173     }
174 
175     /++
176     Gets metadata for a specific table column of an attached database.
177 
178     Params:
179         table = The name of the table.
180 
181         column = The name of the column.
182 
183         database = The name of a database attached. If null, then all attached databases
184         are searched for the table using the same algorithm used by the database engine
185         to resolve unqualified table references.
186     +/
187     TableColumnMetadata tableColumnMetadata(string table, string column, string database = "main") {
188         TableColumnMetadata data;
189         char* pzDataType, pzCollSeq;
190         int notNull, primaryKey, autoIncrement;
191         assert(p.handle);
192         check(sqlite3_table_column_metadata(p.handle, database.toStringz, table.toStringz,
193                 column.toStringz, &pzDataType, &pzCollSeq, &notNull,
194                 &primaryKey, &autoIncrement));
195         data.declaredTypeName = pzDataType.to!string;
196         data.collationSequenceName = pzCollSeq.to!string;
197         data.isNotNull = cast(bool) notNull;
198         data.isPrimaryKey = cast(bool) primaryKey;
199         data.isAutoIncrement = cast(bool) autoIncrement;
200         return data;
201     }
202 
203     /++
204     Executes a single SQL statement and returns the results directly.
205 
206     It's the equivalent of `prepare(sql).execute()`.
207     Or when used with args the equivalent of:
208     ---
209     auto stm = prepare(sql);
210     stm.bindAll(args);
211     stm.execute();
212     ---
213 
214     The results become undefined when the Database goes out of scope and is destroyed.
215 
216     Params:
217         sql = The code of the SQL statement.
218         args = Optional arguments to bind to the SQL statement.
219     +/
220     ResultRange execute(Args...)(string sql, Args args) {
221         auto stm = prepare(sql);
222         static if (Args.length)
223             stm.bindAll(args);
224         return stm.execute();
225     }
226     ///
227     unittest {
228         auto db = Database(":memory:");
229         db.execute("CREATE TABLE test (val INTEGER)");
230         db.execute("INSERT INTO test (val) VALUES (:v)", 1);
231         assert(db.execute("SELECT val FROM test WHERE val=:v", 1).oneValue!int == 1);
232     }
233 
234     /++
235     Runs an SQL script that can contain multiple statements.
236 
237     Params:
238         script = The code of the SQL script.
239 
240         dg = A delegate to call for each statement to handle the results. The passed
241         ResultRange will be empty if a statement doesn't return rows. If the delegate
242         return false, the execution is aborted.
243     +/
244     void run(string script, bool delegate(ResultRange) dg = null) {
245         foreach (sql; script.byStatement) {
246             auto stmt = prepare(sql);
247             auto results = stmt.execute();
248             if (dg && !dg(results))
249                 return;
250         }
251     }
252     ///
253     unittest {
254         auto db = Database(":memory:");
255         db.run(`CREATE TABLE test1 (val INTEGER);
256                 CREATE TABLE test2 (val FLOAT);
257                 DROP TABLE test1;
258                 DROP TABLE test2;`);
259     }
260 
261     /++
262     Prepares (compiles) a single SQL statement and returngs it, so that it can be bound to
263     values before execution.
264 
265     The statement becomes invalid if the Database goes out of scope and is destroyed.
266     +/
267     Statement prepare(string sql) {
268         return Statement(this, sql);
269     }
270 
271     /// Convenience functions equivalent to an SQL statement.
272     void begin() {
273         execute("BEGIN");
274     }
275     /// Ditto
276     void commit() {
277         execute("COMMIT");
278     }
279     /// Ditto
280     void rollback() {
281         execute("ROLLBACK");
282     }
283 
284     /++
285     Returns the rowid of the last INSERT statement.
286     +/
287     long lastInsertRowid() {
288         assert(p.handle);
289         return sqlite3_last_insert_rowid(p.handle);
290     }
291 
292     /++
293     Gets the number of database rows that were changed, inserted or deleted by the most
294     recently executed SQL statement.
295     +/
296     int changes() @property nothrow {
297         assert(p.handle);
298         return sqlite3_changes(p.handle);
299     }
300 
301     /++
302     Gets the number of database rows that were changed, inserted or deleted since the
303     database was opened.
304     +/
305     int totalChanges() @property nothrow {
306         assert(p.handle);
307         return sqlite3_total_changes(p.handle);
308     }
309 
310     /++
311     Gets the SQLite error code of the last operation.
312     +/
313     int errorCode() @property nothrow {
314         return p.handle ? sqlite3_errcode(p.handle) : 0;
315     }
316 
317     /++
318     Interrupts any pending database operations.
319 
320     It's safe to call this function from anouther thread.
321 
322     See_also: $(LINK http://www.sqlite.org/c3ref/interrupt.html).
323     +/
324     void interrupt() {
325         assert(p.handle);
326         sqlite3_interrupt(p.handle);
327     }
328 
329     /++
330     Sets a connection configuration option.
331 
332     See_Also: $(LINK http://www.sqlite.org/c3ref/db_config.html).
333     +/
334     void config(Args...)(int code, Args args) {
335         assert(p.handle);
336         auto result = sqlite3_db_config(p.handle, code, args);
337         enforce(result == SQLITE_OK,
338                 new SqliteException("Database configuration: error %s".format(result)));
339     }
340 
341     /++
342     Enables or disables loading extensions.
343     +/
344     void enableLoadExtensions(bool enable = true) {
345         assert(p.handle);
346         enforce(sqlite3_enable_load_extension(p.handle, enable) == SQLITE_OK,
347                 new SqliteException("Could not enable loading extensions."));
348     }
349 
350     /++
351     Loads an extension.
352 
353     Params:
354         path = The path of the extension file.
355 
356         entryPoint = The name of the entry point function. If null is passed, SQLite
357         uses the name of the extension file as the entry point.
358     +/
359     void loadExtension(string path, string entryPoint = null) {
360         assert(p.handle);
361         immutable ret = sqlite3_load_extension(p.handle, path.toStringz,
362                 entryPoint.toStringz, null);
363         enforce(ret == SQLITE_OK,
364                 new SqliteException("Could not load extension: %s:%s".format(entryPoint, path)));
365     }
366 
367     /++
368     Creates and registers a new function in the database.
369 
370     If a function with the same name and the same arguments already exists, it is replaced
371     by the new one.
372 
373     The memory associated with the function will be released when the database connection
374     is closed.
375 
376     Params:
377         name = The name that the function will have in the database.
378 
379         fun = a delegate or function that implements the function. $(D_PARAM fun)
380         must satisfy the following criteria:
381             $(UL
382                 $(LI It must not be variadic.)
383                 $(LI Its arguments must all have a type that is compatible with SQLite types:
384                 it must be a boolean or numeric type, a string, an array, `null`,
385                 or a `Nullable!T` where T is any of the previous types.)
386                 $(LI Its return value must also be of a compatible type.)
387             )
388             or
389             $(UL
390                 $(LI It must be a normal or type-safe variadic function where the arguments
391                 are of type `ColumnData`. In other terms, the signature of the function must be:
392                 `function(ColumnData[] args)` or `function(ColumnData[] args...)`)
393                 $(LI Its return value must be a boolean or numeric type, a string, an array, `null`,
394                 or a `Nullable!T` where T is any of the previous types.)
395             )
396         Pass a `null` function pointer to delete the function from the database connection.
397 
398         det = Tells SQLite whether the result of the function is deterministic, i.e. if the
399         result is the same when called with the same parameters. Recent versions of SQLite
400         perform optimizations based on this. Set to `Deterministic.no` otherwise.
401 
402     See_Also: $(LINK http://www.sqlite.org/c3ref/create_function.html).
403     +/
404     void createFunction(T)(string name, T fun, Deterministic det = Deterministic.yes)
405             if (isFunctionPointer!T || isDelegate!T) {
406         import std.meta : AliasSeq, staticMap, EraseAll;
407         import std.traits : variadicFunctionStyle, Variadic, ParameterTypeTuple,
408             ParameterDefaultValueTuple, ReturnType, Unqual;
409 
410         static assert(variadicFunctionStyle!(fun) == Variadic.no
411                 || is(ParameterTypeTuple!fun == AliasSeq!(ColumnData[])),
412                 "only type-safe variadic functions with ColumnData arguments are supported");
413 
414         static if (is(ParameterTypeTuple!fun == AliasSeq!(ColumnData[]))) {
415             extern (C) static nothrow void x_func(sqlite3_context* context,
416                     int argc, sqlite3_value** argv) {
417                 string name;
418                 try {
419                     import std.array : appender;
420 
421                     auto args = appender!(ColumnData[]);
422 
423                     foreach (i; 0 .. argc) {
424                         auto value = argv[i];
425                         immutable type = sqlite3_value_type(value);
426 
427                         final switch (type) {
428                         case SqliteType.INTEGER:
429                             args.put(ColumnData(getValue!long(value)));
430                             break;
431 
432                         case SqliteType.FLOAT:
433                             args.put(ColumnData(getValue!double(value)));
434                             break;
435 
436                         case SqliteType.TEXT:
437                             args.put(ColumnData(getValue!string(value)));
438                             break;
439 
440                         case SqliteType.BLOB:
441                             args.put(ColumnData(getValue!Blob(value)));
442                             break;
443 
444                         case SqliteType.NULL:
445                             args.put(ColumnData(null));
446                             break;
447                         }
448                     }
449 
450                     auto ptr = sqlite3_user_data(context);
451 
452                     auto wrappedDelegate = delegateUnwrap!T(ptr);
453                     auto dlg = wrappedDelegate.dlg;
454                     name = wrappedDelegate.name;
455                     setResult(context, dlg(args.data));
456                 } catch (Exception e) {
457                     sqlite3_result_error(context,
458                             "error in function %s(): %s".nothrowFormat(name, e.msg).toStringz, -1);
459                 }
460             }
461         } else {
462             static assert(!is(ReturnType!fun == void), "function must not return void");
463 
464             alias PT = staticMap!(Unqual, ParameterTypeTuple!fun);
465             alias PD = ParameterDefaultValueTuple!fun;
466 
467             extern (C) static nothrow void x_func(sqlite3_context* context,
468                     int argc, sqlite3_value** argv) {
469                 string name;
470                 try {
471                     // Get the deledate and its name
472                     auto ptr = sqlite3_user_data(context);
473                     auto wrappedDelegate = delegateUnwrap!T(ptr);
474                     auto dlg = wrappedDelegate.dlg;
475                     name = wrappedDelegate.name;
476 
477                     enum maxArgc = PT.length;
478                     enum minArgc = PT.length - EraseAll!(void, PD).length;
479 
480                     if (argc > maxArgc) {
481                         auto txt = ("too many arguments in function %s(), expecting at most %s").format(name,
482                                 maxArgc);
483                         sqlite3_result_error(context, txt.toStringz, -1);
484                     } else if (argc < minArgc) {
485                         auto txt = ("too few arguments in function %s(), expecting at least %s").format(name,
486                                 minArgc);
487                         sqlite3_result_error(context, txt.toStringz, -1);
488                     } else {
489                         PT args;
490                         foreach (i, type; PT) {
491                             if (i < argc)
492                                 args[i] = getValue!type(argv[i]);
493                             else static if (is(typeof(PD[i])))
494                                 args[i] = PD[i];
495                         }
496                         setResult(context, dlg(args));
497                     }
498                 } catch (Exception e) {
499                     sqlite3_result_error(context,
500                             "error in function %s(): %s".nothrowFormat(name, e.msg).toStringz, -1);
501                 }
502             }
503         }
504 
505         assert(name.length, "function has an empty name");
506 
507         if (!fun)
508             createFunction(name, null);
509 
510         assert(p.handle);
511         check(sqlite3_create_function_v2(p.handle, name.toStringz, -1,
512                 SQLITE_UTF8 | det, delegateWrap(fun, name), &x_func, null, null, &ptrFree));
513     }
514     ///
515     unittest {
516         string star(int count, string starSymbol = "*") {
517             import std.range : repeat;
518             import std.array : join;
519 
520             return starSymbol.repeat(count).join;
521         }
522 
523         auto db = Database(":memory:");
524         db.createFunction("star", &star);
525         assert(db.execute("SELECT star(5)").oneValue!string == "*****");
526         assert(db.execute("SELECT star(3, '♥')").oneValue!string == "♥♥♥");
527     }
528     ///
529     unittest {
530         // The implementation of the new function
531         string myList(ColumnData[] args) {
532             import std.array : appender;
533             import std.string : format, join;
534 
535             auto app = appender!(string[]);
536             foreach (arg; args) {
537                 if (arg.type == SqliteType.TEXT)
538                     app.put(`"%s"`.format(arg));
539                 else
540                     app.put("%s".format(arg));
541             }
542             return app.data.join(", ");
543         }
544 
545         auto db = Database(":memory:");
546         db.createFunction("my_list", &myList);
547         auto list = db.execute("SELECT my_list(42, 3.14, 'text', NULL)").oneValue!string;
548         assert(list == `42, 3.14, "text", null`);
549     }
550 
551     /// Ditto
552     void createFunction(T)(string name, T fun = null) if (is(T == typeof(null))) {
553         assert(name.length, "function has an empty name");
554         assert(p.handle);
555         check(sqlite3_create_function_v2(p.handle, name.toStringz, -1,
556                 SQLITE_UTF8, null, fun, null, null, null));
557     }
558 
559     /++
560     Creates and registers a new aggregate function in the database.
561 
562     Params:
563         name = The name that the aggregate function will have in the database.
564 
565         agg = The struct of type T implementing the aggregate. T must implement
566         at least these two methods: `accumulate()` and `result()`.
567         Each parameter and the returned type of `accumulate()` and `result()` must be
568         a boolean or numeric type, a string, an array, `null`, or a `Nullable!T`
569         where T is any of the previous types. These methods cannot be variadic.
570 
571         det = Tells SQLite whether the result of the function is deterministic, i.e. if the
572         result is the same when called with the same parameters. Recent versions of SQLite
573         perform optimizations based on this. Set to `Deterministic.no` otherwise.
574 
575     See_Also: $(LINK http://www.sqlite.org/c3ref/create_function.html).
576     +/
577     void createAggregate(T)(string name, T agg, Deterministic det = Deterministic.yes) {
578         import std.meta : staticMap;
579         import std.traits : isAggregateType, ReturnType, variadicFunctionStyle,
580             Variadic, Unqual, ParameterTypeTuple;
581         import core.stdc.stdlib : malloc;
582 
583         static assert(isAggregateType!T, T.stringof ~ " should be an aggregate type");
584         static assert(is(typeof(T.accumulate) == function),
585                 T.stringof ~ " should have a method named accumulate");
586         static assert(is(typeof(T.result) == function),
587                 T.stringof ~ " should have a method named result");
588         static assert(is(typeof({
589                     alias RT = ReturnType!(T.result);
590                     setResult!RT(null, RT.init);
591                 })), T.stringof ~ ".result should return an SQLite-compatible type");
592         static assert(variadicFunctionStyle!(T.accumulate) == Variadic.no,
593                 "variadic functions are not supported");
594         static assert(variadicFunctionStyle!(T.result) == Variadic.no,
595                 "variadic functions are not supported");
596 
597         alias PT = staticMap!(Unqual, ParameterTypeTuple!(T.accumulate));
598         alias RT = ReturnType!(T.result);
599 
600         static struct Context {
601             T aggregate;
602             string functionName;
603         }
604 
605         extern (C) static nothrow void x_step(sqlite3_context* context, int /* argc */ ,
606                 sqlite3_value** argv) {
607             auto ctx = cast(Context*) sqlite3_user_data(context);
608             if (!ctx) {
609                 sqlite3_result_error_nomem(context);
610                 return;
611             }
612 
613             PT args;
614             try {
615                 foreach (i, type; PT)
616                     args[i] = getValue!type(argv[i]);
617 
618                 ctx.aggregate.accumulate(args);
619             } catch (Exception e) {
620                 sqlite3_result_error(context,
621                         "error in aggregate function %s(): %s".nothrowFormat(ctx.functionName,
622                             e.msg).toStringz, -1);
623             }
624         }
625 
626         extern (C) static nothrow void x_final(sqlite3_context* context) {
627             auto ctx = cast(Context*) sqlite3_user_data(context);
628             if (!ctx) {
629                 sqlite3_result_error_nomem(context);
630                 return;
631             }
632 
633             try {
634                 setResult(context, ctx.aggregate.result());
635             } catch (Exception e) {
636                 sqlite3_result_error(context,
637                         "error in aggregate function %s(): %s".nothrowFormat(ctx.functionName,
638                             e.msg).toStringz, -1);
639             }
640         }
641 
642         static if (is(T == class) || is(T == Interface))
643             assert(agg, "Attempt to create an aggregate function from a null reference");
644 
645         auto ctx = cast(Context*) malloc(Context.sizeof);
646         ctx.aggregate = agg;
647         ctx.functionName = name;
648 
649         assert(p.handle);
650         check(sqlite3_create_function_v2(p.handle, name.toStringz, PT.length,
651                 SQLITE_UTF8 | det, cast(void*) ctx, null, &x_step, &x_final, &ptrFree));
652     }
653     ///
654     unittest  // Aggregate creation
655     {
656         import std.array : Appender, join;
657 
658         // The implementation of the aggregate function
659         struct Joiner {
660             private {
661                 Appender!(string[]) stringList;
662                 string separator;
663             }
664 
665             this(string separator) {
666                 this.separator = separator;
667             }
668 
669             void accumulate(string word) {
670                 stringList.put(word);
671             }
672 
673             string result() {
674                 return stringList.data.join(separator);
675             }
676         }
677 
678         auto db = Database(":memory:");
679         db.run("CREATE TABLE test (word TEXT);
680                 INSERT INTO test VALUES ('My');
681                 INSERT INTO test VALUES ('cat');
682                 INSERT INTO test VALUES ('is');
683                 INSERT INTO test VALUES ('black');");
684 
685         db.createAggregate("dash_join", Joiner("-"));
686         auto text = db.execute("SELECT dash_join(word) FROM test").oneValue!string;
687         assert(text == "My-cat-is-black");
688     }
689 
690     /++
691     Creates and registers a collation function in the database.
692 
693     Params:
694         name = The name that the function will have in the database.
695 
696         fun = a delegate or function that implements the collation. The function $(D_PARAM fun)
697         must be `nothrow`` and satisfy these criteria:
698             $(UL
699                 $(LI Takes two string arguments (s1 and s2). These two strings are slices of C-style strings
700                   that SQLite manages internally, so there is no guarantee that they are still valid
701                   when the function returns.)
702                 $(LI Returns an integer (ret).)
703                 $(LI If s1 is less than s2, ret < 0.)
704                 $(LI If s1 is equal to s2, ret == 0.)
705                 $(LI If s1 is greater than s2, ret > 0.)
706                 $(LI If s1 is equal to s2, then s2 is equal to s1.)
707                 $(LI If s1 is equal to s2 and s2 is equal to s3, then s1 is equal to s3.)
708                 $(LI If s1 is less than s2, then s2 is greater than s1.)
709                 $(LI If s1 is less than s2 and s2 is less than s3, then s1 is less than s3.)
710             )
711 
712     See_Also: $(LINK http://www.sqlite.org/lang_aggfunc.html)
713     +/
714     void createCollation(T)(string name, T fun)
715             if (isFunctionPointer!T || isDelegate!T) {
716         import std.traits : isImplicitlyConvertible, functionAttributes,
717             FunctionAttribute, ParameterTypeTuple, isSomeString, ReturnType;
718 
719         static assert(isImplicitlyConvertible!(typeof(fun("a", "b")), int),
720                 "the collation function has a wrong signature");
721 
722         static assert(functionAttributes!(T) & FunctionAttribute.nothrow_,
723                 "only nothrow functions are allowed as collations");
724 
725         alias PT = ParameterTypeTuple!fun;
726         static assert(isSomeString!(PT[0]),
727                 "the first argument of function " ~ name ~ " should be a string");
728         static assert(isSomeString!(PT[1]),
729                 "the second argument of function " ~ name ~ " should be a string");
730         static assert(isImplicitlyConvertible!(ReturnType!fun, int),
731                 "function " ~ name ~ " should return a value convertible to an int");
732 
733         extern (C) static nothrow int x_compare(void* ptr, int n1,
734                 const(void)* str1, int n2, const(void)* str2) {
735             static string slice(const(void)* str, int n) nothrow {
736                 // The string data is owned by SQLite, so it should be safe
737                 // to take a slice of it.
738                 return str ? (cast(immutable)(cast(const(char)*) str)[0 .. n]) : null;
739             }
740 
741             return delegateUnwrap!T(ptr).dlg(slice(str1, n1), slice(str2, n2));
742         }
743 
744         assert(p.handle);
745         auto dgw = delegateWrap(fun, name);
746         auto result = sqlite3_create_collation_v2(p.handle, name.toStringz,
747                 SQLITE_UTF8, delegateWrap(fun, name), &x_compare, &ptrFree);
748         if (result != SQLITE_OK) {
749             ptrFree(dgw);
750             throw new SqliteException(errmsg(p.handle), result);
751         }
752     }
753     ///
754     unittest  // Collation creation
755     {
756         // The implementation of the collation
757         int my_collation(string s1, string s2) nothrow {
758             import std.uni : icmp;
759             import std.exception : assumeWontThrow;
760 
761             return assumeWontThrow(icmp(s1, s2));
762         }
763 
764         auto db = Database(":memory:");
765         db.createCollation("my_coll", &my_collation);
766         db.run("CREATE TABLE test (word TEXT);
767                 INSERT INTO test (word) VALUES ('straße');
768                 INSERT INTO test (word) VALUES ('strasses');");
769 
770         auto word = db.execute("SELECT word FROM test ORDER BY word COLLATE my_coll")
771             .oneValue!string;
772         assert(word == "straße");
773     }
774 
775     /++
776     Registers a delegate of type `UpdateHookDelegate` as the database's update hook.
777 
778     Any previously set hook is released. Pass `null` to disable the callback.
779 
780     See_Also: $(LINK http://www.sqlite.org/c3ref/commit_hook.html).
781     +/
782     void setUpdateHook(UpdateHookDelegate updateHook) {
783         extern (C) static nothrow void callback(void* ptr, int type,
784                 const(char)* dbName, const(char)* tableName, long rowid) {
785             WrappedDelegate!UpdateHookDelegate* dg;
786             dg = delegateUnwrap!UpdateHookDelegate(ptr);
787             dg.dlg(type, dbName.to!string, tableName.to!string, rowid);
788         }
789 
790         ptrFree(p.updateHook);
791         p.updateHook = delegateWrap(updateHook);
792         assert(p.handle);
793         sqlite3_update_hook(p.handle, &callback, p.updateHook);
794     }
795 
796     /++
797     Registers a delegate of type `CommitHookDelegate` as the database's commit hook.
798     Any previously set hook is released.
799 
800     Params:
801         commitHook = A delegate that should return a non-zero value
802         if the operation must be rolled back, or 0 if it can commit.
803         Pass `null` to disable the callback.
804 
805     See_Also: $(LINK http://www.sqlite.org/c3ref/commit_hook.html).
806     +/
807     void setCommitHook(CommitHookDelegate commitHook) {
808         extern (C) static nothrow int callback(void* ptr) {
809             auto dlg = delegateUnwrap!CommitHookDelegate(ptr).dlg;
810             return dlg();
811         }
812 
813         ptrFree(p.commitHook);
814         p.commitHook = delegateWrap(commitHook);
815         assert(p.handle);
816         sqlite3_commit_hook(p.handle, &callback, p.commitHook);
817     }
818 
819     /++
820     Registers a delegate of type `RoolbackHookDelegate` as the database's rollback hook.
821 
822     Any previously set hook is released.
823     Pass `null` to disable the callback.
824 
825     See_Also: $(LINK http://www.sqlite.org/c3ref/commit_hook.html).
826     +/
827     void setRollbackHook(RoolbackHookDelegate rollbackHook) {
828         extern (C) static nothrow void callback(void* ptr) {
829             auto dlg = delegateUnwrap!RoolbackHookDelegate(ptr).dlg;
830             dlg();
831         }
832 
833         ptrFree(p.rollbackHook);
834         p.rollbackHook = delegateWrap(rollbackHook);
835         assert(p.handle);
836         sqlite3_rollback_hook(p.handle, &callback, p.rollbackHook);
837     }
838 
839     /++
840     Registers a delegate of type `ProgressHandlerDelegate` as the progress handler.
841 
842     Any previously set handler is released.
843     Pass `null` to disable the callback.
844 
845     Params:
846         pace = The approximate number of virtual machine instructions that are
847         evaluated between successive invocations of the handler.
848 
849         progressHandler = A delegate that should return 0 if the operation can continue
850         or another value if it must be aborted.
851 
852     See_Also: $(LINK http://www.sqlite.org/c3ref/progress_handler.html).
853     +/
854     void setProgressHandler(int pace, ProgressHandlerDelegate progressHandler) {
855         extern (C) static nothrow int callback(void* ptr) {
856             auto dlg = delegateUnwrap!ProgressHandlerDelegate(ptr).dlg;
857             return dlg();
858         }
859 
860         ptrFree(p.progressHandler);
861         p.progressHandler = delegateWrap(progressHandler);
862         assert(p.handle);
863         sqlite3_progress_handler(p.handle, pace, &callback, p.progressHandler);
864     }
865 
866     /++
867     Registers a delegate of type `TraceCallbackDelegate` as the trace callback.
868 
869     Any previously set trace callback is released.
870     Pass `null` to disable the callback.
871 
872     The string parameter that is passed to the callback is the SQL text of the statement being
873     executed.
874 
875     See_Also: $(LINK http://www.sqlite.org/c3ref/profile.html).
876     +/
877     void setTraceCallback(TraceCallbackDelegate traceCallback) {
878         extern (C) static nothrow void callback(void* ptr, const(char)* str) {
879             auto dlg = delegateUnwrap!TraceCallbackDelegate(ptr).dlg;
880             dlg(str.to!string);
881         }
882 
883         ptrFree(p.traceCallback);
884         p.traceCallback = delegateWrap(traceCallback);
885         assert(p.handle);
886         sqlite3_trace(p.handle, &callback, p.traceCallback);
887     }
888 
889     /++
890     Registers a delegate of type `ProfileCallbackDelegate` as the profile callback.
891 
892     Any previously set profile callback is released.
893     Pass `null` to disable the callback.
894 
895     The string parameter that is passed to the callback is the SQL text of the statement being
896     executed. The time unit is defined in SQLite's documentation as nanoseconds (subject to change,
897     as the functionality is experimental).
898 
899     See_Also: $(LINK http://www.sqlite.org/c3ref/profile.html).
900     +/
901     void setProfileCallback(ProfileCallbackDelegate profileCallback) {
902         extern (C) static nothrow void callback(void* ptr, const(char)* str, sqlite3_uint64 time) {
903             auto dlg = delegateUnwrap!ProfileCallbackDelegate(ptr).dlg;
904             dlg(str.to!string, time);
905         }
906 
907         ptrFree(p.profileCallback);
908         p.profileCallback = delegateWrap(profileCallback);
909         assert(p.handle);
910         sqlite3_profile(p.handle, &callback, p.profileCallback);
911     }
912 
913     version (_UnlockNotify) {
914         /++
915         Registers a `IUnlockNotifyHandler` used to handle database locks.
916 
917         When running in shared-cache mode, a database operation may fail with an SQLITE_LOCKED error if
918         the required locks on the shared-cache or individual tables within the shared-cache cannot be obtained.
919         See SQLite Shared-Cache Mode for a description of shared-cache locking.
920         This API may be used to register a callback that SQLite will invoke when the connection currently
921         holding the required lock relinquishes it.
922         This API can be used only if the SQLite library was compiled with the `SQLITE_ENABLE_UNLOCK_NOTIFY`
923         C-preprocessor symbol defined.
924 
925         See_Also: $(LINK http://sqlite.org/c3ref/unlock_notify.html).
926 
927         Parameters:
928             unlockNotifyHandler - custom handler used to control the unlocking mechanism
929         +/
930         void setUnlockNotifyHandler(IUnlockNotifyHandler unlockNotifyHandler) {
931             p.unlockNotifyHandler = unlockNotifyHandler;
932         }
933 
934         /// Setup and waits for unlock notify using the provided `IUnlockNotifyHandler`
935         package(d2sqlite3) auto waitForUnlockNotify() {
936             if (p.unlockNotifyHandler is null)
937                 return SQLITE_LOCKED;
938 
939             version (SqliteEnableUnlockNotify) {
940                 extern (C) static nothrow void callback(void** ntfPtr, int nPtr) {
941                     for (int i = 0; i < nPtr; i++) {
942                         auto handler = cast(IUnlockNotifyHandler*) ntfPtr[i];
943                         handler.emit(SQLITE_OK);
944                     }
945                 }
946 
947                 int rc = sqlite3_unlock_notify(p.handle, &callback, &p.unlockNotifyHandler);
948                 assert(rc == SQLITE_LOCKED || rc == SQLITE_OK);
949 
950                 /+ The call to sqlite3_unlock_notify() always returns either SQLITE_LOCKED or SQLITE_OK.
951 
952                 If SQLITE_LOCKED was returned, then the system is deadlocked. In this case this function
953                 needs to return SQLITE_LOCKED to the caller so that the current transaction can be rolled
954                 back. Otherwise, block until the unlock-notify callback is invoked, then return SQLITE_OK.
955                 +/
956                 if (rc == SQLITE_OK) {
957                     p.unlockNotifyHandler.wait();
958                     scope (exit)
959                         p.unlockNotifyHandler.reset();
960                     return p.unlockNotifyHandler.result;
961                 }
962                 return rc;
963             } else {
964                 p.unlockNotifyHandler.waitOne();
965                 auto res = p.unlockNotifyHandler.result;
966                 if (res != SQLITE_OK)
967                     p.unlockNotifyHandler.reset();
968                 return res;
969             }
970         }
971     }
972 }
973 
974 /// Delegate types
975 alias UpdateHookDelegate = void delegate(int type, string dbName, string tableName, long rowid) nothrow;
976 /// ditto
977 alias CommitHookDelegate = int delegate() nothrow;
978 /// ditto
979 alias RoolbackHookDelegate = void delegate() nothrow;
980 /// ditto
981 alias ProgressHandlerDelegate = int delegate() nothrow;
982 /// ditto
983 alias TraceCallbackDelegate = void delegate(string sql) nothrow;
984 /// ditto
985 alias ProfileCallbackDelegate = void delegate(string sql, ulong time) nothrow;
986 
987 /// Information about a table column.
988 struct TableColumnMetadata {
989     string declaredTypeName; ///
990     string collationSequenceName; ///
991     bool isNotNull; ///
992     bool isPrimaryKey; ///
993     bool isAutoIncrement; ///
994 }
995 
996 version (_UnlockNotify) {
997     /++
998     UnlockNotifyHandler interface to be used for custom implementations of UnlockNotify pattern with SQLite.
999 
1000     Note:
1001     For the C API sqlite3_unlock_notify to be used, this library must be compiled with
1002     `-version=SqliteEnableUnlockNotify`.
1003     Otherwise only emulated solution is provided, that is based on retries for the defined amount of time.
1004 
1005     Implementation must be able to handle situation when emit is called sooner than the wait itself.
1006 
1007     See_Also: $(LINK http://sqlite.org/c3ref/unlock_notify.html).
1008     See_Also: $(LINK http://www.sqlite.org/unlock_notify.html).
1009     +/
1010     interface IUnlockNotifyHandler {
1011         version (SqliteEnableUnlockNotify) {
1012             /// Blocks until emit is called
1013             void wait();
1014 
1015             /++
1016             Unlocks the handler.
1017             This is called from registered callback from SQLite.
1018 
1019             Params:
1020                 state = Value to set as a handler result. It can be SQLITE_LOCKED or SQLITE_OK.
1021             +/
1022             void emit(int state) nothrow;
1023         } else {
1024             /++
1025             This is used as an alternative when SQLite is not compiled with SQLITE_ENABLE_UNLOCK_NOTIFY, and
1026             when the library is built with `-version=SqliteFakeUnlockNotify`.
1027             Using this, the handler tries to wait out the SQLITE_LOCKED state for some time.
1028             Implementation have to block for some amount of time and check if total amount is not greater than some constant afterwards.
1029             If there is still some time to try again, the handler must set the result to SQLITE_OK or to SQLITE_LOCKED otherwise.
1030             +/
1031             void waitOne();
1032         }
1033 
1034         /// Resets the handler for the next use
1035         void reset();
1036 
1037         /// Result after wait is finished
1038         @property int result() const;
1039     }
1040 
1041     version (SqliteEnableUnlockNotify) {
1042         /++
1043         UnlockNotifyHandler used when SQLite is compiled with SQLITE_ENABLE_UNLOCK_NOTIFY, and
1044         when the library is built with `-version=SqliteEnableUnlockNotify`.
1045         It is implemented using the standard `core.sync` package.
1046 
1047         Use setUnlockNotifyHandler method to handle the database lock.
1048 
1049         See_Also: $(LINK http://sqlite.org/c3ref/unlock_notify.html).
1050         See_Also: $(LINK http://www.sqlite.org/unlock_notify.html).
1051         +/
1052         final class UnlockNotifyHandler : IUnlockNotifyHandler {
1053             import core.sync.condition : Condition;
1054             import core.sync.mutex : Mutex;
1055 
1056             private {
1057                 __gshared Mutex mtx;
1058                 __gshared Condition cond;
1059                 __gshared int res;
1060                 __gshared bool fired;
1061             }
1062 
1063             /// Constructor
1064             this() {
1065                 mtx = new Mutex();
1066                 cond = new Condition(mtx);
1067             }
1068 
1069             /// Blocks until emit is called
1070             void wait() {
1071                 synchronized (mtx) {
1072                     if (!fired)
1073                         cond.wait();
1074                 }
1075             }
1076 
1077             /// Unlocks the handler, state is one of SQLITE_LOCKED or SQLITE_OK
1078             void emit(int res) nothrow
1079             in {
1080                 assert(res == SQLITE_LOCKED || res == SQLITE_OK);
1081             }
1082             body {
1083                 try {
1084                     synchronized (mtx) {
1085                         this.res = res;
1086                         fired = true;
1087                         cond.notify();
1088                     }
1089                 } catch (Exception) {
1090                 }
1091             }
1092 
1093             /// Resets the handler for the next use
1094             void reset() {
1095                 res = SQLITE_LOCKED;
1096                 fired = false;
1097             }
1098 
1099             /// Result after wait is finished
1100             @property int result() const
1101             out (result) {
1102                 assert(result == SQLITE_OK || result == SQLITE_LOCKED);
1103             }
1104             body {
1105                 return res;
1106             }
1107         }
1108     } else {
1109         /++
1110         UnlockNotifyHandler that can be used when SQLite is not compiled with SQLITE_ENABLE_UNLOCK_NOTIFY,
1111         and when the library is built with `-version=SqliteFakeUnlockNotify`..
1112         It retries the statement execution for the provided amount of time before the SQLITE_LOCKED is returned.
1113 
1114         Use setUnlockNotifyHandler method to handle the database lock.
1115 
1116         See_Also: $(LINK http://sqlite.org/c3ref/unlock_notify.html).
1117         See_Also: $(LINK http://www.sqlite.org/unlock_notify.html).
1118         +/
1119         final class UnlockNotifyHandler : IUnlockNotifyHandler {
1120             import core.time : Duration, msecs;
1121             import std.datetime.stopwatch : StopWatch;
1122 
1123             private {
1124                 int res;
1125                 Duration maxDuration;
1126                 StopWatch sw;
1127             }
1128 
1129             /// Constructor
1130             this(Duration max = 1000.msecs)
1131             in {
1132                 assert(max > Duration.zero);
1133             }
1134             body {
1135                 maxDuration = max;
1136             }
1137 
1138             /// Blocks for some time to retry the statement
1139             void waitOne() {
1140                 import core.thread : Thread;
1141                 import std.random : uniform;
1142 
1143                 if (!sw.running)
1144                     sw.start;
1145 
1146                 Thread.sleep(uniform(50, 100).msecs);
1147 
1148                 if (sw.peek > maxDuration) {
1149                     sw.stop;
1150                     res = SQLITE_LOCKED;
1151                 } else
1152                     res = SQLITE_OK;
1153             }
1154 
1155             /// Resets the handler for the next use
1156             void reset() {
1157                 res = SQLITE_LOCKED;
1158                 sw.reset();
1159             }
1160 
1161             /// Result after wait is finished
1162             @property int result() const
1163             out (result) {
1164                 assert(result == SQLITE_OK || result == SQLITE_LOCKED);
1165             }
1166             body {
1167                 return res;
1168             }
1169         }
1170     }
1171 
1172     unittest {
1173         import core.time : Duration, msecs;
1174 
1175         /++
1176         Tests the unlock notify facility.
1177         Params:
1178             delay - time to wait in the transaction to block the other one
1179             expected - expected result (can be used to test timeout when fake unlock notify is used)
1180         +/
1181         void testUnlockNotify(Duration delay = 500.msecs, int expected = 3) {
1182             import core.thread : Thread;
1183             import core.time : msecs, seconds;
1184             import std.concurrency : spawn;
1185 
1186             static void test(int n, Duration delay) {
1187                 auto db = Database("file::memory:?cache=shared",
1188                         SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
1189                         | SQLITE_OPEN_URI | SQLITE_OPEN_MEMORY);
1190                 db.setUnlockNotifyHandler = new UnlockNotifyHandler();
1191                 db.execute("BEGIN IMMEDIATE");
1192                 Thread.sleep(delay);
1193                 db.execute("INSERT INTO foo (bar) VALUES (?)", n);
1194                 db.commit();
1195             }
1196 
1197             auto db = Database("file::memory:?cache=shared",
1198                     SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI
1199                     | SQLITE_OPEN_MEMORY);
1200             db.execute(`CREATE TABLE foo (bar INTEGER);`);
1201 
1202             spawn(&test, 1, delay);
1203             Thread.sleep(100.msecs);
1204             spawn(&test, 2, delay);
1205             Thread.sleep(2 * delay + 100.msecs);
1206             assert(db.execute("SELECT sum(bar) FROM foo").oneValue!int == expected,
1207                     format!"%s != %s"(db.execute("SELECT sum(bar) FROM foo")
1208                         .oneValue!int, expected));
1209         }
1210 
1211         testUnlockNotify();
1212         version (SqliteFakeUnlockNotify)
1213             testUnlockNotify(1500.msecs, 1); //timeout test
1214     }
1215 }
1216 
1217 /++
1218 Exception thrown when SQLite functions return an error.
1219 +/
1220 class SqliteException : Exception {
1221     /++
1222     The _code of the error that raised the exception, or 0 if this _code is not known.
1223     +/
1224     int code;
1225 
1226     /++
1227     The SQL code that raised the exception, if applicable.
1228     +/
1229     string sql;
1230 
1231     private this(string msg, string sql, int code, string file = __FILE__,
1232             size_t line = __LINE__, Throwable next = null) {
1233         this.sql = sql;
1234         this.code = code;
1235         super(msg, file, line, next);
1236     }
1237 
1238 package(d2sqlite3):
1239     this(string msg, int code, string sql = null, string file = __FILE__,
1240             size_t line = __LINE__, Throwable next = null) {
1241         this("error %d: %s".format(code, msg), sql, code, file, line, next);
1242     }
1243 
1244     this(string msg, string sql = null, string file = __FILE__,
1245             size_t line = __LINE__, Throwable next = null) {
1246         this(msg, sql, 0, file, line, next);
1247     }
1248 }