Dabbling in Live Databases: Pt 3

Monday Nov 8th 1999 by Aaron Weiss
Share:

Part 3 of our miniseries continues its focus on MySQL. This time author Aaron Weiss gets GUFE ('goofy' - the Generic but Usable Front End) as he shows you how to build a visual Perl front end to a live SQL database.

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.

Preparing for GUFE

If you've followed along either of the last two articles in the Dabbling with Live Databases mini-series, you're probably prepared for GUFE already. GUFE requires that your host system is installed with Perl, the DBI module, and the DBD module for the particular database system you are using. For instance, on a Windows machine with Microsoft Access, you would need the DBD::ODBC module; on a Un*x system with MySQL you would need the DBD::MySQL module. If none of this makes sense, it is very important that you backtrack to the first two installments in this mini-series.

Caveat: if GUFE seems to stumble, especially if it fails to provide a list of available tables to view, you probably do not have the latest DBI and DBD modules installed. Be sure that you do, and install them if necessary, because older versions of these modules do not support the ability to request a list of available tables.

Let's Walk GUFE

The bulk of this article is a walkthrough of the GUFE Perl script. In doing so, we'll touch on a variety of Perl issues, from those encountered last month to some from much earlier in the Perl You Need to Know series. While we will highlight segments of the GUFE script, you may wish to consult the entire unmodified code. Some of the code lines are quite long and may need to be wrapped on this web page -- the unmodified reference code contains the original line lengths.

First, it is worth noting that GUFE relies on the web template technique that we saw in Part 4 of the Perl You Need to Know. Briefly, this means that GUFE "inserts" its output into a pre-existing HTML document. This relieves GUFE of the need to recreate the entire web page on each call, and provides the web designer with much greater flexibility in designing the surrounding elements of the page. Simply put, GUFE dumps its output in place of a <!--FRONTEND--> comment found within the source of the HTML file.

Taking an overview of GUFE we can see that it is quite simple. GUFE is built mostly of Perl subroutines, each of which performs a focused action. Once the connection to the database has been established, GUFE translates CGI queries (which are created by the browser, usually based on the settings of form fields) into SQL statements. These SQL statements are passed to the database, and GUFE retrieves the results, formatting them for the screen. Various results, such as field labels, are hyperlinked with rigged CGI queries that will, when clicked, intentionally cause GUFE to generate certain SQL queries (for instance, the field labels are rigged with sort parameters which in turn are translated by GUFE into SQL statements with sorting clauses).

The easiest way to understand GUFE is from the inside out. We'll walk through GUFE in several stages, starting, of course, from the top.

use CGI;
use DBI;

#setup database parameters
my $DBD="mysql";
my $dbase="Clients";
my $dbuser="";
my $dbpassword="";
#prepare for output to browser
$cgiobj=new CGI;
print $cgiobj->header;

GUFE begins by including our two crucial modules, CGI for interaction with the web browser, and DBI for interaction with the database.

For convenience, we assign several connection parameters for the database: the DBD protocol ("ODBC" in the case of Microsoft Access), the name of the database, and a username/password if needed to connect to the database. Some systems are case sensitive (Un*x), and some are not (Windows); it's best to pay attention to case. For example, the DBD protocol for MySQL must be all lowercase "mysql" -- you can find the proper syntax from the DBD module documentation for your database.

Standard CGI practice leads us to establish a handle for this instance of the CGI object, $cgiobj, and we output a standard HTML header to prepare the browser for incoming data.

Click here for code example 1.

We need to harvest the various parameters delivered to GUFE via CGI. Since we made these parameters up ourselves, we know what we need to collect. In the above passage, we retrieve a string of field names to return from SQL queries, the table name to query, whether to sort the table, and what type of sort -- ascending or descending.

Click here for code example 2.

We also want to collect the criteria parameters which customize the SQL query and determine what view of the table we receive. For instance, a user may use the criteria form fields to request a table in which we see only those records where the Total is greater than 100. These parameters are a bit more tricky because the parameter names are generated on-the-fly by GUFE based on the field labels contained in a particular table. To help standardize things, the criteria parameters are named criteria_condition_fieldLabel and criteria_value_fieldLabel. In the code above, we retrieve from the CGI object all parameters which conform to these names and we push those values onto a list named @allcriteria. It looks messy, but at the end we construct a single string by using a Perl join(), which might look something like:

