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 ~= cast(string) *t; 240 else 241 r.row ~= c.coerce!(string); 242 } 243 244 return r; 245 } 246 247 void popFront() { 248 position++; 249 } 250 251 override size_t length() { 252 return rows.length; 253 } 254 255 this(Variant[][] rows, char[][] columnNames) { 256 this.rows = rows; 257 foreach(c; columnNames) 258 this.columnNames ~= c.idup; 259 } 260 261 private: 262 string[] columnNames; 263 Variant[][] rows; 264 int position = 0; 265 } 266 267 268 269 270 271 272 struct Statement { 273 private this(Sqlite db, sqlite3_stmt * S) { 274 this.db = db; 275 s = S; 276 finalized = false; 277 } 278 279 Sqlite db; 280 281 this(Sqlite db, string sql) { 282 // the arsd convention is zero based ?, but sqlite insists on one based. so this is stupid but still 283 if(sql.indexOf("?0") != -1) { 284 foreach_reverse(i; 0 .. 10) 285 sql = sql.replace("?" ~ to!string(i), "?" ~ to!string(i + 1)); 286 } 287 288 this.db = db; 289 if(sqlite3_prepare_v2(db.db, toStringz(sql), cast(int) sql.length, &s, null) != SQLITE_OK) 290 throw new DatabaseException(db.error()); 291 } 292 293 version(sqlite_extended_metadata_available) 294 Tuple!(string, string)[string] extendedMetadata; 295 296 ResultSet execute(bool fetchExtendedMetadata = false) { 297 bool first = true; 298 int count; 299 int numRows = 0; 300 int r = 0; 301 // FIXME: doesn't handle busy database 302 while( SQLITE_ROW == sqlite3_step(s) ){ 303 numRows++; 304 if(numRows >= rows.length) 305 rows.length = rows.length + 8; 306 307 if(first){ 308 count = sqlite3_column_count(s); 309 310 columnNames.length = count; 311 for(int a = 0; a < count; a++){ 312 import core.stdc.string : strlen; 313 char* str = sqlite3_column_name(s, a); 314 sizediff_t l = strlen(str); 315 columnNames[a].length = l; 316 for(int b = 0; b < l; b++) 317 columnNames[a][b] = str[b]; 318 319 version(sqlite_extended_metadata_available) { 320 if(fetchExtendedMetadata) { 321 string origtbl; 322 string origcol; 323 324 const(char)* rofl; 325 326 rofl = sqlite3_column_table_name(s, a); 327 if(rofl is null) 328 throw new Exception("null table name pointer"); 329 while(*rofl) { 330 origtbl ~= *rofl; 331 rofl++; 332 } 333 rofl = sqlite3_column_origin_name(s, a); 334 if(rofl is null) 335 throw new Exception("null colum name pointer"); 336 while(*rofl) { 337 origcol ~= *rofl; 338 rofl++; 339 } 340 extendedMetadata[columnNames[a].idup] = tuple(origtbl, origcol); 341 } 342 } 343 } 344 345 first = false; 346 } 347 348 349 rows[r].length = count; 350 351 for(int a = 0; a < count; a++){ 352 Variant v; 353 final switch(sqlite3_column_type(s, a)){ 354 case SQLITE_INTEGER: 355 v = sqlite3_column_int64(s, a); 356 break; 357 case SQLITE_FLOAT: 358 v = sqlite3_column_double(s, a); 359 break; 360 case SQLITE3_TEXT: 361 char* str = sqlite3_column_text(s, a); 362 char[] st; 363 364 import core.stdc.string : strlen; 365 sizediff_t l = strlen(str); 366 st.length = l; 367 for(int aa = 0; aa < l; aa++) 368 st[aa] = str[aa]; 369 370 v = assumeUnique(st); 371 break; 372 case SQLITE_BLOB: 373 byte* str = cast(byte*) sqlite3_column_blob(s, a); 374 byte[] st; 375 376 int l = sqlite3_column_bytes(s, a); 377 st.length = l; 378 for(int aa = 0; aa < l; aa++) 379 st[aa] = str[aa]; 380 381 v = assumeUnique(st); 382 383 break; 384 case SQLITE_NULL: 385 string n = null; 386 v = n; 387 break; 388 } 389 390 rows[r][a] = v; 391 } 392 393 r++; 394 } 395 396 rows.length = numRows; 397 length = numRows; 398 position = 0; 399 executed = true; 400 reset(); 401 402 return new SqliteResult(rows.dup, columnNames); 403 } 404 405 /* 406 template extract(A, T, R...){ 407 void extract(A args, out T t, out R r){ 408 if(r.length + 1 != args.length) 409 throw new DatabaseException("wrong places"); 410 args[0].to(t); 411 static if(r.length) 412 extract(args[1..$], r); 413 } 414 } 415 */ 416 /* 417 bool next(T, R...)(out T t, out R r){ 418 if(position == length) 419 return false; 420 421 extract(rows[position], t, r); 422 423 position++; 424 return true; 425 } 426 */ 427 bool step(out Variant[] row){ 428 assert(executed); 429 if(position == length) 430 return false; 431 432 row = rows[position]; 433 position++; 434 435 return true; 436 } 437 438 bool step(out Variant[char[]] row){ 439 assert(executed); 440 if(position == length) 441 return false; 442 443 for(int a = 0; a < length; a++) 444 row[columnNames[a].idup] = rows[position][a]; 445 446 position++; 447 448 return true; 449 } 450 451 void reset(){ 452 if(sqlite3_reset(s) != SQLITE_OK) 453 throw new DatabaseException("reset " ~ db.error()); 454 } 455 456 void resetBindings(){ 457 sqlite3_clear_bindings(s); 458 } 459 460 void resetAll(){ 461 reset; 462 resetBindings; 463 executed = false; 464 } 465 466 int bindNameLookUp(const char[] name){ 467 int a = sqlite3_bind_parameter_index(s, toStringz(name)); 468 if(a == 0) 469 throw new DatabaseException("bind name lookup failed " ~ db.error()); 470 return a; 471 } 472 473 bool next(T, R...)(out T t, out R r){ 474 assert(executed); 475 if(position == length) 476 return false; 477 478 extract(rows[position], t, r); 479 480 position++; 481 return true; 482 } 483 484 template bindAll(T, R...){ 485 void bindAll(T what, R more){ 486 bindAllHelper(1, what, more); 487 } 488 } 489 490 template exec(T, R...){ 491 void exec(T what, R more){ 492 bindAllHelper(1, what, more); 493 execute(); 494 } 495 } 496 497 void bindAllHelper(A, T, R...)(A where, T what, R more){ 498 bind(where, what); 499 static if(more.length) 500 bindAllHelper(where + 1, more); 501 } 502 503 //void bind(T)(string name, T value) { 504 //bind(bindNameLookUp(name), value); 505 //} 506 507 // This should be a template, but grrrr. 508 void bind (const char[] name, const char[] value){ bind(bindNameLookUp(name), value); } 509 void bind (const char[] name, int value){ bind(bindNameLookUp(name), value); } 510 void bind (const char[] name, float value){ bind(bindNameLookUp(name), value); } 511 void bind (const char[] name, const byte[] value){ bind(bindNameLookUp(name), value); } 512 void bind (const char[] name, const ubyte[] value){ bind(bindNameLookUp(name), value); } 513 514 void bind(int col, typeof(null) value){ 515 if(sqlite3_bind_null(s, col) != SQLITE_OK) 516 throw new DatabaseException("bind " ~ db.error()); 517 } 518 void bind(int col, const char[] value){ 519 if(sqlite3_bind_text(s, col, value.ptr is null ? "" : value.ptr, cast(int) value.length, cast(void*)-1) != SQLITE_OK) 520 throw new DatabaseException("bind " ~ db.error()); 521 } 522 523 void bind(int col, float value){ 524 if(sqlite3_bind_double(s, col, value) != SQLITE_OK) 525 throw new DatabaseException("bind " ~ db.error()); 526 } 527 528 void bind(int col, int value){ 529 if(sqlite3_bind_int(s, col, value) != SQLITE_OK) 530 throw new DatabaseException("bind " ~ db.error()); 531 } 532 533 void bind(int col, long value){ 534 if(sqlite3_bind_int64(s, col, value) != SQLITE_OK) 535 throw new DatabaseException("bind " ~ db.error()); 536 } 537 538 void bind(int col, const ubyte[] value){ 539 if(value is null) { 540 if(sqlite3_bind_null(s, col) != SQLITE_OK) 541 throw new DatabaseException("bind " ~ db.error()); 542 } else { 543 if(sqlite3_bind_blob(s, col, cast(void*)value.ptr, cast(int) value.length, cast(void*)-1) != SQLITE_OK) 544 throw new DatabaseException("bind " ~ db.error()); 545 } 546 } 547 548 void bind(int col, const byte[] value){ 549 if(value is null) { 550 if(sqlite3_bind_null(s, col) != SQLITE_OK) 551 throw new DatabaseException("bind " ~ db.error()); 552 } else { 553 if(sqlite3_bind_blob(s, col, cast(void*)value.ptr, cast(int) value.length, cast(void*)-1) != SQLITE_OK) 554 throw new DatabaseException("bind " ~ db.error()); 555 } 556 } 557 558 void bind(int col, Variant v) { 559 if(v.peek!long) 560 bind(col, v.get!long); 561 else if(v.peek!ulong) 562 bind(col, v.get!ulong); 563 else if(v.peek!int) 564 bind(col, v.get!int); 565 else if(v.peek!(const(int))) 566 bind(col, v.get!(const(int))); 567 else if(v.peek!bool) 568 bind(col, v.get!bool ? 1 : 0); 569 else if(v.peek!DateTime) 570 bind(col, v.get!DateTime.toISOExtString()); 571 else if(v.peek!string) 572 bind(col, v.get!string); 573 else if(v.peek!float) 574 bind(col, v.get!float); 575 else if(v.peek!(byte[])) 576 bind(col, v.get!(byte[])); 577 else if(v.peek!(ubyte[])) 578 bind(col, v.get!(ubyte[])); 579 else if(v.peek!(immutable(ubyte)[])) 580 bind(col, v.get!(immutable(ubyte)[])); 581 else if(v.peek!(void*) && v.get!(void*) is null) 582 bind(col, null); 583 else 584 bind(col, v.coerce!string); 585 //assert(0, v.type.toString ~ " " ~ v.coerce!string); 586 } 587 588 ~this(){ 589 if(!finalized) 590 finalize(); 591 } 592 593 void finalize(){ 594 if(finalized) 595 return; 596 if(sqlite3_finalize(s) != SQLITE_OK) 597 throw new DatabaseException("finalize " ~ db.error()); 598 finalized = true; 599 } 600 private: 601 Variant[][] rows; 602 char[][] columnNames; 603 int length; 604 int position; 605 bool finalized; 606 607 sqlite3_stmt * s; 608 609 bool executed; 610 611 } 612 613 614 615 version(sqlite_extended_metadata_available) { 616 import std.typecons; 617 struct ResultByDataObject { 618 this(SqliteResult r, Tuple!(string, string)[string] mappings, Sqlite db) { 619 result = r; 620 this.db = db; 621 this.mappings = mappings; 622 } 623 624 Tuple!(string, string)[string] mappings; 625 626 ulong length() { return result.length; } 627 bool empty() { return result.empty; } 628 void popFront() { result.popFront(); } 629 DataObject front() { 630 return new DataObject(db, result.front.toAA, mappings); 631 } 632 // would it be good to add a new() method? would be valid even if empty 633 // it'd just fill in the ID's at random and allow you to do the rest 634 635 @disable this(this) { } 636 637 SqliteResult result; 638 Sqlite db; 639 } 640 } 641 642 643 644 645 646 647 648 649 650 651 extern(C) int callback(void* cb, int howmany, char** text, char** columns){ 652 if(cb is null) 653 return 0; 654 655 void delegate(char[][char[]]) onEach = *cast(void delegate(char[][char[]])*)cb; 656 657 658 char[][char[]] row; 659 import core.stdc.string : strlen; 660 661 for(int a = 0; a < howmany; a++){ 662 sizediff_t b = strlen(columns[a]); 663 char[] buf; 664 buf.length = b; 665 for(int c = 0; c < b; c++) 666 buf[c] = columns[a][c]; 667 668 sizediff_t d = strlen(text[a]); 669 char[] t; 670 t.length = d; 671 for(int c = 0; c < d; c++) 672 t[c] = text[a][c]; 673 674 row[buf.idup] = t; 675 } 676 677 onEach(row); 678 679 return 0; 680 } 681 682 683 684 685 686 687 688 689 690 691 extern(C){ 692 alias void sqlite3; 693 alias void sqlite3_stmt; 694 int sqlite3_changes(sqlite3*); 695 int sqlite3_close(sqlite3 *); 696 int sqlite3_exec( 697 sqlite3*, /* An open database */ 698 const(char) *sql, /* SQL to be evaluted */ 699 int function(void*,int,char**,char**), /* Callback function */ 700 void *, /* 1st argument to callback */ 701 char **errmsg /* Error msg written here */ 702 ); 703 704 int sqlite3_open( 705 const(char) *filename, /* Database filename (UTF-8) */ 706 sqlite3 **ppDb /* OUT: SQLite db handle */ 707 ); 708 709 int sqlite3_open_v2( 710 const char *filename, /* Database filename (UTF-8) */ 711 sqlite3 **ppDb, /* OUT: SQLite db handle */ 712 int flags, /* Flags */ 713 const char *zVfs /* Name of VFS module to use */ 714 ); 715 716 int sqlite3_prepare_v2( 717 sqlite3 *db, /* Database handle */ 718 const(char) *zSql, /* SQL statement, UTF-8 encoded */ 719 int nByte, /* Maximum length of zSql in bytes. */ 720 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 721 char **pzTail /* OUT: Pointer to unused portion of zSql */ 722 ); 723 int sqlite3_finalize(sqlite3_stmt *pStmt); 724 int sqlite3_step(sqlite3_stmt*); 725 long sqlite3_last_insert_rowid(sqlite3*); 726 727 const int SQLITE_OK = 0; 728 const int SQLITE_ROW = 100; 729 const int SQLITE_DONE = 101; 730 731 const int SQLITE_INTEGER = 1; // int 732 const int SQLITE_FLOAT = 2; // float 733 const int SQLITE3_TEXT = 3; // char[] 734 const int SQLITE_BLOB = 4; // byte[] 735 const int SQLITE_NULL = 5; // void* = null 736 737 char *sqlite3_mprintf(const char*,...); 738 739 740 int sqlite3_reset(sqlite3_stmt *pStmt); 741 int sqlite3_clear_bindings(sqlite3_stmt*); 742 int sqlite3_bind_parameter_index(sqlite3_stmt*, const(char) *zName); 743 744 int sqlite3_bind_blob(sqlite3_stmt*, int, void*, int n, void*); 745 //int sqlite3_bind_blob(sqlite3_stmt*, int, void*, int n, void(*)(void*)); 746 int sqlite3_bind_double(sqlite3_stmt*, int, double); 747 int sqlite3_bind_int(sqlite3_stmt*, int, int); 748 int sqlite3_bind_int64(sqlite3_stmt*, int, long); 749 int sqlite3_bind_null(sqlite3_stmt*, int); 750 int sqlite3_bind_text(sqlite3_stmt*, int, const(char)*, int n, void*); 751 //int sqlite3_bind_text(sqlite3_stmt*, int, char*, int n, void(*)(void*)); 752 753 void *sqlite3_column_blob(sqlite3_stmt*, int iCol); 754 int sqlite3_column_bytes(sqlite3_stmt*, int iCol); 755 double sqlite3_column_double(sqlite3_stmt*, int iCol); 756 int sqlite3_column_int(sqlite3_stmt*, int iCol); 757 long sqlite3_column_int64(sqlite3_stmt*, int iCol); 758 char *sqlite3_column_text(sqlite3_stmt*, int iCol); 759 int sqlite3_column_type(sqlite3_stmt*, int iCol); 760 char *sqlite3_column_name(sqlite3_stmt*, int N); 761 762 int sqlite3_column_count(sqlite3_stmt *pStmt); 763 void sqlite3_free(void*); 764 char *sqlite3_errmsg(sqlite3*); 765 766 const int SQLITE_OPEN_READONLY = 0x1; 767 const int SQLITE_OPEN_READWRITE = 0x2; 768 const int SQLITE_OPEN_CREATE = 0x4; 769 const int SQLITE_CANTOPEN = 14; 770 771 772 // will need these to enable support for DataObjects here 773 const (char *)sqlite3_column_database_name(sqlite3_stmt*,int); 774 const (char *)sqlite3_column_table_name(sqlite3_stmt*,int); 775 const (char *)sqlite3_column_origin_name(sqlite3_stmt*,int); 776 } 777