diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | README.md | 44 | ||||
| -rw-r--r-- | data/accounts.csv | 3 | ||||
| -rw-r--r-- | data/trips.csv | 3 | ||||
| -rw-r--r-- | src/init.sql | 31 |
5 files changed, 82 insertions, 0 deletions
diff --git a/.gitignore b/.gitignore index c50ada2bf6ea..aa7648cec692 100644 --- a/.gitignore +++ b/.gitignore @@ -1,3 +1,4 @@ *.db +*.sqlite3 *.db-shm *.db-wal \ No newline at end of file diff --git a/README.md b/README.md index e69de29bb2d1..e6d20d649e02 100644 --- a/README.md +++ b/README.md @@ -0,0 +1,44 @@ +# TopTal take-home #2 + +All of the commands defined herein should be run from the top-level directory of +this repository (i.e. the directory in which this file exists). + +## Database + +Create a new database named `db.sqlite3` with: + +```shell +$ sqlite3 db.sqlite3 +``` + +Initialize the schema with: + +``` +sqlite> .read src/init.sql +``` + +You can verify that you successfully initialized the database by running: + +``` +sqlite> .tables +sqlite> .schema Accounts +sqlite> .schema Trips +``` + +Populate the database with some dummy values using the following: + +``` +sqlite> PRAGMA foreign_keys = on; +sqlite> .mode csv +sqlite> .import data/accounts.csv Accounts +sqlite> .import data/trips.csv Trips +``` + +You can verify you successfully populated the tables with: + +``` +sqlite> .mode columns +sqlite> .headers on +sqlite> SELECT * FROM Accounts; +sqlite> SELECT * FROM Trips; +``` diff --git a/data/accounts.csv b/data/accounts.csv new file mode 100644 index 000000000000..51af23eec65e --- /dev/null +++ b/data/accounts.csv @@ -0,0 +1,3 @@ +mimi,testing,miriamwright@google.com,user, +bill,testing,wpcarro@gmail.com,manager, +wpcarro,testing,wpcarro@google.com,admin, \ No newline at end of file diff --git a/data/trips.csv b/data/trips.csv new file mode 100644 index 000000000000..3377efeba4a1 --- /dev/null +++ b/data/trips.csv @@ -0,0 +1,3 @@ +mimi,Rome,2020-08-10,2020-15-30,Heading home before the upcoming trip with Panarea. +mimi,Panarea,2020-08-15,2020-05-30,Exciting upcoming trip with Matt and Sarah! +mimi,London,2020-08-30,2020-08-30,Heading back to London... \ No newline at end of file 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; |