sq3_old.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_old /*is aliced*/;
  19 //pragma(lib, "sqlite3");
  20
  21 //version = sq3_debug_stmtlist;
  22
  23 import iv.alice;
  24
  25 //public import etc.c.sqlite3;
  26 public import iv.c.sqlite3;
  27 import std.traits;
  28 import std.range.primitives;
  29
  30 private import std.internal.cstring : tempCString;
  31
  32 version(none) {
  33 public {
  34   //enum SQLITE_DETERMINISTIC = 0x800;
  35   enum SQLITE_DIRECTONLY = 0x000080000;
  36   enum SQLITE_SUBTYPE    = 0x000100000;
  37   enum SQLITE_INNOCUOUS  = 0x000200000;
  38
  39   enum SQLITE_PREPARE_PERSISTENT = 0x01U;
  40   enum SQLITE_PREPARE_NORMALIZE  = 0x02U;
  41   enum SQLITE_PREPARE_NO_VTAB    = 0x04U;
  42
  43 extern(C) {
  44   int sqlite3_prepare_v3(
  45     sqlite3 *db,            /** Database handle */
  46     const(char)*zSql,       /** SQL statement, UTF-8 encoded */
  47     int nByte,              /** Maximum length of zSql in bytes. */
  48     uint prepFlags,         /* Zero or more SQLITE_PREPARE_ flags */
  49     sqlite3_stmt **ppStmt,  /** OUT: Statement handle */
  50     const(char*)*pzTail     /** OUT: Pointer to unused portion of zSql */
  51   );
  52 }
  53 }
  54 }
  55
  56
  57 ////////////////////////////////////////////////////////////////////////////////
  58 mixin(NewExceptionClass!("SQLiteException", "Exception"));
  59
  60 class SQLiteErr : SQLiteException {
  61   int code;
  62
  63   this (string msg, string file=__FILE__, usize line=__LINE__, Throwable next=null) @trusted nothrow {
  64     code = SQLITE_ERROR;
  65     super("SQLite ERROR: "~msg, file, line, next);
  66   }
  67
  68   this (sqlite3* db, int rc, string file=__FILE__, usize line=__LINE__, Throwable next=null) @trusted nothrow {
  69     //import core.stdc.stdio : stderr, fprintf;
  70     //fprintf(stderr, "SQLITE ERROR: %s\n", sqlite3_errstr(rc));
  71     code = rc;
  72     if (rc == SQLITE_OK) {
  73       super("SQLite ERROR: no error!", file, line, next);
  74     } else {
  75       import std.exception : assumeUnique;
  76       import std.string : fromStringz;
  77       if (db) {
  78         super(sqlite3_errstr(sqlite3_extended_errcode(db)).fromStringz.assumeUnique, file, line, next);
  79       } else {
  80         super(sqlite3_errstr(rc).fromStringz.assumeUnique, file, line, next);
  81       }
  82     }
  83   }
  84 }
  85
  86
  87 public void sq3check (sqlite3* db, int rc, string file=__FILE__, usize line=__LINE__) {
  88   //pragma(inline, true);
  89   if (rc != SQLITE_OK) throw new SQLiteErr(db, rc, file, line);
  90 }
  91
  92
  93 ////////////////////////////////////////////////////////////////////////////////
  94 /*
  95 //k8: nope, don't do this, because users may want to call `sqlite3_config()` before it
  96 shared static this () {
  97   if (sqlite3_initialize() != SQLITE_OK) throw new Error("can't initialize SQLite");
  98 }
  99
 100 // and this is not required at all
 101 shared static ~this () {
 102   sqlite3_shutdown();
 103 }
 104 */
 105
 106
 107 // use this in `to` to avoid copying
 108 public alias SQLStringc = const(char)[];
 109 // use this in `to` to avoid copying
 110 public alias SQ3Blob = const(char)[];
 111 // use this in `to` to avoid copying
 112 public alias SQ3Text = const(char)[];
 113
 114
 115 ////////////////////////////////////////////////////////////////////////////////
 116 // WARNING! don't forget to finalize ALL prepared statements!
 117 struct Database {
 118 private:
 119   static struct DBInfo {
 120     sqlite3* db = null;
 121     uint rc = 0;
 122     usize onCloseSize = 0;
 123     char *onClose = null; // 0-terminated
 124     DBStatement.Data *stmthead = null;
 125     DBStatement.Data *stmttail = null;
 126   }
 127
 128   DBInfo* dbi = null;
 129
 130 private:
 131   // caller should perform all necessary checks
 132   void clearStatements () nothrow @trusted {
 133     while (dbi.stmthead !is null) {
 134       version(sq3_debug_stmtlist) { import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "clearStatements(%p): p=%p\n", dbi, dbi.stmthead); }
 135       DBStatement.Data *dd = dbi.stmthead;
 136       dbi.stmthead = dd.next;
 137       dd.owner = null;
 138       dd.prev = null;
 139       dd.next = null;
 140       dd.stepIndex = 0;
 141       if (dd.st !is null) {
 142         sqlite3_reset(dd.st);
 143         sqlite3_clear_bindings(dd.st);
 144         sqlite3_finalize(dd.st);
 145         dd.st = null;
 146       }
 147     }
 148     dbi.stmttail = null;
 149   }
 150
 151 public:
 152   enum Mode {
 153     ReadOnly,
 154     ReadWrite,
 155     ReadWriteCreate,
 156   }
 157
 158 public:
 159   // `null` schema means "open as R/O"
 160   // non-null, but empty schema means "open as r/w"
 161   // non-empty scheme means "create if absent"
 162   this (const(char)[] name, Mode mode, const(char)[] pragmas=null, const(char)[] schema=null) { openEx(name, mode, pragmas, schema); }
 163   this (const(char)[] name, const(char)[] schema) { openEx(name, (schema !is null ? Mode.ReadWriteCreate : Mode.ReadOnly), null, schema); }
 164   ~this () nothrow @trusted { close(); }
 165
 166   this (this) nothrow @trusted @nogc { if (dbi !is null) ++dbi.rc; }
 167
 168   @property bool isOpen () const pure nothrow @safe @nogc { return (dbi !is null && dbi.db !is null); }
 169
 170   void close () nothrow @trusted {
 171     if (dbi !is null) {
 172       if (--dbi.rc == 0) {
 173         import core.stdc.stdlib : free;
 174         if (dbi.db !is null) {
 175           clearStatements();
 176           if (dbi.onClose !is null) {
 177             auto rc = sqlite3_exec(dbi.db, dbi.onClose, null, null, null);
 178             if (rc != SQLITE_OK) {
 179               import core.stdc.stdio : stderr, fprintf;
 180               fprintf(stderr, "SQLITE ERROR ON CLOSE: %s\n", sqlite3_errstr(sqlite3_extended_errcode(dbi.db)));
 181             }
 182             version(none) {
 183               import core.stdc.stdio : stderr, fprintf;
 184               fprintf(stderr, "exec:===\n%s\n===\n", dbi.onClose);
 185             }
 186             free(dbi.onClose);
 187           }
 188           sqlite3_close_v2(dbi.db);
 189         }
 190         free(dbi);
 191       }
 192       dbi = null;
 193     }
 194   }
 195
 196   void appendOnClose (const(char)[] stmts) {
 197     if (!isOpen) throw new SQLiteException("database is not opened");
 198     while (stmts.length && stmts[0] <= 32) stmts = stmts[1..$];
 199     while (stmts.length && stmts[$-1] <= 32) stmts = stmts[0..$-1];
 200     if (stmts.length == 0) return;
 201     import core.stdc.stdlib : realloc;
 202     //FIXME: overflow. don't do it.
 203     usize nsz = dbi.onCloseSize+stmts.length;
 204     if (nsz+1 <= dbi.onCloseSize) throw new SQLiteException("out of memory for OnClose");
 205     char *np = cast(char *)realloc(dbi.onClose, nsz+1);
 206     if (np is null) throw new SQLiteException("out of memory for OnClose");
 207     dbi.onClose = np;
 208     np[dbi.onCloseSize..dbi.onCloseSize+stmts.length] = stmts[];
 209     dbi.onCloseSize += stmts.length;
 210     np[dbi.onCloseSize] = 0;
 211   }
 212
 213   void setOnClose (const(char)[] stmts) {
 214     if (!isOpen) throw new SQLiteException("database is not opened");
 215     if (dbi.onClose !is null) {
 216       import core.stdc.stdlib : free;
 217       free(dbi.onClose);
 218       dbi.onClose = null;
 219       dbi.onCloseSize = 0;
 220     }
 221     appendOnClose(stmts);
 222   }
 223
 224   // `null` schema means "open as R/O"
 225   // non-null, but empty schema means "open as r/w"
 226   // non-empty scheme means "create is absend"
 227   void openEx (const(char)[] name, Mode mode, const(char)[] pragmas=null, const(char)[] schema=null) {
 228     close();
 229     import core.stdc.stdlib : malloc, free;
 230     import std.internal.cstring;
 231     immutable bool allowWrite = (mode == Mode.ReadWrite || mode == Mode.ReadWriteCreate);
 232     bool allowCreate = (mode == Mode.ReadWriteCreate);
 233     if (allowCreate) {
 234       while (schema.length && schema[$-1] <= ' ') schema = schema[0..$-1];
 235       if (schema.length == 0) allowCreate = false;
 236     }
 237     dbi = cast(DBInfo *)malloc(DBInfo.sizeof);
 238     if (!dbi) throw new Error("out of memory");
 239     *dbi = DBInfo.init;
 240     dbi.rc = 1;
 241     immutable int rc = sqlite3_open_v2(name.tempCString, &dbi.db, (allowWrite ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY)|(allowCreate ? SQLITE_OPEN_CREATE : 0), null);
 242     if (rc != SQLITE_OK) {
 243       free(dbi);
 244       dbi = null;
 245       sq3check(null, rc);
 246     }
 247     scope(failure) { close(); }
 248     execute(pragmas);
 249     if (allowCreate && schema.length) execute(schema);
 250   }
 251
 252   // `null` schema means "open as R/O"
 253   void open (const(char)[] name, const(char)[] schema=null) {
 254     Mode mode = Mode.ReadOnly;
 255     if (schema != null) {
 256       while (schema.length && schema[$-1] <= ' ') schema = schema[0..$-1];
 257       mode = (schema.length ? Mode.ReadWriteCreate : Mode.ReadWrite);
 258     }
 259     return openEx(name, mode, null, schema);
 260   }
 261
 262   ulong lastRowId () nothrow @trusted { return (isOpen ? sqlite3_last_insert_rowid(dbi.db) : 0); }
 263
 264   void setBusyTimeout (int msecs) nothrow @trusted { if (isOpen) sqlite3_busy_timeout(dbi.db, msecs); }
 265
 266   // execute one or more SQL statements
 267   // SQLite will take care of splitting
 268   void execute (const(char)[] ops) {
 269     if (!isOpen) throw new SQLiteException("database is not opened");
 270     import std.internal.cstring;
 271     //char* errmsg;
 272     immutable int rc = sqlite3_exec(dbi.db, ops.tempCString, null, null, null/*&errmsg*/);
 273     if (rc != SQLITE_OK) {
 274       //import core.stdc.stdio : stderr, fprintf;
 275       //fprintf(stderr, "SQLITE ERROR: %s\n", errmsg);
 276       //sqlite3_free(errmsg);
 277       sq3check(dbi.db, rc);
 278     }
 279   }
 280
 281   // create prepared SQL statement
 282   DBStatement statement (const(char)[] stmtstr, bool persistent=false) {
 283     if (!isOpen) throw new SQLiteException("database is not opened");
 284     return DBStatement(dbi, stmtstr, persistent);
 285   }
 286
 287   DBStatement persistentStatement (const(char)[] stmtstr) {
 288     return statement(stmtstr, persistent:true);
 289   }
 290
 291   sqlite3* getHandle () nothrow @nogc @trusted { return (isOpen ? dbi.db : null); }
 292
 293   extern(C) {
 294     alias UserFn = void function (sqlite3_context *ctx, int argc, sqlite3_value **argv);
 295   }
 296
 297   void createFunction (const(char)[] name, int argc, UserFn xFunc, bool deterministic=true, int moreflags=0) {
 298     import std.internal.cstring : tempCString;
 299     if (!isOpen) throw new SQLiteException("database is not opened");
 300     immutable int rc = sqlite3_create_function(dbi.db, name.tempCString, argc, SQLITE_UTF8|(deterministic ? SQLITE_DETERMINISTIC : 0)|moreflags, null, xFunc, null, null);
 301     sq3check(dbi.db, rc);
 302   }
 303 }
 304
 305
 306 // ////////////////////////////////////////////////////////////////////////// //
 307 public static bool isUTF8ValidSQ3 (const(char)[] str) pure nothrow @trusted @nogc {
 308   usize len = str.length;
 309   immutable(ubyte)* p = cast(immutable(ubyte)*)str.ptr;
 310   while (len--) {
 311     immutable ubyte b = *p++;
 312     if (b == 0) return false;
 313     if (b < 128) continue;
 314     ubyte blen =
 315       (b&0xe0) == 0xc0 ? 1 :
 316       (b&0xf0) == 0xe0 ? 2 :
 317       (b&0xf8) == 0xe8 ? 3 :
 318       0; // no overlongs
 319     if (!blen) return false;
 320     if (len < blen) return false;
 321     len -= blen;
 322     while (blen--) {
 323       immutable ubyte b1 = *p++;
 324       if ((b1&0xc0) != 0x80) return false;
 325     }
 326   }
 327   return true;
 328 }
 329
 330
 331 // ////////////////////////////////////////////////////////////////////////// //
 332 struct DBFieldIndex {
 333   uint idx;
 334 }
 335
 336 struct DBFieldType {
 337   enum {
 338     Unknown,
 339     Integer,
 340     Float,
 341     Text,
 342     Blob,
 343     Null,
 344   }
 345   uint idx;
 346
 347   string toString () const pure nothrow @trusted @nogc {
 348     switch (idx) {
 349       case Unknown: return "Unknown";
 350       case Integer: return "Integer";
 351       case Float: return "Float";
 352       case Text: return "Text";
 353       case Blob: return "Blob";
 354       case Null: return "Null";
 355       default: break;
 356     }
 357     return "Invalid";
 358   }
 359 }
 360
 361 struct DBStatement {
 362 private:
 363   enum FieldIndexMixin = `
 364     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 365     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 366     char[257] fldname = 0;
 367     if (name.length > 255) throw new SQLiteException("field name too long");
 368     if (name[0] == ':' || name[0] == '?' || name[0] == '@') {
 369       fldname[0..name.length] = name[];
 370     } else {
 371       fldname[0] = ':';
 372       fldname[1..name.length+1] = name[];
 373     }
 374     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 375     if (idx < 1) throw new SQLiteException("invalid field name: '"~name.idup~"'");
 376   `;
 377
 378 public:
 379   this (this) nothrow @trusted @nogc { this.incref(data); }
 380   ~this () nothrow @trusted { this.decref(data); data = null; }
 381
 382   private this (Database.DBInfo *dbi, const(char)[] stmtstr, bool persistent=false) {
 383     if (dbi is null || dbi.db is null) throw new SQLiteException("database is not opened");
 384     if (stmtstr.length > int.max/4) throw new SQLiteException("statement too big");
 385     import core.stdc.stdlib : malloc;
 386     data = cast(Data*)malloc(Data.sizeof);
 387     if (data is null) {
 388       import core.exception : onOutOfMemoryErrorNoGC;
 389       onOutOfMemoryErrorNoGC();
 390     }
 391     *data = Data.init;
 392     data.refcount = 1;
 393     // register in the owner list
 394     version(sq3_debug_stmtlist) { import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "new stmt(%p): p=%p\n", dbi, data); }
 395     data.owner = dbi;
 396     data.prev = dbi.stmttail;
 397     if (dbi.stmttail !is null) dbi.stmttail.next = data; else dbi.stmthead = data;
 398     dbi.stmttail = data;
 399     // done registering
 400     scope(failure) { data.st = null; DBStatement.decref(data); }
 401     const(char)* e;
 402     if (persistent) {
 403       sq3check(dbi.db, sqlite3_prepare_v3(dbi.db, stmtstr.ptr, cast(int)stmtstr.length, SQLITE_PREPARE_PERSISTENT, &data.st, &e));
 404     } else {
 405       sq3check(dbi.db, sqlite3_prepare_v2(dbi.db, stmtstr.ptr, cast(int)stmtstr.length, &data.st, &e));
 406     }
 407     // check for extra code
 408     if (e !is null) {
 409       usize left = stmtstr.length-cast(usize)(e-stmtstr.ptr);
 410       //{ import core.stdc.stdio : printf; printf("%u: <%.*s>\n", cast(uint)left, cast(uint)left, e); }
 411       while (left--) {
 412         char ch = *e++;
 413         //{ import core.stdc.stdio : printf; printf("left=%u: ch=%u\n", cast(uint)left, cast(uint)ch); }
 414         if (ch <= 32 || ch == ';') continue;
 415         if (ch == '-') {
 416           if (left < 1 || *e != '-') throw new SQLiteErr("extra code in SQL statement (--)");
 417           while (left && *e != '\n') { --left; ++e; }
 418           continue;
 419         }
 420         if (ch == '/') {
 421           if (left < 1 || *e != '*') throw new SQLiteErr("extra code in SQL statement (mlc)");
 422           --left;
 423           ++e;
 424           while (left > 1) {
 425             if (*e == '*' && e[1] == '/') {
 426               left -= 2;
 427               e += 2;
 428               break;
 429             }
 430             --left;
 431             ++e;
 432           }
 433         }
 434         throw new SQLiteErr("extra code in SQL statement (text): "~e[0..left].idup);
 435       }
 436     }
 437   }
 438
 439   @property bool valid () nothrow @trusted @nogc { return (data !is null && data.st !is null); }
 440
 441   void close () nothrow @trusted { if (data !is null) this.decref(data); data = null; }
 442
 443   @property auto range () {
 444     if (!valid) throw new SQLiteException("cannot get range from invalid statement");
 445     //if (st is null) throw new SQLiteException("statement is not prepared");
 446     if (data.stepIndex != 0) throw new SQLiteException("can't get range from busy statement");
 447     return DBRowRange(this);
 448   }
 449
 450   void reset () nothrow @trusted {
 451     //if (data.stepIndex != 0) throw new SQLiteException("can't reset busy statement");
 452     if (valid) {
 453       data.stepIndex = 0;
 454       sqlite3_reset(data.st);
 455       sqlite3_clear_bindings(data.st);
 456     }
 457   }
 458
 459   void doAll () {
 460     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 461     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 462     scope(exit) reset();
 463     for (;;) {
 464       auto rc = sqlite3_step(data.st);
 465       if (rc == SQLITE_DONE) break;
 466       if (rc != SQLITE_ROW) sq3check(data.owner.db, rc);
 467     }
 468   }
 469
 470   ref DBStatement bind(T) (uint idx, T value) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 471     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 472     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 473     if (idx < 1 || idx > sqlite3_bind_parameter_count(data.st)) {
 474       import std.conv : to;
 475       throw new SQLiteException("invalid field index: "~to!string(idx));
 476     }
 477     int rc;
 478     static if (is(T == typeof(null))) {
 479       rc = sqlite3_bind_null(data.st, idx);
 480     } else static if (isNarrowString!T) {
 481       if (value.length > cast(usize)int.max) throw new SQLiteException("value too big");
 482       static if (is(ElementEncodingType!T == immutable(char))) {
 483         version(none) {
 484           if (isUTF8ValidSQ3(value[])) {
 485             rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 486           } else {
 487             rc = sqlite3_bind_blob(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 488           }
 489         } else {
 490           rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 491         }
 492       } else {
 493         version(none) {
 494           if (isUTF8ValidSQ3(value[])) {
 495             rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 496           } else {
 497             rc = sqlite3_bind_blob(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 498           }
 499         } else {
 500             rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 501         }
 502       }
 503     } else static if (isIntegral!T) {
 504       static if (isSigned!T) {
 505         // signed ints
 506         static if (T.sizeof <= 4) {
 507           rc = sqlite3_bind_int(data.st, idx, cast(int)value);
 508         } else {
 509           rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 510         }
 511       } else {
 512         // unsigned ints
 513         static if (T.sizeof < 4) {
 514           rc = sqlite3_bind_int(data.st, idx, cast(int)cast(uint)value);
 515         } else {
 516           rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 517         }
 518       }
 519     } else {
 520       static assert(0, "WTF?!");
 521     }
 522     sq3check(data.owner.db, rc);
 523     return this;
 524   }
 525
 526   ref DBStatement bind(T) (const(char)[] name, T value) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 527     mixin(FieldIndexMixin);
 528     return bind!T(idx, value);
 529   }
 530
 531
 532   ref DBStatement bindText (uint idx, const(void)[] text, bool transient=true, bool allowNull=false) {
 533     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 534     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 535     if (idx < 1) {
 536       import std.conv : to;
 537       throw new SQLiteException("invalid field index: "~to!string(idx));
 538     }
 539     int rc;
 540     if (text is null) {
 541       if (allowNull) {
 542         rc = sqlite3_bind_null(data.st, idx);
 543       } else {
 544         rc = sqlite3_bind_text(data.st, idx, "".ptr, 0, SQLITE_STATIC);
 545       }
 546     } else {
 547       rc = sqlite3_bind_text(data.st, idx, cast(const(char)*)text.ptr, cast(int)text.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 548     }
 549     sq3check(data.owner.db, rc);
 550     return this;
 551   }
 552
 553   ref DBStatement bindText (const(char)[] name, const(void)[] text, bool transient=true, bool allowNull=false) {
 554     mixin(FieldIndexMixin);
 555     return bindText(cast(uint)idx, text, transient, allowNull);
 556   }
 557
 558
 559   ref DBStatement bindBlob (uint idx, const(void)[] blob, bool transient=true, bool allowNull=false) {
 560     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 561     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 562     if (idx < 1) {
 563       import std.conv : to;
 564       throw new SQLiteException("invalid field index: "~to!string(idx));
 565     }
 566     int rc;
 567     if (blob is null) {
 568       if (allowNull) {
 569         rc = sqlite3_bind_null(data.st, idx);
 570       } else {
 571         rc = sqlite3_bind_blob(data.st, idx, "".ptr, 0, SQLITE_STATIC);
 572       }
 573     } else {
 574       rc = sqlite3_bind_blob(data.st, idx, blob.ptr, cast(int)blob.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 575     }
 576     sq3check(data.owner.db, rc);
 577     return this;
 578   }
 579
 580   ref DBStatement bindBlob (const(char)[] name, const(void)[] blob, bool transient=true, bool allowNull=false) {
 581     mixin(FieldIndexMixin);
 582     return bindBlob(cast(uint)idx, blob, transient, allowNull);
 583   }
 584
 585
 586   ref DBStatement bindNull (uint idx) {
 587     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 588     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 589     if (idx < 1) {
 590       import std.conv : to;
 591       throw new SQLiteException("invalid field index: "~to!string(idx));
 592     }
 593     immutable int rc = sqlite3_bind_null(data.st, idx);
 594     sq3check(data.owner.db, rc);
 595     return this;
 596   }
 597
 598   ref DBStatement bindNull (const(char)[] name) {
 599     mixin(FieldIndexMixin);
 600     return bindNull(cast(uint)idx);
 601   }
 602
 603
 604   ref DBStatement bindInt(T) (uint idx, T v) if (isIntegral!T) {
 605     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 606     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 607     if (idx < 1) {
 608       import std.conv : to;
 609       throw new SQLiteException("invalid field index: "~to!string(idx));
 610     }
 611     static if (isSigned!T) {
 612       immutable int rc = sqlite3_bind_int64(data.st, idx, cast(long)v);
 613     } else {
 614       immutable int rc = sqlite3_bind_int64(data.st, idx, cast(ulong)v);
 615     }
 616     sq3check(data.owner.db, rc);
 617     return this;
 618   }
 619
 620   ref DBStatement bindInt(T) (const(char)[] name, T v) if (isIntegral!T) {
 621     mixin(FieldIndexMixin);
 622     return bindInt(cast(uint)idx, v);
 623   }
 624
 625
 626   ref DBStatement bindFloat(T) (uint idx, T v) if (is(T == float) || is(T == double)) {
 627     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 628     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 629     if (idx < 1) {
 630       import std.conv : to;
 631       throw new SQLiteException("invalid field index: "~to!string(idx));
 632     }
 633     rc = sqlite3_bind_double(data.st, idx, cast(double)v);
 634     sq3check(data.owner.db, rc);
 635     return this;
 636   }
 637
 638   ref DBStatement bindFloat(T) (const(char)[] name, T v) if (is(T == float) || is(T == double)) {
 639     mixin(FieldIndexMixin);
 640     return bindFloat(cast(uint)idx, v);
 641   }
 642
 643   int colcount () nothrow @trusted {
 644     if (!valid) return 0;
 645     return sqlite3_column_count(data.st);
 646   }
 647
 648   const(char)[] colname (int idx) nothrow @trusted {
 649     if (!valid || idx < 0) return null;
 650     if (idx >= sqlite3_column_count(data.st)) return null;
 651     const(char)* cname = sqlite3_column_name(data.st, idx);
 652     if (cname is null) return null;
 653     usize ep = 0;
 654     while (cname[ep]) ++ep;
 655     return cname[0..ep];
 656   }
 657
 658 private:
 659   struct DBRow {
 660     private this (DBStatement.Data* adata) nothrow @trusted @nogc {
 661       data____ = adata;
 662       DBStatement.incref(data____);
 663       ++data____.rowcount;
 664     }
 665
 666     this (this) nothrow @trusted @nogc { DBStatement.incref(data____); ++data____.rowcount; }
 667
 668     ~this () nothrow @trusted {
 669       DBStatement.decrowref(data____);
 670       DBStatement.decref(data____);
 671     }
 672
 673     bool valid_ () pure nothrow @trusted @nogc {
 674       pragma(inline, true);
 675       return (data____.stepIndex > 0 && data____.st !is null);
 676     }
 677
 678     int colcount_ () nothrow @trusted {
 679       if (data____.st is null) return 0;
 680       return sqlite3_column_count(data____.st);
 681     }
 682
 683     const(char)[] colname_ (int idx) nothrow @trusted {
 684       if (idx < 0 || data____.st is null) return null;
 685       if (idx >= sqlite3_column_count(data____.st)) return null;
 686       const(char)* cname = sqlite3_column_name(data____.st, idx);
 687       if (cname is null) return null;
 688       usize ep = 0;
 689       while (cname[ep]) ++ep;
 690       return cname[0..ep];
 691     }
 692
 693     int fieldIndex____ (const(char)[] name) {
 694       if (name.length > 0) {
 695         foreach (immutable int idx; 0..sqlite3_data_count(data____.st)) {
 696           import core.stdc.string : memcmp, strlen;
 697           auto n = sqlite3_column_name(data____.st, idx);
 698           if (n !is null) {
 699             auto len = strlen(n);
 700             if (len == name.length && memcmp(n, name.ptr, len) == 0) return idx;
 701           }
 702         }
 703       }
 704       throw new SQLiteException("invalid field name: '"~name.idup~"'");
 705     }
 706
 707     T to(T) (uint idx)
 708       if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T || is(T : DBFieldIndex) || is(T : DBFieldType) ||
 709           is(T == float) || is(T == double))
 710     {
 711       if (!valid_) throw new SQLiteException("can't get row field of completed statement");
 712       if (idx >= sqlite3_data_count(data____.st)) throw new SQLiteException("invalid result index");
 713       static if (is(T : DBFieldIndex)) {
 714         return DBFieldIndex(idx);
 715       } else static if (is(T : DBFieldType)) {
 716         switch (sqlite3_column_type(data____.st, idx)) {
 717           case SQLITE_INTEGER: return DBFieldType(DBFieldType.Integer);
 718           case SQLITE_FLOAT: return DBFieldType(DBFieldType.Float);
 719           case SQLITE3_TEXT: return DBFieldType(DBFieldType.Text);
 720           case SQLITE_BLOB: return DBFieldType(DBFieldType.Blob);
 721           case SQLITE_NULL: return DBFieldType(DBFieldType.Null);
 722           default: break;
 723         }
 724         return DBFieldType(DBFieldType.Unknown);
 725       } else static if (isIntegral!T) {
 726         auto res = sqlite3_column_int64(data____.st, idx);
 727         if (res < T.min || res > T.max) throw new SQLiteException("integral overflow");
 728         return cast(T)res;
 729       } else static if (is(T == double)) {
 730         auto res = sqlite3_column_double(data____.st, idx);
 731         return cast(double)res;
 732       } else static if (is(T == float)) {
 733         auto res = sqlite3_column_double(data____.st, idx);
 734         return cast(float)res;
 735       } else {
 736         auto len = sqlite3_column_bytes(data____.st, idx);
 737         if (len < 0) throw new SQLiteException("invalid result");
 738         const(char)* res = cast(const(char)*)sqlite3_column_blob(data____.st, idx);
 739         if (len == 0) res = ""; else if (res is null) throw new SQLiteException("invalid result");
 740         static if (is(ElementEncodingType!T == const(char))) {
 741           return res[0..len];
 742         } else static if (is(ElementEncodingType!T == immutable(char))) {
 743           return res[0..len].idup;
 744         } else {
 745           return res[0..len].dup;
 746         }
 747       }
 748     }
 749     T to(T) (const(char)[] name) { return this.to!T(fieldIndex____(name)); }
 750
 751     const(ubyte)[] blob_ (const(char)[] name) {
 752       immutable int idx = fieldIndex____(name);
 753       auto len = sqlite3_column_bytes(data____.st, idx);
 754       if (len < 0) throw new SQLiteException("invalid result");
 755       const(ubyte)* res = cast(const(ubyte)*)sqlite3_column_blob(data____.st, idx);
 756       return res[0..len];
 757     }
 758
 759     template opIndex() {
 760       T opIndexImpl(T) (uint idx) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T || is(T : DBFieldIndex) || is(T : DBFieldType)) { return this.to!T(idx); }
 761       T opIndexImpl(T) (const(char)[] name) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T || is(T : DBFieldIndex) || is(T : DBFieldType)) { return this.to!T(name); }
 762       alias opIndex = opIndexImpl;
 763     }
 764
 765     template opDispatch(string name) {
 766       T opDispatchImpl(T=const(char)[]) () if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T || is(T : DBFieldIndex) || is(T : DBFieldType)) { return this.to!T(name); }
 767       alias opDispatch = opDispatchImpl;
 768     }
 769
 770     auto index_ () pure const nothrow @nogc { return (data____.stepIndex > 0 ? data____.stepIndex-1 : 0); }
 771
 772     private DBStatement.Data* data____;
 773   } // end of DBRow
 774
 775 private:
 776   struct DBRowRange {
 777     private this (ref DBStatement astat) {
 778       data = astat.data;
 779       DBStatement.incref(data);
 780       ++data.rowcount;
 781       assert(data.stepIndex == 0);
 782       data.stepIndex = 1;
 783       popFront();
 784     }
 785
 786     this (this) nothrow @trusted @nogc { DBStatement.incref(data); ++data.rowcount; }
 787
 788     ~this () nothrow @trusted {
 789       DBStatement.decrowref(data);
 790       DBStatement.decref(data);
 791     }
 792
 793     @property bool empty () const pure nothrow @nogc { return (data.stepIndex == 0); }
 794
 795     @property auto front () {
 796       if (data.stepIndex == 0) throw new SQLiteException("can't get front element of completed statement");
 797       return DBRow(data);
 798     }
 799
 800     void popFront () {
 801       if (data.stepIndex == 0) throw new SQLiteException("can't pop element of completed statement");
 802       auto rc = sqlite3_step(data.st);
 803       if (rc == SQLITE_DONE) {
 804         data.stepIndex = 0;
 805         return;
 806       }
 807       if (rc != SQLITE_ROW) {
 808         data.stepIndex = 0;
 809         sq3check(data.owner.db, rc);
 810       }
 811       ++data.stepIndex;
 812     }
 813
 814     auto index_ () pure const nothrow @nogc { return (data.stepIndex > 0 ? data.stepIndex-1 : 0); }
 815
 816     private DBStatement.Data* data;
 817   } // end of DBRowRange
 818
 819 private:
 820   static void incref (Data* data) nothrow @nogc @trusted {
 821     if (data !is null) ++data.refcount;
 822   }
 823
 824   static void decref (Data* data) nothrow @trusted {
 825     if (data !is null) {
 826       if (--data.refcount == 0) {
 827         import core.stdc.stdlib : free;
 828         if (data.st !is null) {
 829           sqlite3_reset(data.st);
 830           sqlite3_clear_bindings(data.st);
 831           sqlite3_finalize(data.st);
 832         }
 833         // unregister from the owner list
 834         if (data.owner) {
 835           version(sq3_debug_stmtlist) { import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "removing stmt(%p): p=%p\n", data.owner, data); }
 836           if (data.prev) data.prev.next = data.next; else data.owner.stmthead = data.next;
 837           if (data.next) data.next.prev = data.prev; else data.owner.stmttail = data.prev;
 838           data.owner = null;
 839         }
 840         free(data);
 841         data = null;
 842       }
 843     }
 844   }
 845
 846   static void decrowref (Data* data) nothrow @trusted {
 847     if (data !is null) {
 848       if (--data.rowcount == 0) {
 849         data.stepIndex = 0;
 850         if (data.st !is null) {
 851           sqlite3_reset(data.st);
 852           sqlite3_clear_bindings(data.st);
 853         }
 854       }
 855     }
 856   }
 857
 858 private:
 859   static struct Data {
 860     uint refcount = 0;
 861     uint rowcount = 0; // number of row structs using this statement
 862     uint stepIndex = 0;
 863     sqlite3_stmt* st = null;
 864     Data* prev = null;
 865     Data* next = null;
 866     Database.DBInfo* owner = null;
 867   }
 868   Data* data;
 869 }