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

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

(Adding new DB support) Minor improvements to getinfo().

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  vector<text_tmap> sql_results;
90  if (sqlitefile == NULL || !sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)
91  {
92    return false;
93  }
94
95  text_tmap sql_result = sql_results[0];
96  text_t sql_result_value = sql_result["value"];
97  text_t::iterator sql_result_value_iterator = sql_result_value.begin();
98  text_t ikey, ivalue;
99  info.clear();
100  while (getinfoline(sql_result_value_iterator, sql_result_value.end(), ikey, ivalue))
101  {
102    info.addinfo(ikey, ivalue);
103  }
104
105  return true;
106}
107
108
109// returns true if exists
110bool sqliteclass::exists(const text_t& key)
111{
112  // !! TO IMPLEMENT
113  return false;
114}
115
116
117// returns true on success
118bool sqliteclass::setinfo(const text_t &key, const infodbclass &info)
119{
120  if (sqlitefile == NULL) return false;
121
122  text_t subkey;
123  text_t data;
124
125  // get all the keys and values
126  infodbclass::const_iterator info_here = info.begin();
127  infodbclass::const_iterator info_end = info.end();
128  while (info_here != info_end) {
129    // add the key
130    subkey.clear();
131    subkey.push_back('<');
132    text_t::const_iterator subkey_here = (*info_here).first.begin();
133    text_t::const_iterator subkey_end = (*info_here).first.end();
134    while (subkey_here != subkey_end) {
135      if (*subkey_here == '>') {
136    subkey.push_back('\\'); subkey.push_back('>');
137      } else if (*subkey_here == '\n') {
138    subkey.push_back('\\'); subkey.push_back('n');
139      } else if (*subkey_here == '\r') {
140    subkey.push_back('\\'); subkey.push_back('r');
141      } else if (*subkey_here == '\\') {
142    subkey.push_back('\\'); subkey.push_back('\\');
143      } else {
144    subkey.push_back (*subkey_here);
145      }
146      ++subkey_here;
147    }
148    subkey.push_back('>');
149
150    // add the values
151    text_tarray::const_iterator subvalue_here = (*info_here).second.begin();
152    text_tarray::const_iterator subvalue_end = (*info_here).second.end();
153    while (subvalue_here != subvalue_end) {
154      data += subkey;
155     
156      text_t::const_iterator thissubvalue_here = (*subvalue_here).begin();
157      text_t::const_iterator thissubvalue_end = (*subvalue_here).end();
158      while (thissubvalue_here != thissubvalue_end) {
159    if (*thissubvalue_here == '>') {
160      data.push_back('\\'); data.push_back('>');
161    } else if (*thissubvalue_here == '\n') {
162      data.push_back('\\'); data.push_back('n');
163    } else if (*thissubvalue_here == '\r') {
164      data.push_back('\\'); data.push_back('r');
165    } else if (*thissubvalue_here == '\\') {
166      data.push_back('\\'); data.push_back('\\');
167    } else {
168      data.push_back (*thissubvalue_here);
169    }
170   
171    ++thissubvalue_here;
172      }
173     
174      data.push_back('\n');
175      ++subvalue_here;
176    }
177
178    ++info_here;
179  }
180
181  outconvertclass text_t2ascii;
182  (*logout) << text_t2ascii << "Inserting for " << key << ":\n" << data << "\n";
183
184  text_t sql_cmd = "INSERT INTO data (key, value) VALUES ('" + key + "', '" + data + "')";
185  return sqlexec(sql_cmd);
186}
187
188
189void sqliteclass::deletekey(const text_t &key)
190{
191  if (sqlitefile == NULL) return; 
192
193  // !! TO IMPLEMENT
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.