User Interfaces for Databases

Pyrseas was started to improve on Andromeda. In addition to the schema version control I’ve been discussing in these posts, Andromeda provides two other capabilities: automations and web application programming.

Andromeda’s web programming allows you to generate a database application with very little actual programming, since Andromeda takes into account the primary key, foreign key and other information present in the YAML specification to generate much of the application code for you. This is another area that I’d like to explore for implementation in Pyrseas.

Administrative Applications

Nearly any database needs a user interface to add, retrieve, update and delete records, known by the unattractive acronym CRUD. There are a number of admin apps that provide such generic facilties. For example, for PostgreSQL we have pgAdmin which offers a GUI, and phpPgAdmin and Adminer which offer PHP web interfaces. These have a couple of limitations.

Admin apps almost always operate on a single table at a time. Interfaces for end users often require interacting with or viewing two or more tables on a single screen or page, e.g., customers with their invoices, accounts with their transactions.

By definition, admin apps are “raw” and cater to the needs of DBAs and programmers. After retrieving a customer record searching by name, the admin user can update any column, including the primary key and other columns that end users would normally be prevented from updating.

Django Admin

Django includes an appealing admin site. With a moderate dose of programming, you can develop an application suitable for end users. However, you’re at the mercy of Django’s constraints on “modeling” a database. Take for example the film_category table I used in my second post on version control:

CREATE TABLE film_category (
    film_id INTEGER NOT NULL REFERENCES film (id),
    category_id INTEGER NOT NULL
        REFERENCES category (category_id),
    last_update TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (film_id, category_id)
);

Django cannot use this table “as is” because it insists in having a single column as the primary key. So you end up adding an unnecessary “id” column together with a SEQUENCE (and needless to say, you incur the wrath of the SSSKA).

We could turn to SQLAlchemy, a Python-based ORM that understands that a primary key –albeit singular– can encompass more than one column, and some other web framework or even Django itself, and attempt to recreate the nice Django admin app. However, aside from the additional effort that would represent, we wouldn’t be taking advantage of the YAML database specification we can now store in our VCS.

This is a very broad, and some may say, ambitious topic, but as I did with version control, in future posts I’ll survey existing “art” and progress towards designing and implementing a generic end user interface for PostgreSQL. Reader feedback will be much appreciated.

About these ads

10 thoughts on “User Interfaces for Databases”

  1. In years past, I looked into application generation from database schema. Andromeda was one, but its use of external schema/metadata didn’t impress me favorably. From memory, I found middlegen (OS) and firestorm (not) in the java world, and a bunch of others I had on a sticky note long gone. middlegen looks to be moribund, and firestorm kind of. OTOH, I’ve long advocated that BCNF schemas on SSD (or any amount of HDD which behaves similarly) are perfect candidates for code generation. It’s just that to be effective, the generator needs to be aware of all the constraints in the catalog/schema so that it can emit complete edits on the client side code. Aye, matey, thar’s the rub.

    To keep the generator simple minded (and more nearly RDBMS agnostic) then one must tend toward high normalization, thus limiting information needs to PKs and FKs. And that’s where SSD as primary datastore becomes necessary.

    To the extent that information_schema is fully supported across vendors (not right now), then one could put simple check constraints in the catalog, and be able to write client side code agnostically. And so on.

    In any case, I applaud your effort.

    1. Thanks for the comments. I should point out that I don’t intend to make it RDBMS agnostic. These will be tools for PostgreSQL.

  2. You really really don’t want a primary key that spends multiple columns. That makes the live of everyone who comes after you and wants to refer to this table more complicated. Second, if you have get duplicates in this table, it’s going to be impossible to delete them. Thirdly, life is so much easier if every table follows the same pattern.

    1. Are you serious? In case you are, let me refer you to Josh Berkus’ Primary Keyvil, Part I, II and III or for the short version, take a look at his lightning talk (under SSSKA in my post).

      How can you get duplicates in a table that has a PRIMARY KEY constraint? If you do, your DBMS is seriously faulty.

      Consider the typical (purchase) order and order_item table. The former has an order_number as primary key. The latter most likely has a line_number to identify the line for each product ordered and of course, has order_number as a foreign key. The natural primary key for order_item is thus order_number and line_number. Would you add another column, say, order_item_id, to order_item as the primary key, just so that “every table follows the same pattern?” And how does that prevent duplicates (unless you also add a UNIQUE constraint over the natural keys)?

  3. – Second, if you have get duplicates in this table, it’s going to be impossible to delete them.

    Whoa, Nelly. Using “natural” multi-column PKs is exactly, and only, how one avoids “duplicate” rows. As jma says, referencing SSSKA, the use of surrogate keys is how logical duplicates arise and are very difficult to remove. It is true that certain ORMs don’t like multi-column keys, and users of such ORMs fall under the spell of surrogates. Read up Date, or even Celko, on the RM.

  4. If you want the means to quickly create a CRUD application that gives you more than the simple one screen per table, then take a look at RADICORE (http://www.radicore.org). This allows you create a basic family of screens (list, search, create, read, update and delete) without writing a single line of HTML or SQL. You only need to play with the code (it’s written in PHP) to customise the screens and to add in your business rules.

    It works by importing your database schema, which can be any of MySQL, PostgreSQL, Oracle and SQL Server, into its Data Dictionary. You have the option of modifying this data before you export it to produce the class files which will be used by your application. There are no artificial rules such as “the primary key must be an integer called ID” – it will use whatever it is given.

    You also use the data dictionary to generate tasks by matching one or more database tables with a Transaction Pattern. These patterns range from the simple to the complex, and have enabled me to write an ERP/CRM application which uses 200 tables, 300 relationships and 1600 tasks.

    It also comes with a Role Based Access Control (RBAC) system, Audit logging, and an Activity based Workflow system. It is well documented, and comes with a selection of prototype apps so that yoiu can see what can be done, then examine te code to see how it is done.

    1. Thanks, Tony. I’ll take a look. However, Pyrseas is about developing tools and interfaces to PostgreSQL (the pachyderm) using Python (the serpent). It wouldn’t fit well with PHP (which appparently lacks a mascot) :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s