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 is absend"
 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) 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     execute(pragmas);
 278     if (allowCreate && schema.length) execute(schema);
 279   }
 280
 281   // `null` schema means "open as R/O"
 282   void open (const(char)[] name, const(char)[] schema=null) {
 283     Mode mode = Mode.ReadOnly;
 284     if (schema != null) {
 285       while (schema.length && schema[$-1] <= ' ') schema = schema[0..$-1];
 286       mode = (schema.length ? Mode.ReadWriteCreate : Mode.ReadWrite);
 287     }
 288     return openEx(name, mode, null, schema);
 289   }
 290
 291   ulong lastRowId () nothrow @trusted @nogc { return (isOpen ? sqlite3_last_insert_rowid(dbi.db) : 0); }
 292
 293   void setBusyTimeout (int msecs) nothrow @trusted @nogc { if (isOpen) sqlite3_busy_timeout(dbi.db, msecs); }
 294   void setMaxBusyTimeout () nothrow @trusted @nogc { if (isOpen) sqlite3_busy_timeout(dbi.db, 0x1fffffff); }
 295
 296   // execute one or more SQL statements
 297   // SQLite will take care of splitting
 298   void execute (const(char)[] ops) {
 299     if (!isOpen) throw new SQLiteException("database is not opened");
 300     sq3check(dbi.db, sqlite3_exec(dbi.db, ops.tempCString, null, null, null));
 301   }
 302
 303   // create prepared SQL statement
 304   DBStatement statement (const(char)[] stmtstr, bool persistent=false) {
 305     if (!isOpen) throw new SQLiteException("database is not opened");
 306     return DBStatement(dbi, stmtstr, persistent);
 307   }
 308
 309   DBStatement persistentStatement (const(char)[] stmtstr) {
 310     return statement(stmtstr, persistent:true);
 311   }
 312
 313   @property sqlite3* getHandle () nothrow @trusted @nogc { return (isOpen ? dbi.db : null); }
 314
 315   extern(C) {
 316     alias UserFn = void function (sqlite3_context *ctx, int argc, sqlite3_value **argv);
 317   }
 318
 319   void createFunction (const(char)[] name, int argc, UserFn xFunc, bool deterministic=true, int moreflags=0) {
 320     import std.internal.cstring : tempCString;
 321     if (!isOpen) throw new SQLiteException("database is not opened");
 322     immutable int rc = sqlite3_create_function(dbi.db, name.tempCString, argc, SQLITE_UTF8|(deterministic ? SQLITE_DETERMINISTIC : 0)|moreflags, null, xFunc, null, null);
 323     sq3check(dbi.db, rc);
 324   }
 325
 326   private static void performLoopedExecOn(string stmt, int seconds) (sqlite3 *db) {
 327     if (db is null) throw new SQLiteException("database is not opened");
 328     static if (seconds > 0) {
 329       //int tries = 30000; // ~300 seconds
 330       int tries = seconds*100;
 331     }
 332     for (;;) {
 333       immutable rc = sqlite3_exec(db, stmt, null, null, null);
 334       if (rc == SQLITE_OK) break;
 335       if (rc != SQLITE_BUSY) sq3check(db, rc);
 336       static if (seconds > 0) {
 337         if (--tries == 0) sq3check(db, rc);
 338       }
 339       sqlite3_sleep(10);
 340     }
 341   }
 342
 343   static void beginTransactionOn(int seconds=-1) (sqlite3 *db) { performLoopedExecOn!(`BEGIN IMMEDIATE TRANSACTION;`, seconds)(db); }
 344   static void commitTransactionOn(int seconds=-1) (sqlite3 *db) { performLoopedExecOn!(`COMMIT TRANSACTION;`, seconds)(db); }
 345   static void rollbackTransactionOn(int seconds=-1) (sqlite3 *db) { performLoopedExecOn!(`ROLLBACK TRANSACTION;`, seconds)(db); }
 346
 347   void beginTransaction(int seconds=-1) () { if (!isOpen) throw new SQLiteException("database is not opened"); beginTransactionOn!seconds(dbi.db); }
 348   void commitTransaction(int seconds=-1) () { if (!isOpen) throw new SQLiteException("database is not opened"); commitTransactionOn!seconds(dbi.db); }
 349   void rollbackTransaction(int seconds=-1) () { if (!isOpen) throw new SQLiteException("database is not opened"); rollbackTransactionOn!seconds(dbi.db); }
 350
 351   void transacted(int seconds=-1) (void delegate () dg) {
 352     if (dg is null) return;
 353     if (!isOpen) throw new SQLiteException("database is not opened");
 354     beginTransaction!seconds();
 355     scope(success) commitTransaction!seconds();
 356     scope(failure) rollbackTransaction!seconds();
 357     dg();
 358   }
 359 }
 360
 361
 362 // ////////////////////////////////////////////////////////////////////////// //
 363 struct DBStatement {
 364 private:
 365   enum FieldIndexMixin = `
 366     if (!valid) throw new SQLiteException("cannot bind field \""~name.idup~"\" in invalid statement");
 367     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 368     if (name.length == 0) throw new SQLiteException("empty field name");
 369     if (name.length > 63) throw new SQLiteException("field name too long");
 370     char[64] fldname = void;
 371     if (name.ptr[0] == ':' || name.ptr[0] == '?' || name.ptr[0] == '@' || name.ptr[0] == '$') {
 372       fldname[0..name.length] = name[];
 373       fldname[name.length] = 0;
 374     } else {
 375       fldname[0] = ':';
 376       fldname[1..name.length+1] = name[];
 377       fldname[name.length+1] = 0;
 378     }
 379     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 380     if (idx < 1) throw new SQLiteException("invalid field name: \""~name.idup~"\"");
 381   `;
 382
 383 private:
 384   static struct StmtData {
 385     uint refcount = 0;
 386     uint rowcount = 0; // number of row structs using this statement
 387     uint stepIndex = 0;
 388     sqlite3_stmt* st = null;
 389     StmtData* prev = null;
 390     StmtData* next = null;
 391     Database.DBInfo* owner = null;
 392   }
 393
 394   usize datau = 0;
 395
 396 private:
 397   @property inout(StmtData)* data () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(StmtData)*)datau; }
 398   @property void data (StmtData *adbi) nothrow @trusted @nogc { pragma(inline, true); datau = cast(usize)adbi; }
 399
 400 private:
 401   static void clearStmt (StmtData* dta) nothrow @trusted {
 402     assert(dta);
 403     assert(dta.rowcount == 0);
 404     dta.stepIndex = 0;
 405     if (dta.st !is null) {
 406       sqlite3_reset(dta.st);
 407       sqlite3_clear_bindings(dta.st);
 408     }
 409   }
 410
 411   static void killData (StmtData* dta) nothrow @trusted {
 412     assert(dta);
 413     assert(dta.refcount == 0);
 414     import core.stdc.stdlib : free;
 415     if (dta.st !is null) {
 416       sqlite3_reset(dta.st);
 417       sqlite3_clear_bindings(dta.st);
 418       sqlite3_finalize(dta.st);
 419       dta.st = null;
 420     }
 421     // unregister from the owner list
 422     if (dta.owner) {
 423       version(sq3_debug_stmtlist) {
 424         import core.stdc.stdio : stderr, fprintf;
 425         fprintf(stderr, "removing stmt(%p): p=%p\n", dta.owner, dta);
 426       }
 427       if (dta.prev) dta.prev.next = dta.next; else dta.owner.stmthead = dta.next;
 428       if (dta.next) dta.next.prev = dta.prev; else dta.owner.stmttail = dta.prev;
 429       dta.owner = null;
 430     }
 431     free(dta);
 432   }
 433
 434 private:
 435   static void incrowref (const usize datau) nothrow @nogc @trusted {
 436     pragma(inline, true);
 437     if (datau) {
 438       ++(cast(StmtData*)datau).refcount;
 439       ++(cast(StmtData*)datau).rowcount;
 440     }
 441   }
 442
 443   static void decref (ref usize udata) nothrow @trusted {
 444     pragma(inline, true);
 445     if (udata) {
 446       if (--(cast(StmtData*)udata).refcount == 0) killData(cast(StmtData*)udata);
 447       udata = 0;
 448     }
 449   }
 450
 451   static void decrowref (ref usize udata) nothrow @trusted {
 452     pragma(inline, true);
 453     if (udata) {
 454       if (--(cast(StmtData*)udata).rowcount == 0) clearStmt(cast(StmtData*)udata);
 455       decref(ref udata);
 456     }
 457   }
 458
 459 private:
 460   // skips blanks and comments
 461   static const(char)[] skipSQLBlanks (const(char)[] s) nothrow @trusted @nogc {
 462     while (s.length) {
 463       char ch = s.ptr[0];
 464       if (ch <= 32 || ch == ';') { s = s[1..$]; continue; }
 465       switch (ch) {
 466         case '-': // single-line comment
 467           if (s.length == 1 || s.ptr[1] != '-') return s;
 468           while (s.length && s.ptr[0] != '\n') s = s[1..$];
 469           break;
 470         case '/': // multi-line comment
 471           if (s.length == 1 || s.ptr[1] != '*') return s;
 472           s = s[2..$];
 473           while (s.length) {
 474             ch = s.ptr[0];
 475             s = s[1..$];
 476             if (ch == '*' && s.length && s.ptr[1] == '/') {
 477               s = s[1..$];
 478               break;
 479             }
 480           }
 481           break;
 482         default:
 483           return s;
 484       }
 485     }
 486     return s;
 487   }
 488
 489 public:
 490   @disable this (usize);
 491   this (this) nothrow @trusted @nogc { pragma(inline, true); if (datau) ++(cast(StmtData*)datau).refcount; }
 492   ~this () nothrow @trusted { pragma(inline, true); decref(ref datau); }
 493
 494   private this (Database.DBInfo *dbi, const(char)[] stmtstr, bool persistent=false) {
 495     if (dbi is null || dbi.db is null) throw new SQLiteException("database is not opened");
 496     auto anchor = stmtstr; // for GC
 497     stmtstr = skipSQLBlanks(stmtstr);
 498     if (stmtstr.length > int.max/4) throw new SQLiteException("SQL statement too big");
 499     if (stmtstr.length == 0) throw new SQLiteException("empty SQL statement");
 500     version(none) {
 501       import core.stdc.stdio : stderr, fprintf;
 502       fprintf(stderr, "stmt:===\n%.*s\n===\n", cast(uint)stmtstr.length, stmtstr.ptr);
 503     }
 504     import core.stdc.stdlib : calloc;
 505     data = cast(StmtData*)calloc(1, StmtData.sizeof);
 506     if (data is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
 507     //assert(datau);
 508     //*data = StmtData.init;
 509     data.refcount = 1;
 510     // register in the owner list
 511     version(sq3_debug_stmtlist) {
 512       import core.stdc.stdio : stderr, fprintf;
 513       fprintf(stderr, "new stmt(%p): p=%p\n", dbi, data);
 514     }
 515     data.owner = dbi;
 516     data.prev = dbi.stmttail;
 517     if (dbi.stmttail !is null) dbi.stmttail.next = data; else dbi.stmthead = data;
 518     dbi.stmttail = data;
 519     // done registering
 520     scope(failure) { data.st = null; decref(datau); }
 521     const(char)* e;
 522     if (persistent) {
 523       sq3check(dbi.db, sqlite3_prepare_v3(dbi.db, stmtstr.ptr, cast(int)stmtstr.length, SQLITE_PREPARE_PERSISTENT, &data.st, &e));
 524     } else {
 525       sq3check(dbi.db, sqlite3_prepare_v2(dbi.db, stmtstr.ptr, cast(int)stmtstr.length, &data.st, &e));
 526     }
 527     // check for extra code
 528     if (e !is null) {
 529       usize left = stmtstr.length-cast(usize)(e-stmtstr.ptr);
 530       stmtstr = skipSQLBlanks(e[0..left]);
 531       if (stmtstr.length) throw new SQLiteErr("extra code in SQL statement (text): "~stmtstr.idup);
 532     }
 533   }
 534
 535   @property bool valid () const pure nothrow @trusted @nogc { return (datau && data.st !is null); }
 536   @property bool busy () const pure nothrow @trusted @nogc { return (datau && data.st !is null && data.stepIndex); }
 537
 538   void close () nothrow @trusted { pragma(inline, true); decref(datau); }
 539
 540   @property auto range () {
 541     if (!valid) throw new SQLiteException("cannot get range from invalid statement");
 542     if (data.stepIndex != 0) throw new SQLiteException("can't get range from busy statement");
 543     if (data.st is null) throw new SQLiteException("can't get range of empty statement");
 544     return DBRowRange(this);
 545   }
 546
 547   void reset () nothrow @trusted {
 548     //if (data.stepIndex != 0) throw new SQLiteException("can't reset busy statement");
 549     if (valid) {
 550       data.stepIndex = 0;
 551       sqlite3_reset(data.st);
 552       sqlite3_clear_bindings(data.st);
 553     }
 554   }
 555
 556   void doAll (void delegate (sqlite3_stmt* stmt) dg=null) {
 557     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 558     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 559     scope(exit) reset();
 560     for (;;) {
 561       immutable rc = sqlite3_step(data.st);
 562       if (rc == SQLITE_DONE) break;
 563       if (rc != SQLITE_ROW) sq3check(data.owner.db, rc);
 564       if (dg !is null) dg(data.st);
 565     }
 566   }
 567
 568   void beginTransaction () {
 569     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 570     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 571     Database.beginTransactionOn(data.owner.db);
 572   }
 573
 574   void commitTransaction () {
 575     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 576     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 577     Database.commitTransactionOn(data.owner.db);
 578   }
 579
 580   void rollbackTransaction () {
 581     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 582     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 583     Database.rollbackTransactionOn(data.owner.db);
 584   }
 585
 586   void doTransacted (void delegate (sqlite3_stmt* stmt) dg=null) {
 587     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 588     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 589     scope(exit) reset();
 590     Database.beginTransactionOn(data.owner.db);
 591     scope(success) Database.commitTransactionOn(data.owner.db);
 592     scope(failure) Database.rollbackTransactionOn(data.owner.db);
 593     for (;;) {
 594       immutable rc = sqlite3_step(data.st);
 595       if (rc == SQLITE_DONE) break;
 596       if (rc != SQLITE_ROW) sq3check(data.owner.db, rc);
 597       if (dg !is null) dg(data.st);
 598     }
 599   }
 600
 601   ref DBStatement bind(T) (uint idx, T value)
 602     if (is(T:const(char)[]) ||
 603         is(T:const(byte)[]) ||
 604         is(T:const(ubyte)[]) ||
 605         __traits(isIntegral, T) ||
 606         (__traits(isFloating, T) && T.sizeof <= 8))
 607   {
 608     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 609     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 610     if (idx < 1 || idx > sqlite3_bind_parameter_count(data.st)) {
 611       import std.conv : to;
 612       throw new SQLiteException("invalid field index: "~to!string(idx));
 613     }
 614     int rc = void;
 615     static if (is(T == typeof(null))) {
 616       rc = sqlite3_bind_null(data.st, idx);
 617     } else static if (is(T:const(char)[])) {
 618       if (value.length >= cast(usize)int.max) throw new SQLiteException("value too big");
 619       rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 620     } else static if (is(T:const(byte)[]) || is(T:const(ubyte)[])) {
 621       if (value.length >= cast(usize)int.max) throw new SQLiteException("value too big");
 622       rc = sqlite3_bind_blob(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 623     } else static if (__traits(isIntegral, T)) {
 624       static if (__traits(isUnsigned, T)) {
 625         // unsigned ints
 626         static if (T.sizeof < 4) {
 627           rc = sqlite3_bind_int(data.st, idx, cast(int)cast(uint)value);
 628         } else {
 629           rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 630         }
 631       } else {
 632         // signed ints
 633         static if (T.sizeof <= 4) {
 634           rc = sqlite3_bind_int(data.st, idx, cast(int)value);
 635         } else {
 636           rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 637         }
 638       }
 639     } else static if (__traits(isFloating, T) && T.sizeof <= 8) {
 640       rc = sqlite3_bind_double(data.st, idx, cast(double)value);
 641     } else {
 642       static assert(0, "WTF?!");
 643     }
 644     sq3check(data.owner.db, rc);
 645     return this;
 646   }
 647
 648   ref DBStatement bind(T) (const(char)[] name, T value)
 649     if (is(T:const(char)[]) ||
 650         is(T:const(byte)[]) ||
 651         is(T:const(ubyte)[]) ||
 652         __traits(isIntegral, T) ||
 653         (__traits(isFloating, T) && T.sizeof <= 8))
 654   {
 655     mixin(FieldIndexMixin);
 656     return bind!T(idx, value);
 657   }
 658
 659
 660   ref DBStatement bindText (uint idx, const(void)[] text, bool transient=true, bool allowNull=false) {
 661     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 662     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 663     if (idx < 1) {
 664       import std.conv : to;
 665       throw new SQLiteException("invalid field index: "~to!string(idx));
 666     }
 667     int rc;
 668     if (text is null) {
 669       if (allowNull) {
 670         rc = sqlite3_bind_null(data.st, idx);
 671       } else {
 672         rc = sqlite3_bind_text(data.st, idx, "".ptr, 0, SQLITE_STATIC);
 673       }
 674     } else {
 675       rc = sqlite3_bind_text(data.st, idx, cast(const(char)*)text.ptr, cast(int)text.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 676     }
 677     sq3check(data.owner.db, rc);
 678     return this;
 679   }
 680
 681   ref DBStatement bindConstText (uint idx, const(void)[] text, bool allowNull=false) {
 682     return bindText(idx, text, false, allowNull);
 683   }
 684
 685   ref DBStatement bindText (const(char)[] name, const(void)[] text, bool transient=true, bool allowNull=false) {
 686     mixin(FieldIndexMixin);
 687     return bindText(cast(uint)idx, text, transient, allowNull);
 688   }
 689
 690   ref DBStatement bindConstText (const(char)[] name, const(void)[] text, bool allowNull=false) {
 691     mixin(FieldIndexMixin);
 692     return bindConstText(cast(uint)idx, text, allowNull);
 693   }
 694
 695
 696   ref DBStatement bindBlob (uint idx, const(void)[] blob, bool transient=true, bool allowNull=false) {
 697     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 698     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 699     if (idx < 1) {
 700       import std.conv : to;
 701       throw new SQLiteException("invalid field index: "~to!string(idx));
 702     }
 703     int rc;
 704     if (blob is null) {
 705       if (allowNull) {
 706         rc = sqlite3_bind_null(data.st, idx);
 707       } else {
 708         rc = sqlite3_bind_blob(data.st, idx, "".ptr, 0, SQLITE_STATIC);
 709       }
 710     } else {
 711       rc = sqlite3_bind_blob(data.st, idx, blob.ptr, cast(int)blob.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 712     }
 713     sq3check(data.owner.db, rc);
 714     return this;
 715   }
 716
 717   ref DBStatement bindConstBlob (uint idx, const(void)[] blob, bool allowNull=false) {
 718     return bindBlob(idx, blob, false, allowNull);
 719   }
 720
 721   ref DBStatement bindBlob (const(char)[] name, const(void)[] blob, bool transient=true, bool allowNull=false) {
 722     mixin(FieldIndexMixin);
 723     return bindBlob(cast(uint)idx, blob, transient, allowNull);
 724   }
 725
 726   ref DBStatement bindConstBlob (const(char)[] name, const(void)[] blob, bool allowNull=false) {
 727     mixin(FieldIndexMixin);
 728     return bindConstBlob(cast(uint)idx, blob, allowNull);
 729   }
 730
 731
 732   ref DBStatement bindNull (uint idx) {
 733     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 734     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 735     if (idx < 1) {
 736       import std.conv : to;
 737       throw new SQLiteException("invalid field index: "~to!string(idx));
 738     }
 739     immutable int rc = sqlite3_bind_null(data.st, idx);
 740     sq3check(data.owner.db, rc);
 741     return this;
 742   }
 743
 744   ref DBStatement bindNull (const(char)[] name) {
 745     mixin(FieldIndexMixin);
 746     return bindNull(cast(uint)idx);
 747   }
 748
 749
 750   ref DBStatement bindInt(T) (uint idx, T v) if (__traits(isIntegral, T)) {
 751     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 752     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 753     if (idx < 1) {
 754       import std.conv : to;
 755       throw new SQLiteException("invalid field index: "~to!string(idx));
 756     }
 757     int rc = void;
 758     static if (__traits(isUnsigned, T)) {
 759       // unsigned ints
 760       static if (T.sizeof < 4) {
 761         rc = sqlite3_bind_int(data.st, idx, cast(int)cast(uint)value);
 762       } else {
 763         rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 764       }
 765     } else {
 766       // signed ints
 767       static if (T.sizeof <= 4) {
 768         rc = sqlite3_bind_int(data.st, idx, cast(int)value);
 769       } else {
 770         rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 771       }
 772     }
 773     sq3check(data.owner.db, rc);
 774     return this;
 775   }
 776
 777   ref DBStatement bindInt(T) (const(char)[] name, T v) if (__traits(isIntegral, T)) {
 778     mixin(FieldIndexMixin);
 779     return bindInt(cast(uint)idx, v);
 780   }
 781
 782
 783   ref DBStatement bindFloat(T) (uint idx, T v) if (__traits(isFloating, T) && T.sizeof <= 8) {
 784     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 785     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 786     if (idx < 1) {
 787       import std.conv : to;
 788       throw new SQLiteException("invalid field index: "~to!string(idx));
 789     }
 790     rc = sqlite3_bind_double(data.st, idx, cast(double)v);
 791     sq3check(data.owner.db, rc);
 792     return this;
 793   }
 794
 795   ref DBStatement bindFloat(T) (const(char)[] name, T v) if (__traits(isFloating, T) && T.sizeof <= 8) {
 796     mixin(FieldIndexMixin);
 797     return bindFloat(cast(uint)idx, v);
 798   }
 799
 800   int colcount () nothrow @trusted @nogc {
 801     if (!valid) return 0;
 802     return sqlite3_column_count(data.st);
 803   }
 804
 805   const(char)[] colname (int idx) nothrow @trusted @nogc {
 806     import core.stdc.string : strlen;
 807     if (!valid || idx < 0) return null;
 808     if (idx >= sqlite3_column_count(data.st)) return null;
 809     const(char)* cname = sqlite3_column_name(data.st, idx);
 810     if (cname is null) return null;
 811     return cname[0..strlen(cname)];
 812   }
 813
 814 private:
 815   struct DBRow {
 816   private:
 817     /*DBStatement.StmtData* */usize data_____u;
 818
 819     @property inout(DBStatement.StmtData)* data____ () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(DBStatement.StmtData)*)data_____u; }
 820
 821   public:
 822     @disable this (usize);
 823
 824     private this (DBStatement.StmtData* adata) nothrow @trusted @nogc {
 825       pragma(inline, true);
 826       data_____u = cast(usize)adata;
 827       DBStatement.incrowref(cast(usize)adata);
 828     }
 829
 830     this (this) nothrow @trusted @nogc { pragma(inline, true); DBStatement.incrowref(data_____u); }
 831     ~this () nothrow @trusted { pragma(inline, true); DBStatement.decrowref(data_____u); }
 832
 833     bool valid_ () pure nothrow @trusted @nogc {
 834       pragma(inline, true);
 835       return (data_____u && data____.stepIndex > 0 && data____.st !is null);
 836     }
 837
 838     int colcount_ () nothrow @trusted @nogc {
 839       if (!data_____u || data____.st is null) return 0;
 840       return sqlite3_column_count(data____.st);
 841     }
 842
 843     const(char)[] colname_ (int idx) nothrow @trusted {
 844       import core.stdc.string : strlen;
 845       if (idx < 0 || !data_____u || data____.st is null) return null;
 846       if (idx >= sqlite3_column_count(data____.st)) return null;
 847       const(char)* cname = sqlite3_column_name(data____.st, idx);
 848       if (cname is null) return null;
 849       return cname[0..strlen(cname)];
 850     }
 851
 852     int fieldIndex____ (const(char)[] name) {
 853       if (name.length > 0 && data_____u && data____.st !is null) {
 854         foreach (immutable int idx; 0..sqlite3_data_count(data____.st)) {
 855           import core.stdc.string : memcmp, strlen;
 856           auto n = sqlite3_column_name(data____.st, idx);
 857           if (n !is null) {
 858             immutable len = strlen(n);
 859             if (len == name.length && memcmp(n, name.ptr, len) == 0) return idx;
 860           }
 861         }
 862       }
 863       throw new SQLiteException("invalid field name: '"~name.idup~"'");
 864     }
 865
 866     T to(T) (uint idx)
 867       if (is(T:const(char)[]) ||
 868           is(T:const(byte)[]) ||
 869           is(T:const(ubyte)[]) ||
 870           __traits(isIntegral, T) ||
 871           (__traits(isFloating, T)) ||
 872           is(T:DBFieldIndex) || is(T:DBFieldType))
 873     {
 874       if (!valid_) throw new SQLiteException("can't get row field of completed statement");
 875       if (idx >= sqlite3_data_count(data____.st)) throw new SQLiteException("invalid result index");
 876       static if (is(T:DBFieldIndex)) {
 877         return DBFieldIndex(idx);
 878       } else static if (is(T:DBFieldType)) {
 879         switch (sqlite3_column_type(data____.st, idx)) {
 880           case SQLITE_INTEGER: return DBFieldType(DBFieldType.Integer);
 881           case SQLITE_FLOAT: return DBFieldType(DBFieldType.Float);
 882           case SQLITE3_TEXT: return DBFieldType(DBFieldType.Text);
 883           case SQLITE_BLOB: return DBFieldType(DBFieldType.Blob);
 884           case SQLITE_NULL: return DBFieldType(DBFieldType.Null);
 885           default: break;
 886         }
 887         return DBFieldType(DBFieldType.Unknown);
 888       } else static if (__traits(isIntegral, T)) {
 889         auto res = sqlite3_column_int64(data____.st, idx);
 890         if (res < T.min || res > T.max) throw new SQLiteException("integral overflow");
 891         return cast(T)res;
 892       } else static if (__traits(isFloating, T)) {
 893         return cast(T)sqlite3_column_double(data____.st, idx);
 894       } else {
 895         auto len = sqlite3_column_bytes(data____.st, idx);
 896         if (len < 0) throw new SQLiteException("invalid result");
 897         const(char)* res;
 898         if (len == 0) {
 899           res = "";
 900         } else {
 901           res = cast(const(char)*)sqlite3_column_blob(data____.st, idx);
 902           if (res is null) throw new SQLiteException("invalid result");
 903         }
 904         static if (is(T:char[]) || is(T:byte[]) || is(T:ubyte[])) {
 905           //{ import core.stdc.stdio : printf; printf("***DUP***\n"); }
 906           return res[0..len].dup;
 907         } else static if (is(T:immutable(char)[]) || is(T:immutable(byte)[]) || is(T:immutable(ubyte)[])) {
 908           //{ import core.stdc.stdio : printf; printf("***I-DUP***\n"); }
 909           return res[0..len].idup;
 910         } else {
 911           //{ import core.stdc.stdio : printf; printf("***NO-DUP***\n"); }
 912           return res[0..len];
 913         }
 914       }
 915     }
 916     T to(T) (const(char)[] name) { return this.to!T(fieldIndex____(name)); }
 917
 918     const(ubyte)[] blob_ (const(char)[] name) {
 919       immutable int idx = fieldIndex____(name);
 920       auto len = sqlite3_column_bytes(data____.st, idx);
 921       if (len < 0) throw new SQLiteException("invalid result");
 922       const(ubyte)* res = cast(const(ubyte)*)sqlite3_column_blob(data____.st, idx);
 923       if (len == 0 && res is null) return cast(const(ubyte)[])"";
 924       return res[0..len];
 925     }
 926
 927     template opIndex() {
 928       T opIndexImpl(T) (uint idx)
 929         if (is(T:const(char)[]) ||
 930             is(T:const(byte)[]) ||
 931             is(T:const(ubyte)[]) ||
 932             __traits(isIntegral, T) ||
 933             (__traits(isFloating, T)) ||
 934             is(T:DBFieldIndex) || is(T:DBFieldType))
 935       { pragma(inline, true); return this.to!T(idx); }
 936       T opIndexImpl(T) (const(char)[] name)
 937         if (is(T:const(char)[]) ||
 938             is(T:const(byte)[]) ||
 939             is(T:const(ubyte)[]) ||
 940             __traits(isIntegral, T) ||
 941             (__traits(isFloating, T)) ||
 942             is(T:DBFieldIndex) || is(T:DBFieldType))
 943       { pragma(inline, true); return this.to!T(name); }
 944       alias opIndex = opIndexImpl;
 945     }
 946
 947     template opDispatch(string name) {
 948       T opDispatchImpl(T=const(char)[]) ()
 949         if (is(T:const(char)[]) ||
 950             is(T:const(byte)[]) ||
 951             is(T:const(ubyte)[]) ||
 952             __traits(isIntegral, T) ||
 953             (__traits(isFloating, T)) ||
 954             is(T:DBFieldIndex) || is(T:DBFieldType))
 955       { pragma(inline, true); return this.to!T(name); }
 956       alias opDispatch = opDispatchImpl;
 957     }
 958
 959     auto index_ () pure const nothrow @nogc { return (data____.stepIndex > 0 ? data____.stepIndex-1 : 0); }
 960   } // end of DBRow
 961
 962 private:
 963   struct DBRowRange {
 964   private:
 965     /*DBStatement.StmtData* */usize datau;
 966
 967     @property inout(DBStatement.StmtData)* data () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(inout(DBStatement.StmtData)*)datau; }
 968
 969   public:
 970     @disable this (usize);
 971
 972     private this (ref DBStatement astat) {
 973       datau = astat.datau;
 974       DBStatement.incrowref(datau);
 975       assert(data.stepIndex == 0);
 976       data.stepIndex = 1;
 977       // perform first step
 978       popFront!false();
 979     }
 980
 981     this (this) nothrow @trusted @nogc { pragma(inline, true); DBStatement.incrowref(datau); }
 982     ~this () nothrow @trusted { pragma(inline, true); DBStatement.decrowref(datau); }
 983
 984     @property bool empty () const pure nothrow @trusted @nogc { pragma(inline, true); return (data.stepIndex == 0); }
 985
 986     @property auto front () {
 987       if (data.stepIndex == 0) throw new SQLiteException("can't get front element of completed statement");
 988       return DBRow(data);
 989     }
 990
 991     void popFront(bool incrow=true) () {
 992       if (data.stepIndex == 0) throw new SQLiteException("can't pop element of completed statement");
 993       auto rc = sqlite3_step(data.st);
 994       if (rc == SQLITE_DONE) {
 995         data.stepIndex = 0;
 996         return;
 997       }
 998       if (rc != SQLITE_ROW) {
 999         data.stepIndex = 0;
1000         sq3check(data.owner.db, rc);
1001       }
1002       static if (incrow) ++data.stepIndex;
1003     }
1004
1005     auto index_ () pure const nothrow @trusted @nogc { pragma(inline, true); return (data.stepIndex > 0 ? data.stepIndex-1 : 0); }
1006   } // end of DBRowRange
1007 }
1008
1009
1010 shared static this () {
1011   if (sqlite3_libversion_number() < SQLITE_VERSION_NUMBER) {
1012     //import core.stdc.string : strlen;
1013     //immutable(char)* lver = sqlite3_libversion();
1014     //auto len = strlen(lver);
1015     //{ import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "VER(%u): <%s> : <%s>\n", cast(uint)len, lver, sqlite3_version); }
1016     throw new Exception("expected SQLite at least v"~SQLITE_VERSION);
1017   }
1018 }