path: root/database/sqlite/sqlite.c

/* sqlite.c  --  Generic functions to simplify SQLite3 queries
 *
 *  GPLv2 only - Copyright (C) 2008 - 2012
 *               David Sommerseth <dazo@users.sourceforge.net>
 *
 *  This program is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU General Public License
 *  as published by the Free Software Foundation; version 2
 *  of the License.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 *
 */

/**
 * @file   sqlite.c
 * @author David Sommerseth <dazo@users.sourceforge.net>
 * @date   2008-08-06
 *
 * @brief  Generic functions to simplify the SQLite3 integration.
 *
 */

#include <string.h>
#include <stdlib.h>
#include <assert.h>
#include <sqlite3.h>
#include <time.h>

#ifdef HAVE_LIBXML2
# include <libxml/tree.h>
#endif

#include <eurephia_log.h>
#include <eurephia_nullsafe.h>
#include <eurephia_directions.h>
#include <eurephiadb_mapping.h>

#include "./sqlite.h"

/**
 * @{
 */
#define btWHERE  0x001
#define btINSERT 0x002
#define btUPDATE 0x004
/**
 * @}
 */

/**
 * Internal function for setting the error fields.
 */
static void _sqlite_set_error(dbresult *dbres, ErrorSeverity sev, const char *query, const char *fmt, ...)
{
        char errbuf[4098];
        va_list ap;

        va_start(ap, fmt);
        memset(&errbuf, 0, 4098);
        vsnprintf(errbuf, 4096, fmt, ap);
        va_end(ap);

        dbres->errSeverity = sev;
        dbres->errMsg = strdup(errbuf);
        dbres->query = strdup_nullsafe(query);
}

/**
 * Internal function.  Free up a dbresult structure.  This function is available via the
 * the public macro sqlite_free_results() which is defined in sqlite.h
 *
 * @param inres Pointer to the dbresult structure to be freed
 */
void _sqlite_free_results(dbresult *inres) 
{
        _sqlite_tuples *tup = NULL, *fld = NULL;
        _sqlite_header *hdr = NULL; 

        if( inres == NULL ) {
                return;
        }

        // Remove all tuples from memory
        if( inres->tuples != NULL ) {
                tup = inres->tuples;
                do {
                        fld = tup->nextfield;
                        // Remove all fields from the current tuple recird
                        do {
                                if( fld != fld->prevfield ) {
                                        fld = fld->nextfield;
                                        free_nullsafe(NULL, fld->prevfield->value);
                                        free_nullsafe(NULL, fld->prevfield);
                                }
                        } while( fld != tup );
                        tup = tup->nexttuple;
                        free_nullsafe(NULL, fld->value);
                        free_nullsafe(NULL, fld);
                } while( tup != inres->tuples );
        }

        // Remove all header information
        if( inres->headerrec != NULL ) {
                hdr = inres->headerrec->next;
                do {
                        if( hdr != hdr->prev ) {
                                hdr = hdr->next;
                                free_nullsafe(NULL, hdr->prev->name);
                                free_nullsafe(NULL, hdr->prev);
                        }
                } while( hdr != inres->headerrec );
                free_nullsafe(NULL, hdr->name);
                free_nullsafe(NULL, hdr);
        }

        // Remove error messages and copy of the SQL query
        inres->status = dbEMPTY;
        free_nullsafe(NULL, inres->errMsg);
        free_nullsafe(NULL, inres->query);
        free_nullsafe(NULL, inres);
}

/**
 * Callback function used by sqlite3_exec - populates a global variable glob_results.
 * The glob_results structure is allocated with a new address on each sqlite_query call
 *
 * @param resultptr  Pointer to a dbresult struct where the query result will be saved
 * @param argc       Number of fields in the result
 * @param argv       Array pointer to the values of the fields
 * @param colName    Array pointer to the field names (column names)
 *
 * @return SQLite3 defines 0 for no errors, and 1 for errors.
 */
