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 	final class PreparedStatement
100 	{
101 		sqlite3_stmt* stmt;
102 
103 		void bind(int idx, int v)
104 		{
105 			sqlite3_bind_int(stmt, idx, v);
106 		}
107 
108 		void bind(int idx, long v)
109 		{
110 			sqlite3_bind_int64(stmt, idx, v);
111 		}
112 
113 		void bind(int idx, double v)
114 		{
115 			sqlite3_bind_double(stmt, idx, v);
116 		}
117 
118 		void bind(int idx, in char[] v)
119 		{
120 			sqlite3_bind_text(stmt, idx, v.ptr, to!int(v.length), SQLITE_TRANSIENT);
121 		}
122 
123 		void bind(int idx, in wchar[] v)
124 		{
125 			sqlite3_bind_text16(stmt, idx, v.ptr, to!int(v.length*2), SQLITE_TRANSIENT);
126 		}
127 
128 		void bind(int idx, void* n)
129 		{
130 			assert(n is null);
131 			sqlite3_bind_null(stmt, idx);
132 		}
133 
134 		void bind(int idx, in void[] v)
135 		{
136 			sqlite3_bind_blob(stmt, idx, v.ptr, to!int(v.length), SQLITE_TRANSIENT);
137 		}
138 
139 		void bindAll(T...)(T args)
140 		{
141 			foreach (int n, arg; args)
142 				bind(n+1, arg);
143 		}
144 
145 		/// Return "true" if a row is available, "false" if done.
146 		bool step()
147 		{
148 			auto res = sqlite3_step(stmt);
149 			if (res == SQLITE_DONE)
150 			{
151 				reset();
152 				return false;
153 			}
154 			else
155 			if (res == SQLITE_ROW)
156 				return true;
157 			else
158 			{
159 				scope(exit) sqlite3_reset(stmt);
160 				sqenforce(res);
161 				return false; // only on SQLITE_OK, which shouldn't happen
162 			}
163 		}
164 
165 		void reset()
166 		{
167 			sqenforce(sqlite3_reset(stmt));
168 		}
169 
170 		void exec(T...)(T args)
171 		{
172 			static if (T.length)
173 				bindAll!T(args);
174 			while (step()) {}
175 		}
176 
177 		static struct Iterator
178 		{
179 			PreparedStatement stmt;
180 
181 			@trusted int opApply(U...)(int delegate(ref U args) @system dg)
182 			{
183 				int res = 0;
184 				while (stmt.step())
185 				{
186 					scope(failure) stmt.reset();
187 					static if (U.length == 1 && is(U[0] V : V[]) &&
188 						!is(U[0] : string) && !is(V == void) && !is(V == ubyte))
189 					{
190 						U[0] result;
191 						result.length = stmt.columnCount();
192 						foreach (int c, ref r; result)
193 							r = stmt.column!V(c);
194 						res = dg(result);
195 					}
196 					else
197 					static if (U.length == 1 && is(U[0] V : V[string]))
198 					{
199 						U[0] result;
200 						foreach (c; 0..stmt.columnCount())
201 							result[stmt.columnName(c)] = stmt.column!V(c);
202 						res = dg(result);
203 					}
204 					else
205 					{
206 						U columns;
207 						stmt.columns(columns);
208 						res = dg(columns);
209 					}
210 					if (res)
211 					{
212 						stmt.reset();
213 						break;
214 					}
215 				}
216 				return res;
217 			}
218 		}
219 
220 		Iterator iterate(T...)(T args)
221 		{
222 			static if (T.length)
223 				bindAll!T(args);
224 			return Iterator(this);
225 		}
226 
227 		T column(T)(int idx)
228 		{
229 			static if (is(T == string))
230 				return (cast(char*)sqlite3_column_blob(stmt, idx))[0..sqlite3_column_bytes(stmt, idx)].idup;
231 			else
232 			static if (is(T V : V[]) && (is(V == void) || is(V == ubyte)))
233 			{
234 				auto arr = (cast(V*)sqlite3_column_blob(stmt, idx))[0..sqlite3_column_bytes(stmt, idx)];
235 				static if (isStaticArray!T)
236 				{
237 					enforce(arr.length == T.length, "Wrong size for static array column");
238 					return arr[0..T.length];
239 				}
240 				else
241 					return arr.dup;
242 			}
243 			else
244 			static if (is(T == int))
245 				return sqlite3_column_int(stmt, idx);
246 			else
247 			static if (is(T == long))
248 				return sqlite3_column_int64(stmt, idx);
249 			else
250 			static if (is(T == bool))
251 				return sqlite3_column_int(stmt, idx) != 0;
252 			else
253 			static if (is(T == double))
254 				return sqlite3_column_double(stmt, idx);
255 			else
256 				static assert(0, "Can't get column with type " ~ T.stringof);
257 		}
258 
259 		unittest
260 		{
261 			PreparedStatement s;
262 			if (false)
263 			{
264 				s.column!(void[])(0);
265 				s.column!(ubyte[])(0);
266 				s.column!(void[16])(0);
267 				s.column!(ubyte[16])(0);
268 			}
269 		}
270 
271 		void columns(T...)(ref T args)
272 		{
273 			foreach (i, arg; args)
274 				args[i] = column!(typeof(arg))(i);
275 		}
276 
277 		T[] getArray(T=string)()
278 		{
279 			T[] result = new T[dataCount()];
280 			foreach (i, ref value; result)
281 				value = column!T(i);
282 			return result;
283 		}
284 
285 		T[string] getAssoc(T=string)()
286 		{
287 			T[string] result;
288 			foreach (i; 0..dataCount())
289 				result[columnName(i)] = column!T(i);
290 			return result;
291 		}
292 
293 		int columnCount()
294 		{
295 			return sqlite3_column_count(stmt);
296 		}
297 
298 		int dataCount()
299 		{
300 			return sqlite3_data_count(stmt);
301 		}
302 
303 		string columnName(int idx)
304 		{
305 			return to!string(sqlite3_column_name(stmt, idx));
306 		}
307 
308 		~this()
309 		{
310 			sqlite3_finalize(stmt);
311 		}
312 	}
313 
314 	PreparedStatement prepare(string sql)
315 	{
316 		auto s = new PreparedStatement;
317 		sqenforce(sqlite3_prepare_v2(db, toStringz(sql), -1, &s.stmt, null));
318 		return s;
319 	}
320 
321 	private void sqenforce(int res)
322 	{
323 		if (res != SQLITE_OK)
324 			throw new SQLiteException(db, res);
325 	}
326 }
327 
328 class SQLiteException : Exception
329 {
330 	int code;
331 
332 	this(sqlite3* db, int code)
333 	{
334 		this.code = code;
335 		super(to!string(sqlite3_errmsg(db)));
336 	}
337 }
338