/**
* \file datastore_sqlite.c SQLite 3 database driver back-end
* \author Matthias Andree <matthias.andree@gmx.de>
* \date 2004, 2005
*
* This file handles a static table named "bogofilter" in a SQLite3
* database. The table has two "BLOB"-typed columns, key and value.
*
* GNU GENERAL PUBLIC LICENSE v2
*/
#include "common.h"
#include <errno.h>
#include <sqlite3.h>
#include "datastore_db.h"
#include "error.h"
#include "rand_sleep.h"
#include "xmalloc.h"
#include "xstrdup.h"
/** Structure to hold database handle and associated data. */
struct dbhsqlite_t {
char *path; /**< directory to hold database */
char *name; /**< database file name */
sqlite3 *db; /**< pointer to SQLite3 handle */
sqlite3_stmt *stmt_select; /**< prepared SELECT statement for DB retrieval */
sqlite3_stmt *stmt_insert; /**< prepared INSERT OR REPLACE for DB update */
sqlite3_stmt *stmt_delete; /**< prepared DELETE statement */
bool created; /**< gets set by db_open if it created the database new */
bool swapped; /**< if endian swapped on disk vs. current host */
};
/** Convenience shortcut to avoid typing "struct dbh_t" */
typedef struct dbhsqlite_t dbh_t;
static const char *ENDIAN32 = ".ENDIAN32";
void db_flush(void *unused) { (void)unused; }
static int sql_txn_begin(void *vhandle);
static int sql_txn_abort(void *vhandle);
static int sql_txn_commit(void *vhandle);
static u_int32_t sql_pagesize(bfpath *bfp);
static ex_t sql_verify(bfpath *bfp);
/** The layout of the bogofilter table, formatted as SQL statement.
*
* The additional index, although making writes a bit slower, speeds up
* queries noticably as it improves locality of referenced data and
* reduces complexity of the retrieval of the value column.
*/
#define LAYOUT \
"CREATE TABLE bogofilter (" \
" key BLOB PRIMARY KEY," \
" value BLOB);" \
"CREATE INDEX bfidx ON bogofilter(key,value);"
/*
* another experimental layout is as follows,
* but does not appear to make a lot of difference
* performance-wise (evaluation in other environments
* is required though):
*
#define LAYOUT \
"CREATE TABLE bogofilter (key BLOB, value BLOB); " \
"CREATE INDEX bfidx ON bogofilter(key,value);" \
"CREATE TRIGGER bfuniquekey BEFORE INSERT ON bogofilter " \
" FOR EACH ROW WHEN EXISTS(SELECT key FROM bogofilter WHERE (key=NEW.key) LIMIT 1) " \
" BEGIN UPDATE bogofilter SET value=NEW.value WHERE (key=NEW.key); SELECT RAISE(IGNORE); END;"
#endif
*/
dsm_t dsm_sqlite = {
/* public -- used in datastore.c */
&sql_txn_begin,
&sql_txn_abort,
&sql_txn_commit,
/* private -- used in datastore_db_*.c */
NULL, /* dsm_env_init */
NULL, /* dsm_cleanup */
NULL, /* dsm_cleanup_lite */
NULL, /* dsm_get_env_dbe */
NULL, /* dsm_database_name */
NULL, /* dsm_recover_open */
NULL, /* dsm_auto_commit_flags*/
NULL, /* dsm_get_rmw_flag */
NULL, /* dsm_lock */
NULL, /* dsm_common_close */
NULL, /* dsm_sync */
NULL, /* dsm_log_flush */
&sql_pagesize,/* dsm_pagesize */
NULL, /* dsm_purgelogs */
NULL, /* dsm_checkpoint */
NULL, /* dsm_recover */
NULL, /* dsm_remove */
&sql_verify,/* dsm_verify */
NULL, /* dsm_list_logfiles */
NULL /* dsm_leafpages */
};
dsm_t *dsm = &dsm_sqlite;
/** The command to begin a regular transaction. */
#define BEGIN \
"BEGIN TRANSACTION;"
/* real functions */
/** Initialize database handle and return it.
* \returns non-NULL, as it exits with EX_ERROR in case of trouble. */
static dbh_t *dbh_init(bfpath *bfp)
{
dbh_t *handle;
dsm = &dsm_sqlite;
handle = (dbh_t *)xmalloc(sizeof(dbh_t));
memset(handle, 0, sizeof(dbh_t));
handle->name = xstrdup(bfp->filepath);
return handle;
}
/** Free internal database handle \a dbh. */
static void free_dbh(dbh_t *dbh) {
if (!dbh)
return;
xfree(dbh->name);
xfree(dbh->path);
xfree(dbh);
}
/** Executes the SQL statement \a cmd on the database \a db and returns
* the sqlite3_exec return code. If the return code is nonzero, this
* routine will have printed an error message.
*/
static int sqlexec(sqlite3 *db, const char *cmd) {
char *e = NULL;
int rc;
rc = sqlite3_exec(db, cmd, NULL, NULL, &e);
if (rc) {
print_error(__FILE__, __LINE__,
"Error executing \"%s\": %s (#%d)\n",
cmd, e ? e : "NULL", rc);
if (e)
sqlite3_free(e);
}
return rc;
}
/** Compile SQL statement \a cmd for database handle \a dbh, exiting on
* failure if \a bailout is true. */
static sqlite3_stmt *sqlprep(dbh_t *dbh /** data base handle */,
const char *cmd /** sqlite command to compile */,
bool bailout /** exit on error? */) {
const char *tail; /* dummy */
sqlite3_stmt *ptr;
if (sqlite3_prepare_v2(dbh->db, cmd, strlen(cmd), &ptr, &tail) != SQLITE_OK) {
print_error(__FILE__, __LINE__, "cannot compile %s: %s\n", cmd, sqlite3_errmsg(dbh->db));
if (bailout)
exit(EX_ERROR);
return NULL;
}
return ptr;
}
/** Short trace handler function, passed to SQLite if debugging is
* enabled. */
static void db_trace(void *userdata /** unused */,
const char *log /** log message */) {
(void)userdata;
fprintf(dbgout, "SQLite[%ld]: %s\n", (long)getpid(), log);
}
/** Foreach function, we call \a hook for
* each (key, value) tuple in the database.
*/
static int db_loop(sqlite3 *db, /**< SQLite3 database handle */
const char *cmd, /**< SQL command to obtain data */
db_foreach_t hook, /**< if non-NULL, called for each value */
void *userdata /** this is passed to the \a hook */
) {
const char *tail;
sqlite3_stmt *stmt;
int rc;
bool loop, found = false;
dbv_t key;
dbv_const_t val;
/* sqlite3_exec doesn't allow us to retrieve BLOBs */
rc = sqlite3_prepare_v2(db, cmd, strlen(cmd), &stmt, &tail);
if (rc) {
print_error(__FILE__, __LINE__,
"Error preparing \"%s\": %s (#%d)\n",
cmd, sqlite3_errmsg(db), rc);
sqlite3_finalize(stmt);
return rc;
}
loop = true;
while (loop) {
rc = sqlite3_step(stmt);
switch (rc) {
case SQLITE_ROW:
found = true;
if (hook != NULL)
{
key.leng = sqlite3_column_bytes(stmt, /* column */ 0);
key.data = xmalloc(key.leng);
memcpy(key.data, sqlite3_column_blob(stmt, 0), key.leng);
val.leng = sqlite3_column_bytes(stmt, /* column */ 1);
val.data = sqlite3_column_blob(stmt, 1);
/* skip ENDIAN32 token */
if (key.leng != strlen(ENDIAN32)
|| memcmp(key.data, ENDIAN32, key.leng) != 0)
rc = hook(&key, &val, userdata);
else
rc = 0;
xfree(key.data);
if (rc) {
sqlite3_finalize(stmt);
return rc;
}
}
break;
case SQLITE_DONE:
loop = false;
break;
default:
print_error(__FILE__, __LINE__, "Error executing \"%s\": %s (#%d)\n",
cmd, sqlite3_errmsg(db), rc);
sqlite3_finalize(stmt);
return rc;
}
}
/* free resources */
sqlite3_finalize(stmt);
return found ? 0 : DS_NOTFOUND;
}
/** This busy handler just sleeps a while and retries */
static int busyhandler(void *dummy, int count)
{
(void)dummy;
(void)count;
rand_sleep(1000, 1000000);
return 1;
}
static void check_sqlite_version(void)
{
const unsigned int wmaj = 3, wmin = 6, wpl = 12; /* desired version of sqlite3 library */
unsigned int vmaj, vmin, vpl; /* actual version of sqlite3 library */
static int complained;
const char *v;
if (complained)
return;
complained = 1;
v = sqlite3_libversion();
sscanf(v, "%u.%u.%u", &vmaj, &vmin, &vpl);
if (vmaj > wmaj) return;
if (vmaj == wmaj && vmin > wmin) return;
if (vmaj == wmaj && vmin == wmin && vpl >= wpl) return;
if (!getenv("BF_USE_OLD_SQLITE"))
fprintf(stderr,
"\n"
"WARNING: please update sqlite to %d.%d.%d or newer.\n"
"\n", wmaj, wmin, wpl);
}
void *db_open(void *dummyenv, bfpath *bfp, dbmode_t mode)
{
int rc;
dbh_t *dbh;
dbv_t k, v;
(void)dummyenv;
check_sqlite_version();
dbh = dbh_init(bfp);
/* open database file */
if (DEBUG_DATABASE(1) || getenv("BF_DEBUG_DB")) {
fprintf(dbgout, "SQLite: db_open(%s)\n", dbh->name);
fflush(dbgout);
}
rc = sqlite3_open(dbh->name, &dbh->db);
if (rc) {
print_error(__FILE__, __LINE__, "Can't open database %s: %s\n",
dbh->name, sqlite3_errmsg(dbh->db));
goto barf;
}
/* request extended result codes for improved error reporting */
(void)sqlite3_extended_result_codes(dbh->db, true);
/* set trace mode */
if (DEBUG_DATABASE(1) || getenv("BF_DEBUG_DB"))
sqlite3_trace(dbh->db, db_trace, NULL);
/* set busy handler */
if (sqlite3_busy_handler(dbh->db, busyhandler, NULL)) {
print_error(__FILE__, __LINE__, "Can't set busy handler: %s\n",
sqlite3_errmsg(dbh->db));
goto barf;
}
/* check/set endianness marker and create table if needed */
if (mode != DS_READ) {
/* using IMMEDIATE or DEFERRED here locks up in t.lock3
* or t.bulkmode
* using EXCLUSIVE locks up in t.lock3 on MAC OSX
*/
if (sqlexec(dbh->db, BEGIN)) goto barf;
/*
* trick: the sqlite_master table (see SQLite FAQ) is read-only
* and lists all tables, indexes etc. so we use it to check if
* the bogofilter table is already there, the error codes are
* too vague either way, for "no such table" and "table already
* exists" we always get SQLITE_ERROR, which we'll also get for
* syntax errors, such as "EXCLUSIVE" not supported on older
* versions :-(
*/
rc = db_loop(dbh->db, "SELECT name FROM sqlite_master "
"WHERE type='table' AND name='bogofilter';",
NULL, NULL);
switch (rc) {
case 0:
if (sqlexec(dbh->db, "COMMIT;")) goto barf;
break;
case DS_NOTFOUND:
{
u_int32_t p[2] = { 0x01020304, 0x01020304 };
if (sqlexec(dbh->db, LAYOUT)) goto barf;
/* set endianness marker */
k.data = xstrdup(ENDIAN32);
k.leng = strlen((const char *)k.data);
v.data = p;
v.leng = sizeof(p);
rc = db_set_dbvalue(dbh, &k, &v);
xfree(k.data);
if (rc)
goto barf;
if (sqlexec(dbh->db, "COMMIT;")) goto barf;
dbh->created = true;
}
break;
default:
goto barf;
}
}
/*
* initialize common statements
* dbh->insert is not here as it's needed earlier,
* so it sets itself up lazily
*/
dbh->stmt_select = sqlprep(dbh, "SELECT value FROM bogofilter WHERE key=? LIMIT 1;", false);
if (dbh->stmt_select == NULL)
{
fprintf(stderr,
"\nRemember to register some spam and ham messages before you\n"
"use bogofilter to evaluate mail for its probable spam status!\n\n");
exit(EX_ERROR);
}
dbh->stmt_delete = sqlprep(dbh, "DELETE FROM bogofilter WHERE(key = ?);", true);
/* check if byteswapped */
{
u_int32_t t, b[2];
int ee;
k.data = xstrdup(ENDIAN32);
k.leng = strlen((const char *)k.data);
v.data = b;
v.leng = sizeof(b);
ee = db_get_dbvalue(dbh, &k, &v);
xfree(k.data);
switch (ee) {
case 0: /* found endian marker token, read it */
if (v.leng < 4)
goto barf;
t = ((u_int32_t *)v.data)[0];
switch (t) {
case 0x01020304: /* same endian, "UNIX" */
dbh->swapped = false;
break;
case 0x04030201: /* swapped, "XINU" */
dbh->swapped = true;
break;
default: /* NUXI or IXUN or crap */
print_error(__FILE__, __LINE__,
"Unknown endianness on %s: %08x.\n",
dbh->name, ((u_int32_t *)v.data)[0]);
goto barf2;
}
break;
case DS_NOTFOUND: /* no marker token, assume not swapped */
dbh->swapped = false;
break;
default:
goto barf;
}
}
return dbh;
barf:
print_error(__FILE__, __LINE__, "Error on database %s: %s\n",
dbh->name, sqlite3_errmsg(dbh->db));
barf2:
db_close(dbh);
return NULL;
}
void db_close(void *handle) {
int rc;
dbh_t *dbh = (dbh_t *)handle;
if (dbh->stmt_delete) sqlite3_finalize(dbh->stmt_delete);
if (dbh->stmt_insert) sqlite3_finalize(dbh->stmt_insert);
if (dbh->stmt_select) sqlite3_finalize(dbh->stmt_select);
rc = sqlite3_close(dbh->db);
if (rc) {
print_error(__FILE__, __LINE__, "Can't close database %s: %d",
dbh->name, rc);
exit(EX_ERROR);
}
free_dbh(dbh);
}
const char *db_version_str(void) {
static char buf[80];
if (!buf[0])
snprintf(buf, sizeof(buf), "SQLite %s", sqlite3_libversion());
return buf;
}
static int sql_txn_begin(void *vhandle) {
dbh_t *dbh = (dbh_t *)vhandle;
return sqlexec(dbh->db, BEGIN );
}
static int sql_txn_abort(void *vhandle) {
dbh_t *dbh = (dbh_t *)vhandle;
return sqlexec(dbh->db, "ROLLBACK;");
}
static int sql_txn_commit(void *vhandle) {
dbh_t *dbh = (dbh_t *)vhandle;
return sqlexec(dbh->db, "COMMIT;");
}
/** common code for db_delete, db_(get|set)_dbvalue.
* This works by setting variables in precompiled statements (see PREP,
* sqlite3_prepare, sqlite3_bind_*, sqlite3_reset) and avoids encoding
* binary data into SQL's hex representation as well as compiling the
* same SQL statement over and over again. */
static int sql_fastpath(
dbh_t *dbh, /**< database handle */
const char *func, /**< function name to report in errors */
sqlite3_stmt *stmt, /**< SQLite3 statement to execute/reset */
dbv_t *val, /**< OUT value from first row, NULL ok */
int retnotfound /** return value if no rows found */
)
{
int rc;
bool found = false;
while (1) {
rc = sqlite3_step(stmt);
switch (rc) {
case SQLITE_ROW: /* this is the only branch that loops */
if (val) {
int len = min(INT_MAX, val->leng);
val->leng = min(len, sqlite3_column_bytes(stmt, 0));
memcpy(val->data, sqlite3_column_blob(stmt, 0), val->leng);
}
found = 1;
break;
/* all other branches below return control to the caller */
case SQLITE_BUSY:
sqlite3_reset(stmt);
sql_txn_abort(dbh);
return DS_ABORT_RETRY;
case SQLITE_DONE:
sqlite3_reset(stmt);
return found ? 0 : retnotfound;
default:
print_error(__FILE__, __LINE__,
"%s: error executing statement on %s: %s (%d)\n",
func, dbh->name, sqlite3_errmsg(dbh->db), rc);
sqlite3_reset(stmt);
return rc;
}
}
}
int db_delete(void *vhandle, const dbv_t *key) {
dbh_t *dbh = (dbh_t *)vhandle;
sqlite3_bind_blob(dbh->stmt_delete, 1, key->data, key->leng, SQLITE_STATIC);
return sql_fastpath(dbh, "db_delete", dbh->stmt_delete, NULL, 0);
}
int db_set_dbvalue(void *vhandle, const dbv_t *key, const dbv_t *val) {
dbh_t *dbh = (dbh_t *)vhandle;
if (!dbh->stmt_insert)
dbh->stmt_insert = sqlprep(dbh, "INSERT OR REPLACE INTO bogofilter VALUES(?,?);", true);
sqlite3_bind_blob(dbh->stmt_insert, 1, key->data, key->leng, SQLITE_STATIC);
sqlite3_bind_blob(dbh->stmt_insert, 2, val->data, val->leng, SQLITE_STATIC);
return sql_fastpath(dbh, "db_set_dbvalue", dbh->stmt_insert, NULL, 0);
}
int db_get_dbvalue(void *vhandle, const dbv_t* token, /*@out@*/ dbv_t *val) {
dbh_t *dbh = (dbh_t *)vhandle;
sqlite3_bind_blob(dbh->stmt_select, 1, token->data, token->leng, SQLITE_STATIC);
return sql_fastpath(dbh, "db_get_dbvalue", dbh->stmt_select, val, DS_NOTFOUND);
}
ex_t db_foreach(void *vhandle, db_foreach_t hook, void *userdata) {
dbh_t *dbh = (dbh_t *)vhandle;
const char *cmd = "SELECT key, value FROM bogofilter;";
return db_loop(dbh->db, cmd, hook, userdata) == 0 ? EX_OK : EX_ERROR;
}
const char *db_str_err(int e) {
return e == 0 ? "no error" : "unknown condition (not yet implemented)";
}
bool db_created(void *vhandle) {
dbh_t *dbh = (dbh_t *)vhandle;
return dbh->created;
}
bool db_is_swapped(void *vhandle) {
dbh_t *dbh = (dbh_t *)vhandle;
return dbh->swapped;
}
static int pagesize_cb(void *ptr, int argc, char **argv, char **dummy) {
u_int32_t *uptr = (u_int32_t *)ptr;
(void)dummy;
if (argc != 1)
return -1;
errno = 0;
*uptr = strtoul(argv[0], NULL, 0);
return errno;
}
static u_int32_t sql_pagesize(bfpath *bfp)
{
dbh_t *dbh;
int rc;
u_int32_t size;
dbh = (dbh_t *)db_open(NULL, bfp, DS_READ);
if (!dbh)
return 0xffffffff;
rc = sqlite3_exec(dbh->db, "PRAGMA page_size;", pagesize_cb, &size, NULL);
if (rc != SQLITE_OK) {
return 0xffffffff;
}
db_close(dbh);
return size;
}
static int cb_first;
/** callback function for sql_verify,
* \returns 0 to exhaust the full list of error messages. */
static int cb_verify(void *errflag, int columns,
char * * values, char * * names)
{
int *e = (int *)errflag;
if (columns != 1) {
fprintf(stderr, "Strange: got row with more or less than one column.\n"
"Aborting verification.\n");
return 1;
}
if (cb_first != 1 || 0 != strcmp(values[0], "ok")) {
*e = 1; /* If we get any output other than a single row with the
"ok" text, there's something wrong with the database ->
set error flag and print the error. */
fprintf(stderr, "%s\n", values[0]);
}
(void)names;
cb_first = 0;
return 0;
}
/** Run database verification PRAGMA. Prints all error messages
* and returns EX_OK if none were delivered by sqlite3 or EX_ERROR in
* case of trouble. */
static ex_t sql_verify(bfpath *bfp)
{
dbh_t *dbh;
int rc;
int faulty = 0;
char *errmsg = NULL;
const char stmt[] = "PRAGMA integrity_check;";
if (DEBUG_DATABASE(1) || getenv("BF_DEBUG_DB")) {
fprintf(dbgout, "SQLite: sql_verify(%s)\n", bfp->filename);
fflush(dbgout);
}
dbh = (dbh_t *)db_open(NULL, bfp, DS_READ);
if (!dbh)
return EX_ERROR;
cb_first = 1;
rc = sqlite3_exec(dbh->db, stmt, cb_verify, &faulty, &errmsg);
if (rc != SQLITE_OK) {
print_error(__FILE__, __LINE__, "Error while evaluating \"%s\": %s", stmt,
errmsg ? errmsg : "(unknown)");
if (errmsg) sqlite3_free(errmsg);
return EX_ERROR;
}
db_close(dbh);
if (faulty == 0) {
/* success */
if (verbose) printf("%s: OK\n", bfp->filename);
} else {
/* database faulty */
fprintf(stderr, "%s: Database integrity check failed.\n", bfp->filename);
}
return faulty ? EX_ERROR : EX_OK;
}