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

Page generated by adrdox