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

Revision 22043, 14.8 KB (checked in by davidb, 10 years ago)

Upgrading of database backends to allow support for sql-query support. Opportunity also taken to make calls to sql related classes (such as mssql and sqlite) to be more unified.

Line 
1/**********************************************************************
2 *
3 * mssqldbclass.cpp --
4 * Copyright (C) 2008  DL Consulting Ltd
5 *
6 * A component of the Greenstone digital library software
7 * from the New Zealand Digital Library Project at the
8 * University of Waikato, New Zealand.
9 *
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 2 of the License, or
13 * (at your option) any later version.
14 *
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18 * GNU General Public License for more details.
19 *
20 * You should have received a copy of the GNU General Public License
21 * along with this program; if not, write to the Free Software
22 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 *
24 *********************************************************************/
25
26#include "mssqldbclass.h"
27#include "gsdlunicode.h"
28#include "unitool.h"
29#include "cfgread.h"
30#include <stdio.h>
31
32#ifdef __WIN32__
33// for Sleep
34#  include <windows.h>
35#else
36// for usleep
37#  include <unistd.h>
38#endif
39
40
41#define MSSQL_MAX_RETRIES 8
42
43
44mssqldbclass::mssqldbclass ()
45{
46  dbptr = NULL;
47  tableid = "";
48  debug = true;
49}
50
51
52mssqldbclass::~mssqldbclass ()
53{
54  closedatabase();
55}
56
57
58// returns true if opened
59bool mssqldbclass::opendatabase (const text_t &filename, int mode, int num_retrys,
60#ifdef __WIN32__
61                                 bool need_filelock
62#else
63                                 bool
64#endif
65                                 )
66{
67  // Read the database information in from the .mssqldbinfo file
68  debug_output("MSSQL: Read in infodb file:[" + filename + "]\n");
69  text_tmap db_info_map = read_infodb_file(filename);
70  tableid = db_info_map["tableid"];
71 
72  // Initializing connection to MS-SQL
73  debug_output("MSSQL: Opening connection...\n");
74  CoInitialize(NULL);
75  char *db;
76  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;";
77  db = constr.getcstr();
78 
79  // Handle the error when create connection instance failed
80  if (FAILED(dbptr.CreateInstance(__uuidof(Connection))))
81  {
82    cerr << "mssqldbclass::opendatabase(): CreateInstance failed." << endl;
83    return false;
84  }
85 
86  // Try to open the database, if it failed, output the error.
87  // Note, the error description is not really helpful, but you can google the
88  // error number and you can always find something helpful
89  try
90  {
91    dbptr->Open(db, "", "", -1);
92  }
93  catch (_com_error &e)
94  {
95    text_t error;
96    error.setcstr(e.ErrorMessage());
97    cerr << "mssqldbclass::opendatabase(): Open failed (" << error << ")" << endl;
98  }
99  catch (...)
100  {
101    cerr << "mssqldbclass::opendatabase(): Open failed badly." << endl;
102  }
103
104  CoUninitialize();
105
106  return true;
107}
108
109
110void mssqldbclass::closedatabase ()
111{
112  // Close the database and catch any error if failed.
113  if (dbptr != NULL)
114  {
115    debug_output("MSSQL: Closing Database\n");
116    try
117    {
118      dbptr->Close();
119    }
120    catch (_com_error &e)
121    {
122      text_t error;
123      error.setcstr(e.ErrorMessage());
124      cerr << "mssqldbclass::closedatabase(): Close failed (" << error << ")" << endl;
125    }
126    catch (...)
127    {
128      cerr << "mssqldbclass::closedatabase(): Close failed badly." << endl;
129    }
130    delete dbptr;
131    dbptr = NULL;
132  }
133}
134
135
136// Return the file extension for the mssql's info file
137text_t mssqldbclass::getfileextension ()
138{
139  return ".mssqldbinfo";
140}
141
142
143void mssqldbclass::deletekey (const text_t &key)
144{
145  text_t sql_cmd = "DELETE FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
146  sqlexec(sql_cmd);
147}
148
149
150// returns true on success
151bool mssqldbclass::getkeydata (const text_t& key, text_t &data)
152{
153  text_t sql_cmd = "SELECT one_value FROM data_" + tableid + " WHERE one_key=N'" + sql_safe(key) + "'";
154  vector<text_tmap> sql_results;
155  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
156  {
157    return false;
158  }
159 
160  text_tmap sql_result = sql_results[0];
161  data = sql_result["one_value"];
162  return true;
163}
164
165
166// returns array of keys
167text_tarray mssqldbclass::getkeys ()
168{
169  text_tarray keys;
170 
171  // Get all the entries in the "key" column of the table
172  text_t sql_cmd = "SELECT one_key FROM data_" + tableid;
173  vector<text_tmap> sql_results;
174  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
175  {
176    return keys;
177  }
178 
179  // Iterate through the keys and add them to the array to be returned
180  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
181  while (sql_results_iterator != sql_results.end())
182  {
183    text_tmap sql_result = (*sql_results_iterator);
184    keys.push_back(sql_result["one_key"]);
185    sql_results_iterator++;
186  }
187 
188  return keys;
189}
190
191
192// returns true on success
193bool mssqldbclass::setkeydata (const text_t &key, const text_t &data)
194{
195  // We need to do either an INSERT or UPDATE depending on whether the key already exists
196  if (!exists(key))
197  {
198    text_t sql_cmd = "INSERT INTO data_" + tableid + " (one_key, one_value) VALUES (N'" + sql_safe(key) + "', N'" + sql_safe(data) + "')";
199    return sqlexec(sql_cmd);
200  }
201  else
202  {
203    text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + sql_safe(data) + "' WHERE one_key=N'" + sql_safe(key) + "'";
204    return sqlexec(sql_cmd);
205  }
206}
207
208
209// returns array of document OIDs
210text_tarray mssqldbclass::get_documents_with_metadata_value (const text_tarray &metadata_element_names,
211                                                             const text_t &metadata_value,
212                                                             const text_t &sort_by_metadata_element_name)
213{
214  text_tarray document_OIDs;
215 
216  // Check at least one metadata element and a metadata value has been specified
217  if (metadata_element_names.empty() || metadata_value == "")
218  {
219    return document_OIDs;
220  }
221 
222  // Get the entries in the "document_metadata" table where the element and value matches those specified
223  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
224  for (int i = 1; i < metadata_element_names.size(); i++)
225  {
226    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
227  }
228  sql_cmd += ") AND value='" + sql_safe(metadata_value) + "'";
229 
230  // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
231  if (sort_by_metadata_element_name != "")
232  {
233    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";
234  }
235 
236  // Perform the SQL request
237  vector<text_tmap> sql_results;
238  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
239  {
240    return document_OIDs;
241  }
242 
243  // Iterate through the documents and add them to the array to be returned
244  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
245  while (sql_results_iterator != sql_results.end())
246  {
247    text_tmap sql_result = (*sql_results_iterator);
248    document_OIDs.push_back(sql_result["docOID"]);
249    sql_results_iterator++;
250  }
251 
252  return document_OIDs;
253}
254
255
256// returns array of values
257text_tarray mssqldbclass::get_metadata_values (const text_tarray &metadata_element_names,
258                                               const text_t &metadata_value_filter,
259                                               const text_t &metadata_value_grouping_expression)
260{
261  text_tarray metadata_values;
262 
263  // Check at least one metadata element has been specified
264  if (metadata_element_names.empty())
265  {
266    return metadata_values;
267  }
268 
269  // Get the raw "value" field unless a grouping expression was provided (in this case an edited value is returned)
270  text_t value_select_expression = "value";
271  if (metadata_value_grouping_expression != "")
272  {
273    value_select_expression = metadata_value_grouping_expression;
274  }
275 
276  // Get the entries in the "document_metadata" table where the element matches that specified
277  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata_" + tableid + " WHERE element IN (N'" + sql_safe(metadata_element_names[0]) + "'";
278  for (int i = 1; i < metadata_element_names.size(); i++)
279  {
280    sql_cmd += ",N'" + sql_safe(metadata_element_names[i]) + "'";
281  }
282  sql_cmd += ")";
283   
284  // Add value filter, if one has been defined
285  if (metadata_value_filter != "")
286  {
287    sql_cmd += " AND value GLOB N'" + sql_safe(metadata_value_filter) + "'";
288  }
289 
290  // Perform the SQL request
291  vector<text_tmap> sql_results;
292  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
293  {
294    return metadata_values;
295  }
296   
297  // Iterate through the values and add them to the array to be returned
298  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
299  while (sql_results_iterator != sql_results.end())
300  {
301    text_tmap sql_result = (*sql_results_iterator);
302    metadata_values.push_back(sql_result[value_select_expression]);
303    sql_results_iterator++;
304  }
305 
306  return metadata_values;
307}
308
309
310// sqlexec(const text_t &sql)
311// Takes a sql statement and executes it
312// Returns false if failed, otherwise true
313bool mssqldbclass::sqlexec (const text_t &sql)
314{
315  char *sql_c = sql.getcstr();
316  bool rv = true;
317 
318  // Creating the command instance
319  _CommandPtr cmd;
320  if (FAILED(cmd.CreateInstance(__uuidof(Command))))
321  {
322    cerr << "mssqldbclass::sqlexec: CreateInstance failed" << endl;
323    return false;
324  }
325 
326  // Execute the sql statements and catch any error encountered
327  try
328  {
329    cmd->ActiveConnection = dbptr;
330    cmd->CommandText = sql_c;
331    cmd->Execute(NULL, NULL, adCmdText);
332  }
333  catch (_com_error &e)
334  {
335    text_t error;
336    error.setcstr(e.ErrorMessage());
337    cerr << "mssqldbclass::sqlexec: _com_error: (" << sql << ") : (" << error << ")" << endl;
338    rv = false;
339  }
340  catch (...)
341  {
342    cerr << "mssqldbclass::sqlexec: unknown error: (" + sql + ")" << endl;
343    rv = false;
344  }
345
346  delete [] sql_c;
347 
348  return rv;
349}
350
351
352// sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
353// Takes a sql statement and returns a vetor of result text_tmaps
354// Each map containing [field] => [value]. Note, all in text_t
355bool mssqldbclass::sqlgetarray (const text_t &sql_cmd,
356                                vector<text_tmap> &sql_results)
357{     
358  _RecordsetPtr rset;
359  bool result = false;
360  char *sql_c = sql_cmd.getcstr();
361 
362  // Create instance of the Recordset and die if failed
363  if (FAILED(rset.CreateInstance(__uuidof(Recordset))))
364  {
365    cerr << "mssqldbclass::sqlgetarray(): CreateInstance failed." << endl;
366    return false;
367  }
368 
369  // Do the query to the database - this can throw exceptions, hence
370  // the try...catch
371  try
372  {     
373    rset->Open(sql_c, dbptr.GetInterfacePtr(), adOpenStatic, adLockReadOnly, adCmdText);
374   
375    // There may have been no results found
376    if (rset->RecordCount < 1)
377    {
378      debug_output("mssqldbclass::sqlgetarray(): No records found!\n");
379      return true; // Still want to return true even if there is no result found
380    }
381 
382    // Retrieve the informaton and place it into the sql_result map vector
383    // It will be array[["key"=>"values"]]
384    while(!rset->EndOfFile)
385    {
386      text_tmap rowmap;       
387      _variant_t index;
388      index.vt = VT_I2;
389      for (int i = 0; i < rset->Fields->Count; i++)
390      {
391        index.iVal = i;
392        text_t v = "";
393        text_t n = (char *)_bstr_t(rset->Fields->GetItem(index)->Name);
394        if (rset->Fields->GetItem(index)->Value.vt != VT_NULL)
395        {
396          v = convert_bstr_to_textt(rset->Fields->GetItem(index)->Value);
397        }
398        rowmap[n] = v;
399      }
400      sql_results.push_back(rowmap);
401      rset->MoveNext();
402    }
403    result = true;
404  }
405  catch (_com_error &e)
406  {
407    text_t error;
408    error.setcstr(e.ErrorMessage());
409    cerr << "mssqldbclass::sqlgetarray(): Error " << error << endl;
410    result = false;
411  }
412  catch (...)
413  {
414    cerr << "mssqldbclass::sqlgetarray(): Unknown Error" << endl;
415    result = false;
416  }
417
418  delete [] sql_c;
419
420  return result; 
421}
422
423
424// returns true if exists
425bool sqlitedbclass::sqltableexists(const text_t &table_name)
426{
427  cerr << "**** mssqldbclass::sqltableexists: This implementation is untested!" << endl;
428  cerr << "**** Remove warning message once confirmed/fixed to works as required" << endl;
429
430  text_t sql_cmd = "SELECT name FROM sysobjects WHERE name='" + sql_safe(table_name)
431    + "' AND OBJECTPROPERTY(id,'IsUserTable')=1";
432
433  vector<text_tmap> sql_results;
434  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
435  {
436    return false;
437  }
438
439  return true;
440}
441
442
443text_t mssqldbclass::sql_safe (const text_t &value_arg)
444{
445  text_t value = value_arg;
446  value.replace("'", "''");
447  return value;
448}
449
450
451//---------------------------------------------------------------------------//
452// MS-SQL Private Utilities [START]
453//---------------------------------------------------------------------------//
454
455
456// read_infodb_file(text_t filename)
457// Unlike the others (sqlite and gdbm), this is a server-client based
458// database.  Therefore, instead of store the actual database, we store the
459// infoserver-client based database.
460// What we want to do here is to have the
461text_tmap mssqldbclass::read_infodb_file(text_t filename)
462{
463  text_tarray cfgline;
464  text_tmap dbinfo_hash;
465 
466  // Read in the infodb file
467  // It is the same format as all other config file
468  // Therefore let's reuse the confin code
469  char *infodb_cfgc = filename.getcstr();
470  ifstream confin(infodb_cfgc);   
471
472  // Read in the config and store it into the hash
473  if (confin)
474  {
475    while (read_cfg_line(confin, cfgline) >= 0)
476    {
477      if (cfgline.size() == 2)
478      {
479        dbinfo_hash[cfgline[0]] = cfgline[1];
480      }
481    }
482    confin.close();
483  }
484  delete []infodb_cfgc;
485  return dbinfo_hash;
486}
487
488
489void mssqldbclass::debug_output (text_t output_string)
490{
491  // Only ouput this when we want to see the debug message
492  if (debug)
493  {
494    cerr << output_string;
495  }
496}
497
498
499text_t mssqldbclass::convert_bstr_to_textt (_variant_t variant)
500{
501  text_t result = "";
502
503  // For some reasons Windows Server 2003 does not
504  // allow _bstr_t to be casted to unsigned short* directly
505  // Therefore we need to convert it to wchar_t * then convert
506  // to unsigned short*
507  _bstr_t in = _bstr_t(variant);
508  wchar_t* temp_conv = (wchar_t*)in;
509  unsigned short* ints = (unsigned short*)temp_conv;
510  for (int i = 0 ; i < in.length() ; i++)
511  {     
512    result.push_back(ints[i]);
513  }
514  return result;
515}
516
517//---------------------------------------------------------------------------//
518// MS-SQL Private Utilities [END]
519//---------------------------------------------------------------------------//
520
Note: See TracBrowser for help on using the browser.