chat485

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/chat485.sqlite3. The var directory is commonly where the system writes data during the course of its operation.

$ mkdir var
$ sqlite3 var/chat485.sqlite3
sqlite> CREATE TABLE users(
          userid INTEGER PRIMARY KEY AUTOINCREMENT,
          fullname VARCHAR(255) NOT NULL,
          email VARCHAR(255) NOT NULL UNIQUE
        );
sqlite> .tables
users
sqlite> .exit

Now, we can add a user to the database. We omit userid so SQLite assigns it automatically. Your output formatting may be slightly different.

$ sqlite3 var/chat485.sqlite3
sqlite> INSERT INTO users(fullname, email)
        VALUES ('Andrew DeOrio', 'awdeorio@umich.edu');
sqlite> SELECT * FROM users;
1|Andrew DeOrio|awdeorio@umich.edu
sqlite> .exit

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

$ sqlite3 var/chat485.sqlite3 "SELECT * FROM users;"
1|Andrew DeOrio|awdeorio@umich.edu

.mode column
.header on

$ sqlite3 var/chat485.sqlite3 "SELECT * FROM users;"
-- Loading resources from /Users/awdeorio/.sqliterc
userid  fullname       email
------  -------------  ------------------
1       Andrew DeOrio  awdeorio@umich.edu

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(
  userid INTEGER PRIMARY KEY AUTOINCREMENT,
  fullname VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

And put this in sql/data.sql:

PRAGMA foreign_keys = ON;

INSERT INTO users(fullname, email)
VALUES ('Andrew DeOrio', 'awdeorio@umich.edu');

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

$ rm var/chat485.sqlite3
$ sqlite3 var/chat485.sqlite3 < sql/schema.sql
$ sqlite3 var/chat485.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 chat485db 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/chat485db
Usage: ./bin/chat485db (create|destroy|reset)

Create

The create argument runs the sqlite3 command line utility.

$ ./bin/chat485db create
+ mkdir -p var
+ sqlite3 var/chat485.sqlite3 < sql/schema.sql
+ sqlite3 var/chat485.sqlite3 < sql/data.sql

Avoid clobbering an existing database.

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

Destroy

The destroy argument removes the database.

$ ./bin/chat485db destroy
+ rm -rf var/chat485.sqlite3

Reset

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

$ ./bin/chat485db reset
+ rm -rf var/chat485.sqlite3
+ mkdir -p var
+ sqlite3 var/chat485.sqlite3 < sql/schema.sql
+ sqlite3 var/chat485.sqlite3 < sql/data.sql

Sample code

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

#!/bin/bash
# chat485db

# Stop on errors
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"
    ;;

  *)
    usage
    exit 1
    ;;
esac

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

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.