Update field mapping to include data type. Improved sqlite_query_mapped(...) function

The new sqlite_query_mapped(...) function will now consider the field
type when formatting the data values for SQLite.  In addition reserved
words on the DATETIME data type will be preserved without quote
encapsulation.

Improved the sqlite_query_mapped(...) to also handle UPDATE, INSERT and
DELETE in addition to SELECT.  The function now needs two eDBfieldMap
mappings.  The first one will contain update/insert values, while the
last one defines search criteria.

1 files changed, 151 insertions, 22 deletions

diff --git a/database/sqlite/sqlite.c b/database/sqlite/sqlite.c
index 7d240a9..425b128 100644
--- a/database/sqlite/sqlite.c
+++ b/database/sqlite/sqlite.c
@@ -38,6 +38,9 @@
 #include <memwatch.h>
 #endif
 
+#define btWHERE  0x001
+#define btINSERT 0x002
+#define btUPDATE 0x004
 
 dbresult *glob_results = NULL;
 
@@ -240,34 +243,160 @@ dbresult *sqlite_query(eurephiaCTX *ctx, char *fmt, ... ) {
         return glob_results;
 }
 
-dbresult *sqlite_query_mapped(eurephiaCTX *ctx, char *sql_start, eDBfieldMap *whmap) {
+inline int SQLreservedWord(char **reserved_words, const char *val) {
+        int i;
+
+        // If we find the word in val defined in the reserved_words array, return 1.
+        for( i = 0; reserved_words[i] != NULL; i++ ) {
+                if( strcmp(val, reserved_words[i]) == 0) {
+                        return 1;
+                }
+        }
+        return 0;
+}
+
+
+char *_build_value_string(eDBfieldMap *ptr) {
+        char *reserved_datetime[] = {"CURRENT_TIMESTAMP", "CURRENT_TIME", "CURRENT_DATE", NULL};
+        char *val = NULL;
+
+        // Format the input value according to the defined data type
+        switch( ptr->field_type ) {
+        case ft_INT:
+                // Format integer values as integers
+                val = sqlite3_mprintf("%i", atoi_nullsafe(ptr->value));
+                break;
+
+        case ft_DATETIME:
+                // If the value is a reserved SQL word, don't encapsulate the value
+                if( SQLreservedWord(reserved_datetime, ptr->value) == 1 ) {
+                        val = sqlite3_mprintf("%q", ptr->value);
+                } else {
+                        val = sqlite3_mprintf("'%q'", ptr->value);
+                }
+                break;
+
+        case ft_PASSWD:
+        case ft_STRING:
+        default:
+                val = sqlite3_mprintf("'%q'", ptr->value);
+                break;
+        }
+        return val;
+}
+
+#define append_str(dest, src, size) strncat(dest, src, (size - strlen_nullsafe(dest)))
+char *_build_sqlpart(int btyp, eDBfieldMap *map) {
         eDBfieldMap *ptr = NULL;
-        char where[8195], *sqlp = NULL;
-        int first;
-
-        assert((ctx != NULL) && (sql_start != NULL) && (whmap != NULL) );
-
-        // Prepare where clause
-        memset(&where, 0, 8195);
-        strcat(where, " (");
-        first = 1;
-
-        // Loop through all fields in the field map, and add properly
-        // formed SQL for a WHERE clause
-        for(ptr = whmap; ptr != NULL; ptr = ptr->next) {
-                // Only add those fields which is not NULL
-                if( (ptr->value != NULL) ) {
-                        sqlp = sqlite3_mprintf("%s%s='%q'", (first != 1 ? " AND " : ""),
-                                               ptr->field_name, ptr->value);
-                        strncat(where, sqlp,  (8192 - strlen_nullsafe(where)));
-                        sqlite3_free(sqlp);
+        char *ret = NULL;
+        char *fsep = NULL;
+        char fields[4094], vals[4094];
+        char buf[8196];
+        int first = 1;
+
+        memset(&buf, 0, 8196);
+        switch( btyp ) {
+        case btWHERE:
+        case btUPDATE:
+                fsep = (btyp == btWHERE ? " AND " : ",");
+                append_str(buf, "(", 8192);
+                for( ptr = map; ptr != NULL; ptr = ptr->next ) {
+                        char *val = NULL;
+
+                        // If the value is NULL, ignore it if we're building up WHERE clause
+                        if( (ptr->value == NULL) && (btyp == btWHERE) ) {
+                                continue;
+                        }
+
+                        // Add separator in front of next value
+                        if( first != 1 ) {
+                                append_str(buf, fsep, 8192);
+                        }
+
+                        // Put the pieces together and append it to the final result
+                        val = _build_value_string(ptr);
+                        append_str(buf, ptr->field_name, 8192);
+                        append_str(buf, "=", 8192);
+                        append_str(buf, val, 8192);
+                        sqlite3_free(val);
                         first = 0;
                 }
+                append_str(buf, ")", 8192);
+                break;
+
+        case btINSERT: // (field_name, field_name, field_name) VALUES ('val','val','val')
+                memset(&fields, 0, 4090);
+
+                for( ptr = map; ptr != NULL; ptr = ptr->next ) {
+                        char *val = NULL;
+
+                        // Put separator in front of values and field names
+                        if( first != 1 ) {
+                                append_str(fields, ",", 8192);
+                                append_str(vals, ",", 8192);
+                                first = 0;
+                        }
+
+                        // Append the current field name and value to each string
+                        append_str(fields, ptr->field_name, 4092);
+                        val = _build_value_string(ptr);
+                        append_str(vals, val, 4092);
+                        sqlite3_free(val);
+                }
+
+                // Put all the pieces together
+                append_str(buf, "(", 8192);
+                append_str(buf, fields, 8192);
+                append_str(buf, ") VALUES (", 8192);
+                append_str(buf, vals, 8192);
+                append_str(buf, ")", 8192);
+                break;
         }
-        strcat(where, ")");  // End this where clause
+
+        ret = strdup_nullsafe(buf);
+        return ret;
+}
+
+
+dbresult *sqlite_query_mapped(eurephiaCTX *ctx, SQLqueryType qType, const char *sqlstub,
+                              eDBfieldMap *valMap, eDBfieldMap *whereMap)
+{
+        dbresult *res = NULL;
+        char *tmp1 = NULL, *tmp2 = NULL;
+
+        assert((ctx != NULL) && (sqlstub != NULL));
+
+        // Build up SQL clause and send the query
+        switch( qType ) {
+        case SQL_SELECT:
+        case SQL_DELETE:
+                if( whereMap != NULL ) {
+                        tmp1 = _build_sqlpart(btWHERE, whereMap);
+                        res = sqlite_query(ctx, "%s WHERE %s", sqlstub, tmp1);
+                        free_nullsafe(tmp1);
+                }
+                break;
+
+        case SQL_UPDATE:
+                if( (whereMap != NULL) && (valMap != NULL) ) {
+                        tmp1 = _build_sqlpart(btUPDATE, valMap);
+                        tmp2 = _build_sqlpart(btWHERE, whereMap);
+
+                        res = sqlite_query(ctx, "%s SET %s WHERE %s", sqlstub, tmp1, tmp2);
+                        free_nullsafe(tmp1);
+                        free_nullsafe(tmp2);
+                }
+                break;
+
+        case SQL_INSERT:
+                tmp1 = _build_sqlpart(btINSERT, valMap);
+                res = sqlite_query(ctx, "%s %s", sqlstub, tmp1);
+                free_nullsafe(tmp1);
+                break;
+       }
 
         // Send the SQL query to the database and return the result
-        return sqlite_query(ctx, "%s WHERE %s", sql_start, where);
+        return res;
 }
 
 // Simple line-by-line result dumper