[password] TEXT NOT NULL, -- argon2(password_plain, salt).
[creation_datetime] DATETIME NOT NULL, -- Updated when the validation email is sent.
- [validation_token] TEXT -- If not null then the user has not validated his account yet.
+ [validation_token] TEXT, -- If not null then the user has not validated his account yet.
+
+ [is_admin] INTEGER NOT NULL DEFAULT FALSE
);
CREATE UNIQUE INDEX [User_email_index] ON [User] ([email]);
[ip] TEXT, -- Can be ipv4 or ipv6
[user_agent] TEXT,
- FOREIGN KEY([user_id]) REFERENCES [User]([id])
+ FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE
);
CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken] ([token]);
CREATE TABLE [Recipe] (
[id] INTEGER PRIMARY KEY,
- [user_id] INTEGER NOT NULL,
+ [user_id] INTEGER, -- Can be null if a user is deleted.
[title] TEXT NOT NULL,
[estimate_time] INTEGER,
[description] TEXT,
[servings] INTEGER DEFAULT 4,
- FOREIGN KEY([user_id]) REFERENCES [User]([id])
+ FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL
);
CREATE TABLE [Quantity] (
[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]) ON DELETE CASCADE,
+ FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ON DELETE CASCADE
);
CREATE TABLE [Group] (
[recipe_id] INTEGER,
name TEXT,
- FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id])
+ FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
);
CREATE INDEX [Group_order_index] ON [Group] ([order]);
[action] TEXT NOT NULL,
[group_id] INTEGER NOT NULL,
- FOREIGN KEY(group_id) REFERENCES [Group](id)
+ FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
);
CREATE INDEX [Step_order_index] ON [Group] ([order]);
[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])
+ FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]) ON DELETE CASCADE,
+ FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE,
+ FOREIGN KEY([input_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE
);