Exercise 1: updating the menu

You can download the menu table CSV here

Using the SQL tab in PHPMyAdmin, perform the following updates to the menu table. You can reimport the table if something goes wrong.

1. toast != bread: Update all menu items with "toast" in the name to have a subcategory of "Toasts".
2. Bagel Tax: There is a new tax on selling bagels (ridiculous, I know.) Update all rows with the subcategory "Bagels" to be $0.25 more expensive, both in price and cost.
3. Profitable Pastries: This cafe cannot afford to sell items that are not making at least 2.5 times as much as they cost. Delete all items where the price is less than 2.5 times the cost.

Exercise 1 Solutions

UPDATE menu
SET subcategory = "Toasts"
WHERE name LIKE "%toast%";

SQL Solution 1

UPDATE menu
SET price = price + 0.25,
    cost = cost + 0.25
WHERE subcategory = "Bagels";

SQL Solution 2

DELETE FROM menu
WHERE price < 2.5 * cost;

SQL Solution 3

Exercise 2: What's wrong with this table?

Redundancy. If you know the netid of either the student or the TA, the name and email are always the same. Additionally, the same user information is repeated in many rows. How could we fix this? Brainstorm some ideas.

Possible Refactoring

Here we have a questions table, a users table, and a tas table. It may look more complicated, but it will be much easier to use and maintain.

What are the FOREIGN KEYs and PRIMARY KEYs here?

The ids are all PRIMARY KEYs. student, ta, and id in the tas table are all FOREIGN KEYs. Note that a column can be both kinds of key.

Here is the code to set up these tables:

CREATE TABLE users(
  id VARCHAR(7) PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL
);
CREATE TABLE tas(
  id VARCHAR(7) PRIMARY KEY,
  FOREIGN KEY(id) REFERENCES users(id)
);
CREATE TABLE questions(
  id INT PRIMARY KEY AUTO_INCREMENT,
  question VARCHAR(255) NOT NULL,
  length TINYINT NOT NULL,
  student VARCHAR(7) NOT NULL,
  ta VARCHAR(7) NOT NULL,
  FOREIGN KEY(student) REFERENCES users(id),
  FOREIGN KEY(ta) REFERENCES tas(id)
);

SQL

Exercise 3: Simple JOINs

Download and import setup-wplmini.sql to get the table from Exercise 2, complete with the three questions. Note that this is a slightly different version than the wpl database used in lecture, with fewer columns.

Using this table, write the following queries:

1. Get the names of all TAs
2. Get the questions asked by Dubs. Assume that you do not know Dubs' id, only their name.

Exercise 3 Solutions

SELECT users.name
FROM users
JOIN tas ON users.id = tas.id;

SQL Solution 1, join style

SELECT users.name
FROM users, tas
WHERE users.id = tas.id;

SQL Solution 1, where style

SELECT q.question
FROM questions q
JOIN users ON users.id = q.student
WHERE users.name = "Dubs";

SQL Solution 2, join style

SELECT q.question
FROM questions q, users
WHERE users.id = q.student
AND users.name = "Dubs";

SQL Solution 2, where style

Cookies on the Serverside

First, the usual steps:

npm install cookie-parser in the terminal.

const cookieParser = require("cookie-parser");

app.use(cookieParser());

After doing this, you can set cookies using the response object, and read a cookie with the request object. See the slides below for details.

You can set cookies with res.cookie(...). Provide it a key, a value, and optionally an object of metadata, like expiration time:

res.cookie("last_search", "puppies", {expires: new Date(Date.now() + 60 * 1000)});

Node.js Example

To set an expiration time, you need to pass in a Date object set to the time it will expire. Use Date.now() to get the current time, and then add the number of milliseconds until you want it to expire (1 minute in the example.)

You can clear cookies with res.clearCookie(cookieName)

res.clearCookie("last_search");

Node.js Example

You can read cookies the same way you read query parameters:

let lastSearch = req.cookies["last_search"]; // "puppies"

Node.js Example

Exercise 4: Piazza Party

Let's wrap up serverside programming with a fake SQL-connected piazza webservice, using cookies to keep users logged in for a period of time. We will implement two endpoints:

• /login: A POST endpoint that takes a username and password. If valid, it will set their login cookie to a random session id.
• /logout: A POST endpoint that logs the user out by deleting the login cookie.
• /stats: A protected GET webservice that will provide statistics about the given user's Piazza activity, assuming they have a valid login cookie.

We will use a database to keep track of users, their session ids, and their personal information. Be aware that in practice this is not at all a secure solution (for example, we store passwords in plain text), but good practice for storing secure user information is beyond the course's scope.

Getting Started

Download the starter code here.

Included in the starter files is the setup.sql file you need to set up the database and populate it with sample data, and the node API with most of the setup done for you. All you need to implement are the three endpoints. The sql file will populate into a database named piazzadb.

Throughout the exercise, remember to use placeholders in SQL queries to prevent SQL injection vulnerabilities!

let userId = req.body.id;
let query = "SELECT question FROM courses WHERE answerer = ?";
db.query(query, [userId]);

Node.JS Example

Look in PHPMyAdmin to see the full structure of the following tables. FOREIGN KEYs point to their PRIMARY KEYs.

/login

Implement the POST endpoint /login. It takes two body parameters: username, and password.

Your endpoint should check if there is a user matching the given credentials. If not, provide a 400 status error: "Invalid credentials." If it does match, then:

1. Use the provided getSessionId(db) function to get a random session id. It is asynchronous, and requires you pass the db object.
2. Update the users table to have that session id for the given user.
3. Set the user's cookie "sessionid" to the id, and have it expire in 3 hours.
4. Respond in plain text with "Successfully logged in!"

You should also provide a 400 status error if they fail to pass the required parameters, and a 500 error if something goes wrong on the server.

/logout

Implement a POST endpoint /logout. If the user has no login cookie, respond with the 200 status message "Already logged out". If they do, delete the cookie by setting it with an expiration time of right now, and then respond with "Successfully logged out!"

Do not worry about clearing the sessionid from the users table.

You can test out the first two endpoints by viewing the cookies in Postman (it's right under the send button) and by checking your db in PHPMyAdmin.

/stats: Verification

This endpoint involves many queries, so we will proceed in small steps.

First, implement a GET /stats endpoint which checks to see if there is a user with the client's "sessionid" cookie value. If the "sessionid" cookie is not set, respond with a 400 "You are not logged in" message. If there is no record with that sessionid, respond with a 400 status error message: "Invalid login token, please log in again!". If there is one, respond with a JSON object with one field "Username" whose value is their username.

You can complete this with one SELECT query.

/stats: Questions Asked

Add a new field to the response JSON: "Questions Asked"

The value should be the number of questions asked by the user, not including responses.

Complete this by adding one multi-table query.

/stats: Courses

Add a new field to the response JSON: "Courses"

The value should be an array containing all course names the student is enrolled in, according to the enrollment table.

Complete this with one multi-table query.

/stats: Responses Recieved

Add a new field to the response JSON: "Responses Recieved"

The value should be the number of responses that have been recieved on the user's questions, NOT the number of responses the user has made.

Complete this with one multi-table query.

Solution