Total > 100 AND Paid = 1

When all is said and done, we now have a variety of variables which contain the various parameters passed to GUFE. Basically, this is all about setting up the pins. Now we need to get the ball rolling.

Click here for code example 3.

The database connection is established, and the handle passed onto $dbh. Next, a main if clause determines whether the page request specified a table to view, via the table CGI parameter.

If yes, we continue processing by constructing an SQL statement using portions of CGI parameters we have harvested earlier. The SQL statement is then passed onto and executed by our &sendSQL subroutine and the statement handle is passed onto $sth. We'll need to use this handle to access the results of the query.

We end this if clause with a compound statement, one which executes our &createPage subroutine using the results of the &resultTable subroutine. We pass several parameters to &resultTable, including the database and SQL statement handles, the name of the table being queried, and the fields to be returned. This subroutine, which we'll see shortly, actually creates the entire HTML output of GUFE, which is then passed to &createPage in this case. For its part, &createPage takes the HTML produced by &resultTable and shoves it into the HTML template. Finally, the results of that are output to the screen by the containing print() function. Whoa!

In case the user did not specify a table to query (namely by visiting gufe.cgi with no additional parameters), the else clause skips construction of the SQL statement and proceeds directly to building and outputting the result table, which itself will alert the user to select a table to view.

Click here for code example 4.

Establishing the connection to the database is fairly simple, and that's just what the &openDB subroutine does. Four parameters are sent to this subroutine when it is called -- the DBD protocol to use (such as "mysql" or "ODBC"), the database to connect to (such as "Clients"), and if necessary a username and password. Some databases, such as those in MySQL, can be protected requiring login. You can see that parameters passed to a subroutine wind up in the Perl built-in variable @_, a list of each value sent in.

If for some reason the connection should fail, the subroutine "dies", meaning that it exits to the browser with an error message.

Click here for code example 5.

The &sendSQL subroutine, while extremely important to GUFE, is also quite simple. That's because interpreting and processing SQL statements is the job of the database itself. In short, &sendSQL receives the SQL statement and the database handle. Then, using standard DBI syntax, it prepares and executes the SQL statement at the database engine. The final result of this is a statement handle, which is returned from the subroutine, and which we later use to access the actual data returned by the database in response to the query. It is with this statement handle, $sth, that we'll do much of our work.

The workhorse subroute of GUFE is &resultTable, which parses through the data returned by the database and constructs an HTML table out of it all. Because this subroutine is on the longer side, let's take it on in bite sized chunks.

Click here for code example 6.

Several initial variables are setup which will be used in creating the HTML, including $rowcount, $labelRow, $tableHTML, and so on. The first real work we do, however, is to construct a URL query string. The URL query string is all the stuff that appears after the ? in a URL, such as in:

http://www.somesite.com/cgi-bin/script.cgi?blah=1&boo=2

In the above example, "blah=1&boo=2" is the query string. We need to construct a query string that replicates the state of GUFE -- that is, what table is being viewed, what are the criteria in action, and so forth. We do this because when we rig hyperlinks such as the field labels, to sort the table, we want to preserve these various states in the page that is returned. That is, we want to preserve which table is being viewed, what criteria are being used, etc. Continuing on ...

