| 1 |
/* sqlite.c -- Generic functions to simplify SQLite3 queries |
| 2 |
* |
| 3 |
* GPLv2 only - Copyright (C) 2008 - 2012 |
| 4 |
* David Sommerseth <[email protected]> |
| 5 |
* |
| 6 |
* This program is free software; you can redistribute it and/or |
| 7 |
* modify it under the terms of the GNU General Public License |
| 8 |
* as published by the Free Software Foundation; version 2 |
| 9 |
* of the License. |
| 10 |
* |
| 11 |
* This program is distributed in the hope that it will be useful, |
| 12 |
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 13 |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| 14 |
* GNU General Public License for more details. |
| 15 |
* |
| 16 |
* You should have received a copy of the GNU General Public License |
| 17 |
* along with this program; if not, write to the Free Software |
| 18 |
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. |
| 19 |
* |
| 20 |
*/ |
| 21 |
|
| 22 |
/** |
| 23 |
* @file sqlite.c |
| 24 |
* @author David Sommerseth <[email protected]> |
| 25 |
* @date 2008-08-06 |
| 26 |
* |
| 27 |
* @brief Generic functions to simplify the SQLite3 integration. |
| 28 |
* |
| 29 |
*/ |
| 30 |
|
| 31 |
#include <string.h> |
| 32 |
#include <stdlib.h> |
| 33 |
#include <assert.h> |
| 34 |
#include <sqlite3.h> |
| 35 |
#include <time.h> |
| 36 |
|
| 37 |
#ifdef HAVE_LIBXML2 |
| 38 |
# include <libxml/tree.h> |
| 39 |
#endif |
| 40 |
|
| 41 |
#include <eurephia_log.h> |
| 42 |
#include <eurephia_nullsafe.h> |
| 43 |
#include <eurephia_directions.h> |
| 44 |
#include <eurephiadb_mapping.h> |
| 45 |
|
| 46 |
#include "./sqlite.h" |
| 47 |
|
| 48 |
/** |
| 49 |
* @{ |
| 50 |
*/ |
| 51 |
#define btWHERE 0x001 |
| 52 |
#define btINSERT 0x002 |
| 53 |
#define btUPDATE 0x004 |
| 54 |
/** |
| 55 |
* @} |
| 56 |
*/ |
| 57 |
|
| 58 |
/** |
| 59 |
* Internal function for setting the error fields. |
| 60 |
*/ |
| 61 |
static void _sqlite_set_error(dbresult *dbres, ErrorSeverity sev, const char *query, const char *fmt, ...) |
| 62 |
{ |
| 63 |
char errbuf[4098]; |
| 64 |
va_list ap; |
| 65 |
|
| 66 |
va_start(ap, fmt); |
| 67 |
memset(&errbuf, 0, 4098); |
| 68 |
vsnprintf(errbuf, 4096, fmt, ap); |
| 69 |
va_end(ap); |
| 70 |
|
| 71 |
dbres->errSeverity = sev; |
| 72 |
dbres->errMsg = strdup(errbuf); |
| 73 |
dbres->query = strdup_nullsafe(query); |
| 74 |
} |
| 75 |
|
| 76 |
/** |
| 77 |
* Internal function. Free up a dbresult structure. This function is available via the |
| 78 |
* the public macro sqlite_free_results() which is defined in sqlite.h |
| 79 |
* |
| 80 |
* @param inres Pointer to the dbresult structure to be freed |
| 81 |
*/ |
| 82 |
void _sqlite_free_results(dbresult *inres) |
| 83 |
{ |
| 84 |
_sqlite_tuples *tup = NULL, *fld = NULL; |
| 85 |
_sqlite_header *hdr = NULL; |
| 86 |
|
| 87 |
if( inres == NULL ) { |
| 88 |
return; |
| 89 |
} |
| 90 |
|
| 91 |
// Remove all tuples from memory |
| 92 |
if( inres->tuples != NULL ) { |
| 93 |
tup = inres->tuples; |
| 94 |
do { |
| 95 |
fld = tup->nextfield; |
| 96 |
// Remove all fields from the current tuple recird |
| 97 |
do { |
| 98 |
if( fld != fld->prevfield ) { |
| 99 |
fld = fld->nextfield; |
| 100 |
free_nullsafe(NULL, fld->prevfield->value); |
| 101 |
free_nullsafe(NULL, fld->prevfield); |
| 102 |
} |
| 103 |
} while( fld != tup ); |
| 104 |
tup = tup->nexttuple; |
| 105 |
free_nullsafe(NULL, fld->value); |
| 106 |
free_nullsafe(NULL, fld); |
| 107 |
} while( tup != inres->tuples ); |
| 108 |
} |
| 109 |
|
| 110 |
// Remove all header information |
| 111 |
if( inres->headerrec != NULL ) { |
| 112 |
hdr = inres->headerrec->next; |
| 113 |
do { |
| 114 |
if( hdr != hdr->prev ) { |
| 115 |
hdr = hdr->next; |
| 116 |
free_nullsafe(NULL, hdr->prev->name); |
| 117 |
free_nullsafe(NULL, hdr->prev); |
| 118 |
} |
| 119 |
} while( hdr != inres->headerrec ); |
| 120 |
free_nullsafe(NULL, hdr->name); |
| 121 |
free_nullsafe(NULL, hdr); |
| 122 |
} |
| 123 |
|
| 124 |
// Remove error messages and copy of the SQL query |
| 125 |
inres->status = dbEMPTY; |
| 126 |
free_nullsafe(NULL, inres->errMsg); |
| 127 |
free_nullsafe(NULL, inres->query); |
| 128 |
free_nullsafe(NULL, inres); |
| 129 |
} |
| 130 |
|
| 131 |
/** |
| 132 |
* Callback function used by sqlite3_exec - populates a global variable glob_results. |
| 133 |
* The glob_results structure is allocated with a new address on each sqlite_query call |
| 134 |
* |
| 135 |
* @param resultptr Pointer to a dbresult struct where the query result will be saved |
| 136 |
* @param argc Number of fields in the result |
| 137 |
* @param argv Array pointer to the values of the fields |
| 138 |
* @param colName Array pointer to the field names (column names) |
| 139 |
* |
| 140 |
* @return SQLite3 defines 0 for no errors, and 1 for errors. |
| 141 |
*/ |
| 142 |
static int _cb_parse_result(void *resultptr, int argc, char **argv, char **colName) |
| 143 |
{ |
| 144 |
// This callback function is called for each row returned by the query |
| 145 |
_sqlite_header *hrec = NULL; |
| 146 |
_sqlite_tuples *trec = NULL, *new_frec = NULL; |
| 147 |
int i; |
| 148 |
dbresult *dbres = (dbresult *) resultptr; |
| 149 |
|
| 150 |
if( dbres == NULL ) { |
| 151 |
return 1; |
| 152 |
} |
| 153 |
|
| 154 |
// If no header records are found, populate this structure |
| 155 |
if( dbres->headerrec == NULL ) { |
| 156 |
for( i = 0; i < argc; i++ ) { |
| 157 |
hrec = (_sqlite_header *) malloc_nullsafe(NULL, sizeof(_sqlite_header)+2); |
| 158 |
|
| 159 |
hrec->fieldid = i; |
| 160 |
hrec->name = strdup_nullsafe(colName[i]); |
| 161 |
hrec->namelength = strlen_nullsafe(hrec->name); |
| 162 |
|
| 163 |
// Append the new record to the "end" of the |
| 164 |
// circular pointer chain for header info |
| 165 |
if( dbres->headerrec == NULL ) { |
| 166 |
dbres->headerrec = hrec; |
| 167 |
hrec->next = hrec; |
| 168 |
hrec->prev = hrec; |
| 169 |
} else { |
| 170 |
hrec->next = dbres->headerrec; |
| 171 |
hrec->prev = dbres->headerrec->prev; |
| 172 |
dbres->headerrec->prev->next = hrec; |
| 173 |
dbres->headerrec->prev = hrec; |
| 174 |
} |
| 175 |
} |
| 176 |
dbres->num_fields = argc; |
| 177 |
} |
| 178 |
|
| 179 |
// Add all data fields for this record into a tuple structure |
| 180 |
hrec = dbres->headerrec; |
| 181 |
for( i = 0; i < argc; i++ ) { |
| 182 |
new_frec = (_sqlite_tuples *) malloc_nullsafe(NULL, sizeof(_sqlite_tuples)+2); |
| 183 |
|
| 184 |
// trec contains the head of a new record set with fields |
| 185 |
if( trec == NULL ) { |
| 186 |
// Pointer to the first record in the tupleset |
| 187 |
trec = new_frec; |
| 188 |
|
| 189 |
// Add this header to the "end" of the |
| 190 |
// circular pointer chain for tuples |
| 191 |
// |
| 192 |
// These pointers directs you to the next or previous |
| 193 |
// record set of data fields |
| 194 |
if( dbres->tuples == NULL ) { |
| 195 |
dbres->tuples = trec; |
| 196 |
trec->nexttuple = trec; |
| 197 |
trec->prevtuple = trec; |
| 198 |
} else { |
| 199 |
trec->nexttuple = dbres->tuples; |
| 200 |
trec->prevtuple = dbres->tuples->prevtuple; |
| 201 |
dbres->tuples->prevtuple->nexttuple = trec; |
| 202 |
dbres->tuples->prevtuple = trec; |
| 203 |
} |
| 204 |
} |
| 205 |
|
| 206 |
new_frec->tupleid = dbres->num_tuples; |
| 207 |
new_frec->fieldid = i; |
| 208 |
new_frec->value = strdup_nullsafe(argv[i]); |
| 209 |
new_frec->length = strlen_nullsafe(trec->value); |
| 210 |
|
| 211 |
new_frec->nexttuple = trec->nexttuple; |
| 212 |
new_frec->prevtuple = trec->prevtuple; |
| 213 |
|
| 214 |
// Add a pointer to the header structure as well, but only |
| 215 |
// if we are on the same ID with with header structure as |
| 216 |
// in the data field structure |
| 217 |
if( hrec->fieldid == i ) { |
| 218 |
new_frec->header = hrec; |
| 219 |
} |
| 220 |
|
| 221 |
if( new_frec->length > hrec->maxvaluelength ) { |
| 222 |
hrec->maxvaluelength = new_frec->length; |
| 223 |
} |
| 224 |
|
| 225 |
// These pointers directs you to the next or previous |
| 226 |
// field in this record set |
| 227 |
if( trec->nextfield == NULL ) { |
| 228 |
trec->nextfield = new_frec; |
| 229 |
trec->prevfield = new_frec; |
| 230 |
} else { |
| 231 |
new_frec->nextfield = trec; |
| 232 |
new_frec->prevfield = trec->prevfield; |
| 233 |
trec->prevfield->nextfield = new_frec; |
| 234 |
trec->prevfield = new_frec; |
| 235 |
} |
| 236 |
|
| 237 |
// Get ready for the next field - find the next header record. |
| 238 |
hrec = hrec->next; |
| 239 |
} |
| 240 |
// Increase the tuple counter |
| 241 |
dbres->num_tuples++; |
| 242 |
|
| 243 |
return 0; |
| 244 |
} |
| 245 |
|
| 246 |
|
| 247 |
/** |
| 248 |
* Simple date/time function which converts UTC to local time |
| 249 |
* |
| 250 |
* @param ctx SQLite database connection context |
| 251 |
* @param argc Number of arguments available |
| 252 |
* @param argv Argument list |
| 253 |
* |
| 254 |
*/ |
| 255 |
void _sqlitefnc_localdatetime(sqlite3_context *context, int argc, sqlite3_value **argv) |
| 256 |
{ |
| 257 |
struct tm tm, loctm; |
| 258 |
time_t t; |
| 259 |
char buf[255]; |
| 260 |
|
| 261 |
assert( argc == 1); |
| 262 |
|
| 263 |
switch(sqlite3_value_type(argv[0])) { |
| 264 |
case SQLITE_NULL: |
| 265 |
/* NULL in is NULL out */ |
| 266 |
sqlite3_result_null(context); |
| 267 |
break; |
| 268 |
|
| 269 |
case SQLITE_TEXT: |
| 270 |
memset(&tm, 0, sizeof(struct tm)); |
| 271 |
memset(&t, 0, sizeof(time_t)); |
| 272 |
memset(&buf, 0, sizeof(buf)); |
| 273 |
|
| 274 |
/* Convert the input datetime string to time_t format, expect it to be UTC/GMT */ |
| 275 |
strptime((const char *)sqlite3_value_text(argv[0]), "%Y-%m-%d %H:%M:%S", &tm); |
| 276 |
t = timegm(&tm); |
| 277 |
|
| 278 |
/* Convert from UTC/GMT to localtime and format the new output string */ |
| 279 |
localtime_r(&t, &loctm); |
| 280 |
strftime(buf, sizeof(buf), "%Y-%m-%d %H:%M:%S", &loctm); |
| 281 |
sqlite3_result_text(context, buf, strlen(buf), SQLITE_TRANSIENT); |
| 282 |
break; |
| 283 |
} |
| 284 |
} |
| 285 |
|
| 286 |
|
| 287 |
/** |
| 288 |
* Initialise the SQLite database with additional functions usable via SQL |
| 289 |
* |
| 290 |
* @param ctx eurephia context with the opened database |
| 291 |
* |
| 292 |
* @return Returns dbSUCCESS on success, otherwise dbERROR. |
| 293 |
*/ |
| 294 |
int sqlite_init_functions(eurephiaCTX *ctx) |
| 295 |
{ |
| 296 |
int rc; |
| 297 |
|
| 298 |
/* locdt(TIMESTAMP): datetime function which converts UTC/GMT time stamps into |
| 299 |
* the localtime zone. Expects the format to be 'YYYY-MM-DD HH24:MI:SS'. |
| 300 |
* Used by the administration functions to return the correct local time |
| 301 |
*/ |
| 302 |
rc = sqlite3_create_function(ctx->dbc->dbhandle, "locdt", 1, SQLITE_ANY, |
| 303 |
NULL, &_sqlitefnc_localdatetime, NULL, NULL); |
| 304 |
if( rc != SQLITE_OK ) { |
| 305 |
eurephia_log(ctx, LOG_PANIC, 0, "Failed to register local date/time function (%i)", rc); |
| 306 |
return dbERROR; |
| 307 |
} |
| 308 |
|
| 309 |
return dbSUCCESS; |
| 310 |
} |
| 311 |
|
| 312 |
|
| 313 |
/** |
| 314 |
* Logs sqlite error messages via the eurephia log interface and returns an XML node with details |
| 315 |
* (This function is only included if libxml2 is available) |
| 316 |
* |
| 317 |
* @param eurephiaCTX* eurephia context pointer, where logging will be performed |
| 318 |
* @param dbresult* Pointer to the database result with the error message |
| 319 |
* |
| 320 |
*/ |
| 321 |
void sqlite_log_error(eurephiaCTX *ctx, dbresult *dbres) { |
| 322 |
if( dbres == NULL ) { |
| 323 |
eurephia_log(ctx, LOG_CRITICAL, 2, "Unknown error (NULL result)"); |
| 324 |
return; |
| 325 |
} |
| 326 |
|
| 327 |
if( dbres->status != dbSUCCESS ) { |
| 328 |
eurephia_log(ctx, LOG_ERROR, 4, "SQL Error: %s", dbres->errMsg); |
| 329 |
} |
| 330 |
DEBUG(ctx, 33, "SQL Query: %s", (dbres->query != NULL ? dbres->query : "(None)")); |
| 331 |
} |
| 332 |
|
| 333 |
|
| 334 |
/** |
| 335 |
* Logs sqlite error messages via the eurephia log interface and returns an XML node with details |
| 336 |
* (This function is only included if libxml2 is available) |
| 337 |
* |
| 338 |
* @param eurephiaCTX* eurephia context pointer, where logging will be performed |
| 339 |
* @param dbresult* Pointer to the database result with the error message |
| 340 |
* |
| 341 |
* @return An xmlNode pointer with more details of the error. |
| 342 |
*/ |
| 343 |
#ifdef HAVE_LIBXML2 |
| 344 |
xmlNode *sqlite_log_error_xml(eurephiaCTX *ctx, dbresult *dbres ) { |
| 345 |
xmlNode *ret = NULL; |
| 346 |
|
| 347 |
sqlite_log_error(ctx, dbres); |
| 348 |
if( dbres == NULL ) { |
| 349 |
return NULL; |
| 350 |
} |
| 351 |
|
| 352 |
ret = xmlNewNode(NULL, (xmlChar *) "SQLError"); |
| 353 |
if( ret != NULL ) { |
| 354 |
xmlNode *err_n = NULL; |
| 355 |
xmlChar *errstr = NULL; |
| 356 |
const xmlChar *ErrorSeverity_str[] = { |
| 357 |
(xmlChar *) "Warning", |
| 358 |
(xmlChar *) "Error", |
| 359 |
(xmlChar *) "Critical", |
| 360 |
(xmlChar *) "PANIC", |
| 361 |
NULL |
| 362 |
}; |
| 363 |
|
| 364 |
xmlNewProp(ret, (xmlChar *) "driver", (xmlChar *) "edb-sqlite.so"); |
| 365 |
errstr = xmlCharStrdup(dbres->errMsg); |
| 366 |
err_n = xmlNewTextChild(ret, NULL, (xmlChar *) "ErrorMessage", errstr); |
| 367 |
xmlNewProp(err_n, (xmlChar *) "severity", ErrorSeverity_str[dbres->errSeverity]); |
| 368 |
free_nullsafe(NULL, errstr); |
| 369 |
} |
| 370 |
return ret; |
| 371 |
} |
| 372 |
#endif |
| 373 |
|
| 374 |
|
| 375 |
/** |
| 376 |
* Main function for query a SQLite3 database. |
| 377 |
* |
| 378 |
* @param ctx eurephiaCTX |
| 379 |
* @param fmt The SQL query. It supports stdarg to build up dynamic queries directly. |
| 380 |
* |
| 381 |
* @return Returns a pointer to a dbresult struct on success, otherwise NULL. |
| 382 |
*/ |
| 383 |
dbresult *sqlite_query(eurephiaCTX *ctx, const char *fmt, ... ) { |
| 384 |
int rc; |
| 385 |
va_list ap; |
| 386 |
char *errMsg = NULL, *sql = NULL; |
| 387 |
eDBconn *dbc = ctx->dbc; |
| 388 |
dbresult *dbres = NULL; |
| 389 |
|
| 390 |
// prepare a new result set ... |
| 391 |
dbres = malloc_nullsafe(ctx, sizeof(dbresult)+2); |
| 392 |
dbres->status = dbEMPTY; |
| 393 |
dbres->num_tuples = 0; |
| 394 |
|
| 395 |
// prepare SQL query |
| 396 |
va_start(ap, fmt); |
| 397 |
sql = sqlite3_vmprintf(fmt, ap); |
| 398 |
va_end(ap); |
| 399 |
|
| 400 |
if( sql == NULL ) { |
| 401 |
_sqlite_set_error(dbres, sevPANIC, NULL, "Could not allocate memory for SQL query string"); |
| 402 |
goto exit; |
| 403 |
} |
| 404 |
|
| 405 |
|
| 406 |
if( ctx->dbc == NULL ) { |
| 407 |
_sqlite_set_error(dbres, sevPANIC, sql, "No open database connection to perfom SQL query to"); |
| 408 |
goto exit; |
| 409 |
} |
| 410 |
|
| 411 |
if( ctx->context_type == ECTX_NO_PRIVILEGES ) { |
| 412 |
_sqlite_set_error(dbres, sevCRITICAL, sql, "Database query attempted from wrong context"); |
| 413 |
goto exit; |
| 414 |
} |
| 415 |
|
| 416 |
DEBUG(ctx, 25, "Doing SQL Query: %s", sql); |
| 417 |
rc = sqlite3_exec( (sqlite3 *) dbc->dbhandle, sql, _cb_parse_result, dbres, &errMsg ); |
| 418 |
if( rc != SQLITE_OK ) { |
| 419 |
_sqlite_set_error(dbres, (dbres->num_tuples > 0 ? sevWARNING : sevERROR), sql, "%s", errMsg); |
| 420 |
sqlite3_free(errMsg); errMsg = NULL; |
| 421 |
goto exit; |
| 422 |
} |
| 423 |
|
| 424 |
if( strcasestr(sql, "INSERT INTO") != 0) { |
| 425 |
dbres->last_insert_id = sqlite3_last_insert_rowid((sqlite3 *) dbc->dbhandle); |
| 426 |
}; |
| 427 |
if( strcasestr(sql, "SELECT ") == 0 ) { |
| 428 |
// If not a SELECT query, then get the number of affected records |
| 429 |
dbres->affected_rows = sqlite3_changes((sqlite3 *) dbc->dbhandle); |
| 430 |
} |
| 431 |
|
| 432 |
dbres->status = dbSUCCESS; |
| 433 |
dbres->srch_tuples = dbres->tuples; |
| 434 |
dbres->srch_headerrec = dbres->headerrec; |
| 435 |
exit: |
| 436 |
sqlite3_free(sql); sql = NULL; |
| 437 |
|
| 438 |
return dbres; |
| 439 |
} |
| 440 |
|
| 441 |
/** |
| 442 |
* Internal function. Checks if the input string contains reserved words |
| 443 |
* |
| 444 |
* @param reserved_words Array containing the reserver words |
| 445 |
* @param val Value to check against the reserved words |
| 446 |
* |
| 447 |
* @return Returns 1 if a reserverd word is found, otherwise 0 |
| 448 |
*/ |
| 449 |
inline int SQLreservedWord(char **reserved_words, const char *val) { |
| 450 |
int i; |
| 451 |
|
| 452 |
// If we find the word in val defined in the reserved_words array, return 1. |
| 453 |
for( i = 0; reserved_words[i] != NULL; i++ ) { |
| 454 |
if( (val != NULL) && strcmp(val, reserved_words[i]) == 0) { |
| 455 |
return 1; |
| 456 |
} |
| 457 |
} |
| 458 |
return 0; |
| 459 |
} |
| 460 |
|
| 461 |
|
| 462 |
/** |
| 463 |
* Internal function. Creates a string with the value from a particular field in a eDBfieldMap and |
| 464 |
* formats it properly according to its field type. |
| 465 |
* |
| 466 |
* @param ptr Pointer to the field in the eDBfieldMap |
| 467 |
* |
| 468 |
* @return Returns a string to be used within an SQL query. |
| 469 |
*/ |
| 470 |
static char *_build_value_string(eDBfieldMap *ptr) { |
| 471 |
char *reserved_datetime[] = {"CURRENT_TIMESTAMP", "CURRENT_TIME", "CURRENT_DATE", NULL}; |
| 472 |
char *val = NULL; |
| 473 |
|
| 474 |
// Format the input value according to the defined data type |
| 475 |
switch( ptr->field_type ) { |
| 476 |
case ft_INT: |
| 477 |
// Format integer values as integers |
| 478 |
val = sqlite3_mprintf("%i", atoi_nullsafe(ptr->value)); |
| 479 |
break; |
| 480 |
|
| 481 |
case ft_DATETIME: |
| 482 |
// If the value is a reserved SQL word, don't encapsulate the value |
| 483 |
if( SQLreservedWord(reserved_datetime, ptr->value) == 1 ) { |
| 484 |
val = sqlite3_mprintf("%q", ptr->value); |
| 485 |
} else { |
| 486 |
val = sqlite3_mprintf("'%q'", ptr->value); |
| 487 |
} |
| 488 |
break; |
| 489 |
|
| 490 |
case ft_SETNULL: |
| 491 |
val = sqlite3_mprintf("NULL"); |
| 492 |
break; |
| 493 |
|
| 494 |
case ft_STRING_LOWER: |
| 495 |
val = sqlite3_mprintf("lower('%q')", ptr->value); |
| 496 |
break; |
| 497 |
|
| 498 |
case ft_PASSWD: |
| 499 |
case ft_STRING: |
| 500 |
default: |
| 501 |
val = sqlite3_mprintf("'%q'", ptr->value); |
| 502 |
break; |
| 503 |
} |
| 504 |
return val; |
| 505 |
} |
| 506 |
|
| 507 |
|
| 508 |
/** |
| 509 |
* Internal function. Builds up a part of an SQL query, depending on the buildType. The values |
| 510 |
* are taken from a pointer to an eDBfieldMap keeping the values |
| 511 |
* |
| 512 |
* @param btyp Must be btWHERE, btINSERT or btUPDATE |
| 513 |
* @param map Pointer to a eDBfieldMap chain containing the values |
| 514 |
* |
| 515 |
* @return Returns a string with parts of the SQL query containing the dynamic variables on success, |
| 516 |
* otherwise NULL is returned. |
| 517 |
*/ |
| 518 |
static char *_build_sqlpart(int btyp, eDBfieldMap *map) { |
| 519 |
eDBfieldMap *ptr = NULL; |
| 520 |
char *ret = NULL; |
| 521 |
char *fsep = NULL; |
| 522 |
char fields[4094], vals[4094]; |
| 523 |
char buf[8196]; |
| 524 |
int first = 1; |
| 525 |
|
| 526 |
memset(&buf, 0, 8196); |
| 527 |
switch( btyp ) { |
| 528 |
case btWHERE: |
| 529 |
case btUPDATE: |
| 530 |
if( btyp == btWHERE ) { |
| 531 |
fsep = " AND "; |
| 532 |
append_str(buf, "(", 8192); |
| 533 |
} else { |
| 534 |
fsep = ","; |
| 535 |
} |
| 536 |
for( ptr = map; ptr != NULL; ptr = ptr->next ) { |
| 537 |
char *val = NULL; |
| 538 |
|
| 539 |
// If the value is NULL, ignore it if we're building up WHERE clause |
| 540 |
// or if we're doing an update and the field_type is set to SETNULL (we will set NULL) |
| 541 |
if( (ptr->value == NULL) && (btyp == btWHERE || ptr->field_type != ft_SETNULL) ) { |
| 542 |
continue; |
| 543 |
} |
| 544 |
|
| 545 |
// Add separator in front of next value |
| 546 |
if( first != 1 ) { |
| 547 |
append_str(buf, fsep, 8192); |
| 548 |
} |
| 549 |
|
| 550 |
// Put the pieces together and append it to the final result |
| 551 |
val = _build_value_string(ptr); |
| 552 |
if( (btyp == btWHERE) && (ptr->table_alias != NULL) ) { |
| 553 |
append_str(buf, ptr->table_alias, 8192); |
| 554 |
append_str(buf, ".", 8192); |
| 555 |
} |
| 556 |
if( (btyp == btWHERE) && ptr->field_type == ft_STRING_LOWER ) { |
| 557 |
append_str(buf, "lower(", 8192); |
| 558 |
append_str(buf, ptr->field_name, 8192); |
| 559 |
append_str(buf, ")", 8192); |
| 560 |
} else { |
| 561 |
append_str(buf, ptr->field_name, 8192); |
| 562 |
} |
| 563 |
|
| 564 |
switch( ptr->filter_type ) { |
| 565 |
case flt_LT: |
| 566 |
append_str(buf, "<", 8192); break; |
| 567 |
case flt_LTE: |
| 568 |
append_str(buf, "<=", 8192); break; |
| 569 |
case flt_GT: |
| 570 |
append_str(buf, ">", 8192); break; |
| 571 |
case flt_GTE: |
| 572 |
append_str(buf, ">=", 8192); break; |
| 573 |
case flt_NEQ: |
| 574 |
append_str(buf, "!=", 8192); break; |
| 575 |
|
| 576 |
case flt_EQ: |
| 577 |
default: |
| 578 |
append_str(buf, "=", 8192); break; |
| 579 |
} |
| 580 |
append_str(buf, val, 8192); |
| 581 |
sqlite3_free(val); |
| 582 |
first = 0; |
| 583 |
} |
| 584 |
if( btyp == btWHERE ) { |
| 585 |
if( (strlen_nullsafe(buf) > 1) ) { |
| 586 |
append_str(buf, ")", 8192); |
| 587 |
} else { |
| 588 |
*buf = '\0'; |
| 589 |
} |
| 590 |
} |
| 591 |
break; |
| 592 |
|
| 593 |
case btINSERT: // (field_name, field_name, field_name) VALUES ('val','val','val') |
| 594 |
memset(&fields, 0, 4094); |
| 595 |
memset(&vals, 0, 4094); |
| 596 |
|
| 597 |
first = 1; |
| 598 |
for( ptr = map; ptr != NULL; ptr = ptr->next ) { |
| 599 |
char *val = NULL; |
| 600 |
|
| 601 |
// If the value is NULL, ignore it |
| 602 |
if( ptr->value == NULL ) { |
| 603 |
continue; |
| 604 |
} |
| 605 |
|
| 606 |
// Put separator in front of values and field names |
| 607 |
if( first == 0 ) { |
| 608 |
append_str(fields, ",", 8192); |
| 609 |
append_str(vals, ",", 8192); |
| 610 |
} |
| 611 |
|
| 612 |
// Append the current field name and value to each string |
| 613 |
append_str(fields, ptr->field_name, 4092); |
| 614 |
val = _build_value_string(ptr); |
| 615 |
append_str(vals, val, 4092); |
| 616 |
sqlite3_free(val); |
| 617 |
first = 0; |
| 618 |
} |
| 619 |
|
| 620 |
// Put all the pieces together |
| 621 |
append_str(buf, "(", 8192); |
| 622 |
append_str(buf, fields, 8192); |
| 623 |
append_str(buf, ") VALUES (", 8192); |
| 624 |
append_str(buf, vals, 8192); |
| 625 |
append_str(buf, ")", 8192); |
| 626 |
break; |
| 627 |
} |
| 628 |
|
| 629 |
ret = strdup_nullsafe(buf); |
| 630 |
return ret; |
| 631 |
} |
| 632 |
|
| 633 |
|
| 634 |
/** |
| 635 |
* Queries an SQLite3 database by using an SQL stub plus values from a eDBfieldMap pointer chain |
| 636 |
* |
| 637 |
* @param ctx eurephiaCTX |
| 638 |
* @param qType Query type, must be SQL_SELECT, SQL_INSERT, SQL_UPDATE or SQL_DELETE |
| 639 |
* @param sqlstub The generic part of the SQL query |
| 640 |
* @param valMap Values to be used in INSERT or UPDATE queries. May be NULL if not SQL_INSERT or SQL_UPDATE |
| 641 |
* @param whereMap Values to be used in the WHERE section of the SQL query |
| 642 |
* @param sortkeys Only for SQL_SELECT, list of field names to sort the result by. |
| 643 |
* |
| 644 |
* @return Returns a pointer to a dbresult struct on success, otherwise NULL. |
| 645 |
*/ |
| 646 |
dbresult *sqlite_query_mapped(eurephiaCTX *ctx, SQLqueryType qType, const char *sqlstub, |
| 647 |
eDBfieldMap *valMap, eDBfieldMap *whereMap, const char *sortkeys) |
| 648 |
{ |
| 649 |
dbresult *res = NULL; |
| 650 |
char *tmp1 = NULL, *tmp2 = NULL; |
| 651 |
|
| 652 |
assert((ctx != NULL) && (sqlstub != NULL)); |
| 653 |
|
| 654 |
// Build up SQL clause and send the query |
| 655 |
switch( qType ) { |
| 656 |
case SQL_SELECT: |
| 657 |
case SQL_DELETE: |
| 658 |
if( whereMap != NULL ) { |
| 659 |
tmp1 = _build_sqlpart(btWHERE, whereMap); |
| 660 |
if( sortkeys == NULL ) { |
| 661 |
res = sqlite_query(ctx, "%s %s %s", |
| 662 |
sqlstub, (strlen_nullsafe(tmp1) > 0 ? "WHERE" : ""), tmp1); |
| 663 |
} else { |
| 664 |
res = sqlite_query(ctx, "%s %s %s ORDER BY %s", |
| 665 |
sqlstub, (strlen_nullsafe(tmp1) > 0 ? "WHERE" : ""), tmp1, |
| 666 |
sortkeys); |
| 667 |
} |
| 668 |
free_nullsafe(ctx, tmp1); |
| 669 |
} |
| 670 |
break; |
| 671 |
|
| 672 |
case SQL_UPDATE: |
| 673 |
if( (whereMap != NULL) && (valMap != NULL) ) { |
| 674 |
tmp1 = _build_sqlpart(btUPDATE, valMap); |
| 675 |
tmp2 = _build_sqlpart(btWHERE, whereMap); |
| 676 |
|
| 677 |
res = sqlite_query(ctx, "%s SET %s WHERE %s", sqlstub, tmp1, tmp2); |
| 678 |
free_nullsafe(ctx, tmp1); |
| 679 |
free_nullsafe(ctx, tmp2); |
| 680 |
} |
| 681 |
break; |
| 682 |
|
| 683 |
case SQL_INSERT: |
| 684 |
tmp1 = _build_sqlpart(btINSERT, valMap); |
| 685 |
res = sqlite_query(ctx, "%s %s", sqlstub, tmp1); |
| 686 |
free_nullsafe(ctx, tmp1); |
| 687 |
break; |
| 688 |
} |
| 689 |
|
| 690 |
// Send the SQL query to the database and return the result |
| 691 |
return res; |
| 692 |
} |
| 693 |
|
| 694 |
/** |
| 695 |
* Very simple dbresult dumper. Will list out all records to the given FILE destination. |
| 696 |
* |
| 697 |
* @param dmp pointer to a FILE stream |
| 698 |
* @param res pointe to the dbresults to be dumped |
| 699 |
*/ |
| 700 |
void sqlite_dump_result(FILE *dmp, dbresult *res) { |
| 701 |
_sqlite_tuples *row = NULL, *field = NULL; |
| 702 |
|
| 703 |
if( (res == NULL) || (res->tuples == NULL) ) { |
| 704 |
fprintf(dmp, "(No records found)\n"); |
| 705 |
return; |
| 706 |
} |
| 707 |
|
| 708 |
/* |
| 709 |
hdr = (char *) malloc( ((res->maxlen_colname + res->maxlen_colvalue - 6) / 2) + 2 ); |
| 710 |
memset(hdr, 0, ((res->maxlen_colname + res->maxlen_colvalue - 6) / 2) + 2 ); |
| 711 |
memset(hdr, '-', ((res->maxlen_colname + res->maxlen_colvalue - 6) / 2)); |
| 712 |
*/ |
| 713 |
|
| 714 |
row = res->tuples; |
| 715 |
do { |
| 716 |
fprintf(dmp, "** Record %i\n", row->tupleid); |
| 717 |
field = row; |
| 718 |
do { |
| 719 |
fprintf(dmp, "(%i) %s | %s\n", field->fieldid, field->header->name, field->value); |
| 720 |
field = field->nextfield; |
| 721 |
} while ( field != row); |
| 722 |
row = row->nexttuple; |
| 723 |
fprintf(dmp, "-----------------------------------------------------\n"); |
| 724 |
} while( row != res->tuples); |
| 725 |
fprintf(dmp, "-----------------------------------------------------\n(%i records found)\n", |
| 726 |
(int) res->num_tuples); |
| 727 |
// fprintf(dmp, "%s----------%s\n(%i records found)\n\n", hdr, hdr, res->num_tuples); |
| 728 |
// free(hdr); |
| 729 |
} |
| 730 |
|
| 731 |
/** |
| 732 |
* Retrieves a particular value out of a dbresult |
| 733 |
* |
| 734 |
* @param res Pointer to dbresult |
| 735 |
* @param row Row number (record number) to extract |
| 736 |
* @param col Column number (field number) to extract |
| 737 |
* |
| 738 |
* @return Returns a pointer to the value if found, otherwise NULL is returned. |
| 739 |
* @remark This function returns a pointer to the value insisde the dbresult struct. If you want to use |
| 740 |
* this value after freeing the dbresult, you must make sure you copy this value yourself. |
| 741 |
*/ |
| 742 |
char *sqlite_get_value(dbresult *res, int row, int col) { |
| 743 |
_sqlite_tuples *ptr = res->srch_tuples; |
| 744 |
|
| 745 |
if( (ptr == NULL) || (row > res->num_tuples) || (col > res->num_fields) ) { |
| 746 |
return NULL; |
| 747 |
} |
| 748 |
|
| 749 |
do { |
| 750 |
if( ptr->tupleid == row ) { |
| 751 |
do { |
| 752 |
if( ptr->fieldid == col ) { |
| 753 |
res->srch_tuples = ptr; |
| 754 |
return ptr->value; |
| 755 |
} |
| 756 |
ptr = (DIRECTION(ptr->fieldid, col, res->num_fields) == DIR_R |
| 757 |
? ptr->nextfield : ptr->prevfield); |
| 758 |
} while( ptr != res->srch_tuples ) ; |
| 759 |
} |
| 760 |
ptr = (DIRECTION(ptr->tupleid, row, res->num_tuples) == DIR_R ? ptr->nexttuple : ptr->prevtuple); |
| 761 |
} while( ptr != res->srch_tuples ); |
| 762 |
return NULL; |
| 763 |
} |
| 764 |
|
| 765 |
|
| 766 |
#ifdef HAVE_LIBXML2 |
| 767 |
/** |
| 768 |
* Retrieves a particular value out of a dbresult and places it inside an xmlNode pointer. |
| 769 |
* |
| 770 |
* @param node Pointer to the xmlNode |
| 771 |
* @param xmltyp Must be XML_ATTR or XML_NODE. If XML_ATTR it will be added as an attribute/properties |
| 772 |
* to the given xmlNode. If XML_NODE, it will be added as a child to the given xmlNode. |
| 773 |
* @param inname String containing the name of the attribute or XML tag. (Depending on xmltyp) |
| 774 |
* @param res Pointer to dbresult |
| 775 |
* @param row Row number (record number) to extract |
| 776 |
* @param col Column number (field number) to extract |
| 777 |
* |
| 778 |
* @return Returns a pointer to the xmlNode which got updated. If xmltyp == XML_NODE it will point at |
| 779 |
* the newly added node. On failure NULL will be returned. |
| 780 |
*/ |
| 781 |
xmlNodePtr sqlite_xml_value(xmlNodePtr node, xmlFieldType xmltyp, char *inname, dbresult *res, int row, int col) { |
| 782 |
xmlChar *name = NULL, *data = NULL; |
| 783 |
xmlNodePtr retnode = NULL; |
| 784 |
|
| 785 |
name = xmlCharStrdup(inname); |
| 786 |
assert( name != NULL ); |
| 787 |
|
| 788 |
data = xmlCharStrdup(sqlite_get_value(res, row, col)); |
| 789 |
if( xmlStrlen(data) > 0 ) { |
| 790 |
switch( xmltyp ) { |
| 791 |
case XML_ATTR: |
| 792 |
xmlNewProp(node, name, data); |
| 793 |
retnode = node; |
| 794 |
break; |
| 795 |
|
| 796 |
case XML_NODE: |
| 797 |
retnode = xmlNewChild(node, NULL, name, data); |
| 798 |
break; |
| 799 |
|
| 800 |
default: |
| 801 |
retnode = NULL; |
| 802 |
} |
| 803 |
} |
| 804 |
free_nullsafe(NULL, data); |
| 805 |
free_nullsafe(NULL, name); |
| 806 |
return retnode; |
| 807 |
} |
| 808 |
#endif |
| 809 |
|
| 810 |
|
| 811 |
#ifdef SQLITE_DEBUG |
| 812 |
/* |
| 813 |
* Just a simple test program ... to debug this sqlite wrapper |
| 814 |
* |
| 815 |
* To compile it: |
| 816 |
* gcc -o sqlite sqlite.c -lsqlite3 -I ../ -I ../../common/ ../../common/eurephia_log.c ../../common/eurephia_nullsafe.c ../eurephiadb_mapping.c ../../common/eurephia_xml.c `pkg-config libxml-2.0 --cflags --libs` ../../common/passwd.c -DSQLITE_DEBUG ../../common/sha512.c ../../common/eurephia_values.c ../../common/randstr.c `pkg-config openssl --cflags --libs` -Wall -g -DHAVE_LIBXML2 -D_GNU_SOURCE |
| 817 |
*/ |
| 818 |
int main(int argc, char **argv) { |
| 819 |
int rc; |
| 820 |
dbresult *res = NULL; |
| 821 |
eurephiaCTX *ctx; |
| 822 |
|
| 823 |
if( argc != 3 ) { |
| 824 |
fprintf(stderr, "Usage: %s <db file> <sql query>\n", argv[0]); |
| 825 |
return 1; |
| 826 |
} |
| 827 |
|
| 828 |
ctx = malloc_nullsafe(NULL, sizeof(eurephiaCTX)+2); |
| 829 |
ctx->dbc = malloc_nullsafe(NULL, sizeof(eDBconn)+2); |
| 830 |
eurephia_log_init(ctx, "sqlitedbg", "stderr:", 10); |
| 831 |
|
| 832 |
rc = sqlite3_open(argv[1], (void *) &ctx->dbc->dbhandle); |
| 833 |
if( rc ) { |
| 834 |
fprintf(stderr, "Could not open db\n"); |
| 835 |
return 1; |
| 836 |
} |
| 837 |
|
| 838 |
if( sqlite_init_functions(ctx) != dbSUCCESS ) { |
| 839 |
fprintf(stderr, "Failed to register functions\n"); |
| 840 |
return 1; |
| 841 |
} |
| 842 |
|
| 843 |
res = sqlite_query(ctx, argv[2]); |
| 844 |
if( res != NULL ) { |
| 845 |
sqlite_dump_result(stdout, res); |
| 846 |
sqlite_free_results(res); |
| 847 |
} |
| 848 |
sqlite3_close(ctx->dbc->dbhandle); |
| 849 |
free(ctx->dbc); |
| 850 |
eurephia_log_close(ctx); |
| 851 |
free(ctx); |
| 852 |
return 0; |
| 853 |
} |
| 854 |
#endif |