From ff0095ff928dca78bf07148c4081191fd57f9c4d Mon Sep 17 00:00:00 2001 From: Jeffrey Armstrong Date: Tue, 26 Apr 2022 17:10:33 -0400 Subject: Added routines for validating user passwords and creating new users. Added routines for looking up sessions. --- captain/sql/create.sql | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'captain/sql/create.sql') diff --git a/captain/sql/create.sql b/captain/sql/create.sql index 8e93e23..9e4d204 100644 --- a/captain/sql/create.sql +++ b/captain/sql/create.sql @@ -17,3 +17,8 @@ CREATE TABLE checkin(player INTEGER PRIMARY KEY, year INTEGER, month INTEGER, da CREATE TABLE schedule(instruction INTEGER NOT NULL, player INTEGER NOT NULL, day INTEGER DEFAULT 0, hour INTEGER DEFAULT 0, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE ); +CREATE TABLE users(id INTEGER PRIMARY KEY, username TEXT UNIQUE NOT NULL, email TEXT NOT NULL, password TEXT NOT NULL, level INTEGER DEFAULT 1); + +CREATE TABLE sessions(user INTEGER, session TEXT NOT NULL, expiration TEXT NOT NULL, FOREIGN KEY(user) REFERENCES users(id) ON DELETE CASCADE); + +CREATE VIEW session_auth AS SELECT sessions.session, users.username, users.level FROM sessions INNER JOIN users ON sessions.user = users.id; -- cgit v1.2.3