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