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