path: root/database/sqlite/sqlite.c

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

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <string.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 <eurephia_admin_struct.h>
#include <eurephiadb_mapping.h>

#define SQLITE_C
#include "./sqlite.h"

#ifdef MEMWATCH
#include <memwatch.h>
#endif

#define btWHERE  0x001
#define btINSERT 0x002
#define btUPDATE 0x004

dbresult *glob_results = NULL;

// Free up a dbresult structure
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(fld->prevfield->value);
                                        free_nullsafe(fld->prevfield);
                                }
                        } while( fld != tup );
                        tup = tup->nexttuple;
                        free_nullsafe(fld->value);
                        free_nullsafe(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(hdr->prev->name);
                                free_nullsafe(hdr->prev);
                        }
                } while( hdr != inres->headerrec );
                free_nullsafe(hdr->name);
                free_nullsafe(hdr);
        }
        free_nullsafe(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
static int _cb_parse_result(void *NotUsed, 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;

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

        // If no header records are found, populate this structure
        if( glob_results->headerrec == NULL ) {
                for( i = 0; i < argc; i++ ) {
                        hrec = (_sqlite_header *) malloc(sizeof(_sqlite_header)+2);
                        memset(hrec, 0, 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( glob_results->headerrec == NULL ) {
                                glob_results->headerrec = hrec;
                                hrec->next = hrec;
                                hrec->prev = hrec;
                        } else {
                                hrec->next = glob_results->headerrec;
                                hrec->prev = glob_results->headerrec->prev;
                                glob_results->headerrec->prev->next = hrec;
                                glob_results->headerrec->prev = hrec;
                        }
                }
                glob_results->num_fields = argc;
        }

        // Add all data fields for this record into a tuple structure
        hrec = glob_results->headerrec;
        for( i = 0; i < argc; i++ ) {
                new_frec = (_sqlite_tuples *) malloc(sizeof(_sqlite_tuples)+2);
                memset(new_frec, 0, 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( glob_results->tuples == NULL ) {
                                glob_results->tuples = trec;
                                trec->nexttuple = trec;
                                trec->prevtuple = trec;
                        } else {
                                trec->nexttuple = glob_results->tuples;
                                trec->prevtuple = glob_results->tuples->prevtuple;
                                glob_results->tuples->prevtuple->nexttuple = trec;
                                glob_results->tuples->prevtuple = trec;
                        }
                }

                new_frec->tupleid = glob_results->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
        glob_results->num_tuples++;

        return 0;
}


// A simpler way to fetch data from SQLite than to use sqlite3_exec with callback 
// each time you want to do a query
dbresult *sqlite_query(eurephiaCTX *ctx, char *fmt, ... ) { 
        int rc;
        va_list ap;
        char *errMsg = NULL, *sql = NULL;
        eDBconn *dbc = ctx->dbc;

        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
        glob_results = malloc(sizeof(dbresult)+2);
        memset(glob_results, 0, sizeof(dbresult)+2);
        glob_results->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, 0, &errMsg );
        if( rc != SQLITE_OK ) {
                eurephia_log(ctx, LOG_ERROR, 0, "SQL Error: %s", errMsg);
                sqlite3_free(sql); sql = NULL;
                free_nullsafe(glob_results);
                return NULL;
        }

        glob_results->srch_tuples = glob_results->tuples;
        glob_results->srch_headerrec = glob_results->headerrec;
        sqlite3_free(sql); sql = NULL;

        return glob_results;
}

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


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

#define append_str(dest, src, size) strncat(dest, src, (size - strlen_nullsafe(dest)))
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);
                        append_str(buf, "=", 8192);
                        append_str(buf, val, 8192);
                        sqlite3_free(val);
                        first = 0;
                }
                if( btyp == btWHERE ) {
                        append_str(buf, ")", 8192);
                }
                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;
}


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

// Simple line-by-line result dumper
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);
}

// Retrieve a specific value from a dbresult structure
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
xmlNodePtr sqlite_xml_value(xmlNodePtr node, xmlFieldType xmltyp, char *name, dbresult *res, int row, int col) {
        switch( xmltyp ) {
        case XML_ATTR:
                xmlNewProp(node, (xmlChar *)name, (xmlChar *) sqlite_get_value(res, row, col));
                return node;

        case XML_NODE:
                return xmlNewChild(node, NULL, (xmlChar *) name, (xmlChar *)sqlite_get_value(res, row, col));
        }
        return NULL;
}
#endif

// Retrieve number of tuples in current dbresult structure
int sqlite_get_numtuples(dbresult *res) {
        return (res != NULL ? res->num_tuples : 0);
}

#ifdef SQLITE_DEBUG
//  Just a simple test program ... to debug this sqlite wrapper
int main() {
        int rc;
        dbresult *res = NULL, *res2 = NULL;
        eurephiaCTX *ctx;

#ifdef MEMWATCH
        mwStatistics(3);
#endif

        ctx = malloc(sizeof(eurephiaCTX)+2);
        memset(ctx, 0, sizeof(eurephiaCTX)+2);

        ctx->dbc = malloc(sizeof(eDBconn)+2);
        memset(ctx->dbc, 0, sizeof(eDBconn)+2);
        
        rc = sqlite3_open("./vpnaccess", (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, "SELECT * FROM openvpn_access");
        res2 = sqlite_query(ctx, "SELECT CURRENT_TIMESTAMP");
        if( res != NULL ) {
                sqlite_dump_result(stdout, res);
                sqlite_dump_result(stdout, res2);

                fprintf(stderr, "--> %s - %s\n", sqlite_get_value(res, 3, 2), sqlite_get_value(res, 1, 4));
                fprintf(stderr, "--> %s - %s\n", sqlite_get_value(res2, 0, 0), sqlite_get_value(res2, 0, 4));

                sqlite_free_results(res);
                sqlite_free_results(res2);
        }
        sqlite3_close(ctx->dbc->dbhandle);
        free(ctx->dbc);
        free(ctx);
        return 0;
}
#endif