Add some data access methods to Connection

[recipes.git] / backend / sql / version_1.sql

diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql
index d68982d..9673d25 100644 (file)
--- a/backend/sql/version_1.sql
+++ b/backend/sql/version_1.sql
@@ -1,64 +1,73 @@
 -- Version 1 is the initial structure.
-CREATE TABLE Version (
-    id INTEGER PRIMARY KEY,
-    version INTEGER NOT NULL UNIQUE,
-    datetime DATETIME
+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 [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,
+CREATE TABLE [Recipe] (
+    [id] INTEGER PRIMARY KEY,
+    [user_id] INTEGER NOT NULL,
+    [title] TEXT NOT NULL,
+    [estimate_time] INTEGER,
+    [description] TEXT,
 
-    FOREIGN KEY(user_id) REFERENCES User(id)
+    FOREIGN KEY([user_id]) REFERENCES [User]([id])
 );
 
-CREATE TABLE Quantity (
-    id INTEGER PRIMARY KEY,
-    value REAL,
-    unit TEXT
+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,
+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)
+    FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]),
+    FOREIGN KEY([input_step_id]) REFERENCES Step([id])
 );
 
 CREATE TABLE [Group] (
-    id INTEGER PRIMARY KEY,
-    name TEXT
+    [id] INTEGER PRIMARY KEY,
+    [order] INTEGER NOT NULL DEFAULT 0,
+    [recipe_id] INTEGER,
+    name TEXT,
+
+    FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id])
 );
 
-CREATE TABLE Step (
-    id INTEGER PRIMARY KEY,
-    action TEXT NOT NULL,
-    group_id INTEGER NOT NULL,
+CREATE INDEX [Group_order_index] ON [Group] ([order]);
+
+CREATE TABLE [Step] (
+    [id] INTEGER PRIMARY KEY,
+    [order] INTEGER NOT NULL DEFAULT 0,
+    [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,
+CREATE INDEX [Step_order_index] ON [Group] ([order]);
 
-    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
+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])
+);