DuckDB schema for physical activity tracking

schema.sql

CREATE TABLE library_exercise (
    name VARCHAR PRIMARY KEY,
    met NUMERIC
);
COPY library_exercise FROM 'library/exercises.csv' (AUTO_DETECT TRUE);

CREATE TABLE library_food (
    name VARCHAR PRIMARY KEY,
    calories USMALLINT,
    protein USMALLINT,
    fat USMALLINT,
    carbs USMALLINT
);
COPY library_food FROM 'library/foods.csv' (AUTO_DETECT TRUE);

CREATE TABLE exercise (
    date DATE,
    exercise VARCHAR,
    FOREIGN KEY(exercise) REFERENCES library_exercise(name)
);
COPY exercise FROM 'data/exercises.csv' (AUTO_DETECT TRUE);

CREATE TABLE food (
    date DATE,
    food VARCHAR,
    FOREIGN KEY(food) REFERENCES library_food(name)
);
COPY food FROM 'data/foods.csv' (AUTO_DETECT TRUE);

CREATE TABLE plan (
    day_num USMALLINT PRIMARY KEY,
    date DATE,
    lbs DECIMAL(4,1),
    lbs_upper DECIMAL(4,1),
    lbs_lower DECIMAL(4,1),
    body_fat_percent DECIMAL(3,1),
    body_fat_percent_upper DECIMAL(3,1),
    body_fat_percent_lower DECIMAL(3,1),
    bmi DECIMAL(3,1),
    lbs_fat DECIMAL(4,1),
    lbs_fat_free DECIMAL(4,1),
    calories_in USMALLINT,
    calories_out USMALLINT
);
COPY plan FROM 'data/plans.csv' (SKIP 1, HEADER 1, DATEFORMAT '%m/%d/%y');

CREATE TABLE weighin (
    date DATE,
    lbs DECIMAL(4,1)
);
COPY weighin FROM 'data/weighins.csv' (AUTO_DETECT TRUE);