EECS 485 Lab

EECS 485 Lab 3: Project 2 Setup

Due: 8:00pm EST September 20, 2020.

Goals

Project 2 Setup

Follow the Project 2 Setup Tutorial. You’ll need all of the setup tutorial completed without errors before you can move on to the database and Flask app setup.

Database Setup

Follow the Project 2 Database Setup. Stop after completing the database section.

If you’re not familiar with SQL, there’s a plentitude of resources available on the internet.

Schema Example

A schema describes the structure of different types of data in a database. Let’s walk through the schema for the one of the tables in Project 2.

The users table should have each of these columns:

Edit the file sql/schema.sql in your Project 2 and try to create a table with the schema described above.

Answer
CREATE TABLE users(
	username VARCHAR(20) NOT NULL,
	fullname VARCHAR(40) NOT NULL,
	email VARCHAR(40) NOT NULL,
	filename CHARACTER(64) NOT NULL,
	password CHARACTER(256) NOT NULL,
	created DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(username)
);

Next, add the table to your database. The schema input file is sql/schema.sql. The database output file is var/insta485.sqlite3.

$ pwd
/Users/awdeorio/src/eecs485/p2-insta485-serverside
$ sqlite3 var/insta485.sqlite3 < sql/schema.sql

Data Example

Now, let’s insert some data into the database. We will add all four users to the database. You can find the values for each record (row of data) at the end of the data dump file here. Open up data.sql in the sql folder and give it a shot!

Answer
INSERT INTO users(username, fullname, email, filename, password)
VALUES ('awdeorio', 'Andrew DeOrio', 'awdeorio@umich.edu','e1a7c5c32973862ee15173b0259e3efdb6a391af.jpg','sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

INSERT INTO users(username, fullname, email, filename, password)
VALUES ('jflinn', 'Jason Flinn', 'jflinn@umich.edu','505083b8b56c97429a728b68f31b0b2a089e5113.jpg','sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

INSERT INTO users(username, fullname, email, filename, password)
VALUES ('michjc', 'Michael Cafarella', 'michjc@umich.edu','5ecde7677b83304132cb2871516ea50032ff7a4f.jpg', 'sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

INSERT INTO users(username, fullname, email, filename, password)
VALUES ('jag', 'H.V. Jagadish', 'jag@umich.edu','73ab33bd357c3fd42292487b825880958c595655.jpg', 'sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

Add the data described in data.sql to your database.

$ pwd
/Users/awdeorio/src/eecs485/p2-insta485-serverside
$ sqlite3 var/insta485.sqlite3 < sql/data.sql

Query Example

Now that we have data in the database, we can issue SQL queries to view it.

Dump the entire contents of the users table with the query SELECT * FROM users. Type .exit or Control-d to quit.

$ sqlite3 var/insta485.sqlite3 
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> SELECT * FROM users;
awdeorio|Andrew DeOrio|awdeorio@umich.edu|e1a7c5c32973862ee15173b0259e3efdb6a391af.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
jflinn|Jason Flinn|jflinn@umich.edu|505083b8b56c97429a728b68f31b0b2a089e5113.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
michjc|Michael Cafarella|michjc@umich.edu|5ecde7677b83304132cb2871516ea50032ff7a4f.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
jag|H.V. Jagadish|jag@umich.edu|73ab33bd357c3fd42292487b825880958c595655.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
sqlite> .exit

Try these queries:

Flask Setup

Follow the Project 2 Flask Setup Tutorial. Stop after completing the Testing section.

Completion Criteria

Lab Quiz

Complete the lab quiz by the due date.