Return to gnutrition-updatedb.in CVS log

Up to [GNUtrition Sources] / gnutrition

Annotation of gnutrition/gnutrition-updatedb.in, revision 1.1

1.1     ! asm         1: #!/bin/bash
        !             2: # SPDX-License-Identifier: GPL-3.0-or-later
        !             3: #
        !             4: # $Id$
        !             5: #
        !             6: # gnutrition-updatedb.in - Download USDA food data and build a SQLite database
        !             7: #
        !             8: # Copyright (C) 2026 Free Software Foundation, Inc.
        !             9: #
        !            10: # Author: Jason Self <jself@gnu.org>
        !            11: #         Anton McClure <asm@gnu.org>
        !            12: 
        !            13: set -e
        !            14: 
        !            15: dir="@datadir@/gnutrition"
        !            16: mkdir -p "$dir" && \
        !            17: cd "$dir" && \
        !            18: 
        !            19: # Check for write permissions
        !            20: touch @datadir@/gnutrition/food.db && \
        !            21: 
        !            22: # Check for required commands
        !            23: missing=()
        !            24: for cmd in wget sha512sum libreoffice sqlite3; do
        !            25:     if ! command -v "$cmd" > /dev/null 2>&1; then
        !            26:         missing+=("$cmd")
        !            27:     fi
        !            28: done
        !            29: 
        !            30: if [ ${#missing[@]} -ne 0 ]; then
        !            31:     echo "Error: The following required commands are not available:" >&2
        !            32:     for cmd in "${missing[@]}"; do
        !            33:         echo "  - $cmd (please install it)" >&2
        !            34:     done
        !            35:     exit 1
        !            36: fi
        !            37: 
        !            38: # Download the Excel files
        !            39: BASE_URL="https://www.ars.usda.gov/ARSUserFiles/80400530/apps"
        !            40: 
        !            41: wget -O "2019-2020 FNDDS At A Glance - Foods and Beverages.xlsx" \
        !            42:     "${BASE_URL}/2019-2020%20FNDDS%20At%20A%20Glance%20-%20Foods%20and%20Beverages.xlsx"
        !            43: 
        !            44: wget -O "2019-2020 FNDDS At A Glance - Portions and Weights.xlsx" \
        !            45:     "${BASE_URL}/2019-2020%20FNDDS%20At%20A%20Glance%20-%20Portions%20and%20Weights.xlsx"
        !            46: 
        !            47: wget -O "2019-2020 FNDDS At A Glance - FNDDS Nutrient Values.xlsx" \
        !            48:     "${BASE_URL}/2019-2020%20FNDDS%20At%20A%20Glance%20-%20FNDDS%20Nutrient%20Values.xlsx"
        !            49: 
        !            50: wget -O "FPED_1720.xls" \
        !            51:     "${BASE_URL}/FPED_1720.xls"
        !            52: 
        !            53: # Verify SHA-512 hashes
        !            54: echo "Verifying file integrity..."
        !            55: 
        !            56: if ! sha512sum -c <<'EOF'
        !            57: a2d99aa0e4761e5fed5dd9e5b2003ece4c8f61c954ddabbdacef816abb15c2647d2f1b1cfef8bed98af5a20d4af6d2a34c3e80fd271e33bdf728797f72c18e0c  2019-2020 FNDDS At A Glance - Foods and Beverages.xlsx
        !            58: 7da5e96923f92bf94d0420d899b86004fd86c69a5a7bb2273b02ee21147e827b63af37b8e80525c91b680dd6ed035cfad677cf27dd80f1ae8c10ed0e8e49a0f8  2019-2020 FNDDS At A Glance - Portions and Weights.xlsx
        !            59: 43ee5a5661499b41e621622a4fdffd071805f86839e0577654316a8f21952ba69d56594e0437b77aac32391d90df053a2c036fd7067e2e6b2cad5c5e0576edc1  2019-2020 FNDDS At A Glance - FNDDS Nutrient Values.xlsx
        !            60: 38b46ba1972d700de4d1625acfd0260c518b64850fa0c8251dc7e960ad09f008915ed4fa935074b19fc83cc09abee756dc7dd62a4c487b2a2ec7b4bf196bd683  FPED_1720.xls
        !            61: EOF
        !            62: then
        !            63:     echo "Error: SHA-512 hash verification failed. Downloaded files may be corrupted." >&2
        !            64:     exit 1
        !            65: fi
        !            66: 
        !            67: echo "All hashes verified successfully."
        !            68: 
        !            69: # Convert Excel files to CSV using LibreOffice
        !            70: echo "Converting Excel files to CSV..."
        !            71: 
        !            72: for file in *.xls *.xlsx; do
        !            73:     libreoffice --headless --convert-to csv "$file"
        !            74: done
        !            75: 
        !            76: # The USDA "At A Glance" Excel files contain 1-2 rows of report title
        !            77: # and date information before the actual column headers (e.g., Food code,
        !            78: # Main food description). These extra rows must be removed so that the
        !            79: # first row of each CSV contains the column names; otherwise SQLite will
        !            80: # import the title text as a data row or fail on type mismatches.
        !            81: for csv in "2019-2020 FNDDS At A Glance - Foods and Beverages.csv" \
        !            82:            "2019-2020 FNDDS At A Glance - Portions and Weights.csv" \
        !            83:            "2019-2020 FNDDS At A Glance - FNDDS Nutrient Values.csv"; do
        !            84:     sed -i '1,2d' "$csv"
        !            85: done
        !            86: 
        !            87: echo "Conversion complete."
        !            88: 
        !            89: # Build the SQLite database
        !            90: echo "Building SQLite database..."
        !            91: 
        !            92: rm -f food.db
        !            93: 
        !            94: sqlite3 food.db <<'SQL'
        !            95: .mode csv
        !            96: 
        !            97: -- Import foods table
        !            98: .import '2019-2020 FNDDS At A Glance - Foods and Beverages.csv' foods
        !            99: 
        !           100: -- Import portions table
        !           101: .import '2019-2020 FNDDS At A Glance - Portions and Weights.csv' portions
        !           102: 
        !           103: -- Import nutrients table
        !           104: .import '2019-2020 FNDDS At A Glance - FNDDS Nutrient Values.csv' nutrients
        !           105: 
        !           106: -- Import points table
        !           107: .import 'FPED_1720.csv' points
        !           108: SQL
        !           109: 
        !           110: echo "SQLite database food.db created successfully."