OpenDNSSEC-enforcer  1.4.6
database_support_lite.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_support - Database Utility Functions
29  *
30  * Description:
31  * Holds miscellaneous utility functions associated with the MySql
32  * database.
33 -*/
34 
35 #include <stdarg.h>
36 #include <string.h>
37 #include <stdio.h>
38 #include <time.h>
39 
40 #include <sqlite3.h>
41 
42 #include "ksm/dbsdef.h"
43 #include "ksm/database.h"
44 #include "ksm/debug.h"
45 #include "ksm/message.h"
46 #include "ksm/string_util.h"
47 #include "ksm/string_util2.h"
48 
49 #define MIN(x, y) ((x) < (y) ? (x) : (y))
50 #define MAX(x, y) ((x) > (y) ? (x) : (y))
51 
52 
53 
54 /*+
55  * DbExecuteSqlNoResult - Execute SQL Statement and Ignore Result
56  *
57  * Description:
58  * Executes the given SQL statement; any results are discarded.
59  *
60  * This function is useful for statements such as DELETE and INSERT.
61  *
62  * Arguments:
63  * DB_HANDLE handle
64  * Handle to the currently opened database.
65  *
66  * const char* stmt_str
67  * Statement to execute
68  *
69  * Returns:
70  * int
71  * Status return.
72  * 0 Success
73  * Other Error. A message will have been output.
74 -*/
75 
76 int DbExecuteSqlNoResult(DB_HANDLE handle, const char* stmt_str)
77 {
78  DB_RESULT result; /* Pointer to result string */
79  int status; /* Status return */
80 
81  status = DbExecuteSql(handle, stmt_str, &result);
82  if (status == 0) {
83  if (result) {
84 
85  /* Result given - get rid of it, we don't want it */
86 
87  status = MsgLog(DBS_UNEXRES, stmt_str);
88  DbFreeResult(result);
89  }
90  }
91 
92  return status;
93 }
94 
95 
96 /*+
97  * DbRowId - Return ID of Current Row
98  *
99  * Description:
100  * Returns the ID of the current row. This is assumed to be an auto-
101  * increment column at index 0 of the table.
102  *
103  * Arguments:
104  * DB_ROW row
105  * Row in question.
106  *
107  * DB_ID* id
108  * ID of the row is returned here.
109  *
110  * Returns:
111  * int
112  * Status return.
113  *
114  * 0 Success
115  * Other Error. A message will have been output.
116 -*/
117 
118 int DbRowId(DB_ROW row, DB_ID* id)
119 {
120  unsigned long rowid; /* ID of the row as a known type */
121  int status; /* Status return */
122 
123  if (id == NULL) {
124  status = MsgLog(DBS_INVARG, "NULL id");
125  return -1;
126  }
127 
128  status = DbUnsignedLong(row, 0, &rowid);
129  *id = (DB_ID) rowid; /* Do the conversion between types here */
130 
131  return status;
132 }
133 
134 
135 
136 
137 /*+
138  * DbInt - Return Integer from Field
139  *
140  * Description:
141  * Returns an integer value from the current row.
142  *
143  * Arguments:
144  * DB_ROW row
145  * Pointer to the row object.
146  *
147  * int field_index
148  * Index of the value required.
149  *
150  * int *value
151  * Value returned.
152  *
153  * Returns:
154  * int
155  * Status return:
156  * 0 Success
157  * Other Error accessing data. A message will have been output.
158 -*/
159 
160 int DbInt(DB_ROW row, int field_index, int *value)
161 {
162  char* buffer = NULL; /* Text buffer for returned string */
163  int status; /* Status return */
164 
165  /* Access the text in the field */
166 
167  status = DbString(row, field_index, &buffer);
168  if (status == 0) {
169 
170  /* Got the string, can we convert it? */
171 
172  if (buffer != NULL) {
173 
174  /* Not best-efforts - ignore trailing non-numeric values */
175 
176  status = StrStrtoi(buffer, value);
177  if (status == -1) {
178 
179  /* Could not translate the string to an integer */
180 
181  status = MsgLog(DBS_NOTINT, buffer);
182  *value = 0;
183  }
184  DbStringFree(buffer);
185  }
186  else {
187 
188  /* Field is NULL, return 0 */
189  /* TODO should we do something better here ? */
190 
191  *value = 0;
192  }
193  }
194 
195  return status;
196 }
197 
198 
199 
200 /*+
201  * DbUnsignedLong - Return Unsigned Long from Field
202  *
203  * Description:
204  * Returns an integer value from the current row.
205  *
206  * Arguments:
207  * DB_ROW row
208  * Pointer to the row object.
209  *
210  * int field_index
211  * Index of the value required.
212  *
213  * unsigned long *value
214  * Value returned.
215  *
216  * Returns:
217  * int
218  * Status return:
219  * 0 Success
220  * Other Error accessing data. A message will have been output.
221 -*/
222 
223 int DbUnsignedLong(DB_ROW row, int field_index, unsigned long *value)
224 {
225  char* buffer = NULL; /* Text buffer for returned string */
226  int status; /* Status return */
227 
228  /* Access the text in the field */
229 
230  status = DbString(row, field_index, &buffer);
231  if (status == 0) {
232 
233  /* Got the string, can we convert it? */
234 
235  if (buffer != NULL) {
236 
237  /* Not best-efforts - ignore trailing non-numeric values */
238 
239  status = StrStrtoul(buffer, value);
240  if (status == -1) {
241 
242  /* Could not translate the string to an unsigned long */
243 
244  status = MsgLog(DBS_NOTINT, buffer);
245  *value = 0;
246  }
247  DbStringFree(buffer);
248  }
249  else {
250 
251  /* Field is NULL, return 0 */
252 
253  *value = 0;
254  }
255  }
256 
257  return status;
258 }
259 
260 
261 
262 /*+
263  * DbIntQuery - Perform Query Returning Single Integer
264  *
265  * Description:
266  * Many queries are of the form:
267  *
268  * SELECT COUNT(*) FROM ...
269  * or
270  * SELECT <single integer value> FROM ...
271  *
272  * This function performs the query and returns the single value.
273  *
274  * Arguments:
275  * DB_HANDLE handle
276  * Handle to the currently opened database.
277  *
278  * int* value
279  * Result of the query. Note that if the query returns no rows,
280  * a zero is returned.
281  *
282  * const char* query
283  * Query to run.
284  *
285  * Returns:
286  * int
287  * 0 Success
288  * Other Error (a message will have been output)
289 -*/
290 
291 int DbIntQuery(DB_HANDLE handle, int* value, const char* query)
292 {
293  DB_RESULT result; /* Result object */
294  DB_ROW row = NULL; /* Row object */
295  int status; /* Status return */
296 
297  status = DbExecuteSql(handle, query, &result);
298  if (status == SQLITE_OK) {
299 
300  /* Get first row */
301  status = DbFetchRow(result, &row);
302  if (status == 0) {
303  /* Got the row, so convert to integer */
304 
305  status = DbInt(row, 0, value);
306 
307  /* Query succeeded, but are there any more rows? */
308  if (DbFetchRow(result, &row) != -1) {
309  (void) MsgLog(DBS_TOOMANYROW, query); /* Too much data */
310  }
311 
312  }
313  else
314  {
315  status = MsgLog(DBS_NORESULT); /* Query did not return a result */
316  }
317 
318  DbFreeResult(result);
319  DbFreeRow(row);
320 
321  }
322 
323  return status;
324 }
325 
326 
327 /*+
328  * DbStringBuffer - Return String Value into User-Supplied Buffer
329  *
330  * Description:
331  * Returns string value from the current row into a user-supplied
332  * buffer. The returned value is truncated if required.
333  *
334  * Arguments:
335  * DB_ROW row
336  * Pointer to the row object.
337  *
338  * int field_index
339  * Index of the value required.
340  *
341  * char* buffer
342  * Null-terminated buffer into which the data is put. If the returned
343  * string is NULL, the buffer will contain a zero-length string. There
344  * is no way to distinguish between this and the database holding an
345  * empty string.
346  *
347  * size_t buflen
348  * Length of the buffer.
349  *
350  * Returns:
351  * int
352  * 0 Success
353  * Other Error. A message will have been output.
354 -*/
355 
356 int DbStringBuffer(DB_ROW row, int field_index, char* buffer, size_t buflen)
357 {
358  char* data = NULL; /* Data returned from DbString */
359  int status; /* Status return */
360 
361  if (row && (row->magic == DB_ROW_MAGIC) && buffer && (buflen != 0)) {
362 
363  /* Arguments OK, get the information */
364 
365  status = DbString(row, field_index, &data);
366  if (status == 0) {
367 
368  /* Success, copy the data into destination & free buffer
369  Note the StrStrncpy copes with data == NULL */
370 
371  StrStrncpy(buffer, data, buflen);
372  DbStringFree(data);
373  }
374  }
375  else {
376 
377  /* Invalid srguments, notify the user */
378 
379  status = MsgLog(DBS_INVARG, "DbStringBuffer");
380  }
381 
382  return status;
383 }
384 
385 
386 
387 /*+
388  * DbErrno - Return Last Error Number
389  *
390  * Description:
391  * Returns the numeric code associated with the last operation
392  * on this connection that gave an error.
393  *
394  * Arguments:
395  * DB_HANDLE handle
396  * Handle to an open database.
397  *
398  * Returns:
399  * int
400  * Error number.
401 -*/
402 
403 int DbErrno(DB_HANDLE handle)
404 {
405  return sqlite3_errcode((sqlite3*) handle);
406 }
407 
408 
409 
410 /*+
411  * DbErrmsg - Return Last Error Message
412  *
413  * Description:
414  * Returns the last error on this connection. This is just an
415  * encapsulation of mysql_error.
416  *
417  * Arguments:
418  * DB_HANDLE handle
419  * Handle to an open database.
420  *
421  * Returns:
422  * const char*
423  * Error string. This should be copied and must not be freed.
424 -*/
425 
426 const char* DbErrmsg(DB_HANDLE handle)
427 {
428  return sqlite3_errmsg((sqlite3*) handle);
429 }
430 
431 
432 /*+
433  * DbLastRowId - Return Last Row ID
434  *
435  * Description:
436  * Returns the ID field of the last row inserted.
437  *
438  * All tables are assumed to include an auto-incrementing ID field. Apart
439  * from providing the unique primary key, this is a relatively
440  * implementation-unique way of uniquely identifying a row in a table.
441  *
442  * Arguments:
443  * DB_HANDLE handle
444  * Handle to the database connection.
445  *
446  * DB_ID* id
447  * ID of the last row inserted (into any table) on this connection.
448  *
449  * Returns:
450  * int
451  * Status return
452  *
453  * 0 Success
454  * Other Error code. An error message will have been output.
455 -*/
456 
457 int DbLastRowId(DB_HANDLE handle, DB_ID* id)
458 {
459 
460  if (id == NULL) {
461  return MsgLog(DBS_INVARG, "NULL id");
462  }
463 
464  /* TODO returns a sqlite_int64; can this be cast into an unsigned long?
465  * do we need to check this for each platform? */
466  *id = (DB_ID) sqlite3_last_insert_rowid((sqlite3*) handle);
467 
468  /*
469  * In sqlite, there is no error code; a value of 0 is returned if there
470  * is no matching row. In this case, convert it to an error code.
471  */
472 
473  return (*id != 0) ? 0 : DBS_NOSUCHROW;
474 }
475 
476 /*+
477  * DbQuoteString - Return quoted version of the input string
478  *
479  * Description:
480  * Return quoted version of the input string
481  *
482  * Arguments:
483  * DB_HANDLE handle
484  * Handle to the database connection. (not used, but needed for MySQL
485  * version).
486  *
487  * const char* in
488  * String to quote
489  *
490  * char* buffer
491  * Quoted string
492  *
493  * Returns:
494  * int
495  * Status return
496  *
497  * 0 Success
498  * Other Error code. An error message will have been output.
499 -*/
500 
501 int DbQuoteString(DB_HANDLE handle, const char* in, char* buffer, size_t buflen)
502 {
503 
504  (void) handle;
505 
506  if (in == NULL) {
507  return MsgLog(DBS_INVARG, "NULL input string to DbQuoteString");
508  }
509 
510  sqlite3_snprintf(buflen, buffer, "%q", in);
511 
512  return ( strlen(buffer) == 0 ) ? 1 : 0;
513 }
514 
515 /*+
516  * DbDateDiff - Return SQL statement for a date plus or minus a delta
517  *
518  * Description:
519  * Return quoted version of the input string
520  *
521  * Arguments:
522  *
523  * const char* start
524  * Start date
525  *
526  * int delta
527  * Difference in seconds
528  *
529  * int sign
530  * -1 to subtract the delta, +1 to add
531  *
532  * char* buffer
533  * SQL string
534  *
535  * Returns:
536  * int
537  * Status return
538  *
539  * 0 Success
540  * Other Error code. An error message will have been output.
541 -*/
542 
543 int DbDateDiff(const char* start, int delta, int sign, char* buffer, size_t buflen)
544 {
545  int nchar;
546 
547  if (start == NULL) {
548  return MsgLog(DBS_INVARG, "NULL input string to DbDateDiff");
549  }
550 
551  if (sign == 1) {
552  nchar = snprintf(buffer, buflen,
553  "DATETIME('%s', '+%d SECONDS')", start, delta);
554  }
555  else if (sign == -1) {
556  nchar = snprintf(buffer, buflen,
557  "DATETIME('%s', '-%d SECONDS')", start, delta);
558  }
559  else {
560  return MsgLog(DBS_INVARG, "Invalid sign to DbDateDiff");
561  }
562 
563  if (nchar >= (int)buflen || nchar < 0) {
564  return 1;
565  }
566 
567  return 0;
568 
569 }
void DbFreeResult(DB_RESULT result)
sqlite3 * DB_HANDLE
Definition: database.h:77
int DbFetchRow(DB_RESULT result, DB_ROW *row)
int MsgLog(int status,...)
Definition: message.c:335
#define DBS_INVARG
Definition: dbsdef.h:48
int DbString(DB_ROW row, int field_index, char **result)
#define DBS_TOOMANYROW
Definition: dbsdef.h:58
int DbQuoteString(DB_HANDLE handle, const char *in, char *buffer, size_t buflen)
void StrStrncpy(char *dest, const char *src, size_t destlen)
Definition: string_util.c:176
int DbLastRowId(DB_HANDLE handle, DB_ID *id)
unsigned long DB_ID
Definition: database.h:78
const char * DbErrmsg(DB_HANDLE handle)
int DbErrno(DB_HANDLE handle)
void DbFreeRow(DB_ROW row)
int DbExecuteSql(DB_HANDLE handle, const char *stmt_str, DB_RESULT *result)
int DbStringBuffer(DB_ROW row, int field_index, char *buffer, size_t buflen)
int StrStrtoi(const char *string, int *value)
Definition: string_util2.c:506
int DbIntQuery(DB_HANDLE handle, int *value, const char *query)
int DbDateDiff(const char *start, int delta, int sign, char *buffer, size_t buflen)
int DbUnsignedLong(DB_ROW row, int field_index, unsigned long *value)
#define DB_ROW_MAGIC
Definition: database.h:97
int DbRowId(DB_ROW row, DB_ID *id)
#define DBS_NORESULT
Definition: dbsdef.h:50
int DbInt(DB_ROW row, int field_index, int *value)
#define DBS_NOSUCHROW
Definition: dbsdef.h:51
#define DBS_NOTINT
Definition: dbsdef.h:54
int StrStrtoul(const char *string, unsigned long *value)
Definition: string_util2.c:447
#define DBS_UNEXRES
Definition: dbsdef.h:59
int DbExecuteSqlNoResult(DB_HANDLE handle, const char *stmt_str)
unsigned int magic
Definition: database.h:94
void DbStringFree(char *string)