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:
- The application needs to present data both as single records and in list or tabular form.
- 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.
- Data items need an initialization or default value.
- 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.
- Depending on the operation mode, modification of some items may be prevented, e.g., primary key cannot be entered or changed in Update mode.
- One or more facilities are needed to select a record for Update or Delete, e.g., by the primary key, from a list, etc.
- When reporting actions taken, a short external representation of a record is desirable1.
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.
1 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.
Great example of Python commandline application. But I’m sure you can do without all those dependencies – just use Python on it’s own with a database.
Thanks. The database I’ve chosen is PostgreSQL and in order to interface to it, if I’m going to use Python, I need an adapter, hence psycopg2.
That said, it’s probably fairly easy to modify dbapp.py to use SQLite.
Git is not a dependency but is useful if you want to get the source to experiment with (although you could copy-and-paste from browser to an editor). Pyrseas is not really a dependency, but this blog is about that, my pet project.