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

Last change on this file since 17476 was 17476, checked in by mdewsnip, 13 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.

File size: 14.3 KB
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 repository browser.