if ($queryTable){
while (my $row=$sth->fetchrow_hashref) {

This while loop will, on each pass through, retrieve the next record from the database. This assumes that the database has returned at least one matching record -- if it hasn't, this whole while loop will never execute and we'll drop down further below in the subroutine. The record is returned from the database as a hash reference. A hash is a set of associated key-value pairs, as we saw in Part 1 of the Perl You Need to Know. With DBI, the returned hash contains each field name associated with its value. The reference to this hash is then stored in $row. Notice that $row is a scalar variable, not a hash (which would begin with a %) ... that is because $row merely contains a reference, or pointer, to the hash, not the actual hash itself.

#retrieve each record row-by-row from the database
#build HTML table row-by-row using this data
 $tableHTML.="<TR>";
 @fieldNames=keys %$row; #build a list of field labels
 $fieldCount=$#fieldNames+1;
 foreach my $fieldName (@fieldNames) {
  unless ($rowcount>0) {
	my $queryURLb=$queryURLa."&sort=$fieldName";
	if ($querySortType eq "ASC") {
	 $queryURLb.="&sortType=DESC"
	}
	else { $queryURLb.="&sortType=ASC" }
   #wrap hyperlink around field names, rigged with sort parameters
   $labelrow.="<TD><A HREF=\"/cgi-bin/gufe.cgi?$queryURLb\">".
              "<B>$fieldName</B></A></TD>";
  }

Here we loop through each field label as returned from the database. In doing so, we create the first row of the table, and wrap each field label inside a hyperlink. The hyperlink is rigged to connect back to GUFE with the same parameters as presently, with the addition of a sort parameter. The sort type is set to the opposite of whatever the current sort type is (ascending or descending). We only need to create the field label row once, not each time a record is retrieved, ensured by the unless ($rowcount>0) condition.

  if ($fieldName eq "Invoice") {
   #wrap hyperlink around invoice data, rigged to invoice table
   $tableHTML.="<TD><A HREF=\"/cgi-bin/gufe.cgi?".
               "table=invoices&criteria_condition_Invoice=%3D".
               "&criteria_value_Invoice=$row->{$fieldName}\">".
               "$row->{$fieldName}</A></TD>";
   }
  else {
   $tableHTML.="<TD>$row->{$fieldName}</TD>";
  }

The above excerpt is the least "generic" code in GUFE. Specifically, it traps for a field labeled Invoice, and it wraps such data inside a hyperlink that is rigged to pull up that Invoice record in the invoices database. This is not generic because we can't assume that any database contains an Invoice field, let alone one which is keyed between two tables. The code has been kept in GUFE to illustrate, though, how you might rig your own database in this fashion, to allow the user to quickly move between related records.

If the field label is not trapped as "Invoice", our normal, generic processing occurs. A table column is created into which the value for that field is placed. Note the syntax for retrieving the field value from the hash reference:

$row->{$fieldName}

Of course, you can explicitly request the value for a given field as well:

$row->{ClientEmail}

The important thing to remember is that $row is a variable of our choosing, to which we assigned the fetchrow_hashref procedure of the $sth statement handle.

}
$tableHTML.="</TR>";
$rowcount++;
}

if ($rowcount==0) {
 #database did not return any records
 $tableHTML.="<TR><TD>There is no table view that matches ".
             "your selected criteria.</TD></TR>";
}
} #end if wrapper
else {
	#no table has been selected to view
	$queryTable="None Selected"; }

It is possible that the user's selected criteria won't return any records from the database, in which case $rowcount would never have been incremented, and we can return a "no match" message rather than the database table, as seen above.

The else clause above is actually the partner of a much earlier if clause, that which tested whether any table had been requested at all. If no table were requested, we modify $queryTable to "None Selected", which will later appear as the title of the empty table that is produced.

#generate hyperlinks to other tables in this database
@tableNames=$dbh->tables;
$tableHTML.="<TR><TD COLSPAN=$fieldCount><BR>".
            "<FONT FACE=\"Arial,Helvetica\">".
            "Available tables to view: ";
foreach $tableName (@tableNames) {
 unless (($tableName eq $queryTable)&&($rowcount>0)) {
  $tableHTML.="<A HREF=\"/cgi-bin/gufe.cgi?".
  "table=$tableName\">$tableName</A> ";
 }
}
$tableHTML.="</FONT></TD></TR>";

$tableHTML="<TABLE width='75%' border=0 bgcolor='#FFFFCC'>".
           "<CAPTION><B><I>Table: $queryTable</I></B>"."
           "</CAPTION><TR>$labelrow</TR>$tableHTML</TABLE>";

