WIP: model: Rework database modeldb-completion

1 files changed, 66 insertions, 13 deletions

diff --git a/model/sql/migrate.sql b/model/sql/migrate.sql
index 4cbf75b..57f9e1d 100644
--- a/model/sql/migrate.sql
+++ b/model/sql/migrate.sql
@@ -1,14 +1,67 @@
-CREATE TABLE 'recipes' (
-	'id'       integer PRIMARY KEY AUTOINCREMENT,
-	'title'    text    DEFAULT '',
-	'portions' text    DEFAULT '',  -- FIXME has to be integer
-	'url'      text    DEFAULT '',
-	'notes'    text    DEFAULT ''
-);
-
-CREATE TABLE 'recipe_steps' (
-	'id'        integer PRIMARY KEY,
-	'recipe_id' integer DEFAULT NULL,
-	'index'     integer DEFAULT NULL,
-	'text'      text    DEFAULT ''
+PRAGMA foreign_keys = ON;
+
+CREATE TABLE instances (
+	id           INTEGER PRIMARY KEY,
+	uuid         TEXT    NOT NULL,
+	name         TEXT    NOT NULL,
+	is_self      BOOLEAN NOT NULL,
+	last_changed INTEGER NOT NULL
+);
+
+CREATE TABLE users (
+	id           INTEGER PRIMARY KEY,
+	uuid         TEXT    NOT NULL,
+	name         TEXT    NOT NULL,
+	home         INTEGER NOT NULL,
+	last_changed INTEGER NOT NULL,
+	FOREIGN KEY(home) REFERENCES instances(id)
+);
+
+CREATE TABLE recipes (
+	id   INTEGER PRIMARY KEY,
+	uuid TEXT    NOT NULL
+);
+
+CREATE TABLE recipe_versions (
+	id           INTEGER PRIMARY KEY,
+	parent       INTEGER NOT NULL,
+	recipe       INTEGER NOT NULL,
+	author       INTEGER NOT NULL,
+	instance     INTEGER NOT NULL,
+	language     TEXT    NOT NULL,
+	title        TEXT    NOT NULL,
+	portions     INTEGER NOT NULL,
+	url          TEXT    NOT NULL,
+	notes        TEXT    NOT NULL,
+	last_changed INTEGER NOT NULL,
+	FOREIGN KEY(parent)   REFERENCES recipe_versions(id),
+	FOREIGN KEY(recipe)   REFERENCES recipes(id),
+	FOREIGN KEY(author)   REFERENCES users(id),
+	FOREIGN KEY(instance) REFERENCES instances(id)
+);
+
+CREATE TABLE steps (
+	id   INTEGER PRIMARY KEY,
+	text TEXT    NOT NULL
+);
+
+CREATE TABLE ingredients (
+	id            INTEGER PRIMARY KEY,
+	ingredient_id TEXT    NOT NULL,
+	amount        REAL    NOT NULL,
+	unit          INTEGER NOT NULL -- must match unit enum from application code
+);
+
+CREATE TABLE foodstuffs (
+	id     INTEGER PRIMARY KEY,
+	uuid   TEXT    NOT NULL,
+	name   TEXT    NOT NULL, -- in US english
+	plural TEXT              -- in US english or NULL if uncountable
+);
+
+CREATE TABLE products (
+	id   INTEGER PRIMARY KEY,
+	uuid TEXT    NOT NULL,
+	name TEXT    NOT NULL,
+	gtin TEXT
 );