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 pragma(lib, "sqlite3");
  20
  21 import iv.alice;
  22
  23 import etc.c.sqlite3;
  24 import std.traits;
  25 import std.range.primitives;
  26
  27
  28 ////////////////////////////////////////////////////////////////////////////////
  29 mixin(NewExceptionClass!("SQLiteException", "Exception"));
  30
  31 class SQLiteErr : SQLiteException {
  32   int code;
  33
  34   this (int rc, string file=__FILE__, usize line=__LINE__, Throwable next=null) @trusted nothrow {
  35     //import core.stdc.stdio : stderr, fprintf;
  36     //fprintf(stderr, "SQLITE ERROR: %s\n", sqlite3_errstr(rc));
  37     import std.exception : assumeUnique;
  38     import std.string : fromStringz;
  39     code = rc;
  40     super(sqlite3_errstr(rc).fromStringz.assumeUnique, file, line, next);
  41   }
  42 }
  43
  44
  45 private void sqcheck (int rc, string file=__FILE__, usize line=__LINE__) {
  46   //pragma(inline, true);
  47   if (rc != SQLITE_OK) throw new SQLiteErr(rc, file, line);
  48 }
  49
  50
  51 ////////////////////////////////////////////////////////////////////////////////
  52 shared static this () {
  53   if (sqlite3_initialize() != SQLITE_OK) throw new Error("can't initialize SQLite");
  54 }
  55
  56 shared static ~this () {
  57   sqlite3_shutdown();
  58 }
  59
  60
  61 public alias SQLStringc = const(char)[];
  62
  63
  64 ////////////////////////////////////////////////////////////////////////////////
  65 // WARNING! don't forget to finalize ALL prepared statements!
  66 struct Database {
  67 private:
  68   sqlite3* db;
  69
  70 public:
  71   @disable this (this); // no copies!
  72
  73   // `null` schema means "open as R/O"
  74   // non-null, but empty schema means "open as r/w"
  75   // non-empty scheme means "create is absend"
  76   this (const(char)[] name, const(char)[] schema=null) { open(name, schema); }
  77   ~this () { close(); }
  78
  79   // `null` schema means "open as R/O"
  80   // non-null, but empty schema means "open as r/w"
  81   // non-empty scheme means "create is absend"
  82   void open (const(char)[] name, const(char)[] schema=null) {
  83     close();
  84     import std.internal.cstring;
  85     bool allowCreate = false, allowWrite = false;
  86     if (schema !is null) {
  87       allowWrite = true;
  88       while (schema.length && schema[0] <= ' ') schema = schema[1..$];
  89       allowCreate = (schema.length != 0);
  90     }
  91     sqcheck(sqlite3_open_v2(name.tempCString, &db, (allowWrite ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY)|(allowCreate ? SQLITE_OPEN_CREATE : 0), null));
  92     scope(failure) { sqlite3_close_v2(db); db = null; }
  93     if (allowCreate) execute(schema);
  94   }
  95
  96   @property bool isOpen () const pure nothrow @safe @nogc { return (db !is null); }
  97
  98   void close () {
  99     if (db !is null) sqlite3_close_v2(db);
 100     db = null;
 101   }
 102
 103   ulong lastRowId () { return (db ? sqlite3_last_insert_rowid(db) : 0); }
 104
 105   // execute one or more SQL statements
 106   // SQLite will take care of splitting
 107   void execute (const(char)[] ops) {
 108     if (!isOpen) throw new SQLiteException("database is not opened");
 109     import std.internal.cstring;
 110     char* errmsg;
 111     auto rc = sqlite3_exec(db, ops.tempCString, null, null, &errmsg);
 112     if (rc != SQLITE_OK) {
 113       import core.stdc.stdio : stderr, fprintf;
 114       fprintf(stderr, "SQLITE ERROR: %s\n", errmsg);
 115       sqlite3_free(errmsg);
 116       sqcheck(rc);
 117     }
 118   }
 119
 120   // create prepared SQL statement
 121   DBStatement statement (const(char)[] stmtstr) {
 122     if (!isOpen) throw new SQLiteException("database is not opened");
 123     return DBStatement(db, stmtstr);
 124   }
 125 }
 126
 127
 128 // ////////////////////////////////////////////////////////////////////////// //
 129 public static bool isUTF8ValidSQ3 (const(char)[] str) pure nothrow @trusted @nogc {
 130   usize len = str.length;
 131   immutable(ubyte)* p = cast(immutable(ubyte)*)str.ptr;
 132   while (len--) {
 133     immutable ubyte b = *p++;
 134     if (b < 128) continue;
 135     ubyte blen =
 136       (b&0xe0) == 0xc0 ? 1 :
 137       (b&0xf0) == 0xe0 ? 2 :
 138       (b&0xf8) == 0xe8 ? 3 :
 139       0; // no overlongs
 140     if (!blen) return false;
 141     if (len < blen) return false;
 142     len -= blen;
 143     while (blen--) {
 144       immutable ubyte b1 = *p++;
 145       if ((b1&0xc0) != 0x80) return false;
 146     }
 147   }
 148   return true;
 149 }
 150
 151
 152 // ////////////////////////////////////////////////////////////////////////// //
 153 struct DBFieldIndex {
 154   uint idx;
 155 }
 156
 157 struct DBFieldType {
 158   enum {
 159     Unknown,
 160     Integer,
 161     Float,
 162     Text,
 163     Blob,
 164     Null,
 165   }
 166   uint idx;
 167
 168   string toString () const pure nothrow @trusted @nogc {
 169     switch (idx) {
 170       case Unknown: return "Unknown";
 171       case Integer: return "Integer";
 172       case Float: return "Float";
 173       case Text: return "Text";
 174       case Blob: return "Blob";
 175       case Null: return "Null";
 176       default: break;
 177     }
 178     return "Invalid";
 179   }
 180 }
 181
 182 struct DBStatement {
 183 public:
 184   this (this) { this.incref(data); }
 185   ~this () { this.decref(data); data = null; }
 186
 187   private this (sqlite3* db, const(char)[] stmtstr) {
 188     if (db is null) throw new SQLiteException("database is not opened");
 189     if (stmtstr.length > int.max) throw new SQLiteException("statement too big");
 190     import core.stdc.stdlib : malloc;
 191     data = cast(Data*)malloc(Data.sizeof);
 192     if (data is null) {
 193       import core.exception : onOutOfMemoryErrorNoGC;
 194       onOutOfMemoryErrorNoGC();
 195     }
 196     data.refcount = 1;
 197     data.rowcount = 0;
 198     data.stepIndex = 0;
 199     data.st = null;
 200     scope(failure) DBStatement.decref(data);
 201     const(char)* e;
 202     sqcheck(sqlite3_prepare_v2(db, stmtstr.ptr, cast(int)stmtstr.length, &data.st, &e));
 203   }
 204
 205   @property bool valid () @safe nothrow { return (data !is null); }
 206
 207   void close () { if (data !is null) this.decref(data); data = null; }
 208
 209   @property auto range () {
 210     if (!valid) throw new SQLiteException("cannot get range from invalid statement");
 211     //if (st is null) throw new SQLiteException("statement is not prepared");
 212     if (data.stepIndex != 0) throw new SQLiteException("can't get range from busy statement");
 213     return DBRowRange(this);
 214   }
 215
 216   void reset () {
 217     //if (data.stepIndex != 0) throw new SQLiteException("can't reset busy statement");
 218     if (valid) {
 219       data.stepIndex = 0;
 220       sqlite3_reset(data.st);
 221       sqlite3_clear_bindings(data.st);
 222     }
 223   }
 224
 225   void doAll () {
 226     if (!valid) throw new SQLiteException("cannot execute invalid statement");
 227     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 228     scope(exit) reset();
 229     for (;;) {
 230       auto rc = sqlite3_step(data.st);
 231       if (rc == SQLITE_DONE) break;
 232       if (rc != SQLITE_ROW) sqcheck(rc);
 233     }
 234   }
 235
 236   ref DBStatement bind(T) (uint idx, T value) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 237     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 238     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 239     if (idx < 1 || idx > sqlite3_bind_parameter_count(data.st)) {
 240       import std.conv : to;
 241       throw new SQLiteException("invalid field index: "~to!string(idx));
 242     }
 243     int rc;
 244     static if (isNarrowString!T) {
 245       if (value.length > int.max) throw new SQLiteException("value too big");
 246       static if (is(ElementEncodingType!T == immutable(char))) {
 247         version(none) {
 248           if (isUTF8ValidSQ3(value[])) {
 249             rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 250           } else {
 251             rc = sqlite3_bind_blob(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 252           }
 253         } else {
 254           rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 255         }
 256       } else {
 257         version(none) {
 258           if (isUTF8ValidSQ3(value[])) {
 259             rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 260           } else {
 261             rc = sqlite3_bind_blob(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 262           }
 263         } else {
 264             rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 265         }
 266       }
 267     } else static if (isIntegral!T) {
 268       static if (isSigned!T) {
 269         rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 270       } else {
 271         rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 272       }
 273     } else {
 274       static assert(0, "WTF?!");
 275     }
 276     sqcheck(rc);
 277     return this;
 278   }
 279
 280   ref DBStatement bind(T) (const(char)[] name, T value) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 281     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 282     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 283     char[257] fldname = 0;
 284     if (name.length > 255) throw new SQLiteException("field name too long");
 285     if (name[0] == ':' || name[0] == '?' || name[0] == '@') {
 286       fldname[0..name.length] = name[];
 287     } else {
 288       fldname[0] = ':';
 289       fldname[1..name.length+1] = name[];
 290     }
 291     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 292     if (idx < 1) throw new SQLiteException("invalid field name: '"~name.idup~"'");
 293     return bind!T(idx, value);
 294   }
 295
 296
 297   ref DBStatement bindText (uint idx, const(void)[] text, bool transient=true) {
 298     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 299     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 300     if (idx < 1) {
 301       import std.conv : to;
 302       throw new SQLiteException("invalid field index: "~to!string(idx));
 303     }
 304     immutable int rc = sqlite3_bind_text(data.st, idx, cast(const(char)*)text.ptr, cast(int)text.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 305     sqcheck(rc);
 306     return this;
 307   }
 308
 309   ref DBStatement bindText (const(char)[] name, const(void)[] text, bool transient=true) {
 310     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 311     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 312     char[257] fldname = 0;
 313     if (name.length > 255) throw new SQLiteException("field name too long");
 314     if (name[0] == ':' || name[0] == '?' || name[0] == '@') {
 315       fldname[0..name.length] = name[];
 316     } else {
 317       fldname[0] = ':';
 318       fldname[1..name.length+1] = name[];
 319     }
 320     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 321     if (idx < 1) throw new SQLiteException("invalid field name: '"~name.idup~"'");
 322     return bindText(cast(uint)idx, text, transient);
 323   }
 324
 325
 326   ref DBStatement bindBlob (uint idx, const(void)[] blob, bool transient=true) {
 327     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 328     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 329     if (idx < 1) {
 330       import std.conv : to;
 331       throw new SQLiteException("invalid field index: "~to!string(idx));
 332     }
 333     immutable int rc = sqlite3_bind_blob(data.st, idx, blob.ptr, cast(int)blob.length, (transient ? SQLITE_TRANSIENT : SQLITE_STATIC));
 334     sqcheck(rc);
 335     return this;
 336   }
 337
 338   ref DBStatement bindBlob (const(char)[] name, const(void)[] blob, bool transient=true) {
 339     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 340     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 341     char[257] fldname = 0;
 342     if (name.length > 255) throw new SQLiteException("field name too long");
 343     if (name[0] == ':' || name[0] == '?' || name[0] == '@') {
 344       fldname[0..name.length] = name[];
 345     } else {
 346       fldname[0] = ':';
 347       fldname[1..name.length+1] = name[];
 348     }
 349     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 350     if (idx < 1) throw new SQLiteException("invalid field name: '"~name.idup~"'");
 351     return bindBlob(cast(uint)idx, blob, transient);
 352   }
 353
 354
 355   ref DBStatement bindNull (uint idx) {
 356     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 357     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 358     if (idx < 1) {
 359       import std.conv : to;
 360       throw new SQLiteException("invalid field index: "~to!string(idx));
 361     }
 362     immutable int rc = sqlite3_bind_null(data.st, idx);
 363     sqcheck(rc);
 364     return this;
 365   }
 366
 367   ref DBStatement bindNull (const(char)[] name) {
 368     if (!valid) throw new SQLiteException("cannot bind in invalid statement");
 369     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 370     char[257] fldname = 0;
 371     if (name.length > 255) throw new SQLiteException("field name too long");
 372     if (name[0] == ':' || name[0] == '?' || name[0] == '@') {
 373       fldname[0..name.length] = name[];
 374     } else {
 375       fldname[0] = ':';
 376       fldname[1..name.length+1] = name[];
 377     }
 378     immutable idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 379     if (idx < 1) throw new SQLiteException("invalid field name: '"~name.idup~"'");
 380     return bindNull(cast(uint)idx);
 381   }
 382
 383 private:
 384   struct DBRow {
 385     private this (DBStatement.Data* adata) {
 386       data____ = adata;
 387       DBStatement.incref(data____);
 388       ++data____.rowcount;
 389     }
 390
 391     this (this) { DBStatement.incref(data____); ++data____.rowcount; }
 392
 393     ~this () {
 394       DBStatement.decrowref(data____);
 395       DBStatement.decref(data____);
 396     }
 397
 398     int fieldIndex____ (const(char)[] name) {
 399       if (name.length > 0) {
 400         foreach (immutable int idx; 0..sqlite3_data_count(data____.st)) {
 401           import core.stdc.string : memcmp, strlen;
 402           auto n = sqlite3_column_name(data____.st, idx);
 403           if (n !is null) {
 404             auto len = strlen(n);
 405             if (len == name.length && memcmp(n, name.ptr, len) == 0) return idx;
 406           }
 407         }
 408       }
 409       throw new SQLiteException("invalid field name: '"~name.idup~"'");
 410     }
 411
 412     T to(T) (uint idx) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T || is(T : DBFieldIndex) || is(T : DBFieldType)) {
 413       if (data____.stepIndex == 0) throw new SQLiteException("can't get row field of completed statement");
 414       if (idx >= sqlite3_data_count(data____.st)) throw new SQLiteException("invalid result index");
 415       static if (is(T : DBFieldIndex)) {
 416         return DBFieldIndex(idx);
 417       } else static if (is(T : DBFieldType)) {
 418         switch (sqlite3_column_type(data____.st, idx)) {
 419           case SQLITE_INTEGER: return DBFieldType(DBFieldType.Integer);
 420           case SQLITE_FLOAT: return DBFieldType(DBFieldType.Float);
 421           case SQLITE3_TEXT: return DBFieldType(DBFieldType.Text);
 422           case SQLITE_BLOB: return DBFieldType(DBFieldType.Blob);
 423           case SQLITE_NULL: return DBFieldType(DBFieldType.Null);
 424           default: break;
 425         }
 426         return DBFieldType(DBFieldType.Unknown);
 427       } else static if (isIntegral!T) {
 428         auto res = sqlite3_column_int64(data____.st, idx);
 429         if (res < T.min || res > T.max) throw new SQLiteException("integral overflow");
 430         return cast(T)res;
 431       } else {
 432         auto len = sqlite3_column_bytes(data____.st, idx);
 433         if (len < 0) throw new SQLiteException("invalid result");
 434         const(char)* res =
 435           sqlite3_column_type(data____.st, idx) == SQLITE_BLOB ?
 436             cast(const(char)*)sqlite3_column_text(data____.st, idx) :
 437             cast(const(char)*)sqlite3_column_blob(data____.st, idx);
 438         static if (is(ElementEncodingType!T == const(char))) {
 439           return res[0..len];
 440         } else static if (is(ElementEncodingType!T == immutable(char))) {
 441           return res[0..len].idup;
 442         } else {
 443           return res[0..len].dup;
 444         }
 445       }
 446     }
 447     T to(T) (const(char)[] name) { return this.to!T(fieldIndex____(name)); }
 448
 449     template opIndex() {
 450       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); }
 451       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); }
 452       alias opIndex = opIndexImpl;
 453     }
 454
 455     template opDispatch(string name) {
 456       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); }
 457       alias opDispatch = opDispatchImpl;
 458     }
 459
 460     auto index_ () pure const nothrow @nogc { return (data____.stepIndex > 0 ? data____.stepIndex-1 : 0); }
 461
 462     private DBStatement.Data* data____;
 463   }
 464
 465   struct DBRowRange {
 466     private this (ref DBStatement astat) {
 467       data = astat.data;
 468       DBStatement.incref(data);
 469       ++data.rowcount;
 470       assert(data.stepIndex == 0);
 471       data.stepIndex = 1;
 472       popFront();
 473     }
 474
 475     this (this) { DBStatement.incref(data); ++data.rowcount; }
 476
 477     ~this () {
 478       DBStatement.decrowref(data);
 479       DBStatement.decref(data);
 480     }
 481
 482     @property bool empty () const pure nothrow @nogc { return (data.stepIndex == 0); }
 483
 484     @property auto front () {
 485       if (data.stepIndex == 0) throw new SQLiteException("can't get front element of completed statement");
 486       return DBRow(data);
 487     }
 488
 489     void popFront () {
 490       if (data.stepIndex == 0) throw new SQLiteException("can't pop element of completed statement");
 491       auto rc = sqlite3_step(data.st);
 492       if (rc == SQLITE_DONE) {
 493         data.stepIndex = 0;
 494         return;
 495       }
 496       if (rc != SQLITE_ROW) {
 497         data.stepIndex = 0;
 498         sqcheck(rc);
 499       }
 500       ++data.stepIndex;
 501     }
 502
 503     auto index_ () pure const nothrow @nogc { return (data.stepIndex > 0 ? data.stepIndex-1 : 0); }
 504
 505     private DBStatement.Data* data;
 506   }
 507
 508   static void incref (Data* data) {
 509     assert(data !is null);
 510     ++data.refcount;
 511   }
 512
 513   static void decref (Data* data) {
 514     assert(data !is null);
 515     --data.refcount;
 516     if (data.refcount == 0) {
 517       import core.stdc.stdlib : free;
 518       if (data.st !is null) {
 519         sqlite3_reset(data.st);
 520         sqlite3_clear_bindings(data.st);
 521         sqlite3_finalize(data.st);
 522       }
 523       free(data);
 524     }
 525   }
 526
 527   static void decrowref (Data* data) {
 528     assert(data !is null);
 529     --data.rowcount;
 530     if (data.rowcount == 0) {
 531       data.stepIndex = 0;
 532       sqlite3_reset(data.st);
 533       sqlite3_clear_bindings(data.st);
 534     }
 535   }
 536
 537 private:
 538   static struct Data {
 539     uint refcount;
 540     uint rowcount; // number of row structs using this statement
 541     uint stepIndex;
 542     sqlite3_stmt* st;
 543   }
 544   Data* data;
 545 }