Added routines for validating user passwords and creating new users. Added routines for looking up sessions.

1 files changed, 5 insertions, 0 deletions

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;