source: main/trunk/greenstone2/common-src/src/lib/sqlitedbclass.cpp@ 24422

/**********************************************************************
 *
 * 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 "gsdlunicode.h"
#include "unitool.h"

#ifdef __WIN32__
// for Sleep
#  include <windows.h>
#else
// for usleep
#  include <unistd.h>
#endif


#define SQLITE_MAX_RETRIES 8

sqlitedbclass::sqlitedbclass(const text_t& gsdlhome) 
  : sqldbclass(gsdlhome)
{ 
  sqlitefile = NULL; 
}


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

  openfile = filename;

  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='" + sql_safe(key) + "'";
  sqlexec(sql_cmd);
}


// returns array of document OIDs
text_tarray sqlitedbclass::get_documents_with_metadata_value (const text_tarray &metadata_element_names,
                                  const text_t &metadata_value,
                                  const text_t &sort_by_metadata_element_name)
{
  text_tarray document_OIDs;

  // Check at least one metadata element and a metadata value has been specified
  if (metadata_element_names.empty() || metadata_value == "")
  {
    return document_OIDs;
  }

  // Get the entries in the "document_metadata" table where the element and value matches those specified
  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata WHERE element IN ('" + sql_safe(metadata_element_names[0]) + "'";
  for (int i = 1; i < metadata_element_names.size(); i++)
  {
    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
  }
  sql_cmd += ") AND value='" + sql_safe(metadata_value) + "'";

  // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
  if (sort_by_metadata_element_name != "")
  {
    sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata WHERE element='" + sql_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
  }

  // Perform the SQL request
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return document_OIDs;
  }

  // Iterate through the documents 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);
    document_OIDs.push_back(sql_result["docOID"]);
    sql_results_iterator++;
  }

  return document_OIDs;
}


// returns file extension string
text_t sqlitedbclass::getfileextension ()
{
  return ".db";
}


// returns true on success
bool sqlitedbclass::getkeydata (const text_t& key, text_t &data)
{
  text_t sql_cmd = "SELECT value FROM data WHERE key='" + sql_safe(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];
  data = sql_result["value"];
  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 array of values
text_tarray sqlitedbclass::get_metadata_values (const text_tarray &metadata_element_names,
                        const text_t &metadata_value_filter,
                        const text_t &metadata_value_grouping_expression)
{
  text_tarray metadata_values;

  // Check at least one metadata element has been specified
  if (metadata_element_names.empty())
  {
    return metadata_values;
  }

  // Get the raw "value" field unless a grouping expression was provided (in this case an edited value is returned)
  text_t value_select_expression = "value";
  if (metadata_value_grouping_expression != "")
  {
    value_select_expression = metadata_value_grouping_expression;
  }

  // Get the entries in the "document_metadata" table where the element matches that specified
  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata WHERE element IN ('" + sql_safe(metadata_element_names[0]) + "'";
  for (int i = 1; i < metadata_element_names.size(); i++)
  {
    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
  }
  sql_cmd += ")";

  // Add value filter, if one has been defined
  if (metadata_value_filter != "")
  {
    sql_cmd += " AND value GLOB '" + sql_safe(metadata_value_filter) + "'";
  }

  // Perform the SQL request
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return metadata_values;
  }

  // Iterate through the values 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);
    metadata_values.push_back(sql_result[value_select_expression]);
    sql_results_iterator++;
  }

  return metadata_values;
}


// returns true on success
bool sqlitedbclass::setkeydata (const text_t &key, const text_t &data)
{
  // 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 ('" + to_utf8(sql_safe(key)) + "', '" + to_utf8(sql_safe(data)) + "')";
    return sqlexec(sql_cmd);
  }
  else
  {
    text_t sql_cmd = "UPDATE data SET value='" + to_utf8(sql_safe(data)) + "' WHERE key='" + to_utf8(sql_safe(key)) + "'";
    return sqlexec(sql_cmd);
  }
}


// ----------------------------------------------------------------------------------------
//   SQLITE-ONLY FUNCTIONS
// ----------------------------------------------------------------------------------------

// sleep for the given number of milliseconds
void sleep(int m)
{
#ifdef __WIN32__
  Sleep(m);
#else
  usleep(m * 1000);  // usleep() actually takes microseconds, not milliseconds
#endif
}


text_t sqlitedbclass::sql_safe (const text_t &value_arg)
{
  text_t value = value_arg;
  value.replace("'", "''");
  return value;
}


// 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 = to_utf8(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 << ", rv: " << rv << "\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]] = to_uni(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 = to_utf8(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='" + sql_safe(table_name) + "'";
  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return false;
  }

  return true;
}