PostgreSQL Version Control Feature Coverage

In a comment to one of my early posts about version control, Peter Eisentraut stated he was “somewhat discouraged because [seeing how fast PostgreSQL develops and adds new features,] I don’t see how a tool like apgdiff can keep up and stay useful unless a lot more resources are put into it.” At the time, the first release of Pyrseas (0.1.0) was well over a month away. Currently, Pyrseas 0.3.0 is nearing release . This seems like a good time to summarize where Pyrseas stands vis-à-vis the list of PostgreSQL data definition features:

DDL Feature 0.1 0.2 0.3

Note that PosgreSQL 9.1 will add EXTENSION to the list above. Note also that although the links in the matrix go to the corresponding CREATE object documentation page, Pyrseas supports CREATE, CREATE OR REPLACE, DROP and ALTER, as applicable. A final note: an “X” in a particular release doesn’t imply that the feature is supported in all its syntactic glory.

Caveats aside, progress has been made. Pyrseas 0.1.0 supported most of the basic relational database objects and concepts present in the SQL Standard, and usually covered by generic version control tools. Pyrseas 0.3.0 will support the features showcased in the Pagila sample database, which I hope is somewhat representative of the average PostgreSQL database (in terms of DDL features, of course).

The goal for the 0.4.0 release will be to cover the remaining features excluding Text Search, SQL/MED and ROLE/GROUP, which will be tackled in 0.5.0 or later. Is there a feature you or your organization is interested in? Let me know in the comments.


To MVC or not to MVC

Many (or most) application frameworks have adopted a Model-View-Controller (MVC) architecture, for example, Django (they call it MTV but it’s very similar), Pylons, Ruby on Rails, and Struts.

In The Helsinki Declaration (IT Version), Toon Koppelaars makes the case for a different method to architect and implement database applications. Instead of using MVC for what he calls “Window-on-Data” (WoD) applications, Toon suggests using the Helsinki approach, which using acronyms could perhaps be named the UI-BL-DL architecture. It consists of three layers:

  • User Interface (UI): this is equivalent to the View and Controller of MVC. It can be implemented with the front-end technology “du jour.”
  • Business Logic (BL): this is the Model code that implements queries (the read-BL sublayer) or transactions (the write-BL sublayer) in a manner required by the enterprise using the application.
  • Data Logic (DL): this includes the database design as well as integrity constraints.

In the last point, integrity constraints are meant in the broadest sense, as used in Chris Date’s An Introduction to Database Systems (8th edition), Chapter 9, i.e., much more than primary and foreign keys. Toon has in mind something like the SQL Standard CREATE ASSERTION, but he’ll settle for TRIGGERs as an implementation vehicle.

The Helsinki approach appears eminently suitable for the design of a generic database user interface (in this context, the latter is more than the UI layer listed above). Therefore, let me apply these concepts to the minimalist command line interface (a technology “du jour” at some distant time in the past) I presented before.

As a first step, I factored all the calls to Psycopg2 objects out of dbapp.py and into their own module named dblib.py. Then I went further and split out of the latter the application-specific code into a “business logic” module named bl.py, shown below:

# -*- coding: utf-8 -*-

from dblib import fetchall, fetchone, execute

def get_all(dbconn):
    return fetchall(
        dbconn, "SELECT id, title, release_year FROM film ORDER BY id")