static int _cb_parse_result(void *resultptr, int argc, char **argv, char **colName)
{
        // This callback function is called for each row returned by the query
        _sqlite_header *hrec = NULL;
        _sqlite_tuples *trec = NULL, *new_frec = NULL;
        int i;
        dbresult *dbres = (dbresult *) resultptr;

        if( dbres == NULL ) {
                return 1;
        }

        // If no header records are found, populate this structure
        if( dbres->headerrec == NULL ) {
                for( i = 0; i < argc; i++ ) {
                        hrec = (_sqlite_header *) malloc_nullsafe(NULL, sizeof(_sqlite_header)+2);

                        hrec->fieldid = i;
                        hrec->name = strdup_nullsafe(colName[i]);
                        hrec->namelength = strlen_nullsafe(hrec->name);

                        // Append the new record to the "end" of the 
                        // circular pointer chain for header info
                        if( dbres->headerrec == NULL ) {
                                dbres->headerrec = hrec;
                                hrec->next = hrec;
                                hrec->prev = hrec;
                        } else {
                                hrec->next = dbres->headerrec;
                                hrec->prev = dbres->headerrec->prev;
                                dbres->headerrec->prev->next = hrec;
                                dbres->headerrec->prev = hrec;
                        }
                }
                dbres->num_fields = argc;
        }

        // Add all data fields for this record into a tuple structure
        hrec = dbres->headerrec;
        for( i = 0; i < argc; i++ ) {
                new_frec = (_sqlite_tuples *) malloc_nullsafe(NULL, sizeof(_sqlite_tuples)+2);
                
                // trec contains the head of a new record set with fields
                if( trec == NULL ) {
                        // Pointer to the first record in the tupleset
                        trec = new_frec;

                        // Add this header to the "end" of the 
                        // circular pointer chain for tuples
                        //
                        // These pointers directs you to the next or previous
                        // record set of data fields
                        if( dbres->tuples == NULL ) {
                                dbres->tuples = trec;
                                trec->nexttuple = trec;
                                trec->prevtuple = trec;
                        } else {
                                trec->nexttuple = dbres->tuples;
                                trec->prevtuple = dbres->tuples->prevtuple;
                                dbres->tuples->prevtuple->nexttuple = trec;
                                dbres->tuples->prevtuple = trec;
                        }
                }

                new_frec->tupleid = dbres->num_tuples;
                new_frec->fieldid = i;
                new_frec->value = strdup_nullsafe(argv[i]);
                new_frec->length = strlen_nullsafe(trec->value);

                new_frec->nexttuple = trec->nexttuple;
                new_frec->prevtuple = trec->prevtuple;

                // Add a pointer to the header structure as well, but only
                // if we are on the same ID with with header structure as 
                // in the data field structure
                if( hrec->fieldid == i ) {
                        new_frec->header = hrec;
                }

                if( new_frec->length > hrec->maxvaluelength ) {
                        hrec->maxvaluelength = new_frec->length;
                }
                
                // These pointers directs you to the next or previous
                // field in this record set
                if( trec->nextfield == NULL ) {
                        trec->nextfield = new_frec;
                        trec->prevfield = new_frec;
                } else {
                        new_frec->nextfield = trec;
                        new_frec->prevfield = trec->prevfield;
                        trec->prevfield->nextfield = new_frec;
                        trec->prevfield = new_frec;
                }

                // Get ready for the next field - find the next header record.
                hrec = hrec->next;
        }
        // Increase the tuple counter
        dbres->num_tuples++;

        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)
 *
 *  @param eurephiaCTX*  eurephia context pointer, where logging will be performed
 *  @param dbresult*     Pointer to the database result with the error message
 *
 */
void sqlite_log_error(eurephiaCTX *ctx, dbresult *dbres) {
        if( dbres == NULL ) {
                eurephia_log(ctx, LOG_CRITICAL, 2, "Unknown error (NULL result)");
                return;
        }

        if( dbres->status != dbSUCCESS ) {
                eurephia_log(ctx, LOG_ERROR, 4, "SQL Error: %s", dbres->errMsg);
        }
        DEBUG(ctx, 33, "SQL Query: %s", (dbres->query != NULL ? dbres->query : "(None)"));
}


