OpenDNSSEC-enforcer  1.4.7
database_access_mysql.c
Go to the documentation of this file.
1 /*
2  * Copyright (c) 2008-2009 Nominet UK. All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions
6  * are met:
7  * 1. Redistributions of source code must retain the above copyright
8  * notice, this list of conditions and the following disclaimer.
9  * 2. Redistributions in binary form must reproduce the above copyright
10  * notice, this list of conditions and the following disclaimer in the
11  * documentation and/or other materials provided with the distribution.
12  *
13  * THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
14  * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
15  * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
16  * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
17  * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
18  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
19  * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
20  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
21  * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
22  * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
23  * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
24  *
25  */
26 
27 /*+
28  * database_access - database Access Functions
29  *
30  * Description:
31  * Holds miscellaneous utility functions associated with the MySql
32  * database.
33  *
34  * This particular file holds encapsulations of the underlying access
35  * functions - querying/modifying the database and retrieving results.
36 -*/
37 
38 #include <stdarg.h>
39 #include <string.h>
40 #include <stdio.h>
41 #include <time.h>
42 
43 #include <mysql.h>
44 
45 #include "ksm/dbsdef.h"
46 #include "ksm/database.h"
47 #include "ksm/debug.h"
48 #include "ksm/memory.h"
49 #include "ksm/message.h"
50 #include "ksm/string_util.h"
51 
52 #define MIN(x, y) ((x) < (y) ? (x) : (y))
53 #define MAX(x, y) ((x) > (y) ? (x) : (y))
54 
55 
56 /*+
57  * DbExecuteSqlStatement - Execute SQL Statement
58  *
59  * Description:
60  * A wrapper round mysql_query that outputs the query being executed
61  * if the appropriate debug flag is set.
62  *
63  * Arguments:
64  * DB_HANDLE handle
65  * Handle to the currently opened database.
66  *
67  * const char* stmt_str
68  * SQL statement to execute.
69  *
70  * Returns:
71  * int
72  * Any return value from mysql_query.
73 -*/
74 
75 static int DbExecuteSqlStatement(DB_HANDLE handle, const char* stmt_str)
76 {
77  DbgOutput(DBG_M_SQL, "%s\n", stmt_str);
78  return mysql_query((MYSQL*) handle, stmt_str);
79 }
80 
81 
82 
83 /*+
84  * DbExecuteSql - Execute SQL Statement
85  *
86  * Description:
87  * Executes the given SQL statement and returns the results (if any).
88  *
89  * Arguments:
90  * DB_HANDLE handle
91  * Handle to the currently opened database.
92  *
93  * const char* stmt_str
94  * Statement to execute.
95  *
96  * DB_RESULT* result
97  * Pointer to the result set is put here. It must be freed by
98  * DbFreeResult(). This is NULL if no data is returned; on error, the
99  * value is undefined.
100  *
101  * Returns:
102  * int
103  * 0 Success
104  * Other Error code. A message will have been output.
105 -*/
106 
107 int DbExecuteSql(DB_HANDLE handle, const char* stmt_str, DB_RESULT* result)
108 {
109  const char* errmsg = NULL; /* Error message from MySql on failure */
110  int status = 0; /* Status return */
111 
112  /* Argument check */
113 
114  if ((!handle) || (!stmt_str) || (*stmt_str == '\0') || (! result)) {
115  status = MsgLog(DBS_INVARG, "DbExecuteSql");
116  return status;
117  }
118 
119  /* Allocate the result structure */
120 
121  *result = (DB_RESULT) MemCalloc(1, sizeof(struct db_result));
122  (*result)->magic = DB_RESULT_MAGIC;
123  (*result)->handle = handle;
124 
125  /* Execute statement */
126 
127  status = DbExecuteSqlStatement(handle, stmt_str);
128  if (status == 0) {
129 
130  /* Get the pointer to the result set */
131 
132  (*result)->data = mysql_store_result((MYSQL*) handle);
133  if ((*result)->data == NULL) {
134 
135  /*
136  * No result set, so could be some error. See if this is the case
137  * by checking if there is error text. If not, there are no results
138  * from the SQL - it could have been a statement such as DELETE or
139  * INSERT.
140  */
141 
142  errmsg = DbErrmsg(handle);
143  if (errmsg && *errmsg) {
144 
145  /* Error text, so error occurred. Output message & tidy up */
146 
147  status = MsgLog(DBS_SQLFAIL, errmsg);
148  }
149  /*
150  * else {
151  *
152  * No error, so we just don't have any results.
153  * }
154  */
155 
156  /*
157  * Regardless of what heppened, there is no result set, so free up
158  * allocated memory.
159  */
160 
161  MemFree(*result);
162  *result = NULL;
163  }
164  else {
165 
166  /*
167  * Success. "result" holds the result set. Store the number of
168  * fields along with the length of each one for possible later use.
169  */
170 
171  (*result)->count = mysql_field_count((MYSQL*) (*result)->handle);
172  }
173  }
174  else {
175 
176  /* Query failed. Log the error and free up the structure */
177 
178  status = MsgLog(DBS_SQLFAIL, DbErrmsg(handle));
179  MemFree(*result);
180  *result = NULL;
181  }
182 
183  return status;
184 }
185 
186 /*+
187  * DbFreeResult - Free Result
188  *
189  * Description:
190  * Frees up resources allocated for the result by DbExecuteSql.
191  *
192  * Arguments:
193  * DB_RESULT result
194  * Handle to the query result. May be NULL, in which case this
195  * function is a no-op.
196  *
197  * If invalid, an error message will be output.
198 -*/
199 
201 {
202  if (result) {
203  if (result->magic == DB_RESULT_MAGIC) {
204 
205  /* Free up data */
206 
207  mysql_free_result((MYSQL_RES*) result->data);
208  MemFree(result);
209  }
210  else {
211 
212  /* Invalid structure - output a warning but do nothing */
213 
214  (void) MsgLog(DBS_INVARG, "DbFreeResult");
215  }
216  }
217 
218  return;
219 }
220 
221 
222 
223 /*+
224  * DbFetchRow - Fetch Row from Result
225  *
226  * Description:
227  * Fetches the next row from the result set. The structure returned
228  * *must* be freed by DbFreeRow() after use.
229  *
230  * Arguments:
231  * DB_RESULT result
232  * The result handle returned by the call to DbExecuteSql.
233  *
234  * DB_ROW* row
235  * The row object is put here. It will be NULL end of file; on error,
236  * it is undefined.
237  *
238  * Returns:
239  * int
240  * 0 Success, row information returned
241  * -1 Success, no more rows for this result
242  * Other Error code or error number from DbErrno().
243 -*/
244 
245 int DbFetchRow(DB_RESULT result, DB_ROW* row)
246 {
247  int status = 0; /* Status return */
248  MYSQL_ROW rowdata; /* Fetched row information */
249 
250  if (result && (result->magic == DB_RESULT_MAGIC) && row) {
251 
252  /* There is a result structure (and row pointer), do something */
253 
254  rowdata = mysql_fetch_row(result->data);
255  if (rowdata) {
256 
257  /* Something returned, encapsulate the result in a structure */
258 
259  *row = (DB_ROW) MemCalloc(1, sizeof(struct db_row));
260  (*row)->magic = DB_ROW_MAGIC;
261  (*row)->result = result;
262  (*row)->data = rowdata;
263  }
264  else {
265 
266  /*
267  * End of file - in this implementation, only mysql_store_result is
268  * used, so mysql_fetch_row returns NULL only on end of file.
269  */
270 
271  /* leave freeing the row to the calling function */
272  /* *row = NULL; */
273  status = -1;
274  }
275  }
276  else {
277  status = MsgLog(DBS_INVARG, "DbFetchRow");
278  }
279 
280  return status;
281 }
282 
283 
284 
285 /*+
286  * DbFreeRow - Free Row
287  *
288  * Description:
289  * Frees up resources allocated for the row.
290  *
291  * Arguments:
292  * DB_RESULT result
293  * Handle to the query result. May be NULL, in which case this
294  * function is a no-op.
295 -*/
296 
297 void DbFreeRow(DB_ROW row)
298 {
299  if (row) {
300  if (row->magic == DB_ROW_MAGIC) {
301  MemFree(row);
302  }
303  else {
304 
305  /* Output warning, but otherwise do nothing */
306 
307  (void) MsgLog(DBS_INVARG, "DbFreeRow");
308  }
309  }
310 
311  return;
312 }
313 
314 
315 
316 /*+
317  * DbString - Return String Value
318  *
319  * Description:
320  * Returns string value from the current row.
321  *
322  * Arguments:
323  * DB_ROW row
324  * Pointer to the row object.
325  *
326  * int field_index
327  * Index of the value required.
328  *
329  * char** result
330  * Value of the field. It is up to the caller to free it with
331  * a call to DbStringFree(). Note that this can be NULL if the
332  * corresponding field is NULL.
333  *
334  * Returns:
335  * int
336  * 0 Success
337  * Other Some error. A message will have been output
338 -*/
339 
340 int DbString(DB_ROW row, int field_index, char** result)
341 {
342  int status = 0; /* Status return */
343  unsigned long *lengths; /* Lengths of columns in each row */
344 
345  /* Check arguments */
346 
347  if (row && (row->magic == DB_ROW_MAGIC) && result) {
348 
349  /* Is the index requested valid? */
350 
351  if ((field_index >= 0) && (field_index < row->result->count)) {
352 
353  /* Get the lengths of the fields in the row */
354 
355  lengths = mysql_fetch_lengths((MYSQL_RES*) row->result->data);
356 
357  /* Get string into null-terminated form */
358 
359  if (row->data[field_index] != NULL) {
360  /* TODO replece the below with strdup or StrStrdup ? */
361  *result = MemMalloc(lengths[field_index] + 1);
362  memcpy(*result, row->data[field_index], lengths[field_index]);
363  (*result)[lengths[field_index]] = 0;
364  }
365  else {
366  *result = NULL;
367  }
368  }
369  else {
370 
371  /* Invalid field, tell the user */
372 
373  status = MsgLog(DBS_INVINDEX, field_index, row->result->count);
374  }
375 
376  }
377  else {
378 
379  /* Problem with the command arguments */
380 
381  status = MsgLog(DBS_INVARG, "DbString");
382  }
383 
384  return status;
385 
386 }
387 
388 
389 /*+
390  * DbStringFree - Free String Returned by DbString
391  *
392  * Description:
393  * Frees the pointer-to string.
394  *
395  * Arguments:
396  * char* string
397  * String allocated by DbString. On exit, this pointer is invalid.
398 -*/
399 
400 void DbStringFree(char* string)
401 {
402  MemFree(string);
403 }
404 
405 /*+
406  * DbBeginTransaction - Start a (non-nested) transaction
407  *
408  * Description:
409  * NB the following will not work if your tables are MyISAM
410  * as transactions are not supported
411  *
412  * Arguments:
413  * NONE
414 -*/
415 
417 {
418  const char* sql = "start transaction";
419  return DbExecuteSqlNoResult(DbHandle(), sql);
420 }
421 
422 /*+
423  * DbCommit - End a (non-nested) transaction by commiting it
424  *
425  * Description:
426  *
427  *
428  * Arguments:
429  * NONE
430 -*/
431 
432 int DbCommit(void)
433 {
434  const char* sql = "commit";
435  return DbExecuteSqlNoResult(DbHandle(), sql);
436 }
437 
438 /*+
439  * DbRollback - End a (non-nested) transaction by rolling it back
440  *
441  * Description:
442  *
443  *
444  * Arguments:
445  * NONE
446 -*/
447 
448 int DbRollback(void)
449 {
450  const char* sql = "rollback";
451  return DbExecuteSqlNoResult(DbHandle(), sql);
452 }
void * MemCalloc(size_t nmemb, size_t size)
Definition: memory.c:68
sqlite3 * DB_HANDLE
Definition: database.h:77
#define DB_RESULT_MAGIC
Definition: database.h:88
void DbgOutput(unsigned int mask, const char *format,...)
Definition: debug.c:135
unsigned int magic
Definition: database.h:81
sqlite3_stmt * data
Definition: database.h:84
struct db_row * DB_ROW
Definition: database.h:98
int MsgLog(int status,...)
Definition: message.c:335
void DbStringFree(char *string)
#define DBG_M_SQL
Definition: debug.h:43
int DbFetchRow(DB_RESULT result, DB_ROW *row)
#define DBS_INVARG
Definition: dbsdef.h:48
#define MemFree(ptr)
Definition: memory.h:48
DB_HANDLE DbHandle(void)
struct db_result * DB_RESULT
Definition: database.h:90
const char * DbErrmsg(DB_HANDLE handle)
int DbExecuteSql(DB_HANDLE handle, const char *stmt_str, DB_RESULT *result)
#define DBS_INVINDEX
Definition: dbsdef.h:49
void DbFreeRow(DB_ROW row)
#define DB_ROW_MAGIC
Definition: database.h:97
DB_RESULT result
Definition: database.h:95
void DbFreeResult(DB_RESULT result)
int DbBeginTransaction(void)
int count
Definition: database.h:82
void * MemMalloc(size_t size)
Definition: memory.c:57
#define DBS_SQLFAIL
Definition: dbsdef.h:55
int DbRollback(void)
int DbString(DB_ROW row, int field_index, char **result)
int DbCommit(void)
int DbExecuteSqlNoResult(DB_HANDLE handle, const char *stmt_str)
unsigned int magic
Definition: database.h:94