path: root/database/sqlite/sqlite.c

/* sqlite.c  --  Generic functions to simplify SQLite3 queries
 *
 *  GPLv2 only - Copyright (C) 2008, 2009
 *               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>

#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.  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);
        }
        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;
}


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

        if( ctx->dbc == NULL ) {
                eurephia_log(ctx, LOG_PANIC, 0, "No open database connection to perfom SQL query to");
                return NULL;
        }

        if( ctx->context_type == ECTX_NO_PRIVILEGES ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Database query attempted from wrong context");
                return NULL;
        }
        
        // prepare a new (global) result set ... 
        // do not delete the old ones, since we return this "global"
        // result as a new individual result
        dbres = malloc_nullsafe(ctx, sizeof(dbresult)+2);
        dbres->num_tuples = 0;
        
        // prepare SQL query
        va_start(ap, fmt);
        sql = sqlite3_vmprintf(fmt, ap);
        va_end(ap);

        DEBUG(ctx, 25, "Doing SQL Query: %s", sql);
        rc = sqlite3_exec( (sqlite3 *) dbc->dbhandle, sql, _cb_parse_result, dbres, &errMsg );
        if( rc != SQLITE_OK ) {
                eurephia_log(ctx, LOG_ERROR, 0, "SQL Error: %s", errMsg);
                sqlite3_free(sql); sql = NULL;
                free_nullsafe(ctx, dbres);
                return NULL;
        }

        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->srch_tuples = dbres->tuples;
        dbres->srch_headerrec = dbres->headerrec;
        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.
 */
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_PASSWD:
        case ft_STRING:
        default:
                val = sqlite3_mprintf("'%q'", ptr->value);
                break;
        }
        return val;
}


/**
 * Wrapper macro, which appends a string to a destination string without exceeding the size
 * of the destination buffer.
 *
 * @param dest Pointer to the destination buffer
 * @param src  Pointer to the value being concatenated to the destination string.
 * @param size Size of the destination buffer
 */
#define append_str(dest, src, size) strncat(dest, src, (size - strlen_nullsafe(dest)))


/**
 * 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.
 */
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( ptr->table_alias != NULL ) {
                                append_str(buf, ptr->table_alias, 8192);
                                append_str(buf, ".", 8192);
                        }
                        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 ) {
                fprintf(dmp, "(No records found)");
        }

        /*
        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


/**
 * Retrieve number of tuples in a given dbresult structure
 *
 * @param res Pointer to a dbresult
 *
 * @return Returns number of rows/number of tuples in the result.
 */
int sqlite_get_numtuples(dbresult *res) {
        return (res != NULL ? res->num_tuples : 0);
}


/**
 * Retrieve number of affected tuples in current dbresult structure.  This is only useful
 * when called on a result from INSERT, UPDATE or DELETE queries.
 *
 * @param res Pointer to a dbresult
 *
 * @return Returns number of rows/tuples affected by the SQL query
 */
int sqlite_get_affected_rows(dbresult *res) {
        return (res != NULL ? res->affected_rows : 0);
}

#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);
        
        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;
        

        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);
        free(ctx);
        return 0;
}
#endif