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 <sqlite3.h>

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

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

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


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

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

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

        return glob_results;
}

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

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