1 /** 2 * DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC. 3 * 4 * Source file ddbc/drivers/pgsqlddbc.d. 5 * 6 * DDBC library attempts to provide implementation independent interface to different databases. 7 * 8 * Set of supported RDBMSs can be extended by writing Drivers for particular DBs. 9 * 10 * JDBC documentation can be found here: 11 * $(LINK http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html)$(BR) 12 * 13 * This module contains implementation of SQLite Driver 14 * 15 * You can find usage examples in unittest{} sections. 16 * 17 * Copyright: Copyright 2013 18 * License: $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0). 19 * Author: Vadim Lopatin 20 */ 21 module ddbc.drivers.sqliteddbc; 22 23 24 version(USE_SQLITE) { 25 26 import std.algorithm; 27 import std.conv; 28 import std.datetime : Date, DateTime, TimeOfDay; 29 import std.datetime.date; 30 import std.datetime.systime : SysTime, Clock; 31 import std.datetime.timezone : UTC; 32 import std.exception; 33 34 // For backwards compatibily 35 // 'enforceEx' will be removed with 2.089 36 static if(__VERSION__ < 2080) { 37 alias enforceHelper = enforceEx; 38 } else { 39 alias enforceHelper = enforce; 40 } 41 42 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 43 import std.experimental.logger; 44 } 45 import std.stdio; 46 import std..string; 47 import std.variant; 48 import core.sync.mutex; 49 import ddbc.common; 50 import ddbc.core; 51 //import ddbc.drivers.sqlite; 52 import ddbc.drivers.utils; 53 import etc.c.sqlite3; 54 import std.traits : isSomeString; 55 56 57 version (Windows) { 58 pragma (lib, "sqlite3"); 59 } else version (linux) { 60 pragma (lib, "sqlite3"); 61 } else version (OSX) { 62 pragma (lib, "sqlite3"); 63 } else version (Posix) { 64 pragma (lib, "libsqlite3"); 65 } else { 66 pragma (msg, "You will need to manually link in the SQLite library."); 67 } 68 69 version(unittest) { 70 /* 71 To allow unit tests using PostgreSQL server, 72 */ 73 /// change to false to disable tests on real PostgreSQL server 74 immutable bool SQLITE_TESTS_ENABLED = true; 75 /// change parameters if necessary 76 const string SQLITE_UNITTEST_URL = "sqlite::memory:"; // "sqlite:ddbctest.sqlite"; 77 78 static if (SQLITE_TESTS_ENABLED) { 79 /// use this data source for tests 80 DataSource createUnitTestSQLITEDataSource() { 81 return createConnectionPool(SQLITE_UNITTEST_URL); 82 } 83 } 84 } 85 86 /// Converts from a selection of the standard SQLite time formats into a SysTime object. 87 // Should have similar features to 'DateTime fromResultSet(string)' but handling TZ as well 88 // 89 // SQLite can store dates and times as TEXT, REAL, or INTEGER values: 90 // 91 // TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). 92 // REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. 93 // INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. 94 // 95 // Presume time is being kept as TEXT and try to parse it: 96 // YYYY-MM-DD HH:MM:SS 97 // YYYY-MM-DD HH:MM:SS.SSS 98 // YYYY-MM-DDTHH:MM:SS 99 // YYYY-MM-DDTHH:MM:SS.SSS 100 // YYYY-MM-DD HH:MM:SS 101 // YYYYMMDDHHMMSS 102 private SysTime parseSysTime(S)(in S sqliteString) @safe 103 if (isSomeString!S) { 104 // 105 try { 106 import std.regex : match; 107 if(match(sqliteString, r"\d{4}-\D{3}-\d{2}.*")) { 108 return SysTime.fromSimpleString(sqliteString); 109 } else if(match(sqliteString, r".*[\+|\-]\d{1,2}:\d{1,2}|.*Z")) { 110 return sqliteString.canFind('-') ? 111 SysTime.fromISOExtString(sqliteString) : 112 SysTime.fromISOString(sqliteString); 113 } else { 114 return SysTime(fromResultSet(sqliteString), UTC()); 115 } 116 } catch (ConvException) { 117 // Let the exception fall to the throw statement below 118 } 119 throw new DateTimeException(format("Unknown SQLite DATETIME string: %s", sqliteString)); 120 } 121 122 /// Converts from a selection of the standard SQLite time formats into a DateTime object. 123 private DateTime fromResultSet(S)(in S sqliteString) @safe 124 if (isSomeString!S) { 125 126 try { 127 switch (sqliteString.length) { 128 case 5: 129 if (sqliteString[2] == ':') { 130 // HH:MM 131 int hours = cast(int) to!uint(sqliteString[0..2]); 132 int minutes = cast(int) to!uint(sqliteString[3..5]); 133 return DateTime(0, 1, 1, hours, minutes); 134 } 135 break; 136 case 8: 137 if (sqliteString[2] == ':' && sqliteString[5] == ':') { 138 // HH:MM:SS 139 auto time = TimeOfDay.fromISOExtString(sqliteString); 140 return DateTime(Date(), time); 141 } 142 break; 143 case 10: 144 if (sqliteString[4] == '-' && sqliteString[7] == '-') { 145 // YYYY-MM-DD 146 auto date = Date.fromISOExtString(sqliteString); 147 return DateTime(date, TimeOfDay()); 148 } 149 break; 150 case 11: 151 // YYYY-MMM-DD 152 auto date = Date.fromSimpleString(sqliteString); 153 return DateTime(date, TimeOfDay()); 154 case 12: 155 if (sqliteString[2] == ':' && sqliteString[5] == ':') { 156 // HH:MM:SS.SSS 157 auto time = TimeOfDay.fromISOExtString(sqliteString[0..8]); // chop the '.SSS' off 158 return DateTime(Date(), time); 159 } 160 break; 161 case 15: 162 // YYYYMMDDTHHMMSS 163 return DateTime.fromISOString(sqliteString); 164 case 16: 165 // YYYY-MM-DD HH:MM 166 // YYYY-MM-DDTHH:MM 167 168 auto date = Date.fromISOExtString(sqliteString[0..10]); 169 170 int hours = cast(int) to!uint(sqliteString[11 .. 13]); 171 int minutes = cast(int) to!uint(sqliteString[14 .. 16]); 172 auto time = TimeOfDay(hours, minutes); 173 return DateTime(date, time); 174 case 19: 175 case 23: 176 // YYYY-MM-DD HH:MM:SS 177 // YYYY-MM-DD HH:MM:SS.SSS 178 // YYYY-MM-DDTHH:MM:SS 179 // YYYY-MM-DDTHH:MM:SS.SSS 180 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 181 if(sqliteString.length > 19) { 182 sharedLog.warning(sqliteString ~ " will be converted to DateTime and lose the milliseconds. Consider using SysTime"); 183 } 184 } 185 186 auto date = Date.fromISOExtString(sqliteString[0..10]); 187 auto time = TimeOfDay.fromISOExtString(sqliteString[11..19]); 188 return DateTime(date, time); 189 case 20: 190 // YYYY-MMM-DD HH:MM:SS 191 auto date = Date.fromSimpleString(sqliteString[0..11]); 192 auto time = TimeOfDay.fromISOExtString(sqliteString[12..20]); 193 return DateTime(date, time); 194 default: 195 // Fall through to the throw statement below 196 break; 197 } 198 } catch (ConvException) { 199 // Let the exception fall to the throw statement below 200 } 201 throw new DateTimeException(format("Unknown SQLite date string: %s", sqliteString)); 202 } 203 204 unittest { 205 DateTime hm = fromResultSet("15:18"); // HH:MM 206 DateTime hms = fromResultSet("15:18:51"); // HH:MM:SS 207 208 DateTime hmss = fromResultSet("15:18:51.500"); // HH:MM:SS.SSS 209 assert(hmss.toISOExtString() == "0001-01-01T15:18:51"); // it'll lose the precision and default to 0001-01-01 210 211 212 DateTime ymd = fromResultSet("2019-09-15"); // YYYY-MM-DD 213 fromResultSet("2019-Sep-15"); 214 DateTime ymdhm = fromResultSet("2019-09-15 15:18"); // YYYY-MM-DD HH:MM 215 DateTime ymdthm = fromResultSet("2019-09-15T15:18"); // YYYY-MM-DDTHH:MM 216 217 DateTime nonstandard = fromResultSet("20190915T151851"); // YYYYMMDDTHHMMSS 218 219 DateTime ymdhms = fromResultSet("2019-09-15 15:18:51"); // YYYY-MM-DD HH:MM:SS 220 221 DateTime ymdhmss = fromResultSet("2019-09-15 15:18:51.500"); // YYYY-MM-DD HH:MM:SS.SSS 222 assert(ymdhmss.toISOExtString() == "2019-09-15T15:18:51"); // it'll lose the precision 223 224 DateTime ymdthms = fromResultSet("2019-09-15T15:18:51"); // YYYY-MM-DDTHH:MM:SS 225 fromResultSet("2019-Sep-15T15:18:51"); // YYYY-MMM-DDTHH:MM:SS 226 227 DateTime ymdthmss = fromResultSet("2019-09-15T15:18:51.500"); // YYYY-MM-DDTHH:MM:SS.SSS 228 assert(ymdthmss.toISOExtString() == "2019-09-15T15:18:51"); // it'll lose the precision 229 230 // todo. SQLite DATETIME values can also have timezone : [+-]HH:MM or Z (for UTC) 231 // as well as greater preciion than a std.datetime.date : DateTime can handle. 232 // we need to add support for std.datetime.systime : SysTime so that we can do: 233 // SysTime.fromISOExtString("2018-01-01T10:30:00Z"); 234 // see: https://github.com/buggins/ddbc/issues/62 235 236 SysTime nonstandardUtc = parseSysTime("20190915T151851Z"); // YYYYMMDDTHHMMSSZ 237 238 parseSysTime("2018-01-01T10:30:00Z"); 239 parseSysTime("2010-Dec-30 00:00:00Z"); // values may come back from db in this format 240 241 // values may come back from db without tz 242 parseSysTime("2019-09-22 20:54:57"); 243 //parseSysTime("2019-09-22T20:54"); 244 245 SysTime ymdthmssUtc = parseSysTime("2019-09-15T15:18:51.500Z"); // YYYY-MM-DDTHH:MM:SS.SSSZ 246 assert(ymdthmssUtc.toISOExtString() == "2019-09-15T15:18:51.5Z", ymdthmssUtc.toISOExtString()); 247 248 SysTime ymdthmssUtcPlus2 = parseSysTime("2019-09-15T15:18:51.500+02:00"); // YYYY-MM-DDTHH:MM:SS.SSS+HH:MM 249 assert(ymdthmssUtcPlus2.toISOExtString() == "2019-09-15T15:18:51.5+02:00", ymdthmssUtcPlus2.toISOExtString()); 250 } 251 252 class SQLITEConnection : ddbc.core.Connection { 253 private: 254 string filename; 255 256 sqlite3 * conn; 257 258 bool closed; 259 bool autocommit; 260 Mutex mutex; 261 262 263 SQLITEStatement [] activeStatements; 264 265 void closeUnclosedStatements() { 266 SQLITEStatement [] list = activeStatements.dup; 267 foreach(stmt; list) { 268 stmt.close(); 269 } 270 } 271 272 void checkClosed() { 273 if (closed) 274 throw new SQLException("Connection is already closed"); 275 } 276 277 public: 278 279 private string getError() { 280 return copyCString(sqlite3_errmsg(conn)); 281 } 282 283 void lock() { 284 mutex.lock(); 285 } 286 287 void unlock() { 288 mutex.unlock(); 289 } 290 291 sqlite3 * getConnection() { return conn; } 292 293 294 void onStatementClosed(SQLITEStatement stmt) { 295 myRemove(activeStatements, stmt); 296 } 297 298 this(string url, string[string] params) { 299 mutex = new Mutex(); 300 extractParamsFromURL(url, params); 301 if (url.startsWith("sqlite:")) 302 url = url[7 .. $]; 303 this.filename = url; 304 //writeln("trying to connect"); 305 int res = sqlite3_open(toStringz(filename), &conn); 306 if(res != SQLITE_OK) 307 throw new SQLException("SQLITE Error " ~ to!string(res) ~ " while trying to open DB " ~ filename ~ " : " ~ getError()); 308 assert(conn !is null); 309 closed = false; 310 setAutoCommit(true); 311 } 312 313 override void close() { 314 checkClosed(); 315 316 lock(); 317 scope(exit) unlock(); 318 319 closeUnclosedStatements(); 320 int res = sqlite3_close(conn); 321 if (res != SQLITE_OK) 322 throw new SQLException("SQLITE Error " ~ to!string(res) ~ " while trying to close DB " ~ filename ~ " : " ~ getError()); 323 closed = true; 324 } 325 326 override void commit() { 327 checkClosed(); 328 329 lock(); 330 scope(exit) unlock(); 331 332 Statement stmt = createStatement(); 333 scope(exit) stmt.close(); 334 stmt.executeUpdate("COMMIT"); 335 } 336 337 override Statement createStatement() { 338 checkClosed(); 339 340 lock(); 341 scope(exit) unlock(); 342 343 SQLITEStatement stmt = new SQLITEStatement(this); 344 activeStatements ~= stmt; 345 return stmt; 346 } 347 348 PreparedStatement prepareStatement(string sql) { 349 checkClosed(); 350 351 lock(); 352 scope(exit) unlock(); 353 354 SQLITEPreparedStatement stmt = new SQLITEPreparedStatement(this, sql); 355 activeStatements ~= cast(SQLITEStatement)stmt; 356 return stmt; 357 } 358 359 override string getCatalog() { 360 return "default"; 361 } 362 363 /// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work. 364 override void setCatalog(string catalog) { 365 checkClosed(); 366 throw new SQLException("Not implemented"); 367 } 368 369 override bool isClosed() { 370 return closed; 371 } 372 373 override void rollback() { 374 checkClosed(); 375 376 lock(); 377 scope(exit) unlock(); 378 379 Statement stmt = createStatement(); 380 scope(exit) stmt.close(); 381 //TODO: 382 //stmt.executeUpdate("ROLLBACK"); 383 } 384 override bool getAutoCommit() { 385 return autocommit; 386 } 387 override void setAutoCommit(bool autoCommit) { 388 checkClosed(); 389 if (this.autocommit == autoCommit) 390 return; 391 lock(); 392 scope(exit) unlock(); 393 394 Statement stmt = createStatement(); 395 scope(exit) stmt.close(); 396 //TODO: 397 //stmt.executeUpdate("SET autocommit = " ~ (autoCommit ? "ON" : "OFF")); 398 this.autocommit = autoCommit; 399 } 400 } 401 402 class SQLITEStatement : Statement { 403 private: 404 SQLITEConnection conn; 405 // Command * cmd; 406 // ddbc.drivers.mysql.ResultSet rs; 407 SQLITEResultSet resultSet; 408 409 bool closed; 410 411 public: 412 void checkClosed() { 413 enforceHelper!SQLException(!closed, "Statement is already closed"); 414 } 415 416 void lock() { 417 conn.lock(); 418 } 419 420 void unlock() { 421 conn.unlock(); 422 } 423 424 this(SQLITEConnection conn) { 425 this.conn = conn; 426 } 427 428 public: 429 SQLITEConnection getConnection() { 430 checkClosed(); 431 return conn; 432 } 433 434 private PreparedStatement _currentStatement; 435 private ResultSet _currentResultSet; 436 437 private void closePreparedStatement() { 438 if (_currentResultSet !is null) { 439 _currentResultSet.close(); 440 _currentResultSet = null; 441 } 442 if (_currentStatement !is null) { 443 _currentStatement.close(); 444 _currentStatement = null; 445 } 446 } 447 448 override ddbc.core.ResultSet executeQuery(string query) { 449 closePreparedStatement(); 450 _currentStatement = conn.prepareStatement(query); 451 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 452 sharedLog.trace(_currentStatement); 453 } 454 _currentResultSet = _currentStatement.executeQuery(); 455 return _currentResultSet; 456 } 457 458 // string getError() { 459 // return copyCString(PQerrorMessage(conn.getConnection())); 460 // } 461 462 override int executeUpdate(string query) { 463 Variant dummy; 464 return executeUpdate(query, dummy); 465 } 466 467 override int executeUpdate(string query, out Variant insertId) { 468 closePreparedStatement(); 469 _currentStatement = conn.prepareStatement(query); 470 471 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 472 sharedLog.trace(_currentStatement); 473 } 474 475 return _currentStatement.executeUpdate(insertId); 476 } 477 478 override void close() { 479 checkClosed(); 480 lock(); 481 scope(exit) unlock(); 482 closePreparedStatement(); 483 closed = true; 484 conn.onStatementClosed(this); 485 } 486 487 void closeResultSet() { 488 } 489 } 490 491 class SQLITEPreparedStatement : SQLITEStatement, PreparedStatement { 492 string query; 493 int paramCount; 494 495 sqlite3_stmt * stmt; 496 497 bool done; 498 bool preparing; 499 500 ResultSetMetaData metadata; 501 ParameterMetaData paramMetadata; 502 this(SQLITEConnection conn, string query) { 503 super(conn); 504 this.query = query; 505 506 int res = sqlite3_prepare_v2( 507 conn.getConnection(), /* Database handle */ 508 toStringz(query), /* SQL statement, UTF-8 encoded */ 509 cast(int)query.length, /* Maximum length of zSql in bytes. */ 510 &stmt, /* OUT: Statement handle */ 511 null /* OUT: Pointer to unused portion of zSql */ 512 ); 513 enforceHelper!SQLException(res == SQLITE_OK, "Error #" ~ to!string(res) ~ " while preparing statement " ~ query ~ " : " ~ conn.getError()); 514 paramMetadata = createParamMetadata(); 515 paramCount = paramMetadata.getParameterCount(); 516 metadata = createMetadata(); 517 resetParams(); 518 preparing = true; 519 } 520 bool[] paramIsSet; 521 void resetParams() { 522 paramIsSet = new bool[paramCount]; 523 } 524 // before execution of query 525 private void allParamsSet() { 526 for(int i = 0; i < paramCount; i++) { 527 enforceHelper!SQLException(paramIsSet[i], "Parameter " ~ to!string(i + 1) ~ " is not set"); 528 } 529 if (preparing) { 530 preparing = false; 531 } else { 532 closeResultSet(); 533 sqlite3_reset(stmt); 534 } 535 } 536 // before setting any parameter 537 private void checkIndex(int index) { 538 if (index < 1 || index > paramCount) 539 throw new SQLException("Parameter index " ~ to!string(index) ~ " is out of range"); 540 if (!preparing) { 541 closeResultSet(); 542 sqlite3_reset(stmt); 543 preparing = true; 544 } 545 } 546 ref Variant getParam(int index) { 547 throw new SQLException("Not implemented"); 548 // checkIndex(index); 549 // return cmd.param(cast(ushort)(index - 1)); 550 } 551 public: 552 SqlType sqliteToSqlType(int t) { 553 switch(t) { 554 case SQLITE_INTEGER: return SqlType.BIGINT; 555 case SQLITE_FLOAT: return SqlType.DOUBLE; 556 case SQLITE3_TEXT: return SqlType.VARCHAR; 557 case SQLITE_BLOB: return SqlType.BLOB; 558 case SQLITE_NULL: return SqlType.NULL; 559 default: 560 return SqlType.BLOB; 561 } 562 } 563 564 ResultSetMetaData createMetadata() { 565 int fieldCount = sqlite3_column_count(stmt); 566 ColumnMetadataItem[] list = new ColumnMetadataItem[fieldCount]; 567 for(int i = 0; i < fieldCount; i++) { 568 ColumnMetadataItem item = new ColumnMetadataItem(); 569 item.label = copyCString(sqlite3_column_origin_name(stmt, i)); 570 item.name = copyCString(sqlite3_column_name(stmt, i)); 571 item.schemaName = copyCString(sqlite3_column_database_name(stmt, i)); 572 item.tableName = copyCString(sqlite3_column_table_name(stmt, i)); 573 item.type = sqliteToSqlType(sqlite3_column_type(stmt, i)); 574 list[i] = item; 575 } 576 return new ResultSetMetaDataImpl(list); 577 } 578 579 ParameterMetaData createParamMetadata() { 580 int fieldCount = sqlite3_bind_parameter_count(stmt); 581 ParameterMetaDataItem[] res = new ParameterMetaDataItem[fieldCount]; 582 for(int i = 0; i < fieldCount; i++) { 583 ParameterMetaDataItem item = new ParameterMetaDataItem(); 584 item.type = SqlType.VARCHAR; 585 res[i] = item; 586 } 587 paramCount = fieldCount; 588 return new ParameterMetaDataImpl(res); 589 } 590 591 override void close() { 592 if (closed) 593 return; 594 checkClosed(); 595 lock(); 596 scope(exit) unlock(); 597 598 closeResultSet(); 599 int res = sqlite3_finalize(stmt); 600 enforceHelper!SQLException(res == SQLITE_OK, "Error #" ~ to!string(res) ~ " while closing prepared statement " ~ query ~ " : " ~ conn.getError()); 601 closed = true; 602 conn.onStatementClosed(this); 603 } 604 605 606 /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. 607 override ResultSetMetaData getMetaData() { 608 checkClosed(); 609 lock(); 610 scope(exit) unlock(); 611 return metadata; 612 } 613 614 /// Retrieves the number, types and properties of this PreparedStatement object's parameters. 615 override ParameterMetaData getParameterMetaData() { 616 checkClosed(); 617 lock(); 618 scope(exit) unlock(); 619 return paramMetadata; 620 } 621 622 override int executeUpdate(out Variant insertId) { 623 //throw new SQLException("Not implemented"); 624 checkClosed(); 625 lock(); 626 scope(exit) unlock(); 627 allParamsSet(); 628 629 int rowsAffected = 0; 630 int res = sqlite3_step(stmt); 631 if (res == SQLITE_DONE) { 632 insertId = Variant(sqlite3_last_insert_rowid(conn.getConnection())); 633 rowsAffected = sqlite3_changes(conn.getConnection()); 634 done = true; 635 } else if (res == SQLITE_ROW) { 636 // row is available 637 rowsAffected = -1; 638 } else { 639 enforceHelper!SQLException(false, "Error #" ~ to!string(res) ~ " while trying to execute prepared statement: " ~ " : " ~ conn.getError()); 640 } 641 return rowsAffected; 642 } 643 644 override int executeUpdate() { 645 Variant insertId; 646 return executeUpdate(insertId); 647 } 648 649 override ddbc.core.ResultSet executeQuery() { 650 checkClosed(); 651 lock(); 652 scope(exit) unlock(); 653 allParamsSet(); 654 enforceHelper!SQLException(metadata.getColumnCount() > 0, "Query doesn't return result set"); 655 resultSet = new SQLITEResultSet(this, stmt, getMetaData()); 656 return resultSet; 657 } 658 659 override void clearParameters() { 660 throw new SQLException("Not implemented"); 661 // checkClosed(); 662 // lock(); 663 // scope(exit) unlock(); 664 // for (int i = 1; i <= paramCount; i++) 665 // setNull(i); 666 } 667 668 override void setFloat(int parameterIndex, float x) { 669 setDouble(parameterIndex, x); 670 } 671 override void setDouble(int parameterIndex, double x){ 672 checkClosed(); 673 lock(); 674 scope(exit) unlock(); 675 checkIndex(parameterIndex); 676 sqlite3_bind_double(stmt, parameterIndex, x); 677 paramIsSet[parameterIndex - 1] = true; 678 } 679 override void setBoolean(int parameterIndex, bool x) { 680 setLong(parameterIndex, x ? 1 : 0); 681 } 682 override void setLong(int parameterIndex, long x) { 683 checkClosed(); 684 lock(); 685 scope(exit) unlock(); 686 checkIndex(parameterIndex); 687 sqlite3_bind_int64(stmt, parameterIndex, x); 688 paramIsSet[parameterIndex - 1] = true; 689 } 690 override void setUlong(int parameterIndex, ulong x) { 691 setLong(parameterIndex, cast(long)x); 692 } 693 override void setInt(int parameterIndex, int x) { 694 setLong(parameterIndex, cast(long)x); 695 } 696 override void setUint(int parameterIndex, uint x) { 697 setLong(parameterIndex, cast(long)x); 698 } 699 override void setShort(int parameterIndex, short x) { 700 setLong(parameterIndex, cast(long)x); 701 } 702 override void setUshort(int parameterIndex, ushort x) { 703 setLong(parameterIndex, cast(long)x); 704 } 705 override void setByte(int parameterIndex, byte x) { 706 setLong(parameterIndex, cast(long)x); 707 } 708 override void setUbyte(int parameterIndex, ubyte x) { 709 setLong(parameterIndex, cast(long)x); 710 } 711 override void setBytes(int parameterIndex, byte[] x) { 712 checkClosed(); 713 lock(); 714 scope(exit) unlock(); 715 checkIndex(parameterIndex); 716 if (x.ptr is null) { 717 setNull(parameterIndex); 718 return; 719 } 720 sqlite3_bind_blob(stmt, parameterIndex, cast(const (void *))x.ptr, cast(int)x.length, SQLITE_TRANSIENT); 721 paramIsSet[parameterIndex - 1] = true; 722 } 723 override void setUbytes(int parameterIndex, ubyte[] x) { 724 checkClosed(); 725 lock(); 726 scope(exit) unlock(); 727 checkIndex(parameterIndex); 728 if (x.ptr is null) { 729 setNull(parameterIndex); 730 return; 731 } 732 sqlite3_bind_blob(stmt, parameterIndex, cast(const char *)x.ptr, cast(int)x.length, SQLITE_TRANSIENT); 733 paramIsSet[parameterIndex - 1] = true; 734 } 735 override void setString(int parameterIndex, string x) { 736 checkClosed(); 737 lock(); 738 scope(exit) unlock(); 739 checkIndex(parameterIndex); 740 if (x.ptr is null) { 741 setNull(parameterIndex); 742 return; 743 } 744 sqlite3_bind_text(stmt, parameterIndex, cast(const char *)x.ptr, cast(int)x.length, SQLITE_TRANSIENT); 745 paramIsSet[parameterIndex - 1] = true; 746 } 747 748 override void setSysTime(int parameterIndex, SysTime x) { 749 // ISO string is "20180101T103000-05:00" 750 // ISO Ext string is "2018-01-01T10:30:00-05:00" 751 setString(parameterIndex, x.toISOExtString()); 752 } 753 754 override void setDateTime(int parameterIndex, DateTime x) { 755 setString(parameterIndex, x.toISOString()); 756 } 757 override void setDate(int parameterIndex, Date x) { 758 setString(parameterIndex, x.toISOString()); 759 } 760 override void setTime(int parameterIndex, TimeOfDay x) { 761 setString(parameterIndex, x.toISOString()); 762 } 763 override void setVariant(int parameterIndex, Variant x) { 764 if (x == null) 765 setNull(parameterIndex); 766 else if (x.convertsTo!long) 767 setLong(parameterIndex, x.get!long); 768 else if (x.convertsTo!ulong) 769 setLong(parameterIndex, x.get!ulong); 770 else if (x.convertsTo!double) 771 setDouble(parameterIndex, x.get!double); 772 else if (x.convertsTo!(byte[])) 773 setBytes(parameterIndex, x.get!(byte[])); 774 else if (x.convertsTo!(ubyte[])) 775 setUbytes(parameterIndex, x.get!(ubyte[])); 776 else if (x.convertsTo!DateTime) 777 setDateTime(parameterIndex, x.get!DateTime); 778 else if (x.convertsTo!Date) 779 setDate(parameterIndex, x.get!Date); 780 else if (x.convertsTo!TimeOfDay) 781 setTime(parameterIndex, x.get!TimeOfDay); 782 else 783 setString(parameterIndex, x.toString()); 784 } 785 override void setNull(int parameterIndex) { 786 checkClosed(); 787 lock(); 788 scope(exit) unlock(); 789 checkIndex(parameterIndex); 790 sqlite3_bind_null(stmt, parameterIndex); 791 paramIsSet[parameterIndex - 1] = true; 792 } 793 override void setNull(int parameterIndex, int sqlType) { 794 setNull(parameterIndex); 795 } 796 797 override string toString() { 798 return this.query; 799 } 800 } 801 802 class SQLITEResultSet : ResultSetImpl { 803 private SQLITEStatement stmt; 804 private sqlite3_stmt * rs; 805 ResultSetMetaData metadata; 806 private bool closed; 807 private int currentRowIndex; 808 // private int rowCount; 809 private int[string] columnMap; 810 private bool lastIsNull; 811 private int columnCount; 812 813 private bool _last; 814 private bool _first; 815 816 // checks index, updates lastIsNull, returns column type 817 int checkIndex(int columnIndex) { 818 enforceHelper!SQLException(columnIndex >= 1 && columnIndex <= columnCount, "Column index out of bounds: " ~ to!string(columnIndex)); 819 int res = sqlite3_column_type(rs, columnIndex - 1); 820 lastIsNull = (res == SQLITE_NULL); 821 return res; 822 } 823 824 void checkClosed() { 825 if (closed) 826 throw new SQLException("Result set is already closed"); 827 } 828 829 public: 830 831 void lock() { 832 stmt.lock(); 833 } 834 835 void unlock() { 836 stmt.unlock(); 837 } 838 839 this(SQLITEStatement stmt, sqlite3_stmt * rs, ResultSetMetaData metadata) { 840 this.stmt = stmt; 841 this.rs = rs; 842 this.metadata = metadata; 843 closed = false; 844 // The column count cannot use sqlite3_data_count, because sqlite3_step has not yet been used with this result set. 845 // Because there are not results ready to return, sqlite3_data_count will return 0 causing no columns to be mapped. 846 this.columnCount = metadata.getColumnCount(); 847 for (int i=0; i<columnCount; i++) { 848 columnMap[metadata.getColumnName(i + 1)] = i; 849 } 850 currentRowIndex = -1; 851 _first = true; 852 } 853 854 void onStatementClosed() { 855 closed = true; 856 } 857 string decodeTextBlob(ubyte[] data) { 858 char[] res = new char[data.length]; 859 foreach (i, ch; data) { 860 res[i] = cast(char)ch; 861 } 862 return to!string(res); 863 } 864 865 // ResultSet interface implementation 866 867 //Retrieves the number, types and properties of this ResultSet object's columns 868 override ResultSetMetaData getMetaData() { 869 checkClosed(); 870 lock(); 871 scope(exit) unlock(); 872 return metadata; 873 } 874 875 override void close() { 876 if (closed) 877 return; 878 checkClosed(); 879 lock(); 880 scope(exit) unlock(); 881 stmt.closeResultSet(); 882 closed = true; 883 } 884 override bool first() { 885 checkClosed(); 886 lock(); 887 scope(exit) unlock(); 888 throw new SQLException("Not implemented"); 889 } 890 override bool isFirst() { 891 checkClosed(); 892 lock(); 893 scope(exit) unlock(); 894 return _first; 895 } 896 override bool isLast() { 897 checkClosed(); 898 lock(); 899 scope(exit) unlock(); 900 return _last; 901 } 902 903 override bool next() { 904 checkClosed(); 905 lock(); 906 scope(exit) unlock(); 907 908 if (_first) { 909 _first = false; 910 //writeln("next() first time invocation, columnCount=" ~ to!string(columnCount)); 911 //return columnCount > 0; 912 } 913 914 int res = sqlite3_step(rs); 915 if (res == SQLITE_DONE) { 916 _last = true; 917 columnCount = sqlite3_data_count(rs); 918 //writeln("sqlite3_step = SQLITE_DONE columnCount=" ~ to!string(columnCount)); 919 // end of data 920 return columnCount > 0; 921 } else if (res == SQLITE_ROW) { 922 //writeln("sqlite3_step = SQLITE_ROW"); 923 // have a row 924 currentRowIndex++; 925 columnCount = sqlite3_data_count(rs); 926 return true; 927 } else { 928 enforceHelper!SQLException(false, "Error #" ~ to!string(res) ~ " while reading query result: " ~ copyCString(sqlite3_errmsg(stmt.conn.getConnection()))); 929 return false; 930 } 931 } 932 933 override int findColumn(string columnName) { 934 checkClosed(); 935 lock(); 936 scope(exit) unlock(); 937 int * p = (columnName in columnMap); 938 if (!p) 939 throw new SQLException("Column " ~ columnName ~ " not found"); 940 return *p + 1; 941 } 942 943 override bool getBoolean(int columnIndex) { 944 return getLong(columnIndex) != 0; 945 } 946 override ubyte getUbyte(int columnIndex) { 947 return cast(ubyte)getLong(columnIndex); 948 } 949 override byte getByte(int columnIndex) { 950 return cast(byte)getLong(columnIndex); 951 } 952 override short getShort(int columnIndex) { 953 return cast(short)getLong(columnIndex); 954 } 955 override ushort getUshort(int columnIndex) { 956 return cast(ushort)getLong(columnIndex); 957 } 958 override int getInt(int columnIndex) { 959 return cast(int)getLong(columnIndex); 960 } 961 override uint getUint(int columnIndex) { 962 return cast(uint)getLong(columnIndex); 963 } 964 override long getLong(int columnIndex) { 965 checkClosed(); 966 checkIndex(columnIndex); 967 lock(); 968 scope(exit) unlock(); 969 auto v = sqlite3_column_int64(rs, columnIndex - 1); 970 return v; 971 } 972 override ulong getUlong(int columnIndex) { 973 return cast(ulong)getLong(columnIndex); 974 } 975 override double getDouble(int columnIndex) { 976 checkClosed(); 977 checkIndex(columnIndex); 978 lock(); 979 scope(exit) unlock(); 980 auto v = sqlite3_column_double(rs, columnIndex - 1); 981 return v; 982 } 983 override float getFloat(int columnIndex) { 984 return cast(float)getDouble(columnIndex); 985 } 986 override byte[] getBytes(int columnIndex) { 987 checkClosed(); 988 checkIndex(columnIndex); 989 lock(); 990 scope(exit) unlock(); 991 const byte * bytes = cast(const byte *)sqlite3_column_blob(rs, columnIndex - 1); 992 int len = sqlite3_column_bytes(rs, columnIndex - 1); 993 byte[] res = new byte[len]; 994 for (int i=0; i<len; i++) 995 res[i] = bytes[i]; 996 return res; 997 } 998 override ubyte[] getUbytes(int columnIndex) { 999 checkClosed(); 1000 checkIndex(columnIndex); 1001 lock(); 1002 scope(exit) unlock(); 1003 const ubyte * bytes = cast(const ubyte *)sqlite3_column_blob(rs, columnIndex - 1); 1004 int len = sqlite3_column_bytes(rs, columnIndex - 1); 1005 ubyte[] res = new ubyte[len]; 1006 for (int i=0; i<len; i++) 1007 res[i] = bytes[i]; 1008 return res; 1009 } 1010 override string getString(int columnIndex) { 1011 checkClosed(); 1012 checkIndex(columnIndex); 1013 lock(); 1014 scope(exit) unlock(); 1015 const char * bytes = cast(const char *)sqlite3_column_text(rs, columnIndex - 1); 1016 int len = sqlite3_column_bytes(rs, columnIndex - 1); 1017 char[] res = new char[len]; 1018 for (int i=0; i<len; i++) 1019 res[i] = bytes[i]; 1020 return cast(string)res; 1021 } 1022 1023 override SysTime getSysTime(int columnIndex) { 1024 immutable string s = getString(columnIndex); 1025 if (s is null) 1026 return Clock.currTime(); 1027 try { 1028 return parseSysTime(s); 1029 } catch (Throwable e) { 1030 throw new SQLException("Cannot convert '" ~ s ~ "' to SysTime"); 1031 } 1032 } 1033 1034 override DateTime getDateTime(int columnIndex) { 1035 string s = getString(columnIndex); 1036 if (s is null) 1037 return cast(DateTime) Clock.currTime(); 1038 try { 1039 return fromResultSet(s); 1040 } catch (Throwable e) { 1041 throw new SQLException("Cannot convert '" ~ s ~ "' to DateTime"); 1042 } 1043 } 1044 override Date getDate(int columnIndex) { 1045 string s = getString(columnIndex); 1046 Date dt; 1047 if (s is null) 1048 return dt; 1049 try { 1050 // date is likely to be either YYYY-MM-DD or YYYY-MMM-DD. 1051 // In D we can easily handle the following x3 formats: 1052 final switch (s.length) { 1053 case 8: 1054 return Date.fromISOString(s); // ISO: YYYYMMDD 1055 case 10: 1056 return Date.fromISOExtString(s); // ISO extended: YYYY-MM-DD 1057 case 11: 1058 return Date.fromSimpleString(s); // YYYY-MMM-DD 1059 } 1060 } catch (Throwable e) { 1061 throw new SQLException("Cannot convert string to Date - " ~ s); 1062 } 1063 } 1064 override TimeOfDay getTime(int columnIndex) { 1065 string s = getString(columnIndex); 1066 TimeOfDay dt; 1067 if (s is null) 1068 return dt; 1069 try { 1070 return fromResultSet(s).timeOfDay; 1071 } catch (Throwable e) { 1072 throw new SQLException("Cannot convert string to TimeOfDay - " ~ s); 1073 } 1074 } 1075 1076 override Variant getVariant(int columnIndex) { 1077 checkClosed(); 1078 int type = checkIndex(columnIndex); 1079 lock(); 1080 scope(exit) unlock(); 1081 Variant v = null; 1082 if (lastIsNull) 1083 return v; 1084 switch (type) { 1085 case SQLITE_INTEGER: 1086 v = getLong(columnIndex); 1087 break; 1088 case SQLITE_FLOAT: 1089 v = getDouble(columnIndex); 1090 break; 1091 case SQLITE3_TEXT: 1092 v = getString(columnIndex); 1093 break; 1094 case SQLITE_BLOB: 1095 v = getUbytes(columnIndex); 1096 break; 1097 default: 1098 break; 1099 } 1100 return v; 1101 } 1102 override bool wasNull() { 1103 checkClosed(); 1104 lock(); 1105 scope(exit) unlock(); 1106 return lastIsNull; 1107 } 1108 override bool isNull(int columnIndex) { 1109 checkClosed(); 1110 lock(); 1111 scope(exit) unlock(); 1112 checkIndex(columnIndex); 1113 return lastIsNull; 1114 } 1115 1116 //Retrieves the Statement object that produced this ResultSet object. 1117 override Statement getStatement() { 1118 checkClosed(); 1119 lock(); 1120 scope(exit) unlock(); 1121 return stmt; 1122 } 1123 1124 //Retrieves the current row number 1125 override int getRow() { 1126 checkClosed(); 1127 lock(); 1128 scope(exit) unlock(); 1129 if (currentRowIndex <0) 1130 return 0; 1131 return currentRowIndex + 1; 1132 } 1133 1134 //Retrieves the fetch size for this ResultSet object. 1135 // override ulong getFetchSize() { 1136 // checkClosed(); 1137 // lock(); 1138 // scope(exit) unlock(); 1139 // return -1; 1140 // } 1141 } 1142 1143 1144 // sample URL: 1145 // mysql://localhost:3306/DatabaseName 1146 1147 //String url = "jdbc:postgresql://localhost/test"; 1148 //Properties props = new Properties(); 1149 //props.setProperty("user","fred"); 1150 //props.setProperty("password","secret"); 1151 //Connection conn = DriverManager.getConnection(url, props); 1152 class SQLITEDriver : Driver { 1153 // helper function 1154 public static string generateUrl(string host, ushort port, string dbname) { 1155 return "sqlite://" ~ host ~ ":" ~ to!string(port) ~ "/" ~ dbname; 1156 } 1157 public static string[string] setUserAndPassword(string username, string password) { 1158 string[string] params; 1159 params["user"] = username; 1160 params["password"] = password; 1161 return params; 1162 } 1163 override ddbc.core.Connection connect(string url, string[string] params) { 1164 //writeln("SQLITEDriver.connect " ~ url); 1165 return new SQLITEConnection(url, params); 1166 } 1167 } 1168 1169 unittest { 1170 if (SQLITE_TESTS_ENABLED) { 1171 1172 import std.conv; 1173 DataSource ds = createUnitTestSQLITEDataSource(); 1174 //writeln("trying to open connection"); 1175 auto conn = ds.getConnection(); 1176 //writeln("connection is opened"); 1177 assert(conn !is null); 1178 scope(exit) conn.close(); 1179 { 1180 //writeln("dropping table"); 1181 Statement stmt = conn.createStatement(); 1182 scope(exit) stmt.close(); 1183 stmt.executeUpdate("DROP TABLE IF EXISTS t1"); 1184 } 1185 { 1186 //writeln("creating table"); 1187 Statement stmt = conn.createStatement(); 1188 scope(exit) stmt.close(); 1189 stmt.executeUpdate("CREATE TABLE IF NOT EXISTS t1 (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL, flags int null)"); 1190 } 1191 { 1192 //writeln("populating table"); 1193 PreparedStatement stmt = conn.prepareStatement("INSERT INTO t1 (name) VALUES ('test1')"); 1194 scope(exit) stmt.close(); 1195 Variant id = 0; 1196 assert(stmt.executeUpdate(id) == 1); 1197 assert(id.get!long > 0); 1198 } 1199 { 1200 //writeln("populating table"); 1201 PreparedStatement stmt = conn.prepareStatement("INSERT INTO t1 (name) VALUES ('test2')"); 1202 scope(exit) stmt.close(); 1203 Variant id = 0; 1204 assert(stmt.executeUpdate(id) == 1); 1205 assert(id.get!long > 0); 1206 } 1207 { 1208 //writeln("reading table"); 1209 Statement stmt = conn.createStatement(); 1210 scope(exit) stmt.close(); 1211 ResultSet rs = stmt.executeQuery("SELECT id, name, flags FROM t1"); 1212 assert(rs.getMetaData().getColumnCount() == 3); 1213 assert(rs.getMetaData().getColumnName(1) == "id"); 1214 assert(rs.getMetaData().getColumnName(2) == "name"); 1215 assert(rs.getMetaData().getColumnName(3) == "flags"); 1216 scope(exit) rs.close(); 1217 //writeln("id" ~ "\t" ~ "name"); 1218 while (rs.next()) { 1219 long id = rs.getLong(1); 1220 string name = rs.getString(2); 1221 assert(rs.isNull(3)); 1222 //writeln("" ~ to!string(id) ~ "\t" ~ name); 1223 } 1224 } 1225 { 1226 //writeln("reading table with parameter id=1"); 1227 PreparedStatement stmt = conn.prepareStatement("SELECT id, name, flags FROM t1 WHERE id = ?"); 1228 scope(exit) stmt.close(); 1229 assert(stmt.getMetaData().getColumnCount() == 3); 1230 assert(stmt.getMetaData().getColumnName(1) == "id"); 1231 assert(stmt.getMetaData().getColumnName(2) == "name"); 1232 assert(stmt.getMetaData().getColumnName(3) == "flags"); 1233 stmt.setLong(1, 1); 1234 { 1235 ResultSet rs = stmt.executeQuery(); 1236 scope(exit) rs.close(); 1237 //writeln("id" ~ "\t" ~ "name"); 1238 while (rs.next()) { 1239 long id = rs.getLong(1); 1240 string name = rs.getString(2); 1241 assert(rs.isNull(3)); 1242 //writeln("" ~ to!string(id) ~ "\t" ~ name); 1243 } 1244 } 1245 //writeln("changing parameter id=2"); 1246 stmt.setLong(1, 2); 1247 { 1248 ResultSet rs = stmt.executeQuery(); 1249 scope(exit) rs.close(); 1250 //writeln("id" ~ "\t" ~ "name"); 1251 while (rs.next()) { 1252 long id = rs.getLong(1); 1253 string name = rs.getString(2); 1254 //writeln("" ~ to!string(id) ~ "\t" ~ name); 1255 } 1256 } 1257 } 1258 } 1259 } 1260 1261 __gshared static this() { 1262 // register SQLiteDriver 1263 import ddbc.common; 1264 DriverFactory.registerDriverFactory("sqlite", delegate() { return new SQLITEDriver(); }); 1265 } 1266 1267 1268 } else { // version(USE_SQLITE) 1269 version(unittest) { 1270 immutable bool SQLITE_TESTS_ENABLED = false; 1271 } 1272 }