Pantry

schema.sql 31 lines

CREATE TABLE quantity_units (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  name_plural TEXT,
  description TEXT
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  location_id INTEGER,
  qu_id_purchase INTEGER REFERENCES quantity_units(id),
  qu_id_stock INTEGER REFERENCES quantity_units(id),
  min_stock_amount REAL NOT NULL DEFAULT 0 CHECK (min_stock_amount >= 0),
  default_best_before_days INTEGER NOT NULL DEFAULT 0 CHECK (default_best_before_days >= 0),
  default_best_before_days_after_open INTEGER NOT NULL DEFAULT 0 CHECK (default_best_before_days_after_open >= 0),
  product_group_id INTEGER
);
CREATE TABLE stock_entries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL REFERENCES products(id),
  location_id INTEGER,
  amount REAL NOT NULL CHECK (amount > 0),
  best_before_date TEXT,
  purchased_date TEXT,
  stock_id TEXT NOT NULL UNIQUE DEFAULT ('local-' || lower(hex(randomblob(8)))),
  price REAL,
  open INTEGER NOT NULL DEFAULT 0 CHECK (open IN (0, 1)),
  opened_date TEXT,
  note TEXT
);