sq3.d

   1 /* Invisible Vector Library
   2  * coded by Ketmar // Invisible Vector <ketmar@ketmar.no-ip.org>
   3  * Understanding is not required. Only obedience.
   4  *
   5  * This program is free software: you can redistribute it and/or modify
   6  * it under the terms of the GNU General Public License as published by
   7  * the Free Software Foundation, version 3 of the License ONLY.
   8  *
   9  * This program is distributed in the hope that it will be useful,
  10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  * GNU General Public License for more details.
  13  *
  14  * You should have received a copy of the GNU General Public License
  15  * along with this program. If not, see <http://www.gnu.org/licenses/>.
  16  */
  17 // sqlite3 helpers
  18 module iv.sq3 is aliced;
  19
  20 //version = sq3_debug_stmtlist;
  21
  22 import iv.alice;
  23
  24 public import iv.c.sqlite3;
  25 //private import std.traits;
  26 private import std.range.primitives;
  27
  28 private import std.internal.cstring : tempCString;
  29
  30
  31 // ////////////////////////////////////////////////////////////////////////// //
  32 // use this in `to` to avoid copying
  33 public alias SQ3Blob = const(char)[];
  34 // use this in `to` to avoid copying
  35 public alias SQ3Text = const(char)[];
  36
  37
  38 // ////////////////////////////////////////////////////////////////////////// //
  39 struct DBFieldIndex {
  40   uint idx;
  41 }
  42
  43 struct DBFieldType {
  44   enum {
  45     Unknown,
  46     Integer,
  47     Float,
  48     Text,
  49     Blob,
  50     Null,
  51   }
  52   uint idx;
  53
  54   string toString () const pure nothrow @trusted @nogc {
  55     switch (idx) {
  56       case Unknown: return "Unknown";
  57       case Integer: return "Integer";
  58       case Float: return "Float";
  59       case Text: return "Text";
  60       case Blob: return "Blob";
  61       case Null: return "Null";
  62       default: break;
  63     }
  64     return "Invalid";
  65   }
  66 }
  67
  68
  69 ////////////////////////////////////////////////////////////////////////////////
  70 mixin(NewExceptionClass!("SQLiteException", "Exception"));
  71
  72
  73 class SQLiteErr : SQLiteException {
  74   int code;
  75
  76   this (string msg, string file=__FILE__, usize line=__LINE__, Throwable next=null) @trusted nothrow {
  77     code = SQLITE_ERROR;
  78     super("SQLite ERROR: "~msg, file, line, next);
  79   }
  80
  81   this (sqlite3* db, int rc, string file=__FILE__, usize line=__LINE__, Throwable next=null) @trusted nothrow {
  82     //import core.stdc.stdio : stderr, fprintf;
  83     //fprintf(stderr, "SQLITE ERROR: %s\n", sqlite3_errstr(rc));
  84     code = rc;
  85     if (rc == SQLITE_OK) {
  86       super("SQLite ERROR: no error!", file, line, next);
  87     } else {
  88       import std.exception : assumeUnique;
  89       import std.string : fromStringz;
  90       if (db) {
  91         super(sqlite3_errstr(sqlite3_extended_errcode(db)).fromStringz.assumeUnique, file, line, next);
  92       } else {
  93         super(sqlite3_errstr(rc).fromStringz.assumeUnique, file, line, next);
  94       }
  95     }
  96   }
  97 }
  98
  99
 100 // ////////////////////////////////////////////////////////////////////////// //
 101 public static bool isUTF8ValidSQ3 (const(char)[] str) pure nothrow @trusted @nogc {
 102   usize len = str.length;
 103   immutable(ubyte)* p = cast(immutable(ubyte)*)str.ptr;
 104   while (len--) {
 105     immutable ubyte b = *p++;
 106     if (b == 0) return false;
 107     if (b < 128) continue;
 108     ubyte blen =
 109       (b&0xe0) == 0xc0 ? 1 :
 110       (b&0xf0) == 0xe0 ? 2 :
 111       (b&0xf8) == 0xe8 ? 3 :
 112       0; // no overlongs
 113     if (!blen) return false;
 114     if (len < blen) return false;
 115     len -= blen;
 116     while (blen--) {
 117       immutable ubyte b1 = *p++;
 118       if ((b1&0xc0) != 0x80) return false;
 119     }
 120   }
 121   return true;
 122 }
 123
 124
 125 // ////////////////////////////////////////////////////////////////////////// //
 126 public void sq3check() (sqlite3* db, int rc, string file=__FILE__, usize line=__LINE__) {
 127   pragma(inline, true);
 128   if (rc != SQLITE_OK) throw new SQLiteErr(db, rc, file, line);
 129 }
 130
 131
 132 ////////////////////////////////////////////////////////////////////////////////
 133 // WARNING! don't forget to finalize ALL prepared statements!
 134 struct Database {
 135 private:
 136   static struct DBInfo {
 137     sqlite3* db = null;
 138     uint rc = 0;
 139     usize onCloseSize = 0;
 140     char *onClose = null; // 0-terminated
 141     DBStatement.StmtData *stmthead = null;
 142     DBStatement.StmtData *stmttail = null;
 143   }
 144
 145   usize udbi = 0;
 146
 147 private:
 148   @property inout(DBInfo)* dbi () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(DBInfo)*)udbi; }
 149   @property void dbi (DBInfo *adbi) nothrow @trusted @nogc { pragma(inline, true); udbi = cast(usize)adbi; }
 150
 151 private:
 152   // caller should perform all necessary checks
 153   void clearStatements () nothrow @trusted @nogc {
 154     for (;;) {
 155       DBStatement.StmtData *dd = dbi.stmthead;
 156       if (dd is null) break;
 157       version(sq3_debug_stmtlist) {
 158         import core.stdc.stdio : stderr, fprintf;
 159         fprintf(stderr, "clearStatements(%p): p=%p\n", dbi, dbi.stmthead);
 160       }
 161       dbi.stmthead = dd.next;
 162       dd.owner = null;
 163       dd.prev = null;
 164       dd.next = null;
 165       dd.stepIndex = 0;
 166       if (dd.st !is null) {
 167         sqlite3_reset(dd.st);
 168         sqlite3_clear_bindings(dd.st);
 169         sqlite3_finalize(dd.st);
 170         dd.st = null;
 171       }
 172     }
 173     dbi.stmttail = null;
 174   }
 175
 176 public:
 177   enum Mode {
 178     ReadOnly,
 179     ReadWrite,
 180     ReadWriteCreate,
 181   }
 182
 183   alias getHandle this;
 184
 185 public:
 186   @disable this (usize);
 187
 188   /// `null` schema means "open as R/O"
 189   /// non-null, but empty schema means "open as r/w"
 190   /// non-empty scheme means "create if absent"
 191   this (const(char)[] name, Mode mode, const(char)[] pragmas=null, const(char)[] schema=null) { openEx(name, mode, pragmas, schema); }
 192   this (const(char)[] name, const(char)[] schema) { openEx(name, (schema !is null ? Mode.ReadWriteCreate : Mode.ReadOnly), null, schema); }
 193
 194   ~this () nothrow @trusted { pragma(inline, true); if (udbi) { if (--dbi.rc == 0) { ++dbi.rc; close(); } } }
 195
 196   this (this) nothrow @trusted @nogc { pragma(inline, true); if (udbi) ++dbi.rc; }
 197
 198   @property bool isOpen () const pure nothrow @trusted @nogc { return (udbi && (cast(const(DBInfo)*)udbi).db !is null); }
 199
 200   void close () nothrow @trusted {
 201     if (!udbi) return;
 202     if (--dbi.rc == 0) {
 203       import core.stdc.stdlib : free;
 204       if (dbi.db !is null) {
 205         clearStatements();
 206         if (dbi.onClose !is null) {
 207           auto rc = sqlite3_exec(dbi.db, dbi.onClose, null, null, null);
 208           if (rc != SQLITE_OK) {
 209             import core.stdc.stdio : stderr, fprintf;
 210             fprintf(stderr, "SQLITE ERROR ON CLOSE: %s\n", sqlite3_errstr(sqlite3_extended_errcode(dbi.db)));
 211           }
 212           version(none) {
 213             import core.stdc.stdio : stderr, fprintf;
 214             fprintf(stderr, "exec:===\n%s\n===\n", dbi.onClose);
 215           }
 216           free(dbi.onClose);
 217         }
 218         sqlite3_close_v2(dbi.db);
 219       }
 220       free(dbi);
 221     }
 222     udbi = 0;
 223   }
 224
 225   void appendOnClose (const(char)[] stmts) {
 226     if (!isOpen) throw new SQLiteException("database is not opened");
 227     while (stmts.length && stmts[0] <= 32) stmts = stmts[1..$];
 228     while (stmts.length && stmts[$-1] <= 32) stmts = stmts[0..$-1];
 229     if (stmts.length == 0) return;
 230     import core.stdc.stdlib : realloc;
 231     //FIXME: overflow. don't do it.
 232     usize nsz = dbi.onCloseSize+stmts.length;
 233     if (nsz+1 <= dbi.onCloseSize) throw new SQLiteException("out of memory for OnClose");
 234     char *np = cast(char *)realloc(dbi.onClose, nsz+1);
 235     if (np is null) throw new SQLiteException("out of memory for OnClose");
 236     dbi.onClose = np;
 237     np[dbi.onCloseSize..dbi.onCloseSize+stmts.length] = stmts[];
 238     dbi.onCloseSize += stmts.length;
 239     np[dbi.onCloseSize] = 0;
 240   }
 241
 242   void setOnClose (const(char)[] stmts) {
 243     if (!isOpen) throw new SQLiteException("database is not opened");
 244     if (dbi.onClose !is null) {
 245       import core.stdc.stdlib : free;
 246       free(dbi.onClose);
 247       dbi.onClose = null;
 248       dbi.onCloseSize = 0;
 249     }
 250     appendOnClose(stmts);
 251   }
 252
 253   // `null` schema means "open as R/O"
 254   // non-null, but empty schema means "open as r/w"
 255   // non-empty scheme means "create if absent"
 256   void openEx (const(char)[] name, Mode mode, const(char)[] pragmas=null, const(char)[] schema=null) {
 257     close();
 258     import core.stdc.stdlib : calloc, free;
 259     import std.internal.cstring;
 260     immutable bool allowWrite = (mode == Mode.ReadWrite || mode == Mode.ReadWriteCreate);
 261     bool allowCreate = (mode == Mode.ReadWriteCreate);
 262     if (allowCreate) {
 263       while (schema.length && schema[$-1] <= ' ') schema = schema[0..$-1];
 264       if (schema.length == 0) allowCreate = false;
 265     }
 266     dbi = cast(DBInfo *)calloc(1, DBInfo.sizeof);
 267     if (dbi is null) throw new Error("out of memory");
 268     //*dbi = DBInfo.init;
 269     dbi.rc = 1;
 270     immutable int rc = sqlite3_open_v2(name.tempCString, &dbi.db, (allowWrite ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY)|(allowCreate ? SQLITE_OPEN_CREATE : 0), null);
 271     if (rc != SQLITE_OK) {
 272       free(dbi);
 273       dbi = null;
 274       sq3check(null, rc);
 275     }
 276     scope(failure) { close(); }
 277     if (pragmas.length) {
 278       execute(pragmas);
 279       execute(`PRAGMA trusted_schema = ON;`);
 280     }
 281     if (allowCreate && schema.length) execute(schema);
 282   }
 283
 284   // `null` schema means "open as R/O"
 285   void open (const(char)[] name, const(char)[] schema=null) {
 286     Mode mode = Mode.ReadOnly;
 287     if (schema != null) {
 288       while (schema.length && schema[$-1] <= ' ') schema = schema[0..$-1];
 289       mode = (schema.length ? Mode.ReadWriteCreate : Mode.ReadWrite);
 290     }
 291     return openEx(name, mode, null, schema);
 292   }
 293
 294   ulong lastRowId () nothrow @trusted @nogc { return (isOpen ? sqlite3_last_insert_rowid(dbi.db) : 0); }
 295
 296   void setBusyTimeout (int msecs) nothrow @trusted @nogc { if (isOpen) sqlite3_busy_timeout(dbi.db, msecs); }
 297   void setMaxBusyTimeout () nothrow @trusted @nogc { if (isOpen) sqlite3_busy_timeout(dbi.db, 0x1fffffff); }
 298
 299   // will change if any change to the database occured, either with this connection, or with any other connection
 300   uint getDataVersion () nothrow @trusted @nogc {
 301     pragma(inline, true);
 302     if (!isOpen) return 0;
 303     uint res;
 304     if (sqlite3_file_control(dbi.db, "main", SQLITE_FCNTL_DATA_VERSION, &res) != SQLITE_OK) res = 0;
 305     return res;
 306   }
 307
 308   // will change if any change to the database occured, either with this connection, or with any other connection
 309   uint getTempDataVersion () nothrow @trusted @nogc {
 310     pragma(inline, true);
 311     if (!isOpen) return 0;
 312     uint res;
 313     if (sqlite3_file_control(dbi.db, "temp", SQLITE_FCNTL_DATA_VERSION, &res) != SQLITE_OK) res = 0;
 314     return res;
 315   }
 316
 317   // will change if any change to the database occured, either with this connection, or with any other connection
 318   uint getDBDataVersion (const(char)[] dbname) nothrow @trusted @nogc {
 319     import core.stdc.stdlib : malloc, free;
 320     if (!isOpen || dbname.length == 0) return 0;
 321     char* ts = cast(char*)malloc(dbname.length+1);
 322     if (ts is null) return 0;
 323     scope(exit) free(ts);
 324     ts[0..dbname.length] = dbname[];
 325     ts[dbname.length] = 0;
 326     uint res;
 327     if (sqlite3_file_control(dbi.db, ts, SQLITE_FCNTL_DATA_VERSION, &res) != SQLITE_OK) res = 0;
 328     return res;
 329   }
 330
 331   // execute one or more SQL statements
 332   // SQLite will take care of splitting
 333   void execute (const(char)[] ops) {
 334     if (!isOpen) throw new SQLiteException("database is not opened");
 335     sq3check(dbi.db, sqlite3_exec(dbi.db, ops.tempCString, null, null, null));
 336   }
 337
 338   // create prepared SQL statement
 339   DBStatement statement (const(char)[] stmtstr, bool persistent=false) {
 340     if (!isOpen) throw new SQLiteException("database is not opened");
 341     return DBStatement(dbi, stmtstr, persistent);
 342   }
 343
 344   DBStatement persistentStatement (const(char)[] stmtstr) {
 345     return statement(stmtstr, persistent:true);
 346   }
 347
 348   @property sqlite3* getHandle () nothrow @trusted @nogc { return (isOpen ? dbi.db : null); }
 349
 350   extern(C) {
 351     alias UserFn = void function (sqlite3_context *ctx, int argc, sqlite3_value **argv);
 352   }
 353
 354   void createFunction (const(char)[] name, int argc, UserFn xFunc, bool deterministic=true, int moreflags=0) {
 355     import std.internal.cstring : tempCString;
 356     if (!isOpen) throw new SQLiteException("database is not opened");
 357     immutable int rc = sqlite3_create_function(dbi.db, name.tempCString, argc, SQLITE_UTF8|(deterministic ? SQLITE_DETERMINISTIC : 0)|moreflags, null, xFunc, null, null);
 358     sq3check(dbi.db, rc);
 359   }
 360
 361   private static void performLoopedExecOn(string stmt, int seconds) (sqlite3 *db) {
 362     if (db is null) throw new SQLiteException("database is not opened");
 363     static if (seconds > 0) {
 364       //int tries = 30000; // ~300 seconds
 365       int tries = seconds*100;
 366     }
 367     for (;;) {
 368       immutable rc = sqlite3_exec(db, stmt, null, null, null);
 369       if (rc == SQLITE_OK) break;
 370       if (rc != SQLITE_BUSY) sq3check(db, rc);
 371       static if (seconds > 0) {
 372         if (--tries == 0) sq3check(db, rc);
 373       }
 374       sqlite3_sleep(10);
 375     }
 376   }
 377
 378   static void beginTransactionOn(int seconds=-1) (sqlite3 *db) { performLoopedExecOn!(`BEGIN IMMEDIATE TRANSACTION;`, seconds)(db); }
 379   static void commitTransactionOn(int seconds=-1) (sqlite3 *db) { performLoopedExecOn!(`COMMIT TRANSACTION;`, seconds)(db); }
 380   static void rollbackTransactionOn(int seconds=-1) (sqlite3 *db) { performLoopedExecOn!(`ROLLBACK TRANSACTION;`, seconds)(db); }
 381
 382   void beginTransaction(int seconds=-1) () { if (!isOpen) throw new SQLiteException("database is not opened"); beginTransactionOn!seconds(dbi.db); }
 383   void commitTransaction(int seconds=-1) () { if (!isOpen) throw new SQLiteException("database is not opened"); commitTransactionOn!seconds(dbi.db); }
 384   void rollbackTransaction(int seconds=-1) () { if (!isOpen) throw new SQLiteException("database is not opened"); rollbackTransactionOn!seconds(dbi.db); }
 385
 386   void transacted(int seconds=-1) (scope void delegate () dg) {
 387     if (dg is null) return;
 388     if (!isOpen) throw new SQLiteException("database is not opened");
 389     beginTransaction!seconds();
 390     scope(success) commitTransaction!seconds();
 391     scope(failure) rollbackTransaction!seconds();
 392     dg();
 393   }
 394 }
 395
 396
 397 // ////////////////////////////////////////////////////////////////////////// //
 398 struct DBStatement {
 399 private:
 400   enum FieldIndexMixin = `
 401     if (!valid) throw new SQLiteException("cannot bind field \""~name.idup~"\" in invalid statement");
 402     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 403     if (name.length == 0) throw new SQLiteException("empty field name");
 404     if (name.length > 63) throw new SQLiteException("field name too long");
 405     char[64] fldname = void;
 406     if (name.ptr[0] == ':' || name.ptr[0] == '?' || name.ptr[0] == '@' || name.ptr[0] == '$') {
 407       fldname[0..name.length] = name[];
 408       fldname[name.length] = 0;
 409     } else {
 410       fldname[0] = ':';
 411       fldname[1..name.length+1] = name[];
 412       fldname[name.length+1] = 0;
 413     }
 414     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 415     if (idx < 1) throw new SQLiteException("invalid field name: \""~name.idup~"\"");
 416   `;
 417
 418 private:
 419   static struct StmtData {
 420     uint refcount = 0;
 421     uint rowcount = 0; // number of row structs using this statement
 422     uint stepIndex = 0;
 423     sqlite3_stmt* st = null;
 424     StmtData* prev = null;
 425     StmtData* next = null;
 426     Database.DBInfo* owner = null;
 427   }
 428
 429   usize datau = 0;
 430
 431 private:
 432   @property inout(StmtData)* data () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(StmtData)*)datau; }
 433   @property void data (StmtData *adbi) nothrow @trusted @nogc { pragma(inline, true); datau = cast(usize)adbi; }
 434
 435 private:
 436   static void clearStmt (StmtData* dta) nothrow @trusted {
 437     assert(dta);
 438     assert(dta.rowcount == 0);
 439     dta.stepIndex = 0;
 440     if (dta.st !is null) {
 441       sqlite3_reset(dta.st);
 442       sqlite3_clear_bindings(dta.st);
 443     }
 444   }
 445
 446   static void killData (StmtData* dta) nothrow @trusted {
 447     assert(dta);
 448     assert(dta.refcount == 0);
 449     import core.stdc.stdlib : free;
 450     if (dta.st !is null) {
 451       sqlite3_reset(dta.st);
 452       sqlite3_clear_bindings(dta.st);
 453       sqlite3_finalize(dta.st);
 454       dta.st = null;
 455     }
 456     // unregister from the owner list
 457     if (dta.owner) {
 458       version(sq3_debug_stmtlist) {
 459         import core.stdc.stdio : stderr, fprintf;
 460         fprintf(stderr, "removing stmt(%p): p=%p\n", dta.owner, dta);
 461       }
 462       if (dta.prev) dta.prev.next = dta.next; else dta.owner.stmthead = dta.next;
 463       if (dta.next) dta.next.prev = dta.prev; else dta.owner.stmttail = dta.prev;
 464       dta.owner = null;
 465     }
 466     free(dta);
 467   }
 468
 469 private:
 470   static void incrowref (const usize datau) nothrow @nogc @trusted {
 471     pragma(inline, true);
 472     if (datau) {
 473       ++(cast(StmtData*)datau).refcount;
 474       ++(cast(StmtData*)datau).rowcount;
 475     }
 476   }
 477
 478   static void decref (ref usize udata) nothrow @trusted {
 479     pragma(inline, true);
 480     if (udata) {
 481       if (--(cast(StmtData*)udata).refcount == 0) killData(cast(StmtData*)udata);
 482       udata = 0;
 483     }
 484   }
 485
 486   static void decrowref (ref usize udata) nothrow @trusted {
 487     pragma(inline, true);
 488     if (udata) {
 489       if (--(cast(StmtData*)udata).rowcount == 0) clearStmt(cast(StmtData*)udata);
 490       decref(ref udata);
 491     }
 492   }
 493
 494 private:
 495   // skips blanks and comments
 496   static const(char)[] skipSQLBlanks (const(char)[] s) nothrow @trusted @nogc {
 497     while (s.length) {
 498       char ch = s.ptr[0];
 499       if (ch <= 32 || ch == ';') { s = s[1..$]; continue; }
 500       switch (ch) {
 501         case '-': // single-line comment
 502           if (s.length == 1 || s.ptr[1] != '-') return s;
 503           while (s.length && s.ptr[0] != '\n') s = s[1..$];
 504           break;
 505         case '/': // multi-line comment
 506           if (s.length == 1 || s.ptr[1] != '*') return s;
 507           s = s[2..$];
 508           while (s.length) {
 509             ch = s.ptr[0];
 510             s = s[1..$];
 511             if (ch == '*' && s.length && s.ptr[1] == '/') {
 512               s = s[1..$];
 513               break;
 514             }
 515           }
 516           break;
 517         default:
 518           return s;
 519       }
 520     }
 521     return s;
 522   }
 523
 524 public:
 525   @disable this (usize);
 526   this (this) nothrow @trusted @nogc { pragma(inline, true); if (datau) ++(cast(StmtData*)datau).refcount; }
 527   ~this () nothrow @trusted { pragma(inline, true); decref(ref datau); }
 528
 529   private this (Database.DBInfo *dbi, const(char)[] stmtstr, bool persistent=false) {
 530     if (dbi is null || dbi.db is null) throw new SQLiteException("database is not opened");
 531     auto anchor = stmtstr; // for GC
 532     stmtstr = skipSQLBlanks(stmtstr);
 533     if (stmtstr.length > int.max/4) throw new SQLiteException("SQL statement too big");
 534     if (stmtstr.length == 0) throw new SQLiteException("empty SQL statement");
 535     version(none) {
 536       import core.stdc.stdio : stderr, fprintf;
 537       fprintf(stderr, "stmt:===\n%.*s\n===\n", cast(uint)stmtstr.length, stmtstr.ptr);
 538     }
 539     import core.stdc.stdlib : calloc;
 540     data = cast(StmtData*)calloc(1, StmtData.sizeof);
 541     if (data is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
 542     //assert(datau);
 543     //*data = StmtData.init;
 544     data.refcount = 1;
 545     // register in the owner list
 546     version(sq3_debug_stmtlist) {
 547       import core.stdc.stdio : stderr, fprintf;
 548       fprintf(stderr, "new stmt(%p): p=%p\n", dbi, data);
 549     }
 550     data.owner = dbi;
 551     data.prev = dbi.stmttail;
 552     if (dbi.stmttail !is null) dbi.stmttail.next = data; else dbi.stmthead = data;
 553     dbi.stmttail = data;
 554     // done registering
 555     scope(failure) { data.st = null; decref(datau); }
 556     const(char)* e;
 557     if (persistent) {
 558       sq3check(dbi.db, sqlite3_prepare_v3(dbi.db, stmtstr.ptr, cast(int)stmtstr.length, SQLITE_PREPARE_PERSISTENT, &data.st, &e));
 559     } else {
 560       sq3check(dbi.db, sqlite3_prepare_v2(dbi.db, stmtstr.ptr, cast(int)stmtstr.length, &data.st, &e));
 561     }
 562     // check for extra code
 563     if (e !is null) {
 564       usize left = stmtstr.length-cast(usize)(e-stmtstr.ptr);
 565       stmtstr = skipSQLBlanks(e[0..left]);
 566       if (stmtstr.length) throw new SQLiteErr("extra code in SQL statement (text): "~stmtstr.idup);
 567     }
 568   }
 569
 570   @property bool valid () const pure nothrow @trusted @nogc { return (datau && data.st !is null); }
 571   @property bool busy () const pure nothrow @trusted @nogc { return (datau && data.st !is null && data.stepIndex); }
 572
 573   void close () nothrow @trusted { pragma(inline, true); decref(datau); }
 574
 575   @property auto range () {
 576     if (!valid) throw new SQLiteException("cannot get range from invalid statement");
 577     if (data.stepIndex != 0) throw new SQLiteException("can't get range from busy statement");
 578     if (data.st is null) throw new SQLiteException("can't get range of empty statement");
 579     return DBRowRange(this);
 580   }
 581
 582   void reset () nothrow @trusted {
 583     //if (data.stepIndex != 0) throw new SQLiteException("can't reset busy statement");
 584     if (valid) {
 585       data.stepIndex = 0;
 586       sqlite3_reset(data.st);
 587       sqlite3_clear_bindings(data.st);
 588     }
 589   }
 590
 591   void doAll (void delegate (sqlite3_stmt* stmt) dg=null) {
 592     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 593     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 594     scope(exit) reset();
 595     for (;;) {
 596       immutable rc = sqlite3_step(data.st);
 597       if (rc == SQLITE_DONE) break;
 598       if (rc != SQLITE_ROW) sq3check(data.owner.db, rc);
 599       if (dg !is null) dg(data.st);
 600     }
 601   }
 602
 603   void beginTransaction () {
 604     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 605     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 606     Database.beginTransactionOn(data.owner.db);
 607   }
 608
 609   void commitTransaction () {
 610     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 611     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 612     Database.commitTransactionOn(data.owner.db);
 613   }
 614
 615   void rollbackTransaction () {
 616     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 617     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 618     Database.rollbackTransactionOn(data.owner.db);
 619   }
 620
 621   void doTransacted (void delegate (sqlite3_stmt* stmt) dg=null) {
 622     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 623     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 624     scope(exit) reset();
 625     Database.beginTransactionOn(data.owner.db);
 626     scope(success) Database.commitTransactionOn(data.owner.db);
 627     scope(failure) Database.rollbackTransactionOn(data.owner.db);
 628     for (;;) {
 629       immutable rc = sqlite3_step(data.st);
 630       if (rc == SQLITE_DONE) break;
 631       if (rc != SQLITE_ROW) sq3check(data.owner.db, rc);
 632       if (dg !is null) dg(data.st);
 633     }
 634   }
 635
 636   ref DBStatement bind(T) (uint idx, T value)
 637     if (is(T:const(char)[]) ||
 638         is(T:const(byte)[]) ||
 639         is(T:const(ubyte)[]) ||
 640         __traits(isIntegral, T) ||
 641         (__traits(isFloating, T) && T.sizeof <= 8))
 642   {
 643     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 644     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 645     if (idx < 1 || idx > sqlite3_bind_parameter_count(data.st)) {
 646       import std.conv : to;
 647       throw new SQLiteException("invalid field index: "~to!string(idx));
 648     }
 649     int rc = void;
 650     static if (is(T == typeof(null))) {
 651       rc = sqlite3_bind_null(data.st, idx);
 652     } else static if (is(T:const(char)[])) {
 653       if (value.length >= cast(usize)int.max) throw new SQLiteException("value too big");
 654       rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 655     } else static if (is(T:const(byte)[]) || is(T:const(ubyte)[])) {
 656       if (value.length >= cast(usize)int.max) throw new SQLiteException("value too big");
 657       rc = sqlite3_bind_blob(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 658     } else static if (__traits(isIntegral, T)) {
 659       static if (__traits(isUnsigned, T)) {
 660         // unsigned ints
 661         static if (T.sizeof < 4) {
 662           rc = sqlite3_bind_int(data.st, idx, cast(int)cast(uint)value);
 663         } else {
 664           rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 665         }
 666       } else {
 667         // signed ints
 668         static if (T.sizeof <= 4) {
 669           rc = sqlite3_bind_int(data.st, idx, cast(int)value);
 670         } else {
 671           rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 672         }
 673       }
 674     } else static if (__traits(isFloating, T) && T.sizeof <= 8) {
 675       rc = sqlite3_bind_double(data.st, idx, cast(double)value);
 676     } else {
 677       static assert(0, "WTF?!");
 678     }
 679     sq3check(data.owner.db, rc);
 680     return this;
 681   }
 682
 683   ref DBStatement bind(T) (const(char)[] name, T value)
 684     if (is(T:const(char)[]) ||
 685         is(T:const(byte)[]) ||
 686         is(T:const(ubyte)[]) ||
 687         __traits(isIntegral, T) ||
 688         (__traits(isFloating, T) && T.sizeof <= 8))
 689   {
 690     mixin(FieldIndexMixin);
 691     return bind!T(idx, value);
 692   }
 693
 694
 695   ref DBStatement bindText (uint idx, const(void)[] text, bool transient=true, bool allowNull=false) {
 696     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 697     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 698     if (idx < 1) {
 699       import std.conv : to;
 700       throw new SQLiteException("invalid field index: "~to!string(idx));
 701     }
 702     int rc;
 703     if (text is null) {
 704       if (allowNull) {
 705         rc = sqlite3_bind_null(data.st, idx);
 706       } else {
 707         rc = sqlite3_bind_text(data.st, idx, "".ptr, 0, SQLITE_STATIC);
 708       }
 709     } else {
 710       rc = sqlite3_bind_text(data.st, idx, cast(const(char)*)text.ptr, cast(int)text.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 711     }
 712     sq3check(data.owner.db, rc);
 713     return this;
 714   }
 715
 716   ref DBStatement bindConstText (uint idx, const(void)[] text, bool allowNull=false) {
 717     return bindText(idx, text, false, allowNull);
 718   }
 719
 720   ref DBStatement bindText (const(char)[] name, const(void)[] text, bool transient=true, bool allowNull=false) {
 721     mixin(FieldIndexMixin);
 722     return bindText(cast(uint)idx, text, transient, allowNull);
 723   }
 724
 725   ref DBStatement bindConstText (const(char)[] name, const(void)[] text, bool allowNull=false) {
 726     mixin(FieldIndexMixin);
 727     return bindConstText(cast(uint)idx, text, allowNull);
 728   }
 729
 730
 731   ref DBStatement bindBlob (uint idx, const(void)[] blob, bool transient=true, bool allowNull=false) {
 732     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 733     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 734     if (idx < 1) {
 735       import std.conv : to;
 736       throw new SQLiteException("invalid field index: "~to!string(idx));
 737     }
 738     int rc;
 739     if (blob is null) {
 740       if (allowNull) {
 741         rc = sqlite3_bind_null(data.st, idx);
 742       } else {
 743         rc = sqlite3_bind_blob(data.st, idx, "".ptr, 0, SQLITE_STATIC);
 744       }
 745     } else {
 746       rc = sqlite3_bind_blob(data.st, idx, blob.ptr, cast(int)blob.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 747     }
 748     sq3check(data.owner.db, rc);
 749     return this;
 750   }
 751
 752   ref DBStatement bindConstBlob (uint idx, const(void)[] blob, bool allowNull=false) {
 753     return bindBlob(idx, blob, false, allowNull);
 754   }
 755
 756   ref DBStatement bindBlob (const(char)[] name, const(void)[] blob, bool transient=true, bool allowNull=false) {
 757     mixin(FieldIndexMixin);
 758     return bindBlob(cast(uint)idx, blob, transient, allowNull);
 759   }
 760
 761   ref DBStatement bindConstBlob (const(char)[] name, const(void)[] blob, bool allowNull=false) {
 762     mixin(FieldIndexMixin);
 763     return bindConstBlob(cast(uint)idx, blob, allowNull);
 764   }
 765
 766
 767   ref DBStatement bindNull (uint idx) {
 768     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 769     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 770     if (idx < 1) {
 771       import std.conv : to;
 772       throw new SQLiteException("invalid field index: "~to!string(idx));
 773     }
 774     immutable int rc = sqlite3_bind_null(data.st, idx);
 775     sq3check(data.owner.db, rc);
 776     return this;
 777   }
 778
 779   ref DBStatement bindNull (const(char)[] name) {
 780     mixin(FieldIndexMixin);
 781     return bindNull(cast(uint)idx);
 782   }
 783
 784
 785   ref DBStatement bindInt(T) (uint idx, T v) if (__traits(isIntegral, T)) {
 786     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 787     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 788     if (idx < 1) {
 789       import std.conv : to;
 790       throw new SQLiteException("invalid field index: "~to!string(idx));
 791     }
 792     int rc = void;
 793     static if (__traits(isUnsigned, T)) {
 794       // unsigned ints
 795       static if (T.sizeof < 4) {
 796         rc = sqlite3_bind_int(data.st, idx, cast(int)cast(uint)value);
 797       } else {
 798         rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 799       }
 800     } else {
 801       // signed ints
 802       static if (T.sizeof <= 4) {
 803         rc = sqlite3_bind_int(data.st, idx, cast(int)value);
 804       } else {
 805         rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 806       }
 807     }
 808     sq3check(data.owner.db, rc);
 809     return this;
 810   }
 811
 812   ref DBStatement bindInt(T) (const(char)[] name, T v) if (__traits(isIntegral, T)) {
 813     mixin(FieldIndexMixin);
 814     return bindInt(cast(uint)idx, v);
 815   }
 816
 817
 818   ref DBStatement bindFloat(T) (uint idx, T v) if (__traits(isFloating, T) && T.sizeof <= 8) {
 819     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 820     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 821     if (idx < 1) {
 822       import std.conv : to;
 823       throw new SQLiteException("invalid field index: "~to!string(idx));
 824     }
 825     rc = sqlite3_bind_double(data.st, idx, cast(double)v);
 826     sq3check(data.owner.db, rc);
 827     return this;
 828   }
 829
 830   ref DBStatement bindFloat(T) (const(char)[] name, T v) if (__traits(isFloating, T) && T.sizeof <= 8) {
 831     mixin(FieldIndexMixin);
 832     return bindFloat(cast(uint)idx, v);
 833   }
 834
 835   int colcount () nothrow @trusted @nogc {
 836     if (!valid) return 0;
 837     return sqlite3_column_count(data.st);
 838   }
 839
 840   const(char)[] colname (int idx) nothrow @trusted @nogc {
 841     import core.stdc.string : strlen;
 842     if (!valid || idx < 0) return null;
 843     if (idx >= sqlite3_column_count(data.st)) return null;
 844     const(char)* cname = sqlite3_column_name(data.st, idx);
 845     if (cname is null) return null;
 846     return cname[0..strlen(cname)];
 847   }
 848
 849 private:
 850   struct DBRow {
 851   private:
 852     /*DBStatement.StmtData* */usize data_____u;
 853
 854     @property inout(DBStatement.StmtData)* data____ () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(DBStatement.StmtData)*)data_____u; }
 855
 856   public:
 857     @disable this (usize);
 858
 859     private this (DBStatement.StmtData* adata) nothrow @trusted @nogc {
 860       pragma(inline, true);
 861       data_____u = cast(usize)adata;
 862       DBStatement.incrowref(cast(usize)adata);
 863     }
 864
 865     this (this) nothrow @trusted @nogc { pragma(inline, true); DBStatement.incrowref(data_____u); }
 866     ~this () nothrow @trusted { pragma(inline, true); DBStatement.decrowref(data_____u); }
 867
 868     sqlite3_stmt* getStatementHandle () { return (data_____u ? data____.st : null); }
 869
 870     bool valid_ () pure nothrow @trusted @nogc {
 871       pragma(inline, true);
 872       return (data_____u && data____.stepIndex > 0 && data____.st !is null);
 873     }
 874
 875     int colcount_ () nothrow @trusted @nogc {
 876       if (!data_____u || data____.st is null) return 0;
 877       return sqlite3_column_count(data____.st);
 878     }
 879
 880     const(char)[] colname_ (int idx) nothrow @trusted {
 881       import core.stdc.string : strlen;
 882       if (idx < 0 || !data_____u || data____.st is null) return null;
 883       if (idx >= sqlite3_column_count(data____.st)) return null;
 884       const(char)* cname = sqlite3_column_name(data____.st, idx);
 885       if (cname is null) return null;
 886       return cname[0..strlen(cname)];
 887     }
 888
 889     int fieldIndex____ (const(char)[] name) {
 890       if (name.length > 0 && data_____u && data____.st !is null) {
 891         foreach (immutable int idx; 0..sqlite3_data_count(data____.st)) {
 892           import core.stdc.string : memcmp, strlen;
 893           auto n = sqlite3_column_name(data____.st, idx);
 894           if (n !is null) {
 895             immutable len = strlen(n);
 896             if (len == name.length && memcmp(n, name.ptr, len) == 0) return idx;
 897           }
 898         }
 899       }
 900       throw new SQLiteException("invalid field name: '"~name.idup~"'");
 901     }
 902
 903     T to(T) (uint idx)
 904       if (is(T:const(char)[]) ||
 905           is(T:const(byte)[]) ||
 906           is(T:const(ubyte)[]) ||
 907           __traits(isIntegral, T) ||
 908           (__traits(isFloating, T)) ||
 909           is(T:DBFieldIndex) || is(T:DBFieldType))
 910     {
 911       if (!valid_) throw new SQLiteException("can't get row field of completed statement");
 912       if (idx >= sqlite3_data_count(data____.st)) throw new SQLiteException("invalid result index");
 913       static if (is(T:DBFieldIndex)) {
 914         return DBFieldIndex(idx);
 915       } else static if (is(T:DBFieldType)) {
 916         switch (sqlite3_column_type(data____.st, idx)) {
 917           case SQLITE_INTEGER: return DBFieldType(DBFieldType.Integer);
 918           case SQLITE_FLOAT: return DBFieldType(DBFieldType.Float);
 919           case SQLITE3_TEXT: return DBFieldType(DBFieldType.Text);
 920           case SQLITE_BLOB: return DBFieldType(DBFieldType.Blob);
 921           case SQLITE_NULL: return DBFieldType(DBFieldType.Null);
 922           default: break;
 923         }
 924         return DBFieldType(DBFieldType.Unknown);
 925       } else static if (__traits(isIntegral, T)) {
 926         auto res = sqlite3_column_int64(data____.st, idx);
 927         if (res < T.min || res > T.max) throw new SQLiteException("integral overflow");
 928         return cast(T)res;
 929       } else static if (__traits(isFloating, T)) {
 930         return cast(T)sqlite3_column_double(data____.st, idx);
 931       } else {
 932         auto len = sqlite3_column_bytes(data____.st, idx);
 933         if (len < 0) throw new SQLiteException("invalid result");
 934         const(char)* res;
 935         if (len == 0) {
 936           res = "";
 937         } else {
 938           res = cast(const(char)*)sqlite3_column_blob(data____.st, idx);
 939           if (res is null) throw new SQLiteException("invalid result");
 940         }
 941         static if (is(T:char[]) || is(T:byte[]) || is(T:ubyte[])) {
 942           //{ import core.stdc.stdio : printf; printf("***DUP***\n"); }
 943           return res[0..len].dup;
 944         } else static if (is(T:immutable(char)[]) || is(T:immutable(byte)[]) || is(T:immutable(ubyte)[])) {
 945           //{ import core.stdc.stdio : printf; printf("***I-DUP***\n"); }
 946           return res[0..len].idup;
 947         } else {
 948           //{ import core.stdc.stdio : printf; printf("***NO-DUP***\n"); }
 949           return res[0..len];
 950         }
 951       }
 952     }
 953     T to(T) (const(char)[] name) { return this.to!T(fieldIndex____(name)); }
 954
 955     const(ubyte)[] blob_ (const(char)[] name) {
 956       immutable int idx = fieldIndex____(name);
 957       auto len = sqlite3_column_bytes(data____.st, idx);
 958       if (len < 0) throw new SQLiteException("invalid result");
 959       const(ubyte)* res = cast(const(ubyte)*)sqlite3_column_blob(data____.st, idx);
 960       if (len == 0 && res is null) return cast(const(ubyte)[])"";
 961       return res[0..len];
 962     }
 963
 964     template opIndex() {
 965       T opIndexImpl(T) (uint idx)
 966         if (is(T:const(char)[]) ||
 967             is(T:const(byte)[]) ||
 968             is(T:const(ubyte)[]) ||
 969             __traits(isIntegral, T) ||
 970             (__traits(isFloating, T)) ||
 971             is(T:DBFieldIndex) || is(T:DBFieldType))
 972       { pragma(inline, true); return this.to!T(idx); }
 973       T opIndexImpl(T) (const(char)[] name)
 974         if (is(T:const(char)[]) ||
 975             is(T:const(byte)[]) ||
 976             is(T:const(ubyte)[]) ||
 977             __traits(isIntegral, T) ||
 978             (__traits(isFloating, T)) ||
 979             is(T:DBFieldIndex) || is(T:DBFieldType))
 980       { pragma(inline, true); return this.to!T(name); }
 981       alias opIndex = opIndexImpl;
 982     }
 983
 984     template opDispatch(string name) {
 985       T opDispatchImpl(T=const(char)[]) ()
 986         if (is(T:const(char)[]) ||
 987             is(T:const(byte)[]) ||
 988             is(T:const(ubyte)[]) ||
 989             __traits(isIntegral, T) ||
 990             (__traits(isFloating, T)) ||
 991             is(T:DBFieldIndex) || is(T:DBFieldType))
 992       { pragma(inline, true); return this.to!T(name); }
 993       alias opDispatch = opDispatchImpl;
 994     }
 995
 996     auto index_ () pure const nothrow @nogc { return (data____.stepIndex > 0 ? data____.stepIndex-1 : 0); }
 997   } // end of DBRow
 998
 999 private:
1000   struct DBRowRange {
1001   private:
1002     /*DBStatement.StmtData* */usize datau;
1003
1004     @property inout(DBStatement.StmtData)* data () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(DBStatement.StmtData)*)datau; }
1005
1006   public:
1007     @disable this (usize);
1008
1009     private this (ref DBStatement astat) {
1010       datau = astat.datau;
1011       DBStatement.incrowref(datau);
1012       assert(data.stepIndex == 0);
1013       data.stepIndex = 1;
1014       // perform first step
1015       popFront!false();
1016     }
1017
1018     this (this) nothrow @trusted @nogc { pragma(inline, true); DBStatement.incrowref(datau); }
1019     ~this () nothrow @trusted { pragma(inline, true); DBStatement.decrowref(datau); }
1020
1021     @property bool empty () const pure nothrow @trusted @nogc { pragma(inline, true); return (data.stepIndex == 0); }
1022
1023     @property auto front () {
1024       if (data.stepIndex == 0) throw new SQLiteException("can't get front element of completed statement");
1025       return DBRow(data);
1026     }
1027
1028     void popFront(bool incrow=true) () {
1029       if (data.stepIndex == 0) throw new SQLiteException("can't pop element of completed statement");
1030       auto rc = sqlite3_step(data.st);
1031       if (rc == SQLITE_DONE) {
1032         data.stepIndex = 0;
1033         return;
1034       }
1035       if (rc != SQLITE_ROW) {
1036         data.stepIndex = 0;
1037         sq3check(data.owner.db, rc);
1038       }
1039       static if (incrow) ++data.stepIndex;
1040     }
1041
1042     auto index_ () pure const nothrow @trusted @nogc { pragma(inline, true); return (data.stepIndex > 0 ? data.stepIndex-1 : 0); }
1043   } // end of DBRowRange
1044 }
1045
1046
1047 shared static this () {
1048   if (sqlite3_libversion_number() < SQLITE_VERSION_NUMBER) {
1049     //import core.stdc.string : strlen;
1050     //immutable(char)* lver = sqlite3_libversion();
1051     //auto len = strlen(lver);
1052     //{ import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "VER(%u): <%s> : <%s>\n", cast(uint)len, lver, sqlite3_version); }
1053     throw new Exception("expected SQLite at least v"~SQLITE_VERSION);
1054   }
1055 }