source: main/trunk/greenstone2/common-src/src/lib/mssqldbclass.cpp@ 22043

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

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


#define MSSQL_MAX_RETRIES 8


mssqldbclass::mssqldbclass ()
{
  dbptr = NULL;
  tableid = "";
  debug = true;
}


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


// returns true if opened
bool mssqldbclass::opendatabase (const text_t &filename, int mode, int num_retrys, 
#ifdef __WIN32__
                                 bool need_filelock
#else
                                 bool
#endif
                                 )
{
  // Read the database information in from the .mssqldbinfo file
  debug_output("MSSQL: Read in infodb file:[" + filename + "]\n");
  text_tmap db_info_map = read_infodb_file(filename);
  tableid = db_info_map["tableid"];
  
  // Initializing connection to MS-SQL
  debug_output("MSSQL: Opening connection...\n");
  CoInitialize(NULL);
  char *db;
  text_t constr = "Provider=SQLNCLI;DataTypeCompatibility=80;Server=" + db_info_map["mss-host"] + ";User ID=" + db_info_map["username"] + ";Password=" + db_info_map["password"] + ";Initial Catalog=" + db_info_map["database"] + ";AutoTranslate=on;";
  db = constr.getcstr();
  
  // Handle the error when create connection instance failed
  if (FAILED(dbptr.CreateInstance(__uuidof(Connection)))) 
  {
    cerr << "mssqldbclass::opendatabase(): CreateInstance failed." << endl;
    return false;
  }
  
  // Try to open the database, if it failed, output the error.
  // Note, the error description is not really helpful, but you can google the
  // error number and you can always find something helpful
  try 
  {
    dbptr->Open(db, "", "", -1);
  }
  catch (_com_error &e)
  {
    text_t error;
    error.setcstr(e.ErrorMessage());
    cerr << "mssqldbclass::opendatabase(): Open failed (" << error << ")" << endl;
  }
  catch (...)
  {
    cerr << "mssqldbclass::opendatabase(): Open failed badly." << endl;
  }

  CoUninitialize();

  return true;
}


void mssqldbclass::closedatabase ()
{
  // Close the database and catch any error if failed.
  if (dbptr != NULL)
  {
    debug_output("MSSQL: Closing Database\n");
    try 
    {
      dbptr->Close();
    }
    catch (_com_error &e)
    {
      text_t error;
      error.setcstr(e.ErrorMessage());
      cerr << "mssqldbclass::closedatabase(): Close failed (" << error << ")" << endl;
    }
    catch (...)
    {
      cerr << "mssqldbclass::closedatabase(): Close failed badly." << endl;
    }
    delete dbptr;
    dbptr = NULL;
  }
}


// Return the file extension for the mssql's info file
text_t mssqldbclass::getfileextension ()
{
  return ".mssqldbinfo";
}


void mssqldbclass::deletekey (const text_t &key)
{
  text_t sql_cmd = "DELETE FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
  sqlexec(sql_cmd);
}


// returns true on success
bool mssqldbclass::getkeydata (const text_t& key, text_t &data)
{
  text_t sql_cmd = "SELECT one_value FROM data_" + tableid + " WHERE one_key=N'" + 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["one_value"];
  return true;
}


// returns array of keys
text_tarray mssqldbclass::getkeys ()
{
  text_tarray keys;
  
  // Get all the entries in the "key" column of the table
  text_t sql_cmd = "SELECT one_key FROM data_" + tableid;
  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["one_key"]);
    sql_results_iterator++;
  }
  
  return keys;
}


// returns true on success
bool mssqldbclass::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_" + tableid + " (one_key, one_value) VALUES (N'" + sql_safe(key) + "', N'" + sql_safe(data) + "')";
    return sqlexec(sql_cmd);
  }
  else
  {
    text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + sql_safe(data) + "' WHERE one_key=N'" + sql_safe(key) + "'";
    return sqlexec(sql_cmd);
  }
}


