source: gsdl/trunk/lib/sqlitedbclass.cpp@ 15796

Last change on this file since 15796 was 15796, checked in by mdewsnip, 16 years ago

(Adding dynamic classifiers) Changed getmetadatavalues() to get_metadata_values(), for my sanity.

File size: 7.5 KB
RevLine 
[15599]1/**********************************************************************
2 *
[15640]3 * sqlitedbclass.cpp --
[15599]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
[15640]26#include "sqlitedbclass.h"
[15623]27#include "unitool.h"
[15599]28
[15783]29#ifdef __WIN32__
30// for Sleep
31# include <windows.h>
32#else
33// for usleep
34# include <unistd.h>
35#endif
[15599]36
[15783]37
[15601]38#define SQLITE_MAX_RETRIES 8
39
40
[15640]41sqlitedbclass::~sqlitedbclass()
[15599]42{
43 closedatabase();
44}
45
46
47// returns true if opened
[15642]48bool sqlitedbclass::opendatabase (const text_t &filename, int mode, int num_retrys,
[15599]49#ifdef __WIN32__
[15642]50 bool need_filelock
[15599]51#else
[15642]52 bool
[15599]53#endif
54 )
55{
56 // Check if we've already got the database open
57 if (sqlitefile != NULL)
58 {
59 if (openfile == filename) return true;
60 else closedatabase();
61 }
62
63 char *filename_cstr = filename.getcstr();
64 sqlite3_open(filename_cstr, &sqlitefile);
65 delete[] filename_cstr;
66
[15633]67 if (sqlitefile == NULL)
[15599]68 {
[15640]69 (*logout) << "ERROR: sqlitedbclass::opendatabase() failed on: " << filename << "\n";
[15633]70 return false;
[15599]71 }
72
[15637]73 if ((mode == DB_WRITER || mode == DB_WRITER_CREATE) && !sqltableexists("data"))
[15603]74 {
75 sqlexec("CREATE TABLE data (key TEXT, value TEXT, PRIMARY KEY(key))");
76 }
77
[15633]78 return true;
[15599]79}
80
81
[15642]82void sqlitedbclass::closedatabase ()
[15599]83{
84 if (sqlitefile == NULL) return;
85
86 sqlite3_close(sqlitefile);
87 sqlitefile = NULL;
88 openfile.clear();
89}
90
91
[15642]92void sqlitedbclass::deletekey (const text_t &key)
93{
94 text_t sql_cmd = "DELETE FROM data WHERE key='" + key + "'";
95 sqlexec(sql_cmd);
96}
97
98
[15679]99// returns file extension string
100text_t sqlitedbclass::getfileextension ()
101{
102 return ".db";
103}
104
105
[15599]106// returns true on success
[15643]107bool sqlitedbclass::getkeydata (const text_t& key, text_t &data)
108{
[15603]109 text_t sql_cmd = "SELECT value FROM data WHERE key='" + key + "'";
[15602]110 vector<text_tmap> sql_results;
[15635]111 if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
[15602]112 {
113 return false;
114 }
115
116 text_tmap sql_result = sql_results[0];
[15643]117 data = sql_result["value"];
[15602]118 return true;
[15599]119}
120
121
[15642]122// returns array of keys
123text_tarray sqlitedbclass::getkeys ()
[15599]124{
[15642]125 text_tarray keys;
126
127 // Get all the entries in the "key" column of the table
128 text_t sql_cmd = "SELECT key FROM data";
[15634]129 vector<text_tmap> sql_results;
[15635]130 if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
[15634]131 {
[15642]132 return keys;
[15634]133 }
134
[15642]135 // Iterate through the keys and add them to the array to be returned
136 vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
137 while (sql_results_iterator != sql_results.end())
138 {
139 text_tmap sql_result = (*sql_results_iterator);
140 keys.push_back(sql_result["key"]);
141 sql_results_iterator++;
142 }
143
144 return keys;
[15599]145}
146
147
[15796]148text_tarray sqlitedbclass::get_metadata_values (const text_t &metadata_element_name)
[15752]149{
150 text_tarray metadata_values;
151
152 // Get all the entries in the "value" column of the "document_metadata" table
153 text_t sql_cmd = "SELECT value FROM document_metadata WHERE element='" + metadata_element_name + "'";
154 vector<text_tmap> sql_results;
155 if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
156 {
157 return metadata_values;
158 }
159
160 // Iterate through the values and add them to the array to be returned
161 vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
162 while (sql_results_iterator != sql_results.end())
163 {
164 text_tmap sql_result = (*sql_results_iterator);
165 metadata_values.push_back(sql_result["value"]);
166 sql_results_iterator++;
167 }
168
169 return metadata_values;
170}
171
172
[15599]173// returns true on success
[15648]174bool sqlitedbclass::setkeydata (const text_t &key, const text_t &data)
[15647]175{
[15636]176 // We need to do either an INSERT or UPDATE depending on whether the key already exists
177 if (!exists(key))
178 {
179 text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + key + "', '" + data + "')";
180 return sqlexec(sql_cmd);
181 }
182 else
183 {
184 text_t sql_cmd = "UPDATE data SET value='" + data + "' WHERE key='" + key + "'";
185 return sqlexec(sql_cmd);
186 }
[15599]187}
188
189
[15623]190// ----------------------------------------------------------------------------------------
[15655]191// SQLITE-ONLY FUNCTIONS
[15623]192// ----------------------------------------------------------------------------------------
193
[15783]194// sleep for the given number of milliseconds
195void sleep(int m)
196{
197#ifdef __WIN32__
198 Sleep(m);
199#else
200 usleep(m);
201#endif
202}
203
204
[15601]205// sqlexec simply executes the given sql statement - it doesn't obtain a
206// result set - returns true if the sql statement was executed successfully
[15640]207bool sqlitedbclass::sqlexec(const text_t &sql_cmd)
[15601]208{
[15635]209 if (sqlitefile == NULL) return false;
210
[15601]211 char *sql_cmd_cstr = sql_cmd.getcstr();
212
213 int rv = 0;
214 int tries = 0;
215 while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, NULL, NULL, NULL)) == SQLITE_BUSY)
216 {
217 sleep(1000);
218 tries++;
219 if (tries > SQLITE_MAX_RETRIES)
220 {
221 outconvertclass text_t2ascii;
222 (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
223 break;
224 }
225 }
226
227 delete[] sql_cmd_cstr;
228
229 if (rv == SQLITE_OK) return true;
230
[15602]231 // sqlite3_exec failed - return false
232 outconvertclass text_t2ascii;
233 (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
234 return false;
235}
236
237
238// callback functions for sqlgetarray
239static int sqlgetarray_callback(void *res, int numcols, char **vals, char **columnnames)
240{
241 vector<text_tmap> *result = (vector<text_tmap>*) res;
242 text_tmap row;
243
244 for (int i = 0; i < numcols; i++)
[15601]245 {
[15602]246 row[columnnames[i]] = "";
247 // vals[i] will be NULL if set to a NULL db value
248 if (vals[i])
249 {
250 row[columnnames[i]] = vals[i];
251 }
[15601]252 }
253
[15602]254 result->push_back(row);
255 return 0;
256}
257
258
[15623]259// sqlgetarray executes sql and returns the result set in sql_results
[15640]260bool sqlitedbclass::sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
[15602]261{
[15635]262 if (sqlitefile == NULL) return false;
263
[15602]264 char *sql_cmd_cstr = sql_cmd.getcstr();
265 sql_results.erase(sql_results.begin(), sql_results.end());
266
267 int rv = 0;
268 int tries = 0;
269 while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, sqlgetarray_callback, &sql_results, NULL)) == SQLITE_BUSY)
270 {
271 sleep(1000);
272 tries++;
273 if (tries > SQLITE_MAX_RETRIES)
274 {
275 outconvertclass text_t2ascii;
276 (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
277 break;
278 }
279 }
280
281 delete[] sql_cmd_cstr;
282
283 if (rv == SQLITE_OK) return true;
284
285 // sqlite3_exec failed - return empty result set
286 outconvertclass text_t2ascii;
287 (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
[15601]288 return false;
289}
290
291
[15637]292// returns true if exists
[15640]293bool sqlitedbclass::sqltableexists(const text_t &table_name)
[15637]294{
295 text_t sql_cmd = "SELECT * FROM sqlite_master WHERE tbl_name='" + table_name + "'";
296 vector<text_tmap> sql_results;
297 if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
298 {
299 return false;
300 }
301
302 return true;
303}
Note: See TracBrowser for help on using the repository browser.