From 36a2fea6863e692d815a513d4b0f15b8c1fcb886 Mon Sep 17 00:00:00 2001
From: William Carroll <wpcarro@gmail.com>
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