// returns array of document OIDs
text_tarray mssqldbclass::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_" + tableid + " WHERE element IN (N'" + 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_" + tableid + " WHERE element=N'" + 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 array of values
text_tarray mssqldbclass::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_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
  for (int i = 1; i < metadata_element_names.size(); i++)
  {
    sql_cmd += ",N'" + 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 N'" + 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;
}


// sqlexec(const text_t &sql)
// Takes a sql statement and executes it
// Returns false if failed, otherwise true
bool mssqldbclass::sqlexec (const text_t &sql)
{
  char *sql_c = sql.getcstr();
  bool rv = true;
  
  // Creating the command instance
  _CommandPtr cmd;
  if (FAILED(cmd.CreateInstance(__uuidof(Command))))
  {
    cerr << "mssqldbclass::sqlexec: CreateInstance failed" << endl;
    return false;
  }
  
  // Execute the sql statements and catch any error encountered
  try 
  {
    cmd->ActiveConnection = dbptr;
    cmd->CommandText = sql_c;
    cmd->Execute(NULL, NULL, adCmdText);
  }
  catch (_com_error &e)
  {
    text_t error;
    error.setcstr(e.ErrorMessage());
    cerr << "mssqldbclass::sqlexec: _com_error: (" << sql << ") : (" << error << ")" << endl;
    rv = false;
  }
  catch (...)
  {
    cerr << "mssqldbclass::sqlexec: unknown error: (" + sql + ")" << endl;
    rv = false;
  }

  delete [] sql_c;
  
  return rv;
}


// sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
// Takes a sql statement and returns a vetor of result text_tmaps
// Each map containing [field] => [value]. Note, all in text_t
bool mssqldbclass::sqlgetarray (const text_t &sql_cmd,
                                vector<text_tmap> &sql_results)
{     
  _RecordsetPtr rset;
  bool result = false;
  char *sql_c = sql_cmd.getcstr();
  
  // Create instance of the Recordset and die if failed
  if (FAILED(rset.CreateInstance(__uuidof(Recordset))))
  {
    cerr << "mssqldbclass::sqlgetarray(): CreateInstance failed." << endl;
    return false;
  }
  
  // Do the query to the database - this can throw exceptions, hence
  // the try...catch
  try
  {      
    rset->Open(sql_c, dbptr.GetInterfacePtr(), adOpenStatic, adLockReadOnly, adCmdText);
    
    // There may have been no results found 
    if (rset->RecordCount < 1)
    {
      debug_output("mssqldbclass::sqlgetarray(): No records found!\n");
      return true; // Still want to return true even if there is no result found
    }
  
    // Retrieve the informaton and place it into the sql_result map vector
    // It will be array[["key"=>"values"]]
    while(!rset->EndOfFile)
    {
      text_tmap rowmap;        
      _variant_t index;
      index.vt = VT_I2;
      for (int i = 0; i < rset->Fields->Count; i++)
      {
        index.iVal = i;
        text_t v = "";
        text_t n = (char *)_bstr_t(rset->Fields->GetItem(index)->Name);
        if (rset->Fields->GetItem(index)->Value.vt != VT_NULL)
        {
          v = convert_bstr_to_textt(rset->Fields->GetItem(index)->Value);
        }
        rowmap[n] = v;
      }
      sql_results.push_back(rowmap);
      rset->MoveNext();
    }
    result = true;
  }
  catch (_com_error &e)
  {
    text_t error;
    error.setcstr(e.ErrorMessage());
    cerr << "mssqldbclass::sqlgetarray(): Error " << error << endl;
    result = false;
  }
  catch (...)
  {
    cerr << "mssqldbclass::sqlgetarray(): Unknown Error" << endl;
    result = false;
  }

  delete [] sql_c;

  return result;  
}


// returns true if exists
bool sqlitedbclass::sqltableexists(const text_t &table_name)
{
  cerr << "**** mssqldbclass::sqltableexists: This implementation is untested!" << endl;
  cerr << "**** Remove warning message once confirmed/fixed to works as required" << endl;

  text_t sql_cmd = "SELECT name FROM sysobjects WHERE name='" + sql_safe(table_name) 
    + "' AND OBJECTPROPERTY(id,'IsUserTable')=1";

  vector<text_tmap> sql_results;
  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
  {
    return false;
  }

  return true;
}


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


//---------------------------------------------------------------------------//
// MS-SQL Private Utilities [START]
//---------------------------------------------------------------------------//


// read_infodb_file(text_t filename)
// Unlike the others (sqlite and gdbm), this is a server-client based
// database.  Therefore, instead of store the actual database, we store the
// infoserver-client based database.
// What we want to do here is to have the 
text_tmap mssqldbclass::read_infodb_file(text_t filename)
{
  text_tarray cfgline;
  text_tmap dbinfo_hash;
  
  // Read in the infodb file
  // It is the same format as all other config file
  // Therefore let's reuse the confin code
  char *infodb_cfgc = filename.getcstr();
  ifstream confin(infodb_cfgc);    

  // Read in the config and store it into the hash
  if (confin)
  {
    while (read_cfg_line(confin, cfgline) >= 0)
    {
      if (cfgline.size() == 2)
      {
        dbinfo_hash[cfgline[0]] = cfgline[1];
      }
    }
    confin.close();
  }
  delete []infodb_cfgc;
  return dbinfo_hash;
}


void mssqldbclass::debug_output (text_t output_string)
{
  // Only ouput this when we want to see the debug message
  if (debug)
  {
    cerr << output_string;
  }
}


text_t mssqldbclass::convert_bstr_to_textt (_variant_t variant)
{
  text_t result = "";

  // For some reasons Windows Server 2003 does not 
  // allow _bstr_t to be casted to unsigned short* directly
  // Therefore we need to convert it to wchar_t * then convert
  // to unsigned short*
  _bstr_t in = _bstr_t(variant);
  wchar_t* temp_conv = (wchar_t*)in;
  unsigned short* ints = (unsigned short*)temp_conv;
  for (int i = 0 ; i < in.length() ; i++)
  {     
    result.push_back(ints[i]);
  }
  return result;
}

//---------------------------------------------------------------------------//
// MS-SQL Private Utilities [END]
//---------------------------------------------------------------------------//