diff options
Diffstat (limited to 'database/sqlite/sqlite.c')
-rw-r--r-- | database/sqlite/sqlite.c | 81 |
1 files changed, 76 insertions, 5 deletions
diff --git a/database/sqlite/sqlite.c b/database/sqlite/sqlite.c index 2133df8..5ae1ca1 100644 --- a/database/sqlite/sqlite.c +++ b/database/sqlite/sqlite.c @@ -32,6 +32,7 @@ #include <stdlib.h> #include <assert.h> #include <sqlite3.h> +#include <time.h> #ifdef HAVE_LIBXML2 # include <libxml/tree.h> @@ -242,6 +243,73 @@ static int _cb_parse_result(void *resultptr, int argc, char **argv, char **colNa return 0; } + +/** + * Simple date/time function which converts UTC to local time + * + * @param ctx SQLite database connection context + * @param argc Number of arguments available + * @param argv Argument list + * + */ +void _sqlitefnc_localdatetime(sqlite3_context *context, int argc, sqlite3_value **argv) +{ + struct tm tm, loctm; + time_t t; + char buf[255]; + + assert( argc == 1); + + switch(sqlite3_value_type(argv[0])) { + case SQLITE_NULL: + /* NULL in is NULL out */ + sqlite3_result_null(context); + break; + + case SQLITE_TEXT: + memset(&tm, 0, sizeof(struct tm)); + memset(&t, 0, sizeof(time_t)); + memset(&buf, 0, sizeof(buf)); + + /* Convert the input datetime string to time_t format, expect it to be UTC/GMT */ + strptime((const char *)sqlite3_value_text(argv[0]), "%Y-%m-%d %H:%M:%S", &tm); + t = timegm(&tm); + + /* Convert from UTC/GMT to localtime and format the new output string */ + localtime_r(&t, &loctm); + strftime(buf, sizeof(buf), "%Y-%m-%d %H:%M:%S", &loctm); + sqlite3_result_text(context, buf, strlen(buf), SQLITE_TRANSIENT); + break; + } +} + + +/** + * Initialise the SQLite database with additional functions usable via SQL + * + * @param ctx eurephia context with the opened database + * + * @return Returns dbSUCCESS on success, otherwise dbERROR. + */ +int sqlite_init_functions(eurephiaCTX *ctx) +{ + int rc; + + /* locdt(TIMESTAMP): datetime function which converts UTC/GMT time stamps into + * the localtime zone. Expects the format to be 'YYYY-MM-DD HH24:MI:SS'. + * Used by the administration functions to return the correct local time + */ + rc = sqlite3_create_function(ctx->dbc->dbhandle, "locdt", 1, SQLITE_ANY, + NULL, &_sqlitefnc_localdatetime, NULL, NULL); + if( rc != SQLITE_OK ) { + eurephia_log(ctx, LOG_PANIC, 0, "Failed to register local date/time function (%i)", rc); + return dbERROR; + } + + return dbSUCCESS; +} + + /** * Logs sqlite error messages via the eurephia log interface and returns an XML node with details * (This function is only included if libxml2 is available) @@ -753,16 +821,18 @@ int main(int argc, char **argv) { ctx = malloc_nullsafe(NULL, sizeof(eurephiaCTX)+2); ctx->dbc = malloc_nullsafe(NULL, sizeof(eDBconn)+2); - + eurephia_log_init(ctx, "sqlitedbg", "stderr:", 10); + rc = sqlite3_open(argv[1], (void *) &ctx->dbc->dbhandle); if( rc ) { fprintf(stderr, "Could not open db\n"); return 1; } - - ctx->log = stderr; - ctx->loglevel = 5; - + + if( sqlite_init_functions(ctx) != dbSUCCESS ) { + fprintf(stderr, "Failed to register functions\n"); + return 1; + } res = sqlite_query(ctx, argv[2]); if( res != NULL ) { @@ -771,6 +841,7 @@ int main(int argc, char **argv) { } sqlite3_close(ctx->dbc->dbhandle); free(ctx->dbc); + eurephia_log_close(ctx); free(ctx); return 0; } |