root/vtcross/trunk/src/cognitive_engines/CBR_CE/cbr.c @ 411

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

#include <stdlib.h>
#include <string.h>
#include <stdio.h>

#include "vtcross/cbr.h"
#include "vtcross/common.h"


struct cbr_s {
    char filename[64];
    char tablename[64];
    char command[2048];
    sqlite3 *db; 
    unsigned int num_columns;
};


// open a database or create a database if it does not exist
int OpenDatabase(cbr _cbr){
    int rc;
    //sqlite3 **db;

    //printf("database name: %s\n", _cbr->filename);
    //rc = sqlite3_open(_cbr->filename, db);
    rc = sqlite3_open(_cbr->filename, &(_cbr->db));
    if (rc) {
        //fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(*db));
        fprintf(stderr, "can't open database: %s\n", sqlite3_errmsg(_cbr->db));
        sqlite3_close(_cbr->db);
        exit(1);
    } else{
        //printf("database opened.\n");
    }
    //_cbr->db = *db;
    return rc;
}


// simple callback function, display result
int 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;
}


// execute command
int ExecuteCommand(cbr _cbr){
    int rc;
    char *zErrMsg = 0;

    //printf("command: %s\n", _cbr->command);
    rc = sqlite3_exec(_cbr->db, _cbr->command, callback, 0, &zErrMsg);
    if( rc!=SQLITE_OK){
        fprintf(stderr, "SQL error: %s: %s\n", zErrMsg,_cbr->command);
        sqlite3_free(zErrMsg);
    } else{
        //printf("command executed.\n");
    }
    return rc;
}


// execute search command
int ExecuteSearchCommand(cbr _cbr, float *_retvals){
    int rc;
    unsigned int i;

    sqlite3_stmt * pStatement;
    rc = sqlite3_prepare_v2(_cbr->db, _cbr->command, -1, &pStatement, NULL);
    if (rc == SQLITE_OK){ 
        if (sqlite3_step(pStatement) == SQLITE_ROW){
            for (i=0; i<_cbr->num_columns; ++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,_cbr->command);
    }

    sqlite3_finalize(pStatement);
   
    return rc;
}


// create database
cbr cbr_create(char * _filename, char * _tablename, char * _cols[], unsigned int _len)
{
    // cbr is a pointer to struct cbr_s
    cbr _cbr = (cbr) malloc(sizeof(struct cbr_s));

    // create database

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

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

    // create table

    // copy tablename
    strcpy(_cbr->tablename, _tablename);

    // number of columns in the table
    _cbr->num_columns = _len;

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

    // execute create table command
    ExecuteCommand(_cbr);

    return _cbr;
}


// free space
void cbr_free(cbr _cbr)
{  
    // generate command, remove a table with its content
    strcpy(_cbr->command, "drop table ");
    strcat(_cbr->command, _cbr->tablename);

    // execute delete command
    ExecuteCommand(_cbr);

    // clean the database
    strcpy(_cbr->command, "vacuum");
    ExecuteCommand(_cbr);

    free(_cbr);
}


// print 
void cbr_print(cbr _cbr)
{
    // generate commandi
    strcpy(_cbr->command, "select ");
    strcat(_cbr->command, _cbr->tablename);
    strcat(_cbr->command, ".* from ");
    strcat(_cbr->command, _cbr->tablename);
    strcat(_cbr->command, ";");

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


/*//static
int cbr_callback(void *notUsed, int argc, char **argv, char **azColName)
{
    return 0;
}*/


const char * ops_str[] = {
    "==", "!=", ">", ">=", "<", "<="};


// cbr search
int cbr_search(
    cbr _cbr,
    char *_names[],
    int * _ops,
    float *_vals,
    unsigned int _n,
    float *_retvals)
{   
    int rc;
    // generate command
    strcpy(_cbr->command, "select ");
    strcat(_cbr->command, _cbr->tablename);
    strcat(_cbr->command, ".* from ");
    strcat(_cbr->command, _cbr->tablename);
    strcat(_cbr->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(_cbr->command, _names[i]);
        strcat(_cbr->command, ops_str[_ops[i]]);
    printf("search command: %s\n", _cbr->command);
        sprintf(str_buffer, "%E", _vals[i]);
        strcat(_cbr->command, str_buffer);


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

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

    //ExecuteCommand(_cbr);
    rc = ExecuteSearchCommand(_cbr, _retvals);
    
    /*printf("search result: ");
    for (i=0; i<_cbr->num_columns; i++)
        printf("%f, ",_retvals[i]);
    printf("\n");*/

    return rc;
}


// update a row 
int cbr_update(cbr _cbr, char *_where[], char*_set[], float *_wherevals, float *_setvals, 
                unsigned int _wherelen, unsigned int _setlen)
{
    unsigned int i;
    
    // generate command
    //printf("%s\n", _cbr->command);
    strcpy(_cbr->command, "UPDATE ");
    strcat(_cbr->command, _cbr->tablename);
    
    strcat(_cbr->command, " SET ");
    for (i=0; i<_setlen; i++) {
        strcat(_cbr->command, _set[i]);
        strcat(_cbr->command, " = ");
        sprintf(_cbr->command, "%s%f", _cbr->command, _setvals[i]);
        strcat(_cbr->command, "  ");
        if (i != _setlen-1) // not last entry
            strcat(_cbr->command, ", ");
    }
    strcat(_cbr->command, " WHERE ");

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

    return 0;
}

// cbr add a row
int cbr_add_row(cbr _cbr, char *_cols[], float *_vals, unsigned int _len)
{
    unsigned int i;
    
    // generate command
    //printf("%s\n", _cbr->command);
    strcpy(_cbr->command, "insert into ");
    strcat(_cbr->command, _cbr->tablename);
    
    strcat(_cbr->command, " (");
    for (i=0; i<_len; i++) {
        strcat(_cbr->command, _cols[i]);
        if (i != _cbr->num_columns-1) // not last entry
            strcat(_cbr->command, ", ");
    }
    strcat(_cbr->command, ") ");

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

    return 0;
}