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

/*
 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 declaration *and* default implementation of a VTCROSS
 * case-based reasoner.
 *
 * This class can be used either as-is, or with re-implementations of some/all
 * of the functions.  All functions are declared virtual, and so may be
 * overridden in the cognitive engine source file as necessary. In addition,
 * internal class members are declared 'protected', so that they may be
 * inherited by child implementations.
 */


#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"


class CBR
{
    public:
        /* Constructors for the CBR class. */
        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();

        virtual int32_t OpenDatabase();

        virtual int32_t ExecuteCommand();

        virtual int32_t ExecuteSearchCommand(float *_retvals);

        virtual void Print();

        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, unsigned int _n, \
            float *_retvals);

        virtual int32_t Update(char *_where[], char *_set[], float *_wherevals, \
                float *_setvals, uint32_t _wherelen, uint32_t _setlen);

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


int32_t 
callback(void *notUsed, int argc, char **argv, char **azColName)
{
    int i;
    for(i=0; i<argc; i++){
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}


// open a database or create a database if it does not exist
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;
}


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


// execute search command
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;
}


// create database
CBR::CBR(char * _filename, char * _tablename, char * _cols[], unsigned int _len)
{
    // 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");
        if (i != numColumns-1) // not last entry
            strcat(command, ", ");
    }
    strcat(command, ");");

    // execute create table command
    ExecuteCommand();
}

// create database
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();
}



// free space
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();
}


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

//
// cbr search
//int cbr_search(cbr _cbr, char *_names[], int * _ops, float *_vals, unsigned int _n, float *_retvals);
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;
}

// cbr search for a sum
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;
}


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




// update a row 
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;
}

// cbr add a row
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