Dabbling in Live Databases: Pt 3

GUFE (goofy): The Generic but Usable Front End

Yadda, yadda, yadda … for the past two installments we’ve been chattering away about databases, getting all high falutin’ and theoretical. That’s all fine and good, but after a while of heavy pondering you need something concrete between your fingers, to knead the dough and stomp in the mud. After all, would Einstein’s theory of relativity have become so popular if he had nothing tangible to show for it, namely his hair? This month we go concrete, using Perl to build a visual front end to the live databases we have been dabbling with these past two months. It’s simple, it’s attractive, and it’s GUFE – “goofy” – the Generic but Usable Front End. Why “generic”? Because you can basically apply this front end to any SQL compliant database with very little modification. Dabblers should be familiar with using DBI to query live databases from Perl, as we’ve seen in the past two installments, as well as with using CGI with Perl and web templates.

Let’s Talk GUFE

The beauty of the DBI module in Perl, as we’ve seen with both

MS Access databases
under Windows and

MySQL databases
under Linux, is that you can use the
same core code to query a database regardless of its vendor
format, so long as you have installed an appropriate DBD
module for that vendor. In the last two articles we’ve worked
with one simple but prototypical sample database, named Clients, which has contained two tables: billing and invoices.

We’ve worked closely with the guts of DBI and performing
queries of this database, but these queries have remained
theoretical … GUFE, on the other hand, is a Perl script
which bridges the gap between the database and the web
browser, essentially querying the database and formatting
the results in a web browser using CGI.

GUFE screen shot
Click for full size image

The GUFE interface is relatively simple, as you can see
in the screenshot above. The yellow (upper) portion
of the page presents a view of the selected table based upon
the query criteria. The default view simply presents
the entire database table including all rows and fields, the
equivalent of the SQL statement,

select * from billing

for example. Each field label in the top row of the table
is a hyperlink -- clicking a field label generates
a toggle sort of the entire table on that field; thus,
clicking the "ClientName" label will sort the
table in ascending order alphabetically on the ClientName
field. A second click of the same label will re-sort
the table in a descending fashion. At the bottom of the
yellow region there are links to any other tables which
are part of this database, allowing you to switch the
view to a different table, thus allowing you to query any
table in the database. Our example Clients database
has only two tables of course.

The blue (lower) portion of the page presents several
criteria fields, with which you can query the currently
selected table. Because GUFE is a teaching tool for this
article, rather than a production-quality script, the
criteria implementation is far from airtight. For instance,
you the user must enclose string values in single quotes
in the criteria fields, and you will generate errors if you
mismatch conditions and field types--in other words,
attempting to character match against an integer field in
the database table. Multiple criteria fields can be
"AND" or "OR" combined, but you cannot
use this interface to AND some fields and OR others
simultaneously. Accounting for these gaps in interface design would be
necessary in a real-world production, but the extra code
required would dilute the main goal of GUFE and leave the
script more difficult to follow. (Also, it's hard to do!)

Why is GUFE generic? With the exception of the hyperlinked
"Invoice" field, GUFE can be applied to
any SQL compliant database. All of GUFE's output, including
the list of available tables, the table display, and
the criteria fields, are generated on-the-fly based on
queries of the specified database. Thus, GUFE is not tightly
married to our example Clients database, and should
be easy for you to adapt to your own live database project.

You are, of course, welcome to play with GUFE using our
fictional Clients database. In fact, we recommend
doing so before and after reading the remainder of this
article, as we walk-through the little scamp's inner workings.

Latest Articles