ubmkNDry

1DROP DATABASE IF EXISTS cs336g32;
2CREATE DATABASE cs336g32;
3USE cs336g32;
4
5DROP TABLE IF EXISTS users;
6DROP TABLE IF EXISTS rideOffers;
7DROP TABLE IF EXISTS rides;
8DROP TABLE IF EXISTS rating;
9DROP TABLE IF EXISTS messages;
10
11CREATE TABLE users(
12	username	VARCHAR(20) UNIQUE NOT NULL,
13    password	VARCHAR(50) NOT NULL,
14    email 		VARCHAR(30) UNIQUE NOT NULL,
15    name		VARCHAR(20),
16    address		VARCHAR(50),
17    phone		VARCHAR(16),
18    carDesc		VARCHAR(20) DEFAULT "None",
19    carNumPassengers	INT DEFAULT "0",
20    type		VARCHAR(5) DEFAULT "user",
21    PRIMARY KEY (username)
22);
23
24CREATE TABLE rideOffers(
25	rideId INT UNIQUE NOT NULL AUTO_INCREMENT,
26	driver VARCHAR(20) NOT NULL,
27    carDesc VARCHAR(20) NOT NULL,
28    departure VARCHAR(20) NOT NULL,
29    destination VARCHAR(20) NOT NULL,
30    timeWindow VARCHAR(20) NOT NULL,
31    recurring BOOL NOT NULL DEFAULT "0",
32    PRIMARY KEY(rideId),
33    FOREIGN KEY(driver) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
34);
35
36CREATE TABLE rideRequests(
37	rideId INT UNIQUE NOT NULL AUTO_INCREMENT,
38    rider VARCHAR(20) NOT NULL,
39    departure VARCHAR(20) NOT NULL,
40    destination VARCHAR(20) NOT NULL,
41    timeWindow VARCHAR(20) NOT NULL,
42    recurring BOOL NOT NULL DEFAULT "0",
43    PRIMARY KEY(rideId),
44	FOREIGN KEY(rider) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
45);
46
47CREATE TABLE rides(
48	rideId INT NOT NULL,
49	driver VARCHAR(20) NOT NULL,
50    carDesc VARCHAR(20) NOT NULL,
51    date DATETIME NOT NULL DEFAULT current_timestamp, 
52    PRIMARY KEY(rideId),
53    FOREIGN KEY(rideId) REFERENCES rideRequests (rideId) ON DELETE CASCADE ON UPDATE CASCADE,
54    FOREIGN KEY(driver) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
55);
56
57CREATE TABLE rating(
58	userRated VARCHAR(20) NOT NULL, 
59    userRater VARCHAR(20) NOT NULL,
60	rideId INT NOT NULL,
61	value INT NOT NULL,
62    comment TEXT,
63    PRIMARY KEY(rideId, userRated, userRater),
64    FOREIGN KEY(rideId) REFERENCES rideOffers (rideId) ON DELETE CASCADE ON UPDATE CASCADE,
65    FOREIGN KEY(userRated) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE,
66    FOREIGN KEY(userRater) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
67);
68
69CREATE TABLE messages(
70	toUser VARCHAR(20) NOT NULL,
71    fromUser VARCHAR(20) NOT NULL,
72    body TEXT NOT NULL,
73    FOREIGN KEY(toUser) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE,
74    FOREIGN KEY(fromUser) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE,
75    PRIMARY KEY(toUser, fromUser)
76);
77
78INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
79	VALUES ("admin", "password", "admin@rideme.com", "Administrator", "The Matrix", "1234567890", "UFO", "0", "admin");
80INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
81	VALUES ("support", "support", "support@hotmail.com", "Renard Tumbokon", "The Sub-Matrix", "9081234567", "Space ship", "0", "staff");
82INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
83	VALUES ("renard", "renard", "renard@hotmail.com", "Renard Tumbokon", "302 Cranford Ave", "9081234567", "Small ass car", "1", "user");
84INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
85	VALUES ("nick", "nick", "nick@hotmail.com", "Nick Prezioso", "10 Concord Dr", "4165430986", "Big ass truck", "4", "user");