Add CHECK constraints to schema

I believe data should be validated at each level of the stack:
- database
- server
- client

The database, in my opinion, is the most important layer at which to validate
because you can eliminate entire classes of bugs. However, the CHECK constraint
is limited, and the more complex the predicates are, the more expensive database
operations become.

At the server and client layers, the data validations can be more sophisticated
and return more useful error messages to help users better understand the shape
of the data that our application expects.

1 files changed, 8 insertions, 7 deletions

diff --git a/src/init.sql b/src/init.sql
index 951ea3ecbf33..f1109feacba1 100644
--- a/src/init.sql
+++ b/src/init.sql
@@ -10,19 +10,20 @@ DROP TABLE IF EXISTS Accounts;
 DROP TABLE IF EXISTS Trips;
 
 CREATE TABLE Accounts (
-  username TEXT NOT NULL,
-  password TEXT NOT NULL,
-  email TEXT NOT NULL UNIQUE,
-  role TEXT NOT NULL,
+-- TODO(wpcarro): Add CHECK(..) constraint
+  username TEXT CHECK(LENGTH(username) > 0) NOT NULL,
+  password TEXT CHECK(LENGTH(password) > 0) NOT NULL,
+  email TEXT CHECK(LENGTH(email) > 0) NOT NULL UNIQUE,
+  role TEXT CHECK(role IN ('user', 'manager', 'admin')) NOT NULL,
   profilePicture BLOB,
   PRIMARY KEY (username)
 );
 
 CREATE TABLE Trips (
   username TEXT NOT NULL,
-  destination TEXT NOT NULL,
-  startDate TEXT NOT NULL, -- YYYY-MM-DD
-  endDate TEXT NOT NULL, -- YYYY-MM-DD
+  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
   comment TEXT NOT NULL,
   PRIMARY KEY (username, destination, startDate),
   FOREIGN KEY (username) REFERENCES Accounts ON DELETE CASCADE