database: Add interface to handle transactions
[strongswan.git] / src / libstrongswan / plugins / sqlite / sqlite_database.c
1 /*
2 * Copyright (C) 2013 Tobias Brunner
3 * Copyright (C) 2007 Martin Willi
4 * Hochschule fuer Technik Rapperswil
5 *
6 * This program is free software; you can redistribute it and/or modify it
7 * under the terms of the GNU General Public License as published by the
8 * Free Software Foundation; either version 2 of the License, or (at your
9 * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
10 *
11 * This program is distributed in the hope that it will be useful, but
12 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
13 * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
14 * for more details.
15 */
16
17 #include "sqlite_database.h"
18
19 #include <sqlite3.h>
20 #include <unistd.h>
21 #include <library.h>
22 #include <utils/debug.h>
23 #include <threading/mutex.h>
24
25 typedef struct private_sqlite_database_t private_sqlite_database_t;
26
27 /**
28 * private data of sqlite_database
29 */
30 struct private_sqlite_database_t {
31
32 /**
33 * public functions
34 */
35 sqlite_database_t public;
36
37 /**
38 * sqlite database connection
39 */
40 sqlite3 *db;
41
42 /**
43 * mutex used to lock execute()
44 */
45 mutex_t *mutex;
46 };
47
48 /**
49 * Create and run a sqlite stmt using a sql string and args
50 */
51 static sqlite3_stmt* run(private_sqlite_database_t *this, char *sql,
52 va_list *args)
53 {
54 sqlite3_stmt *stmt = NULL;
55 int params, i, res = SQLITE_OK;
56
57 #ifdef HAVE_SQLITE3_PREPARE_V2
58 if (sqlite3_prepare_v2(this->db, sql, -1, &stmt, NULL) == SQLITE_OK)
59 #else
60 if (sqlite3_prepare(this->db, sql, -1, &stmt, NULL) == SQLITE_OK)
61 #endif
62 {
63 params = sqlite3_bind_parameter_count(stmt);
64 for (i = 1; i <= params; i++)
65 {
66 switch (va_arg(*args, db_type_t))
67 {
68 case DB_INT:
69 {
70 res = sqlite3_bind_int(stmt, i, va_arg(*args, int));
71 break;
72 }
73 case DB_UINT:
74 {
75 res = sqlite3_bind_int64(stmt, i, va_arg(*args, u_int));
76 break;
77 }
78 case DB_TEXT:
79 {
80 const char *text = va_arg(*args, const char*);
81 res = sqlite3_bind_text(stmt, i, text, -1, SQLITE_STATIC);
82 break;
83 }
84 case DB_BLOB:
85 {
86 chunk_t c = va_arg(*args, chunk_t);
87 res = sqlite3_bind_blob(stmt, i, c.ptr, c.len, SQLITE_STATIC);
88 break;
89 }
90 case DB_DOUBLE:
91 {
92 res = sqlite3_bind_double(stmt, i, va_arg(*args, double));
93 break;
94 }
95 case DB_NULL:
96 {
97 res = sqlite3_bind_null(stmt, i);
98 break;
99 }
100 default:
101 {
102 res = SQLITE_MISUSE;
103 break;
104 }
105 }
106 if (res != SQLITE_OK)
107 {
108 break;
109 }
110 }
111 }
112 else
113 {
114 DBG1(DBG_LIB, "preparing sqlite statement failed: %s",
115 sqlite3_errmsg(this->db));
116 }
117 if (res != SQLITE_OK)
118 {
119 DBG1(DBG_LIB, "binding sqlite statement failed: %s",
120 sqlite3_errmsg(this->db));
121 sqlite3_finalize(stmt);
122 return NULL;
123 }
124 return stmt;
125 }
126
127 typedef struct {
128 /** implements enumerator_t */
129 enumerator_t public;
130 /** associated sqlite statement */
131 sqlite3_stmt *stmt;
132 /** number of result columns */
133 int count;
134 /** column types */
135 db_type_t *columns;
136 /** back reference to parent */
137 private_sqlite_database_t *database;
138 } sqlite_enumerator_t;
139
140 /**
141 * destroy a sqlite enumerator
142 */
143 static void sqlite_enumerator_destroy(sqlite_enumerator_t *this)
144 {
145 sqlite3_finalize(this->stmt);
146 #if SQLITE_VERSION_NUMBER < 3005000
147 this->database->mutex->unlock(this->database->mutex);
148 #endif
149 free(this->columns);
150 free(this);
151 }
152
153 /**
154 * Implementation of database.query().enumerate
155 */
156 static bool sqlite_enumerator_enumerate(sqlite_enumerator_t *this, ...)
157 {
158 int i;
159 va_list args;
160
161 switch (sqlite3_step(this->stmt))
162 {
163 case SQLITE_ROW:
164 break;
165 default:
166 DBG1(DBG_LIB, "stepping sqlite statement failed: %s",
167 sqlite3_errmsg(this->database->db));
168 /* fall */
169 case SQLITE_DONE:
170 return FALSE;
171 }
172 va_start(args, this);
173 for (i = 0; i < this->count; i++)
174 {
175 switch (this->columns[i])
176 {
177 case DB_INT:
178 {
179 int *value = va_arg(args, int*);
180 *value = sqlite3_column_int(this->stmt, i);
181 break;
182 }
183 case DB_UINT:
184 {
185 u_int *value = va_arg(args, u_int*);
186 *value = (u_int)sqlite3_column_int64(this->stmt, i);
187 break;
188 }
189 case DB_TEXT:
190 {
191 const unsigned char **value = va_arg(args, const unsigned char**);
192 *value = sqlite3_column_text(this->stmt, i);
193 break;
194 }
195 case DB_BLOB:
196 {
197 chunk_t *chunk = va_arg(args, chunk_t*);
198 chunk->len = sqlite3_column_bytes(this->stmt, i);
199 chunk->ptr = (u_char*)sqlite3_column_blob(this->stmt, i);
200 break;
201 }
202 case DB_DOUBLE:
203 {
204 double *value = va_arg(args, double*);
205 *value = sqlite3_column_double(this->stmt, i);
206 break;
207 }
208 default:
209 DBG1(DBG_LIB, "invalid result type supplied");
210 va_end(args);
211 return FALSE;
212 }
213 }
214 va_end(args);
215 return TRUE;
216 }
217
218 METHOD(database_t, query, enumerator_t*,
219 private_sqlite_database_t *this, char *sql, ...)
220 {
221 sqlite3_stmt *stmt;
222 va_list args;
223 sqlite_enumerator_t *enumerator = NULL;
224 int i;
225
226 #if SQLITE_VERSION_NUMBER < 3005000
227 /* sqlite connections prior to 3.5 may be used by a single thread only, */
228 this->mutex->lock(this->mutex);
229 #endif
230
231 va_start(args, sql);
232 stmt = run(this, sql, &args);
233 if (stmt)
234 {
235 enumerator = malloc_thing(sqlite_enumerator_t);
236 enumerator->public.enumerate = (void*)sqlite_enumerator_enumerate;
237 enumerator->public.destroy = (void*)sqlite_enumerator_destroy;
238 enumerator->stmt = stmt;
239 enumerator->count = sqlite3_column_count(stmt);
240 enumerator->columns = malloc(sizeof(db_type_t) * enumerator->count);
241 enumerator->database = this;
242 for (i = 0; i < enumerator->count; i++)
243 {
244 enumerator->columns[i] = va_arg(args, db_type_t);
245 }
246 }
247 va_end(args);
248 return (enumerator_t*)enumerator;
249 }
250
251 METHOD(database_t, execute, int,
252 private_sqlite_database_t *this, int *rowid, char *sql, ...)
253 {
254 sqlite3_stmt *stmt;
255 int affected = -1;
256 va_list args;
257
258 /* we need a lock to get our rowid/changes correctly */
259 this->mutex->lock(this->mutex);
260 va_start(args, sql);
261 stmt = run(this, sql, &args);
262 va_end(args);
263 if (stmt)
264 {
265 if (sqlite3_step(stmt) == SQLITE_DONE)
266 {
267 if (rowid)
268 {
269 *rowid = sqlite3_last_insert_rowid(this->db);
270 }
271 affected = sqlite3_changes(this->db);
272 }
273 else
274 {
275 DBG1(DBG_LIB, "sqlite execute failed: %s",
276 sqlite3_errmsg(this->db));
277 }
278 sqlite3_finalize(stmt);
279 }
280 this->mutex->unlock(this->mutex);
281 return affected;
282 }
283
284 METHOD(database_t, transaction, bool,
285 private_sqlite_database_t *this)
286 {
287 return FALSE;
288 }
289
290 METHOD(database_t, commit, bool,
291 private_sqlite_database_t *this)
292 {
293 return FALSE;
294 }
295
296 METHOD(database_t, rollback, bool,
297 private_sqlite_database_t *this)
298 {
299 return FALSE;
300 }
301
302 METHOD(database_t, get_driver, db_driver_t,
303 private_sqlite_database_t *this)
304 {
305 return DB_SQLITE;
306 }
307
308 /**
309 * Busy handler implementation
310 */
311 static int busy_handler(private_sqlite_database_t *this, int count)
312 {
313 /* add a backoff time, quadratically increasing with every try */
314 usleep(count * count * 1000);
315 /* always retry */
316 return 1;
317 }
318
319 METHOD(database_t, destroy, void,
320 private_sqlite_database_t *this)
321 {
322 if (sqlite3_close(this->db) == SQLITE_BUSY)
323 {
324 DBG1(DBG_LIB, "sqlite close failed because database is busy");
325 }
326 this->mutex->destroy(this->mutex);
327 free(this);
328 }
329
330 /*
331 * see header file
332 */
333 sqlite_database_t *sqlite_database_create(char *uri)
334 {
335 char *file;
336 private_sqlite_database_t *this;
337
338 /**
339 * parse sqlite:///path/to/file.db uri
340 */
341 if (!strpfx(uri, "sqlite://"))
342 {
343 return NULL;
344 }
345 file = uri + 9;
346
347 INIT(this,
348 .public = {
349 .db = {
350 .query = _query,
351 .execute = _execute,
352 .transaction = _transaction,
353 .commit = _commit,
354 .rollback = _rollback,
355 .get_driver = _get_driver,
356 .destroy = _destroy,
357 },
358 },
359 .mutex = mutex_create(MUTEX_TYPE_RECURSIVE),
360 );
361
362 if (sqlite3_open(file, &this->db) != SQLITE_OK)
363 {
364 DBG1(DBG_LIB, "opening SQLite database '%s' failed: %s",
365 file, sqlite3_errmsg(this->db));
366 _destroy(this);
367 return NULL;
368 }
369
370 sqlite3_busy_handler(this->db, (void*)busy_handler, this);
371
372 return &this->public;
373 }
374