root/gsdl/trunk/lib/sqliteclass.cpp @ 15633

Revision 15633, 9.2 KB (checked in by mdewsnip, 11 years ago)

(Adding new DB support) Minor improvements to opendatabase() and an initial implementation of deletekey().

Line 
1/**********************************************************************
2 *
3 * sqliteclass.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 "sqliteclass.h"
27#include "unitool.h"
28
29
30#define SQLITE_MAX_RETRIES 8
31
32
33sqliteclass::~sqliteclass()
34{
35  closedatabase();
36}
37
38
39// returns true if opened
40bool sqliteclass::opendatabase(const text_t &filename, int mode, int num_retrys,
41#ifdef __WIN32__
42                   bool need_filelock
43#else
44                   bool
45#endif
46                  )
47{
48  // Check if we've already got the database open
49  if (sqlitefile != NULL)
50  {
51    if (openfile == filename) return true;
52    else closedatabase();
53  }
54
55  char *filename_cstr = filename.getcstr();
56  sqlitefile = NULL;
57  sqlite3_open(filename_cstr, &sqlitefile);
58  delete[] filename_cstr;
59
60  if (sqlitefile == NULL)
61  {
62    outconvertclass text_t2ascii;
63    (*logout) << text_t2ascii << "database open failed on: " << filename << "\n";
64    return false;
65  }
66
67  if (mode == DB_WRITER_CREATE)
68  {
69    sqlexec("CREATE TABLE data (key TEXT, value TEXT, PRIMARY KEY(key))");
70  }
71
72  return true;
73}
74
75
76void sqliteclass::closedatabase()
77{
78  if (sqlitefile == NULL) return;
79
80  sqlite3_close(sqlitefile);
81  sqlitefile = NULL;
82  openfile.clear();
83}
84
85
86// returns true on success
87bool sqliteclass::getinfo(const text_t& key, infodbclass &info)
88{
89  text_t sql_cmd = "SELECT value FROM data WHERE key='" + key + "'";
90  vector<text_tmap> sql_results;
91  if (sqlitefile == NULL || !sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
92  {
93    return false;
94  }
95
96  text_tmap sql_result = sql_results[0];
97  text_t sql_result_value = sql_result["value"];
98  text_t::iterator sql_result_value_iterator = sql_result_value.begin();
99  text_t ikey, ivalue;
100  info.clear();
101  while (getinfoline(sql_result_value_iterator, sql_result_value.end(), ikey, ivalue))
102  {
103    info.addinfo(ikey, ivalue);
104  }
105
106  return true;
107}
108
109
110// returns true if exists
111bool sqliteclass::exists(const text_t& key)
112{
113  // !! TO IMPLEMENT
114  return false;
115}
116
117
118// returns true on success
119bool sqliteclass::setinfo(const text_t &key, const infodbclass &info)
120{
121  if (sqlitefile == NULL) return false;
122
123  text_t subkey;
124  text_t data;
125
126  // get all the keys and values
127  infodbclass::const_iterator info_here = info.begin();
128  infodbclass::const_iterator info_end = info.end();
129  while (info_here != info_end) {
130    // add the key
131    subkey.clear();
132    subkey.push_back('<');
133    text_t::const_iterator subkey_here = (*info_here).first.begin();
134    text_t::const_iterator subkey_end = (*info_here).first.end();
135    while (subkey_here != subkey_end) {
136      if (*subkey_here == '>') {
137    subkey.push_back('\\'); subkey.push_back('>');
138      } else if (*subkey_here == '\n') {
139    subkey.push_back('\\'); subkey.push_back('n');
140      } else if (*subkey_here == '\r') {
141    subkey.push_back('\\'); subkey.push_back('r');
142      } else if (*subkey_here == '\\') {
143    subkey.push_back('\\'); subkey.push_back('\\');
144      } else {
145    subkey.push_back (*subkey_here);
146      }
147      ++subkey_here;
148    }
149    subkey.push_back('>');
150
151    // add the values
152    text_tarray::const_iterator subvalue_here = (*info_here).second.begin();
153    text_tarray::const_iterator subvalue_end = (*info_here).second.end();
154    while (subvalue_here != subvalue_end) {
155      data += subkey;
156     
157      text_t::const_iterator thissubvalue_here = (*subvalue_here).begin();
158      text_t::const_iterator thissubvalue_end = (*subvalue_here).end();
159      while (thissubvalue_here != thissubvalue_end) {
160    if (*thissubvalue_here == '>') {
161      data.push_back('\\'); data.push_back('>');
162    } else if (*thissubvalue_here == '\n') {
163      data.push_back('\\'); data.push_back('n');
164    } else if (*thissubvalue_here == '\r') {
165      data.push_back('\\'); data.push_back('r');
166    } else if (*thissubvalue_here == '\\') {
167      data.push_back('\\'); data.push_back('\\');
168    } else {
169      data.push_back (*thissubvalue_here);
170    }
171   
172    ++thissubvalue_here;
173      }
174     
175      data.push_back('\n');
176      ++subvalue_here;
177    }
178
179    ++info_here;
180  }
181
182  outconvertclass text_t2ascii;
183  (*logout) << text_t2ascii << "Inserting for " << key << ":\n" << data << "\n";
184
185  text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + key + "', '" + data + "')";
186  return sqlexec(sql_cmd);
187}
188
189
190void sqliteclass::deletekey (const text_t &key)
191{
192  text_t sql_cmd = "DELETE FROM data WHERE key='" + key + "'";
193  sqlexec(sql_cmd);
194}
195
196
197text_tarray sqliteclass::getkeys ()
198{
199  text_tarray keys;
200
201  // Get all the entries in the "key" column of the table
202  text_t sql_cmd = "SELECT key FROM data";
203  vector<text_tmap> sql_results;
204  if (sqlitefile == NULL || !sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
205  {
206    return keys;
207  }
208
209  // Iterate through the keys and add them to the array to be returned
210  vector<text_tmap>::iterator sql_results_iterator = sql_results.begin();
211  while (sql_results_iterator != sql_results.end())
212  {
213    text_tmap sql_result = (*sql_results_iterator);
214    keys.push_back(sql_result["key"]);
215    sql_results_iterator++;
216  }
217
218  return keys;
219}
220
221
222// returns true on success
223bool sqliteclass::getinfoline (text_t::iterator &here, text_t::iterator end,
224                   text_t &key, text_t &value)
225{
226  key.clear();
227  value.clear();
228
229  // ignore white space
230  while (here != end && is_unicode_space (*here)) ++here;
231
232  // get the '<'
233  if (here == end || *here != '<') return false;
234  ++here;
235 
236  // get the key
237  while (here != end && *here != '>') {
238    key.push_back(*here);
239    ++here;
240  }
241 
242  // get the '>'
243  if (here == end || *here != '>') return false;
244  ++here;
245 
246  // get the value
247  while (here != end && *here != '\n') {
248    if (*here == '\\') {
249      // found escape character
250      ++here;
251      if (here != end) {
252    if (*here == 'n') value.push_back ('\n');
253    else if (*here == 'r') value.push_back ('\r');
254    else value.push_back(*here);
255      }
256
257    } else {
258      // a normal character
259      value.push_back(*here);
260    }
261
262    ++here;
263  }
264
265  return true;
266}
267
268
269// ----------------------------------------------------------------------------------------
270//   CORE SQL FUNCTIONS
271// ----------------------------------------------------------------------------------------
272
273// sqlexec simply executes the given sql statement - it doesn't obtain a
274// result set - returns true if the sql statement was executed successfully
275bool sqliteclass::sqlexec(const text_t &sql_cmd)
276{
277  char *sql_cmd_cstr = sql_cmd.getcstr();
278
279  int rv = 0;
280  int tries = 0;
281  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, NULL, NULL, NULL)) == SQLITE_BUSY)
282  {
283    sleep(1000);
284    tries++;
285    if (tries > SQLITE_MAX_RETRIES)
286    {
287      outconvertclass text_t2ascii;
288      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
289      break;
290    }
291  }
292
293  delete[] sql_cmd_cstr;
294
295  if (rv == SQLITE_OK) return true;
296
297  // sqlite3_exec failed - return false
298  outconvertclass text_t2ascii;
299  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
300  return false;
301}
302
303
304// callback functions for sqlgetarray
305static int sqlgetarray_callback(void *res, int numcols, char **vals, char **columnnames)
306{
307  vector<text_tmap> *result = (vector<text_tmap>*) res;
308  text_tmap row;
309
310  for (int i = 0; i < numcols; i++)
311  {
312    row[columnnames[i]] = "";
313    // vals[i] will be NULL if set to a NULL db value
314    if (vals[i])
315    {
316      row[columnnames[i]] = vals[i];     
317    }
318  }
319
320  result->push_back(row);
321  return 0;
322}
323
324
325// sqlgetarray executes sql and returns the result set in sql_results
326bool sqliteclass::sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
327{
328  char *sql_cmd_cstr = sql_cmd.getcstr();
329  sql_results.erase(sql_results.begin(), sql_results.end());
330
331  int rv = 0;
332  int tries = 0;
333  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, sqlgetarray_callback, &sql_results, NULL)) == SQLITE_BUSY)
334  {
335    sleep(1000);
336    tries++;
337    if (tries > SQLITE_MAX_RETRIES)
338    {
339      outconvertclass text_t2ascii;
340      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
341      break;
342    }
343  }
344
345  delete[] sql_cmd_cstr;
346
347  if (rv == SQLITE_OK) return true;
348
349  // sqlite3_exec failed - return empty result set
350  outconvertclass text_t2ascii;
351  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
352  return false;
353}
354
355
356// sleep for the given number of milliseconds
357void sleep(int m)
358{
359#ifdef __WIN32__
360  Sleep(m);
361#else
362  usleep(m);
363#endif
364}
Note: See TracBrowser for help on using the browser.