source: main/trunk/greenstone2/common-src/src/lib/sqlitedbclass.cpp@ 22837

Last change on this file since 22837 was 22837, checked in by davidb, 14 years ago

Need to get the sql_cmd in UTF-8 form to send through sqlite3_exec. Simply calling 'getcstr()' on the text_t string returns '?' for any char > 128

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