From 051c116edc01745fdcfc5f771d5958136d7847a3 Mon Sep 17 00:00:00 2001 From: Shane Jaroch Date: Thu, 14 Jul 2022 10:30:37 -0400 Subject: [PATCH] rename tables, cf, prep plugins, update svg --- docs/nt.svg | 707 +++++++++++++++++++++++++------------------ sql/data/version.csv | 1 + sql/tables.sql | 118 +++++--- 3 files changed, 484 insertions(+), 342 deletions(-) diff --git a/docs/nt.svg b/docs/nt.svg index 98c75f1..df0c492 100644 --- a/docs/nt.svg +++ b/docs/nt.svg @@ -4,344 +4,451 @@ - - + + undefined - -sql/nt.sqlite3 + +sql/nt.sqlite3 version - -version - -id* -integer -version -text -created -date -notes -text - - + +version + +id* +integer +version +text +created +date +notes +text + + -bmr_eqs - -bmr_eqs - -id* -integer -name -text - - +bmr_eq + +bmr_eq + +id* +integer +name +text + + -bf_eqs - -bf_eqs - -id* -integer -name -text - - +bf_eq + +bf_eq + +id* +integer +name +text + + -profiles - -profiles - -id* -integer -name -text -gender -text -dob -date -act_lvl -int -goal_wt -real -goal_bf -real -bmr_eq_id -int -bf_eq_id -int -created -int - - +profile + +profile + +id* +integer +uuid +int +name +text +gender +text +dob +date +act_lvl +int +goal_wt +real +goal_bf +real +bmr_eq_id +int +bf_eq_id +int +created +int + + -profiles->bmr_eqs - - +profile->bmr_eq + + - + -profiles->bf_eqs - - +profile->bf_eq + + - + -recipes - -recipes - -id* -integer -tagname -text -name -text -created -int +rda + +rda + +profile_id* +int +nutr_id* +int +rda +real - - -recipe_dat - -recipe_dat - -recipe_id* -int -food_id* -int -grams -real -notes -text -created -int - - + -recipe_dat->recipes - - +rda->profile + + - - -custom_foods - -custom_foods - -id* -integer -tagname -text -name -text -created -int + + +custom_food + +custom_food + +id* +integer +tagname +text +name +text +created +int - + cf_dat - -cf_dat - -cf_id* -int -nutr_id* -int -nutr_val -real -notes -text -created -int - - + +cf_dat + +cf_id* +int +nutr_id* +int +nutr_val +real +notes +text +created +int + + -cf_dat->custom_foods - - +cf_dat->custom_food + + - + + +recipe + +recipe + +id* +integer +tagname +text +name +text +created +int + + -meal_name - -meal_name - -id* -integer -name -text - - - -food_log - -food_log - -id* -integer -profile_id -int -date -int -meal_id -int -food_id -int -msre_id -int -amt -real -created -int - - - -food_log->profiles - - +recipe_dat + +recipe_dat + +recipe_id* +int +food_id* +int +grams +real +notes +text +created +int - + -food_log->meal_name - - +recipe_dat->recipe + + - - -recipe_log - -recipe_log - -id* -integer -profile_id -int -date -int -meal_id -int -recipe_id -int -grams -real -created -int - - - -recipe_log->profiles - - + + +recipe_dat_cf + +recipe_dat_cf + +recipe_id* +int +custom_food_id* +int +grams +real +notes +text +created +int - + -recipe_log->recipes - - +recipe_dat_cf->recipe + + - - -recipe_log->meal_name - - + + +cf + +cf - + + +recipe_dat_cf->cf + + + + + +meal_name + +meal_name + +id* +integer +name +text + + -rda - -rda - -profile_id* -int -nutr_id* -int -rda -real - - - -rda->profiles - - +log_food + +log_food + +id* +integer +profile_id +int +date +int +meal_id +int +food_id +int +msre_id +int +amt +real +created +int + + + +log_food->profile + + + + + +log_food->meal_name + + - + -food_cost - -food_cost - -food_id* -int -profile_id* -int -cost -real - - +log_cf + +log_cf + +id* +integer +profile_id +int +date +int +meal_id +int +food_id +int +custom_food_id +int +msre_id +int +amt +real +created +int + + + +log_cf->profile + + + + + +log_cf->custom_food + + + + -food_cost->profiles - - +log_cf->meal_name + + - + +log_recipe + +log_recipe + +id* +integer +profile_id +int +date +int +meal_id +int +recipe_id +int +grams +real +created +int + + + +log_recipe->profile + + + + + +log_recipe->recipe + + + + + +log_recipe->meal_name + + + + + +cost_food + +cost_food + +food_id* +int +profile_id* +int +cost +real + + + +cost_food->profile + + + + + +cost_cf + +cost_cf + +custom_food_id* +int +profile_id* +int +cost +real + + + +cost_cf->profile + + + + + +cost_cf->custom_food + + + + + bug - -bug - -id* -integer -profile_id -int -created -int -arguments -text -stack -text -os -text -py_ver -text -user_details -text -submitted -tinyint - - - -bug->profiles - - + +bug + +id* +integer +profile_id +int +created +int +arguments +text +activity +text +stack +text +client_info +json +app_info +json +user_details +json +submitted +tinyint + + + +bug->profile + + - + msg - -msg - -id* -integer -profile_id -int -msg_id -int -created -int -received -int -header -text -body -text - - - -msg->profiles - - + +msg + +id* +integer +profile_id +int +msg_id +int +created +int +received +int +header +text +body +text + + + +msg->profile + + diff --git a/sql/data/version.csv b/sql/data/version.csv index f5f5eb3..8561818 100644 --- a/sql/data/version.csv +++ b/sql/data/version.csv @@ -5,3 +5,4 @@ id,version,created,notes 4,0.0.3,2021-05-24,general cleanup 5,0.0.4,2021-06-17,"add custom foods tables (custom_foods, cf_dat)" 6,0.0.5,2022-07-11,remove biometrics +7,0.0.6,2022-07-14,"rename tables, start to organize for plugins" diff --git a/sql/tables.sql b/sql/tables.sql index ef7e773..de48195 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -13,6 +13,7 @@ -- -- You should have received a copy of the GNU General Public License -- along with this program. If not, see . +-- CREATE TABLE `version` ( id integer PRIMARY KEY AUTOINCREMENT, `version` text NOT NULL UNIQUE, @@ -24,15 +25,15 @@ CREATE TABLE `version` ( -- TODO: enforce FK constraint across two DBs? -- --------------------------------- --- Equations +-- Formula / Equation --------------------------------- CREATE TABLE bmr_eq ( - id integer PRIMARY KEY AUTOINCREMENT, + id integer PRIMARY KEY, name text NOT NULL UNIQUE ); CREATE TABLE bf_eq ( - id integer PRIMARY KEY AUTOINCREMENT, + id integer PRIMARY KEY, name text NOT NULL UNIQUE ); @@ -40,15 +41,17 @@ CREATE TABLE bf_eq ( -------------------------------- -- Profile table -------------------------------- --- TODO: active profile? Decide what belongs here, vs. in prefs.json (if at all) +-- TODO: active profile? +-- Decide what belongs here vs. in "prefs.json" (where to maintain xyz the easiest, if at all?) CREATE TABLE profile ( id integer PRIMARY KEY AUTOINCREMENT, + uuid int NOT NULL DEFAULT (RANDOM()), name text NOT NULL UNIQUE, gender text, dob date, act_lvl int DEFAULT 2, -- [1, 2, 3, 4, 5] goal_wt real, - goal_bf real, + goal_bf real DEFAULT 18, bmr_eq_id int DEFAULT 1, bf_eq_id int DEFAULT 1, created int DEFAULT (strftime ('%s', 'now')), @@ -56,46 +59,74 @@ CREATE TABLE profile ( FOREIGN KEY (bf_eq_id) REFERENCES bf_eq (id) ON UPDATE CASCADE ON DELETE CASCADE ); +-- TODO: how much of this belongs in plugins/extensions? +-- Do we want to support everything in SQL, or provide a more basic setup and +-- encourage community-driven CSV/zip plugins? -- -------------------------------- --- Recipe +-- Custom RDA values -------------------------------- -CREATE TABLE recipe ( +CREATE TABLE rda ( + profile_id int NOT NULL, + nutr_id int NOT NULL, + rda real NOT NULL, + PRIMARY KEY (profile_id, nutr_id), + FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE CASCADE ON DELETE CASCADE +); + +-- TODO: do we want GUID / UUID values on any of these? +-- Does that simplify potential CSV imports? Having a GUID / UUID column? +-- +-------------------------------- +-- Custom food +-------------------------------- +CREATE TABLE custom_food ( id integer PRIMARY KEY AUTOINCREMENT, tagname text NOT NULL UNIQUE, name text NOT NULL UNIQUE, created int DEFAULT (strftime ('%s', 'now')) ); -CREATE TABLE recipe_dat ( - recipe_id int NOT NULL, - food_id int NOT NULL, - grams real NOT NULL, +CREATE TABLE cf_dat ( + cf_id int NOT NULL, + nutr_id int NOT NULL, -- NOTE: no FK constraining on USDA + nutr_val real NOT NULL, notes text, created int DEFAULT (strftime ('%s', 'now')), - PRIMARY KEY (recipe_id, food_id), - FOREIGN KEY (recipe_id) REFERENCES recipe (id) ON UPDATE CASCADE ON DELETE CASCADE + PRIMARY KEY (cf_id, nutr_id), + FOREIGN KEY (cf_id) REFERENCES custom_food (id) ON UPDATE CASCADE ON DELETE CASCADE ); -- -------------------------------- --- Custom foods +-- Recipe -------------------------------- -CREATE TABLE custom_food ( +CREATE TABLE recipe ( id integer PRIMARY KEY AUTOINCREMENT, tagname text NOT NULL UNIQUE, name text NOT NULL UNIQUE, created int DEFAULT (strftime ('%s', 'now')) ); -CREATE TABLE cf_dat ( - cf_id int NOT NULL, - nutr_id int NOT NULL, -- no FK constraining on usda :[ - nutr_val real NOT NULL, +CREATE TABLE recipe_dat ( + recipe_id int NOT NULL, + food_id int NOT NULL, + grams real NOT NULL, notes text, created int DEFAULT (strftime ('%s', 'now')), - PRIMARY KEY (cf_id, nutr_id), - FOREIGN KEY (cf_id) REFERENCES custom_food (id) ON UPDATE CASCADE ON DELETE CASCADE + PRIMARY KEY (recipe_id, food_id), + FOREIGN KEY (recipe_id) REFERENCES recipe (id) ON UPDATE CASCADE ON DELETE CASCADE +); + +CREATE TABLE recipe_dat_cf ( + recipe_id int NOT NULL, + custom_food_id int NOT NULL, + grams real NOT NULL, + notes text, + created int DEFAULT (strftime ('%s', 'now')), + PRIMARY KEY (recipe_id, custom_food_id), + FOREIGN KEY (recipe_id) REFERENCES recipe (id) ON UPDATE CASCADE ON DELETE CASCADE, + FOREIGN KEY (custom_food_id) REFERENCES cf (id) ON UPDATE CASCADE ON DELETE CASCADE ); -- @@ -108,13 +139,25 @@ CREATE TABLE meal_name ( name text NOT NULL ); -CREATE TABLE food_log ( +CREATE TABLE log_food ( id integer PRIMARY KEY AUTOINCREMENT, profile_id int NOT NULL, date int DEFAULT (strftime ('%s', 'now')), meal_id int NOT NULL, - -- NOTE: do we want separate tables for logging `food_id` vs. `custom_food_id`? - food_id int, + food_id int NOT NULL, + msre_id int NOT NULL, + amt real NOT NULL, + created int DEFAULT (strftime ('%s', 'now')), + FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE CASCADE ON DELETE CASCADE, + FOREIGN KEY (meal_id) REFERENCES meal_name (id) ON UPDATE CASCADE ON DELETE CASCADE +); + +CREATE TABLE log_cf ( + id integer PRIMARY KEY AUTOINCREMENT, + profile_id int NOT NULL, + date int DEFAULT (strftime ('%s', 'now')), + meal_id int NOT NULL, + food_id int NOT NULL, custom_food_id int, msre_id int NOT NULL, amt real NOT NULL, @@ -125,7 +168,7 @@ CREATE TABLE food_log ( ); -- TODO: support msre_id for recipe -CREATE TABLE recipe_log ( +CREATE TABLE log_recipe ( id integer PRIMARY KEY AUTOINCREMENT, profile_id int NOT NULL, date int DEFAULT (strftime ('%s', 'now')), @@ -138,32 +181,23 @@ CREATE TABLE recipe_log ( FOREIGN KEY (recipe_id) REFERENCES recipe (id) ON UPDATE CASCADE ON DELETE CASCADE ); --- TODO: CREATE TABLE custom_food_log ( ... ); -- -------------------------------- --- Custom RDAs +-- Food cost -------------------------------- -CREATE TABLE rda ( +CREATE TABLE cost_food ( + food_id int NOT NULL, profile_id int NOT NULL, - nutr_id int NOT NULL, - rda real NOT NULL, - PRIMARY KEY (profile_id, nutr_id), + cost real NOT NULL, + PRIMARY KEY (food_id, profile_id), FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE CASCADE ON DELETE CASCADE ); --- --------------------------------- --- Food costs --------------------------------- --- Case for no FK? e.g. points to food OR custom_food? --- Leave edge cases potentially dangling (should never happen) --- Does this simplify imports with a potential `guid` column? -CREATE TABLE food_cost ( - food_id int, - custom_food_id int, +CREATE TABLE cost_cf ( + custom_food_id int NOT NULL, profile_id int NOT NULL, cost real NOT NULL, - PRIMARY KEY (food_id, custom_food_id, profile_id), + PRIMARY KEY (custom_food_id, profile_id), FOREIGN KEY (custom_food_id) REFERENCES custom_food (id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (profile_id) REFERENCES profile (id) ON UPDATE CASCADE ON DELETE CASCADE ); -- 2.52.0