1 /** 2 * Higher-level wrapper over etc.c.sqlite3 3 * 4 * License: 5 * This Source Code Form is subject to the terms of 6 * the Mozilla Public License, v. 2.0. If a copy of 7 * the MPL was not distributed with this file, You 8 * can obtain one at http://mozilla.org/MPL/2.0/. 9 * 10 * Authors: 11 * Vladimir Panteleev <vladimir@thecybershadow.net> 12 */ 13 14 module ae.sys.sqlite3; 15 16 pragma(lib, "sqlite3"); 17 18 import etc.c.sqlite3; 19 import std.string : toStringz; 20 import std.conv : to; 21 22 final class SQLite 23 { 24 private sqlite3* db; 25 26 this(string fn, bool readOnly = false) 27 { 28 sqenforce(sqlite3_open_v2(toStringz(fn), &db, readOnly ? SQLITE_OPEN_READONLY : SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, null)); 29 } 30 31 ~this() 32 { 33 sqlite3_close(db); 34 } 35 36 auto query(string sql) 37 { 38 struct Iterator 39 { 40 alias int delegate(ref const(char)[][] args, ref const(char)[][] columns) F; 41 42 string sql; 43 sqlite3* db; 44 F dg; 45 int fres; 46 Throwable throwable = null; 47 48 int opApply(F dg) 49 { 50 this.dg = dg; 51 auto res = sqlite3_exec(db, toStringz(sql), &callback, &this, null); 52 if (res == SQLITE_ABORT) 53 { 54 if (throwable) 55 throw throwable; 56 else 57 return fres; 58 } 59 else 60 if (res != SQLITE_OK) 61 throw new SQLiteException(db, res); 62 return 0; 63 } 64 65 static /*nothrow*/ extern(C) int callback(void* ctx, int argc, char** argv, char** colv) 66 { 67 auto i = cast(Iterator*)ctx; 68 static const(char)[][] args, cols; 69 args.length = cols.length = argc; 70 foreach (n; 0..argc) 71 args[n] = to!(const(char)[])(argv[n]), 72 cols[n] = to!(const(char)[])(colv[n]); 73 try 74 return i.fres = i.dg(args, cols); 75 catch (Exception e) 76 { 77 i.throwable = e; 78 return 1; 79 } 80 } 81 } 82 83 return Iterator(sql, db); 84 } 85 86 void exec(string sql) 87 { 88 foreach (cells, columns; query(sql)) 89 break; 90 } 91 92 @property long lastInsertRowID() 93 { 94 return sqlite3_last_insert_rowid(db); 95 } 96 97 final class PreparedStatement 98 { 99 sqlite3_stmt* stmt; 100 101 void bind(int idx, int v) 102 { 103 sqlite3_bind_int(stmt, idx, v); 104 } 105 106 void bind(int idx, long v) 107 { 108 sqlite3_bind_int64(stmt, idx, v); 109 } 110 111 void bind(int idx, double v) 112 { 113 sqlite3_bind_double(stmt, idx, v); 114 } 115 116 void bind(int idx, in char[] v) 117 { 118 sqlite3_bind_text(stmt, idx, v.ptr, to!int(v.length), SQLITE_TRANSIENT); 119 } 120 121 void bind(int idx, in wchar[] v) 122 { 123 sqlite3_bind_text16(stmt, idx, v.ptr, to!int(v.length*2), SQLITE_TRANSIENT); 124 } 125 126 void bind(int idx, void* n) 127 { 128 assert(n is null); 129 sqlite3_bind_null(stmt, idx); 130 } 131 132 void bind(int idx, in void[] v) 133 { 134 sqlite3_bind_blob(stmt, idx, v.ptr, to!int(v.length), SQLITE_TRANSIENT); 135 } 136 137 void bindAll(T...)(T args) 138 { 139 foreach (int n, arg; args) 140 bind(n+1, arg); 141 } 142 143 /// Return "true" if a row is available, "false" if done. 144 bool step() 145 { 146 auto res = sqlite3_step(stmt); 147 if (res == SQLITE_DONE) 148 { 149 reset(); 150 return false; 151 } 152 else 153 if (res == SQLITE_ROW) 154 return true; 155 else 156 { 157 sqlite3_reset(stmt); 158 sqenforce(res); 159 return false; // only on SQLITE_OK, which shouldn't happen 160 } 161 } 162 163 void reset() 164 { 165 sqenforce(sqlite3_reset(stmt)); 166 } 167 168 void exec(T...)(T args) 169 { 170 static if (T.length) 171 bindAll!T(args); 172 while (step()) {} 173 } 174 175 static struct Iterator 176 { 177 PreparedStatement stmt; 178 179 @trusted int opApply(U...)(int delegate(ref U args) @system dg) 180 { 181 int res = 0; 182 while (stmt.step()) 183 { 184 scope(failure) stmt.reset(); 185 static if (U.length == 1 && is(U[0] V : V[]) && !is(U[0] : string) && !is(V == void)) 186 { 187 U[0] result; 188 result.length = stmt.columnCount(); 189 foreach (int c, ref r; result) 190 r = stmt.column!V(c); 191 res = dg(result); 192 } 193 else 194 static if (U.length == 1 && is(U[0] V : V[string])) 195 { 196 U[0] result; 197 foreach (c; 0..stmt.columnCount()) 198 result[stmt.columnName(c)] = stmt.column!V(c); 199 res = dg(result); 200 } 201 else 202 { 203 U columns; 204 stmt.columns(columns); 205 res = dg(columns); 206 } 207 if (res) 208 { 209 stmt.reset(); 210 break; 211 } 212 } 213 return res; 214 } 215 } 216 217 Iterator iterate(T...)(T args) 218 { 219 static if (T.length) 220 bindAll!T(args); 221 return Iterator(this); 222 } 223 224 T column(T)(int idx) 225 { 226 static if (is(T == string)) 227 return (cast(char*)sqlite3_column_blob(stmt, idx))[0..sqlite3_column_bytes(stmt, idx)].idup; 228 else 229 static if (is(T == void[])) 230 return (cast(void*)sqlite3_column_blob(stmt, idx))[0..sqlite3_column_bytes(stmt, idx)].dup; 231 else 232 static if (is(T == int)) 233 return sqlite3_column_int(stmt, idx); 234 else 235 static if (is(T == long)) 236 return sqlite3_column_int64(stmt, idx); 237 else 238 static if (is(T == bool)) 239 return sqlite3_column_int(stmt, idx) != 0; 240 else 241 static if (is(T == double)) 242 return sqlite3_column_double(stmt, idx); 243 else 244 static assert(0, "Can't get column with type " ~ T.stringof); 245 } 246 247 void columns(T...)(ref T args) 248 { 249 foreach (i, arg; args) 250 args[i] = column!(typeof(arg))(i); 251 } 252 253 T[] getArray(T=string)() 254 { 255 T[] result = new T[dataCount()]; 256 foreach (i, ref value; result) 257 value = column!T(i); 258 return result; 259 } 260 261 T[string] getAssoc(T=string)() 262 { 263 T[string] result; 264 foreach (i; 0..dataCount()) 265 result[columnName(i)] = column!T(i); 266 return result; 267 } 268 269 int columnCount() 270 { 271 return sqlite3_column_count(stmt); 272 } 273 274 int dataCount() 275 { 276 return sqlite3_data_count(stmt); 277 } 278 279 string columnName(int idx) 280 { 281 return to!string(sqlite3_column_name(stmt, idx)); 282 } 283 284 ~this() 285 { 286 sqlite3_finalize(stmt); 287 } 288 } 289 290 PreparedStatement prepare(string sql) 291 { 292 auto s = new PreparedStatement; 293 sqenforce(sqlite3_prepare_v2(db, toStringz(sql), -1, &s.stmt, null)); 294 return s; 295 } 296 297 private void sqenforce(int res) 298 { 299 if (res != SQLITE_OK) 300 throw new SQLiteException(db, res); 301 } 302 } 303 304 class SQLiteException : Exception 305 { 306 int code; 307 308 this(sqlite3* db, int code) 309 { 310 this.code = code; 311 super(to!string(sqlite3_errmsg(db))); 312 } 313 } 314