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

Revision 15631, 9.2 KB (checked in by mdewsnip, 12 years ago)

(Adding new DB support) Adding an implementation of getkeys() for the sqliteclass.

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