Lesson 7: Databases

Filesystems Review Questions

• What might happen to a busy ext2 volume on power loss?
• ext3?
• ext4?

But what about our poor data?

• Possibly gone, like the wind
• Or worse: Half completed writes!
• General purpose operating systems, by design, don't understand structured data

Enter the Database

A database system's fundamental goal is to provide consistent views of structured data using the tools the operating system makes available.

Chief among them is fsync(2)

Structure

SQL databases are structured around Relational Algebra

• Tables
  • Columns are fields
  • Rows define a relation between fields
• A Primary key is a set of columns that uniquely identify rows in a table
• A Foreign key is a column that matches the primary key of another table

Relational Algebra Visualized

Installing MySQL

$ yum install mysql-server
$ /sbin/service mysqld start
$ /usr/bin/mysql_secure_installation

Managing MySQL

$ /sbin/service mysqld status
$ mysqladmin -p ping
$ mysqladmin -p create nobel

Configuration

• /etc/my.conf

• The most important MySQL tuning rule:

  • almost always prefer InnoDB

Users & Permissions

$ sudo mysql -p

mysql> CREATE USER 'vagrant'@'localhost'
       IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON nobel.*
       TO 'vagrant'@'localhost'
       WITH GRANT OPTION;

Importing Data

$ wget http://osl.io/nobel -O nobel.sql
$ mysql -p nobel < nobel.sql
$ mysql -p nobel

SHOW TABLES;
DESCRIBE nobel;

Basic Queries

4 basic operations on data:

• SELECT
• INSERT
• UPDATE
• DELETE

SELECT

SELECT
   yr, subject, winner
FROM
   nobel
WHERE
   yr = 1960;

Practice

• Who won the prize for Medicine in 1952?
• How many people were awarded the 1903 Nobel in Physics?
• How many prizes were awarded to Linus Pauling?
• How many people have won more than once? (Difficult)

INSERT

INSERT VALUES
   ('2013','Literature','Herta Müller')
INTO
   nobel;

Practice

In 2009:

• Barack Obama won the Peace Prize
• Elinor Ostrom and Oliver E. Williamson won the prize in Economics
• http://en.wikipedia.org/wiki/List_of_Nobel_laureates

UPDATE

UPDATE
   nobel
SET
   winner='Andrew Ryan'
WHERE
   subject='Peace' AND yr='1951';

Practice

• Brigid Tenenbaum Medicine prize in 1952

DELETE

DELETE FROM
   nobel
WHERE
   yr = 1989, subject = peace;

Further Reading, Resources, etc.

• Codd, E.F. (1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM 13 (6): 377–387.
• sqlzoo.net
• CS 440: Database Management Systems

Hands-On: Make a Database

• Create a new database

mysql> create database systemview

mysql> GRANT ALL PRIVILEGES ON systemview.*
       TO 'vagrant'@'localhost'
       WITH GRANT OPTION;

• Grant a user privileges on your new database

Databases in Applications

Applications love databases.

• Application data - the information to be displayed and manipulated
• User data - complex authentication and authorization
• Logging, statistics, state and session data, etc...

Native SQL

Most languages allow you to speak directly to a database

Python:

#!/usr/bin/python
import MySQLdb

db = ("localhost","testuser","test123","nobel" )

cursor = db.cursor()

cursor.execute("SELECT subject, yr, winner FROM nobel WHERE yr = 1960)

data = cursor.fetchall()

for winner in data:
    print "%s winner in %s: %s " % (winner[0], winner[1], winner[2])

db.close()

Introducing the ORM

Object Relational Mapper

• Maps an Object in an application to a database table or relationship
• Talks SQL to the database, your favorite language to you
• Lets you point to different databases with the same syntax
• Intelligently manages transactions to the database

Life With a Python ORM

Look, ma! No SQL!

for subject, yr, winner in session.query(Nobel).filter_by(yr=1960):
    print "%s winner in %s: %s " % (subject, yr, winner)

Much easier to read and understand, but requires some setting up first.

Setting Up the Magic - SqlAlchemy

SqlAlchemy - a popular Python ORM, frequently used in Flask apps (like SystemView!).

To use it, we'll need to:

• Import sqlalchemy
• Create a "model" - a representation of our data in code
• Create an "engine" and connect it to the database
• Create a session to store the model instances and transactions

Let's Databasify Systemview

Project:

• Store search terms, then provide them as links on the search page, so you can just click the most common terms you search for.

What else? Ideas?

Hands On

• Install the following packages:

sudo yum install python-devel
sudo yum install mysql-devel

• Check out systemview from GitHub (if you don't have it already)

git clone git@github.com:DevOpsBootcamp/systemview

Hands On (Cont...)

• Switch to 'save-search' branch

git checkout -tb save-search origin/save-search

• Activate your virtualenv

source <path to virtualenv>/bin/activate

• Install the requirements

pip install -r requirements.txt

Goals

• Connect the app to your new database
• Add a new column
• Save data to that column whenever someone searches
• Fetch the data from that column and display it on the search page
• challenge: limit the returned result to only 5 terms

http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html