sqlitedataset.cpp

Gambas is a graphical development environment based on a Basic interpreter, like Visual Basic. It us

/**********************************************************************
 * Copyright (c) 2002, Leo Seib, Hannover
 *
 * Project:SQLiteDataset C++ Dynamic Library
 * Module: SQLiteDataset class realisation file
 * Author: Leo Seib      E-Mail: [e-mail]
 * Begin: 5/04/2002
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 **********************************************************************/


#include <stdio.h>
#include <ctype.h>
#include <stdarg.h>
#include <time.h>
#include <iostream>
#include <string.h>
#include <math.h>

#include "sqlitedataset.h"
#include <unistd.h>



/**************************************************************/

//************* Callback function ***************************

int callback(void* res_ptr,int ncol, char** reslt,char** cols){

	/* NG: Type definition */
//   typedef vector<string> Tables;
   Tables tables;
   Tables::iterator it;

   char *item;
   char *table;

   result_set* r = (result_set*)res_ptr;//dynamic_cast<result_set*>(res_ptr); 
   int sz = r->records.size();

   //if (reslt == NULL ) cout << "EMPTY!!!\n";
   if (!r->record_header.size()){ /*IF*/ 
     for (int i=0; i < ncol; i++) {
         item = strchr(cols[i], (int)'.');
	 if (!item){ /* Field does not include table info */
             item = cols[i];
                 r->record_header[i].name = item; //NG
                 table = NULL;
                 r->record_header[i].field_table = "";
                 r->record_header[i].type = ft_String;//default type to string
	 }
	 else {
             //table = strndup(cols[i], strchr(cols[i], (int)'.') - cols[i]);
             GB.NewString(&table, cols[i], strchr(cols[i], (int)'.') - cols[i]);
//             table = strdup(reslt[0]);
             r->record_header[i].name = item + 1;
             r->record_header[i].field_table = table;
             r->record_header[i].type = ft_String;//default type to string
	 }
	 
      if (!table) {
	      /* Field does not contain table info,
	       * so let's default to string.  This
	       * has probably happened because aliases
	       * are being used */
      }
      else {
	      /* Check Table Name and add to list */
	      bool TableRegistered = false;
	      for ( it = tables.begin(); it != tables.end(); it++ ){
		      if (strcmp((*it).data(), table) == 0)
			      TableRegistered = true;
	      }
	      if ( TableRegistered == false ){
		      tables.push_back(table);
	      }
      }
      GB.FreeString(&table); //from strdup
     }

     SetFieldType( r, tables); // Set all the field types

     for (int i=0; i < ncol; i++) {
      /* Should table name be included in field name */
      if (tables.size() > 1){
         r->record_header[i].name = cols[i];
      }
      
/*      if (tables.size() < 1){
            r->record_header[i].type = ft_String;//Where type cannot be found
	                                         //default to string
      }*/
     }
   }/* IF close */ 

   sql_record rec;
   field_value v;
 
   if (reslt != NULL) {
     for (int i=0; i<ncol; i++){ 

       if (reslt[i] == NULL) {
	 v.set_asString("");
	 v.set_isNull();
       }
       else {
	       switch (r->record_header[i].type){
		       case ft_String:
	 		    v.set_asString(reslt[i]);
			    break; 
		       case ft_Boolean:
	 		    v.set_asString(reslt[i]);
			    if ( reslt[i][0] == 't' || reslt[i][0] == 'T')
				    v.set_asBool(1);
			    else
	 		            v.set_asBool(atoi(reslt[i]));
			    break; 
		       case ft_Char:
	 		    v.set_asString(reslt[i]);
	 		    v.set_asChar(reslt[i][0]);
			    break; 
		       case ft_Short:
	 		    v.set_asString(reslt[i]);
	 		    v.set_asShort(atoi(reslt[i]));
			    break; 
		       case ft_UShort:
	 		    v.set_asString(reslt[i]);
	 		    v.set_asUShort(atoi(reslt[i]));//Not sure if this will work
			    break; 
		       case ft_Long:
	 		    v.set_asString(reslt[i]);
	 		    v.set_asLong(strtol(reslt[i], NULL, 0));
			    break; 
		       case ft_ULong:
	 		    v.set_asString(reslt[i]);
	 		    v.set_asULong(strtol(reslt[i], NULL, 0));
			    break; 
		       case ft_Float:
		       case ft_Double:
	 		    v.set_asString(reslt[i]);
	 		    v.set_asDouble(strtod(reslt[i],NULL)); 
			    break; 
		       case ft_Date:
	 		    v.set_asDate(reslt[i]);
			    break; 
		       default:
	 		    v.set_asString(reslt[i]);
	       }
       }
     rec[i] = v;//(long)5;//reslt[i];
   //cout << "Field value: " << i << " " << v << "\n";
     }
   r->records[sz] = rec;
   }
   //printf("Fsz: [%i]\n",r->record_header.size());
   //printf("Recs: [%i]\n",r->records.size());
   //cout << " val |" <<reslt<<"|"<<cols<<"|"<<"\n\n";
  return 0;  
 }


