source: gsdl/trunk/lib/sqlitedbclass.cpp@ 15642

/**********************************************************************
 *
 * sqlitedbclass.cpp -- 
 * Copyright (C) 2008  DL Consulting Ltd
 *
 * A component of the Greenstone digital library software
 * from the New Zealand Digital Library Project at the
 * University of Waikato, New Zealand.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *
 *********************************************************************/

#include "sqlitedbclass.h"
#include "unitool.h"


#define SQLITE_MAX_RETRIES 8


sqlitedbclass::~sqlitedbclass()
{
  closedatabase();
}


// returns true if opened
bool sqlitedbclass::opendatabase (const text_t &filename, int mode, int num_retrys, 
#ifdef __WIN32__
                  bool need_filelock
#else
                  bool
#endif
                  )
{
  // Check if we've already got the database open
  if (sqlitefile != NULL)
  {
    if (openfile == filename) return true;
    else closedatabase();
  }

  char *filename_cstr = filename.getcstr();
  sqlite3_open(filename_cstr, &sqlitefile);
  delete[] filename_cstr;

  if (sqlitefile == NULL)
  {
    (*logout) << "ERROR: sqlitedbclass::opendatabase() failed on: " << filename << "\n";
    return false;
  }

  if ((mode == DB_WRITER || mode == DB_WRITER_CREATE) && !sqltableexists("data"))
  {
    sqlexec("CREATE TABLE data (key TEXT, value TEXT, PRIMARY KEY(key))");
  }

  return true;
}


void sqlitedbclass::closedatabase ()
{
  if (sqlitefile == NULL) return;

  sqlite3_close(sqlitefile);
  sqlitefile = NULL;
  openfile.clear();
}


void sqlitedbclass::deletekey (const text_t &key)
{
  text_t sql_cmd = "DELETE FROM data WHERE key='" + key + "'";
  sqlexec(sql_cmd);
}


// returns true if exists
bool sqlitedbclass::exists (const text_t& key)
{
  text_t sql_cmd = "SELECT value FROM data WHERE key='" + key + "'";
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return false;
  }

  return true;
}


// returns true on success
bool sqlitedbclass::getinfo (const text_t& key, infodbclass &info)
{
  text_t sql_cmd = "SELECT value FROM data WHERE key='" + key + "'";
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return false;
  }

  text_tmap sql_result = sql_results[0];
  text_t sql_result_value = sql_result["value"];
  text_t::iterator sql_result_value_iterator = sql_result_value.begin();
  text_t ikey, ivalue;
  info.clear();
  while (getinfoline(sql_result_value_iterator, sql_result_value.end(), ikey, ivalue))
  {
    info.addinfo(ikey, ivalue);
  }

  return true;
}


// returns array of keys
text_tarray sqlitedbclass::getkeys ()
{
  text_tarray keys;

  // Get all the entries in the "key" column of the table
  text_t sql_cmd = "SELECT key FROM data";
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return keys;
  }

  // Iterate through the keys and add them to the array to be returned
  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
  while (sql_results_iterator != sql_results.end())
  {
    text_tmap sql_result = (*sql_results_iterator);
    keys.push_back(sql_result["key"]);
    sql_results_iterator++;
  }

  return keys;
}


// returns true on success
bool sqlitedbclass::setinfo (const text_t &key, const infodbclass &info)
{
  text_t subkey;
  text_t data;

  // get all the keys and values
  infodbclass::const_iterator info_here = info.begin();
  infodbclass::const_iterator info_end = info.end();
  while (info_here != info_end) {
    // add the key
    subkey.clear();
    subkey.push_back('<');
    text_t::const_iterator subkey_here = (*info_here).first.begin();
    text_t::const_iterator subkey_end = (*info_here).first.end();
    while (subkey_here != subkey_end) {
      if (*subkey_here == '>') {
    subkey.push_back('\\'); subkey.push_back('>');
      } else if (*subkey_here == '\n') {
    subkey.push_back('\\'); subkey.push_back('n');
      } else if (*subkey_here == '\r') {
    subkey.push_back('\\'); subkey.push_back('r');
      } else if (*subkey_here == '\\') {
    subkey.push_back('\\'); subkey.push_back('\\');
      } else {
    subkey.push_back (*subkey_here);
      }
      ++subkey_here;
    }
    subkey.push_back('>');

    // add the values
    text_tarray::const_iterator subvalue_here = (*info_here).second.begin();
    text_tarray::const_iterator subvalue_end = (*info_here).second.end();
    while (subvalue_here != subvalue_end) {
      data += subkey;
      
      text_t::const_iterator thissubvalue_here = (*subvalue_here).begin();
      text_t::const_iterator thissubvalue_end = (*subvalue_here).end();
      while (thissubvalue_here != thissubvalue_end) {
    if (*thissubvalue_here == '>') {
      data.push_back('\\'); data.push_back('>');
    } else if (*thissubvalue_here == '\n') {
      data.push_back('\\'); data.push_back('n');
    } else if (*thissubvalue_here == '\r') {
      data.push_back('\\'); data.push_back('r');
    } else if (*thissubvalue_here == '\\') {
      data.push_back('\\'); data.push_back('\\');
    } else {
      data.push_back (*thissubvalue_here);
    }
    
    ++thissubvalue_here;
      }
      
      data.push_back('\n');
      ++subvalue_here;
    }

    ++info_here;
  }

  // We need to do either an INSERT or UPDATE depending on whether the key already exists
  if (!exists(key))
  {
    text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + key + "', '" + data + "')";
    return sqlexec(sql_cmd);
  }
  else
  {
    text_t sql_cmd = "UPDATE data SET value='" + data + "' WHERE key='" + key + "'";
    return sqlexec(sql_cmd);
  }
}


