Debian Codegraph @

database/sqlite/sqlite.c

raw ◀ ▶
1 Debian patch touch this file
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 perform 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