Serving up CRUD

Create a full fledged data-driven commenting system.

Today's DB Objectives:

Understand HTTP Requests: Verb/Methods, Headers, Payload
Grok the basic idea of a RESTful API
Learn basic CREATE, INSERT, UPDATE, DELETE statements (and add JOIN to your understanding of SELECT)
Implement an HTTP driven CRUD service
Run it with POSTMAN or cURL

Lesson 1: HTTP requests

HTTP stands for Hypertext Transfer Protocol. This is the backbone of the internet and you use it almost perpetually. Let's break down the meaning:

Hypertext: (think HTML/most websites) structured text which might contain "links" to other texts
Transfer: (think changing of hands) a way of moving hypertext from place to place
Protocol: (think handshake+greeting) patterned well enough that programs know exactly what to expect

When you typed "http://db.prof.ninja" into your browser and hit enter it issued an HTTP request. The request has headers and content, and the request got passed off from your machine to a whole range of other machines. A complex (distributed) lookup was done by computers in many places to find my server and ask my server to deliver (serve) what it deems most appropriate for someone searching for "db.prof.ninja".

To see the request you can open your javascript console and hit refresh.

To see which machines the request got forwarded to you can use a webapp which will show you: http://www.simpledns.com/lookup-dg.aspx. HTTP is intended to be stateless, so each request will get its response independently of all previous requests.

The HTTP request might look like this:

    
GET /index.html HTTP/1.1
Host: db.prof.ninja
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Firefox/38.0

This is asking to GET (as opposed to PUT or DELETE or POST) the "resource" at "/" + "index.html" using HTTP specification 1.1 at the host db.prof.ninja. GET is the method of the request, and the host plus the resource form the URL (uniform resource locator).

The HTTP Response might look like this:


HTTP/1.1 200 OK
Connection:  keep-alive
Content-Encoding:  gzip
Content-Language:  en
Content-Length:  35435
Content-Type:  text/html; charset=UTF-8
Date:  Thu, 11 Jun 2015 05:51:29 GMT
Last-Modified:  Tue, 09 Jun 2015 20:53:47 GMT
Server: Apache
Accept-Ranges: bytes
Connection: close

<html>
<head>
<title>
Random HTML document/resource/hypertext
</title>
</head>
<body>
Hi there, this is hypertext!
</body>
</html>

In general there are headers, the key: value pairs before the content, and content. There is also a STATUS CODE which is given in the first line, 200 OK is good 404 Not Found means a bad URL and 500 Server Error means the server screwed up.

The big idea is that each URL will identify some stateless resource out there, and all of the web pages (or in our case data) that someone might want will have a specific way to access it. In this way we can all refer to each other's data and have a standard protocol to speak.

Go ahead and try to look at some HTTP requests using javascript console from Chrome or Firebug from Firefox. Safari has a developer console, I think.

Lesson 2: REST APIs

An API (Application Programming Interface) is a program's contract with the world. If you ask in exactly THIS way I will give you exactly THAT. For instance we've seen that a GET request to db.prof.ninja will get an HTTP response including the contents of the file index.html on my server. This is a type of API. When you wrote your C++ program last time you made a simple API which was, run my executable and you'll get some random scrabble racks.

REST (Representational State Transfer) is a programming philosophy really. It is really a way of designing an API which is to let "resources" be given locations and let those resources get acted on by verbs. A REST API typically refers to having a URL be a resource and the HTTP Method by be the verb.

For instance, I could make a REST API for a program on my server that supports the following interface:

An HTTP GET request sent to (http://db.prof.ninja/myprog)/items will return a list of items
An HTTP POST request sent to the same address /items will create a new item and return its ID
An HTTP GET request sent to /items/:ItemID will respond with data about the specified item.

Please note that in most API specs a variable in the URL is preceded by a colon, so /items/:ItemID simultaneously refers to /items/2 for item number 2, and items/5 for item number 5.

Now many internet folk debate what constitutes a proper REST API and what doesn't but the general idea is an open HTTP driven way of interacting with your program. There are only a few rules that I stick to when designing such an API:

All methods/verbs except POST should be idempotent, that means: issuing the request many times in a row will not have a different outcome. POST is the only exception, and it's how all of the dirtiest commands are done.
I like to have the ethos of /collection/:id/collection/:id for my locations. So if I were running a vets office I might have /owners/:ownerId/pets/:petId so that the specific pet is part of the specific owners pet collection.
Your program deciphers the meaning of these addresses.
Use the URL strictly to identify the object you want to act on, send JSON data as a payload to carry variables.

HTTP requests can send key: value pairs to the server for parsing. To practice the basic idea of an HTTP API I encourage you to try my AJAX (asynchronous javascript) playground. This lets you construct various requests with various payloads, send them to the server at a specified URL and have your request mirrored back to you. The code is available on GitHub in our class organization. Can you fork it, get it running on your cloud9, and make it respond to a request of GET /bananas with the string "Daylight come"?

Node version of an API

Lesson 3: New SQL commands

CRUD stands for Create Read Update Destroy, and they make the four most essential tasks of any data-driven program. Last time we learned about Reading from a database using SELECT. Now I want to add commands for the other three using SQL.

As a complete SQL API for select here is a flowchart of SQLite's reading of SELECT:

For Create, we have two types, creating tables, and creating records in the tables. In SQL the command to create a new table is CREATE and here is a pleasant diagram showing how SQLite3 parses a CREATE command:

A typical create command in SQLite might look like: CREATE TABLE tableNameHere (id integer primary key, column1 string, column2 integer). We will look at this far more in-depth when start designing databases in the next project.

This is our first database "key", which is a column (or set of columns) designed to uniquely identify a row. As we move into future commands this will be very important.

When creating a record we use an INSERT command to add to that table. It typically looks like this: INSERT INTO tableNameHere (column1, column2) VALUES ("andy is number ", 1). Here is a complete insert diagram:

For Updating a record that is already in a table we use something like: UPDATE tableNameHere SET (column1 = "relax") WHERE column2 = 1. Complete diagram here:

Destroying a record is very similar: DELETE FROM tableNameHEre WHERE column2 = 1. Here is the flowchart:

Let's build a super simple "blog" to practice these skills.

Do the following from the command line in a cloud9 session:

make a table called posts with at least two columns, an id integer primary key, and a text string.
make a table called comments with at least two columns, a post_id integer and a text string column.
Create several posts, when you create them you don't need to specify the id, it will be automatically created.
For one post create at least two comments.
Edit a post.
Delete a post.

Lesson 4: Implement a CRUD REST API

In the page for project 1 there are some sample codebases for starter APIs, likewise earlier in today's work we tried the AJAX playground which has an API of sorts in it.

Last time we connected programs to the database, so this time let's see if we can react to the four different types of HTTP verbs.

Write six simple DB procedures as functions in either PHP or NodeJS:

one to fetch all posts
one to fetch all comments given a post_id
one to create a new post given some input text
one to change a post given its id and the replacement text
one to delete a post given its id
one to create a new comment given some input text and a post_id

Now try to call those functions in reaction to the various types of requests:

GET /posts should fetch all
GET /posts/:id should get the comments for that post
POST /posts should create a new post (you'll need help with payload)
PUST /posts/:id should update that post
DELETE /posts/:id should destroy that post
POST /posts/:id/comments should create a new comment

Lesson 5: Testing the API

Use POSTMAN to make requests of various types with various parameters to either /posts or /posts/:id.