1 /* sqldb.c -- implementation of sqlite abstraction layer
2 *
3 * Copyright (c) 1994-2012 Carnegie Mellon University. All rights reserved.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions
7 * are met:
8 *
9 * 1. Redistributions of source code must retain the above copyright
10 * notice, this list of conditions and the following disclaimer.
11 *
12 * 2. Redistributions in binary form must reproduce the above copyright
13 * notice, this list of conditions and the following disclaimer in
14 * the documentation and/or other materials provided with the
15 * distribution.
16 *
17 * 3. The name "Carnegie Mellon University" must not be used to
18 * endorse or promote products derived from this software without
19 * prior written permission. For permission or any legal
20 * details, please contact
21 * Carnegie Mellon University
22 * Center for Technology Transfer and Enterprise Creation
23 * 4615 Forbes Avenue
24 * Suite 302
25 * Pittsburgh, PA 15213
26 * (412) 268-7393, fax: (412) 268-7395
27 * innovation@andrew.cmu.edu
28 *
29 * 4. Redistributions of any form whatsoever must retain the following
30 * acknowledgment:
31 * "This product includes software developed by Computing Services
32 * at Carnegie Mellon University (http://www.cmu.edu/computing/)."
33 *
34 * CARNEGIE MELLON UNIVERSITY DISCLAIMS ALL WARRANTIES WITH REGARD TO
35 * THIS SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
36 * AND FITNESS, IN NO EVENT SHALL CARNEGIE MELLON UNIVERSITY BE LIABLE
37 * FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
38 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN
39 * AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING
40 * OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
41 *
42 */
43
44 #include <config.h>
45
46 #include <stdlib.h>
47 #include <syslog.h>
48 #include <string.h>
49 #include <errno.h>
50 #ifdef HAVE_UNISTD_H
51 #include <unistd.h>
52 #endif
53 #include <sys/stat.h>
54 #include <sys/types.h>
55 #include <sys/wait.h>
56
57 #include "assert.h"
58 #include "sqldb.h"
59 #include "util.h"
60 #include "xmalloc.h"
61
62 static int sqldb_active = 0;
63
64 static sqldb_t *open_sqldbs;
65
sqldb_init(void)66 EXPORTED int sqldb_init(void)
67 {
68 if (!sqldb_active++) {
69 sqlite3_initialize();
70 assert(!open_sqldbs);
71 }
72
73 return 0;
74 }
75
sqldb_done(void)76 EXPORTED int sqldb_done(void)
77 {
78 if (!--sqldb_active) {
79 sqlite3_shutdown();
80 /* XXX - report the problems? */
81 assert(!open_sqldbs);
82 }
83
84 return 0;
85 }
86
_debug(void * fname,const char * sql)87 static void _debug(void *fname, const char *sql)
88 {
89 syslog(LOG_DEBUG, "sqldb_exec(%s): %s", (const char *) fname, sql);
90 }
91
_free_open(sqldb_t * open)92 static int _free_open(sqldb_t *open)
93 {
94 int rc = sqlite3_close(open->db);
95 free(open->fname);
96 free(open);
97 int r = (rc == SQLITE_OK ? 0 : -1);
98 return r;
99 }
100
_version_cb(void * rock,int ncol,char ** vals,char ** names)101 static int _version_cb(void *rock, int ncol, char **vals, char **names __attribute__((unused)))
102 {
103 int *vptr = (int *)rock;
104 if (ncol == 1 && vals[0])
105 *vptr = atoi(vals[0]);
106 else
107 abort();
108 return 0;
109 }
110
111 /* Open DAV DB corresponding in file */
sqldb_open(const char * fname,const char * initsql,int version,const struct sqldb_upgrade * upgrade,int timeout_ms)112 EXPORTED sqldb_t *sqldb_open(const char *fname, const char *initsql,
113 int version, const struct sqldb_upgrade *upgrade,
114 int timeout_ms)
115 {
116 int rc = SQLITE_OK;
117 struct stat sbuf;
118 sqldb_t *open;
119 int i;
120
121 for (open = open_sqldbs; open; open = open->next) {
122 if (!strcmp(open->fname, fname)) {
123 /* already open! */
124 open->refcount++;
125 return open;
126 }
127 }
128
129 open = xzmalloc(sizeof(sqldb_t));
130 open->fname = xstrdup(fname);
131
132 rc = stat(open->fname, &sbuf);
133 if (rc == -1 && errno == ENOENT) {
134 rc = cyrus_mkdir(open->fname, 0755);
135 }
136
137 rc = sqlite3_open_v2(open->fname, &open->db,
138 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
139 if (rc != SQLITE_OK) {
140 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) open: %s",
141 open->fname, open->db ? sqlite3_errmsg(open->db) : "failed");
142 _free_open(open);
143 return NULL;
144 }
145
146 sqlite3_extended_result_codes(open->db, 1);
147 sqlite3_trace(open->db, _debug, open->fname);
148
149 sqlite3_busy_timeout(open->db, timeout_ms);
150
151 rc = sqlite3_exec(open->db, "PRAGMA foreign_keys = ON;", NULL, NULL, NULL);
152 if (rc != SQLITE_OK) {
153 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) enable foreign_keys: %s",
154 open->fname, sqlite3_errmsg(open->db));
155 _free_open(open);
156 return NULL;
157 }
158
159 /* <http://stackoverflow.com/questions/19530419/
160 * sqlite-efficient-way-to-drop-lots-of-rows/19536232#19536232>
161 * it's expensive and not needed here
162 */
163 rc = sqlite3_exec(open->db, "PRAGMA secure_delete = OFF;", NULL, NULL, NULL);
164 if (rc != SQLITE_OK) {
165 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) disable secure_delete: %s",
166 open->fname, sqlite3_errmsg(open->db));
167 _free_open(open);
168 return NULL;
169 }
170
171 /* https://sqlite.org/pragma.html#pragma_temp_store
172 * When temp_store is MEMORY (2) temporary tables and indices are
173 * kept in as if they were pure in-memory databases memory.
174 */
175 rc = sqlite3_exec(open->db, "PRAGMA temp_store = 2;", NULL, NULL, NULL);
176 if (rc != SQLITE_OK) {
177 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) enable foreign_keys: %s",
178 open->fname, sqlite3_errmsg(open->db));
179 _free_open(open);
180 return NULL;
181 }
182
183 rc = sqlite3_exec(open->db, "PRAGMA user_version;", _version_cb, &open->version, NULL);
184 if (rc != SQLITE_OK) {
185 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) get user_version: %s",
186 open->fname, sqlite3_errmsg(open->db));
187 _free_open(open);
188 return NULL;
189 }
190 if (open->version == version) goto out;
191
192 /* if no initsql was passed, then we have no way to create a DB */
193 if (!initsql) {
194 /* just keep the version we already have */
195 if (open->version) goto out;
196 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) no initsql and no DB",
197 open->fname);
198 _free_open(open);
199 return NULL;
200 }
201
202 rc = sqlite3_exec(open->db, "BEGIN EXCLUSIVE;", NULL, NULL, NULL);
203 if (rc != SQLITE_OK) {
204 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) begin: %s",
205 open->fname, sqlite3_errmsg(open->db));
206 _free_open(open);
207 return NULL;
208 }
209
210 rc = sqlite3_exec(open->db, "PRAGMA user_version;", _version_cb, &open->version, NULL);
211 if (rc != SQLITE_OK) {
212 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) get user_version locked: %s",
213 open->fname, sqlite3_errmsg(open->db));
214 _free_open(open);
215 return NULL;
216 }
217
218 if (open->version == version) goto transout;
219
220 if (open->version == 0) {
221 syslog(LOG_NOTICE, "creating sql_db %s", open->fname);
222 rc = sqlite3_exec(open->db, initsql, NULL, NULL, NULL);
223 if (rc != SQLITE_OK) {
224 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) create: %s",
225 open->fname, sqlite3_errmsg(open->db));
226 _free_open(open);
227 return NULL;
228 }
229 }
230 else {
231 if (!upgrade) {
232 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) need upgrade from %d to %d: %s",
233 open->fname, open->version, version, sqlite3_errmsg(open->db));
234 _free_open(open);
235 return NULL;
236 }
237 for (i = 0; upgrade[i].to; i++) {
238 if (upgrade[i].to <= open->version) continue;
239
240 syslog(LOG_NOTICE, "sqldb_open(%s) upgrade to v%d", open->fname, upgrade[i].to);
241 if (upgrade[i].sql) {
242 rc = sqlite3_exec(open->db, upgrade[i].sql, NULL, NULL, NULL);
243 if (rc != SQLITE_OK) {
244 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) upgrade v%d: %s",
245 open->fname, upgrade[i].to, sqlite3_errmsg(open->db));
246 _free_open(open);
247 return NULL;
248 }
249 }
250 if (upgrade[i].cb) {
251 rc = upgrade[i].cb(open);
252 if (rc != SQLITE_OK) {
253 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) upgrade v%d: %s",
254 open->fname, upgrade[i].to, sqlite3_errmsg(open->db));
255 _free_open(open);
256 return NULL;
257 }
258 }
259 }
260 }
261
262 struct buf buf = BUF_INITIALIZER;
263 buf_printf(&buf, "PRAGMA user_version = %d;", version);
264 rc = sqlite3_exec(open->db, buf_cstring(&buf), NULL, NULL, NULL);
265 buf_free(&buf);
266 if (rc != SQLITE_OK) {
267 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) user_version: %s",
268 open->fname, sqlite3_errmsg(open->db));
269 _free_open(open);
270 return NULL;
271 }
272
273 open->version = version;
274
275 transout:
276 rc = sqlite3_exec(open->db, "COMMIT;", NULL, NULL, NULL);
277 if (rc != SQLITE_OK) {
278 syslog(LOG_ERR, "DBERROR: sqldb_open(%s) commit: %s",
279 open->fname, sqlite3_errmsg(open->db));
280 _free_open(open);
281 return NULL;
282 }
283
284 out:
285 /* stitch on up */
286 open->refcount = 1;
287 open->next = open_sqldbs;
288 open_sqldbs = open;
289
290 return open;
291 }
292
_prepare_stmt(sqldb_t * open,const char * cmd)293 static sqlite3_stmt *_prepare_stmt(sqldb_t *open, const char *cmd)
294 {
295 int i;
296 sqlite3_stmt *stmt;
297 for (i = 0; i < open->stmts.count; i++) {
298 stmt = ptrarray_nth(&open->stmts, i);
299 if (!strcmp(cmd, sqlite3_sql(stmt)))
300 return stmt;
301 }
302 /* prepare new statement */
303 int rc = sqlite3_prepare_v2(open->db, cmd, -1, &stmt, NULL);
304 if (rc != SQLITE_OK) {
305 syslog(LOG_ERR, "DBERROR: sqldb_exec(%s) prepare <%s>: %s",
306 open->fname, cmd, sqlite3_errmsg(open->db));
307 return NULL;
308 }
309 ptrarray_append(&open->stmts, stmt);
310 return stmt;
311 }
312
_finish_stmt(sqldb_t * open)313 static void _finish_stmt(sqldb_t *open)
314 {
315 int i;
316 sqlite3_stmt *stmt;
317 for (i = 0; i < open->stmts.count; i++) {
318 stmt = ptrarray_nth(&open->stmts, i);
319 sqlite3_finalize(stmt);
320 }
321 ptrarray_fini(&open->stmts);
322 }
323
sqldb_exec(sqldb_t * open,const char * cmd,struct sqldb_bindval bval[],int (* cb)(sqlite3_stmt * stmt,void * rock),void * rock)324 EXPORTED int sqldb_exec(sqldb_t *open, const char *cmd, struct sqldb_bindval bval[],
325 int (*cb)(sqlite3_stmt *stmt, void *rock), void *rock)
326 {
327 int rc, r = 0;
328 sqlite3_stmt *stmt = _prepare_stmt(open, cmd);
329 if (!stmt) return -1;
330
331 /* bind values */
332 for (; bval && bval->name; bval++) {
333 int cidx = sqlite3_bind_parameter_index(stmt, bval->name);
334
335 switch (bval->type) {
336 case SQLITE_INTEGER:
337 sqlite3_bind_int64(stmt, cidx, bval->val.i);
338 break;
339
340 case SQLITE_TEXT:
341 sqlite3_bind_text(stmt, cidx, bval->val.s, -1, NULL);
342 break;
343 }
344 }
345
346 /* execute and process the results */
347 while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
348 if (cb && (r = cb(stmt, rock))) break;
349 }
350
351 /* reset statement and clear all bindings */
352 sqlite3_reset(stmt);
353 sqlite3_clear_bindings(stmt);
354
355 if (!r && rc != SQLITE_DONE) {
356 syslog(LOG_ERR, "DBERROR: sqldb_exec() step: %s for (%s: %s)",
357 sqlite3_errmsg(open->db), open->fname, cmd);
358 r = -1;
359 }
360
361 return r;
362 }
363
_onecmd(sqldb_t * open,const char * cmd,const char * name)364 static int _onecmd(sqldb_t *open, const char *cmd, const char *name)
365 {
366 static struct buf buf = BUF_INITIALIZER;
367 buf_reset(&buf);
368 buf_printf(&buf, "%s %s;", cmd, name);
369 return sqldb_exec(open, buf_cstring(&buf), NULL, NULL, NULL);
370 }
371
sqldb_begin(sqldb_t * open,const char * name)372 EXPORTED int sqldb_begin(sqldb_t *open, const char *name)
373 {
374 if (!name) name = "DUMMY";
375 int r = 0;
376 if (!open->writelock) r = sqldb_writelock(open);
377 if (!r) r = _onecmd(open, "SAVEPOINT", name);
378 if (!r) strarray_push(&open->trans, name);
379 return r;
380 }
381
sqldb_commit(sqldb_t * open,const char * name)382 EXPORTED int sqldb_commit(sqldb_t *open, const char *name)
383 {
384 assert(open->trans.count);
385 char *prev = strarray_pop(&open->trans);
386 if (name) assert(!strcmp(prev, name));
387 int r = _onecmd(open, "RELEASE SAVEPOINT", prev);
388 if (r) strarray_push(&open->trans, prev);
389 free(prev);
390 if (!r && !open->trans.count) r = sqldb_writecommit(open);
391 return r;
392 }
393
sqldb_rollback(sqldb_t * open,const char * name)394 EXPORTED int sqldb_rollback(sqldb_t *open, const char *name)
395 {
396 assert(open->trans.count);
397 char *prev = strarray_pop(&open->trans);
398 if (name) assert(!strcmp(prev, name));
399 int r = _onecmd(open, "ROLLBACK TO SAVEPOINT", prev);
400 if (r) strarray_push(&open->trans, prev);
401 // it's still commit here even if we rolled back THIS savepoint,
402 // because other savepoints may have committed, so we want to
403 // commit the wrapping transaction
404 if (!r && !open->trans.count) r = sqldb_writecommit(open);
405 free(prev);
406 return r;
407 }
408
sqldb_writelock(sqldb_t * open)409 EXPORTED int sqldb_writelock(sqldb_t *open)
410 {
411 assert (!open->writelock);
412 assert (!open->trans.count);
413 int r = sqldb_exec(open, "BEGIN IMMEDIATE;", NULL, NULL, NULL);
414 if (!r) open->writelock = 1;
415 return r;
416 }
417
sqldb_writecommit(sqldb_t * open)418 EXPORTED int sqldb_writecommit(sqldb_t *open)
419 {
420 if (!open->writelock) return 0;
421 strarray_truncate(&open->trans, 0);
422 int r = sqldb_exec(open, "COMMIT;", NULL, NULL, NULL);
423 if (!r) open->writelock = 0;
424 return r;
425 }
426
sqldb_writeabort(sqldb_t * open)427 EXPORTED int sqldb_writeabort(sqldb_t *open)
428 {
429 if (!open->writelock) return 0;
430 strarray_truncate(&open->trans, 0);
431 int r = sqldb_exec(open, "ROLLBACK;", NULL, NULL, NULL);
432 if (!r) open->writelock = 0;
433 return r;
434 }
435
436
sqldb_lastid(sqldb_t * open)437 EXPORTED int sqldb_lastid(sqldb_t *open)
438 {
439 return sqlite3_last_insert_rowid(open->db);
440 }
441
sqldb_changes(sqldb_t * open)442 EXPORTED int sqldb_changes(sqldb_t *open)
443 {
444 return sqlite3_changes(open->db);
445 }
446
sqldb_close(sqldb_t ** dbp)447 EXPORTED int sqldb_close(sqldb_t **dbp)
448 {
449 sqldb_t *open, *prev = NULL;
450
451 if (!*dbp) return 0;
452
453 for (open = open_sqldbs; open; open = open->next) {
454 if (*dbp == open) {
455 if (--open->refcount) return 0; /* still in use */
456 if (prev)
457 prev->next = open->next;
458 else
459 open_sqldbs = open->next;
460 break;
461 }
462 prev = open;
463 }
464
465 assert(open);
466 assert(!open->trans.count);
467
468 strarray_fini(&open->trans);
469 _finish_stmt(open);
470
471 *dbp = NULL;
472
473 return _free_open(open);
474 }
475
sqldb_attach(sqldb_t * open,const char * fname)476 EXPORTED int sqldb_attach(sqldb_t *open, const char *fname)
477 {
478 if (open->attached) return SQLITE_MISUSE;
479 struct sqldb_bindval bval[] = {
480 { ":fname", SQLITE_TEXT, { .s = fname } },
481 { NULL, SQLITE_NULL, { .s = NULL } } };
482
483 struct stat sbuf;
484 if (stat(fname, &sbuf)) return SQLITE_NOTFOUND;
485
486 int r = sqldb_exec(open, "ATTACH DATABASE :fname AS other;", bval, NULL, NULL);
487 if (r) return r;
488 open->attached = 1;
489 return 0;
490 }
491
sqldb_detach(sqldb_t * open)492 EXPORTED int sqldb_detach(sqldb_t *open)
493 {
494 if (!open->attached) return SQLITE_MISUSE;
495 int r = sqldb_exec(open, "DETACH DATABASE other;", NULL, NULL, NULL);
496 if (r) return r;
497 open->attached = 0;
498 return 0;
499 }
500