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, ¬Null, 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 }