/**
 *  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)
 *
 *  @param eurephiaCTX*  eurephia context pointer, where logging will be performed
 *  @param dbresult*     Pointer to the database result with the error message
 *
 *  @return An xmlNode pointer with more details of the error.
 */
#ifdef HAVE_LIBXML2
xmlNode *sqlite_log_error_xml(eurephiaCTX *ctx, dbresult *dbres ) {
        xmlNode *ret = NULL;

        sqlite_log_error(ctx, dbres);
        if( dbres == NULL ) {
                return NULL;
        }

        ret = xmlNewNode(NULL, (xmlChar *) "SQLError");
        if( ret != NULL ) {
                xmlNode *err_n = NULL;
                xmlChar *errstr = NULL;
                const xmlChar *ErrorSeverity_str[] = {
                        (xmlChar *) "Warning",
                        (xmlChar *) "Error",
                        (xmlChar *) "Critical",
                        (xmlChar *) "PANIC",
                        NULL
                };

                xmlNewProp(ret, (xmlChar *) "driver", (xmlChar *) "edb-sqlite.so");
                errstr = xmlCharStrdup(dbres->errMsg);
                err_n = xmlNewTextChild(ret, NULL, (xmlChar *) "ErrorMessage", errstr);
                xmlNewProp(err_n, (xmlChar *) "severity", ErrorSeverity_str[dbres->errSeverity]);
                free_nullsafe(NULL, errstr);
        }
        return ret;
}
#endif


/**
 * Main function for query a SQLite3 database.
 *
 * @param ctx eurephiaCTX
 * @param fmt The SQL query.  It supports stdarg to build up dynamic queries directly.
 *
 * @return Returns a pointer to a dbresult struct on success, otherwise NULL.
 */
dbresult *sqlite_query(eurephiaCTX *ctx, const char *fmt, ... ) {
        int rc;
        va_list ap;
        char *errMsg = NULL, *sql = NULL;
        eDBconn *dbc = ctx->dbc;
        dbresult *dbres = NULL;

        // prepare a new result set ...
        dbres = malloc_nullsafe(ctx, sizeof(dbresult)+2);
        dbres->status = dbEMPTY;
        dbres->num_tuples = 0;

        // prepare SQL query
        va_start(ap, fmt);
        sql = sqlite3_vmprintf(fmt, ap);
        va_end(ap);

        if( sql == NULL ) {
                _sqlite_set_error(dbres, sevPANIC, NULL, "Could not allocate memory for SQL query string");
                goto exit;
        }


        if( ctx->dbc == NULL ) {
                _sqlite_set_error(dbres, sevPANIC, sql, "No open database connection to perfom SQL query to");
                goto exit;
        }

        if( ctx->context_type == ECTX_NO_PRIVILEGES ) {
                _sqlite_set_error(dbres, sevCRITICAL, sql, "Database query attempted from wrong context");
                goto exit;
        }

        DEBUG(ctx, 25, "Doing SQL Query: %s", sql);
        rc = sqlite3_exec( (sqlite3 *) dbc->dbhandle, sql, _cb_parse_result, dbres, &errMsg );
        if( rc != SQLITE_OK ) {
                _sqlite_set_error(dbres, (dbres->num_tuples > 0 ? sevWARNING : sevERROR), sql, "%s", errMsg);
                sqlite3_free(errMsg); errMsg = NULL;
                goto exit;
        }

        if( strcasestr(sql, "INSERT INTO") != 0) {
                dbres->last_insert_id = sqlite3_last_insert_rowid((sqlite3 *) dbc->dbhandle);
        };
        if( strcasestr(sql, "SELECT ") == 0 ) {
                // If not a SELECT query, then get the number of affected records
                dbres->affected_rows = sqlite3_changes((sqlite3 *) dbc->dbhandle);
        }

        dbres->status = dbSUCCESS;
        dbres->srch_tuples = dbres->tuples;
        dbres->srch_headerrec = dbres->headerrec;
 exit:
        sqlite3_free(sql); sql = NULL;

        return dbres;
}

