1 // Written in the D programming language 2 /++ 3 This module provides a small wrapper around SQLite for the D programming language. 4 It wraps the C API in an idiomatic manner and handles built-in D types and 5 `Nullable!T` automatically. 6 7 Authors: 8 Nicolas Sicard (biozic) and other contributors at $(LINK https://github.com/biozic/d2sqlite3) 9 10 Copyright: 11 Copyright 2011-15 Nicolas Sicard. 12 13 License: 14 $(LINK2 http://www.boost.org/LICENSE_1_0.txt, Boost License 1.0). 15 16 Macros: 17 D = <tt>$0</tt> 18 DK = <strong><tt>$0</tt></strong> 19 +/ 20 module sqlite.d2sqlite3; 21 22 import std.algorithm; 23 import std.array; 24 import std.conv; 25 import std.exception; 26 import std.range; 27 import std.string; 28 import std.traits; 29 import std.typecons; 30 import std.typetuple; 31 import std.variant; 32 import core.stdc.string : memcpy; 33 import core.memory : GC; 34 35 36 public import etc.c.sqlite3; 37 //public import sqlite3; replaced by etc.c.sqlite3 and some line from sqlite3. 38 extern (C) nothrow 39 { 40 int sqlite3_bind_text64(sqlite3_stmt*, int, const char*, sqlite3_uint64, 41 void function (void*), ubyte encoding); 42 int sqlite3_bind_blob64(sqlite3_stmt*, int, const void*, sqlite3_uint64, 43 void function (void*)); 44 int sqlite3_libversion_number(); 45 int sqlite3_threadsafe(); 46 int sqlite3_changes(sqlite3*); 47 int sqlite3_total_changes(sqlite3*); 48 int sqlite3_errcode(sqlite3 *db); 49 int sqlite3_bind_parameter_count(sqlite3_stmt*); 50 int sqlite3_column_count(sqlite3_stmt *pStmt); 51 void sqlite3_result_text64(sqlite3_context*, const char*,sqlite3_uint64, 52 void function(void*), ubyte encoding); 53 void sqlite3_result_blob64(sqlite3_context*,const void*,sqlite3_uint64,void function(void*)); 54 } 55 56 57 58 /// SQLite type codes 59 enum SqliteType 60 { 61 INTEGER = SQLITE_INTEGER, /// 62 FLOAT = SQLITE_FLOAT, /// 63 TEXT = SQLITE3_TEXT, /// 64 BLOB = SQLITE_BLOB, /// 65 NULL = SQLITE_NULL /// 66 } 67 68 /++ 69 Gets the library's version string (e.g. "3.8.7"). 70 +/ 71 string versionString() 72 { 73 return to!string(sqlite3_libversion()); 74 } 75 76 /++ 77 Gets the library's version number (e.g. 3008007). 78 +/ 79 int versionNumber() nothrow 80 { 81 return sqlite3_libversion_number(); 82 } 83 84 unittest 85 { 86 import std.string : startsWith; 87 assert(versionString.startsWith("3.")); 88 assert(versionNumber >= 3008007); 89 } 90 91 /++ 92 Tells whether SQLite was compiled with the thread-safe options. 93 94 See_also: ($LINK http://www.sqlite.org/c3ref/threadsafe.html). 95 +/ 96 bool threadSafe() nothrow 97 { 98 return cast(bool) sqlite3_threadsafe(); 99 } 100 unittest 101 { 102 auto ts = threadSafe; 103 } 104 105 /++ 106 Manually initializes (or shuts down) SQLite. 107 108 SQLite initializes itself automatically on the first request execution, so this 109 usually wouldn't be called. Use for instance before a call to config(). 110 +/ 111 void initialize() 112 { 113 auto result = sqlite3_initialize(); 114 enforce(result == SQLITE_OK, new SqliteException("Initialization: error %s".format(result))); 115 } 116 /// Ditto 117 void shutdown() 118 { 119 auto result = sqlite3_shutdown(); 120 enforce(result == SQLITE_OK, new SqliteException("Shutdown: error %s".format(result))); 121 } 122 123 /++ 124 Sets a configuration option. Use before initialization, e.g. before the first 125 call to initialize and before execution of the first statement. 126 127 See_Also: $(LINK http://www.sqlite.org/c3ref/config.html). 128 +/ 129 void config(Args...)(int code, Args args) 130 { 131 auto result = sqlite3_config(code, args); 132 enforce(result == SQLITE_OK, 133 new SqliteException("Configuration: error %s".format(result))); 134 } 135 version (D_Ddoc) 136 { 137 /// 138 unittest 139 { 140 config(SQLITE_CONFIG_MULTITHREAD); 141 142 // Setup a logger callback function 143 config(SQLITE_CONFIG_LOG, 144 function(void* p, int code, const(char*) msg) 145 { 146 import std.stdio; 147 writefln("%05d | %s", code, msg.to!string); 148 }, 149 null); 150 initialize(); 151 } 152 } 153 else 154 { 155 unittest 156 { 157 shutdown(); 158 config(SQLITE_CONFIG_MULTITHREAD); 159 config(SQLITE_CONFIG_LOG, function(void* p, int code, const(char*) msg) {}, null); 160 initialize(); 161 } 162 } 163 164 165 /++ 166 A caracteristic of user-defined functions or aggregates. 167 +/ 168 enum Deterministic 169 { 170 /++ 171 The returned value is the same if the function is called with the same parameters. 172 +/ 173 yes = 0x800, 174 175 /++ 176 The returned value can vary even if the function is called with the same parameters. 177 +/ 178 no = 0 179 } 180 181 182 /++ 183 An SQLite database connection. 184 185 This struct is a reference-counted wrapper around a $(D sqlite3*) pointer. 186 +/ 187 struct Database 188 { 189 private: 190 struct _Payload 191 { 192 sqlite3* handle; 193 void* progressHandler; 194 195 this(sqlite3* handle) @safe pure nothrow 196 { 197 this.handle = handle; 198 } 199 200 ~this() 201 { 202 if (handle) 203 { 204 auto result = sqlite3_close(handle); 205 enforce(result == SQLITE_OK, new SqliteException(errmsg(handle), result)); 206 } 207 handle = null; 208 ptrFree(progressHandler); 209 } 210 211 @disable this(this); 212 void opAssign(_Payload) { assert(false); } 213 } 214 215 alias Payload = RefCounted!(_Payload, RefCountedAutoInitialize.no); 216 Payload p; 217 218 void check(int result) { 219 enforce(result == SQLITE_OK, new SqliteException(errmsg(p.handle), result)); 220 } 221 222 public: 223 /++ 224 Opens a database connection. 225 226 Params: 227 path = The path to the database file. In recent versions of SQLite, the path can be 228 an URI with options. 229 230 flags = Options flags. 231 232 See_Also: $(LINK http://www.sqlite.org/c3ref/open.html) to know how to use the flags 233 parameter or to use path as a file URI if the current configuration allows it. 234 +/ 235 this(string path, int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE) 236 { 237 sqlite3* hdl; 238 auto result = sqlite3_open_v2(path.toStringz, &hdl, flags, null); 239 enforce(result == SQLITE_OK, new SqliteException(p.handle 240 ? errmsg(p.handle) : "Error opening the database", result)); 241 p = Payload(hdl); 242 } 243 244 /++ 245 Gets the SQLite internal _handle of the database connection. 246 +/ 247 sqlite3* handle() @property @safe pure nothrow 248 { 249 return p.handle; 250 } 251 252 /++ 253 Gets the path associated with an attached database. 254 255 Params: 256 database = The name of an attached database. 257 258 Returns: The absolute path of the attached database. 259 If there is no attached database, or if database is a temporary or 260 in-memory database, then null is returned. 261 +/ 262 string attachedFilePath(string database = "main") 263 { 264 return sqlite3_db_filename(p.handle, database.toStringz).to!string; 265 } 266 267 /++ 268 Gets the read-only status of an attached database. 269 270 Params: 271 database = The name of an attached database. 272 +/ 273 bool isReadOnly(string database = "main") 274 { 275 int ret = sqlite3_db_readonly(p.handle, database.toStringz); 276 enforce(ret >= 0, new SqliteException("Database not found: %s".format(database))); 277 return ret == 1; 278 } 279 280 /++ 281 Gets metadata for a specific table column of an attached database. 282 283 Params: 284 table = The name of the table. 285 286 column = The name of the column. 287 288 database = The name of a database attached. If null, then all attached databases 289 are searched for the table using the same algorithm used by the database engine 290 to resolve unqualified table references. 291 +/ 292 ColumnMetadata tableColumnMetadata(string table, string column, string database = "main") 293 { 294 ColumnMetadata data; 295 char* pzDataType, pzCollSeq; 296 int notNull, primaryKey, autoIncrement; 297 check(sqlite3_table_column_metadata(p.handle, database.toStringz, table.toStringz, 298 column.toStringz, &pzDataType, &pzCollSeq, ¬Null, &primaryKey, &autoIncrement)); 299 data.declaredTypeName = pzDataType.to!string; 300 data.collationSequenceName = pzCollSeq.to!string; 301 data.isNotNull = cast(bool) notNull; 302 data.isPrimaryKey = cast(bool) primaryKey; 303 data.isAutoIncrement = cast(bool) autoIncrement; 304 return data; 305 } 306 unittest 307 { 308 auto db = Database(":memory:"); 309 db.run("CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, 310 val FLOAT NOT NULL)"); 311 assert(db.tableColumnMetadata("test", "id") == 312 ColumnMetadata("INTEGER", "BINARY", false, true, true)); 313 assert(db.tableColumnMetadata("test", "val") == 314 ColumnMetadata("FLOAT", "BINARY", true, false, false)); 315 } 316 317 /++ 318 Explicitly closes the database. 319 320 After this function has been called successfully, using the database or one of its 321 prepared statement is an error. 322 +/ 323 void close() 324 { 325 check(sqlite3_close(handle)); 326 p.handle = null; 327 } 328 329 /++ 330 Executes a single SQL statement and returns the results directly. It's the equivalent 331 of $(D prepare(sql).execute()). 332 333 The results become undefined when the Database goes out of scope and is destroyed. 334 +/ 335 ResultRange execute(string sql) 336 { 337 return prepare(sql).execute(); 338 } 339 /// 340 unittest 341 { 342 auto db = Database(":memory:"); 343 db.execute("VACUUM"); 344 } 345 346 /++ 347 Runs an SQL script that can contain multiple statements. 348 349 Params: 350 sql = The code of the script. 351 352 dg = A delegate to call for each statement to handle the results. The passed 353 ResultRange will be empty if a statement doesn't return rows. If the delegate 354 return false, the execution is aborted. 355 +/ 356 void run(string sql, scope bool delegate(ResultRange) dg = null) 357 { 358 foreach (statement; sql.byStatement) 359 { 360 auto stmt = prepare(statement); 361 auto results = stmt.execute(); 362 if (dg && !dg(results)) 363 return; 364 } 365 } 366 /// 367 unittest 368 { 369 auto db = Database(":memory:"); 370 db.run(`CREATE TABLE test1 (val INTEGER); 371 CREATE TABLE test2 (val FLOAT); 372 DROP TABLE test1; 373 DROP TABLE test2;`); 374 } 375 376 /++ 377 Prepares (compiles) a single SQL statement and returngs it, so that it can be bound to 378 values before execution. 379 380 The statement becomes invalid if the Database goes out of scope and is destroyed. 381 +/ 382 Statement prepare(string sql) 383 { 384 return Statement(this, sql); 385 } 386 387 /// Convenience functions equivalent to an SQL statement. 388 void begin() { execute("BEGIN"); } 389 /// Ditto 390 void commit() { execute("COMMIT"); } 391 /// Ditto 392 void rollback() { execute("ROLLBACK"); } 393 394 /++ 395 Returns the rowid of the last INSERT statement. 396 +/ 397 long lastInsertRowid() 398 { 399 return sqlite3_last_insert_rowid(p.handle); 400 } 401 402 /++ 403 Gets the number of database rows that were changed, inserted or deleted by the most 404 recently executed SQL statement. 405 +/ 406 int changes() @property nothrow 407 { 408 assert(p.handle); 409 return sqlite3_changes(p.handle); 410 } 411 412 /++ 413 Gets the number of database rows that were changed, inserted or deleted since the 414 database was opened. 415 +/ 416 int totalChanges() @property nothrow 417 { 418 assert(p.handle); 419 return sqlite3_total_changes(p.handle); 420 } 421 422 /++ 423 Gets the SQLite error code of the last operation. 424 +/ 425 int errorCode() @property nothrow 426 { 427 return p.handle ? sqlite3_errcode(p.handle) : 0; 428 } 429 430 version (SQLITE_OMIT_LOAD_EXTENSION) {} 431 else 432 { 433 /++ 434 Enables or disables loading extensions. 435 +/ 436 void enableLoadExtensions(bool enable = true) 437 { 438 enforce(sqlite3_enable_load_extension(p.handle, enable) == SQLITE_OK, 439 new SqliteException("Could not enable loading extensions.")); 440 } 441 442 /++ 443 Loads an extension. 444 445 Params: 446 path = The path of the extension file. 447 448 entryPoint = The name of the entry point function. If null is passed, SQLite 449 uses the name of the extension file as the entry point. 450 +/ 451 void loadExtension(string path, string entryPoint = null) 452 { 453 auto ret = sqlite3_load_extension(p.handle, path.toStringz, entryPoint.toStringz, null); 454 enforce(ret == SQLITE_OK, new SqliteException( 455 "Could not load extension: %s:%s".format(entryPoint, path))); 456 } 457 } 458 459 /++ 460 Creates and registers a new function in the database. 461 462 If a function with the same name and the same arguments already exists, it is replaced 463 by the new one. 464 465 The memory associated with the function will be released when the database connection 466 is closed. 467 468 Params: 469 name = The name that the function will have in the database; this name defaults to 470 the identifier of $(D_PARAM fun). 471 472 fun = a $(D delegate) or $(D function) that implements the function. $(D_PARAM fun) 473 must satisfy these criteria: 474 $(UL 475 $(LI It must not be a variadic.) 476 $(LI Its arguments must all have a type that is compatible with SQLite types: 477 it must be a boolean or numeric type, a string, an array, null, 478 or a Nullable!T where T is any of the previous types.) 479 $(LI Its return value must also be of a compatible type.) 480 ) 481 482 det = Tells SQLite whether the result of the function is deterministic, i.e. if the 483 result is the same when called with the same parameters. Recent versions of SQLite 484 perform optimizations based on this. Set to $(D Deterministic.no) otherwise. 485 486 See_Also: $(LINK http://www.sqlite.org/c3ref/create_function.html). 487 +/ 488 void createFunction(string name, T)(T fun, Deterministic det = Deterministic.yes) 489 { 490 static assert(isCallable!fun, "expecting a callable"); 491 static assert(variadicFunctionStyle!(fun) == Variadic.no, 492 "variadic functions are not supported"); 493 494 alias ReturnType!fun RT; 495 static assert(!is(RT == void), "function must not return void"); 496 497 alias PT = staticMap!(Unqual, ParameterTypeTuple!fun); 498 499 extern(C) static 500 void x_func(sqlite3_context* context, int argc, sqlite3_value** argv) 501 { 502 PT args; 503 504 foreach (i, type; PT) 505 args[i] = getValue!type(argv[i]); 506 507 auto ptr = sqlite3_user_data(context); 508 509 try 510 setResult(context, delegateUnwrap!T(ptr)(args)); 511 catch (Exception e) 512 { 513 auto txt = "error in function %s(): %s".format(name, e.msg); 514 sqlite3_result_error(context, txt.toStringz, -1); 515 } 516 } 517 518 assert(p.handle); 519 check(sqlite3_create_function_v2(p.handle, name.toStringz, PT.length, SQLITE_UTF8 | det, 520 delegateWrap(fun), &x_func, null, null, &ptrFree)); 521 } 522 /// 523 unittest 524 { 525 string fmt = "Hello, %s!"; 526 string my_msg(string name) 527 { 528 return fmt.format(name); 529 } 530 auto db = Database(":memory:"); 531 db.createFunction!"msg"(&my_msg); 532 auto msg = db.execute("SELECT msg('John')").oneValue!string; 533 assert(msg == "Hello, John!"); 534 } 535 536 /++ 537 Creates and registers a new aggregate function in the database. 538 539 Params: 540 T = The $(D struct) type implementing the aggregate. T must be default-construtible 541 (a POD type) and implement at least these two methods: $(D accumulate) and $(D result). 542 Each parameter and the returned type of $(D accumulate) and $(D result) must be 543 a boolean or numeric type, a string, an array, null, or a Nullable!T 544 where T is any of the previous types. 545 546 name = The name that the aggregate function will have in the database. 547 548 det = Tells SQLite whether the result of the function is deterministic, i.e. if the 549 result is the same when called with the same parameters. Recent versions of SQLite 550 perform optimizations based on this. Set to $(D Deterministic.no) otherwise. 551 552 See_Also: $(LINK http://www.sqlite.org/c3ref/create_function.html). 553 +/ 554 void createAggregate(T, string name)(Deterministic det = Deterministic.yes) 555 { 556 static assert(isAggregateType!T, 557 name ~ " should be an aggregate type"); 558 static assert(__traits(isPOD, T), 559 name ~ " should be a POD type"); 560 static assert(is(typeof(T.accumulate) == function), 561 name ~ " shoud have a method named accumulate"); 562 static assert(is(typeof(T.result) == function), 563 name ~ " shoud have a method named result"); 564 static assert(variadicFunctionStyle!(T.accumulate) == Variadic.no, 565 "variadic functions are not supported"); 566 static assert(variadicFunctionStyle!(T.result) == Variadic.no, 567 "variadic functions are not supported"); 568 569 alias staticMap!(Unqual, ParameterTypeTuple!(T.accumulate)) PT; 570 alias ReturnType!(T.result) RT; 571 572 extern(C) static 573 void x_step(sqlite3_context* context, int argc, sqlite3_value** argv) 574 { 575 auto aggregate = cast(T*) sqlite3_aggregate_context(context, T.sizeof); 576 if (!aggregate) 577 { 578 sqlite3_result_error_nomem(context); 579 return; 580 } 581 582 PT args; 583 foreach (i, type; PT) 584 args[i] = getValue!type(argv[i]); 585 586 try 587 aggregate.accumulate(args); 588 catch (Exception e) 589 { 590 auto txt = "error in aggregate function %s(): %s".format(name, e.msg); 591 sqlite3_result_error(context, txt.toStringz, -1); 592 } 593 } 594 595 extern(C) static 596 void x_final(sqlite3_context* context) 597 { 598 auto aggregate = cast(T*) sqlite3_aggregate_context(context, T.sizeof); 599 if (!aggregate) 600 { 601 sqlite3_result_error_nomem(context); 602 return; 603 } 604 605 try 606 setResult(context, aggregate.result()); 607 catch (Exception e) 608 { 609 auto txt = "error in aggregate function %s(): %s".format(name, e.msg); 610 sqlite3_result_error(context, txt.toStringz, -1); 611 } 612 } 613 614 assert(p.handle); 615 check(sqlite3_create_function_v2(p.handle, name.toStringz, PT.length, SQLITE_UTF8 | det, 616 null, null, &x_step, &x_final, null)); 617 } 618 /// 619 unittest // Aggregate creation 620 { 621 import std.array : appender, join; 622 623 static struct Joiner 624 { 625 Appender!(string[]) app; 626 string separator; 627 628 void accumulate(string word, string sep) 629 { 630 separator = sep; 631 app.put(word); 632 } 633 634 string result() 635 { 636 return join(app.data, separator); 637 } 638 } 639 640 auto db = Database(":memory:"); 641 db.execute("CREATE TABLE test (word TEXT)"); 642 db.createAggregate!(Joiner, "strjoin"); 643 644 auto statement = db.prepare("INSERT INTO test VALUES (?)"); 645 auto list = ["My", "cat", "is", "black"]; 646 foreach (word; list) 647 { 648 statement.bind(1, word); 649 statement.execute(); 650 statement.reset(); 651 } 652 653 auto text = db.execute("SELECT strjoin(word, '-') FROM test").oneValue!string; 654 assert(text == "My-cat-is-black"); 655 } 656 657 /++ 658 Creates and registers a collation function in the database. 659 660 Params: 661 fun = An alias to the D implementation of the function. The function $(D_PARAM fun) 662 must satisfy these criteria: 663 $(UL 664 $(LI If s1 is less than s2, $(D ret < 0).) 665 $(LI If s1 is equal to s2, $(D ret == 0).) 666 $(LI If s1 is greater than s2, $(D ret > 0).) 667 $(LI If s1 is equal to s2, then s2 is equal to s1.) 668 $(LI If s1 is equal to s2 and s2 is equal to s3, then s1 is equal to s3.) 669 $(LI If s1 is less than s2, then s2 is greater than s1.) 670 $(LI If s1 is less than s2 and s2 is less than s3, then s1 is less than s3.) 671 ) 672 673 name = The name that the function will have in the database; this name defaults to 674 the identifier of $(D_PARAM fun). 675 676 See_Also: $(LINK http://www.sqlite.org/lang_aggfunc.html) 677 +/ 678 void createCollation(string name, T)(T fun) 679 { 680 static assert(isCallable!fun, "expecting a callable"); 681 static assert(variadicFunctionStyle!(fun) == Variadic.no, 682 "variadic functions are not supported"); 683 684 alias ParameterTypeTuple!fun PT; 685 static assert(isSomeString!(PT[0]), 686 "the first argument of function " ~ name ~ " should be a string"); 687 static assert(isSomeString!(PT[1]), 688 "the second argument of function " ~ name ~ " should be a string"); 689 static assert(isImplicitlyConvertible!(ReturnType!fun, int), 690 "function " ~ name ~ " should return a value convertible to an int"); 691 692 extern (C) static 693 int x_compare(void* ptr, int n1, const(void*) str1, int n2, const(void*) str2) 694 { 695 auto dg = delegateUnwrap!T(ptr); 696 char[] s1, s2; 697 s1.length = n1; 698 s2.length = n2; 699 memcpy(s1.ptr, str1, n1); 700 memcpy(s2.ptr, str2, n2); 701 return dg(cast(immutable) s1, cast(immutable) s2); 702 } 703 704 assert(p.handle); 705 check(sqlite3_create_collation_v2(p.handle, name.toStringz, SQLITE_UTF8, 706 delegateWrap(fun), &x_compare, &ptrFree)); 707 } 708 /// 709 unittest // Collation creation 710 { 711 static int my_collation(string s1, string s2) 712 { 713 import std.uni; 714 return icmp(s1, s2); 715 } 716 717 auto db = Database(":memory:"); 718 db.createCollation!"my_coll"(&my_collation); 719 db.execute("CREATE TABLE test (word TEXT)"); 720 721 auto statement = db.prepare("INSERT INTO test (word) VALUES (?)"); 722 foreach (word; ["straße", "strasses"]) 723 { 724 statement.bind(1, word); 725 statement.execute(); 726 statement.reset(); 727 } 728 729 auto word = db.execute("SELECT word FROM test ORDER BY word COLLATE my_coll") 730 .oneValue!string; 731 assert(word == "straße"); 732 } 733 734 /++ 735 Registers a delegate as the database's update hook. Any previously set hook is released. 736 737 See_Also: $(LINK http://www.sqlite.org/c3ref/commit_hook.html). 738 +/ 739 void setUpdateHook(scope void delegate(int type, string dbName, string tableName, long rowid) hook) 740 { 741 extern(C) static 742 void callback(void* ptr, int type, char* dbName, char* tableName, long rowid) 743 { 744 return delegateUnwrap!(void delegate(int, string, string, long))(ptr)( 745 type, dbName.to!string, tableName.to!string, rowid); 746 } 747 748 auto ptr = delegateWrap(hook); 749 auto prev = sqlite3_update_hook(p.handle, &callback, ptr); 750 ptrFree(prev); 751 } 752 /// 753 unittest 754 { 755 int i; 756 auto db = Database(":memory:"); 757 db.setUpdateHook((int type, string dbName, string tableName, long rowid) { 758 assert(type == SQLITE_INSERT); 759 assert(dbName == "main"); 760 assert(tableName == "test"); 761 assert(rowid == 1); 762 i = 42; 763 }); 764 db.execute("CREATE TABLE test (val INTEGER)"); 765 db.execute("INSERT INTO test VALUES (100)"); 766 assert(i == 42); 767 } 768 769 /++ 770 Registers a delegate as the database's commit or rollback hook. 771 Any previously set hook is released. 772 773 Params: 774 hook = For the commit hook, a delegate that should return 0 if the operation must be 775 aborted or another value if it can continue. 776 777 See_Also: $(LINK http://www.sqlite.org/c3ref/commit_hook.html). 778 +/ 779 void setCommitHook(int delegate() hook) 780 { 781 extern(C) static int callback(void* ptr) 782 { 783 return delegateUnwrap!(int delegate())(ptr)(); 784 } 785 786 auto ptr = delegateWrap(hook); 787 auto prev = sqlite3_commit_hook(p.handle, &callback, ptr); 788 ptrFree(prev); 789 } 790 /// Ditto 791 void setRollbackHook(void delegate() hook) 792 { 793 extern(C) static void callback(void* ptr) 794 { 795 delegateUnwrap!(void delegate())(ptr)(); 796 } 797 798 auto ptr = delegateWrap(hook); 799 auto prev = sqlite3_rollback_hook(p.handle, &callback, ptr); 800 ptrFree(prev); 801 } 802 /// 803 unittest 804 { 805 int i; 806 auto db = Database(":memory:"); 807 db.setCommitHook({ i = 42; return SQLITE_OK; }); 808 db.setRollbackHook({ i = 666; }); 809 db.begin(); 810 db.execute("CREATE TABLE test (val INTEGER)"); 811 db.rollback(); 812 assert(i == 666); 813 db.begin(); 814 db.execute("CREATE TABLE test (val INTEGER)"); 815 db.commit(); 816 assert(i == 42); 817 } 818 819 /++ 820 Sets the progress handler. 821 Any previously set handler is released. 822 823 Params: 824 pace = The approximate number of virtual machine instructions that are 825 evaluated between successive invocations of the handler. 826 827 handler = A delegate that should return 0 if the operation must be 828 aborted or another value if it can continue. 829 830 See_Also: $(LINK http://www.sqlite.org/c3ref/commit_hook.html). 831 +/ 832 void setProgressHandler(int pace, int delegate() handler) 833 { 834 extern(C) static int callback(void* ptr) 835 { 836 return delegateUnwrap!(int delegate())(ptr)(); 837 } 838 839 ptrFree(p.progressHandler); 840 auto ptr = delegateWrap(handler); 841 sqlite3_progress_handler(p.handle, pace, &callback, ptr); 842 p.progressHandler = ptr; 843 } 844 } 845 /// 846 unittest // Documentation example 847 { 848 // Note: exception handling is left aside for clarity. 849 850 import std.typecons : Nullable; 851 852 // Open a database in memory. 853 auto db = Database(":memory:"); 854 855 // Create a table 856 db.execute( 857 "CREATE TABLE person ( 858 id INTEGER PRIMARY KEY, 859 name TEXT NOT NULL, 860 score FLOAT 861 )" 862 ); 863 864 // Populate the table 865 866 // Prepare an INSERT statement 867 auto statement = db.prepare( 868 "INSERT INTO person (name, score) 869 VALUES (:name, :score)" 870 ); 871 872 // Bind values one by one (by parameter name or index) 873 statement.bind(":name", "John"); 874 statement.bind(2, 77.5); 875 statement.execute(); 876 877 statement.reset(); // Need to reset the statement after execution. 878 879 // Bind muliple values at once 880 statement.bindAll("John", null); 881 statement.execute(); 882 883 // Count the changes 884 assert(db.totalChanges == 2); 885 886 // Count the Johns in the table. 887 auto count = db.execute("SELECT count(*) FROM person WHERE name == 'John'") 888 .oneValue!long; 889 assert(count == 2); 890 891 // Read the data from the table lazily 892 auto results = db.execute("SELECT * FROM person"); 893 foreach (row; results) 894 { 895 // Retrieve "id", which is the column at index 0, and contains an int, 896 // e.g. using the peek function (best performance). 897 auto id = row.peek!long(0); 898 899 // Retrieve "name", e.g. using opIndex(string), which returns a ColumnData. 900 auto name = row["name"].as!string; 901 902 // Retrieve "score", which is at index 3, e.g. using the peek function, 903 // using a Nullable type 904 auto score = row.peek!(Nullable!double)(3); 905 if (!score.isNull) { 906 // ... 907 } 908 } 909 910 // Read all the table in memory at once 911 auto data = RowCache(db.execute("SELECT * FROM person")); 912 foreach (row; data) 913 { 914 auto id = row[0].as!long; 915 auto last = row["name"].as!string; 916 auto score = row[2].as!(Nullable!double); 917 // etc. 918 } 919 } 920 921 unittest // Database construction 922 { 923 Database db1; 924 auto db2 = db1; 925 db1 = Database(":memory:"); 926 db2 = Database(""); 927 auto db3 = Database(null); 928 db1 = db2; 929 assert(db2.p.refCountedStore.refCount == 2); 930 assert(db1.p.refCountedStore.refCount == 2); 931 } 932 933 unittest 934 { 935 auto db = Database(":memory:"); 936 assert(db.attachedFilePath("main") is null); 937 assert(!db.isReadOnly); 938 db.close(); 939 } 940 941 942 unittest // Execute an SQL statement 943 { 944 auto db = Database(":memory:"); 945 db.run(""); 946 db.run("-- This is a comment!"); 947 db.run(";"); 948 db.run("ANALYZE; VACUUM;"); 949 } 950 951 version (DigitalMars) unittest // Unexpected multiple statements 952 { 953 auto db = Database(":memory:"); 954 db.execute("BEGIN; CREATE TABLE test (val INTEGER); ROLLBACK;"); 955 //assertThrown(db.execute("DROP TABLE test")); 956 957 db.execute("CREATE TABLE test (val INTEGER); DROP TABLE test;"); 958 assertNotThrown(db.execute("DROP TABLE test;")); 959 960 db.execute("SELECT 1; CREATE TABLE test (val INTEGER); DROP TABLE test;"); 961 //assertThrown(db.execute("DROP TABLE test")); 962 } 963 964 unittest // Multiple statements with callback 965 { 966 auto db = Database(":memory:"); 967 RowCache[] rows; 968 db.run("SELECT 1, 2, 3; SELECT 'A', 'B', 'C';", (ResultRange r) { 969 rows ~= RowCache(r); 970 return true; 971 }); 972 assert(equal!"a.as!int == b"(rows[0][0], [1, 2, 3])); 973 assert(equal!"a.as!string == b"(rows[1][0], ["A", "B", "C"])); 974 } 975 976 unittest // Different arguments and result types with createFunction 977 { 978 import std.math; 979 980 auto db = Database(":memory:"); 981 982 T display(T)(T value) 983 { 984 return value; 985 } 986 987 db.createFunction!"display_integer"(&display!int); 988 db.createFunction!"display_float"(&display!double); 989 db.createFunction!"display_text"(&display!string); 990 db.createFunction!"display_blob"(&display!(ubyte[])); 991 992 assert(db.execute("SELECT display_integer(42)").oneValue!int == 42); 993 assert(db.execute("SELECT display_float(3.14)").oneValue!double == 3.14); 994 assert(db.execute("SELECT display_text('ABC')").oneValue!string == "ABC"); 995 assert(db.execute("SELECT display_blob(x'ABCD')").oneValue!(ubyte[]) == cast(ubyte[]) x"ABCD"); 996 997 assert(db.execute("SELECT display_integer(NULL)").oneValue!int == 0); 998 assert(db.execute("SELECT display_float(NULL)").oneValue!double.isNaN); 999 assert(db.execute("SELECT display_text(NULL)").oneValue!string is null); 1000 assert(db.execute("SELECT display_blob(NULL)").oneValue!(ubyte[]) is null); 1001 } 1002 1003 unittest // Different Nullable argument types with createFunction 1004 { 1005 import std.math; 1006 1007 auto db = Database(":memory:"); 1008 1009 auto display(T : Nullable!U, U...)(T value) 1010 { 1011 if (value.isNull) 1012 return T(); 1013 return value; 1014 } 1015 1016 db.createFunction!"display_integer"(&display!(Nullable!int)); 1017 db.createFunction!"display_float"(&display!(Nullable!double)); 1018 db.createFunction!"display_text"(&display!(Nullable!string)); 1019 db.createFunction!"display_blob"(&display!(Nullable!(ubyte[]))); 1020 1021 assert(db.execute("SELECT display_integer(42)").oneValue!(Nullable!int) == 42); 1022 assert(db.execute("SELECT display_float(3.14)").oneValue!(Nullable!double) == 3.14); 1023 assert(db.execute("SELECT display_text('ABC')").oneValue!(Nullable!string) == "ABC"); 1024 assert(db.execute("SELECT display_blob(x'ABCD')").oneValue!(Nullable!(ubyte[])) == cast(ubyte[]) x"ABCD"); 1025 1026 assert(db.execute("SELECT display_integer(NULL)").oneValue!(Nullable!int).isNull); 1027 assert(db.execute("SELECT display_float(NULL)").oneValue!(Nullable!double).isNull); 1028 assert(db.execute("SELECT display_text(NULL)").oneValue!(Nullable!string).isNull); 1029 assert(db.execute("SELECT display_blob(NULL)").oneValue!(Nullable!(ubyte[])).isNull); 1030 } 1031 1032 1033 /++ 1034 An SQLite statement execution. 1035 1036 This struct is a reference-counted wrapper around a $(D sqlite3_stmt*) pointer. Instances 1037 of this struct are typically returned by $(D Database.prepare()). 1038 +/ 1039 struct Statement 1040 { 1041 private: 1042 struct _Payload 1043 { 1044 Database db; 1045 sqlite3_stmt* handle; // null if error or empty statement 1046 1047 ~this() 1048 { 1049 auto result = sqlite3_finalize(handle); 1050 enforce(result == SQLITE_OK, new SqliteException(errmsg(handle), result)); 1051 handle = null; 1052 } 1053 1054 @disable this(this); 1055 void opAssign(_Payload) { assert(false); } 1056 } 1057 alias Payload = RefCounted!(_Payload, RefCountedAutoInitialize.no); 1058 Payload p; 1059 1060 this(Database db, string sql) 1061 { 1062 sqlite3_stmt* handle; 1063 const(char*) ptail; 1064 auto result = sqlite3_prepare_v2(db.handle(), sql.toStringz, sql.length.to!int, 1065 &handle, null); 1066 enforce(result == SQLITE_OK, new SqliteException(errmsg(db.handle()), result, sql)); 1067 p = Payload(db, handle); 1068 } 1069 1070 void checkResult(int result) 1071 { 1072 enforce(result == SQLITE_OK, new SqliteException(errmsg(p.handle), result)); 1073 } 1074 1075 public: 1076 /++ 1077 Gets the SQLite internal _handle of the statement. 1078 +/ 1079 sqlite3_stmt* handle() @property 1080 { 1081 return p.handle; 1082 } 1083 1084 /++ 1085 Tells whether the statement is empty (no SQL statement). 1086 +/ 1087 bool empty() @property 1088 { 1089 return p.handle is null; 1090 } 1091 /// 1092 unittest 1093 { 1094 auto db = Database(":memory:"); 1095 auto statement = db.prepare(" ; "); 1096 assert(statement.empty); 1097 } 1098 1099 /++ 1100 Binds values to parameters of this statement. 1101 1102 Params: 1103 index = The index of the parameter (starting from 1). 1104 1105 value = The bound _value. The type of value must be compatible with the SQLite 1106 types: it must be a boolean or numeric type, a string, an array, null, 1107 or a Nullable!T where T is any of the previous types. 1108 +/ 1109 void bind(T)(int index, T value) 1110 if (is(T == typeof(null)) || is(T == void*)) 1111 { 1112 assert(p.handle, "Operation on an empty statement"); 1113 checkResult(sqlite3_bind_null(p.handle, index)); 1114 } 1115 1116 /// ditto 1117 void bind(T)(int index, T value) 1118 if (isIntegral!T || isSomeChar!T) 1119 { 1120 assert(p.handle, "Operation on an empty statement"); 1121 checkResult(sqlite3_bind_int64(p.handle, index, value.to!long)); 1122 } 1123 1124 /// ditto 1125 void bind(T)(int index, T value) 1126 if (isBoolean!T) 1127 { 1128 assert(p.handle, "Operation on an empty statement"); 1129 checkResult(sqlite3_bind_int(p.handle, index, value.to!T)); 1130 } 1131 1132 /// ditto 1133 void bind(T)(int index, T value) 1134 if (isFloatingPoint!T) 1135 { 1136 assert(p.handle, "Operation on an empty statement"); 1137 checkResult(sqlite3_bind_double(p.handle, index, value.to!double)); 1138 } 1139 1140 /// ditto 1141 void bind(T)(int index, T value) 1142 if (isSomeString!T) 1143 { 1144 assert(p.handle, "Operation on an empty statement"); 1145 string str = value.to!string; 1146 auto ptr = anchorMem(cast(void*) str.ptr); 1147 checkResult(sqlite3_bind_text64(p.handle, index, cast(const(char)*) ptr, str.length, &releaseMem, SQLITE_UTF8)); 1148 } 1149 1150 /// ditto 1151 void bind(T)(int index, T value) 1152 if (isStaticArray!T) 1153 { 1154 assert(p.handle, "Operation on an empty statement"); 1155 checkResult(sqlite3_bind_blob64(p.handle, index, cast(void*) value.ptr, value.sizeof, SQLITE_TRANSIENT)); 1156 } 1157 1158 /// ditto 1159 void bind(T)(int index, T value) 1160 if (isDynamicArray!T && !isSomeString!T) 1161 { 1162 assert(p.handle, "Operation on an empty statement"); 1163 auto arr = cast(void[]) value; 1164 checkResult(sqlite3_bind_blob64(p.handle, index, anchorMem(arr.ptr), arr.length, &releaseMem)); 1165 } 1166 1167 /// ditto 1168 void bind(T)(int index, T value) 1169 if (is(T == Nullable!U, U...)) 1170 { 1171 if (value.isNull) 1172 { 1173 assert(p.handle, "Operation on an empty statement"); 1174 checkResult(sqlite3_bind_null(p.handle, index)); 1175 } 1176 else 1177 bind(index, value.get); 1178 } 1179 1180 /++ 1181 Binds values to parameters of this statement. 1182 1183 Params: 1184 name = The name of the parameter, including the ':', '@' or '$' that introduced it. 1185 1186 value = The bound _value. The type of value must be compatible with the SQLite 1187 types: it must be a boolean or numeric type, a string, an array, null, 1188 or a Nullable!T where T is any of the previous types. 1189 1190 Warning: 1191 While convenient, this overload of $(D bind) is less performant, because it has to 1192 retrieve the column index with a call to the SQLite function $(D 1193 sqlite3_bind_parameter_index). 1194 +/ 1195 void bind(T)(string name, T value) 1196 { 1197 auto index = sqlite3_bind_parameter_index(p.handle, name.toStringz); 1198 enforce(index > 0, new SqliteException(format("no parameter named '%s'", name))); 1199 bind(index, value); 1200 } 1201 1202 /++ 1203 Binds all the arguments at once in order. 1204 +/ 1205 void bindAll(Args...)(Args args) 1206 { 1207 foreach (index, _; Args) 1208 bind(index + 1, args[index]); 1209 } 1210 1211 /++ 1212 Clears the bindings. 1213 1214 This does not reset the statement. Use $(D Statement.reset()) for this. 1215 +/ 1216 void clearBindings() 1217 { 1218 assert(p.handle, "Operation on an empty statement"); 1219 checkResult(sqlite3_clear_bindings(p.handle)); 1220 } 1221 1222 /++ 1223 Executes the statement and return a (possibly empty) range of results. 1224 +/ 1225 ResultRange execute() 1226 { 1227 return ResultRange(this); 1228 } 1229 1230 /++ 1231 Resets a this statement before a new execution. 1232 1233 Calling this method invalidates any $(D ResultRange) struct returned by a previous call 1234 to $(D Database.execute()) or $(D Statement.execute()). 1235 1236 This does not clear the bindings. Use $(D Statement.clear()) for this. 1237 +/ 1238 void reset() 1239 { 1240 assert(p.handle, "Operation on an empty statement"); 1241 checkResult(sqlite3_reset(p.handle)); 1242 } 1243 1244 /++ 1245 Convenience function equivalent of: 1246 --- 1247 bindAll(args); 1248 execute(); 1249 reset(); 1250 --- 1251 +/ 1252 void inject(Args...)(Args args) 1253 { 1254 bindAll(args); 1255 execute(); 1256 reset(); 1257 } 1258 1259 /// Gets the count of bind parameters. 1260 int parameterCount() nothrow 1261 { 1262 if (p.handle) 1263 return sqlite3_bind_parameter_count(p.handle); 1264 else 1265 return 0; 1266 } 1267 1268 /++ 1269 Gets the name of the bind parameter at the given index. 1270 1271 Returns: The name of the parameter or null is not found or out of range. 1272 +/ 1273 string parameterName(int index) 1274 { 1275 assert(p.handle, "Operation on an empty statement"); 1276 return sqlite3_bind_parameter_name(p.handle, index).to!string; 1277 } 1278 1279 /++ 1280 Gets the index of a bind parameter. 1281 1282 Returns: The index of the parameter (the first parameter has the index 1) 1283 or 0 is not found or out of range. 1284 +/ 1285 int parameterIndex(string name) 1286 { 1287 assert(p.handle, "Operation on an empty statement"); 1288 return sqlite3_bind_parameter_index(p.handle, name.toStringz); 1289 } 1290 } 1291 1292 unittest 1293 { 1294 Statement statement; 1295 { 1296 auto db = Database(":memory:"); 1297 statement = db.prepare(" SELECT 42 "); 1298 } 1299 assert(statement.execute.oneValue!int == 42); 1300 } 1301 1302 unittest // Simple parameters binding 1303 { 1304 auto db = Database(":memory:"); 1305 db.execute("CREATE TABLE test (val INTEGER)"); 1306 1307 auto statement = db.prepare("INSERT INTO test (val) VALUES (?)"); 1308 statement.bind(1, 42); 1309 statement.execute(); 1310 statement.reset(); 1311 statement.bind(1, 42); 1312 statement.execute(); 1313 1314 assert(db.lastInsertRowid == 2); 1315 assert(db.changes == 1); 1316 assert(db.totalChanges == 2); 1317 1318 auto results = db.execute("SELECT * FROM test"); 1319 foreach (row; results) 1320 assert(row.peek!int(0) == 42); 1321 } 1322 1323 unittest // Multiple parameters binding 1324 { 1325 auto db = Database(":memory:"); 1326 db.execute("CREATE TABLE test (i INTEGER, f FLOAT, t TEXT)"); 1327 auto statement = db.prepare("INSERT INTO test (i, f, t) VALUES (:i, @f, $t)"); 1328 assert(statement.parameterCount == 3); 1329 statement.bind("$t", "TEXT"); 1330 statement.bind(":i", 42); 1331 statement.bind("@f", 3.14); 1332 statement.execute(); 1333 statement.reset(); 1334 statement.bind(1, 42); 1335 statement.bind(2, 3.14); 1336 statement.bind(3, "TEXT"); 1337 statement.execute(); 1338 1339 auto results = db.execute("SELECT * FROM test"); 1340 foreach (row; results) 1341 { 1342 assert(row.length == 3); 1343 assert(row.peek!int("i") == 42); 1344 assert(row.peek!double("f") == 3.14); 1345 assert(row.peek!string("t") == "TEXT"); 1346 } 1347 } 1348 1349 unittest // Multiple parameters binding: tuples 1350 { 1351 auto db = Database(":memory:"); 1352 db.execute("CREATE TABLE test (i INTEGER, f FLOAT, t TEXT)"); 1353 auto statement = db.prepare("INSERT INTO test (i, f, t) VALUES (?, ?, ?)"); 1354 statement.bindAll(42, 3.14, "TEXT"); 1355 statement.execute(); 1356 1357 auto results = db.execute("SELECT * FROM test"); 1358 foreach (row; results) 1359 { 1360 assert(row.length == 3); 1361 assert(row.peek!int(0) == 42); 1362 assert(row.peek!double(1) == 3.14); 1363 assert(row.peek!string(2) == "TEXT"); 1364 } 1365 } 1366 1367 unittest // Static array binding 1368 { 1369 ubyte[3] data = [1,2,3]; 1370 1371 auto db = Database(":memory:"); 1372 db.execute("CREATE TABLE test (a BLOB)"); 1373 auto statement = db.prepare("INSERT INTO test (a) VALUES (?)"); 1374 statement.bind(1, data); 1375 statement.execute(); 1376 1377 auto results = db.execute("SELECT * FROM test"); 1378 foreach (row; results) 1379 { 1380 assert(row.length == 1); 1381 auto rdata = row.peek!(ubyte[])(0); 1382 assert(rdata.length == 3); 1383 assert(rdata[0] == 1); 1384 assert(rdata[1] == 2); 1385 assert(rdata[2] == 3); 1386 } 1387 } 1388 1389 unittest // Nullable binding 1390 { 1391 auto db = Database(":memory:"); 1392 db.execute("CREATE TABLE test (a, b, c, d, e);"); 1393 1394 auto statement = db.prepare("INSERT INTO test (a,b,c,d,e) VALUES (?,?,?,?,?)"); 1395 statement.bind(1, Nullable!int(123)); 1396 statement.bind(2, Nullable!int()); 1397 statement.bind(3, Nullable!(uint, 0)(42)); 1398 statement.bind(4, Nullable!(uint, 0)()); 1399 statement.bind(5, Nullable!bool(false)); 1400 statement.execute(); 1401 1402 auto results = db.execute("SELECT * FROM test"); 1403 foreach (row; results) 1404 { 1405 assert(row.length == 5); 1406 assert(row.peek!int(0) == 123); 1407 assert(row.columnType(1) == SqliteType.NULL); 1408 assert(row.peek!int(2) == 42); 1409 assert(row.columnType(3) == SqliteType.NULL); 1410 assert(!row.peek!bool(4)); 1411 } 1412 } 1413 1414 unittest // Nullable peek 1415 { 1416 auto db = Database(":memory:"); 1417 auto results = db.execute("SELECT 1, NULL, 8.5, NULL"); 1418 foreach (row; results) 1419 { 1420 assert(row.length == 4); 1421 assert(row.peek!(Nullable!double)(2).get == 8.5); 1422 assert(row.peek!(Nullable!double)(3).isNull); 1423 assert(row.peek!(Nullable!(int, 0))(0).get == 1); 1424 assert(row.peek!(Nullable!(int, 0))(1).isNull); 1425 } 1426 } 1427 1428 unittest // GC anchoring test 1429 { 1430 auto db = Database(":memory:"); 1431 auto stmt = db.prepare("SELECT ?"); 1432 1433 auto str = ("I am test string").dup; 1434 stmt.bind(1, str); 1435 str = null; 1436 1437 for(int i=0; i<3; i++) { 1438 GC.collect(); 1439 GC.minimize(); 1440 } 1441 1442 ResultRange results = stmt.execute(); 1443 foreach(row; results) { 1444 assert(row.length == 1); 1445 assert(row.peek!string(0) == "I am test string"); 1446 } 1447 } 1448 1449 1450 /++ 1451 An input range interface to access the results of the execution of a statement. 1452 1453 The elements of the range are $(D Row) structs. A $(D Row) is just a view of the current 1454 row when iterating the results of a $(D ResultRange). It becomes invalid as soon as $(D 1455 ResultRange.popFront()) is called (it contains undefined data afterwards). Use $(D 1456 RowCache) to store the content of rows past the execution of the statement. 1457 1458 Instances of this struct are typically returned by $(D Database.execute()) or $(D 1459 Statement.execute()). 1460 +/ 1461 struct ResultRange 1462 { 1463 private: 1464 struct _Payload 1465 { 1466 Statement statement; 1467 int state; 1468 1469 @disable this(this); 1470 void opAssign(_Payload) { assert(false); } 1471 } 1472 alias Payload = RefCounted!(_Payload, RefCountedAutoInitialize.no); 1473 Payload p; 1474 1475 this(Statement statement) 1476 { 1477 p = Payload(statement); 1478 if (!p.statement.empty) 1479 p.state = sqlite3_step(p.statement.handle); 1480 else 1481 p.state = SQLITE_DONE; 1482 1483 enforce(p.state == SQLITE_ROW || p.state == SQLITE_DONE, 1484 new SqliteException(errmsg(p.statement.handle), p.state)); 1485 } 1486 1487 public: 1488 /++ 1489 Range primitives. 1490 +/ 1491 bool empty() @property 1492 { 1493 assert(p.state); 1494 return p.state == SQLITE_DONE; 1495 } 1496 1497 /// ditto 1498 Row front() @property 1499 { 1500 assert(p.state); 1501 enforce(!empty, new SqliteException("No rows available")); 1502 return Row(p.statement.handle); 1503 } 1504 1505 /// ditto 1506 void popFront() 1507 { 1508 assert(p.state); 1509 enforce(!empty, new SqliteException("No rows available")); 1510 p.state = sqlite3_step(p.statement.handle); 1511 enforce(p.state == SQLITE_DONE || p.state == SQLITE_ROW, 1512 new SqliteException(errmsg(p.statement.handle), p.state)); 1513 } 1514 1515 /++ 1516 Gets only the first value of the first row returned by the execution of the statement. 1517 +/ 1518 auto oneValue(T)() 1519 { 1520 return front.peek!T(0); 1521 } 1522 /// 1523 unittest // One value 1524 { 1525 auto db = Database(":memory:"); 1526 db.execute("CREATE TABLE test (val INTEGER)"); 1527 auto count = db.execute("SELECT count(*) FROM test").oneValue!long; 1528 assert(count == 0); 1529 } 1530 } 1531 1532 unittest // Refcount tests 1533 { 1534 auto db = Database(":memory:"); 1535 { 1536 db.execute("CREATE TABLE test (val INTEGER)"); 1537 auto tmp = db.prepare("INSERT INTO test (val) VALUES (?)"); 1538 tmp.bind(1, 42); 1539 tmp.execute(); 1540 } 1541 1542 auto results = { return db.execute("SELECT * FROM test"); }(); 1543 assert(!results.empty); 1544 assert(results.oneValue!int == 42); 1545 results.popFront(); 1546 assert(results.empty); 1547 } 1548 1549 1550 /++ 1551 A SQLite row, implemented as a random-access range of ColumnData. 1552 +/ 1553 struct Row 1554 { 1555 private 1556 { 1557 sqlite3_stmt* statement; 1558 int frontIndex; 1559 int backIndex; 1560 } 1561 1562 this(sqlite3_stmt* statement) nothrow 1563 { 1564 assert(statement); 1565 this.statement = statement; 1566 backIndex = sqlite3_column_count(statement) - 1; 1567 } 1568 1569 /// Range interface. 1570 bool empty() @property @safe pure nothrow 1571 { 1572 return length == 0; 1573 } 1574 1575 /// ditto 1576 ColumnData front() @property 1577 { 1578 return opIndex(0); 1579 } 1580 1581 /// ditto 1582 void popFront() @safe pure nothrow 1583 { 1584 frontIndex++; 1585 } 1586 1587 /// ditto 1588 Row save() @property @safe pure nothrow 1589 { 1590 Row ret; 1591 ret.statement = statement; 1592 ret.frontIndex = frontIndex; 1593 ret.backIndex = backIndex; 1594 return ret; 1595 } 1596 1597 /// ditto 1598 ColumnData back() @property 1599 { 1600 return opIndex(backIndex - frontIndex); 1601 } 1602 1603 /// ditto 1604 void popBack() @safe pure nothrow 1605 { 1606 backIndex--; 1607 } 1608 1609 /// ditto 1610 int length() @property @safe pure nothrow 1611 { 1612 return backIndex - frontIndex + 1; 1613 } 1614 1615 /// ditto 1616 ColumnData opIndex(int index) 1617 { 1618 auto i = internalIndex(index); 1619 enforce(i >= 0 && i <= backIndex, 1620 new SqliteException(format("invalid column index: %d", i))); 1621 1622 auto type = sqlite3_column_type(statement, i); 1623 1624 final switch (type) 1625 { 1626 case SqliteType.INTEGER: 1627 auto data = peek!(Nullable!long)(index); 1628 if (data.isNull) 1629 return ColumnData.init; 1630 return ColumnData(Variant(data.get)); 1631 1632 case SqliteType.FLOAT: 1633 auto data = peek!(Nullable!double)(index); 1634 if (data.isNull) 1635 return ColumnData.init; 1636 return ColumnData(Variant(data.get)); 1637 1638 case SqliteType.TEXT: 1639 auto data = peek!(Nullable!string)(index); 1640 if (data.isNull) 1641 return ColumnData.init; 1642 return ColumnData(Variant(data.get)); 1643 1644 case SqliteType.BLOB: 1645 auto data = peek!(Nullable!(ubyte[]))(index); 1646 if (data.isNull) 1647 return ColumnData.init; 1648 return ColumnData(Variant(data.get)); 1649 1650 case SqliteType.NULL: 1651 return ColumnData.init; 1652 } 1653 } 1654 1655 /++ 1656 Returns the data of a column as a $(D ColumnData). 1657 1658 Params: 1659 columnName = The name of the column, as specified in the prepared statement with an AS 1660 clause. 1661 +/ 1662 ColumnData opIndex(string columnName) 1663 { 1664 return opIndex(indexForName(columnName)); 1665 } 1666 1667 /++ 1668 Returns the data of a column. 1669 1670 Contrary to $(D opIndex), the $(D peek) functions return the data directly, 1671 automatically cast to T, without the overhead of using a wrapped $(D Variant) 1672 ($(D ColumnData)). 1673 1674 Params: 1675 T = The type of the returned data. T must be a boolean, a built-in numeric type, a 1676 string, an array or a Variant. 1677 1678 index = The index of the column in the prepared statement or 1679 the name of the column, as specified in the prepared statement 1680 with an AS clause. 1681 1682 Returns: 1683 A value of type T. The returned value is T.init if the data type is NULL. 1684 In all other cases, the data is fetched from SQLite (which returns a value 1685 depending on its own conversion rules; 1686 see $(LINK http://www.sqlite.org/c3ref/column_blob.html) and 1687 $(LINK http://www.sqlite.org/lang_expr.html#castexpr)), and it is converted 1688 to T using $(D std.conv.to!T). 1689 1690 $(TABLE 1691 $(TR $(TH Condition on T) 1692 $(TH Requested database type)) 1693 $(TR $(TD isIntegral!T || isBoolean!T) 1694 $(TD INTEGER, via $(D sqlite3_column_int64))) 1695 $(TR $(TD isFloatingPoint!T) 1696 $(TD FLOAT, via $(D sqlite3_column_double))) 1697 $(TR $(TD isSomeString!T) 1698 $(TD TEXT, via $(D sqlite3_column_text))) 1699 $(TR $(TD isArray!T) 1700 $(TD BLOB, via $(D sqlite3_column_blob))) 1701 $(TR $(TD is(T == Nullable!U)) 1702 $(TD NULL or T)) 1703 $(TR $(TD Other types) 1704 $(TD Compilation error)) 1705 ) 1706 1707 Warnings: 1708 The result is undefined if then index is out of range. 1709 1710 If the second overload is used, the names of all the columns are 1711 tested each time this function is called: use 1712 numeric indexing for better performance. 1713 +/ 1714 T peek(T)(int index) 1715 if (isBoolean!T || isIntegral!T) 1716 { 1717 return sqlite3_column_int64(statement, internalIndex(index)).to!T(); 1718 } 1719 1720 /// ditto 1721 T peek(T)(int index) 1722 if (isFloatingPoint!T) 1723 { 1724 auto i = internalIndex(index); 1725 if (sqlite3_column_type(statement, i) == SqliteType.NULL) 1726 return T.init; 1727 return sqlite3_column_double(statement, i).to!T(); 1728 } 1729 1730 /// ditto 1731 T peek(T)(int index) 1732 if (isSomeString!T) 1733 { 1734 return sqlite3_column_text(statement, internalIndex(index)).to!T; 1735 } 1736 1737 /// ditto 1738 T peek(T)(int index) 1739 if (isArray!T && !isSomeString!T) 1740 { 1741 auto i = internalIndex(index); 1742 auto ptr = sqlite3_column_blob(statement, i); 1743 auto length = sqlite3_column_bytes(statement, i); 1744 ubyte[] blob; 1745 blob.length = length; 1746 memcpy(blob.ptr, ptr, length); 1747 return blob.to!T; 1748 } 1749 1750 /// ditto 1751 T peekNoDup(T)(int index) 1752 if (isArray!T && !isSomeString!T) 1753 { 1754 auto i = internalIndex(index); 1755 auto ptr = sqlite3_column_blob(statement, i); 1756 auto length = sqlite3_column_bytes(statement, i); 1757 return cast(T)ptr[0..length]; 1758 } 1759 1760 /// ditto 1761 T peek(T : Nullable!U, U...)(int index) 1762 { 1763 if (sqlite3_column_type(statement, internalIndex(index)) == SqliteType.NULL) 1764 return T(); 1765 return T(peek!(U[0])(index)); 1766 } 1767 1768 /// ditto 1769 T peek(T)(string columnName) 1770 { 1771 return peek!T(indexForName(columnName)); 1772 } 1773 1774 /++ 1775 Determines the type of a particular result column in SELECT statement. 1776 1777 See_Also: $(D http://www.sqlite.org/c3ref/column_database_name.html). 1778 +/ 1779 SqliteType columnType(int index) 1780 { 1781 return cast(SqliteType) sqlite3_column_type(statement, internalIndex(index)); 1782 } 1783 /// Ditto 1784 SqliteType columnType(string columnName) 1785 { 1786 return columnType(indexForName(columnName)); 1787 } 1788 1789 version (SQLITE_ENABLE_COLUMN_METADATA) 1790 { 1791 /++ 1792 Determines the name of the database, table, or column that is the origin of a 1793 particular result column in SELECT statement. 1794 1795 See_Also: $(D http://www.sqlite.org/c3ref/column_database_name.html). 1796 +/ 1797 string columnDatabaseName(int index) 1798 { 1799 return sqlite3_column_database_name(statement, internalIndex(index)).to!string; 1800 } 1801 /// Ditto 1802 string columnDatabaseName(string columnName) 1803 { 1804 return columnDatabaseName(indexForName(columnName)); 1805 } 1806 /// Ditto 1807 string columnTableName(int index) 1808 { 1809 return sqlite3_column_database_name(statement, internalIndex(index)).to!string; 1810 } 1811 /// Ditto 1812 string columnTableName(string columnName) 1813 { 1814 return columnTableName(indexForName(columnName)); 1815 } 1816 /// Ditto 1817 string columnOriginName(int index) 1818 { 1819 return sqlite3_column_origin_name(statement, internalIndex(index)).to!string; 1820 } 1821 /// Ditto 1822 string columnOriginName(string columnName) 1823 { 1824 return columnOriginName(indexForName(columnName)); 1825 } 1826 } 1827 1828 /++ 1829 Determines the declared type name of a particular result column in SELECT statement. 1830 1831 See_Also: $(D http://www.sqlite.org/c3ref/column_database_name.html). 1832 +/ 1833 string columnDeclaredTypeName(int index) 1834 { 1835 return sqlite3_column_decltype(statement, internalIndex(index)).to!string; 1836 } 1837 /// Ditto 1838 string columnDeclaredTypeName(string columnName) 1839 { 1840 return columnDeclaredTypeName(indexForName(columnName)); 1841 } 1842 1843 private: 1844 int internalIndex(int index) 1845 { 1846 return index + frontIndex; 1847 } 1848 1849 int indexForName(string name) 1850 { 1851 foreach (i; frontIndex .. backIndex + 1) 1852 if (sqlite3_column_name(statement, i).to!string == name) 1853 return i; 1854 1855 throw new SqliteException("invalid column name: '%s'".format(name)); 1856 } 1857 } 1858 1859 version (unittest) 1860 { 1861 static assert(isRandomAccessRange!Row); 1862 static assert(is(ElementType!Row == ColumnData)); 1863 } 1864 1865 unittest // Peek 1866 { 1867 auto db = Database(":memory:"); 1868 db.execute("CREATE TABLE test (value)"); 1869 auto statement = db.prepare("INSERT INTO test VALUES(?)"); 1870 statement.inject(null); 1871 statement.inject(42); 1872 statement.inject(3.14); 1873 statement.inject("ABC"); 1874 auto blob = cast(ubyte[]) x"DEADBEEF"; 1875 statement.inject(blob); 1876 1877 import std.math : isNaN; 1878 auto results = db.execute("SELECT * FROM test"); 1879 auto row = results.front; 1880 assert(row.peek!long(0) == 0); 1881 assert(row.peek!double(0).isNaN); 1882 assert(row.peek!string(0) is null); 1883 assert(row.peek!(ubyte[])(0) is null); 1884 results.popFront(); 1885 row = results.front; 1886 assert(row.peek!long(0) == 42); 1887 assert(row.peek!double(0) == 42); 1888 assert(row.peek!string(0) == "42"); 1889 assert(row.peek!(ubyte[])(0) == cast(ubyte[]) "42"); 1890 results.popFront(); 1891 row = results.front; 1892 assert(row.peek!long(0) == 3); 1893 assert(row.peek!double(0) == 3.14); 1894 assert(row.peek!string(0) == "3.14"); 1895 assert(row.peek!(ubyte[])(0) == cast(ubyte[]) "3.14"); 1896 results.popFront(); 1897 row = results.front; 1898 assert(row.peek!long(0) == 0); 1899 assert(row.peek!double(0) == 0.0); 1900 assert(row.peek!string(0) == "ABC"); 1901 assert(row.peek!(ubyte[])(0) == cast(ubyte[]) "ABC"); 1902 results.popFront(); 1903 row = results.front; 1904 assert(row.peek!long(0) == 0); 1905 assert(row.peek!double(0) == 0.0); 1906 assert(row.peek!string(0) == x"DEADBEEF"); 1907 assert(row.peek!(ubyte[])(0) == cast(ubyte[]) x"DEADBEEF"); 1908 } 1909 1910 unittest // Row random-access range interface 1911 { 1912 auto db = Database(":memory:"); 1913 1914 { 1915 db.execute("CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER, d INTEGER)"); 1916 auto statement = db.prepare("INSERT INTO test VALUES(?, ?, ?, ?)"); 1917 statement.bind(1, 1); 1918 statement.bind(2, 2); 1919 statement.bind(3, 3); 1920 statement.bind(4, 4); 1921 statement.execute(); 1922 statement.reset(); 1923 statement.bind(1, 5); 1924 statement.bind(2, 6); 1925 statement.bind(3, 7); 1926 statement.bind(4, 8); 1927 statement.execute(); 1928 } 1929 1930 { 1931 auto results = db.execute("SELECT * FROM test"); 1932 auto values = [1, 2, 3, 4, 5, 6, 7, 8]; 1933 foreach (row; results) 1934 { 1935 while (!row.empty) 1936 { 1937 assert(row.front.as!int == values.front); 1938 row.popFront(); 1939 values.popFront(); 1940 } 1941 } 1942 } 1943 1944 { 1945 auto results = db.execute("SELECT * FROM test"); 1946 auto values = [4, 3, 2, 1, 8, 7, 6, 5]; 1947 foreach (row; results) 1948 { 1949 while (!row.empty) 1950 { 1951 assert(row.back.as!int == values.front); 1952 row.popBack(); 1953 values.popFront(); 1954 } 1955 } 1956 } 1957 1958 auto results = { return db.execute("SELECT * FROM test"); }(); 1959 auto values = [1, 2, 3, 4, 5, 6, 7, 8]; 1960 foreach (row; results) 1961 { 1962 while (!row.empty) 1963 { 1964 assert(row.front.as!int == values.front); 1965 row.popFront(); 1966 values.popFront(); 1967 } 1968 } 1969 } 1970 1971 1972 /++ 1973 The data retrived from a column, stored internally as a $(D Variant). 1974 +/ 1975 struct ColumnData 1976 { 1977 private Variant variant; 1978 1979 /++ 1980 Returns the data converted to T. If the data is NULL, defaultValue is 1981 returned. 1982 +/ 1983 auto as(T)(T defaultValue = T.init) 1984 if (isBoolean!T || isNumeric!T || isSomeChar!T || isSomeString!T) 1985 { 1986 if (!variant.hasValue) 1987 return defaultValue; 1988 1989 static if (isBoolean!T) 1990 return variant.get!T; // Work around bug in 2.065 1991 else 1992 return variant.coerce!T; 1993 } 1994 1995 /// ditto 1996 auto as(T)(T defaultValue = T.init) 1997 if (isArray!T && !isSomeString!T) 1998 { 1999 if (!variant.hasValue) 2000 return defaultValue; 2001 2002 return cast(T) variant.get!(ubyte[]); 2003 } 2004 2005 /// ditto 2006 auto as(T : Nullable!U, U...)(T defaultValue = T.init) 2007 { 2008 if (!variant.hasValue) 2009 return defaultValue; 2010 2011 return T(as!U()); 2012 } 2013 2014 void toString(scope void delegate(const(char)[]) sink) 2015 { 2016 if (variant.hasValue) 2017 sink(variant.toString); 2018 else 2019 sink("NULL"); 2020 } 2021 } 2022 2023 2024 unittest // Getting integral values 2025 { 2026 auto db = Database(":memory:"); 2027 db.execute("CREATE TABLE test (val INTEGER)"); 2028 2029 auto statement = db.prepare("INSERT INTO test (val) VALUES (?)"); 2030 statement.bind(1, cast(byte) 42); 2031 statement.execute(); 2032 statement.reset(); 2033 statement.bind(1, 42U); 2034 statement.execute(); 2035 statement.reset(); 2036 statement.bind(1, 42UL); 2037 statement.execute(); 2038 statement.reset(); 2039 statement.bind(1, '\x2A'); 2040 statement.execute(); 2041 2042 auto results = db.execute("SELECT * FROM test"); 2043 foreach (row; results) 2044 assert(row.peek!long(0) == 42); 2045 } 2046 2047 unittest // Getting floating point values 2048 { 2049 auto db = Database(":memory:"); 2050 db.execute("CREATE TABLE test (val FLOAT)"); 2051 2052 auto statement = db.prepare("INSERT INTO test (val) VALUES (?)"); 2053 statement.bind(1, 42.0F); 2054 statement.execute(); 2055 statement.reset(); 2056 statement.bind(1, 42.0); 2057 statement.execute(); 2058 statement.reset(); 2059 statement.bind(1, 42.0L); 2060 statement.execute(); 2061 statement.reset(); 2062 statement.bind(1, "42"); 2063 statement.execute(); 2064 2065 auto results = db.execute("SELECT * FROM test"); 2066 foreach (row; results) 2067 assert(row.peek!double(0) == 42.0); 2068 } 2069 2070 unittest // Getting text values 2071 { 2072 auto db = Database(":memory:"); 2073 db.execute("CREATE TABLE test (val TEXT)"); 2074 2075 auto statement = db.prepare("INSERT INTO test (val) VALUES (?)"); 2076 statement.bind(1, "I am a text."); 2077 statement.execute(); 2078 2079 auto results = db.execute("SELECT * FROM test"); 2080 assert(results.front.peek!string(0) == "I am a text."); 2081 } 2082 2083 unittest // Getting blob values 2084 { 2085 auto db = Database(":memory:"); 2086 db.execute("CREATE TABLE test (val BLOB)"); 2087 2088 auto statement = db.prepare("INSERT INTO test (val) VALUES (?)"); 2089 ubyte[] array = [1, 2, 3]; 2090 statement.bind(1, array); 2091 statement.execute(); 2092 statement.reset; 2093 ubyte[3] sarray = [1, 2, 3]; 2094 statement.bind(1, sarray); 2095 statement.execute(); 2096 2097 auto results = db.execute("SELECT * FROM test"); 2098 foreach (row; results) 2099 assert(row.peek!(ubyte[])(0) == [1, 2, 3]); 2100 } 2101 2102 unittest // Getting null values 2103 { 2104 import std.math; 2105 2106 auto db = Database(":memory:"); 2107 db.execute("CREATE TABLE test (val TEXT)"); 2108 2109 auto statement = db.prepare("INSERT INTO test (val) VALUES (?)"); 2110 statement.bind(1, null); 2111 statement.execute(); 2112 2113 auto results = db.execute("SELECT * FROM test"); 2114 assert(results.front.peek!bool(0) == false); 2115 assert(results.front.peek!long(0) == 0); 2116 assert(results.front.peek!double(0).isNaN); 2117 assert(results.front.peek!string(0) is null); 2118 assert(results.front.peek!(ubyte[])(0) is null); 2119 assert(results.front[0].as!bool == false); 2120 assert(results.front[0].as!long == 0); 2121 assert(results.front[0].as!double.isNaN); 2122 assert(results.front[0].as!string is null); 2123 assert(results.front[0].as!(ubyte[]) is null); 2124 } 2125 2126 /// Information about a column. 2127 struct ColumnMetadata 2128 { 2129 string declaredTypeName; /// 2130 string collationSequenceName; /// 2131 bool isNotNull; /// 2132 bool isPrimaryKey; /// 2133 bool isAutoIncrement; /// 2134 } 2135 2136 2137 /++ 2138 Caches all the results of a $(D Statement) in memory as $(D ColumnData). 2139 2140 Allows to iterate on the rows and their columns with an array-like interface. The rows can 2141 be viewed as an array of $(D ColumnData) or as an associative array of $(D ColumnData) 2142 indexed by the column names. 2143 +/ 2144 struct RowCache 2145 { 2146 struct CachedRow 2147 { 2148 ColumnData[] columns; 2149 alias columns this; 2150 2151 int[string] columnIndexes; 2152 2153 private this(Row row, int[string] columnIndexes) 2154 { 2155 this.columnIndexes = columnIndexes; 2156 2157 auto colapp = appender!(ColumnData[]); 2158 foreach (i; 0 .. row.length) 2159 colapp.put(row[i]); 2160 columns = colapp.data; 2161 } 2162 2163 ColumnData opIndex(int index) 2164 { 2165 return columns[index]; 2166 } 2167 2168 ColumnData opIndex(string name) 2169 { 2170 auto index = name in columnIndexes; 2171 enforce(index, new SqliteException("Unknown column name: %s".format(name))); 2172 return columns[*index]; 2173 } 2174 } 2175 2176 CachedRow[] rows; 2177 alias rows this; 2178 2179 private int[string] columnIndexes; 2180 2181 /++ 2182 Creates and populates the cache from the results of the statement. 2183 +/ 2184 this(ResultRange results) 2185 { 2186 if (!results.empty) 2187 { 2188 auto first = results.front; 2189 foreach (i; 0 .. first.length) 2190 { 2191 auto name = sqlite3_column_name(first.statement, i).to!string; 2192 columnIndexes[name] = i; 2193 } 2194 } 2195 2196 auto rowapp = appender!(CachedRow[]); 2197 while (!results.empty) 2198 { 2199 rowapp.put(CachedRow(results.front, columnIndexes)); 2200 results.popFront(); 2201 } 2202 rows = rowapp.data; 2203 } 2204 } 2205 /// 2206 unittest 2207 { 2208 auto db = Database(":memory:"); 2209 db.execute("CREATE TABLE test (msg TEXT, num FLOAT)"); 2210 2211 auto statement = db.prepare("INSERT INTO test (msg, num) VALUES (?1, ?2)"); 2212 statement.bind(1, "ABC"); 2213 statement.bind(2, 123); 2214 statement.execute(); 2215 statement.reset(); 2216 statement.bind(1, "DEF"); 2217 statement.bind(2, 456); 2218 statement.execute(); 2219 2220 auto results = db.execute("SELECT * FROM test"); 2221 auto data = RowCache(results); 2222 assert(data.length == 2); 2223 assert(data[0].front.as!string == "ABC"); 2224 assert(data[0][1].as!int == 123); 2225 assert(data[1]["msg"].as!string == "DEF"); 2226 assert(data[1]["num"].as!int == 456); 2227 } 2228 2229 unittest // RowCache copies 2230 { 2231 auto db = Database(":memory:"); 2232 db.execute("CREATE TABLE test (msg TEXT)"); 2233 auto statement = db.prepare("INSERT INTO test (msg) VALUES (?)"); 2234 statement.bind(1, "ABC"); 2235 statement.execute(); 2236 2237 static getdata(Database db) 2238 { 2239 return RowCache(db.execute("SELECT * FROM test")); 2240 } 2241 2242 auto data = getdata(db); 2243 assert(data.length == 1); 2244 assert(data[0][0].as!string == "ABC"); 2245 } 2246 2247 2248 /++ 2249 Turns $(D_PARAM value) into a _literal that can be used in an SQLite expression. 2250 +/ 2251 string literal(T)(T value) 2252 { 2253 static if (is(T == typeof(null))) 2254 return "NULL"; 2255 else static if (isBoolean!T) 2256 return value ? "1" : "0"; 2257 else static if (isNumeric!T) 2258 return value.to!string(); 2259 else static if (isSomeString!T) 2260 return format("'%s'", value.replace("'", "''")); 2261 else static if (isArray!T) 2262 return "'X%(%X%)'".format(cast(ubyte[]) value); 2263 else 2264 static assert(false, "cannot make a literal of a value of type " ~ T.stringof); 2265 } 2266 /// 2267 unittest 2268 { 2269 assert(null.literal == "NULL"); 2270 assert(false.literal == "0"); 2271 assert(true.literal == "1"); 2272 assert(4.literal == "4"); 2273 assert(4.1.literal == "4.1"); 2274 assert("foo".literal == "'foo'"); 2275 assert("a'b'".literal == "'a''b'''"); 2276 auto a = cast(ubyte[]) x"DEADBEEF"; 2277 assert(a.literal == "'XDEADBEEF'"); 2278 } 2279 2280 /++ 2281 Exception thrown when SQLite functions return an error. 2282 +/ 2283 class SqliteException : Exception 2284 { 2285 /++ 2286 The _code of the error that raised the exception, or 0 if this _code is not known. 2287 +/ 2288 int code; 2289 2290 /++ 2291 The SQL code that raised the exception, if applicable. 2292 +/ 2293 string sql; 2294 2295 private this(string msg, string sql, int code, 2296 string file = __FILE__, size_t line = __LINE__, Throwable next = null) 2297 { 2298 this.sql = sql; 2299 this.code = code; 2300 super(msg, file, line, next); 2301 } 2302 2303 this(string msg, int code, string sql = null, 2304 string file = __FILE__, size_t line = __LINE__, Throwable next = null) 2305 { 2306 this("error %d: %s".format(code, msg), sql, code, file, line, next); 2307 } 2308 2309 this(string msg, string sql = null, 2310 string file = __FILE__, size_t line = __LINE__, Throwable next = null) 2311 { 2312 this(msg, sql, 0, file, line, next); 2313 } 2314 } 2315 2316 2317 private: 2318 2319 string errmsg(sqlite3* db) 2320 { 2321 return sqlite3_errmsg(db).to!string; 2322 } 2323 2324 string errmsg(sqlite3_stmt* stmt) 2325 { 2326 return errmsg(sqlite3_db_handle(stmt)); 2327 } 2328 2329 auto byStatement(string sql) 2330 { 2331 static struct ByStatement 2332 { 2333 string sql; 2334 size_t end; 2335 2336 this(string sql) 2337 { 2338 this.sql = sql; 2339 end = findEnd(); 2340 } 2341 2342 bool empty() 2343 { 2344 return !sql.length; 2345 } 2346 2347 string front() 2348 { 2349 return sql[0 .. end]; 2350 } 2351 2352 void popFront() 2353 { 2354 sql = sql[end .. $]; 2355 end = findEnd(); 2356 } 2357 2358 private: 2359 size_t findEnd() 2360 { 2361 size_t pos; 2362 bool complete; 2363 do 2364 { 2365 auto tail = sql[pos .. $]; 2366 auto offset = tail.countUntil(';') + 1; 2367 pos += offset; 2368 if (offset == 0) 2369 pos = sql.length; 2370 auto part = sql[0 .. pos]; 2371 complete = cast(bool) sqlite3_complete(part.toStringz); 2372 } 2373 while (!complete && pos < sql.length); 2374 return pos; 2375 } 2376 } 2377 2378 return ByStatement(sql); 2379 } 2380 unittest 2381 { 2382 auto sql = "CREATE TABLE test (dummy); 2383 CREATE TRIGGER trig INSERT ON test BEGIN SELECT 1; SELECT 'a;b'; END; 2384 SELECT 'c;d';; 2385 CREATE"; 2386 assert(equal(sql.byStatement.map!(s => s.strip), [ 2387 "CREATE TABLE test (dummy);", 2388 "CREATE TRIGGER trig INSERT ON test BEGIN SELECT 1; SELECT 'a;b'; END;", 2389 "SELECT 'c;d';", 2390 ";", 2391 "CREATE" 2392 ])); 2393 } 2394 2395 struct DelegateWrapper(T) 2396 { 2397 T dlg; 2398 } 2399 2400 void* delegateWrap(T)(T dlg) 2401 if (isCallable!T) 2402 { 2403 import std.functional; 2404 alias D = typeof(toDelegate(dlg)); 2405 auto d = cast(DelegateWrapper!D*) GC.malloc(DelegateWrapper!D.sizeof); 2406 GC.setAttr(d, GC.BlkAttr.NO_MOVE); 2407 d.dlg = toDelegate(dlg); 2408 return cast(void*) d; 2409 } 2410 2411 auto delegateUnwrap(T)(void* ptr) 2412 if (isCallable!T) 2413 { 2414 return (cast(DelegateWrapper!T*) ptr).dlg; 2415 } 2416 2417 extern(C) void ptrFree(void* ptr) 2418 { 2419 if (ptr) 2420 GC.free(ptr); 2421 } 2422 2423 2424 // Anchors and returns a pointer to D memory, so that it will not 2425 // be moved or collected. For use with releaseMem. 2426 void* anchorMem(void* ptr) 2427 { 2428 GC.addRoot(ptr); 2429 GC.setAttr(ptr, GC.BlkAttr.NO_MOVE); 2430 return ptr; 2431 } 2432 2433 // Passed to sqlite3_xxx_blob64/sqlite3_xxx_text64 to unanchor memory. 2434 extern(C) void releaseMem(void* ptr) 2435 { 2436 GC.setAttr(ptr, GC.BlkAttr.NO_MOVE); 2437 GC.removeRoot(ptr); 2438 } 2439 2440 // getValue and setResult function templates 2441 // used by createFunction and createAggregate 2442 2443 auto getValue(T)(sqlite3_value* argv) 2444 if (isBoolean!T) 2445 { 2446 return sqlite3_value_int64(argv) != 0; 2447 } 2448 2449 auto getValue(T)(sqlite3_value* argv) 2450 if (isIntegral!T) 2451 { 2452 return sqlite3_value_int64(argv).to!T; 2453 } 2454 2455 auto getValue(T)(sqlite3_value* argv) 2456 if (isFloatingPoint!T) 2457 { 2458 if (sqlite3_value_type(argv) == SqliteType.NULL) 2459 return double.nan; 2460 return sqlite3_value_double(argv).to!T; 2461 } 2462 2463 auto getValue(T)(sqlite3_value* argv) 2464 if (isSomeString!T) 2465 { 2466 return sqlite3_value_text(argv).to!T; 2467 } 2468 2469 auto getValue(T)(sqlite3_value* argv) 2470 if (isArray!T && !isSomeString!T) 2471 { 2472 auto n = sqlite3_value_bytes(argv); 2473 ubyte[] blob; 2474 blob.length = n; 2475 memcpy(blob.ptr, sqlite3_value_blob(argv), n); 2476 return blob.to!T; 2477 } 2478 2479 auto getValue(T : Nullable!U, U...)(sqlite3_value* argv) 2480 { 2481 if (sqlite3_value_type(argv) == SqliteType.NULL) 2482 return T(); 2483 return T(getValue!(U[0])(argv)); 2484 } 2485 2486 void setResult(T)(sqlite3_context* context, T value) 2487 if (isIntegral!T || isBoolean!T) 2488 { 2489 sqlite3_result_int64(context, value.to!long); 2490 } 2491 2492 void setResult(T)(sqlite3_context* context, T value) 2493 if (isFloatingPoint!T) 2494 { 2495 sqlite3_result_double(context, value.to!double); 2496 } 2497 2498 void setResult(T)(sqlite3_context* context, T value) 2499 if (isSomeString!T) 2500 { 2501 auto val = value.to!string; 2502 sqlite3_result_text64(context, cast(const(char)*) anchorMem(cast(void*) val.ptr), val.length, &releaseMem, SQLITE_UTF8); 2503 } 2504 2505 void setResult(T)(sqlite3_context* context, T value) 2506 if (isDynamicArray!T && !isSomeString!T) 2507 { 2508 auto val = cast(void[]) value; 2509 sqlite3_result_blob64(context, anchorMem(val.ptr), val.length, &releaseMem); 2510 } 2511 2512 void setResult(T)(sqlite3_context* context, T value) 2513 if (isStaticArray!T) 2514 { 2515 auto val = cast(void[]) value; 2516 sqlite3_result_blob64(context, val.ptr, val.sizeof, SQLITE_TRANSIENT); 2517 } 2518 2519 void setResult(T : Nullable!U, U...)(sqlite3_context* context, T value) 2520 { 2521 if (value.isNull) 2522 sqlite3_result_null(context); 2523 else 2524 setResult(context, value.get); 2525 }