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 |
|
---|
44 | mssqldbclass::mssqldbclass ()
|
---|
45 | {
|
---|
46 | dbptr = NULL;
|
---|
47 | tableid = "";
|
---|
48 | debug = true;
|
---|
49 | }
|
---|
50 |
|
---|
51 |
|
---|
52 | mssqldbclass::~mssqldbclass ()
|
---|
53 | {
|
---|
54 | closedatabase();
|
---|
55 | }
|
---|
56 |
|
---|
57 |
|
---|
58 | // returns true if opened
|
---|
59 | bool 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 |
|
---|
110 | void 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
|
---|
137 | text_t mssqldbclass::getfileextension ()
|
---|
138 | {
|
---|
139 | return ".mssqldbinfo";
|
---|
140 | }
|
---|
141 |
|
---|
142 |
|
---|
143 | void 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
|
---|
151 | bool 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
|
---|
167 | text_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
|
---|
193 | bool 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
|
---|
210 | text_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
|
---|
257 | text_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
|
---|
317 | bool 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
|
---|
359 | bool 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 |
|
---|
428 | text_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
|
---|
440 | text_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 |
|
---|
468 | void 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 |
|
---|
478 | text_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 |
|
---|