root/vtcross/trunk/src/include/vtcross/cbr.h @ 471

/*
 Copyright 2009 Virginia Polytechnic Institute and State University  

 Licensed under the Apache License, Version 2.0 (the "License"); 
 you may not use this file except in compliance with the License. 
 You may obtain a copy of the License at 
 
 http://www.apache.org/licenses/LICENSE-2.0 

 Unless required by applicable law or agreed to in writing, software 
 distributed under the License is distributed on an "AS IS" BASIS, 
 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
 See the License for the specific language governing permissions and 
 limitations under the License. 
*/

/* This file contains the full implementation of the CBR class - the default
 * VTCROSS case-based reasoner.
 */



#ifndef CBR_H
#define CBR_H

#include <cstdlib>
#include <cstring>
#include <cstdio>
#include <stdint.h>
#include <string>

#include <sqlite3.h>

#include "vtcross/common.h"
#include "vtcross/debug.h"
#include "vtcross/error.h"

using namespace std;


#define DATABASENAME "vtcross_cbr"


/* This is an internal debugging function used by some sqlite3 function calls.
 * It is not used otherwise in the VTCROSS codebase. */
int32_t 
callback(void *notUsed, int argc, char **argv, char **azColName)
{
    for(size_t i = 0; i < argc; i++) {
        LOG("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    LOG("\n");

    return 0;
}


/* The CBR class is designed to used as either as-is, or as a parent class.  All
 * functions are declared virtual, and internal members are 'protected' rather
 * than private. If you require functionality in a CBR not specifically provided
 * by this class, include this header in your source file, create a new class
 * that derives from CBR, and implement your desired functionality over the
 * original virtual functions as necessary.
 */
class CBR
{
    public:
        /* Constructors for the CBR class. Note that the default constructor
         * must be defined inline here so that super-calls from child classes
         * don't fail (i.e. we cannot rely on the compiler-provided constructor. */
        CBR(){};
        CBR(char *_filename, char *_tablename, char *_cols[], uint32_t _len);
        CBR(char *_filename, char *_tablename, char *_cols[], \
                char *_primcols[], uint32_t _len, uint32_t _primlen);

        /* Destructor for the CBR class. Note that this destructor will be
         * called automatically by any derived classes, and so child classes
         * should not repeat the freeing actions performed in this function. */
        virtual ~CBR();

        /* This function opens the VTCROSS database, or if it has not been
         * created yet, creates it. */
        virtual int32_t OpenDatabase();

        /* Execute a sqlite3 command and return the sqlite3 return code. */
        virtual int32_t ExecuteCommand();

        /* Execute a sqlite3 search command and store the results in the passed
         * retvals argument. */
        virtual int32_t ExecuteSearchCommand(float *_retvals);

        /* Print the VTCROSS sqlite3 database. */
        virtual void Print();

        /* Search the VTCROSS database for specific fields and store the results
         * in the passed retvals argument. */
        virtual int32_t Search(char *_names[], int32_t *_ops, float *_vals, \
                uint32_t _n, float *_retvals);
        virtual int32_t SearchSum(char *_name, float *_retvals);
        virtual int32_t SearchRand(char *_names[], int *_ops, float *_vals, uint32_t _n, \
            float *_retvals);

        /* Update a row in the VTCROSS sqlite3 database. */
        virtual int32_t Update(char *_where[], char *_set[], float *_wherevals, \
                float *_setvals, uint32_t _wherelen, uint32_t _setlen);

        /* Add a row to the VTCROSS sqlite3 database. */
        virtual int32_t AddRow(char *_cols[], float *_vals, uint32_t _len);

    protected:
        char filename[64];
        char tablename[64];
        char command[2048];
        sqlite3 *db; 
        uint32_t numColumns;
};


CBR::CBR(char *_filename, char *_tablename, char *_cols[], uint32_t _len)
{
    // copy filename
    unsigned int i=0;
    strcpy(filename, _filename);

    // execute create database command
    // database handle
    //db = NULL;
    OpenDatabase();

    // create table

    // copy tablename
    strcpy(tablename, _tablename);

    // number of columns in the table
    numColumns = _len;

    // generate command
    strcpy(command, "CREATE TABLE ");
    strcat(command, tablename);
    strcat(command, "(");
    for (i=0; i<numColumns; i++) {
        strcat(command, _cols[i]);
        strcat(command, " FLOAT");
        if (i != numColumns-1) // not last entry
            strcat(command, ", ");
    }
    strcat(command, ");");

    // execute create table command
    ExecuteCommand();
}

CBR::CBR(char * _filename, char * _tablename, 
        char * _cols[], char * _primcols[], unsigned int _len,
        unsigned int _primlen)
{
    // create database

    // copy filename
    unsigned int i=0;
    strcpy(filename, _filename);

    // execute create database command
    // database handle
    //db = NULL;
    OpenDatabase();

    // create table

    // copy tablename
    strcpy(tablename, _tablename);

    // number of columns in the table
    numColumns = _len;

    // generate command
    strcpy(command, "CREATE TABLE ");
    strcat(command, tablename);
    strcat(command, "(");
    for (i=0; i<numColumns; i++) {
        strcat(command, _cols[i]);
        strcat(command, " FLOAT");
        strcat(command, ", ");
    }
   strcat(command, "PRIMARY KEY (");
    for (i=0; i<_primlen; i++) {
        strcat(command, _primcols[i]);
        if (i != _primlen-1) // not last entry
            strcat(command, ", ");
    }
    strcat(command, "));");

    // execute create table command
    ExecuteCommand();
}



CBR::~CBR()
{  
    // generate command, remove a table with its content
    strcpy(command, "drop table ");
    strcat(command, tablename);

    // execute delete command
    ExecuteCommand();

    // clean the database
    strcpy(command, "vacuum");
    ExecuteCommand();
}




int32_t 
CBR::OpenDatabase()
{
    int32_t rc;

    rc = sqlite3_open(filename, &db);
    if(rc) {
        WARNING("Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }

    return rc;
}


int32_t
CBR::ExecuteCommand()
{
    int rc;
    char *zErrMsg = 0;

    rc = sqlite3_exec(db, command, callback, 0, &zErrMsg);
    if( rc!=SQLITE_OK){
        fprintf(stderr, "SQL error: %s: %s\n", zErrMsg, command);
        sqlite3_free(zErrMsg);
    }

    return rc;
}


int32_t 
CBR::ExecuteSearchCommand(float *_retvals)
{
    int rc;
    unsigned int i;

    sqlite3_stmt * pStatement;
    rc = sqlite3_prepare_v2(db, command, -1, &pStatement, NULL);
    if (rc == SQLITE_OK){ 
        if (sqlite3_step(pStatement) == SQLITE_ROW){
            for (i=0; i<numColumns; ++i)
                _retvals[i] = sqlite3_column_double(pStatement, i);
        } else {
                    printf("CBR:: No matched results returning default.\n");
                        rc=31337;
                }
    } else {
                printf("CBR:: Error executing SQL statement. rc = %i\n%s\n",rc,command);
    }

    sqlite3_finalize(pStatement);
   
    return rc;
}
void 
CBR::Print()
{
    // generate commandi
    strcpy(command, "select ");
    strcat(command, tablename);
    strcat(command, ".* from ");
    strcat(command, tablename);
    strcat(command, ";");

    // execute print (select all)  command
    ExecuteCommand();
    printf("database %s, table %s:\n", filename, tablename);
}

int32_t 
CBR::Search(
    char *_names[],
    int * _ops,
    float *_vals,
    unsigned int _n,
    float *_retvals)
{   
    int rc;
        const char *ops_str[] = {"==", "!=", ">", ">=", "<", "<="};
    // generate command
    strcpy(command, "select ");
    strcat(command, tablename);
    strcat(command, ".* from ");
    strcat(command, tablename);
    strcat(command, " where ");

    unsigned int i;
    char str_buffer[64];
    printf("number of ops %d:\n", _n);
    for (i=0; i<_n; i++) {
        // ensure valid ops value
        if (_ops[i] < 0 || _ops[i] > 5) {
            printf("error: cbr_search(), invalid ops id : %d\n", _ops[i]);
            exit(1);
        }

        strcat(command, _names[i]);
        strcat(command, ops_str[_ops[i]]);
    printf("search command: %s\n", command);
        sprintf(str_buffer, "%E", _vals[i]);
        strcat(command, str_buffer);


        if (i<_n-1)
            strcat(command, " AND ");
        else
            strcat(command, " order by utility desc;");
    }


    rc = ExecuteSearchCommand(_retvals);
    
    return rc;
}

int32_t 
CBR::SearchSum(
    char *_name,
    float *_retvals)
{   
    int rc;
    // generate command
    strcpy(command, "select SUM( ");
    strcat(command, tablename);
    strcat(command, ".");
    strcat(command, _name);
    strcat(command, ") from ");
    strcat(command, tablename);
    strcat(command, ";");

    //printf("search command: %s\n", command);

    rc = ExecuteSearchCommand(_retvals);
    /*printf("search result: ");
    for (int i=0; i<numColumns; i++)
        printf("%f, ",_retvals[i]);
    printf("\n");
    */
    return rc;
}


int32_t 
CBR::SearchRand(
    char *_names[],
    int * _ops,
    float *_vals,
    unsigned int _n,
    float *_retvals)
{   
        const char *ops_str[] = {"==", "!=", ">", ">=", "<", "<="};
    int rc;
    // generate command
    strcpy(command, "select ");
    strcat(command, tablename);
    strcat(command, ".* from ");
    strcat(command, tablename);
    strcat(command, " where ");

    unsigned int i;
    char str_buffer[64];
    for (i=0; i<_n; i++) {
        // ensure valid ops value
        if (_ops[i] < 0 || _ops[i] > 5) {
            printf("error: cbr_search(), invalid ops id : %d\n", _ops[i]);
            exit(1);
        }

        strcat(command, _names[i]);
        strcat(command, ops_str[_ops[i]]);
        sprintf(str_buffer, "%E", _vals[i]);
        strcat(command, str_buffer);

        if (i<_n-1)
            strcat(command, " AND ");
        else
            strcat(command, " order by RAND();");
    }

    //printf("search command: %s\n", command);

    rc = ExecuteSearchCommand(_retvals);
    
    /*printf("search result: ");
    for (i=0; i<numColumns; i++)
        printf("%f, ",_retvals[i]);
    printf("\n");
*/
    return rc;
}




int32_t 
CBR::Update(char *_where[], char*_set[], float *_wherevals, float *_setvals, 
                unsigned int _wherelen, unsigned int _setlen)
{
    unsigned int i;
    
    // generate command
    strcpy(command, "UPDATE ");
    strcat(command, tablename);
    
    strcat(command, " SET ");
    for (i=0; i<_setlen; i++) {
        strcat(command, _set[i]);
        strcat(command, " = ");
        sprintf(command, "%s%f", command, _setvals[i]);
        strcat(command, "  ");
        if (i != _setlen-1) // not last entry
            strcat(command, ", ");
    }
    strcat(command, " WHERE ");

    for (i=0; i<_wherelen; i++) {
        strcat(command, _where[i]);
        strcat(command, " = ");
        sprintf(command, "%s%f", command, _wherevals[i]);
        strcat(command, "  ");
        if (i != _wherelen-1) // not last entry
            strcat(command, "AND ");
    }
    strcat(command, ";");
    
    // execute add command
    ExecuteCommand();

    return 0;
}

int32_t 
CBR::AddRow(char *_cols[], float *_vals, unsigned int _len)
{
    unsigned int i;
    
    // generate command
    strcpy(command, "insert into ");
    strcat(command, tablename);
    
    strcat(command, " (");
    for (i=0; i<_len; i++) {
        strcat(command, _cols[i]);
        if (i != numColumns-1) // not last entry
            strcat(command, ", ");
    }
    strcat(command, ") ");

    strcat(command, " values(");
    for (i=0; i<_len; i++) {
        // ???? how to fill the values if numColumns != _len
        // assume = in the following
        sprintf(command, "%s%f", command, _vals[i]);
        if (i != numColumns-1) // not last entry
            strcat(command, ", ");
    }
    strcat(command, ");");
    
    // execute add command
    ExecuteCommand();

    return 0;
}

#endif