From a8fde439693db16071e97a4e13b9654553da88d7 Mon Sep 17 00:00:00 2001
From: gamesguru <mathmuncher11@gmail.com>
Date: Thu, 17 Jun 2021 11:42:53 -0400
Subject: [PATCH] add custom foods tables (custom_foods, cf_dat)

---
 sql/data/version.csv |  1 +
 sql/functions.sql    |  2 --
 sql/tables.sql       | 50 +++++++++++++++++++++++++++++++-------------
 3 files changed, 36 insertions(+), 17 deletions(-)

diff --git a/sql/data/version.csv b/sql/data/version.csv
index c61d3aa..308de8a 100644
--- a/sql/data/version.csv
+++ b/sql/data/version.csv
@@ -3,3 +3,4 @@ id,version,created,notes
 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
+5,0.0.4,2021-06-17,"add custom foods tables (custom_foods, cf_dat)"
diff --git a/sql/functions.sql b/sql/functions.sql
index dda9dbc..1746314 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -169,7 +169,6 @@ GROUP BY
 --------------------------------
 -- Last sync
 --------------------------------
-
 -- SELECT
 --   max((
 --     SELECT
@@ -184,4 +183,3 @@ GROUP BY
 --       last_sync FROM recipe_log), (
 --     SELECT
 --       last_sync FROM rda)) AS last_sync;
-
diff --git a/sql/tables.sql b/sql/tables.sql
index d8812f7..656b803 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -18,7 +18,6 @@ CREATE TABLE version( id integer PRIMARY KEY AUTOINCREMENT, version text NOT NUL
 );
 
 -- TODO: enforce FK constraint across two DBs?
-
 --
 ---------------------------------
 -- Equations
@@ -56,19 +55,6 @@ CREATE TABLE profiles (
   FOREIGN KEY (bf_eq_id) REFERENCES bf_eqs (id) ON UPDATE CASCADE
 );
 
---
---------------------------------
--- Food costs
---------------------------------
-
-CREATE TABLE food_costs (
-  food_id integer NOT NULL,
-  profile_id integer 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
-);
-
 --
 --------------------------------
 -- Biometrics
@@ -125,6 +111,28 @@ CREATE TABLE recipe_dat (
   FOREIGN KEY (recipe_id) REFERENCES recipes (id) ON UPDATE CASCADE
 );
 
+--
+--------------------------------
+-- Custom foods
+--------------------------------
+
+CREATE TABLE custom_foods (
+  id integer PRIMARY KEY AUTOINCREMENT,
+  created int DEFAULT (strftime ('%s', 'now')),
+  updated int DEFAULT (strftime ('%s', 'now')),
+  tagname text NOT NULL UNIQUE,
+  name text NOT NULL UNIQUE
+);
+
+CREATE TABLE cf_dat (
+  cf_id int NOT NULL,
+  nutr_id int NOT NULL, -- no FK constraing on usda :[
+  nutr_val real NOT NULL,
+  notes text,
+  PRIMARY KEY (cf_id, nutr_id),
+  FOREIGN KEY (cf_id) REFERENCES custom_foods (id) ON UPDATE CASCADE
+);
+
 --
 --------------------------------
 -- Food (and recipe) logs
@@ -164,7 +172,6 @@ CREATE TABLE recipe_log (
 );
 
 -- TODO: CREATE TABLE custom_food_log ( ... );
-
 --
 --------------------------------
 -- Custom RDAs
@@ -178,3 +185,16 @@ CREATE TABLE rda (
   FOREIGN KEY (profile_id) REFERENCES profiles (id) ON UPDATE CASCADE
 );
 
+--
+--------------------------------
+-- Food costs
+--------------------------------
+
+CREATE TABLE food_costs (
+  food_id integer NOT NULL,
+  profile_id integer 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