grocery-bot

Log | Files | Refs | README

models.py (2029B)

      1 """SQLite connection handling and schema definition."""
      2 import os
      3 import sqlite3
      4 
      5 DB_PATH = os.environ.get("ANTON_DB_PATH", "anton.db")
      6 
      7 SCHEMA = """
      8 CREATE TABLE IF NOT EXISTS recipes (
      9     id INTEGER PRIMARY KEY,
     10     name TEXT NOT NULL,
     11     servings INTEGER,
     12     instructions TEXT,
     13     source TEXT,
     14     is_keto INTEGER NOT NULL DEFAULT 0
     15 );
     16 
     17 CREATE TABLE IF NOT EXISTS ingredients (
     18     id INTEGER PRIMARY KEY,
     19     recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
     20     name TEXT NOT NULL,
     21     quantity TEXT,
     22     unit TEXT,
     23     category TEXT
     24 );
     25 
     26 CREATE TABLE IF NOT EXISTS staples (
     27     id INTEGER PRIMARY KEY,
     28     name TEXT NOT NULL UNIQUE,
     29     default_quantity TEXT,
     30     unit TEXT,
     31     category TEXT
     32 );
     33 
     34 CREATE TABLE IF NOT EXISTS grocery_list_items (
     35     id INTEGER PRIMARY KEY,
     36     name TEXT NOT NULL,
     37     quantity TEXT,
     38     unit TEXT,
     39     category TEXT,
     40     source TEXT NOT NULL CHECK (source IN ('staple', 'recipe', 'freeform'))
     41 );
     42 
     43 -- "Groups" for manually classifying recipes (e.g. "slowcooker"), separate
     44 -- from the automatic is_keto tag. A tag can exist with zero recipes
     45 -- attached (created ahead of time via /tag add <group>), which is why
     46 -- this is its own table rather than just a column on recipe_tags.
     47 CREATE TABLE IF NOT EXISTS tags (
     48     id INTEGER PRIMARY KEY,
     49     name TEXT NOT NULL UNIQUE COLLATE NOCASE
     50 );
     51 
     52 CREATE TABLE IF NOT EXISTS recipe_tags (
     53     recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
     54     tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
     55     PRIMARY KEY (recipe_id, tag_id)
     56 );
     57 """
     58 
     59 
     60 def get_connection(db_path: str = DB_PATH) -> sqlite3.Connection:
     61     conn = sqlite3.connect(db_path)
     62     conn.row_factory = sqlite3.Row
     63     # SQLite parses the "REFERENCES ... ON DELETE CASCADE" in the schema but
     64     # does NOT enforce foreign keys by default -- must opt in per connection.
     65     conn.execute("PRAGMA foreign_keys = ON")
     66     return conn
     67 
     68 
     69 def init_db(conn: sqlite3.Connection) -> None:
     70     conn.executescript(SCHEMA)
     71     conn.commit()