A Minimalist Command Line Database User Interface

To begin exploring the design of a generic database user interface as mentioned in my previous post, I’ve written a minimalist command line program. To try it, do this:

$ git clone git://github.com/jmafc/database-ui-tutorial.git dbui
$ cd dbui
$ createdb moviesdev
$ yamltodb moviesdev film.yaml | psql moviesdev

Of course, there are some pre-requisites: Git, PostgreSQL, Python, Psycopg2 and Pyrseas. If you don’t want to install the latter, create the film table according to the 0.1 version here. If you simply want to look at the code, you can find dbapp.py at GitHub here.

The following is a sample usage session:

$ cmdline/dbapp.py moviesdev
   A - Add
   L - List
   U - Update
   D - Delete
   Q - Quit
Command? l
    Id Title                            Year
 19777 The Cocoanuts                    1929
 20629 All Quiet on the Western Front   1930
 20640 Animal Crackers                  1930
   A - Add
   L - List
   U - Update
   D - Delete
   Q - Quit
Command? a
Id [0]: 1234
Title []: Testing
Release year [0]: 2010
Film 'Testing - 2010' added
   A - Add
   L - List
   U - Update
   D - Delete
   Q - Quit
Command? u
Id: 1234
Updating 'Testing - 2010'
Title [Testing]: A Test Movie
Release year [2010]:
Film 'A Test Movie - 2010' updated
   A - Add
   L - List
   U - Update
   D - Delete
   Q - Quit
Command? d
Id: 1234
Delete film 'A Test Movie - 2010' (y/n) [n]: y
Film 'A Test Movie - 2010' deleted
   A - Add
   L - List
   U - Update
   D - Delete
   Q - Quit
Command? q
Done

Caveat emptor: The code is very succint and has limited error checking.

Database User Interface Basics

Examining the sample application leads us to identify the following essential features:

1. The application needs to present data both as single records and in list or tabular form.
2. The data is entered and displayed as characters but possibly stored or interfaced to the DBMS in binary formats, hence conversion procedures between external and internal formats are necessary.
3. Data items need an initialization or default value.
4. Many or most data items need validation procedures, including a failure message. Some validations are generic to the data type, others are specific to the item.
5. Depending on the operation mode, modification of some items may be prevented, e.g., primary key cannot be entered or changed in Update mode.
6. One or more facilities are needed to select a record for Update or Delete, e.g., by the primary key, from a list, etc.
7. When reporting actions taken, a short external representation of a record is desirable¹.

Some of these may seem tautological, but they apply regardless of whether the user interface is line-oriented, character-oriented (like ncurses), graphical or web-based.

A program to develop database applications needs to assist the developer in specifying these features. For example, the program has to provide a means to design a single record or tabular format. The advantage of having access to the database catalogs is that the program can facilitate this by supplying useful defaults at various stages in the design process.

Once the design is finalized, we’ll probably want to store it in the database itself, but –mindful of version control– it’s recommended we keep the design in a YAML specification file that can be stored in a VCS.

---

¹ The primary key for the film table is an integer, and in the sample session, the IMDb identifier was used as it were a “natural” key. In most applications, such an id would be auto-generated and possibly not shown to the user.