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;
 }