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