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