diff options
author | William Carroll <wpcarro@gmail.com> | 2020-07-27T10·16+0100 |
---|---|---|
committer | William Carroll <wpcarro@gmail.com> | 2020-07-27T10·16+0100 |
commit | df13b761ff945db894ade4dba6c68fb6f14c8615 (patch) | |
tree | 26737576453b2b390203fd2c23fccb113ce705d2 /src | |
parent | 722205b0818a7fb2280941554baaff9400808d65 (diff) |
Define table schema and CSVs to populate the database
TL;DR: - Created src/init.sql, which defines the tables - Created a data/ directory to house .csv data to populate our db - Updated the README with usage instructions
Diffstat (limited to 'src')
-rw-r--r-- | src/init.sql | 31 |
1 files changed, 31 insertions, 0 deletions
diff --git a/src/init.sql b/src/init.sql new file mode 100644 index 000000000000..951ea3ecbf33 --- /dev/null +++ b/src/init.sql @@ -0,0 +1,31 @@ +-- Run `.read init.sql` from within a SQLite3 REPL to initialize the tables we +-- need for this application. This will erase all current entries, so use with +-- caution. +-- Make sure to set `PRAGMA foreign_keys = on;` when transacting with the +-- database. + +BEGIN TRANSACTION; + +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, + 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 + comment TEXT NOT NULL, + PRIMARY KEY (username, destination, startDate), + FOREIGN KEY (username) REFERENCES Accounts ON DELETE CASCADE +); + +COMMIT; |