1 /** 2 SQLite implementation of the [arsd.database.Database] interface. 3 4 Compile with version=sqlite_extended_metadata_available 5 if your sqlite is compiled with the 6 SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol. 7 8 If you enable that, you get the ability to use the 9 queryDataObject() function with sqlite. (You can still 10 use DataObjects, but you'll have to set up the mappings 11 manually without the extended metadata.) 12 */ 13 module arsd.sqlite; 14 version(static_sqlite) {} else 15 pragma(lib, "sqlite3"); 16 version(linux) 17 pragma(lib, "dl"); // apparently sqlite3 depends on this 18 public import arsd.database; 19 20 import std.exception; 21 22 import std.string; 23 24 import core.stdc.stdlib; 25 import core.exception; 26 import core.memory; 27 import std.file; 28 import std.conv; 29 /* 30 NOTE: 31 32 This only works correctly on INSERTs if the user can grow the 33 database file! This means he must have permission to write to 34 both the file and the directory it is in. 35 36 */ 37 38 39 /** 40 The Database interface provides a consistent and safe way to access sql RDBMSs. 41 42 Why are all the classes scope? To ensure the database connection is closed when you are done with it. 43 The destructor cleans everything up. 44 45 (maybe including rolling back a transaction if one is going and it errors.... maybe, or that could bne 46 scope(exit)) 47 */ 48 49 Sqlite openDBAndCreateIfNotPresent(string filename, string sql, scope void delegate(Sqlite db) initialize = null){ 50 if(exists(filename)) 51 return new Sqlite(filename); 52 else { 53 auto db = new Sqlite(filename); 54 db.exec(sql); 55 if(initialize !is null) 56 initialize(db); 57 return db; 58 } 59 } 60 61 /* 62 import std.stdio; 63 void main() { 64 Database db = new Sqlite("test.sqlite.db"); 65 66 db.query("CREATE TABLE users (id integer, name text)"); 67 68 db.query("INSERT INTO users values (?, ?)", 1, "hello"); 69 70 foreach(line; db.query("SELECT * FROM users")) { 71 writefln("%s %s", line[0], line["name"]); 72 } 73 } 74 */ 75 76 /// 77 class Sqlite : Database { 78 public: 79 /++ 80 Opens and creates the database, if desired. 81 82 History: 83 The `flags` argument was ignored until July 29, 2022. (This function was originally written over 11 years ago, when sqlite3_open_v2 was not commonly supported on some distributions yet, and I didn't notice to revisit it for ages!) 84 +/ 85 this(string filename, int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE) { 86 int error = sqlite3_open_v2(toStringz(filename), &db, flags, null); 87 if(error != SQLITE_OK) 88 throw new DatabaseException(this.error()); 89 /+ 90 int error = sqlite3_open(toStringz(filename), &db); 91 if(error != SQLITE_OK) 92 throw new DatabaseException(this.error()); 93 +/ 94 } 95 96 ~this(){ 97 if(sqlite3_close(db) != SQLITE_OK) 98 throw new DatabaseException(error()); 99 } 100 101 string sysTimeToValue(SysTime s) { 102 return "datetime('" ~ escape(s.toISOExtString()) ~ "')"; 103 } 104 105 // my extension for easier editing 106 version(sqlite_extended_metadata_available) { 107 ResultByDataObject queryDataObject(T...)(string sql, T t) { 108 // modify sql for the best data object grabbing 109 sql = fixupSqlForDataObjectUse(sql); 110 111 auto s = Statement(this, sql); 112 foreach(i, arg; t) { 113 s.bind(i + 1, arg); 114 } 115 116 auto magic = s.execute(true); // fetch extended metadata 117 118 return ResultByDataObject(cast(SqliteResult) magic, magic.extendedMetadata, this); 119 } 120 } 121 122 /// 123 override void startTransaction() { 124 query("BEGIN TRANSACTION"); 125 } 126 127 override ResultSet queryImpl(string sql, Variant[] args...) { 128 auto s = Statement(this, sql); 129 foreach(i, arg; args) { 130 s.bind(cast(int) i + 1, arg); 131 } 132 return s.execute(); 133 } 134 135 override string escape(string sql) { 136 if(sql is null) 137 return null; 138 char* got = sqlite3_mprintf("%q", toStringz(sql)); // FIXME: might have to be %Q, need to check this, but I think the other impls do the same as %q 139 auto orig = got; 140 string esc; 141 while(*got) { 142 esc ~= (*got); 143 got++; 144 } 145 146 sqlite3_free(orig); 147 148 return esc; 149 } 150 151 string escapeBinaryString(const(ubyte)[] b) { 152 return tohexsql(b); 153 } 154 155 string error(){ 156 import core.stdc.string : strlen; 157 char* mesg = sqlite3_errmsg(db); 158 char[] m; 159 sizediff_t a = strlen(mesg); 160 m.length = a; 161 for(int v = 0; v < a; v++) 162 m[v] = mesg[v]; 163 164 return assumeUnique(m); 165 } 166 167 /// 168 int affectedRows(){ 169 return sqlite3_changes(db); 170 } 171 172 /// 173 int lastInsertId(){ 174 return cast(int) sqlite3_last_insert_rowid(db); 175 } 176 177 178 int exec(string sql, void delegate (char[][char[]]) onEach = null) { 179 char* mesg; 180 if(sqlite3_exec(db, toStringz(sql), &callback, &onEach, &mesg) != SQLITE_OK) { 181 import core.stdc.string : strlen; 182 char[] m; 183 sizediff_t a = strlen(mesg); 184 m.length = a; 185 for(int v = 0; v < a; v++) 186 m[v] = mesg[v]; 187 188 sqlite3_free(mesg); 189 throw new DatabaseException("exec " ~ m.idup); 190 } 191 192 return 0; 193 } 194 /* 195 Statement prepare(string sql){ 196 sqlite3_stmt * s; 197 if(sqlite3_prepare_v2(db, toStringz(sql), cast(int) sql.length, &s, null) != SQLITE_OK) 198 throw new DatabaseException("prepare " ~ error()); 199 200 Statement a = new Statement(s); 201 202 return a; 203 } 204 */ 205 private: 206 sqlite3* db; 207 } 208 209 210 211 212 213 214 class SqliteResult : ResultSet { 215 int getFieldIndex(string field) { 216 foreach(i, n; columnNames) 217 if(n == field) 218 return cast(int) i; 219 throw new Exception("no such field " ~ field); 220 } 221 222 string[] fieldNames() { 223 return columnNames; 224 } 225 226 // this is a range that can offer other ranges to access it 227 bool empty() { 228 return position == rows.length; 229 } 230 231 Row front() { 232 Row r; 233 234 r.resultSet = this; 235 if(rows.length <= position) 236 throw new Exception("Result is empty"); 237 foreach(c; rows[position]) { 238 if(auto t = c.peek!(immutable(byte)[])) 239 r.row ~= DatabaseDatum(cast(string) *t); 240 else if (auto d = c.peek!double) 241 // 17 significant decimal digits are enough to not lose precision (IEEE 754 section 5.12.2) 242 r.row ~= DatabaseDatum(format!"%.17s"(*d)); 243 else 244 r.row ~= DatabaseDatum(c.coerce!(string)); 245 } 246 247 return r; 248 } 249 250 void popFront() { 251 position++; 252 } 253 254 override size_t length() { 255 return rows.length; 256 } 257 258 this(Variant[][] rows, char[][] columnNames) { 259 this.rows = rows; 260 foreach(c; columnNames) 261 this.columnNames ~= c.idup; 262 } 263 264 private: 265 string[] columnNames; 266 Variant[][] rows; 267 int position = 0; 268 } 269 270 271 272 273 274 275 struct Statement { 276 private this(Sqlite db, sqlite3_stmt * S) { 277 this.db = db; 278 s = S; 279 finalized = false; 280 } 281 282 Sqlite db; 283 284 this(Sqlite db, string sql) { 285 // the arsd convention is zero based ?, but sqlite insists on one based. so this is stupid but still 286 if(sql.indexOf("?0") != -1) { 287 foreach_reverse(i; 0 .. 10) 288 sql = sql.replace("?" ~ to!string(i), "?" ~ to!string(i + 1)); 289 } 290 291 this.db = db; 292 if(sqlite3_prepare_v2(db.db, toStringz(sql), cast(int) sql.length, &s, null) != SQLITE_OK) 293 throw new DatabaseException(db.error() ~ " " ~ sql); 294 } 295 296 version(sqlite_extended_metadata_available) 297 Tuple!(string, string)[string] extendedMetadata; 298 299 ResultSet execute(bool fetchExtendedMetadata = false) { 300 bool first = true; 301 int count; 302 int numRows = 0; 303 int r = 0; 304 // FIXME: doesn't handle busy database 305 while( SQLITE_ROW == sqlite3_step(s) ){ 306 numRows++; 307 if(numRows >= rows.length) 308 rows.length = rows.length + 8; 309 310 if(first){ 311 count = sqlite3_column_count(s); 312 313 columnNames.length = count; 314 for(int a = 0; a < count; a++){ 315 import core.stdc.string : strlen; 316 char* str = sqlite3_column_name(s, a); 317 sizediff_t l = strlen(str); 318 columnNames[a].length = l; 319 for(int b = 0; b < l; b++) 320 columnNames[a][b] = str[b]; 321 322 version(sqlite_extended_metadata_available) { 323 if(fetchExtendedMetadata) { 324 string origtbl; 325 string origcol; 326 327 const(char)* rofl; 328 329 rofl = sqlite3_column_table_name(s, a); 330 if(rofl is null) 331 throw new Exception("null table name pointer"); 332 while(*rofl) { 333 origtbl ~= *rofl; 334 rofl++; 335 } 336 rofl = sqlite3_column_origin_name(s, a); 337 if(rofl is null) 338 throw new Exception("null colum name pointer"); 339 while(*rofl) { 340 origcol ~= *rofl; 341 rofl++; 342 } 343 extendedMetadata[columnNames[a].idup] = tuple(origtbl, origcol); 344 } 345 } 346 } 347 348 first = false; 349 } 350 351 352 rows[r].length = count; 353 354 for(int a = 0; a < count; a++){ 355 Variant v; 356 final switch(sqlite3_column_type(s, a)){ 357 case SQLITE_INTEGER: 358 v = sqlite3_column_int64(s, a); 359 break; 360 case SQLITE_FLOAT: 361 v = sqlite3_column_double(s, a); 362 break; 363 case SQLITE3_TEXT: 364 char* str = sqlite3_column_text(s, a); 365 char[] st; 366 367 import core.stdc.string : strlen; 368 sizediff_t l = strlen(str); 369 st.length = l; 370 for(int aa = 0; aa < l; aa++) 371 st[aa] = str[aa]; 372 373 v = assumeUnique(st); 374 break; 375 case SQLITE_BLOB: 376 byte* str = cast(byte*) sqlite3_column_blob(s, a); 377 byte[] st; 378 379 int l = sqlite3_column_bytes(s, a); 380 st.length = l; 381 for(int aa = 0; aa < l; aa++) 382 st[aa] = str[aa]; 383 384 v = assumeUnique(st); 385 386 break; 387 case SQLITE_NULL: 388 string n = null; 389 v = n; 390 break; 391 } 392 393 rows[r][a] = v; 394 } 395 396 r++; 397 } 398 399 rows.length = numRows; 400 length = numRows; 401 position = 0; 402 executed = true; 403 reset(); 404 405 return new SqliteResult(rows.dup, columnNames); 406 } 407 408 /* 409 template extract(A, T, R...){ 410 void extract(A args, out T t, out R r){ 411 if(r.length + 1 != args.length) 412 throw new DatabaseException("wrong places"); 413 args[0].to(t); 414 static if(r.length) 415 extract(args[1..$], r); 416 } 417 } 418 */ 419 /* 420 bool next(T, R...)(out T t, out R r){ 421 if(position == length) 422 return false; 423 424 extract(rows[position], t, r); 425 426 position++; 427 return true; 428 } 429 */ 430 bool step(out Variant[] row){ 431 assert(executed); 432 if(position == length) 433 return false; 434 435 row = rows[position]; 436 position++; 437 438 return true; 439 } 440 441 bool step(out Variant[char[]] row){ 442 assert(executed); 443 if(position == length) 444 return false; 445 446 for(int a = 0; a < length; a++) 447 row[columnNames[a].idup] = rows[position][a]; 448 449 position++; 450 451 return true; 452 } 453 454 void reset(){ 455 if(sqlite3_reset(s) != SQLITE_OK) 456 throw new DatabaseException("reset " ~ db.error()); 457 } 458 459 void resetBindings(){ 460 sqlite3_clear_bindings(s); 461 } 462 463 void resetAll(){ 464 reset; 465 resetBindings; 466 executed = false; 467 } 468 469 int bindNameLookUp(const char[] name){ 470 int a = sqlite3_bind_parameter_index(s, toStringz(name)); 471 if(a == 0) 472 throw new DatabaseException("bind name lookup failed " ~ db.error()); 473 return a; 474 } 475 476 bool next(T, R...)(out T t, out R r){ 477 assert(executed); 478 if(position == length) 479 return false; 480 481 extract(rows[position], t, r); 482 483 position++; 484 return true; 485 } 486 487 template bindAll(T, R...){ 488 void bindAll(T what, R more){ 489 bindAllHelper(1, what, more); 490 } 491 } 492 493 template exec(T, R...){ 494 void exec(T what, R more){ 495 bindAllHelper(1, what, more); 496 execute(); 497 } 498 } 499 500 void bindAllHelper(A, T, R...)(A where, T what, R more){ 501 bind(where, what); 502 static if(more.length) 503 bindAllHelper(where + 1, more); 504 } 505 506 //void bind(T)(string name, T value) { 507 //bind(bindNameLookUp(name), value); 508 //} 509 510 // This should be a template, but grrrr. 511 void bind (const char[] name, const char[] value){ bind(bindNameLookUp(name), value); } 512 void bind (const char[] name, int value){ bind(bindNameLookUp(name), value); } 513 void bind (const char[] name, float value){ bind(bindNameLookUp(name), value); } 514 void bind (const char[] name, double value){ bind(bindNameLookUp(name), value); } 515 void bind (const char[] name, const byte[] value){ bind(bindNameLookUp(name), value); } 516 void bind (const char[] name, const ubyte[] value){ bind(bindNameLookUp(name), value); } 517 518 void bind(int col, typeof(null) value){ 519 if(sqlite3_bind_null(s, col) != SQLITE_OK) 520 throw new DatabaseException("bind " ~ db.error()); 521 } 522 void bind(int col, const char[] value){ 523 if(sqlite3_bind_text(s, col, value.ptr is null ? "" : value.ptr, cast(int) value.length, cast(void*)-1) != SQLITE_OK) 524 throw new DatabaseException("bind " ~ db.error()); 525 } 526 527 void bind(int col, float value){ 528 if(sqlite3_bind_double(s, col, value) != SQLITE_OK) 529 throw new DatabaseException("bind " ~ db.error()); 530 } 531 532 void bind(int col, double value){ 533 if(sqlite3_bind_double(s, col, value) != SQLITE_OK) 534 throw new DatabaseException("bind " ~ db.error()); 535 } 536 537 void bind(int col, int value){ 538 if(sqlite3_bind_int(s, col, value) != SQLITE_OK) 539 throw new DatabaseException("bind " ~ db.error()); 540 } 541 542 void bind(int col, long value){ 543 if(sqlite3_bind_int64(s, col, value) != SQLITE_OK) 544 throw new DatabaseException("bind " ~ db.error()); 545 } 546 547 void bind(int col, const ubyte[] value){ 548 if(value is null) { 549 if(sqlite3_bind_null(s, col) != SQLITE_OK) 550 throw new DatabaseException("bind " ~ db.error()); 551 } else { 552 if(sqlite3_bind_blob(s, col, cast(void*)value.ptr, cast(int) value.length, cast(void*)-1) != SQLITE_OK) 553 throw new DatabaseException("bind " ~ db.error()); 554 } 555 } 556 557 void bind(int col, const byte[] value){ 558 if(value is null) { 559 if(sqlite3_bind_null(s, col) != SQLITE_OK) 560 throw new DatabaseException("bind " ~ db.error()); 561 } else { 562 if(sqlite3_bind_blob(s, col, cast(void*)value.ptr, cast(int) value.length, cast(void*)-1) != SQLITE_OK) 563 throw new DatabaseException("bind " ~ db.error()); 564 } 565 } 566 567 void bind(int col, Variant v) { 568 if(v.peek!long) 569 bind(col, v.get!long); 570 else if(v.peek!ulong) 571 bind(col, v.get!ulong); 572 else if(v.peek!int) 573 bind(col, v.get!int); 574 else if(v.peek!(const(int))) 575 bind(col, v.get!(const(int))); 576 else if(v.peek!bool) 577 bind(col, v.get!bool ? 1 : 0); 578 else if(v.peek!DateTime) 579 bind(col, v.get!DateTime.toISOExtString()); 580 else if(v.peek!string) 581 bind(col, v.get!string); 582 else if(v.peek!float) 583 bind(col, v.get!float); 584 else if(v.peek!double) 585 bind(col, v.get!double); 586 else if(v.peek!(byte[])) 587 bind(col, v.get!(byte[])); 588 else if(v.peek!(ubyte[])) 589 bind(col, v.get!(ubyte[])); 590 else if(v.peek!(immutable(ubyte)[])) 591 bind(col, v.get!(immutable(ubyte)[])); 592 else if(v.peek!(void*) && v.get!(void*) is null) 593 bind(col, null); 594 else 595 bind(col, v.coerce!string); 596 //assert(0, v.type.toString ~ " " ~ v.coerce!string); 597 } 598 599 ~this(){ 600 if(!finalized) 601 finalize(); 602 } 603 604 void finalize(){ 605 if(finalized) 606 return; 607 if(sqlite3_finalize(s) != SQLITE_OK) 608 throw new DatabaseException("finalize " ~ db.error()); 609 finalized = true; 610 } 611 private: 612 Variant[][] rows; 613 char[][] columnNames; 614 int length; 615 int position; 616 bool finalized; 617 618 sqlite3_stmt * s; 619 620 bool executed; 621 622 } 623 624 625 626 version(sqlite_extended_metadata_available) { 627 import std.typecons; 628 struct ResultByDataObject { 629 this(SqliteResult r, Tuple!(string, string)[string] mappings, Sqlite db) { 630 result = r; 631 this.db = db; 632 this.mappings = mappings; 633 } 634 635 Tuple!(string, string)[string] mappings; 636 637 ulong length() { return result.length; } 638 bool empty() { return result.empty; } 639 void popFront() { result.popFront(); } 640 DataObject front() { 641 return new DataObject(db, result.front.toAA, mappings); 642 } 643 // would it be good to add a new() method? would be valid even if empty 644 // it'd just fill in the ID's at random and allow you to do the rest 645 646 @disable this(this) { } 647 648 SqliteResult result; 649 Sqlite db; 650 } 651 } 652 653 654 655 656 657 658 659 660 661 662 extern(C) int callback(void* cb, int howmany, char** text, char** columns){ 663 if(cb is null) 664 return 0; 665 666 void delegate(char[][char[]]) onEach = *cast(void delegate(char[][char[]])*)cb; 667 668 669 char[][char[]] row; 670 import core.stdc.string : strlen; 671 672 for(int a = 0; a < howmany; a++){ 673 sizediff_t b = strlen(columns[a]); 674 char[] buf; 675 buf.length = b; 676 for(int c = 0; c < b; c++) 677 buf[c] = columns[a][c]; 678 679 sizediff_t d = strlen(text[a]); 680 char[] t; 681 t.length = d; 682 for(int c = 0; c < d; c++) 683 t[c] = text[a][c]; 684 685 row[buf.idup] = t; 686 } 687 688 onEach(row); 689 690 return 0; 691 } 692 693 694 695 696 697 698 699 700 701 702 extern(C){ 703 alias void sqlite3; 704 alias void sqlite3_stmt; 705 int sqlite3_changes(sqlite3*); 706 int sqlite3_close(sqlite3 *); 707 int sqlite3_exec( 708 sqlite3*, /* An open database */ 709 const(char) *sql, /* SQL to be evaluted */ 710 int function(void*,int,char**,char**), /* Callback function */ 711 void *, /* 1st argument to callback */ 712 char **errmsg /* Error msg written here */ 713 ); 714 715 int sqlite3_open( 716 const(char) *filename, /* Database filename (UTF-8) */ 717 sqlite3 **ppDb /* OUT: SQLite db handle */ 718 ); 719 720 int sqlite3_open_v2( 721 const char *filename, /* Database filename (UTF-8) */ 722 sqlite3 **ppDb, /* OUT: SQLite db handle */ 723 int flags, /* Flags */ 724 const char *zVfs /* Name of VFS module to use */ 725 ); 726 727 int sqlite3_prepare_v2( 728 sqlite3 *db, /* Database handle */ 729 const(char) *zSql, /* SQL statement, UTF-8 encoded */ 730 int nByte, /* Maximum length of zSql in bytes. */ 731 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 732 char **pzTail /* OUT: Pointer to unused portion of zSql */ 733 ); 734 int sqlite3_finalize(sqlite3_stmt *pStmt); 735 int sqlite3_step(sqlite3_stmt*); 736 long sqlite3_last_insert_rowid(sqlite3*); 737 738 const int SQLITE_OK = 0; 739 const int SQLITE_ROW = 100; 740 const int SQLITE_DONE = 101; 741 742 const int SQLITE_INTEGER = 1; // int 743 const int SQLITE_FLOAT = 2; // float 744 const int SQLITE3_TEXT = 3; // char[] 745 const int SQLITE_BLOB = 4; // byte[] 746 const int SQLITE_NULL = 5; // void* = null 747 748 char *sqlite3_mprintf(const char*,...); 749 750 751 int sqlite3_reset(sqlite3_stmt *pStmt); 752 int sqlite3_clear_bindings(sqlite3_stmt*); 753 int sqlite3_bind_parameter_index(sqlite3_stmt*, const(char) *zName); 754 755 int sqlite3_bind_blob(sqlite3_stmt*, int, void*, int n, void*); 756 //int sqlite3_bind_blob(sqlite3_stmt*, int, void*, int n, void(*)(void*)); 757 int sqlite3_bind_double(sqlite3_stmt*, int, double); 758 int sqlite3_bind_int(sqlite3_stmt*, int, int); 759 int sqlite3_bind_int64(sqlite3_stmt*, int, long); 760 int sqlite3_bind_null(sqlite3_stmt*, int); 761 int sqlite3_bind_text(sqlite3_stmt*, int, const(char)*, int n, void*); 762 //int sqlite3_bind_text(sqlite3_stmt*, int, char*, int n, void(*)(void*)); 763 764 void *sqlite3_column_blob(sqlite3_stmt*, int iCol); 765 int sqlite3_column_bytes(sqlite3_stmt*, int iCol); 766 double sqlite3_column_double(sqlite3_stmt*, int iCol); 767 int sqlite3_column_int(sqlite3_stmt*, int iCol); 768 long sqlite3_column_int64(sqlite3_stmt*, int iCol); 769 char *sqlite3_column_text(sqlite3_stmt*, int iCol); 770 int sqlite3_column_type(sqlite3_stmt*, int iCol); 771 char *sqlite3_column_name(sqlite3_stmt*, int N); 772 773 int sqlite3_column_count(sqlite3_stmt *pStmt); 774 void sqlite3_free(void*); 775 char *sqlite3_errmsg(sqlite3*); 776 777 const int SQLITE_OPEN_READONLY = 0x1; 778 const int SQLITE_OPEN_READWRITE = 0x2; 779 const int SQLITE_OPEN_CREATE = 0x4; 780 const int SQLITE_CANTOPEN = 14; 781 782 783 // will need these to enable support for DataObjects here 784 const (char *)sqlite3_column_database_name(sqlite3_stmt*,int); 785 const (char *)sqlite3_column_table_name(sqlite3_stmt*,int); 786 const (char *)sqlite3_column_origin_name(sqlite3_stmt*,int); 787 } 788