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