In the lower region of the database output, GUFE offers hyperlinks to any other available tables in the database. GUFE determines the list of available tables using the DBI tables procedure. Note that this procedure is only supported in the most recent versions of DBI, and it may not be supported in the DBD module for your particular database (it is supported by the most recent DBD modules for ODBC (Access) and MySQL). If your DBI/DBD combination does not support tables then no list of tables will appear, since the list @tableNames will wind up empty.

The HTML table is finished off by prepending some HTML to begin the table and include the table name in the table's caption.

GUFE has now constructed the HTML that comprises the yellow table -- in other words, the table returned from the database. We also want to offer a form with selectable criteria for the user to construct a query, also known as the blue region. It might be worthwhile to construct this form as a separate chunk of HTML, and insert it into the HTML template with a second placeholder ... but for simplicity we simply add this nugget of HTML onto the existing $tableHTML.

unless (($queryTable eq "None Selected")||($rowcount==0)) {
 #construct criteria customization form
 $tableHTML.=
    "<P><FONT face=\"Arial, Helvetica, sans-serif\" size=\"-1\">".
    "Customize the table view using the fields below.".
    "<br>You must put single quotes around text values.".
    "<br>Click Apply with all set to IGNORE to view whole table.".
    "</FONT></P>";
 $tableHTML.=
    "<FORM method='get' action='/cgi-bin/gufe.cgi'>";
 $tableHTML.=
    "<TABLE width='75%' border=0 bgcolor='#CCFFFF'>";
 foreach $fieldName (@fieldNames) {
 $tableHTML.=
    "<TR><TD>$fieldName</TD><TD>";
 $tableHTML.=
    "<select name=\"criteria_condition_$fieldName\">".
    "<option value=\"0\">IGNORE</option>".
    "<option value=\"=\">equal to</option>".
    "<option value=\"like\">like</option>".
    "<option value=\">=\">greater/equal to</option>".
    "<option value=\"<=\">less/equal to</option>".
    "<option value=\">\">greater than</option>".
    "<option value=\"<\">less than</option></select></TD>";
 $tableHTML.=
    "<TD><INPUT type=text width=10".
    " name=\"criteria_value_$fieldName\">".
    "</TD></TR>";
 }
 $tableHTML.=
    "<TR><TD colspan=3>Combine criteria with ".
    "<select name=\"criteria_logic\">".
    "<option value=\"AND\">AND</option>".
    "<option value=\"OR\">OR</option>".
    "</select></TD></TR>";
 $tableHTML.=
    "</TABLE><INPUT type=hidden name=\"table\" value=$queryTable>".
    "<INPUT type=hidden name=\"return\" value=$returnFields>".
    "<INPUT type=submit value=\"Apply Criteria\"></FORM>";
         
 }#end unless wrapper
return $tableHTML
} #end resultTable

The above segment appears messy, but it's none too magical. Mostly, we construct HTML code containing a table nested inside a form. The table contains a row for each field label in the yellow (upper) table. Each row consists of three columns: the field label, a drop down select field, and a text input field. This allows the user to construct a simple condition for each field, such as "Total > 50".

The final line of code, "return $tableHTML" may seem slight but it is crucial. This returns the entire chunk of HTML we've created to the calling statement. Remember that the calling statement in this case was &createPage. In essence, this is like a baton relay, with &resultTable handing off the baton to &createPage.

Speaking of &createPage ...

Click here for code example 7.

Unlike the varied responsibilities of &resultTable, the above subroutine has one straightforward goal: insert the HTML from &resultTable into the HTML template page. We open up the template file, specified in $templatePage, join the file into one long line, and search-and-replace the known comment tag with the entire HTML chunk that GUFE has generated. This subroutine returns its result -- the entire HTML page -- to the calling statement, which was print(), causing it to be sent to the browser. Sweet peas.

Conclusion

We've spent a lot of time this month looking at segments of code and narrating segments of code. But the real core of this article is the code itself. If you want to implement, modify, or better yet, understand GUFE's application to your own web-based database you should spend time reading, tinkering, reading, and tinkering some more with the full unmodified GUFE script.

Resources

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved