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 months 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.

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.

View GUFE in action!