From 0b35249e6e0281341c6b502a1e026e4560d96fa6 Mon Sep 17 00:00:00 2001 From: Shane Jaroch Date: Wed, 20 Apr 2022 01:23:15 -0400 Subject: [PATCH] small tweaks --- docs/nt.svg | 418 +++++++++++++++++++++++------------------------- docs/sqleton.sh | 5 +- sql/export.sh | 14 +- sql/tables.sql | 8 +- 4 files changed, 213 insertions(+), 232 deletions(-) diff --git a/docs/nt.svg b/docs/nt.svg index 2b4a91e..5153e11 100644 --- a/docs/nt.svg +++ b/docs/nt.svg @@ -9,7 +9,7 @@ undefined -nt.sqlite +sql/nt.sqlite version @@ -28,339 +28,329 @@ bmr_eqs - -bmr_eqs - -id* -integer -name -text + +bmr_eqs + +id* +integer +name +text bf_eqs - -bf_eqs - -id* -integer -name -text + +bf_eqs + +id* +integer +name +text profiles - -profiles - + +profiles + id* integer name text -created -int -updated -int -eula -int -gender -text -dob -date -act_lvl -int -goal_wt -real -goal_bf -real -bmr_eq_id -int -bf_eq_id -int +eula +int +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 - - + + profiles->bf_eqs - - + + biometrics - -biometrics - -id* -integer -created -int -updated -int -name -text -unit -text + +biometrics + +id* +integer +name +text +unit +text +created +int biometric_log - -biometric_log - -id* -integer -profile_id -int -created -int -updated -int -date -int -tags -text -notes -text + +biometric_log + +id* +integer +profile_id +int +date +int +tags +text +notes +text +created +int biometric_log->profiles - - + + bio_log_entry - -bio_log_entry - -log_id* -int -biometric_id* -int -value -real + +bio_log_entry + +log_id* +int +biometric_id* +int +value +real bio_log_entry->biometrics - - + + bio_log_entry->biometric_log - - + + recipes - -recipes - -id* -integer -created -int -updated -int -tagname -text -name -text + +recipes + +id* +integer +tagname +text +name +text +created +int recipe_dat - -recipe_dat - -recipe_id* -int -food_id* -int -grams -real -notes -text + +recipe_dat + +recipe_id* +int +food_id* +int +grams +real +notes +text +created +int recipe_dat->recipes - - + + custom_foods - -custom_foods - -id* -integer -created -int -updated -int -tagname -text -name -text + +custom_foods + +id* +integer +tagname +text +name +text +created +int cf_dat - -cf_dat - -cf_id* -int -nutr_id* -int -nutr_val -real -notes -text + +cf_dat + +cf_id* +int +nutr_id* +int +nutr_val +real +notes +text +created +int cf_dat->custom_foods - - + + meal_name - -meal_name - -id* -integer -name -text + +meal_name + +id* +integer +name +text food_log - -food_log - -id* -integer -profile_id -int -created -int -updated -int -date -int -meal_id -int -food_id -int -msre_id -int -amt -real + +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 - - + + food_log->meal_name - - + + recipe_log - -recipe_log - + +recipe_log + id* integer profile_id int -created -int -updated -int -date -int -meal_id -int -recipe_id -int -grams -real +date +int +meal_id +int +recipe_id +int +grams +real +created +int recipe_log->profiles - - + + recipe_log->recipes - - + + recipe_log->meal_name - - + + rda - -rda - -profile_id* -int -nutr_id* -int -rda -real + +rda + +profile_id* +int +nutr_id* +int +rda +real rda->profiles - - + + food_costs - -food_costs - -food_id* -integer -profile_id* -integer -cost -real + +food_costs + +food_id* +integer +profile_id* +integer +cost +real food_costs->profiles - - + + diff --git a/docs/sqleton.sh b/docs/sqleton.sh index ff2e3b3..cecf1b6 100755 --- a/docs/sqleton.sh +++ b/docs/sqleton.sh @@ -1,7 +1,6 @@ #!/bin/bash -e -# cd to script's directory cd "$(dirname "$0")" -cd ../sql +cd .. -sqleton -o ../docs/nt.svg nt.sqlite +sqleton -o docs/nt.svg sql/nt.sqlite diff --git a/sql/export.sh b/sql/export.sh index 7cd49c9..ddf5f30 100755 --- a/sql/export.sh +++ b/sql/export.sh @@ -1,15 +1,7 @@ -#!/bin/bash -e +#!/bin/bash -ex cd "$(dirname "$0")" -table_cmd="sqlite3 nt.sqlite '.tables'" -tables=$(bash -exec "$table_cmd") -echo "$tables" - -for t in $tables -do - export_cmd="SELECT * FROM $t" - echo "\"$export_cmd\"" - export_cmd="sqlite3 -csv nt.sqlite "\"$export_cmd\""" - bash -exec "$export_cmd" > "data/$t.csv" +for t in $(sqlite3 nt.sqlite '.tables'); do + sqlite3 -csv nt.sqlite "SELECT * FROM $t" > "data/$t.csv" done diff --git a/sql/tables.sql b/sql/tables.sql index 0d7d8c6..1770cf0 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -14,7 +14,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, created date NOT NULL, notes text +CREATE TABLE version( id integer PRIMARY KEY AUTOINCREMENT, version text NOT NULL UNIQUE, created date NOT NULL, notes text ); -- TODO: enforce FK constraint across two DBs? @@ -186,13 +186,13 @@ CREATE TABLE rda ( -------------------------------- -- 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_costs ( - food_id integer NOT NULL, - profile_id integer NOT NULL, + food_id int NOT NULL, + profile_id int NOT NULL, cost real NOT NULL, PRIMARY KEY (food_id, profile_id), FOREIGN KEY (profile_id) REFERENCES profiles (id) ON UPDATE CASCADE ON DELETE CASCADE -- 2.52.0