Utilities for simple nutrition tracking (SQLite)

diet.sql

/* References:
 * https://www.fda.gov/food/nutrition-education-resources-and-materials/how-understand-and-use-nutrition-facts-label
 * https://www.accessdata.fda.gov/scripts/interactivenutritionfactslabel/factsheets/vitamin_and_mineral_chart.pdf
 */
CREATE TABLE servings (id INTEGER PRIMARY KEY, product TEXT, size TEXT, grams NUMERIC DEFAULT 0, calories NUMERIC DEFAULT 0, total_fat_g NUMERIC DEFAULT 0, sat_fat_g NUMERIC DEFAULT 0, trans_fat_g NUMERIC DEFAULT 0, cholesterol_mg NUMERIC DEFAULT 0, sodium_mg NUMERIC DEFAULT 0, total_carb_g NUMERIC DEFAULT 0, fiber_g NUMERIC DEFAULT 0, total_sugars_g NUMERIC DEFAULT 0, protein_g NUMERIC DEFAULT 0, vitamin_a_mcg NUMERIC DEFAULT 0, vitamin_c_mcg NUMERIC DEFAULT 0, vitamin_d_mcg NUMERIC DEFAULT 0, calcium_mg NUMERIC DEFAULT 0, iron_mg NUMERIC DEFAULT 0, potassium_mg NUMERIC DEFAULT 0, updated_at DATE);
CREATE TABLE diet (id INTEGER PRIMARY KEY, servings_id INTEGER, num_servings INTEGER DEFAULT 0, time_stamp DATETIME);
CREATE VIEW daily_amount AS SELECT strftime('%Y-%m-%d', diet.time_stamp) AS day, sum(diet.num_servings * servings.calories) AS calories, round(sum(calories) / 20.0, 2) AS calories_pct, sum(diet.num_servings * servings.total_fat_g) AS total_fat_g, round(sum(total_fat_g) / 0.65, 2) AS total_fat_pct, sum(diet.num_servings * servings.sat_fat_g) AS sat_fat_g, round(sum(sat_fat_g) / 0.20, 2) AS sat_fat_pct, sum(diet.num_servings * servings.cholesterol_mg) AS cholesterol_mg, round(sum(cholesterol_mg) / 3.0, 2) AS cholesterol_pct, sum(diet.num_servings * servings.sodium_mg) AS sodium_mg, round(sum(sodium_mg) / 24.0, 2) AS sodium_pct, sum(diet.num_servings * servings.total_carb_g) AS total_carb_g, round(sum(total_carb_g) / 3.0, 2) AS total_carb_pct, sum(diet.num_servings * servings.fiber_g) AS fiber_g, round(sum(fiber_g) / 0.25, 2) AS fiber_pct FROM diet JOIN servings ON diet.servings_id = servings.id GROUP BY strftime(diet.time_stamp, '%Y-%m-%d');
/* daily_amount(day,calories,calories_pct,total_fat_g,total_fat_pct,sat_fat_g,sat_fat_pct,cholesterol_mg,cholesterol_pct,sodium_mg,sodium_pct,total_carb_g,total_carb_pct,fiber_g,fiber_pct) */;