X-Git-Url: http://git.euphorik.ch/?p=recipes.git;a=blobdiff_plain;f=backend%2Fsql%2Fversion_1.sql;fp=backend%2Fsql%2Fversion_1.sql;h=d68982dac208db01767a2ff5674fbb1b746b227e;hp=0000000000000000000000000000000000000000;hb=108476e3554ea3a25dca5b5ab260f38c1e734221;hpb=a080d19cb9076780db9e86325b2ab617886e2a5f

diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql
new file mode 100644
index 0000000..d68982d
--- /dev/null
+++ b/backend/sql/version_1.sql
@@ -0,0 +1,64 @@
+-- Version 1 is the initial structure.
+CREATE TABLE Version (
+    id INTEGER PRIMARY KEY,
+    version INTEGER NOT NULL UNIQUE,
+    datetime DATETIME
+);
+
+CREATE TABLE User (
+    id INTEGER PRIMARY KEY,
+    email TEXT NOT NULL,
+    password TEXT NOT NULL, -- Hashed and salted.
+    name TEXT NOT NULL
+);
+
+CREATE TABLE Recipe (
+    id INTEGER PRIMARY KEY,
+    user_id INTEGER NOT NULL,
+    title TEXT NOT NULL,
+    estimate_time INTEGER,
+    description DATETIME,
+
+    FOREIGN KEY(user_id) REFERENCES User(id)
+);
+
+CREATE TABLE Quantity (
+    id INTEGER PRIMARY KEY,
+    value REAL,
+    unit TEXT
+);
+
+CREATE TABLE Ingredient (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL,
+    quantity_id INTEGER,
+    input_step_id INTEGER NOT NULL,
+
+    FOREIGN KEY(quantity_id) REFERENCES Quantity(id),
+    FOREIGN KEY(input_step_id) REFERENCES Step(id)
+);
+
+CREATE TABLE [Group] (
+    id INTEGER PRIMARY KEY,
+    name TEXT
+);
+
+CREATE TABLE Step (
+    id INTEGER PRIMARY KEY,
+    action TEXT NOT NULL,
+    group_id INTEGER NOT NULL,
+
+    FOREIGN KEY(group_id) REFERENCES [Group](id)
+);
+
+CREATE TABLE IntermediateSubstance (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL,
+    quantity_id INTEGER,
+    output_step_id INTEGER NOT NULL,
+    input_step_id INTEGER NOT NULL,
+
+    FOREIGN KEY(quantity_id) REFERENCES Quantity(id),
+    FOREIGN KEY(output_step_id) REFERENCES Step(id),
+    FOREIGN KEY(input_step_id) REFERENCES Step(id)
+);
\ No newline at end of file