root/vtcross/trunk/src/cbr.c @ 25

//
// Case-based reasoner
//

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

#include "cbr.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\n", zErrMsg);
        sqlite3_free(zErrMsg);
    } else{
        printf("command executed.\n");
    }
    return rc;
}


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

    printf("command: %s\n", _cbr->command);
    sqlite3_stmt * pStatement;
    rc = sqlite3_prepare_v2(_cbr->db, _cbr->command, -1, &pStatement, NULL);
    if (rc == SQLITE_OK && sqlite3_step(pStatement) == SQLITE_ROW){
        for (i=0; i<_cbr->num_columns; ++i)
            _retvals[i] = sqlite3_column_double(pStatement, i);
    } else {
        printf("no row found\n");
        return 0;
    }
    sqlite3_finalize(pStatement);
    
    /*rc = sqlite3_exec(_cbr->db, _cbr->command, SearchCallback, 0, &zErrMsg);
    if( rc!=SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    } else{
        printf("command executed.\n");
    }*/

    return 1;
}


// 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)
{   
    // 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];
    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]]);
        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;");
    }

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

    return 0;
#endif
}


// 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, ");");
    
    // execute add command
    ExecuteCommand(_cbr);

    return 0;
}