1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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;
|
