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

Revision 22043, 10.6 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 * sqlitedbclass.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 "sqlitedbclass.h"
27#include "gsdlunicode.h"
28#include "unitool.h"
29
30#ifdef __WIN32__
31// for Sleep
32#  include <windows.h>
33#else
34// for usleep
35#  include <unistd.h>
36#endif
37
38
39#define SQLITE_MAX_RETRIES 8
40
41sqlitedbclass::sqlitedbclass()
42{
43  sqlitefile = NULL;
44}
45
46
47sqlitedbclass::~sqlitedbclass()
48{
49  closedatabase();
50}
51
52
53// returns true if opened
54bool sqlitedbclass::opendatabase (const text_t &filename, int mode, int num_retrys,
55#ifdef __WIN32__
56                  bool need_filelock
57#else
58                  bool
59#endif
60                  )
61{
62  // Check if we've already got the database open
63  if (sqlitefile != NULL)
64  {
65    if (openfile == filename) return true;
66    else closedatabase();
67  }
68
69  char *filename_cstr = filename.getcstr();
70  sqlite3_open(filename_cstr, &sqlitefile);
71  delete[] filename_cstr;
72
73  if (sqlitefile == NULL)
74  {
75    (*logout) << "ERROR: sqlitedbclass::opendatabase() failed on: " << filename << "\n";
76    return false;
77  }
78
79  if ((mode == DB_WRITER || mode == DB_WRITER_CREATE) && !sqltableexists("data"))
80  {
81    sqlexec("CREATE TABLE data (key TEXT, value TEXT, PRIMARY KEY(key))");
82  }
83
84  openfile = filename;
85
86  return true;
87}
88
89
90void sqlitedbclass::closedatabase ()
91{
92  if (sqlitefile == NULL) return;
93
94  sqlite3_close(sqlitefile);
95  sqlitefile = NULL;
96  openfile.clear();
97}
98
99
100void sqlitedbclass::deletekey (const text_t &key)
101{
102  text_t sql_cmd = "DELETE FROM data WHERE key='" + sql_safe(key) + "'";
103  sqlexec(sql_cmd);
104}
105
106
107// returns array of document OIDs
108text_tarray sqlitedbclass::get_documents_with_metadata_value (const text_tarray &metadata_element_names,
109                                  const text_t &metadata_value,
110                                  const text_t &sort_by_metadata_element_name)
111{
112  text_tarray document_OIDs;
113
114  // Check at least one metadata element and a metadata value has been specified
115  if (metadata_element_names.empty() || metadata_value == "")
116  {
117    return document_OIDs;
118  }
119
120  // Get the entries in the "document_metadata" table where the element and value matches those specified
121  text_t sql_cmd = "SELECT DISTINCT docOID FROM document_metadata WHERE element IN ('" + sql_safe(metadata_element_names[0]) + "'";
122  for (int i = 1; i < metadata_element_names.size(); i++)
123  {
124    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
125  }
126  sql_cmd += ") AND value='" + sql_safe(metadata_value) + "'";
127
128  // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
129  if (sort_by_metadata_element_name != "")
130  {
131    sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata WHERE element='" + sql_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
132  }
133
134  // Perform the SQL request
135  vector<text_tmap> sql_results;
136  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
137  {
138    return document_OIDs;
139  }
140
141  // Iterate through the documents and add them to the array to be returned
142  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
143  while (sql_results_iterator != sql_results.end())
144  {
145    text_tmap sql_result = (*sql_results_iterator);
146    document_OIDs.push_back(sql_result["docOID"]);
147    sql_results_iterator++;
148  }
149
150  return document_OIDs;
151}
152
153
154// returns file extension string
155text_t sqlitedbclass::getfileextension ()
156{
157  return ".db";
158}
159
160
161// returns true on success
162bool sqlitedbclass::getkeydata (const text_t& key, text_t &data)
163{
164  text_t sql_cmd = "SELECT value FROM data WHERE key='" + sql_safe(key) + "'";
165  vector<text_tmap> sql_results;
166  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
167  {
168    return false;
169  }
170
171  text_tmap sql_result = sql_results[0];
172  data = sql_result["value"];
173  return true;
174}
175
176
177// returns array of keys
178text_tarray sqlitedbclass::getkeys ()
179{
180  text_tarray keys;
181
182  // Get all the entries in the "key" column of the table
183  text_t sql_cmd = "SELECT key FROM data";
184  vector<text_tmap> sql_results;
185  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
186  {
187    return keys;
188  }
189
190  // Iterate through the keys and add them to the array to be returned
191  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
192  while (sql_results_iterator != sql_results.end())
193  {
194    text_tmap sql_result = (*sql_results_iterator);
195    keys.push_back(sql_result["key"]);
196    sql_results_iterator++;
197  }
198
199  return keys;
200}
201
202
203// returns array of values
204text_tarray sqlitedbclass::get_metadata_values (const text_tarray &metadata_element_names,
205                        const text_t &metadata_value_filter,
206                        const text_t &metadata_value_grouping_expression)
207{
208  text_tarray metadata_values;
209
210  // Check at least one metadata element has been specified
211  if (metadata_element_names.empty())
212  {
213    return metadata_values;
214  }
215
216  // Get the raw "value" field unless a grouping expression was provided (in this case an edited value is returned)
217  text_t value_select_expression = "value";
218  if (metadata_value_grouping_expression != "")
219  {
220    value_select_expression = metadata_value_grouping_expression;
221  }
222
223  // Get the entries in the "document_metadata" table where the element matches that specified
224  text_t sql_cmd = "SELECT DISTINCT docOID," + value_select_expression + " FROM document_metadata WHERE element IN ('" + sql_safe(metadata_element_names[0]) + "'";
225  for (int i = 1; i < metadata_element_names.size(); i++)
226  {
227    sql_cmd += ",'" + sql_safe(metadata_element_names[i]) + "'";
228  }
229  sql_cmd += ")";
230
231  // Add value filter, if one has been defined
232  if (metadata_value_filter != "")
233  {
234    sql_cmd += " AND value GLOB '" + sql_safe(metadata_value_filter) + "'";
235  }
236
237  // Perform the SQL request
238  vector<text_tmap> sql_results;
239  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
240  {
241    return metadata_values;
242  }
243
244  // Iterate through the values and add them to the array to be returned
245  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
246  while (sql_results_iterator != sql_results.end())
247  {
248    text_tmap sql_result = (*sql_results_iterator);
249    metadata_values.push_back(sql_result[value_select_expression]);
250    sql_results_iterator++;
251  }
252
253  return metadata_values;
254}
255
256
257// returns true on success
258bool sqlitedbclass::setkeydata (const text_t &key, const text_t &data)
259{
260  // We need to do either an INSERT or UPDATE depending on whether the key already exists
261  if (!exists(key))
262  {
263    text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + sql_safe(key) + "', '" + sql_safe(data) + "')";
264    return sqlexec(sql_cmd);
265  }
266  else
267  {
268    text_t sql_cmd = "UPDATE data SET value='" + sql_safe(data) + "' WHERE key='" + sql_safe(key) + "'";
269    return sqlexec(sql_cmd);
270  }
271}
272
273
274// ----------------------------------------------------------------------------------------
275//   SQLITE-ONLY FUNCTIONS
276// ----------------------------------------------------------------------------------------
277
278// sleep for the given number of milliseconds
279void sleep(int m)
280{
281#ifdef __WIN32__
282  Sleep(m);
283#else
284  usleep(m);
285#endif
286}
287
288
289text_t sqlitedbclass::sql_safe (const text_t &value_arg)
290{
291  text_t value = value_arg;
292  value.replace("'", "''");
293  return value;
294}
295
296
297// sqlexec simply executes the given sql statement - it doesn't obtain a
298// result set - returns true if the sql statement was executed successfully
299bool sqlitedbclass::sqlexec(const text_t &sql_cmd)
300{
301  if (sqlitefile == NULL) return false;
302
303  char *sql_cmd_cstr = sql_cmd.getcstr();
304
305  int rv = 0;
306  int tries = 0;
307  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, NULL, NULL, NULL)) == SQLITE_BUSY)
308  {
309    sleep(1000);
310    tries++;
311    if (tries > SQLITE_MAX_RETRIES)
312    {
313      outconvertclass text_t2ascii;
314      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
315      break;
316    }
317  }
318
319  delete[] sql_cmd_cstr;
320
321  if (rv == SQLITE_OK) return true;
322
323  // sqlite3_exec failed - return false
324  outconvertclass text_t2ascii;
325  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << ", rv: " << rv << "\n";
326  return false;
327}
328
329
330// callback functions for sqlgetarray
331static int sqlgetarray_callback(void *res, int numcols, char **vals, char **columnnames)
332{
333  vector<text_tmap> *result = (vector<text_tmap>*) res;
334  text_tmap row;
335
336  for (int i = 0; i < numcols; i++)
337  {
338    row[columnnames[i]] = "";
339    // vals[i] will be NULL if set to a NULL db value
340    if (vals[i])
341    {
342      row[columnnames[i]] = to_uni(vals[i]);
343    }
344  }
345
346  result->push_back(row);
347  return 0;
348}
349
350
351// sqlgetarray executes sql and returns the result set in sql_results
352bool sqlitedbclass::sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
353{
354  if (sqlitefile == NULL) return false;
355
356  char *sql_cmd_cstr = sql_cmd.getcstr();
357  sql_results.erase(sql_results.begin(), sql_results.end());
358
359  int rv = 0;
360  int tries = 0;
361  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, sqlgetarray_callback, &sql_results, NULL)) == SQLITE_BUSY)
362  {
363    sleep(1000);
364    tries++;
365    if (tries > SQLITE_MAX_RETRIES)
366    {
367      outconvertclass text_t2ascii;
368      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
369      break;
370    }
371  }
372
373  delete[] sql_cmd_cstr;
374
375  if (rv == SQLITE_OK) return true;
376
377  // sqlite3_exec failed - return empty result set
378  outconvertclass text_t2ascii;
379  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
380  return false;
381}
382
383
384// returns true if exists
385bool sqlitedbclass::sqltableexists(const text_t &table_name)
386{
387  text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + sql_safe(table_name) + "'";
388  vector<text_tmap> sql_results;
389  if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
390  {
391    return false;
392  }
393
394  return true;
395}
Note: See TracBrowser for help on using the browser.