root/gsdl/trunk/lib/sqlitedbclass.cpp @ 15679

Revision 15679, 6.6 KB (checked in by mdewsnip, 11 years ago)

(Adding new DB support) Adding a new "getfileextension()" function that is implemented differently between gdbmclass (which is endian-specific) and sqlitedbclass (which isn't).

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