sqlite: Added SQL function to convert datetime timestamps from UTC/GMT to localtime

All CURRENT_TIMESTAMP calls are returned in UTC/GMT, and this value is stored in the
database.  When using eurephiadm to look at these datetime fields the UTC/GMT value
is used, and needs to be taken in consideration when looking at the reports.  This
patch is the first step to handle the local time zone better.

This patch also fixes the 'debug' program in sqlite.c, making use of the
eurephia_log_init() and eurephia_log_close() calls for log preparations.

Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>

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()