From 36fb65c6be00921059e6af239b411c3c1c48808c Mon Sep 17 00:00:00 2001 From: gamesguru Date: Mon, 24 May 2021 20:12:42 -0400 Subject: [PATCH] general cleanup, bump version 0.0.3 --- sql/data/biometric_log.csv | 78 +++++++++--------- sql/data/biometrics.csv | 60 +++++++------- sql/data/food_log.csv | 46 +++++------ sql/data/profiles.csv | 8 +- sql/data/rda.csv | 160 ++++++++++++++++++------------------- sql/data/recipe_log.csv | 4 +- sql/data/recipes.csv | 12 +-- sql/data/version.csv | 1 + sql/tables.sql | 28 +++---- 9 files changed, 197 insertions(+), 200 deletions(-) diff --git a/sql/data/biometric_log.csv b/sql/data/biometric_log.csv index c78ea34..1425912 100644 --- a/sql/data/biometric_log.csv +++ b/sql/data/biometric_log.csv @@ -1,39 +1,39 @@ -id,profile_id,created,updated,last_sync,date,tags,notes -1,1,1601977400,1601977400,-1,Sun 28 Jun 2020 02:07:01 PM EDT,exercise, -2,2,1601977400,1601977400,-1,Sun 28 Jun 2020 02:07:01 PM EDT,exercise, -3,1,1601977400,1601977400,-1,Sun 28 Jun 2020 02:38:20 PM EDT,, -4,2,1601977400,1601977400,-1,Sun 28 Jun 2020 02:38:20 PM EDT,, -5,1,1601977400,1601977400,-1,Sun 28 Jun 2020 10:05:26 PM EDT,exercise, -6,2,1601977400,1601977400,-1,Thu 02 Jul 2020 12:12:54 PM EDT,,doctor's office -7,1,1601977400,1601977400,-1,Sat 04 Jul 2020 03:50:13 PM EDT,, -8,2,1601977400,1601977400,-1,Sat 04 Jul 2020 03:54:27 PM EDT,, -9,2,1601977400,1601977400,-1,Sun 05 Jul 2020 01:30:44 PM EDT,no THC, -10,1,1601977400,1601977400,-1,Mon 06 Jul 2020 06:54:49 AM EDT,, -11,2,1601977400,1601977400,-1,Sun 12 Jul 2020 12:29:11 PM EDT,THC, -12,2,1601977400,1601977400,-1,Sun 12 Jul 2020 02:44:25 PM EDT,THC, -13,1,1601977400,1601977400,-1,Sun 12 Jul 2020 02:44:25 PM EDT,THC, -14,3,1601977400,1601977400,-1,Mon 13 Jul 2020 03:30:46 PM EDT,, -15,2,1601977400,1601977400,-1,Fri 17 Jul 2020 02:02:12 PM EDT,, -16,2,1601977400,1601977400,-1,Fri 17 Jul 2020 03:14:37 PM EDT,THC, -17,1,1601977400,1601977400,-1,Fri 17 Jul 2020 03:14:37 PM EDT,, -18,2,1601977400,1601977400,-1,Sat 08 Aug 2020 01:53:25 PM EDT,, -19,3,1601977400,1601977400,-1,Thu 27 Aug 2020 12:44:37 PM EDT,, -20,2,1601977400,1601977400,-1,Fri 28 Aug 2020 03:40:56 PM EDT,, -21,1,1601977400,1601977400,-1,Fri 28 Aug 2020 03:40:56 PM EDT,THC,1st dabs/2 weeks -22,2,1601977400,1601977400,-1,Sun 06 Sep 2020 11:49:47 AM EDT,, -23,1,1601977400,1601977400,-1,Sun 06 Sep 2020 11:53:21 AM EDT,, -24,2,1601977400,1601977400,-1,Sat 12 Sep 2020 12:15:53 PM EDT,, -25,2,1601977400,1601977400,-1,Sun 06 Sep 2020,, -26,1,1601977400,1601977400,-1,Sun 06 Sep 2020,, -27,2,1601977400,1601977400,-1,Sat 12 Sep 2020,, -28,1,1601977400,1601977400,-1,Sat 12 Sep 2020,, -29,1,1601977400,1601977400,-1,Sun 13 Sep 2020,, -30,1,1601977400,1601977400,-1,Mon 14 Sep 2020,, -31,1,1601977400,1601977400,-1,Fri 18 Sep 2020 09:04:38 PM EDT,exercise, -32,2,1601977400,1601977400,-1,Thu 24 Sep 2020 05:05:51 PM EDT,illness, -33,1,1601977400,1601977400,-1,Thu 24 Sep 2020 05:05:51 PM EDT,, -34,2,1601977400,1601977400,-1,Thu 24 Sep 2020,, -35,1,1601977400,1601977400,-1,Fri 25 Sep 2020,gym scale, -36,2,1601977400,1601977400,-1,Sat 26 Sep 2020,, -37,2,1601977400,1601977400,-1,Sat 26 Sep 2020 11:54:17 AM EDT,, -38,1,1601977400,1601977400,-1,Tue 29 Sep 2020 03:40:37 PM EDT,nude, +id,profile_id,created,updated,date,tags,notes +1,1,1601977400,1601977400,Sun 28 Jun 2020 02:07:01 PM EDT,exercise, +2,2,1601977400,1601977400,Sun 28 Jun 2020 02:07:01 PM EDT,exercise, +3,1,1601977400,1601977400,Sun 28 Jun 2020 02:38:20 PM EDT,, +4,2,1601977400,1601977400,Sun 28 Jun 2020 02:38:20 PM EDT,, +5,1,1601977400,1601977400,Sun 28 Jun 2020 10:05:26 PM EDT,exercise, +6,2,1601977400,1601977400,Thu 02 Jul 2020 12:12:54 PM EDT,,doctor's office +7,1,1601977400,1601977400,Sat 04 Jul 2020 03:50:13 PM EDT,, +8,2,1601977400,1601977400,Sat 04 Jul 2020 03:54:27 PM EDT,, +9,2,1601977400,1601977400,Sun 05 Jul 2020 01:30:44 PM EDT,no THC, +10,1,1601977400,1601977400,Mon 06 Jul 2020 06:54:49 AM EDT,, +11,2,1601977400,1601977400,Sun 12 Jul 2020 12:29:11 PM EDT,THC, +12,2,1601977400,1601977400,Sun 12 Jul 2020 02:44:25 PM EDT,THC, +13,1,1601977400,1601977400,Sun 12 Jul 2020 02:44:25 PM EDT,THC, +14,3,1601977400,1601977400,Mon 13 Jul 2020 03:30:46 PM EDT,, +15,2,1601977400,1601977400,Fri 17 Jul 2020 02:02:12 PM EDT,, +16,2,1601977400,1601977400,Fri 17 Jul 2020 03:14:37 PM EDT,THC, +17,1,1601977400,1601977400,Fri 17 Jul 2020 03:14:37 PM EDT,, +18,2,1601977400,1601977400,Sat 08 Aug 2020 01:53:25 PM EDT,, +19,3,1601977400,1601977400,Thu 27 Aug 2020 12:44:37 PM EDT,, +20,2,1601977400,1601977400,Fri 28 Aug 2020 03:40:56 PM EDT,, +21,1,1601977400,1601977400,Fri 28 Aug 2020 03:40:56 PM EDT,THC,1st dabs/2 weeks +22,2,1601977400,1601977400,Sun 06 Sep 2020 11:49:47 AM EDT,, +23,1,1601977400,1601977400,Sun 06 Sep 2020 11:53:21 AM EDT,, +24,2,1601977400,1601977400,Sat 12 Sep 2020 12:15:53 PM EDT,, +25,2,1601977400,1601977400,Sun 06 Sep 2020,, +26,1,1601977400,1601977400,Sun 06 Sep 2020,, +27,2,1601977400,1601977400,Sat 12 Sep 2020,, +28,1,1601977400,1601977400,Sat 12 Sep 2020,, +29,1,1601977400,1601977400,Sun 13 Sep 2020,, +30,1,1601977400,1601977400,Mon 14 Sep 2020,, +31,1,1601977400,1601977400,Fri 18 Sep 2020 09:04:38 PM EDT,exercise, +32,2,1601977400,1601977400,Thu 24 Sep 2020 05:05:51 PM EDT,illness, +33,1,1601977400,1601977400,Thu 24 Sep 2020 05:05:51 PM EDT,, +34,2,1601977400,1601977400,Thu 24 Sep 2020,, +35,1,1601977400,1601977400,Fri 25 Sep 2020,gym scale, +36,2,1601977400,1601977400,Sat 26 Sep 2020,, +37,2,1601977400,1601977400,Sat 26 Sep 2020 11:54:17 AM EDT,, +38,1,1601977400,1601977400,Tue 29 Sep 2020 03:40:37 PM EDT,nude, diff --git a/sql/data/biometrics.csv b/sql/data/biometrics.csv index db27a05..9d9f985 100644 --- a/sql/data/biometrics.csv +++ b/sql/data/biometrics.csv @@ -1,30 +1,30 @@ -id,name,unit,created -1,Height,cm,1580336088 -2,Weight,kg,1580336088 -3,Wrist,cm,1580336088 -4,Ankle,cm,1580336088 -5,Chest,cm,1580336088 -6,Upper Arm,cm,1580336088 -7,Thigh,cm,1580336088 -8,Calf,cm,1580336088 -9,Shoulders,cm,1580336088 -10,Waist,cm,1580336088 -11,Hips,cm,1580336088 -12,Neck,cm,1580336088 -13,Forearm,cm,1580336088 -14,Pectoral,mm,1580336088 -15,Abdominal,mm,1580336088 -16,Quadricep,mm,1580336088 -17,Midaxillary,mm,1580336088 -18,Subscapular,mm,1580336088 -19,Tricep,mm,1580336088 -20,Suprailiac,mm,1580336088 -21,Body Fat (Custom),,1580336088 -22,Pulse,bpm,1580336088 -23,Systolic Blood Pressure,mmHg,1580336088 -24,Diastolic Blood Pressure,mmHg,1580336088 -25,Blood Glucose,mg/dL,1580336088 -26,Total Cholesterol,mg/dL,1580336088 -27,LDL Cholesterol,mg/dL,1580336088 -28,HDL Cholesterol,mg/dL,1580336088 -29,Triglycerides,mg/dL,1580336088 +id,created,updated,name,unit +1,1580336088,1580336088,Height,cm +2,1580336088,1580336088,Weight,kg +3,1580336088,1580336088,Wrist,cm +4,1580336088,1580336088,Ankle,cm +5,1580336088,1580336088,Chest,cm +6,1580336088,1580336088,Upper Arm,cm +7,1580336088,1580336088,Thigh,cm +8,1580336088,1580336088,Calf,cm +9,1580336088,1580336088,Shoulders,cm +10,1580336088,1580336088,Waist,cm +11,1580336088,1580336088,Hips,cm +12,1580336088,1580336088,Neck,cm +13,1580336088,1580336088,Forearm,cm +14,1580336088,1580336088,Pectoral,mm +15,1580336088,1580336088,Abdominal,mm +16,1580336088,1580336088,Quadricep,mm +17,1580336088,1580336088,Midaxillary,mm +18,1580336088,1580336088,Subscapular,mm +19,1580336088,1580336088,Tricep,mm +20,1580336088,1580336088,Suprailiac,mm +21,1580336088,1580336088,Body Fat (Custom), +22,1580336088,1580336088,Pulse,bpm +23,1580336088,1580336088,Systolic Blood Pressure,mmHg +24,1580336088,1580336088,Diastolic Blood Pressure,mmHg +25,1580336088,1580336088,Blood Glucose,mg/dL +26,1580336088,1580336088,Total Cholesterol,mg/dL +27,1580336088,1580336088,LDL Cholesterol,mg/dL +28,1580336088,1580336088,HDL Cholesterol,mg/dL +29,1580336088,1580336088,Triglycerides,mg/dL diff --git a/sql/data/food_log.csv b/sql/data/food_log.csv index e8df44c..986a00a 100644 --- a/sql/data/food_log.csv +++ b/sql/data/food_log.csv @@ -1,23 +1,23 @@ -id,profile_id,created,updated,last_sync,date,meal_id,food_id,grams -1,1,1601977400,1601977400,-1,2020-09-20,1,13047,100 -2,1,1601977400,1601977400,-1,2020-09-20,1,1270,28 -3,1,1601977400,1601977400,-1,2020-09-20,1,9038,55 -4,1,1601977400,1601977400,-1,2020-09-20,1,11251,20 -5,1,1601977400,1601977400,-1,2020-09-20,1,11529,35 -6,1,1601977400,1601977400,-1,2020-09-20,1,11282,15 -7,1,1601977400,1601977400,-1,2020-09-20,1,11828,210 -8,1,1601977400,1601977400,-1,2020-09-20,1,28313,40 -9,1,1601977400,1601977400,-1,2020-09-20,1,9112,100 -10,1,1601977400,1601977400,-1,2020-09-20,2,20137,140 -11,1,1601977400,1601977400,-1,2020-09-20,2,5062,100 -12,1,1601977400,1601977400,-1,2020-09-20,2,12136,45 -13,1,1601977400,1601977400,-1,2020-09-20,2,11821,50 -14,1,1601977400,1601977400,-1,2020-09-20,2,44005,15 -15,1,1601977400,1601977400,-1,2020-09-20,3,20545,150 -16,1,1601977400,1601977400,-1,2020-09-20,3,16146,85 -17,1,1601977400,1601977400,-1,2020-09-20,3,1270,40 -18,1,1601977400,1601977400,-1,2020-09-20,3,9037,60 -19,1,1601977400,1601977400,-1,2020-09-20,3,15076,100 -20,1,1601977400,1601977400,-1,2020-09-20,3,11090,60 -21,1,1601977400,1601977400,-1,2020-09-20,3,11938,35 -22,1,1601977400,1601977400,-1,2020-09-20,3,11282,25 +id,profile_id,created,updated,date,meal_id,food_id,grams +1,1,1601977400,1601977400,2020-09-20,1,13047,100 +2,1,1601977400,1601977400,2020-09-20,1,1270,28 +3,1,1601977400,1601977400,2020-09-20,1,9038,55 +4,1,1601977400,1601977400,2020-09-20,1,11251,20 +5,1,1601977400,1601977400,2020-09-20,1,11529,35 +6,1,1601977400,1601977400,2020-09-20,1,11282,15 +7,1,1601977400,1601977400,2020-09-20,1,11828,210 +8,1,1601977400,1601977400,2020-09-20,1,28313,40 +9,1,1601977400,1601977400,2020-09-20,1,9112,100 +10,1,1601977400,1601977400,2020-09-20,2,20137,140 +11,1,1601977400,1601977400,2020-09-20,2,5062,100 +12,1,1601977400,1601977400,2020-09-20,2,12136,45 +13,1,1601977400,1601977400,2020-09-20,2,11821,50 +14,1,1601977400,1601977400,2020-09-20,2,44005,15 +15,1,1601977400,1601977400,2020-09-20,3,20545,150 +16,1,1601977400,1601977400,2020-09-20,3,16146,85 +17,1,1601977400,1601977400,2020-09-20,3,1270,40 +18,1,1601977400,1601977400,2020-09-20,3,9037,60 +19,1,1601977400,1601977400,2020-09-20,3,15076,100 +20,1,1601977400,1601977400,2020-09-20,3,11090,60 +21,1,1601977400,1601977400,2020-09-20,3,11938,35 +22,1,1601977400,1601977400,2020-09-20,3,11282,25 diff --git a/sql/data/profiles.csv b/sql/data/profiles.csv index d3dc543..16e46a8 100644 --- a/sql/data/profiles.csv +++ b/sql/data/profiles.csv @@ -1,4 +1,4 @@ -id,name,created,updated,last_sync,eula,gender,dob,act_lvl,goal_wt,goal_bf,bmr_id,bf_id -1,Shane,1601067725,1601067725,-1,,MALE,1993-01-01,2,78,0.12,1,1 -2,Mark,1601068574,1601068574,-1,,MALE,1957-01-01,2,72,0.14,1,1 -3,Rocky,1601068718,1601068718,-1,,DOG,2007-08-01,2,7.3,,1,1 +id,name,created,updated,eula,gender,dob,act_lvl,goal_wt,goal_bf,bmr_id,bf_id +1,Shane,1601067725,1601067725,,MALE,1993-01-01,2,78,0.12,1,1 +2,Mark,1601068574,1601068574,,MALE,1957-01-01,2,72,0.14,1,1 +3,Rocky,1601068718,1601068718,,DOG,2007-08-01,2,7.3,,1,1 diff --git a/sql/data/rda.csv b/sql/data/rda.csv index 27caf10..f90d012 100644 --- a/sql/data/rda.csv +++ b/sql/data/rda.csv @@ -1,80 +1,80 @@ -profile_id,created,updated,last_sync,nutr_id,rda -3,1601978681,1601978681,-1,203,18 -3,1601978681,1601978681,-1,204,25 -3,1601978681,1601978681,-1,205,60 -3,1601978681,1601978681,-1,208,501 -3,1601978681,1601978681,-1,269,15 -3,1601978681,1601978681,-1,291,9 -3,1601978681,1601978681,-1,301,300 -3,1601978681,1601978681,-1,303,4 -3,1601978681,1601978681,-1,304,100 -3,1601978681,1601978681,-1,305,250 -3,1601978681,1601978681,-1,306,1300 -3,1601978681,1601978681,-1,307,350 -3,1601978681,1601978681,-1,309,3 -3,1601978681,1601978681,-1,312,0.2 -3,1601978681,1601978681,-1,315,0.6 -3,1601978681,1601978681,-1,317,15 -3,1601978681,1601978681,-1,318,1000 -3,1601978681,1601978681,-1,320,250 -3,1601978681,1601978681,-1,324,100 -3,1601978681,1601978681,-1,328,5 -3,1601978681,1601978681,-1,337,900 -3,1601978681,1601978681,-1,338,2000 -3,1601978681,1601978681,-1,401,30 -3,1601978681,1601978681,-1,404,0.3 -3,1601978681,1601978681,-1,405,0.35 -3,1601978681,1601978681,-1,406,4 -3,1601978681,1601978681,-1,410,1.25 -3,1601978681,1601978681,-1,415,0.4 -3,1601978681,1601978681,-1,417,100 -3,1601978681,1601978681,-1,418,0.6 -3,1601978681,1601978681,-1,421,110 -3,1601978681,1601978681,-1,430,30 -3,1601978681,1601978681,-1,501,0.1 -3,1601978681,1601978681,-1,502,0.3 -3,1601978681,1601978681,-1,503,0.5 -3,1601978681,1601978681,-1,504,1 -3,1601978681,1601978681,-1,505,0.8 -3,1601978681,1601978681,-1,506,0.3 -3,1601978681,1601978681,-1,508,0.3 -3,1601978681,1601978681,-1,509,0.4 -3,1601978681,1601978681,-1,510,0.5 -3,1601978681,1601978681,-1,511,0.3 -3,1601978681,1601978681,-1,512,0.2 -3,1601978681,1601978681,-1,513,0.3 -3,1601978681,1601978681,-1,514,0.3 -3,1601978681,1601978681,-1,515,0.5 -3,1601978681,1601978681,-1,516,0.2 -3,1601978681,1601978681,-1,517,0.2 -3,1601978681,1601978681,-1,518,0.4 -3,1601978681,1601978681,-1,601,50 -3,1601978681,1601978681,-1,605,0 -3,1601978681,1601978681,-1,606,7 -3,1601978681,1601978681,-1,621,0.1 -3,1601978681,1601978681,-1,629,0.05 -3,1601978681,1601978681,-1,645,10 -3,1601978681,1601978681,-1,646,6 -3,1601978681,1601978681,-1,710,8 -3,1601978681,1601978681,-1,711,4 -3,1601978681,1601978681,-1,713,11 -3,1601978681,1601978681,-1,731,5 -3,1601978681,1601978681,-1,734,10 -3,1601978681,1601978681,-1,735,5 -3,1601978681,1601978681,-1,736,5 -3,1601978681,1601978681,-1,737,2 -3,1601978681,1601978681,-1,738,1 -3,1601978681,1601978681,-1,749,10 -3,1601978681,1601978681,-1,750,10 -3,1601978681,1601978681,-1,751,10 -3,1601978681,1601978681,-1,752,10 -3,1601978681,1601978681,-1,753,15 -3,1601978681,1601978681,-1,759,20 -3,1601978681,1601978681,-1,762,15 -3,1601978681,1601978681,-1,770,8 -3,1601978681,1601978681,-1,773,5 -3,1601978681,1601978681,-1,785,5 -3,1601978681,1601978681,-1,786,1.5 -3,1601978681,1601978681,-1,788,0.5 -3,1601978681,1601978681,-1,789,5 -3,1601978681,1601978681,-1,851,0.3 +profile_id,nutr_id,rda +3,203,18 +3,204,25 +3,205,60 +3,208,501 +3,269,15 +3,291,9 +3,301,300 +3,303,4 +3,304,100 +3,305,250 +3,306,1300 +3,307,350 +3,309,3 +3,312,0.2 +3,315,0.6 +3,317,15 +3,318,1000 +3,320,250 +3,324,100 +3,328,5 +3,337,900 +3,338,2000 +3,401,30 +3,404,0.3 +3,405,0.35 +3,406,4 +3,410,1.25 +3,415,0.4 +3,417,100 +3,418,0.6 +3,421,110 +3,430,30 +3,501,0.1 +3,502,0.3 +3,503,0.5 +3,504,1 +3,505,0.8 +3,506,0.3 +3,508,0.3 +3,509,0.4 +3,510,0.5 +3,511,0.3 +3,512,0.2 +3,513,0.3 +3,514,0.3 +3,515,0.5 +3,516,0.2 +3,517,0.2 +3,518,0.4 +3,601,50 +3,605,0 +3,606,7 +3,621,0.1 +3,629,0.05 +3,645,10 +3,646,6 +3,710,8 +3,711,4 +3,713,11 +3,731,5 +3,734,10 +3,735,5 +3,736,5 +3,737,2 +3,738,1 +3,749,10 +3,750,10 +3,751,10 +3,752,10 +3,753,15 +3,759,20 +3,762,15 +3,770,8 +3,773,5 +3,785,5 +3,786,1.5 +3,788,0.5 +3,789,5 +3,851,0.3 diff --git a/sql/data/recipe_log.csv b/sql/data/recipe_log.csv index 4dea799..6c79532 100644 --- a/sql/data/recipe_log.csv +++ b/sql/data/recipe_log.csv @@ -1,2 +1,2 @@ -id,profile_id,created,updated,last_sync,date,meal_id,recipe_id,grams -1,1,1601977400,1601977400,-1,2020-09-26,1,1,315 +id,profile_id,created,updated,date,meal_id,recipe_id,grams +1,1,1601977400,1601977400,2020-09-26,1,1,315 diff --git a/sql/data/recipes.csv b/sql/data/recipes.csv index 5606b84..4e2b1e8 100644 --- a/sql/data/recipes.csv +++ b/sql/data/recipes.csv @@ -1,6 +1,6 @@ -id,created,updated,last_sync,name -1,1601235568,1601235568,-1,Burrito bowl (Everyday) -2,1601235568,1601235568,-1,"Burger (Grass fed, Beef)" -3,1601235568,1601235568,-1,Baked potato wedges -4,1601235568,1601235568,-1,Buckwheat pancake (w/ syrup) -5,1601235568,1601235568,-1,Blueberry-hemp Smoothie +id,created,updated,tagname,name +1,1601235568,1601235568,burrito-bowl,Burrito bowl (Everyday) +2,1601235568,1601235568,burger,"Burger (Grass fed, Beef)" +3,1601235568,1601235568,potato-wedges,Baked potato wedges +4,1601235568,1601235568,buckwheat-pancake,Buckwheat pancake (w/ syrup) +5,1601235568,1601235568,blueberry-hemp-smoothie,Blueberry-hemp Smoothie diff --git a/sql/data/version.csv b/sql/data/version.csv index 094dc72..c61d3aa 100644 --- a/sql/data/version.csv +++ b/sql/data/version.csv @@ -2,3 +2,4 @@ id,version,created,notes 1,0.0.0,2020-09-22,initial release 2,0.0.1,2021-05-21,bump version 3,0.0.2,2021-05-24,remove guids +4,0.0.3,2021-05-24,general cleanup diff --git a/sql/tables.sql b/sql/tables.sql index c3f29f7..24ddab4 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -17,6 +17,8 @@ CREATE TABLE version( id integer PRIMARY KEY AUTOINCREMENT, version text NOT NULL, created date NOT NULL, notes text ); +-- TODO: enforce FK constraint across two DBs? + -- --------------------------------- -- Equations @@ -42,7 +44,6 @@ CREATE TABLE profiles ( name text NOT NULL UNIQUE, created int DEFAULT (strftime ('%s', 'now')), updated int DEFAULT (strftime ('%s', 'now')), - last_sync int DEFAULT - 1, eula int DEFAULT 0, gender text, dob date, @@ -76,9 +77,10 @@ CREATE TABLE food_costs ( CREATE TABLE biometrics ( -- TODO: support custom biometrics and sync? id integer PRIMARY KEY AUTOINCREMENT, + created int DEFAULT (strftime ('%s', 'now')), + updated int DEFAULT (strftime ('%s', 'now')), name text NOT NULL UNIQUE, - unit text, - created int DEFAULT (strftime ('%s', 'now')) + unit text ); CREATE TABLE biometric_log ( @@ -86,7 +88,6 @@ CREATE TABLE biometric_log ( profile_id int NOT NULL, created int DEFAULT (strftime ('%s', 'now')), updated int DEFAULT (strftime ('%s', 'now')), - last_sync int DEFAULT - 1, date int DEFAULT (strftime ('%s', 'now')), tags text, notes text, @@ -111,13 +112,12 @@ CREATE TABLE recipes ( id integer PRIMARY KEY AUTOINCREMENT, created int DEFAULT (strftime ('%s', 'now')), updated int DEFAULT (strftime ('%s', 'now')), - last_sync int DEFAULT - 1, - name text NOT NULL + tagname text NOT NULL UNIQUE, + name text NOT NULL UNIQUE ); CREATE TABLE recipe_dat ( recipe_id int NOT NULL, - -- TODO: enforce FK constraint across two DBs? food_id int NOT NULL, grams real NOT NULL, notes text, @@ -141,10 +141,9 @@ CREATE TABLE food_log ( profile_id int NOT NULL, created int DEFAULT (strftime ('%s', 'now')), updated int DEFAULT (strftime ('%s', 'now')), - last_sync int DEFAULT - 1, - date date DEFAULT CURRENT_DATE, + date int DEFAULT (strftime ('%s', 'now')), meal_id int NOT NULL, - food_id int NOT NULL, -- TODO: enforce FK constraint across two DBs? + food_id int NOT NULL, grams real NOT NULL, FOREIGN KEY (profile_id) REFERENCES profiles (id) ON UPDATE CASCADE, FOREIGN KEY (meal_id) REFERENCES meals (id) ON UPDATE CASCADE @@ -155,8 +154,7 @@ CREATE TABLE recipe_log ( profile_id int NOT NULL, created int DEFAULT (strftime ('%s', 'now')), updated int DEFAULT (strftime ('%s', 'now')), - last_sync int DEFAULT - 1, - date date DEFAULT CURRENT_DATE, + date int DEFAULT (strftime ('%s', 'now')), meal_id int NOT NULL, recipe_id int NOT NULL, grams real NOT NULL, @@ -165,6 +163,8 @@ CREATE TABLE recipe_log ( FOREIGN KEY (recipe_id) REFERENCES recipes (id) ON UPDATE CASCADE ); +-- TODO: CREATE TABLE custom_food_log ( ... ); + -- -------------------------------- -- Custom RDAs @@ -172,10 +172,6 @@ CREATE TABLE recipe_log ( CREATE TABLE rda ( profile_id int NOT NULL, - created int DEFAULT (strftime ('%s', 'now')), - updated int DEFAULT (strftime ('%s', 'now')), - last_sync int DEFAULT - 1, - -- TODO: enforce FK constraint across two DBs? nutr_id int NOT NULL, rda real NOT NULL, PRIMARY KEY (profile_id, nutr_id), -- 2.52.0