p2-insta485-serverside

SQLite Tutorial

SQLite is a library that implements a small SQL database engine. An SQLite database is stored in a file on your hard drive.

If you’re new to SQL, take a look at the w3Schools SQL Intro.

Install

First, we’ll install the sqlite3 command line utility.

Linux and Windows Subsystem for Linux

$ sudo apt-get install sqlite3

MacOS

$ brew install sqlite3

All operating systems

Verify that you have the sqlite3 command line tool installed. Your version might be different.

$ sqlite3 --version
3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6

Manual database manipulation

Create a database with one table in it. The database lives in the file var/insta485.sqlite3. The var directory is commonly where the system writes data during the course of its operation.

$ mkdir var
$ sqlite3 var/insta485.sqlite3
sqlite> CREATE TABLE users(
          username VARCHAR(20) NOT NULL,
          fullname VARCHAR(40) NOT NULL,
          PRIMARY KEY(username)
        );
sqlite> .tables
users
sqlite> .exit

Now, we can add a user to the database. Your output formatting may be slightly different..

$ sqlite3 var/insta485.sqlite3
sqlite> INSERT INTO users(username, fullname)
        VALUES ('awdeorio', 'Andrew DeOrio');
sqlite> SELECT * FROM users;
awdeorio|Andrew DeOrio
sqlite> .exit

You can also run an SQL statement in one line from the command line.

$ sqlite3 var/insta485.sqlite3 "SELECT * FROM users;"
awdeorio|Andrew DeOrio

.mode column
.header on

$ sqlite3 var/insta485.sqlite3 "SELECT * FROM users;"
-- Loading resources from /Users/awdeorio/.sqliterc
username    fullname     
----------  -------------
awdeorio    Andrew DeOrio

Automated database manipulation

It’s easier to put SQL commands in a file and then read the file with sqlite3. Create the files first:

$ touch sql/schema.sql
$ touch sql/data.sql

Put this in sql/schema.sql:

PRAGMA foreign_keys = ON;

CREATE TABLE users(
  username VARCHAR(20) NOT NULL,
  fullname VARCHAR(40) NOT NULL,
  PRIMARY KEY(username)
);

And put this in sql/data.sql:

PRAGMA foreign_keys = ON;

INSERT INTO users(username, fullname)
VALUES ('awdeorio', 'Andrew DeOrio');

Now, we’ll start over and run both of these SQL scripts.

$ rm var/insta485.sqlite3
$ sqlite3 var/insta485.sqlite3 < sql/schema.sql
$ sqlite3 var/insta485.sqlite3 < sql/data.sql

Note: Foreign key support in SQLite 3 is disabled by default in SQlite 3 (docs). The line of SQL PRAGMA foreign_keys = ON turns on foreign key support on a per-connection basis.

Database management shell script

Automate database management tasks with a shell script. Remember to use best shell scripting practices from the Shell Scripting Tutorial.

In this example, the script is called insta485db and it lives in the bin/ directory. We show the output of the instructor solution, but we’ll grade your script only on its functionality, not the output.

Usage

Calling the script without arguments displays a usage message.

$ ./bin/insta485db
Usage: ./bin/insta485db (create|destroy|reset|dump)

Create

The create argument runs the sqlite3 command line utility. You might also copy files used to initialize an uploads/ directory.

$ ./bin/insta485db create
+ mkdir -p var/uploads
+ sqlite3 var/insta485.sqlite3 < sql/schema.sql
+ sqlite3 var/insta485.sqlite3 < sql/data.sql
+ cp sql/uploads/* var/uploads/

Notice that sql/uploads contains the original starter files, while var/uploads/ is a copy. The web server will upload new photos to var/uploads later. This isn’t specific to SQLite, but it’s helpful for EECS 485 project 2 and project 3.

Avoid clobbering an existing database.

$ ./bin/insta485db create
Error: database already exists

Destroy

The destroy argument removes the database. You might also want to remove user-created files like an uploads/ directory.

$ ./bin/insta485db destroy
+ rm -rf var/insta485.sqlite3 var/uploads

Reset

The reset argument does the same thing as destroy followed by create.

$ ./bin/insta485db reset
+ rm -rf var/insta485.sqlite3 var/uploads
+ mkdir -p var/uploads
+ sqlite3 var/insta485.sqlite3 < sql/schema.sql
+ sqlite3 var/insta485.sqlite3 < sql/data.sql
+ cp sql/uploads/* var/uploads/

Dump

The dump argument prints everything in the database. The exact format is up to you. Note to EECS 485 students: the autograder will only check for the right data in the output, not the format.

$ ./bin/insta485db dump
+ sqlite3 -batch -line var/insta485.sqlite3 'SELECT * FROM comments'
commentid = 1
    owner = awdeorio
   postid = 3
     text = #chickensofinstagram
  created = 2017-06-20 15:47:02
...

+ sqlite3 -batch -line var/insta485.sqlite3 'SELECT * FROM following'
username1 = awdeorio
username2 = jflinn
  created = 2017-06-20 15:47:02
...

+ sqlite3 -batch -line var/insta485.sqlite3 'SELECT * FROM likes'
  owner = awdeorio
 postid = 1
created = 2017-06-20 15:47:02
...

+ sqlite3 -batch -line var/insta485.sqlite3 'SELECT * FROM posts'
  postid = 1
filename = 122a7d27ca1d7420a1072f695d9290fad4501a41.jpg
   owner = awdeorio
 created = 2017-06-20 15:47:02
...

+ sqlite3 -batch -line var/insta485.sqlite3 'SELECT * FROM users'
username = awdeorio
fullname = Andrew DeOrio
   email = awdeorio@umich.edu
filename = e1a7c5c32973862ee15173b0259e3efdb6a391af.jpg
password = sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8
 created = 2017-06-20 15:47:02

Sample code

Here’s a starting point for the bin/insta485db script.

#!/bin/bash
# insta485db

# Stop on errors
# See https://vaneyckt.io/posts/safer_bash_scripts_with_set_euxo_pipefail/
set -Eeuo pipefail

# Sanity check command line options
usage() {
  echo "Usage: $0 (create|destroy|reset|dump)"
}

if [ $# -ne 1 ]; then
  usage
  exit 1
fi

# Parse argument.  $1 is the first argument
case $1 in
  "create")
    echo "FIXME implement me"
    ;;

  "destroy")
    echo "FIXME implement me"
    ;;

  "reset")
    echo "FIXME implement me"
    ;;

  "dump")
    echo "FIXME implement me"
    ;;
  *)
    usage
    exit 1
    ;;
esac

Don’t forget to make the file executable with chmod +x bin/insta485db.

Acknowledgments

Original document written by Andrew DeOrio awdeorio@umich.edu.

This document is licensed under a Creative Commons Attribution-NonCommercial 4.0 License. You’re free to copy and share this document, but not to sell it. You may not share source code provided with this document.