A REST Server in Python

To illustrate REST, let us work through a Payroll sample. For simplicity this example is designed so that resources are all database entities (rows in tables), but as we shall see, this does not mean that client requests follow the database schema!

An Employee management system

The tables we will use are basically as follows (see Mark II of these tables in the next section):

EMPLOYEE: (ID INTEGER PRIMARY KEY, NAME VARCHAR(20), NOTES VARCHAR(1000))

POST: (EMPID REFERENCES EMPLOYEE, FROM DATE, GRADE VARCHAR(4), MANAGER REFERENCES EMPLOYEE)

HOLIDAY: (EMPID REFERENCES EMPLOYEE, FROM DATE, TO DATE, AGREED DATE)

And let�s give them some initial data:

EMPLOYEE

1562	John Black	Sales
1567	Mary White	�Finance
1569	Paul Green	HR

POST

1562	1/2/2012	A1	1569
1562	1/4/2012	A2
1567	1/2/2012	B1	1569
1569	1/2/2012	A2

HOLIDAY

1567	2/3/2012	7/3/2012	5/2/2012
1569	4/4/2012	18/4/2012	3/4/2012

To keep things simple, let�s write a very simple Python program to set up the database we want. We will do this in a moment: first we modify this design to take account of transactions.

Transactions and data currency

Before launching in to this tutorial, let us consider the transaction issues in this design, which is of course the back-office end of one or more web applications (for noting a new employee, a new post/resignation/new grade/new manager for an employee, and for booking and approving holidays). The only access to this data will be through our database server, and we will use single threading, so actual concurrency control on the database is not an issue. Obviously the web application will want to deal with authentication and authorisation issues, so that employees can book their holidays but approve them or change their grade, managers can approve holidays but not change them etc; and we can support that using the techniques described later in this tutorial.

However, because HTTP is stateless, it is quite likely that web clients have stale information on their screens, because other clients may have updated something. For 6 of the 7 use cases mentioned in the above paragraph we need a way of checking that the information shown is current: if not, the client should be asked whether they want to refresh it before they proceed with the changes they want to make.

If all the information on the client�s screen came from a single record in the database, the check could use row versioning. Some DBMS including SQLServer, Postgres and PyrrhoDB do this for you, and more generally triggers can be used to maintain row version information. But doing this misses two important points:

(a) This approach will not work well if the data comes from several tables.

(b) Our database is embedded: no other programs can possibly make changes, so we don�t need anything as complicated as triggers

So (although PyrrhoDB has a CHECK function that might work well for us) no matter what database we are using, we need to consider a custom mechanism for checking data currency. In addition to placing some timestamp-like fields in the database, we can add some extra version information to messages between client and server. As we will see this will mean that the data in a REST PUT will not be same as the entries in the row.

Let�s design all of that before building the API.

A Versioning table

RVV: (SEQ INTEGER)

RVV

SEQ

We will implement a simple method in our REST server to increment this version number in the database every time we retrieve it. (This is much better than keeping a static/global counter in the REST server itself.)

And we modify our base tables to contain a RVV field:

EMPLOYEE: (ID INTEGER PRIMARY KEY, NAME VARCHAR(20), NOTES VARCHAR(1000), RVV INTEGER)

POST: (EMPID REFERENCES EMPLOYEE, FROM DATE, GRADE VARCHAR(4), MANAGER REFERENCES EMPLOYEE, RVV INTEGER)

HOLIDAY: (EMPID REFERENCES EMPLOYEE, FROM DATE, TO DATE, AGREED DATE, RVV INTEGER)

The REST API

To a first approximation our REST API will be simply a set of CRUD operations. There will be GETs of course, and also the 7 use cases mentioned above.

New employee: The server will need to assign the new employee id.

New post: This is a POST to the Posts table, containing the information about the new post, but will include the rvv of the Employee information as a check.

Resignation: Although this is basically a PUT to the Posts table, the data sent to the server should include the id and rvv of the Employee as well as the rvv of the most recent Post. If all of that checks out, the resignation can be processed.

New grade/new manager: Here the data sent to the server should include the Employee�s id, new grade/manager and the rvv of the Employee and the current Post.

New holiday: This is a POST to the Holiday table, containing the requested dates and will include the Employee rvv.

Approve holiday: This is a PUT to the Holiday table, identifying the holiday but will also include the rvvs of Employee and Post information, as only the current Manager can approve the holiday, and will want to check the entitlement based on grade etc.