/**
 * Internal function.  Checks if the input string contains reserved words
 *
 * @param reserved_words Array containing the reserver words
 * @param val Value to check against the reserved words
 *
 * @return Returns 1 if a reserverd word is found, otherwise 0
 */
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( (val != NULL) && strcmp(val, reserved_words[i]) == 0) {
                        return 1;
                }
        }
        return 0;
}


/**
 * Internal function.  Creates a string with the value from a particular field in a eDBfieldMap and
 * formats it properly according to its field type.
 *
 * @param ptr Pointer to the field in the eDBfieldMap
 *
 * @return Returns a string to be used within an SQL query.
 */
static 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_SETNULL:
                val = sqlite3_mprintf("NULL");
                break;

        case ft_STRING_LOWER:
                val = sqlite3_mprintf("lower('%q')", ptr->value);
                break;

        case ft_BOOL:
        case ft_PASSWD:
        case ft_STRING:
        default:
                val = sqlite3_mprintf("'%q'", ptr->value);
                break;
        }
        return val;
}


/**
 * Internal function.  Builds up a part of an SQL query, depending on the buildType.  The values
 * are taken from a pointer to an eDBfieldMap keeping the values
 *
 * @param btyp Must be btWHERE, btINSERT or btUPDATE
 * @param map  Pointer to a eDBfieldMap chain containing the values
 *
 * @return Returns a string with parts of the SQL query containing the dynamic variables on success,
 *         otherwise NULL is returned.
 */
static char *_build_sqlpart(int btyp, eDBfieldMap *map) {
        eDBfieldMap *ptr = NULL;
        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:
                if( btyp == btWHERE ) {
                        fsep = " AND ";
                        append_str(buf, "(", 8192);
                } else {
                        fsep = ",";
                }
                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
                        // or if we're doing an update and the field_type is set to SETNULL (we will set NULL)
                        if( (ptr->value == NULL) && (btyp == btWHERE || ptr->field_type != ft_SETNULL) ) {
                                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);
                        if( (btyp == btWHERE) && (ptr->table_alias != NULL) ) {
                                append_str(buf, ptr->table_alias, 8192);
                                append_str(buf, ".", 8192);
                        }
                        if( (btyp == btWHERE) && ptr->field_type == ft_STRING_LOWER ) {
                                append_str(buf, "lower(", 8192);
                                append_str(buf, ptr->field_name, 8192);
                                append_str(buf, ")", 8192);
                        } else {
                                append_str(buf, ptr->field_name, 8192);
                        }

                        switch( ptr->filter_type ) {
                        case flt_LT:
                                append_str(buf, "<", 8192); break;
                        case flt_LTE:
                                append_str(buf, "<=", 8192); break;
                        case flt_GT:
                                append_str(buf, ">", 8192); break;
                        case flt_GTE:
                                append_str(buf, ">=", 8192); break;
                        case flt_NEQ:
                                append_str(buf, "!=", 8192); break;

                        case flt_EQ:
                        default:
                                append_str(buf, "=", 8192); break;
                        }
                        append_str(buf, val, 8192);
                        sqlite3_free(val);
                        first = 0;
                }
                if( btyp == btWHERE ) {
                        if( (strlen_nullsafe(buf) > 1) ) {
                                append_str(buf, ")", 8192);
                        } else {
                                *buf = '\0';
                        }
                }
                break;

        case btINSERT: // (field_name, field_name, field_name) VALUES ('val','val','val')
                memset(&fields, 0, 4094);
                memset(&vals, 0, 4094);

                first = 1;
                for( ptr = map; ptr != NULL; ptr = ptr->next ) {
                        char *val = NULL;

                        // If the value is NULL, ignore it
                        if( ptr->value == NULL ) {
                                continue;
                        }

                        // Put separator in front of values and field names
                        if( first == 0 ) {
                                append_str(fields, ",", 8192);
                                append_str(vals, ",", 8192);
                        }

                        // 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);
                        first = 0;
                }

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

        ret = strdup_nullsafe(buf);
        return ret;
}


