From 051b15d7ef045e12b8cfe0174223ac55a568d1cd Mon Sep 17 00:00:00 2001 From: gamesguru Date: Mon, 28 Sep 2020 10:04:35 -0400 Subject: [PATCH] dont use msre_id --- docs/nt.svg | 394 ++++++++++++++++++++++------------------ sql/data/food_log.csv | 46 ++--- sql/data/recipe_dat.csv | 2 +- sql/export.sh | 14 ++ sql/tables.sql | 4 +- 5 files changed, 256 insertions(+), 204 deletions(-) create mode 100755 sql/export.sh diff --git a/docs/nt.svg b/docs/nt.svg index c398c92..992660a 100644 --- a/docs/nt.svg +++ b/docs/nt.svg @@ -4,254 +4,294 @@ - - + + undefined - -nt.sqlite + +nt.sqlite 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 -bmr_eq -text + +bmr_eqs + +id* +integer +bmr_eq +text bf_eqs - -bf_eqs - -id* -integer -bf_eq -text + +bf_eqs + +id* +integer +bf_eq +text users - -users - -id* -integer -name -text -eula -int -gender -text -dob -date -act_lvl -int -goal_wt -real -goal_bf -real -bmr_id -int -bf_id -int -created -int + +users + +id* +integer +name +text +guid +text +created +int +eula +int +gender +text +dob +date +act_lvl +int +goal_wt +real +goal_bf +real +bmr_id +int +bf_id +int users->bmr_eqs - - + + users->bf_eqs - - + + biometrics - -biometrics - -id* -integer -name -text -unit -text -created -int + +biometrics + +id* +integer +name +text +unit +text +created +int biometric_log - -biometric_log - -id* -integer -guid -text -user_id -int -date -date -biometric_id -int -value -real + +biometric_log + +id* +integer +guid +text +user_id +int +date +date +biometric_id +int +value +real biometric_log->users - - + + biometric_log->biometrics - - + + recipes - -recipes - -id* -integer -guid -text -user_id -int -name -text -created -int - - - -recipes->users - - + +recipes + +id* +integer +guid +text +created +int +name +text 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 - + recipe_dat->recipes - - + + meal_names - -meal_names - -id* -integer -name -text + +meal_names + +id* +integer +name +text food_log - -food_log - -id* -integer -guid -text -user_id -int -date -date -meal_id -int -amount -real -recipe_id -int -food_id -int -food_msre_id -int + +food_log + +id* +integer +guid +text +user_id +int +date +date +meal_id +int +grams +real +food_id +int - -food_log->users - - - - -food_log->recipes - - +food_log->users + + - + food_log->meal_names - - + + - + +recipe_log + +recipe_log + +id* +integer +guid +text +user_id +int +date +date +meal_id +int +grams +real +recipe_id +int + + + +recipe_log->users + + + + + +recipe_log->recipes + + + + + +recipe_log->meal_names + + + + + rda - -rda - -user_id -int -nutr_id -int -rda -real -synced -int + +rda + +user_id +int +nutr_id +int +rda +real +synced +int - + rda->users - - + + + + + +sync_data + +sync_data + +id* +integer +tablename +text +guid +text +constraint +text +action +text diff --git a/sql/data/food_log.csv b/sql/data/food_log.csv index 8c69c02..4f8c060 100644 --- a/sql/data/food_log.csv +++ b/sql/data/food_log.csv @@ -1,23 +1,23 @@ -id,guid,user_id,date,meal_id,amount,food_id,food_msre_id -1,365e2f316ea39c578a63ec0f59c0d0f0,1,2020-09-20,1,100,13047, -2,71d5d8cbbc591d22cb210ae6323249a1,1,2020-09-20,1,28,1270, -3,295f1cd568fc03b760ff9a3a985c4658,1,2020-09-21,1,55,9038, -4,30bb19b8d57492fee6cb1ebbdc9196a,1,2020-09-21,1,20,11251, -5,d2e64b1da42c2e12bd3dc9f029fb1bb5,1,2020-09-22,1,35,11529, -6,b169e76aa7ccd833a3f7d0e1a5f6935b,1,2020-09-22,1,15,11282, -7,2c53dca9604ea742c39184201d41eed4,1,2020-09-23,1,210,11828, -8,edad78872ae5d7e2a444756a336dce40,1,2020-09-23,1,40,28313, -9,c78bb27a7c756763d10d4bb7f860ba23,1,2020-09-24,1,100,9112, -10,490ffdd0063fcfc79ce5f825a7f2d370,1,2020-09-24,2,140,20137, -11,7fe9a33f24010f1738ddf73010041ced,1,2020-09-25,2,100,5062, -12,a54ddeafde280fd76a0cbbd63b9539e7,1,2020-09-25,2,45,12136, -13,6dfcfbbd5d0cb563343260d287cbdff5,1,2020-09-26,2,50,11821, -14,4704b0288bf626799dc2c09cbb75f28,1,2020-09-26,2,15,44005, -15,7053ec55502f3dccccf16d59c2a38873,1,2020-09-27,3,150,20545, -16,8f7d5caef9dac0d7102a4d90ae9f36c8,1,2020-09-27,3,85,16146, -17,79949cb0b9914d63892b89d91a4a4ad0,1,2020-09-28,3,40,1270, -18,85a4ab5a6da9fa84285dece0f2c7fa2a,1,2020-09-28,3,60,9037, -19,16f98e0382dece4799dd41325b292c70,1,2020-09-29,3,100,15076, -20,bcab83a1676a3ac8f6aadd63dd032e06,1,2020-09-29,3,60,11090, -21,44aa12a8695bfd84a513e24bb65896d9,1,2020-09-30,3,35,11938, -22,252045bc8ba3f133d4006d82a467b053,1,2020-09-30,3,25,11282, +id,guid,user_id,date,meal_id,grams,food_id +1,365e2f316ea39c578a63ec0f59c0d0f0,1,2020-09-20,1,100,13047 +2,71d5d8cbbc591d22cb210ae6323249a1,1,2020-09-20,1,28,1270 +3,295f1cd568fc03b760ff9a3a985c4658,1,2020-09-20,1,55,9038 +4,30bb19b8d57492fee6cb1ebbdc9196a,1,2020-09-20,1,20,11251 +5,d2e64b1da42c2e12bd3dc9f029fb1bb5,1,2020-09-20,1,35,11529 +6,b169e76aa7ccd833a3f7d0e1a5f6935b,1,2020-09-20,1,15,11282 +7,2c53dca9604ea742c39184201d41eed4,1,2020-09-20,1,210,11828 +8,edad78872ae5d7e2a444756a336dce40,1,2020-09-20,1,40,28313 +9,c78bb27a7c756763d10d4bb7f860ba23,1,2020-09-20,1,100,9112 +10,490ffdd0063fcfc79ce5f825a7f2d370,1,2020-09-20,2,140,20137 +11,7fe9a33f24010f1738ddf73010041ced,1,2020-09-20,2,100,5062 +12,a54ddeafde280fd76a0cbbd63b9539e7,1,2020-09-20,2,45,12136 +13,6dfcfbbd5d0cb563343260d287cbdff5,1,2020-09-20,2,50,11821 +14,4704b0288bf626799dc2c09cbb75f28,1,2020-09-20,2,15,44005 +15,7053ec55502f3dccccf16d59c2a38873,1,2020-09-20,3,150,20545 +16,8f7d5caef9dac0d7102a4d90ae9f36c8,1,2020-09-20,3,85,16146 +17,79949cb0b9914d63892b89d91a4a4ad0,1,2020-09-20,3,40,1270 +18,85a4ab5a6da9fa84285dece0f2c7fa2a,1,2020-09-20,3,60,9037 +19,16f98e0382dece4799dd41325b292c70,1,2020-09-20,3,100,15076 +20,bcab83a1676a3ac8f6aadd63dd032e06,1,2020-09-20,3,60,11090 +21,44aa12a8695bfd84a513e24bb65896d9,1,2020-09-20,3,35,11938 +22,252045bc8ba3f133d4006d82a467b053,1,2020-09-20,3,25,11282 diff --git a/sql/data/recipe_dat.csv b/sql/data/recipe_dat.csv index 2f62aa2..c1aadda 100644 --- a/sql/data/recipe_dat.csv +++ b/sql/data/recipe_dat.csv @@ -1,4 +1,4 @@ -recipe_id,food_id,amount,notes +recipe_id,food_id,grams,notes 1,20045,180,white rice 1,16042,25,pinto beans 1,11282,45,onions diff --git a/sql/export.sh b/sql/export.sh new file mode 100755 index 0000000..627d8d8 --- /dev/null +++ b/sql/export.sh @@ -0,0 +1,14 @@ +#!/bin/bash -e + +cd "$(dirname "$0")" + +table_cmd="sqlite3 nt.sqlite '.tables'" +tables=`sqlite3 nt.sqlite '.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" +done diff --git a/sql/tables.sql b/sql/tables.sql index 1e21bb8..f021288 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -121,11 +121,9 @@ CREATE TABLE food_log ( user_id int, date date DEFAULT CURRENT_DATE, meal_id int, - amount real NOT NULL, -- grams, if `food_msre_id` IS NULL + grams real NOT NULL, -- TODO: enforce FK constraint across two DBs? - food_id int, - food_msre_id int, FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE, FOREIGN KEY (meal_id) REFERENCES meal_names (id) ON UPDATE CASCADE ); -- 2.52.0