//************* SqliteDatabase implementation ***************

SqliteDatabase::SqliteDatabase() {

  active = false;	
  _in_transaction = false;		// for transaction

  error = "Unknown database error";//S_NO_CONNECTION;
  host = "";
  port = "";
  db = ":memory:";
  login = "";
  passwd = "";
}

SqliteDatabase::~SqliteDatabase() {
  disconnect();
}


Dataset* SqliteDatabase::CreateDataset() const {
	return new SqliteDataset((SqliteDatabase*)this); 
}

int SqliteDatabase::status(void) {
  if (active == false) return DB_CONNECTION_NONE;
  return DB_CONNECTION_OK;
}

int SqliteDatabase::setErr(int err_code){

  switch (err_code) {
  case SQLITE_OK: error ="Successful result";
    break;
  case SQLITE_ERROR: error = "SQL error or missing database";
    break;
  case SQLITE_INTERNAL: error = "Internal logic error - Report this error on the mailing-list at sqlite.org";
    break;
  case SQLITE_PERM: error ="Access permission denied";
    break;
  case SQLITE_ABORT: error = "Callback routine requested an abort";
    break;
  case SQLITE_BUSY: error = "The database file is locked";
    break;
  case SQLITE_LOCKED: error = "A table in the database is locked";
    break;
  case SQLITE_NOMEM: error = "Out of memory";
    break;
  case SQLITE_READONLY: error = "Attempt to write a readonly database";
    break;
  case SQLITE_INTERRUPT: error = "Operation terminated by sqlite_interrupt()";
    break;
  case  SQLITE_IOERR: error = "Some kind of disk I/O error occurred";
    break;
  case  SQLITE_CORRUPT: error = "The database disk image is malformed";
    break;
  case SQLITE_NOTFOUND: error = "(Internal Only) Table or record not found";
    break;
  case SQLITE_FULL: error = "Insertion failed because database is full";
    break;
  case SQLITE_CANTOPEN: error = "Unable to open the database file";
    break;
  case SQLITE_PROTOCOL: error = "Database lock protocol error";
    break;
  case SQLITE_EMPTY:  error = "(Internal Only) Database table is empty";
    break;
  case SQLITE_SCHEMA: error = "The database schema changed";
    break;
  case SQLITE_TOOBIG: error = "Too much data for one row of a table";
    break;
  case SQLITE_CONSTRAINT: error = "Abort due to constraint violation";
    break;
  case SQLITE_MISMATCH:  error = "Data type mismatch";
    break;
  default : error = "Undefined SQLite error";
  }
  return err_code;
}

const char *SqliteDatabase::getErrorMsg() {
   return error.c_str();
}