/**
 * Queries an SQLite3 database by using an SQL stub plus values from a eDBfieldMap pointer chain
 *
 * @param ctx      eurephiaCTX
 * @param qType    Query type, must be SQL_SELECT, SQL_INSERT, SQL_UPDATE or SQL_DELETE
 * @param sqlstub  The generic part of the SQL query
 * @param valMap   Values to be used in INSERT or UPDATE queries.  May be NULL if not SQL_INSERT or SQL_UPDATE
 * @param whereMap Values to be used in the WHERE section of the SQL query
 * @param sortkeys Only for SQL_SELECT, list of field names to sort the result by.
 *
 * @return Returns a pointer to a dbresult struct on success, otherwise NULL.
 */
dbresult *sqlite_query_mapped(eurephiaCTX *ctx, SQLqueryType qType, const char *sqlstub,
                              eDBfieldMap *valMap, eDBfieldMap *whereMap, const char *sortkeys)
{
        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);
                        if( sortkeys == NULL ) {
                                res = sqlite_query(ctx, "%s %s %s",
                                                   sqlstub, (strlen_nullsafe(tmp1) > 0 ? "WHERE" : ""), tmp1);
                        } else {
                                res = sqlite_query(ctx, "%s %s %s ORDER BY %s",
                                                   sqlstub, (strlen_nullsafe(tmp1) > 0 ? "WHERE" : ""), tmp1,
                                                   sortkeys);
                        }
                        free_nullsafe(ctx, 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(ctx, tmp1);
                        free_nullsafe(ctx, tmp2);
                }
                break;

        case SQL_INSERT:
                tmp1 = _build_sqlpart(btINSERT, valMap);
                res = sqlite_query(ctx, "%s %s", sqlstub, tmp1);
                free_nullsafe(ctx, tmp1);
                break;
       }

        // Send the SQL query to the database and return the result
        return res;
}

/**
 * Very simple dbresult dumper.  Will list out all records to the given FILE destination.
 *
 * @param dmp pointer to a FILE stream
 * @param res pointe to the dbresults to be dumped
 */
void sqlite_dump_result(FILE *dmp, dbresult *res) {
        _sqlite_tuples *row = NULL, *field = NULL;

        if( (res == NULL) || (res->tuples == NULL) ) {
                fprintf(dmp, "(No records found)\n");
                return;
        }

        /*
        hdr = (char *) malloc( ((res->maxlen_colname + res->maxlen_colvalue - 6) / 2) + 2 );
        memset(hdr, 0, ((res->maxlen_colname + res->maxlen_colvalue - 6) / 2) + 2 );
        memset(hdr, '-', ((res->maxlen_colname + res->maxlen_colvalue - 6) / 2));
        */

        row = res->tuples;
        do {
                fprintf(dmp, "** Record %i\n", row->tupleid);
                field = row;
                do {
                        fprintf(dmp, "(%i) %s | %s\n", field->fieldid, field->header->name, field->value);
                        field = field->nextfield;
                } while ( field != row);
                row = row->nexttuple;
                fprintf(dmp, "-----------------------------------------------------\n");
        } while( row != res->tuples);
        fprintf(dmp, "-----------------------------------------------------\n(%i records found)\n",
                (int) res->num_tuples);
        // fprintf(dmp, "%s----------%s\n(%i records found)\n\n", hdr, hdr, res->num_tuples);
        // free(hdr);
}

/**
 * Retrieves a particular value out of a dbresult
 *
 * @param res Pointer to dbresult
 * @param row Row number (record number) to extract
 * @param col Column number (field number) to extract
 *
 * @return Returns a pointer to the value if found, otherwise NULL is returned.
 * @remark This function returns a pointer to the value insisde the dbresult struct.  If you want to use
 *         this value after freeing the dbresult, you must make sure you copy this value yourself.
 */
