-- 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])
+);