ddbc.drivers.sqliteddbc source code

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;
29     import std.exception;
30     import std.stdio;
31     import std.string;
32     import std.variant;
33     import core.sync.mutex;
34     import ddbc.common;
35     import ddbc.core;
36     //import ddbc.drivers.sqlite;
37     import ddbc.drivers.utils;
38     import etc.c.sqlite3;
39 
40 
41     version (Windows) {
42         pragma (lib, "sqlite3.lib");
43     } else version (linux) {
44         pragma (lib, "sqlite3");
45     } else version (Posix) {
46         pragma (lib, "libsqlite3.so");
47     } else version (darwin) {
48         pragma (lib, "libsqlite3.so");
49     } else {
50         pragma (msg, "You will need to manually link in the SQLite library.");
51     } 
52 
53     version(unittest) {
54         /*
55             To allow unit tests using PostgreSQL server,
56          */
57         /// change to false to disable tests on real PostgreSQL server
58         immutable bool SQLITE_TESTS_ENABLED = true;
59         /// change parameters if necessary
60         const string SQLITE_UNITTEST_FILENAME = "ddbctest.sqlite";
61 
62         static if (SQLITE_TESTS_ENABLED) {
63             /// use this data source for tests
64             DataSource createUnitTestSQLITEDataSource() {
65                 SQLITEDriver driver = new SQLITEDriver();
66                 string[string] params;
67                 return new ConnectionPoolDataSourceImpl(driver, SQLITE_UNITTEST_FILENAME, params);
68             }
69         }
70     }
71 
72     class SQLITEConnection : ddbc.core.Connection {
73     private:
74         string filename;
75 
76         sqlite3 * conn;
77 
78         bool closed;
79         bool autocommit;
80         Mutex mutex;
81         
82         
83         SQLITEStatement [] activeStatements;
84         
85         void closeUnclosedStatements() {
86             SQLITEStatement [] list = activeStatements.dup;
87             foreach(stmt; list) {
88                 stmt.close();
89             }
90         }
91         
92         void checkClosed() {
93             if (closed)
94                 throw new SQLException("Connection is already closed");
95         }
96         
97     public:
98 
99         private string getError() {
100             return copyCString(sqlite3_errmsg(conn));
101         }
102 
103         void lock() {
104             mutex.lock();
105         }
106         
107         void unlock() {
108             mutex.unlock();
109         }
110         
111         sqlite3 * getConnection() { return conn; }
112         
113         
114         void onStatementClosed(SQLITEStatement stmt) {
115             foreach(index, item; activeStatements) {
116                 if (item == stmt) {
117                     remove(activeStatements, index);
118                     return;
119                 }
120             }
121         }
122         
123         this(string url, string[string] params) {
124             mutex = new Mutex();
125             if (url.startsWith("sqlite::"))
126                 url = url[8 .. $];
127             this.filename = url;
128             //writeln("trying to connect");
129             int res = sqlite3_open(toStringz(filename), &conn);
130             if(res != SQLITE_OK)
131                 throw new SQLException("SQLITE Error " ~ to!string(res) ~ " while trying to open DB " ~ filename ~ " : " ~ getError());
132             assert(conn !is null);
133             closed = false;
134             setAutoCommit(true);
135         }
136         
137         override void close() {
138             checkClosed();
139             
140             lock();
141             scope(exit) unlock();
142             
143             closeUnclosedStatements();
144             int res = sqlite3_close(conn);
145             if (res != SQLITE_OK)
146                 throw new SQLException("SQLITE Error " ~ to!string(res) ~ " while trying to close DB " ~ filename ~ " : " ~ getError());
147             closed = true;
148         }
149         
150         override void commit() {
151             checkClosed();
152             
153             lock();
154             scope(exit) unlock();
155             
156             Statement stmt = createStatement();
157             scope(exit) stmt.close();
158             stmt.executeUpdate("COMMIT");
159         }
160         
161         override Statement createStatement() {
162             checkClosed();
163             
164             lock();
165             scope(exit) unlock();
166             
167             SQLITEStatement stmt = new SQLITEStatement(this);
168             activeStatements ~= stmt;
169             return stmt;
170         }
171         
172         PreparedStatement prepareStatement(string sql) {
173             checkClosed();
174             
175             lock();
176             scope(exit) unlock();
177             
178             SQLITEPreparedStatement stmt = new SQLITEPreparedStatement(this, sql);
179             activeStatements ~= stmt;
180             return stmt;
181         }
182         
183         override string getCatalog() {
184             return "default";
185         }
186         
187         /// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work.
188         override void setCatalog(string catalog) {
189             checkClosed();
190             throw new SQLException("Not implemented");
191         }
192         
193         override bool isClosed() {
194             return closed;
195         }
196         
197         override void rollback() {
198             checkClosed();
199             
200             lock();
201             scope(exit) unlock();
202             
203             Statement stmt = createStatement();
204             scope(exit) stmt.close();
205             //TODO:
206             //stmt.executeUpdate("ROLLBACK");
207         }
208         override bool getAutoCommit() {
209             return autocommit;
210         }
211         override void setAutoCommit(bool autoCommit) {
212             checkClosed();
213             if (this.autocommit == autoCommit)
214                 return;
215             lock();
216             scope(exit) unlock();
217             
218             Statement stmt = createStatement();
219             scope(exit) stmt.close();
220             //TODO:
221             //stmt.executeUpdate("SET autocommit = " ~ (autoCommit ? "ON" : "OFF"));
222             this.autocommit = autoCommit;
223         }
224     }
225 
226     class SQLITEStatement : Statement {
227     private:
228         SQLITEConnection conn;
229         //  Command * cmd;
230         //  ddbc.drivers.mysql.ResultSet rs;
231         SQLITEResultSet resultSet;
232         
233         bool closed;
234         
235     public:
236         void checkClosed() {
237             enforceEx!SQLException(!closed, "Statement is already closed");
238         }
239         
240         void lock() {
241             conn.lock();
242         }
243         
244         void unlock() {
245             conn.unlock();
246         }
247         
248         this(SQLITEConnection conn) {
249             this.conn = conn;
250         }
251         
252     public:
253         SQLITEConnection getConnection() {
254             checkClosed();
255             return conn;
256         }
257 
258         private PreparedStatement _currentStatement;
259         private ResultSet _currentResultSet;
260 
261         private void closePreparedStatement() {
262             if (_currentResultSet !is null) {
263                 _currentResultSet.close();
264                 _currentResultSet = null;
265             }
266             if (_currentStatement !is null) {
267                 _currentStatement.close();
268                 _currentStatement = null;
269             }
270         }
271 
272         override ddbc.core.ResultSet executeQuery(string query) {
273             closePreparedStatement();
274             _currentStatement = conn.prepareStatement(query);
275             _currentResultSet = _currentStatement.executeQuery();
276             return _currentResultSet;
277         }
278         
279     //    string getError() {
280     //        return copyCString(PQerrorMessage(conn.getConnection()));
281     //    }
282         
283         override int executeUpdate(string query) {
284             Variant dummy;
285             return executeUpdate(query, dummy);
286         }
287         
288         override int executeUpdate(string query, out Variant insertId) {
289             closePreparedStatement();
290             _currentStatement = conn.prepareStatement(query);
291             return _currentStatement.executeUpdate(insertId);
292         }
293         
294         override void close() {
295             checkClosed();
296             lock();
297             scope(exit) unlock();
298             closePreparedStatement();
299             closed = true;
300         }
301         
302         void closeResultSet() {
303         }
304     }
305 
306     class SQLITEPreparedStatement : SQLITEStatement, PreparedStatement {
307         string query;
308         int paramCount;
309 
310         sqlite3_stmt * stmt;
311 
312         bool done;
313         bool preparing;
314 
315         ResultSetMetaData metadata;
316         ParameterMetaData paramMetadata;
317         this(SQLITEConnection conn, string query) {
318             super(conn);
319             this.query = query;
320 
321             int res = sqlite3_prepare_v2(
322                 conn.getConnection(),            /* Database handle */
323                 toStringz(query),       /* SQL statement, UTF-8 encoded */
324                 cast(int)query.length,              /* Maximum length of zSql in bytes. */
325                 &stmt,  /* OUT: Statement handle */
326                 null     /* OUT: Pointer to unused portion of zSql */
327                 );
328             enforceEx!SQLException(res == SQLITE_OK, "Error #" ~ to!string(res) ~ " while preparing statement " ~ query ~ " : " ~ conn.getError());
329             paramMetadata = createParamMetadata();
330             paramCount = paramMetadata.getParameterCount();
331             metadata = createMetadata();
332             resetParams();
333             preparing = true;
334         }
335         bool[] paramIsSet;
336         void resetParams() {
337             paramIsSet = new bool[paramCount];
338         }
339         // before execution of query
340         private void allParamsSet() {
341             for(int i = 0; i < paramCount; i++) {
342                 enforceEx!SQLException(paramIsSet[i], "Parameter " ~ to!string(i + 1) ~ " is not set");
343             }
344             if (preparing) {
345                 preparing = false;
346             } else {
347                 closeResultSet();
348                 sqlite3_reset(stmt);
349             }
350         }
351         // before setting any parameter
352         private void checkIndex(int index) {
353             if (index < 1 || index > paramCount)
354                 throw new SQLException("Parameter index " ~ to!string(index) ~ " is out of range");
355             if (!preparing) {
356                 closeResultSet();
357                 sqlite3_reset(stmt);
358                 preparing = true;
359             }
360         }
361         ref Variant getParam(int index) {
362             throw new SQLException("Not implemented");
363             //      checkIndex(index);
364             //      return cmd.param(cast(ushort)(index - 1));
365         }
366     public:
367         SqlType sqliteToSqlType(int t) {
368             switch(t) {
369                 case SQLITE_INTEGER: return SqlType.BIGINT;
370                 case SQLITE_FLOAT: return SqlType.DOUBLE;
371                 case SQLITE3_TEXT: return SqlType.VARCHAR;
372                 case SQLITE_BLOB: return SqlType.BLOB;
373                 case SQLITE_NULL: return SqlType.NULL;
374                 default:
375                     return SqlType.BLOB;
376             }
377         }
378 
379         ResultSetMetaData createMetadata() {
380             int fieldCount = sqlite3_column_count(stmt);
381             ColumnMetadataItem[] list = new ColumnMetadataItem[fieldCount];
382             for(int i = 0; i < fieldCount; i++) {
383                 ColumnMetadataItem item = new ColumnMetadataItem();
384                 item.label = copyCString(sqlite3_column_origin_name(stmt, i));
385                 item.name = copyCString(sqlite3_column_name(stmt, i));
386                 item.schemaName = copyCString(sqlite3_column_database_name(stmt, i));
387                 item.tableName = copyCString(sqlite3_column_table_name(stmt, i));
388                 item.type = sqliteToSqlType(sqlite3_column_type(stmt, i));
389                 list[i] = item;
390             }
391             return new ResultSetMetaDataImpl(list);
392         }
393 
394         ParameterMetaData createParamMetadata() {
395             int fieldCount = sqlite3_bind_parameter_count(stmt);
396             ParameterMetaDataItem[] res = new ParameterMetaDataItem[fieldCount];
397             for(int i = 0; i < fieldCount; i++) {
398                 ParameterMetaDataItem item = new ParameterMetaDataItem();
399                 item.type = SqlType.VARCHAR;
400                 res[i] = item;
401             }
402             paramCount = fieldCount;
403             return new ParameterMetaDataImpl(res);
404         }
405 
406         override void close() {
407             checkClosed();
408             lock();
409             scope(exit) unlock();
410 
411             closeResultSet();
412             int res = sqlite3_finalize(stmt);
413             enforceEx!SQLException(res == SQLITE_OK, "Error #" ~ to!string(res) ~ " while closing prepared statement " ~ query ~ " : " ~ conn.getError());
414             closed = true;
415         }
416 
417         
418         /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed.
419         override ResultSetMetaData getMetaData() {
420             checkClosed();
421             lock();
422             scope(exit) unlock();
423             return metadata;
424         }
425         
426         /// Retrieves the number, types and properties of this PreparedStatement object's parameters.
427         override ParameterMetaData getParameterMetaData() {
428             checkClosed();
429             lock();
430             scope(exit) unlock();
431             return paramMetadata;
432         }
433 
434         override int executeUpdate(out Variant insertId) {
435             //throw new SQLException("Not implemented");
436             checkClosed();
437             lock();
438             scope(exit) unlock();
439             allParamsSet();
440 
441             int rowsAffected = 0;
442             int res = sqlite3_step(stmt);
443             if (res == SQLITE_DONE) {
444                 insertId = Variant(sqlite3_last_insert_rowid(conn.getConnection()));
445                 rowsAffected = sqlite3_changes(conn.getConnection());
446                 done = true;
447             } else if (res == SQLITE_ROW) {
448                 // row is available
449                 rowsAffected = -1;
450             } else {
451                 enforceEx!SQLException(false, "Error #" ~ to!string(res) ~ " while trying to execute prepared statement: "  ~ " : " ~ conn.getError());
452             }
453             return rowsAffected;
454         }
455         
456         override int executeUpdate() {
457             Variant insertId;
458             return executeUpdate(insertId);
459         }
460         
461         override ddbc.core.ResultSet executeQuery() {
462             checkClosed();
463             lock();
464             scope(exit) unlock();
465             allParamsSet();
466             enforceEx!SQLException(metadata.getColumnCount() > 0, "Query doesn't return result set");
467             resultSet = new SQLITEResultSet(this, stmt, getMetaData());
468             return resultSet;
469         }
470         
471         override void clearParameters() {
472             throw new SQLException("Not implemented");
473             //      checkClosed();
474             //      lock();
475             //      scope(exit) unlock();
476             //      for (int i = 1; i <= paramCount; i++)
477             //          setNull(i);
478         }
479         
480         override void setFloat(int parameterIndex, float x) {
481             setDouble(parameterIndex, x);
482         }
483         override void setDouble(int parameterIndex, double x){
484             checkClosed();
485             lock();
486             scope(exit) unlock();
487             checkIndex(parameterIndex);
488             sqlite3_bind_double(stmt, parameterIndex, x);
489             paramIsSet[parameterIndex - 1] = true;
490         }
491         override void setBoolean(int parameterIndex, bool x) {
492             setLong(parameterIndex, x ? 1 : 0);
493         }
494         override void setLong(int parameterIndex, long x) {
495             checkClosed();
496             lock();
497             scope(exit) unlock();
498             checkIndex(parameterIndex);
499             sqlite3_bind_int64(stmt, parameterIndex, x);
500             paramIsSet[parameterIndex - 1] = true;
501         }
502         override void setUlong(int parameterIndex, ulong x) {
503             setLong(parameterIndex, cast(long)x);
504         }
505         override void setInt(int parameterIndex, int x) {
506             setLong(parameterIndex, cast(long)x);
507         }
508         override void setUint(int parameterIndex, uint x) {
509             setLong(parameterIndex, cast(long)x);
510         }
511         override void setShort(int parameterIndex, short x) {
512             setLong(parameterIndex, cast(long)x);
513         }
514         override void setUshort(int parameterIndex, ushort x) {
515             setLong(parameterIndex, cast(long)x);
516         }
517         override void setByte(int parameterIndex, byte x) {
518             setLong(parameterIndex, cast(long)x);
519         }
520         override void setUbyte(int parameterIndex, ubyte x) {
521             setLong(parameterIndex, cast(long)x);
522         }
523         override void setBytes(int parameterIndex, byte[] x) {
524             checkClosed();
525             lock();
526             scope(exit) unlock();
527             checkIndex(parameterIndex);
528             if (x is null) {
529                 setNull(parameterIndex);
530                 return;
531             }
532             sqlite3_bind_blob(stmt, parameterIndex, cast(const (void *))x.ptr, cast(int)x.length, SQLITE_TRANSIENT);
533             paramIsSet[parameterIndex - 1] = true;
534         }
535         override void setUbytes(int parameterIndex, ubyte[] x) {
536             checkClosed();
537             lock();
538             scope(exit) unlock();
539             checkIndex(parameterIndex);
540             if (x is null) {
541                 setNull(parameterIndex);
542                 return;
543             }
544             sqlite3_bind_blob(stmt, parameterIndex, cast(const char *)x.ptr, cast(int)x.length, SQLITE_TRANSIENT);
545             paramIsSet[parameterIndex - 1] = true;
546         }
547         override void setString(int parameterIndex, string x) {
548             checkClosed();
549             lock();
550             scope(exit) unlock();
551             checkIndex(parameterIndex);
552             if (x is null) {
553                 setNull(parameterIndex);
554                 return;
555             }
556             sqlite3_bind_text(stmt, parameterIndex, cast(const char *)x.ptr, cast(int)x.length, SQLITE_TRANSIENT);
557             paramIsSet[parameterIndex - 1] = true;
558         }
559         override void setDateTime(int parameterIndex, DateTime x) {
560             setString(parameterIndex, x.toISOString());
561         }
562         override void setDate(int parameterIndex, Date x) {
563             setString(parameterIndex, x.toISOString());
564         }
565         override void setTime(int parameterIndex, TimeOfDay x) {
566             setString(parameterIndex, x.toISOString());
567         }
568         override void setVariant(int parameterIndex, Variant x) {
569             if (x == null)
570                 setNull(parameterIndex);
571             else if (x.convertsTo!long)
572                 setLong(parameterIndex, x.get!long);
573             else if (x.convertsTo!ulong)
574                 setLong(parameterIndex, x.get!ulong);
575             else if (x.convertsTo!double)
576                 setDouble(parameterIndex, x.get!double);
577             else if (x.convertsTo!(byte[]))
578                 setBytes(parameterIndex, x.get!(byte[]));
579             else if (x.convertsTo!(ubyte[]))
580                 setUbytes(parameterIndex, x.get!(ubyte[]));
581             else if (x.convertsTo!DateTime)
582                 setDateTime(parameterIndex, x.get!DateTime);
583             else if (x.convertsTo!Date)
584                 setDate(parameterIndex, x.get!Date);
585             else if (x.convertsTo!TimeOfDay)
586                 setTime(parameterIndex, x.get!TimeOfDay);
587             else
588                 setString(parameterIndex, x.toString());
589         }
590         override void setNull(int parameterIndex) {
591             checkClosed();
592             lock();
593             scope(exit) unlock();
594             checkIndex(parameterIndex);
595             sqlite3_bind_null(stmt, parameterIndex);
596             paramIsSet[parameterIndex - 1] = true;
597         }
598         override void setNull(int parameterIndex, int sqlType) {
599             setNull(parameterIndex);
600         }
601     }
602 
603     class SQLITEResultSet : ResultSetImpl {
604         private SQLITEStatement stmt;
605         private sqlite3_stmt * rs;
606         ResultSetMetaData metadata;
607         private bool closed;
608         private int currentRowIndex;
609 //        private int rowCount;
610         private int[string] columnMap;
611         private bool lastIsNull;
612         private int columnCount;
613 
614         private bool _last;
615         private bool _first;
616 
617         // checks index, updates lastIsNull, returns column type
618         int checkIndex(int columnIndex) {
619             enforceEx!SQLException(columnIndex >= 1 && columnIndex <= columnCount, "Column index out of bounds: " ~ to!string(columnIndex));
620             int res = sqlite3_column_type(rs, columnIndex - 1);
621             lastIsNull = (res == SQLITE_NULL);
622             return res;
623         }
624         
625         void checkClosed() {
626             if (closed)
627                 throw new SQLException("Result set is already closed");
628         }
629         
630     public:
631         
632         void lock() {
633             stmt.lock();
634         }
635         
636         void unlock() {
637             stmt.unlock();
638         }
639         
640         this(SQLITEStatement stmt, sqlite3_stmt * rs, ResultSetMetaData metadata) {
641             this.stmt = stmt;
642             this.rs = rs;
643             this.metadata = metadata;
644             closed = false;
645             this.columnCount = sqlite3_data_count(rs); //metadata.getColumnCount();
646             for (int i=0; i<columnCount; i++) {
647                 columnMap[metadata.getColumnName(i + 1)] = i;
648             }
649             currentRowIndex = -1;
650             _first = true;
651         }
652         
653         void onStatementClosed() {
654             closed = true;
655         }
656         string decodeTextBlob(ubyte[] data) {
657             char[] res = new char[data.length];
658             foreach (i, ch; data) {
659                 res[i] = cast(char)ch;
660             }
661             return to!string(res);
662         }
663         
664         // ResultSet interface implementation
665         
666         //Retrieves the number, types and properties of this ResultSet object's columns
667         override ResultSetMetaData getMetaData() {
668             checkClosed();
669             lock();
670             scope(exit) unlock();
671             return metadata;
672         }
673         
674         override void close() {
675             if (closed)
676                 return;
677             checkClosed();
678             lock();
679             scope(exit) unlock();
680             stmt.closeResultSet();
681             closed = true;
682         }
683         override bool first() {
684             checkClosed();
685             lock();
686             scope(exit) unlock();
687             throw new SQLException("Not implemented");
688         }
689         override bool isFirst() {
690             checkClosed();
691             lock();
692             scope(exit) unlock();
693             return _first;
694         }
695         override bool isLast() {
696             checkClosed();
697             lock();
698             scope(exit) unlock();
699             return _last;
700         }
701 
702         override bool next() {
703             checkClosed();
704             lock();
705             scope(exit) unlock();
706 
707             if (_first) {
708                 _first = false;
709                 //writeln("next() first time invocation, columnCount=" ~ to!string(columnCount));
710                 //return columnCount > 0;
711             }
712 
713             int res = sqlite3_step(rs);
714             if (res == SQLITE_DONE) {
715                 _last = true;
716                 columnCount = sqlite3_data_count(rs);
717                 //writeln("sqlite3_step = SQLITE_DONE columnCount=" ~ to!string(columnCount));
718                 // end of data
719                 return columnCount > 0;
720             } else if (res == SQLITE_ROW) {
721                 //writeln("sqlite3_step = SQLITE_ROW");
722                 // have a row
723                 currentRowIndex++;
724                 columnCount = sqlite3_data_count(rs);
725                 return true;
726             } else {
727                 enforceEx!SQLException(false, "Error #" ~ to!string(res) ~ " while reading query result: " ~ copyCString(sqlite3_errmsg(stmt.conn.getConnection())));
728                 return false;
729             }
730         }
731         
732         override int findColumn(string columnName) {
733             checkClosed();
734             lock();
735             scope(exit) unlock();
736             int * p = (columnName in columnMap);
737             if (!p)
738                 throw new SQLException("Column " ~ columnName ~ " not found");
739             return *p + 1;
740         }
741         
742         override bool getBoolean(int columnIndex) {
743             return getLong(columnIndex) != 0;
744         }
745         override ubyte getUbyte(int columnIndex) {
746             return cast(ubyte)getLong(columnIndex);
747         }
748         override byte getByte(int columnIndex) {
749             return cast(byte)getLong(columnIndex);
750         }
751         override short getShort(int columnIndex) {
752             return cast(short)getLong(columnIndex);
753         }
754         override ushort getUshort(int columnIndex) {
755             return cast(ushort)getLong(columnIndex);
756         }
757         override int getInt(int columnIndex) {
758             return cast(int)getLong(columnIndex);
759         }
760         override uint getUint(int columnIndex) {
761             return cast(uint)getLong(columnIndex);
762         }
763         override long getLong(int columnIndex) {
764             checkClosed();
765             checkIndex(columnIndex);
766             lock();
767             scope(exit) unlock();
768             auto v = sqlite3_column_int64(rs, columnIndex - 1);
769             return v;
770         }
771         override ulong getUlong(int columnIndex) {
772             return cast(ulong)getLong(columnIndex);
773         }
774         override double getDouble(int columnIndex) {
775             checkClosed();
776             checkIndex(columnIndex);
777             lock();
778             scope(exit) unlock();
779             auto v = sqlite3_column_double(rs, columnIndex - 1);
780             return v;
781         }
782         override float getFloat(int columnIndex) {
783             return cast(float)getDouble(columnIndex);
784         }
785         override byte[] getBytes(int columnIndex) {
786             checkClosed();
787             checkIndex(columnIndex);
788             lock();
789             scope(exit) unlock();
790             const byte * bytes = cast(const byte *)sqlite3_column_blob(rs, columnIndex - 1);
791             int len = sqlite3_column_bytes(rs, columnIndex - 1);
792             byte[] res = new byte[len];
793             for (int i=0; i<len; i++)
794                 res[i] = bytes[i];
795             return res;
796         }
797         override ubyte[] getUbytes(int columnIndex) {
798             checkClosed();
799             checkIndex(columnIndex);
800             lock();
801             scope(exit) unlock();
802             const ubyte * bytes = cast(const ubyte *)sqlite3_column_blob(rs, columnIndex - 1);
803             int len = sqlite3_column_bytes(rs, columnIndex - 1);
804             ubyte[] res = new ubyte[len];
805             for (int i=0; i<len; i++)
806                 res[i] = bytes[i];
807             return res;
808         }
809         override string getString(int columnIndex) {
810             checkClosed();
811             checkIndex(columnIndex);
812             lock();
813             scope(exit) unlock();
814             const char * bytes = cast(const char *)sqlite3_column_text(rs, columnIndex - 1);
815             int len = sqlite3_column_bytes(rs, columnIndex - 1);
816             char[] res = new char[len];
817             for (int i=0; i<len; i++)
818                 res[i] = bytes[i];
819             return cast(string)res;
820         }
821         override DateTime getDateTime(int columnIndex) {
822             string s = getString(columnIndex);
823             DateTime dt;
824             if (s is null)
825                 return dt;
826             try {
827                 return DateTime.fromISOString(s);
828             } catch (Throwable e) {
829                 throw new SQLException("Cannot convert string to DateTime - " ~ s);
830             }
831         }
832         override Date getDate(int columnIndex) {
833             string s = getString(columnIndex);
834             Date dt;
835             if (s is null)
836                 return dt;
837             try {
838                 return Date.fromISOString(s);
839             } catch (Throwable e) {
840                 throw new SQLException("Cannot convert string to DateTime - " ~ s);
841             }
842         }
843         override TimeOfDay getTime(int columnIndex) {
844             string s = getString(columnIndex);
845             TimeOfDay dt;
846             if (s is null)
847                 return dt;
848             try {
849                 return TimeOfDay.fromISOString(s);
850             } catch (Throwable e) {
851                 throw new SQLException("Cannot convert string to DateTime - " ~ s);
852             }
853         }
854         
855         override Variant getVariant(int columnIndex) {
856             checkClosed();
857             int type = checkIndex(columnIndex);
858             lock();
859             scope(exit) unlock();
860             Variant v = null;
861             if (lastIsNull)
862                 return v;
863             switch (type) {
864                 case SQLITE_INTEGER:
865                     v = getLong(columnIndex);
866                     break;
867                 case SQLITE_FLOAT:
868                     v = getDouble(columnIndex);
869                     break;
870                 case SQLITE3_TEXT:
871                     v = getString(columnIndex);
872                     break;
873                 case SQLITE_BLOB:
874                     v = getUbytes(columnIndex);
875                     break;
876                 default:
877                     break;
878             }
879             return v;
880         }
881         override bool wasNull() {
882             checkClosed();
883             lock();
884             scope(exit) unlock();
885             return lastIsNull;
886         }
887         override bool isNull(int columnIndex) {
888             checkClosed();
889             lock();
890             scope(exit) unlock();
891             checkIndex(columnIndex);
892             return lastIsNull;
893         }
894         
895         //Retrieves the Statement object that produced this ResultSet object.
896         override Statement getStatement() {
897             checkClosed();
898             lock();
899             scope(exit) unlock();
900             return stmt;
901         }
902         
903         //Retrieves the current row number
904         override int getRow() {
905             checkClosed();
906             lock();
907             scope(exit) unlock();
908             if (currentRowIndex <0)
909                 return 0;
910             return currentRowIndex + 1;
911         }
912         
913         //Retrieves the fetch size for this ResultSet object.
914         override int getFetchSize() {
915             checkClosed();
916             lock();
917             scope(exit) unlock();
918             return -1;
919         }
920     }
921 
922 
923     // sample URL:
924     // mysql://localhost:3306/DatabaseName
925 
926     //String url = "jdbc:postgresql://localhost/test";
927     //Properties props = new Properties();
928     //props.setProperty("user","fred");
929     //props.setProperty("password","secret");
930     //props.setProperty("ssl","true");
931     //Connection conn = DriverManager.getConnection(url, props);
932     class SQLITEDriver : Driver {
933         // helper function
934         public static string generateUrl(string host, ushort port, string dbname) {
935             return "postgresql://" ~ host ~ ":" ~ to!string(port) ~ "/" ~ dbname;
936         }
937         public static string[string] setUserAndPassword(string username, string password) {
938             string[string] params;
939             params["user"] = username;
940             params["password"] = password;
941             params["ssl"] = "true";
942             return params;
943         }
944         override ddbc.core.Connection connect(string url, string[string] params) {
945             //writeln("SQLITEDriver.connect " ~ url);
946             return new SQLITEConnection(url, params);
947         }
948     }
949 
950     unittest {
951         if (SQLITE_TESTS_ENABLED) {
952             
953             import std.conv;
954             DataSource ds = createUnitTestSQLITEDataSource();
955             //writeln("trying to open connection");        
956             auto conn = ds.getConnection();
957             //writeln("connection is opened");        
958             assert(conn !is null);
959             scope(exit) conn.close();
960             {
961                 //writeln("dropping table");
962                 Statement stmt = conn.createStatement();
963                 scope(exit) stmt.close();
964                 stmt.executeUpdate("DROP TABLE IF EXISTS t1");
965             }
966             {
967                 //writeln("creating table");
968                 Statement stmt = conn.createStatement();
969                 scope(exit) stmt.close();
970                 stmt.executeUpdate("CREATE TABLE IF NOT EXISTS t1 (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL, flags int null)");
971             }
972             {
973                 //writeln("populating table");
974                 PreparedStatement stmt = conn.prepareStatement("INSERT INTO t1 (name) VALUES ('test1'), ('test2')");
975                 scope(exit) stmt.close();
976                 Variant id = 0;
977                 assert(stmt.executeUpdate(id) == 2);
978                 assert(id.get!long > 0);
979             }
980             {
981                 //writeln("reading table");
982                 Statement stmt = conn.createStatement();
983                 scope(exit) stmt.close();
984                 ResultSet rs = stmt.executeQuery("SELECT id, name, flags FROM t1");
985                 assert(rs.getMetaData().getColumnCount() == 3);
986                 assert(rs.getMetaData().getColumnName(1) == "id");
987                 assert(rs.getMetaData().getColumnName(2) == "name");
988                 assert(rs.getMetaData().getColumnName(3) == "flags");
989                 scope(exit) rs.close();
990                 //writeln("id" ~ "\t" ~ "name");
991                 while (rs.next()) {
992                     long id = rs.getLong(1);
993                     string name = rs.getString(2);
994                     assert(rs.isNull(3));
995                     //writeln("" ~ to!string(id) ~ "\t" ~ name);
996                 }
997             }
998             {
999                 //writeln("reading table with parameter id=1");
1000                 PreparedStatement stmt = conn.prepareStatement("SELECT id, name, flags FROM t1 WHERE id = ?");
1001                 scope(exit) stmt.close();
1002                 assert(stmt.getMetaData().getColumnCount() == 3);
1003                 assert(stmt.getMetaData().getColumnName(1) == "id");
1004                 assert(stmt.getMetaData().getColumnName(2) == "name");
1005                 assert(stmt.getMetaData().getColumnName(3) == "flags");
1006                 stmt.setLong(1, 1);
1007                 {
1008                     ResultSet rs = stmt.executeQuery();
1009                     scope(exit) rs.close();
1010                     //writeln("id" ~ "\t" ~ "name");
1011                     while (rs.next()) {
1012                         long id = rs.getLong(1);
1013                         string name = rs.getString(2);
1014                         assert(rs.isNull(3));
1015                         //writeln("" ~ to!string(id) ~ "\t" ~ name);
1016                     }
1017                 }
1018                 //writeln("changing parameter id=2");
1019                 stmt.setLong(1, 2);
1020                 {
1021                     ResultSet rs = stmt.executeQuery();
1022                     scope(exit) rs.close();
1023                     //writeln("id" ~ "\t" ~ "name");
1024                     while (rs.next()) {
1025                         long id = rs.getLong(1);
1026                         string name = rs.getString(2);
1027                         //writeln("" ~ to!string(id) ~ "\t" ~ name);
1028                     }
1029                 }
1030             }
1031         }
1032     }
1033 
1034 } else { // version(USE_SQLITE)
1035     version(unittest) {
1036         immutable bool SQLITE_TESTS_ENABLED = false;
1037     }
1038 }