char *sqlite_get_value(dbresult *res, int row, int col) {
        _sqlite_tuples *ptr = res->srch_tuples;
        
        if( (ptr == NULL) || (row > res->num_tuples) || (col > res->num_fields) ) {
                return NULL;
        }

        do {
                if( ptr->tupleid == row ) {
                        do {
                                if( ptr->fieldid == col ) {
                                        res->srch_tuples = ptr;
                                        return ptr->value;
                                }
                                ptr = (DIRECTION(ptr->fieldid, col, res->num_fields) == DIR_R 
                                       ? ptr->nextfield : ptr->prevfield);
                        } while( ptr != res->srch_tuples ) ;
                }
                ptr = (DIRECTION(ptr->tupleid, row, res->num_tuples) == DIR_R ? ptr->nexttuple : ptr->prevtuple);
        } while( ptr != res->srch_tuples );
        return NULL;
}


#ifdef HAVE_LIBXML2
/**
 * Retrieves a particular value out of a dbresult and places it inside an xmlNode pointer.
 *
 * @param node    Pointer to the xmlNode
 * @param xmltyp  Must be XML_ATTR or XML_NODE.  If XML_ATTR it will be added as an attribute/properties
 *                to the given xmlNode.  If XML_NODE, it will be added as a child to the given xmlNode.
 * @param inname  String containing the name of the attribute or XML tag. (Depending on xmltyp)
 * @param res     Pointer to dbresult
 * @param row     Row number (record number) to extract
 * @param col     Column number (field number) to extract
 *
 * @return Returns a pointer to the xmlNode which got updated.  If xmltyp == XML_NODE it will point at
 *         the newly added node.  On failure NULL will be returned.
 */
xmlNodePtr sqlite_xml_value(xmlNodePtr node, xmlFieldType xmltyp, char *inname, dbresult *res, int row, int col) {
        xmlChar *name = NULL, *data = NULL;
        xmlNodePtr retnode = NULL;

        name = xmlCharStrdup(inname);
        assert( name != NULL );

        data = xmlCharStrdup(sqlite_get_value(res, row, col));
        if( xmlStrlen(data) > 0 ) {
                switch( xmltyp ) {
                case XML_ATTR:
                        xmlNewProp(node, name, data);
                        retnode = node;
                        break;

                case XML_NODE:
                        retnode = xmlNewChild(node, NULL, name, data);
                        break;

                default:
                        retnode = NULL;
                }
        }
        free_nullsafe(NULL, data);
        free_nullsafe(NULL, name);
        return retnode;
}
#endif


#ifdef SQLITE_DEBUG
/*
 * Just a simple test program ... to debug this sqlite wrapper
 *
 *  To compile it:
 * gcc -o sqlite sqlite.c -lsqlite3 -I ../ -I ../../common/ ../../common/eurephia_log.c ../../common/eurephia_nullsafe.c ../eurephiadb_mapping.c ../../common/eurephia_xml.c `pkg-config libxml-2.0 --cflags --libs` ../../common/passwd.c -DSQLITE_DEBUG ../../common/sha512.c ../../common/eurephia_values.c ../../common/randstr.c `pkg-config openssl --cflags --libs` -Wall -g -DHAVE_LIBXML2 -D_GNU_SOURCE
 */
int main(int argc, char **argv) {
        int rc;
        dbresult *res = NULL;
        eurephiaCTX *ctx;

        if( argc != 3 ) {
                fprintf(stderr, "Usage: %s <db file> <sql query>\n", argv[0]);
                return 1;
        }

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

        if( sqlite_init_functions(ctx) != dbSUCCESS ) {
                fprintf(stderr, "Failed to register functions\n");
                return 1;
        }

        res = sqlite_query(ctx, argv[2]);
        if( res != NULL ) {
                sqlite_dump_result(stdout, res);
                sqlite_free_results(res);
        }
        sqlite3_close(ctx->dbc->dbhandle);
        free(ctx->dbc);
        eurephia_log_close(ctx);
        free(ctx);
        return 0;
}
#endif