root/gsdl/trunk/common-src/src/lib/mssqldbclass.cpp @ 17476

Revision 17476, 14.3 KB (checked in by mdewsnip, 12 years ago)

Support for using MSSQL for infodb databases, many thanks to Jeffrey Ke from DL Consulting Ltd. ( http://www.dlconsulting.com). Please note that MSSQL only runs on Windows, and requires some setup before use. Documentation will be added to the Greenstone Wiki explaining this.

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'" + mssql_safe(key) + "'";
146  dbquery(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'" + mssql_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'" + mssql_safe(key) + "', N'" + mssql_safe(data) + "')";
199    return dbquery(sql_cmd);
200  }
201  else
202  {
203    text_t sql_cmd = "UPDATE data_" + tableid + " SET one_value='" + mssql_safe(data) + "' WHERE one_key=N'" + mssql_safe(key) + "'";
204    return dbquery(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'" + mssql_safe(metadata_element_names[0]) + "'";
224  for (int i = 1; i < metadata_element_names.size(); i++)
225  {
226    sql_cmd += ",'" + mssql_safe(metadata_element_names[i]) + "'";
227  }
228  sql_cmd += ") AND value='" + mssql_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'" + mssql_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'" + mssql_safe(metadata_element_names[0]) + "'";
278  for (int i = 1; i < metadata_element_names.size(); i++)
279  {
280    sql_cmd += ",N'" + mssql_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'" + mssql_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//-------------------------------------------------------------------------------------//
311// MS-SQL Private Utilities [START]
312//-------------------------------------------------------------------------------------//
313
314// dbquery(const text_t &sql)
315// Takes a sql statement and executes it
316// Returns false if failed, otherwise true
317bool mssqldbclass::dbquery (const text_t &sql)
318{
319  char *sql_c = sql.getcstr();
320  bool rv = true;
321 
322  // Creating the command instance
323  _CommandPtr cmd;
324  if (FAILED(cmd.CreateInstance(__uuidof(Command))))
325  {
326    cerr << "mssqldbclass::dbquery: CreateInstance failed" << endl;
327    return false;
328  }
329 
330  // Execute the sql statements and catch any error encountered
331  try
332  {
333    cmd->ActiveConnection = dbptr;
334    cmd->CommandText = sql_c;
335    cmd->Execute(NULL, NULL, adCmdText);
336  }
337  catch (_com_error &e)
338  {
339    text_t error;
340    error.setcstr(e.ErrorMessage());
341    cerr << "mssqldbclass::dbquery: _com_error: (" << sql << ") : (" << error << ")" << endl;
342    rv = false;
343  }
344  catch (...)
345  {
346    cerr << "mssqldbclass::dbquery: unknown error: (" + sql + ")" << endl;
347    rv = false;
348  }
349
350  delete [] sql_c;
351 
352  return rv;
353}
354
355
356// sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
357// Takes a sql statement and returns a vetor of result text_tmaps
358// Each map containing [field] => [value]. Note, all in text_t
359bool mssqldbclass::sqlgetarray (const text_t &sql_cmd,
360                                vector<text_tmap> &sql_results)
361{     
362  _RecordsetPtr rset;
363  bool result = false;
364  char *sql_c = sql_cmd.getcstr();
365 
366  // Create instance of the Recordset and die if failed
367  if (FAILED(rset.CreateInstance(__uuidof(Recordset))))
368  {
369    cerr << "mssqldbclass::sqlgetarray(): CreateInstance failed." << endl;
370    return false;
371  }
372 
373  // Do the query to the database - this can throw exceptions, hence
374  // the try...catch
375  try
376  {     
377    rset->Open(sql_c, dbptr.GetInterfacePtr(), adOpenStatic, adLockReadOnly, adCmdText);
378   
379    // There may have been no results found
380    if (rset->RecordCount < 1)
381    {
382      debug_output("mssqldbclass::sqlgetarray(): No records found!\n");
383      return true; // Still want to return true even if there is no result found
384    }
385 
386    // Retrieve the informaton and place it into the sql_result map vector
387    // It will be array[["key"=>"values"]]
388    while(!rset->EndOfFile)
389    {
390      text_tmap rowmap;       
391      _variant_t index;
392      index.vt = VT_I2;
393      for (int i = 0; i < rset->Fields->Count; i++)
394      {
395        index.iVal = i;
396        text_t v = "";
397        text_t n = (char *)_bstr_t(rset->Fields->GetItem(index)->Name);
398        if (rset->Fields->GetItem(index)->Value.vt != VT_NULL)
399        {
400          v = convert_bstr_to_textt(rset->Fields->GetItem(index)->Value);
401        }
402        rowmap[n] = v;
403      }
404      sql_results.push_back(rowmap);
405      rset->MoveNext();
406    }
407    result = true;
408  }
409  catch (_com_error &e)
410  {
411    text_t error;
412    error.setcstr(e.ErrorMessage());
413    cerr << "mssqldbclass::sqlgetarray(): Error " << error << endl;
414    result = false;
415  }
416  catch (...)
417  {
418    cerr << "mssqldbclass::sqlgetarray(): Unknown Error" << endl;
419    result = false;
420  }
421
422  delete [] sql_c;
423
424  return result; 
425}
426
427
428text_t mssqldbclass::mssql_safe (const text_t &value_arg)
429{
430  text_t value = value_arg;
431  value.replace("'", "''");
432  return value;
433}
434
435
436// read_infodb_file(text_t filename)
437// Unlike the others (sqlite and gdbm), this is a server-client based database.
438// Therefore, instead of store the actual database, we store the infoserver-client based database.
439// What we want to do here is to have the
440text_tmap mssqldbclass::read_infodb_file(text_t filename)
441{
442  text_tarray cfgline;
443  text_tmap dbinfo_hash;
444 
445  // Read in the infodb file
446  // It is the same format as all other config file
447  // Therefore let's reuse the confin code
448  char *infodb_cfgc = filename.getcstr();
449  ifstream confin(infodb_cfgc);   
450
451  // Read in the config and store it into the hash
452  if (confin)
453  {
454    while (read_cfg_line(confin, cfgline) >= 0)
455    {
456      if (cfgline.size() == 2)
457      {
458        dbinfo_hash[cfgline[0]] = cfgline[1];
459      }
460    }
461    confin.close();
462  }
463  delete []infodb_cfgc;
464  return dbinfo_hash;
465}
466
467
468void mssqldbclass::debug_output (text_t output_string)
469{
470  // Only ouput this when we want to see the debug message
471  if (debug)
472  {
473    cerr << output_string;
474  }
475}
476
477
478text_t mssqldbclass::convert_bstr_to_textt (_variant_t variant)
479{
480  text_t result = "";
481
482  // For some reasons Windows Server 2003 does not
483  // allow _bstr_t to be casted to unsigned short* directly
484  // Therefore we need to convert it to wchar_t * then convert
485  // to unsigned short*
486  _bstr_t in = _bstr_t(variant);
487  wchar_t* temp_conv = (wchar_t*)in;
488  unsigned short* ints = (unsigned short*)temp_conv;
489  for (int i = 0 ; i < in.length() ; i++)
490  {     
491    result.push_back(ints[i]);
492  }
493  return result;
494}
495
496//-------------------------------------------------------------------------------------//
497// MS-SQL Private Utilities [END]
498//-------------------------------------------------------------------------------------//
499
Note: See TracBrowser for help on using the browser.