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