int SqliteDatabase::connect() {
  disconnect();

  if ((conn = sqlite_open(db.c_str(),0,NULL))) {
    //cout << "Connected!\n";
    char* err=NULL;
    if (setErr(sqlite_exec(getHandle(),"PRAGMA empty_result_callbacks=ON",NULL,NULL,&err)) != SQLITE_OK) {
      GB.Error(getErrorMsg());
    }
    active = true;
    if (setErr(sqlite_exec(getHandle(),"PRAGMA full_column_names=ON",NULL,NULL,&err)) != SQLITE_OK) { //NG
      GB.Error(getErrorMsg()); //NG
    }
    return DB_CONNECTION_OK;
  }
  return DB_CONNECTION_NONE;
};

void SqliteDatabase::disconnect(void) {
  if (active == false) return;
  sqlite_close(conn);
  active = false;
};

int SqliteDatabase::create() {
  return connect();
};

int SqliteDatabase::drop() {
  if (active == false) return DB_ERROR;
  disconnect();
  if (!unlink(db.c_str())) 
     return DB_ERROR;
  return DB_COMMAND_OK;
};


long SqliteDatabase::nextid(const char* sname) {
  if (!active) return DB_UNEXPECTED_RESULT;
  int id;
  result_set res;
  char sqlcmd[512];
  sprintf(sqlcmd,"select nextid from %s where seq_name = '%s'",sequence_table.c_str(), sname);
  res.conn = getHandle(); //NG
  if ((last_err = sqlite_exec(getHandle(),sqlcmd,&callback,&res,NULL)) != SQLITE_OK) {
    return DB_UNEXPECTED_RESULT;
    }
  if (res.records.size() == 0) {
    id = 1;
    sprintf(sqlcmd,"insert into %s (nextid,seq_name) values (%d,'%s')",sequence_table.c_str(),id,sname);
    if ((last_err = sqlite_exec(conn,sqlcmd,NULL,NULL,NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
    return id;
  }
  else {
    id = res.records[0][0].get_asInteger()+1;
    sprintf(sqlcmd,"update %s set nextid=%d where seq_name = '%s'",sequence_table.c_str(),id,sname);
    if ((last_err = sqlite_exec(conn,sqlcmd,NULL,NULL,NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
    return id;    
  }
  return DB_UNEXPECTED_RESULT;
}


// methods for transactions
// ---------------------------------------------
void SqliteDatabase::start_transaction() {
  if (active) {
    sqlite_exec(conn,"begin",NULL,NULL,NULL);
    _in_transaction = true;
  }
}

void SqliteDatabase::commit_transaction() {
  if (active) {
    sqlite_exec(conn,"commit",NULL,NULL,NULL);
    _in_transaction = false;
  }
}

void SqliteDatabase::rollback_transaction() {
  if (active) {
    sqlite_exec(conn,"rollback",NULL,NULL,NULL);
    _in_transaction = false;
  }  
}



//************* SqliteDataset implementation ***************

SqliteDataset::SqliteDataset():Dataset() {
  haveError = false;
  db = NULL;
}


SqliteDataset::SqliteDataset(SqliteDatabase *newDb):Dataset(newDb) {
  haveError = false;
  db = newDb;
}

 SqliteDataset::~SqliteDataset(){
   if (errmsg) sqlite_free_table(&errmsg);
 }



//--------- protected functions implementation -----------------//

sqlite* SqliteDataset::handle(){
  if (db != NULL){
    return dynamic_cast<SqliteDatabase*>(db)->getHandle();
      }
  else return NULL;
}

void SqliteDataset::make_query(StringList &_sql) {
  string query;

 try {

  if (autocommit) db->start_transaction();


  if (db == NULL) GB.Error("No Database Connection");

  //close();


  for (list<string>::iterator i =_sql.begin(); i!=_sql.end(); i++) {
	query = *i;
	char* err=NULL; 
	Dataset::parse_sql(query);
	//cout << "Executing: "<<query<<"\n\n";
	if (db->setErr(sqlite_exec(this->handle(),query.c_str(),NULL,NULL,&err))!=SQLITE_OK) {