diff options
Diffstat (limited to 'database/sqlite')
-rw-r--r-- | database/sqlite/edb-sqlite.c | 7 | ||||
-rw-r--r-- | database/sqlite/sqlite.c | 81 | ||||
-rw-r--r-- | database/sqlite/sqlite.h | 2 |
3 files changed, 85 insertions, 5 deletions
diff --git a/database/sqlite/edb-sqlite.c b/database/sqlite/edb-sqlite.c index 4c15a5a..724e095 100644 --- a/database/sqlite/edb-sqlite.c +++ b/database/sqlite/edb-sqlite.c @@ -164,6 +164,13 @@ int eDBconnect(eurephiaCTX *ctx, const int argc, const char **argv) dbc->config = NULL; ctx->dbc = dbc; + if( sqlite_init_functions(ctx) != dbSUCCESS ) { + sqlite3_close((sqlite3 *) dbc->dbhandle); + free_nullsafe(ctx, dbc->dbname); + dbc->dbhandle = NULL; + return 0; + } + // Load configuration parameters into memory eurephia_log(ctx, LOG_INFO, 1, "Reading config from database (openvpn_config)"); res = sqlite_query(ctx, "SELECT datakey, dataval FROM openvpn_config"); 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; } diff --git a/database/sqlite/sqlite.h b/database/sqlite/sqlite.h index 5a7c5fd..b521687 100644 --- a/database/sqlite/sqlite.h +++ b/database/sqlite/sqlite.h @@ -136,6 +136,8 @@ typedef struct __sqlite_dbresult { typedef enum _SQLqueryType { SQL_SELECT, SQL_INSERT, SQL_UPDATE, SQL_DELETE } SQLqueryType; +int sqlite_init_functions(eurephiaCTX *ctx); + /** * Free up a dbresult structure. This is the public interface for the * internal function _sqlite_free_results() |