From 36a2fea6863e692d815a513d4b0f15b8c1fcb886 Mon Sep 17 00:00:00 2001 From: William Carroll Date: Tue, 28 Jul 2020 18:40:17 +0100 Subject: Create Sessions table TL;DR: - Create Sessions SQL schema - Create Sessions module - Introduce UUID dependency --- src/init.sql | 13 +++++++++++-- 1 file changed, 11 insertions(+), 2 deletions(-) (limited to 'src/init.sql') diff --git a/src/init.sql b/src/init.sql index f1109feacba1..1439bd338835 100644 --- a/src/init.sql +++ b/src/init.sql @@ -8,6 +8,7 @@ BEGIN TRANSACTION; DROP TABLE IF EXISTS Accounts; DROP TABLE IF EXISTS Trips; +DROP TABLE IF EXISTS Sessions; CREATE TABLE Accounts ( -- TODO(wpcarro): Add CHECK(..) constraint @@ -22,11 +23,19 @@ CREATE TABLE Accounts ( CREATE TABLE Trips ( username TEXT NOT NULL, destination TEXT CHECK(LENGTH(destination) > 0) NOT NULL, - startDate TEXT CHECK(LENGTH(startDate) == 10) NOT NULL, -- YYYY-MM-DD - endDate TEXT CHECK(LENGTH(endDate) == 10) NOT NULL, -- YYYY-MM-DD + startDate TEXT CHECK(LENGTH(startDate) == 10) NOT NULL, -- 'YYYY-MM-DD' + endDate TEXT CHECK(LENGTH(endDate) == 10) NOT NULL, -- 'YYYY-MM-DD' comment TEXT NOT NULL, PRIMARY KEY (username, destination, startDate), FOREIGN KEY (username) REFERENCES Accounts ON DELETE CASCADE ); +CREATE TABLE Sessions ( + uuid TEXT CHECK(LENGTH(uuid) == 36) NOT NULL, + username TEXT NOT NULL UNIQUE, + tsCreated TEXT CHECK(LENGTH(tsCreated) == 33) NOT NULL, -- 'YYYY-MM-DD HH:MM:SS' + PRIMARY KEY (uuid), + FOREIGN KEY (username) REFERENCES Accounts ON DELETE CASCADE +); + COMMIT; -- cgit 1.4.1