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