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, either version 3 of the License, or
   8  * (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  *
  15  * You should have received a copy of the GNU General Public License
  16  * along with this program. If not, see <http://www.gnu.org/licenses/>.
  17  */
  18 // sqlite3 helpers
  19 module iv.sq3 /*is aliced*/;
  20 pragma(lib, "sqlite3");
  21
  22 import iv.alice;
  23
  24 import etc.c.sqlite3;
  25 import std.traits;
  26 import std.range.primitives;
  27
  28
  29 ////////////////////////////////////////////////////////////////////////////////
  30 mixin(NewExceptionClass!("SQLiteException", "Exception"));
  31
  32 class SQLiteErr : SQLiteException {
  33   int code;
  34
  35   this (int rc, string file=__FILE__, usize line=__LINE__, Throwable next=null) @trusted nothrow {
  36     //import core.stdc.stdio : stderr, fprintf;
  37     //fprintf(stderr, "SQLITE ERROR: %s\n", sqlite3_errstr(rc));
  38     import std.exception : assumeUnique;
  39     import std.string : fromStringz;
  40     code = rc;
  41     super(sqlite3_errstr(rc).fromStringz.assumeUnique, file, line, next);
  42   }
  43 }
  44
  45
  46 private void sqcheck (int rc, string file=__FILE__, usize line=__LINE__) {
  47   //pragma(inline, true);
  48   if (rc != SQLITE_OK) throw new SQLiteErr(rc, file, line);
  49 }
  50
  51
  52 ////////////////////////////////////////////////////////////////////////////////
  53 shared static this () {
  54   if (sqlite3_initialize() != SQLITE_OK) throw new Error("can't initialize SQLite");
  55 }
  56
  57 shared static ~this () {
  58   sqlite3_shutdown();
  59 }
  60
  61
  62 ////////////////////////////////////////////////////////////////////////////////
  63 struct Database {
  64 private:
  65   sqlite3* db;
  66
  67 public:
  68   @disable this (this); // no copy!
  69
  70   this (const(char)[] name, const(char)[] schema=null) { open(name, schema); }
  71   ~this () { close(); }
  72
  73   void open (const(char)[] name, const(char)[] schema=null) {
  74     close();
  75     import std.internal.cstring;
  76     sqcheck(sqlite3_open_v2(name.tempCString, &db, (schema !is null ? SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE : SQLITE_OPEN_READONLY), null));
  77     scope(failure) { sqlite3_close_v2(db); db = null; }
  78     if (schema.length) execute(schema);
  79   }
  80
  81   @property bool isOpen () const pure nothrow @safe @nogc { return (db !is null); }
  82
  83   void close () {
  84     if (db !is null) sqlite3_close_v2(db);
  85     db = null;
  86   }
  87
  88   ulong lastRowId () { return (db ? sqlite3_last_insert_rowid(db) : 0); }
  89
  90   void execute (const(char)[] ops) {
  91     if (!isOpen) throw new Exception("database is not opened");
  92     foreach (/*auto*/ opstr; sqlSplit(ops)) {
  93       import std.internal.cstring;
  94       char* errmsg;
  95       auto rc = sqlite3_exec(db, opstr.tempCString, null, null, &errmsg);
  96       if (rc != SQLITE_OK) {
  97         import core.stdc.stdio : stderr, fprintf;
  98         fprintf(stderr, "SQLITE ERROR: %s\n", errmsg);
  99         sqlite3_free(errmsg);
 100         sqcheck(rc);
 101       }
 102     }
 103   }
 104
 105   DBStatement statement (const(char)[] stmtstr) {
 106     if (!isOpen) throw new Exception("database is not opened");
 107     return DBStatement(db, stmtstr);
 108   }
 109
 110   static auto sqlSplit(T) (T text) if (isNarrowString!T) {
 111     static struct StatRange {
 112       T text;
 113       T front;
 114
 115       this (T atext) {
 116         text = atext;
 117         popFront();
 118       }
 119
 120       @property bool empty () const pure nothrow @safe @nogc { return (front is null); }
 121
 122       void popFront () {
 123         front = null;
 124         while (text.length) {
 125           // spaces
 126           if (text[0] <= ' ') { text = text[1..$]; continue; }
 127           if (text[0] == ';') { text = text[1..$]; continue; }
 128           // leading comments
 129           if (text[0] == '/' && text.length > 1 && text[1] == '*') {
 130             text = text[2..$];
 131             while (text.length >= 2) {
 132               if (text[0] == '*' && text[1] == '/') break;
 133               text = text[1..$];
 134             }
 135             text = (text.length >= 2 ? text[2..$] : null);
 136             continue;
 137           }
 138           usize pos = 0;
 139           while (pos < text.length) {
 140             // eos?
 141             if (text[pos] == ';') {
 142               front = text[0..pos];
 143               text = text[pos+1..$];
 144               return;
 145             }
 146             // string
 147             if (text[pos] == '\'' || text[pos] == '"') {
 148               char q = text[pos++];
 149               bool wasQ = false;
 150               while (pos < text.length) {
 151                 char ch = text[pos++];
 152                 if (ch == q) {
 153                   // check for double quote
 154                   if (text.length-pos == 0 || text[pos] != q) {
 155                     wasQ = true;
 156                     break;
 157                   }
 158                   ++pos;
 159                   continue;
 160                 }
 161               }
 162               if (!wasQ) throw new Exception("interminated string");
 163             }
 164             // comment
 165             if (text[pos] == '/' && text.length-pos > 1 && text[pos+1] == '*') {
 166               pos += 2;
 167               while (text.length-pos >= 2) {
 168                 if (text[pos] == '*' && text[pos+1] == '/') break;
 169                 ++pos;
 170               }
 171               if (text.length-pos < 2) throw new Exception("unterminated comment");
 172               continue;
 173             }
 174             // other
 175             ++pos;
 176           }
 177           front = (text.length ? text : null);
 178           text = null;
 179           return;
 180         }
 181       }
 182     }
 183     return StatRange(text);
 184   }
 185 }
 186
 187
 188 ////////////////////////////////////////////////////////////////////////////////
 189 struct DBStatement {
 190 public:
 191   this (this) { this.incref(data); }
 192   ~this () { this.decref(data); }
 193
 194   private this (sqlite3* db, const(char)[] stmtstr) {
 195     if (db is null) throw new SQLiteException("database is not opened");
 196     if (stmtstr.length > int.max) throw new SQLiteException("statement too big");
 197     import core.stdc.stdlib : malloc;
 198     data = cast(Data*)malloc(Data.sizeof);
 199     if (data is null) {
 200       import core.exception : onOutOfMemoryErrorNoGC;
 201       onOutOfMemoryErrorNoGC();
 202     }
 203     data.refcount = 1;
 204     data.rowcount = 0;
 205     data.stepIndex = 0;
 206     data.st = null;
 207     scope(failure) DBStatement.decref(data);
 208     const(char)* e;
 209     sqcheck(sqlite3_prepare_v2(db, stmtstr.ptr, cast(int)stmtstr.length, &data.st, &e));
 210   }
 211
 212   @property auto range () {
 213     //if (st is null) throw new SQLiteException("statement is not prepared");
 214     if (data.stepIndex != 0) throw new SQLiteException("can't get range from busy statement");
 215     return DBRowRange(this);
 216   }
 217
 218   void reset () {
 219     //if (data.stepIndex != 0) throw new SQLiteException("can't reset busy statement");
 220     data.stepIndex = 0;
 221     sqlite3_reset(data.st);
 222     sqlite3_clear_bindings(data.st);
 223   }
 224
 225   void doAll () {
 226     if (data.stepIndex != 0) throw new SQLiteException("can't doAll on busy statement");
 227     scope(exit) reset();
 228     for (;;) {
 229       auto rc = sqlite3_step(data.st);
 230       if (rc == SQLITE_DONE) break;
 231       if (rc != SQLITE_ROW) sqcheck(rc);
 232     }
 233   }
 234
 235   ref DBStatement bind(T) (usize idx, T value) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 236     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 237     if (idx < 1 || idx > sqlite3_bind_parameter_count(data.st)) {
 238       import std.conv : to;
 239       throw new SQLiteException("invalid field index: "~to!string(idx));
 240     }
 241     int rc;
 242     static if (isNarrowString!T) {
 243       if (value.length > int.max) throw new SQLiteException("value too big");
 244       static if (is(ElementEncodingType!T == immutable(char))) {
 245         rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, /*SQLITE_STATIC*/SQLITE_TRANSIENT);
 246       } else {
 247         rc = sqlite3_bind_text(data.st, idx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
 248       }
 249     } else static if (isIntegral!T) {
 250       static if (isSigned!T) {
 251         rc = sqlite3_bind_int64(data.st, idx, cast(long)value);
 252       } else {
 253         rc = sqlite3_bind_int64(data.st, idx, cast(ulong)value);
 254       }
 255     } else {
 256       static assert(0, "WTF?!");
 257     }
 258     sqcheck(rc);
 259     return this;
 260   }
 261
 262   ref DBStatement bind(T) (const(char)[] name, T value) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 263     if (data.stepIndex != 0) throw new SQLiteException("can't bind on busy statement");
 264     char[257] fldname = 0;
 265     if (name.length > 255) throw new SQLiteException("field name too long");
 266     if (name[0] == ':') {
 267       fldname[0..name.length] = name[];
 268     } else {
 269       fldname[0] = ':';
 270       fldname[1..name.length+1] = name[];
 271     }
 272     auto idx = sqlite3_bind_parameter_index(data.st, fldname.ptr);
 273     if (idx < 1) throw new SQLiteException("invalid field name: '"~name.idup~"'");
 274     return bind!T(idx, value);
 275   }
 276
 277 private:
 278   struct DBRow {
 279     private this (DBStatement.Data* adata) {
 280       data____ = adata;
 281       DBStatement.incref(data____);
 282       ++data____.rowcount;
 283     }
 284
 285     this (this) { DBStatement.incref(data____); ++data____.rowcount; }
 286
 287     ~this () {
 288       DBStatement.decrowref(data____);
 289       DBStatement.decref(data____);
 290     }
 291
 292     int fieldIndex____ (const(char)[] name) {
 293       if (name.length > 0) {
 294         foreach (immutable int idx; 0..sqlite3_data_count(data____.st)) {
 295           import core.stdc.string : memcmp, strlen;
 296           auto n = sqlite3_column_name(data____.st, idx);
 297           if (n !is null) {
 298             auto len = strlen(n);
 299             if (len == name.length && memcmp(n, name.ptr, len) == 0) return idx;
 300           }
 301         }
 302       }
 303       throw new SQLiteException("invalid field name: '"~name.idup~"'");
 304     }
 305
 306     T to(T) (usize idx) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) {
 307       if (data____.stepIndex == 0) throw new SQLiteException("can't get row field of completed statement");
 308       if (idx >= sqlite3_data_count(data____.st)) throw new SQLiteException("invalid result index");
 309       static if (isIntegral!T) {
 310         auto res = sqlite3_column_int64(data____.st, idx);
 311         if (res < T.min || res > T.max) throw new SQLiteException("integral overflow");
 312         return cast(T)res;
 313       } else {
 314         auto res = sqlite3_column_text(data____.st, idx);
 315         auto len = sqlite3_column_bytes(data____.st, idx);
 316         if (len < 0) throw new SQLiteException("invalid result");
 317         static if (is(ElementEncodingType!T == const(char))) {
 318           return res[0..len];
 319         } else static if (is(ElementEncodingType!T == immutable(char))) {
 320           return res[0..len].idup;
 321         } else {
 322           return res[0..len].dup;
 323         }
 324       }
 325     }
 326     T to(T) (const(char)[] name) { return this.to!T(fieldIndex____(name)); }
 327
 328     template opIndex() {
 329       T opIndexImpl(T) (usize idx) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) { return this.to!T(idx); }
 330       T opIndexImpl(T) (const(char)[] name) if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) { return this.to!T(name); }
 331       alias opIndex = opIndexImpl;
 332     }
 333
 334     template opDispatch(string name) {
 335       T opDispatchImpl(T=const(char)[]) () if ((isNarrowString!T && is(ElementEncodingType!T : char)) || isIntegral!T) { return this.to!T(name); }
 336       alias opDispatch = opDispatchImpl;
 337     }
 338
 339     auto index_ () pure const nothrow @nogc { return (data____.stepIndex > 0 ? data____.stepIndex-1 : 0); }
 340
 341     private DBStatement.Data* data____;
 342   }
 343
 344   struct DBRowRange {
 345     private this (ref DBStatement astat) {
 346       data = astat.data;
 347       DBStatement.incref(data);
 348       ++data.rowcount;
 349       assert(data.stepIndex == 0);
 350       data.stepIndex = 1;
 351       popFront();
 352     }
 353
 354     this (this) { DBStatement.incref(data); ++data.rowcount; }
 355
 356     ~this () {
 357       DBStatement.decrowref(data);
 358       DBStatement.decref(data);
 359     }
 360
 361     @property bool empty () const pure nothrow @nogc { return (data.stepIndex == 0); }
 362
 363     @property auto front () {
 364       if (data.stepIndex == 0) throw new SQLiteException("can't get front element of completed statement");
 365       return DBRow(data);
 366     }
 367
 368     void popFront () {
 369       if (data.stepIndex == 0) throw new SQLiteException("can't pop element of completed statement");
 370       auto rc = sqlite3_step(data.st);
 371       if (rc == SQLITE_DONE) {
 372         data.stepIndex = 0;
 373         return;
 374       }
 375       if (rc != SQLITE_ROW) {
 376         data.stepIndex = 0;
 377         sqcheck(rc);
 378       }
 379       ++data.stepIndex;
 380     }
 381
 382     auto index_ () pure const nothrow @nogc { return (data.stepIndex > 0 ? data.stepIndex-1 : 0); }
 383
 384     private DBStatement.Data* data;
 385   }
 386
 387   static void incref (Data* data) {
 388     assert(data !is null);
 389     ++data.refcount;
 390   }
 391
 392   static void decref (Data* data) {
 393     assert(data !is null);
 394     --data.refcount;
 395     if (data.refcount == 0) {
 396       import core.stdc.stdlib : free;
 397       if (data.st !is null) {
 398         sqlite3_reset(data.st);
 399         sqlite3_clear_bindings(data.st);
 400         sqlite3_finalize(data.st);
 401       }
 402       free(data);
 403     }
 404   }
 405
 406   static void decrowref (Data* data) {
 407     assert(data !is null);
 408     --data.rowcount;
 409     if (data.rowcount == 0) {
 410       data.stepIndex = 0;
 411       sqlite3_reset(data.st);
 412       sqlite3_clear_bindings(data.st);
 413     }
 414   }
 415
 416 private:
 417   static struct Data {
 418     uint refcount;
 419     uint rowcount; // number of row structs using this statement
 420     uint stepIndex;
 421     sqlite3_stmt* st;
 422   }
 423   Data* data;
 424 }