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

Revision 15623, 8.9 KB (checked in by mdewsnip, 12 years ago)

(Adding new DB support) Implemented a first cut at the getinfo() function.

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_t sqliteclass::getfirstkey()
203{
204  if (sqlitefile == NULL) return g_EmptyText;
205
206  // !! TO IMPLEMENT
207  return g_EmptyText;
208}
209
210
211text_t sqliteclass::getnextkey(const text_t &key)
212{
213  if (sqlitefile == NULL || key.empty()) return g_EmptyText;
214
215  // !! TO IMPLEMENT
216  return g_EmptyText;
217}
218
219
220// returns true on success
221bool sqliteclass::getinfoline (text_t::iterator &here, text_t::iterator end,
222                   text_t &key, text_t &value)
223{
224  key.clear();
225  value.clear();
226
227  // ignore white space
228  while (here != end && is_unicode_space (*here)) ++here;
229
230  // get the '<'
231  if (here == end || *here != '<') return false;
232  ++here;
233 
234  // get the key
235  while (here != end && *here != '>') {
236    key.push_back(*here);
237    ++here;
238  }
239 
240  // get the '>'
241  if (here == end || *here != '>') return false;
242  ++here;
243 
244  // get the value
245  while (here != end && *here != '\n') {
246    if (*here == '\\') {
247      // found escape character
248      ++here;
249      if (here != end) {
250    if (*here == 'n') value.push_back ('\n');
251    else if (*here == 'r') value.push_back ('\r');
252    else value.push_back(*here);
253      }
254
255    } else {
256      // a normal character
257      value.push_back(*here);
258    }
259
260    ++here;
261  }
262
263  return true;
264}
265
266
267// ----------------------------------------------------------------------------------------
268//   CORE SQL FUNCTIONS
269// ----------------------------------------------------------------------------------------
270
271// sqlexec simply executes the given sql statement - it doesn't obtain a
272// result set - returns true if the sql statement was executed successfully
273bool sqliteclass::sqlexec(const text_t &sql_cmd)
274{
275  char *sql_cmd_cstr = sql_cmd.getcstr();
276
277  int rv = 0;
278  int tries = 0;
279  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, NULL, NULL, NULL)) == SQLITE_BUSY)
280  {
281    sleep(1000);
282    tries++;
283    if (tries > SQLITE_MAX_RETRIES)
284    {
285      outconvertclass text_t2ascii;
286      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
287      break;
288    }
289  }
290
291  delete[] sql_cmd_cstr;
292
293  if (rv == SQLITE_OK) return true;
294
295  // sqlite3_exec failed - return false
296  outconvertclass text_t2ascii;
297  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
298  return false;
299}
300
301
302// callback functions for sqlgetarray
303static int sqlgetarray_callback(void *res, int numcols, char **vals, char **columnnames)
304{
305  vector<text_tmap> *result = (vector<text_tmap>*) res;
306  text_tmap row;
307
308  for (int i = 0; i < numcols; i++)
309  {
310    row[columnnames[i]] = "";
311    // vals[i] will be NULL if set to a NULL db value
312    if (vals[i])
313    {
314      row[columnnames[i]] = vals[i];     
315    }
316  }
317
318  result->push_back(row);
319  return 0;
320}
321
322
323// sqlgetarray executes sql and returns the result set in sql_results
324bool sqliteclass::sqlgetarray(const text_t &sql_cmd, vector<text_tmap> &sql_results)
325{
326  char *sql_cmd_cstr = sql_cmd.getcstr();
327  sql_results.erase(sql_results.begin(), sql_results.end());
328
329  int rv = 0;
330  int tries = 0;
331  while ((rv = sqlite3_exec(sqlitefile, sql_cmd_cstr, sqlgetarray_callback, &sql_results, NULL)) == SQLITE_BUSY)
332  {
333    sleep(1000);
334    tries++;
335    if (tries > SQLITE_MAX_RETRIES)
336    {
337      outconvertclass text_t2ascii;
338      (*logout) << text_t2ascii << "max_retries exceeded for sql query: " << sql_cmd << "\n";
339      break;
340    }
341  }
342
343  delete[] sql_cmd_cstr;
344
345  if (rv == SQLITE_OK) return true;
346
347  // sqlite3_exec failed - return empty result set
348  outconvertclass text_t2ascii;
349  (*logout) << text_t2ascii << "Error executing sql statement: " << sql_cmd << "\n";
350  return false;
351}
352
353
354// sleep for the given number of milliseconds
355void sleep(int m)
356{
357#ifdef __WIN32__
358  Sleep(m);
359#else
360  usleep(m);
361#endif
362}
Note: See TracBrowser for help on using the browser.