Annotation of gnutrition/db.c, revision 1.1
1.1 ! asm 1: // SPDX-License-Identifier: GPL-3.0-or-later
! 2: /*
! 3: * $Id$
! 4: *
! 5: * db.c - Database access layer for GNUtrition
! 6: *
! 7: * Copyright (C) 2026 Free Software Foundation, Inc.
! 8: *
! 9: * Author: Jason Self <jself@gnu.org>
! 10: */
! 11:
! 12: #include "db.h"
! 13: #include "i18n.h"
! 14:
! 15: #include <stdio.h>
! 16: #include <stdlib.h>
! 17: #include <string.h>
! 18:
! 19: #define INITIAL_CAPACITY 64
! 20:
! 21: sqlite3 *
! 22: db_open (const char *path)
! 23: {
! 24: sqlite3 *db;
! 25: int rc;
! 26:
! 27: /* Note: We rely on SQLite's internal handling of mmap. SQLite attempts
! 28: to mmap and falls back to read/write if it fails, satisfying the
! 29: requirement to handle filesystems that don't support mmap. */
! 30: rc = sqlite3_open_v2 (path, &db, SQLITE_OPEN_READONLY, NULL);
! 31: if (rc != SQLITE_OK)
! 32: {
! 33: fprintf (stderr, _("gnutrition: cannot open database '%s': %s\n"),
! 34: path, sqlite3_errmsg (db));
! 35: sqlite3_close (db);
! 36: return NULL;
! 37: }
! 38: return db;
! 39: }
! 40:
! 41: void
! 42: db_close (sqlite3 *db)
! 43: {
! 44: if (db)
! 45: sqlite3_close (db);
! 46: }
! 47:
! 48: /* Append a food_item to LIST, growing dynamically. Returns 0 on
! 49: success, -1 on allocation failure. */
! 50: static int
! 51: food_list_append (struct food_list *list, int code, const char *desc)
! 52: {
! 53: if (list->count >= list->capacity)
! 54: {
! 55: size_t new_cap;
! 56: struct food_item *tmp;
! 57:
! 58: new_cap = list->capacity == 0 ? INITIAL_CAPACITY : list->capacity * 2;
! 59: tmp = realloc (list->items, new_cap * sizeof *tmp);
! 60: if (!tmp)
! 61: {
! 62: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 63: return -1;
! 64: }
! 65: list->items = tmp;
! 66: list->capacity = new_cap;
! 67: }
! 68:
! 69: list->items[list->count].food_code = code;
! 70: list->items[list->count].description = strdup (desc);
! 71: if (!list->items[list->count].description)
! 72: {
! 73: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 74: return -1;
! 75: }
! 76: list->count++;
! 77: return 0;
! 78: }
! 79:
! 80: int
! 81: db_search_foods (sqlite3 *db, const char *query, struct food_list *results)
! 82: {
! 83: sqlite3_stmt *stmt;
! 84: int rc;
! 85: char *pattern;
! 86: size_t qlen;
! 87:
! 88: results->items = NULL;
! 89: results->count = 0;
! 90: results->capacity = 0;
! 91:
! 92: /* Build a LIKE pattern: %query%. */
! 93: qlen = strlen (query);
! 94: pattern = malloc (qlen + 3);
! 95: if (!pattern)
! 96: {
! 97: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 98: return -1;
! 99: }
! 100: pattern[0] = '%';
! 101: memcpy (pattern + 1, query, qlen);
! 102: pattern[qlen + 1] = '%';
! 103: pattern[qlen + 2] = '\0';
! 104:
! 105: rc = sqlite3_prepare_v2 (db,
! 106: "SELECT \"Food code\", \"Main food description\" "
! 107: "FROM foods "
! 108: "WHERE \"Main food description\" LIKE ?1 "
! 109: "ORDER BY \"Main food description\" "
! 110: "LIMIT 100",
! 111: -1, &stmt, NULL);
! 112:
! 113: if (rc != SQLITE_OK)
! 114: {
! 115: fprintf (stderr, _("gnutrition: SQL error: %s\n"), sqlite3_errmsg (db));
! 116: free (pattern);
! 117: return -1;
! 118: }
! 119:
! 120: sqlite3_bind_text (stmt, 1, pattern, -1, SQLITE_TRANSIENT);
! 121:
! 122: while ((rc = sqlite3_step (stmt)) == SQLITE_ROW)
! 123: {
! 124: int code = sqlite3_column_int (stmt, 0);
! 125: const char *desc = (const char *) sqlite3_column_text (stmt, 1);
! 126: if (food_list_append (results, code, desc ? desc : "") < 0)
! 127: {
! 128: sqlite3_finalize (stmt);
! 129: free (pattern);
! 130: food_list_free (results);
! 131: return -1;
! 132: }
! 133: }
! 134:
! 135: sqlite3_finalize (stmt);
! 136: free (pattern);
! 137:
! 138: if (rc != SQLITE_DONE)
! 139: {
! 140: fprintf (stderr, _("gnutrition: SQL error: %s\n"), sqlite3_errmsg (db));
! 141: food_list_free (results);
! 142: return -1;
! 143: }
! 144:
! 145: return 0;
! 146: }
! 147:
! 148: /* Append a nutrient_info to LIST, growing dynamically. */
! 149: static int
! 150: nutrient_list_append (struct nutrient_list *list, const char *name,
! 151: double value)
! 152: {
! 153: if (list->count >= list->capacity)
! 154: {
! 155: size_t new_cap;
! 156: struct nutrient_info *tmp;
! 157:
! 158: new_cap = list->capacity == 0 ? INITIAL_CAPACITY : list->capacity * 2;
! 159: tmp = realloc (list->items, new_cap * sizeof *tmp);
! 160: if (!tmp)
! 161: {
! 162: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 163: return -1;
! 164: }
! 165: list->items = tmp;
! 166: list->capacity = new_cap;
! 167: }
! 168:
! 169: list->items[list->count].name = strdup (name);
! 170: if (!list->items[list->count].name)
! 171: {
! 172: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 173: return -1;
! 174: }
! 175: list->items[list->count].value = value;
! 176: list->count++;
! 177: return 0;
! 178: }
! 179:
! 180: /* Fatty acid shorthand-to-common-name mapping. The keys are the
! 181: cleaned column names (after newline removal) produced by the USDA
! 182: FNDDS import. */
! 183: static const struct { const char *key; const char *name; } fa_names[] =
! 184: {
! 185: { "4:0(g)", "Butanoic acid (4:0) (g)" },
! 186: { "6:0(g)", "Hexanoic acid (6:0) (g)" },
! 187: { "8:0(g)", "Octanoic acid (8:0) (g)" },
! 188: { "10:0(g)", "Decanoic acid (10:0) (g)" },
! 189: { "12:0(g)", "Dodecanoic acid (12:0) (g)" },
! 190: { "14:0(g)", "Tetradecanoic acid (14:0) (g)" },
! 191: { "16:0(g)", "Hexadecanoic acid (16:0) (g)" },
! 192: { "18:0(g)", "Octadecanoic acid (18:0) (g)" },
! 193: { "16:1(g)", "Hexadecenoic acid (16:1) (g)" },
! 194: { "18:1(g)", "Octadecenoic acid (18:1) (g)" },
! 195: { "20:1(g)", "Eicosenoic acid (20:1) (g)" },
! 196: { "22:1(g)", "Docosenoic acid (22:1) (g)" },
! 197: { "18:2(g)", "Octadecadienoic acid (18:2) (g)" },
! 198: { "18:3(g)", "Octadecatrienoic acid (18:3) (g)" },
! 199: { "18:4(g)", "Octadecatetraenoic acid (18:4) (g)" },
! 200: { "20:4(g)", "Eicosatetraenoic acid (20:4) (g)" },
! 201: { "20:5 n-3(g)", "EPA (20:5 n-3) (g)" },
! 202: { "22:5 n-3(g)", "DPA (22:5 n-3) (g)" },
! 203: { "22:6 n-3(g)", "DHA (22:6 n-3) (g)" },
! 204: };
! 205:
! 206: #define NUM_FA_NAMES (sizeof fa_names / sizeof fa_names[0])
! 207:
! 208: /* Build a clean nutrient name from a column name that may contain
! 209: embedded newlines (an artifact of multi-line Excel headers).
! 210: Fatty-acid shorthand codes are expanded to their common names. */
! 211: static char *
! 212: clean_column_name (const char *raw)
! 213: {
! 214: char *clean;
! 215: size_t i, j, len;
! 216:
! 217: len = strlen (raw);
! 218: clean = malloc (len + 1);
! 219: if (!clean)
! 220: {
! 221: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 222: return NULL;
! 223: }
! 224:
! 225: for (i = 0, j = 0; i < len; i++)
! 226: {
! 227: if (raw[i] == '\n')
! 228: continue;
! 229: clean[j++] = raw[i];
! 230: }
! 231: clean[j] = '\0';
! 232:
! 233: /* Replace fatty acid shorthand with a readable name. */
! 234: for (i = 0; i < NUM_FA_NAMES; i++)
! 235: {
! 236: if (strcmp (clean, fa_names[i].key) == 0)
! 237: {
! 238: char *expanded = strdup (fa_names[i].name);
! 239: if (!expanded)
! 240: {
! 241: fprintf (stderr, _("gnutrition: memory exhausted\n"));
! 242: free (clean);
! 243: return NULL;
! 244: }
! 245: free (clean);
! 246: return expanded;
! 247: }
! 248: }
! 249:
! 250: return clean;
! 251: }
! 252:
! 253: int
! 254: db_get_nutrients (sqlite3 *db, int food_code, struct nutrient_list *results)
! 255: {
! 256: sqlite3_stmt *stmt;
! 257: int rc, col, ncols;
! 258:
! 259: results->items = NULL;
! 260: results->count = 0;
! 261: results->capacity = 0;
! 262:
! 263: /* The nutrients table stores each nutrient as a separate column
! 264: (wide format). Columns 0-3 are metadata (Food code, description,
! 265: WWEIA category number/description); nutrient data starts at
! 266: column 4. */
! 267: rc = sqlite3_prepare_v2 (db,
! 268: "SELECT * FROM nutrients WHERE \"Food code\" = ?1",
! 269: -1, &stmt, NULL);
! 270:
! 271: if (rc != SQLITE_OK)
! 272: {
! 273: fprintf (stderr, _("gnutrition: SQL error: %s\n"), sqlite3_errmsg (db));
! 274: return -1;
! 275: }
! 276:
! 277: sqlite3_bind_int (stmt, 1, food_code);
! 278: rc = sqlite3_step (stmt);
! 279:
! 280: if (rc == SQLITE_ROW)
! 281: {
! 282: ncols = sqlite3_column_count (stmt);
! 283: for (col = 4; col < ncols; col++)
! 284: {
! 285: const char *raw_name = sqlite3_column_name (stmt, col);
! 286: char *name = clean_column_name (raw_name ? raw_name : "");
! 287: double value = sqlite3_column_double (stmt, col);
! 288: if (!name
! 289: || nutrient_list_append (results, name, value) < 0)
! 290: {
! 291: free (name);
! 292: sqlite3_finalize (stmt);
! 293: nutrient_list_free (results);
! 294: return -1;
! 295: }
! 296: free (name);
! 297: }
! 298: }
! 299: else if (rc != SQLITE_DONE)
! 300: {
! 301: fprintf (stderr, _("gnutrition: SQL error: %s\n"), sqlite3_errmsg (db));
! 302: sqlite3_finalize (stmt);
! 303: return -1;
! 304: }
! 305:
! 306: sqlite3_finalize (stmt);
! 307: return 0;
! 308: }
! 309:
! 310: int
! 311: db_get_fped (sqlite3 *db, int food_code, struct fped_entry *entry)
! 312: {
! 313: sqlite3_stmt *stmt;
! 314: int rc;
! 315:
! 316: memset (entry, 0, sizeof *entry);
! 317: entry->food_code = food_code;
! 318:
! 319: /* The FPED table columns include aggregated food pattern categories.
! 320: We sum the sub-categories into the six major groups. */
! 321: rc = sqlite3_prepare_v2 (db,
! 322: "SELECT "
! 323: " COALESCE(\"V_TOTAL (cup eq)\", 0), "
! 324: " COALESCE(\"F_TOTAL (cup eq)\", 0), "
! 325: " COALESCE(\"G_TOTAL (oz eq)\", 0), "
! 326: " COALESCE(\"D_TOTAL (cup eq)\", 0), "
! 327: " COALESCE(\"PF_TOTAL (oz eq)\", 0), "
! 328: " COALESCE(\"OILS (grams)\", 0) "
! 329: "FROM points "
! 330: "WHERE FOODCODE = ?1 "
! 331: "LIMIT 1",
! 332: -1, &stmt, NULL);
! 333:
! 334: if (rc != SQLITE_OK)
! 335: {
! 336: fprintf (stderr, _("gnutrition: SQL error: %s\n"), sqlite3_errmsg (db));
! 337: return -1;
! 338: }
! 339:
! 340: sqlite3_bind_int (stmt, 1, food_code);
! 341: rc = sqlite3_step (stmt);
! 342:
! 343: if (rc == SQLITE_ROW)
! 344: {
! 345: entry->vegetables = sqlite3_column_double (stmt, 0);
! 346: entry->fruits = sqlite3_column_double (stmt, 1);
! 347: entry->grains = sqlite3_column_double (stmt, 2);
! 348: entry->dairy = sqlite3_column_double (stmt, 3);
! 349: entry->protein = sqlite3_column_double (stmt, 4);
! 350: entry->oils = sqlite3_column_double (stmt, 5);
! 351: sqlite3_finalize (stmt);
! 352: return 0;
! 353: }
! 354: else if (rc == SQLITE_DONE)
! 355: {
! 356: sqlite3_finalize (stmt);
! 357: return 1; /* Not found. */
! 358: }
! 359: else
! 360: {
! 361: fprintf (stderr, _("gnutrition: SQL error: %s\n"), sqlite3_errmsg (db));
! 362: sqlite3_finalize (stmt);
! 363: return -1;
! 364: }
! 365: }
! 366:
! 367: void
! 368: food_list_free (struct food_list *list)
! 369: {
! 370: size_t i;
! 371: if (!list)
! 372: return;
! 373: for (i = 0; i < list->count; i++)
! 374: free (list->items[i].description);
! 375: free (list->items);
! 376: list->items = NULL;
! 377: list->count = 0;
! 378: list->capacity = 0;
! 379: }
! 380:
! 381: void
! 382: nutrient_list_free (struct nutrient_list *list)
! 383: {
! 384: size_t i;
! 385: if (!list)
! 386: return;
! 387: for (i = 0; i < list->count; i++)
! 388: free (list->items[i].name);
! 389: free (list->items);
! 390: list->items = NULL;
! 391: list->count = 0;
! 392: list->capacity = 0;
! 393: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to db.c CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [GNUtrition Sources] / gnutrition