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