def get_one(dbconn, id):
    return fetchone(
        dbconn, "SELECT id, title, release_year FROM film WHERE id = %s",

def insert(dbconn, film):
    return execute(
        dbconn, "INSERT INTO film VALUES "
        "(%(id)s, %(title)s, %(release_year)s)", film.__dict__)

def update(dbconn, film):
    return execute(
        dbconn, "UPDATE film SET title = %s, release_year = %s "
        "WHERE id = %s", (film.title, film.release_year, film.id))

def delete(dbconn, id):
    return execute(dbconn, "DELETE FROM film WHERE id = %s", (id,))

As can be seen, segregating this code into its own module means it could easily be re-used by a web (or some other kind of) interface. If you look at the refactored dbapp.py (v0.1.1), you’ll notice that now you’d be hard-pressed to tell which database technology is behind the application. Finally, the dblib.py module is the start of a generic low-level PostgreSQL library.

There are still some rough edges and “magic.” For example, the edit function in dbapp.py validates the release year redundantly with the CHECK constraint in the database. As I understand it, according to the Helsinki method, the UI ought to be calling the DL layer to perform these validations. Furthermore, there is magic in the BL API: the structures (film, row) passed as arguments or returned lack proper definition. Toon recommends using Bertrand Meyer’s Design by Contract (see Object Oriented Object Construction) to specify the UI to BL interface. We’ll revisit these and other topics in forthcoming posts.

As before, the code is available at GitHub.


Designing a (somewhat) RESTful Database User Interface

To continue exploring the design of a generic database user interface, let us turn our attention to a web interface. I’ll use the pattern of the Django admin application as the basis for specifying the behavior of a somewhat RESTful web interface for maintaining the movies database I’ve used in previous posts.

Home Page

The home page (canonically at http://www.example.com/) is comparable to the menu displayed initially by the command line interface (CLI). It will consist of an HTML table listing the logical entities together with options to add a new record (New) or edit an existing one (Edit). For now, the table will include a single entity: a film.

The latter option (or the entity link itself) will take the user to the List Records Page (see below). The New option will go to the

Add Record Page

This will be presented at http://www.example.com/film/new and is comparable to the Add interactions in the CLI. It will have an HTML form with fields (HTML text input or textarea elements) for each attribute and a “Save” button (HTML submit input element). Pressing the button will POST the new data to http://www.example.com/film/create.

List Records Page

This is of course comparable to the CLI List display. It will feature an HTML table listing all films. This is the index page for the entity so its URL will be http://www.example.com/film/.

We’ll worry about pagination later, but when we do the URL will have a query string to select a given page, e.g., http://www.example.com/film/?p=123.

Each row of the HTML table will have a link that leads to the

Record Edit Page

This will have a URL that identifies the film being edited, e.g., http://www.example.com/film/23969, where 23969 corresponds to a value in the id column of the film table in our database. This is equivalent to the CLI Update option.

The Edit page will have the same HTML form as the Add Record page, including the “Save” button, but the POST will go to http://www.example.com/film/save/23969. It will also have a “Delete” link that goes to the

Record Delete Confirmation Page

This will be presented at http://www.example.com/film/delete/23969 and is comparable to the confirmation requested by the CLI Delete. It will display an “Are you sure?” message together with details of the record to be deleted and a button titled “Yes, I’m sure.” The POST will use the same URL as the GET.

Form and Error Handling

Both the Add and Edit pages will validate the data entered. If there are any errors, the respective page will be redisplayed with a message(s) indicating what needs to be corrected. If a “Save” operation succeeds, we’ll redisplay the film listing.

Entity Handler

With this specification, if we translate each URL path above into a class method, we can outline a Python class that will act as the handler (or MVC controller, although Django calls this the view layer) for the HTTP requests. Note that the entity index URL (the listing) maps to the index method while the specific entity URL maps to the default method.

class FilmHandler(object):
    def new(self):
        "Displays a form to create a new film"

    def create(self, **formdata):
        "Saves the film submitted from ``new``"

    def index(self):
        "List all films"

    def default(self, id=None):
        "Displays a form for editing a film by id"

    def save(self, id=None, **formdata):
        "Saves the film submitted from ``default``"

    def delete(self, id=None):
        "Deletes an existing film by id"

There are many more details such as the “Model,” i.e., the interface to the database itself, and the MVC View (which Django calls the template layer). So visit again if you’re interested!

Update: Changed title and first paragraph to emphasize that the interface does not adhere strictly to the REST canon. See Oliver Charles’ detailed critique below.