// returns true on success
bool sqlitedbclass::getinfoline (text_t::iterator &here, text_t::iterator end, 
                 text_t &key, text_t &value)
{
  key.clear();
  value.clear();

  // ignore white space
  while (here != end && is_unicode_space (*here)) ++here;

  // get the '<'
  if (here == end || *here != '<') return false;
  ++here;
  
  // get the key
  while (here != end && *here != '>') {
    key.push_back(*here);
    ++here;
  }
  
  // get the '>'
  if (here == end || *here != '>') return false;
  ++here;
  
  // get the value
  while (here != end && *here != '\n') {
    if (*here == '\\') { 
      // found escape character
      ++here;
      if (here != end) {
    if (*here == 'n') value.push_back ('\n');
    else if (*here == 'r') value.push_back ('\r');
    else value.push_back(*here);
      }

    } else {
      // a normal character
      value.push_back(*here);
    }

    ++here;
  }

  return true;
}


// ----------------------------------------------------------------------------------------
//   CORE SQL FUNCTIONS
// ----------------------------------------------------------------------------------------

// sqlexec simply executes the given sql statement - it doesn't obtain a
// result set - returns true if the sql statement was executed successfully
bool sqlitedbclass::sqlexec(const text_t &sql_cmd)
{
  if (sqlitefile == NULL) return false;

  char *sql_cmd_cstr = sql_cmd.getcstr();

  int rv = 0;
  int tries = 0;
  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, NULL, NULL, NULL)) == SQLITE_BUSY)
  {
    sleep(1000);
    tries++;
    if (tries > SQLITE_MAX_RETRIES)
    {
      outconvertclass text_t2ascii;
      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
      break;
    }
  }

  delete[] sql_cmd_cstr;

  if (rv == SQLITE_OK) return true;

  // sqlite3_exec failed - return false
  outconvertclass text_t2ascii;
  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
  return false;
}


// callback functions for sqlgetarray
static int sqlgetarray_callback(void *res, int numcols, char **vals, char **columnnames)
{
  vector<text_tmap> *result = (vector<text_tmap>*) res;
  text_tmap row;

  for (int i = 0; i < numcols; i++)
  {
    row[columnnames[i]] = "";
    // vals[i] will be NULL if set to a NULL db value
    if (vals[i])
    {
      row[columnnames[i]] = vals[i];      
    }
  }

  result->push_back(row);
  return 0;
}


// sqlgetarray executes sql and returns the result set in sql_results
bool sqlitedbclass::sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
{
  if (sqlitefile == NULL) return false;

  char *sql_cmd_cstr = sql_cmd.getcstr();
  sql_results.erase(sql_results.begin(), sql_results.end());

  int rv = 0;
  int tries = 0;
  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, sqlgetarray_callback, &sql_results, NULL)) == SQLITE_BUSY)
  {
    sleep(1000);
    tries++;
    if (tries > SQLITE_MAX_RETRIES)
    {
      outconvertclass text_t2ascii;
      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
      break;
    }
  }

  delete[] sql_cmd_cstr;

  if (rv == SQLITE_OK) return true;

  // sqlite3_exec failed - return empty result set
  outconvertclass text_t2ascii;
  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
  return false;
}


// returns true if exists
bool sqlitedbclass::sqltableexists(const text_t &table_name)
{
  text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + table_name + "'";
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return false;
  }

  return true;
}


// sleep for the given number of milliseconds
void sleep(int m)
{
#ifdef __WIN32__
  Sleep(m);
#else
  usleep(m);
#endif
}