SE USING CGI

Add To MyInformIT

< Back Contents Next >

Author: Michael Erwin
Retail Price: $59.99
Publisher: Que
ISBN: 0789707403
Publication Date: 4/19/96
Pages: 1000

Chapter 13 - Custom Database Query Scripts

By using CGI scripts to access a d, you can provide worldwide, cross-platform access to your data via the Web while your database helps protect your data from corruption and provides efficient querying. It's not easy to build a good Web/database application, but the results can be well worth the effort. As you'll see in this chapter, a Web gateway to a database can be a powerful way to circumvent some major disadvantages of conventional client/server technology.

In this chapter, you'll learn

Fundamental Design and Security Issues

Figures 13.1 and 13.2 show how information flows between the various programs that together constitute a complete Web/database application. Each layer hides many internal details from the other layers and presents the layers above and below it with standardized protocols. This information hiding is the great advantage a Web front end has over conventional client/server systems: The numerous, widely distributed users need nothing but standard Web browsers on their computers. The Web server and the database server could reside on one machine, or they could reside on different machines, in which case your CGI program talks over a network to the database server.

Figure 13.1 : This schematic of Web/database interaction shows how information flows between the programs that make up the application when the database server and the Web server are on the same machine.

More complex possibilities exist. One application running at my site talks to several database servers, integrating information from a local database with several remote databases. Indeed, users may not even realize how many different computers are cooperating to answer their queries; with a well-written database application, all a user needs to know is what information to request. Your job as a Web/database developer is to build tools that allow users to find information without their knowing or caring where the information is actually stored.

Why Build a Web/RDBMS Interface?

Building a Web interface to a relational database management system, or RDBMS, isn't simple. You need to become familiar with relational database technology and CGI scripting, and you also need to understand how these two technologies can be integrated with each other.

Figure 13.2 : This schematic shows how information flows between the pro-grams that make up the application when the database server and the Web server are on different machines.

You may also need to do much more "roll-your-own" work than you would with a conventional client/server design because you'll need to polish the rough edges off the available tools for Web gateways to relational databases. Many of the most powerful products are freeware or shareware, and the commercial products are all very new. Just about every database vendor now has some kind of Web offering, but most are either in beta or just barely out of beta. In short, if you do Web/database development, you'll be on the cutting edge.

So why do it? For one thing, the Web is now a "hot" field, so you may feel a compelling desire to be in the vanguard. You may also see the neat tricks others make their Web sites do and wonder how you can duplicate such effects.

I believe there are also some very good technical reasons for putting databases on the Web. You can combine the strengths of RDBMS technology for building robust data repositories with the strengths of the Web for providing distributed, cross-platform remote access to your database. In my view, the main advantages of a Web/RDBMS interface are as follows:

Of course, most of these advantages are shared by any Web application, whether it uses the simplest of flat-file structures or a high-end relational database engine.

Let's talk a little about the reasons for using a relational database. I'll admit at the start that a relational database forces you to do a lot of work before you have anything to show for it. With a simple flat-file database, you can get up and running much faster. The extra work involved in setting up a relational database does bring significant benefits, especially for large and/or complex databases.

One major advantage of a relational database over a flat-file approach is that, because a relational database has dramatically less redundancy than a flat-file database, it will have far fewer inconsistencies.

Suppose that Jane Smith's address appears 14 times in a flat-file list of orders from your customers. Some records might list Jane's street address as 123 Elm Street, Apt. 507; others as 123 Elm St. #507; and still others as 123 Elm #507. (Take a look at a week's worth of your own snail mail!) Now suppose that Jane moves. How can you be totally certain you'll update every copy of her address-without changing the 17 copies of Janet Smith's address in your database?

If your database isn't very large, you can check for such inconsistencies manually, with the help of the searching and sorting features of your flat-file database. However, if you have hundreds or thousands of customers, it'll be nearly impossible to keep a flat-file database consistent. With a relational database you store a fact, such as a customer's address, in just one place; you then use the capability to search multiple tables with one query to cross-reference the table of customer addresses with other tables whenever an address is needed.

I won't try to teach relational design in this chapter, because that's beyond the scope of this book. Many excellent books on relational database design are available; I'd recommend practically anything by C.J. Date, David McGoveran, Joe Celko, George Tillman, and Andrew Warden (pseudonym for Hugh Darwen; some of his works have appeared under both names at different times). Any large technical library or bookstore should have works by most of these authors. At a more advanced level, the writings of E.F. Codd, who invented the relational model, will reward prolonged study. If more RDBMS programmers read Codd's works with care, the RDBMS implementations now on the market would be far better.

After reading what these people have to say, I hope you'll share my passionate conviction that the relational model is both an elegant mathematical formalism and a powerful tool for solving real-world database problems in a manner that no other approach can match. In the long run, a flat-file database that gets heavy use will likely create more trouble than you avoided by not doing a good relational design from the start.

Limitations of HTTP in a Database Context

With a CGI front end to an RDBMS, you have multiple programs cooperating to accomplish a task: The remote browser is a client to your HTTP server, but the CGI script launched by the HTTP server is itself a client to the RDBMS server. Furthermore, the HTTP protocols are stateless-the browser connects to the server, sends one query, and waits for the reply. There's no concept of a current connection, so there's no simple mechanism for keeping client state information around on the server.

For most searches, you can get around the statelessness of the server by keeping all client state information on the client. Suppose that a user has just done a search that returns the list of employee records shown in figure 13.3.

Figure 13.3 : In this sample query results screen, each item in the results list is a hot link that performs another search.

Encoded in the URL behind each hot link in a list like that in figure 13.3 is sufficient information for the CGI script to perform a database lookup that will return more details to the user. This technique-sending program-generated HTML that contains enough state information to perform the next search-is known as dynamic HTML. The URLs listed in a search results screen, such as the one in figure 13.3, will typically look something like the following:

id is the primary key on the employees table that's to be searched. (By primary key, I mean the database designer has set things up so that a given key value uniquely identifies a single record.)

How does this allow the database server to display a detail view without any information beyond that in the URL? Well, recall from Chapter 4 "Understanding Basic CGI Elements," that when an URL contains a question mark, the part following the question mark is passed to the CGI program as the environment variable QUERY_STRING. The SQL code generated in response to a QUERY_STRING containing a primary key value of 503, like the first sample URL above, might look something like listing 13.1.

Chapter 17, "Person-to-Person Interaction," shows other examples of how HTML generated in response to each request is changed so that the next request will have the correct state information. Database applications will need to make particularly heavy use of such techniques.

When you're updating a database, the use of a CGI interface presents some serious difficulties if more than one user is allowed to make changes to the database. If you aren't careful, you can have the following sequence of events:

With a conventional client/server database system, each user maintains an active connection with session-specific state information. If a user has downloaded a record for editing, the server can keep that record locked until the user who downloaded that record for editing has either submitted the changes or canceled the editing. Any other user who tries to change the locked record will be told that it's now locked by the user who's editing it. With a stateless HTTP server, there's no concept of a current connection and thus no simple mechanism for locking a record.

Another significant limitation of HTML forms for database work is the lack of field-level validation. A conventional client/server database typically allows the designer to specify various constraints on each field: this one must be an integer between 5 and 99, that one must be in a telephone number format, and so on. When a user tries to type a name into a phone number field, the program immediately beeps and displays an error message. But an HTML form is sent to the server all at once when the user clicks the submit button, so your script must handle the errors all at once.

Security Issues

Remember that any CGI script is being executed on the same machine as your HTTP server to fulfill a request from an untrusted client browser. Although this is of course true of any CGI script, with a CGI RDBMS application, your script is itself a trusted client to your RDBMS server. That means you must be even more careful about what you allow users to do through your CGI interface than you would when writing other types of CGI programs.

I said that your CGI program is a trusted client to your RDBMS server. How does the RDBMS server know your script is to be trusted? Two mechanisms are commonly used by database servers to authenticate client programs, both of which have important security implications.

One approach is for the database server to implement its own user name and password system, independent of your operating system's or Web server's user names and passwords. In this case, your program source code must contain a database password, which it transmits every time it tries to connect. This, of course, means you must be careful to prevent strangers from being able to see your actual program code.

Also, to limit the damage in case someone does manage to see the password contained in your program, you should create a user account on your database server with only the access rights needed for your CGI program to function, and use that in all your CGI programs. If most of your CGI programs do only searching, and only one does updating of the database, just that one should have update rights in the database.

The other common mechanism for the database server to decide whether a client can be trusted is to define database access rights for specified operating system user names. This, of course, means the database server must trust the operating system to authenticate users. In the CGI context, the use of operating system user names for authentication presents an especially tricky issue because most HTTP servers run all CGI scripts under a special, low-privilege user name (for example, most UNIX HTTP servers run all CGI scripts under the name "nobody" or the name "www"). This means you must trust every person who writes CGI scripts on your HTTP server!

One alternative provided in some operating systems is the capability to have a CGI program run as the user name of its owner with all rights that person would have. This eliminates the need to have your database server trust every CGI script, but creates its own security problems because now your CGI program has significantly more access rights to the rest of your system than does a CGI running under a special, low-privilege ID. Of course, if your HTTP server is running under a single-user operating system that lacks the concept of user names, you must trust every CGI program in any case.

A Simple Working Example in Perl

I often find vague generalities and isolated code snippets frustrating because they don't tell you how the various pieces fit together to form a complete application. To show you how a Web/database gateway works, in the following sections I'll build a small working application that maintains a hotlist of Web sites, which remote users can search.

First, let's see how a Perl script talks to the database. I'll show the specific calling conventions of two database engines; then I'll show how to make the code much more portable. The sample application is written using this portable interface.

DBMS Access from Perl

As I noted earlier in the sidebar titled "SQL and its Dialects," the subset of SQL needed for most CGI/database programming is very nearly universal; the same SQL code can often be ported with little or no change from one database engine to another. However, the details of sending that SQL to the database server and getting the returned results back from the server are much more varied. Most SQL database servers provide some form of C API for this purpose-typically, a set of functions that can be linked into a C client program. Because Perl, with its very strong string manipulation and I/O facilities lends itself so well to database manipulation, Perl wrappers have been written for most of the common database server APIs. This enables database access from within Perl programs, but limits portability because each database server API is unique.

Two DBMS APIs

To illustrate the variation in database APIs, listings 13.2 and 13.3 show two short sample programs written in the Perl wrappers for Sybase and mSQL servers. Each program connects to the server, asks for a database called test, sends a single SQL statement, and prints out the results.

The output from either program would look something like the following:

Notice in listings 13.2 and 13.3 that the SQL code string is exactly the same for either database server, and the output is also the same (assuming identical data in the table, of course). Also, the structure of the two programs is very similar-connect to the database, send the query, get the rows. But the details of how the client communicates with the server are different. Obviously, what you need if your code is to be portable is some kind of abstraction layer that insulates the programmer from most database-specific details. Fortunately, such a layer has been written.

A Simple DBMS Abstraction Layer

The author of the excellent WDB database forms-generation package, discussed in more detail later in the section "WDB," has written a simple database interface, or dbi layer. (The dbi libraries are included in the WDB distribution on the CD-ROM accompanying this book.) By isolating most of the database-specific details to one Perl function library, he made the whole package quite easy to port-and various database programmers have written versions of the dbi library. At the time of this writing, versions of WDB-and therefore of the dbi library-are available for Sybase, Informix, mSQL, and Postgres95. The most recent release of WDB includes an alpha version of the Oracle port. Listing 13.4 is a dbi version of the simple Sybase and mSQL clients in listings 13.2 and 13.3.

If you have either Sybase and Sybperl or mSQL and MsqlPerl installed on your system, you can run the code in listing 13.4 on either platform by editing it as indicated by the comments in the program. And revising it to work with the other versions of the dbi library shouldn't be much more difficult. All Perl examples in the rest of this chapter use the msql_dbi.pl interface, so they could easily be ported to any other database for which WDB has been ported.

Schema for the Working Example

This example, as I said earlier in the section "A Simple Working Example in Perl," is a simple interactive hotlist of Web sites, with the URL and a description for each site. Remote users can search the hotlist and submit new entries for potential inclusion. The administrator (who knows the appropriate password) can review submissions, adding approved submissions to the hotlist for public viewing. Think of it as a very rudimentary equivalent of Yahoo-just as the Wright brothers' flying machine of 1903 was a very rudimentary equivalent of an airliner.

Database Tables and Fields

This database has three tables. The UIDs table in table 13.1 is used for generating UIDs so that each record in the other tables will have a unique identifier that can be used as a primary key.

Table 13.1  The UIDs Table

This is a very common technique used by database designers: One creates one row in the UIDs table for each table that needs UIDs generated. MaxUID then records the highest UID yet assigned; each time you create a new row for a data table, you increment the MaxUID value for that table and use this for the new row of data.

The Hotlist table, shown in table 13.2, contains data for all approved submissions to the database.

Table 13.2  The Hotlist Table

New submissions are stored in the Submissions table until they have been approved by the database administrator, as shown in table 13.3. The Hotlist table and the Submissions table are otherwise identical.

Table 13.3  The Submissions Table

Directory Layout

The data tables are stored by the database server-mSQL was used for this example. Although any database server almost certainly stores the actual data as disk files somewhere, the database server manages those internally; indeed, that's fundamentally why one uses a database server. In addition to the database tables described in the preceding section, my sample application consists of three HTML documents and three Perl scripts placed in three directories, as follows:

The three files are the top-level main screen and the two forms for searching and submitting data. Because they reside in my public HTML directory, they can be viewed by any user on the Web.

The following two programs reside in an unprotected directory within the cgi-bin hierarchy on this Web server, so they can be run as CGI scripts by anyone on the Web.

The following directory is password protected by means of the .htpasswd and .htaccess files, so you must type a name and password to run the program in this directory as a CGI script.

Searching the Hotlist

The user of this application typically will begin with a simple opening screen listing the available options. I've intentionally kept the screen in figure 13.4 as simple as possible. Listing 13.5 shows the HTML for it. Most of the hot links in this opening screen point to scripts that perform the actual work of providing database access.

Figure 13.4 : The opening screen for the Hotlist database has hot links to the available programs for database access.

Clicking Search the Hotlist calls up the search form, which I've likewise kept as simple as possible (see fig. 13.5). Listing 13.6 is the HTML for the search form in figure 13.5.]

Figure 13.5 : The user enters search criteria into this form, which then posts the criteria to the searching script.

Submitting a search request-by entering a key to search and clicking the Submit button-will POST the search key to a simple searching script. This script generates a SQL query and submits it to the server. It works just like the three sample database query scripts in listings 13.2 through 13.4, except that the SQL string is built up from the form data. This is known as dynamic SQL.

To keep this example as simple as possible, I provide only one search field, and I canonicalize that field to uppercase in the database. If your search form provides multiple lookup fields, you must generate a complex where clause based on which fields contain search strings. The WDB package, which I discuss later in the section "WDB," can build up such a where clause based on form contents.

In the program in listing 13.7 and all my other form-handling CGI programs, to avoid having all the messy details of reading and parsing the form information, my programs call Steven Brenner's cgi-lib.pl routines. This library can be found at many FTP sites or on the CD-ROM included with this book. One particular advantage of cgi-lib.pl for database work is that it handles GET or POST identically: Name value pairs can be appended to the URL as ?name1=value1&name2=value2... or sent as a POST data block.

After a search is performed, the output looks something like the sample screen shown in figure 13.6. Listing 13.8 is the HTML generated for the search results in figure 13.6.

Figure 13.6 : After searching the hotlist database, the user sees a list of hot links like this one. Each hot link calls a Perl script that shows the details of that record.

Viewing the Detail Record

Notice that the Details links in the search results screen shown in figure 13.6 (with the HTML given in listing 13.8) point to a second cgi script and that each URL has ?UID=nn appended. This simple example shows how state is maintained on the client browser side-no history is maintained on the stateless server. Listing 13.9 shows the code for ShowDetails.p, the CGI program that generates the detail record; it's quite similar to the previous example.

Figure 13.7 shows an example of the detail screen, and listing 13.10 shows the HTML generated for this screen.

Figure 13.7 : Clicking an URL in the search results screen of figure 13.6 generates a detail view such as this one.

In this simple example, I have only one hot link-to the URL being described. In a real application, you can (and should) have multiple hot links in your detail screens-hot links that perform lookups on this or other databases. The HTML snippet in listing 13.11, from a hypothetical Employee Detail screen, shows what I mean.

Clicking any field in the detail record will perform a lookup of related records in that category. The list would contain the names of employees, and the URL behind each name would do a lookup on the employee ID. This effectively converts a relational database into a giant hypertext document.

Such hyperlinks can just as easily link one database with another. For example, several large international databases that are widely used in molecular biology have Web interfaces. A local database used by one research group for its own internal data can include hot links to related information from one or more of these international databases.

Consider a hypothetical business-related example. The marketing people in your company have created a Web-accessible database of product information, including which company locations build given parts of each product. Now suppose that you're a programmer in the personnel department given the job of putting your company directory onto the Web. You could use hyperlinks in your directory database to link product information with directory information. For instance, the location field in a directory detail screen could show the list of products made at that location.

With the cooperation of the folks maintaining the product information database, you could also work the other way: When seeing a record listing the various locations making each part of the product, you could click links that locate people who worked at each location. The possibilities for using the Web to integrate multiple databases in different locations are limited only by the programmer's imagination-and the quality of available databases, of course.

Submitting Data to the Hotlist

Of course, a database is scarcely complete without a means for entering data. For some databases, the Web front end allows only searching. For this example, however, I'll also include a simple data submission form so that any user can submit proposed records for possible inclusion in the publicly searchable database. Figure 13.8 shows the submission screen. Listing 13.12 is the HTML for the submission screen.

Figure 13.8 : This form is used for the remote submission, via the Web, of records to be added to the database.

Submitted data will be posted to the script in listing 13.13.

A couple of interesting "wrinkles" to this script don't appear in the other programs:

The theoretical possibility exists that two people might submit new data at precisely the same moment, so that between the instant of getting the current value of MaxUID and the instant of updating UIDs, another user could get the same UID value. Although that's not very likely with a simple application like this one, it's very much a concern for very active databases. Most high-end database engines have a feature called transactions (which mSQL doesn't support); one declares the actions of getting the UID and updating the UID to be one transaction that must be run as a unit or not run at all.

Generating SQL Code to Transfer Submitted Data

The last piece of this package is a mechanism by which an administrator can transfer data from the Submissions table to the Hotlist table. To sidestep the complexities of updating via the stateless Web server, I use a different approach-a CGI script that doesn't perform any updating itself, but generates an SQL script to perform the required actions. Listing 13.14 is the Perl code.

Running this script via the Web will generate SQL similar to that in listing 13.15.

The database administrator edits the SQL generated to delete records that shouldn't be added to the Hotlist table, and then feeds the script through the mSQL command line. Alternatively, if you want a purely Web solution, you can modify this script to generate a form containing all the SQL in a scroll- able text area. That form would submit the edited SQL to another script that pipes the SQL through the mSQL command line.

Probably the cleanest approach is to generate updating forms that contain database fields instead of SQL code. However, the issue of conflicting updates would then need to be addressed, probably by the use of timestamps. The sequence would be something like the following:

With mSQL, you need to update the timestamps in the CGI script and in every program that updates the database. With many higher-end servers, you can define a timestamp that the database engine will maintain automatically with no possibility that a programmer will forget to change timestamps whenever any data changes.

Web/Database Tools

Database access via the Web is such a "hot area" now that it would be impossible to list every product available or promised. Every major database vendor either has a Web-related product available now or promises to have one soon, and many individual programmers are working on their own database gateways, running with a wide range of database engines. For a large database, one probably needs a high-end engine-such as Sybase, Oracle, or Illustra-but for simple projects, a microcomputer-based DBMS such as Access or FileMaker Pro can also be used. For intermediate-scale projects, you can also use middle-of-the-road databases, such as MiniSQL, which are capable servers for UNIX but lack the power (and expense) of high-end commercial databases.

You can safely assume that a mechanism exists for linking any reasonable combination of database server and HTTP server, but the quality of the implementations varies wildly. If you're thinking of putting a database on the Web, I'd suggest you start by reading Usenet postings in newsgroups related to your HTTP server, your favorite programming languages, and your favorite database packages. You should also look at the Web site of your database vendor. In the following sections I list a few tools available on the Web and some Web sites containing pointers to information about Web/database tools.

WDB

WDB is a freeware package written entirely in Perl that greatly simplifies the task of building a Web front end to a relational database under UNIX. (Source code and installation directions for WDB, in UNIX tar format, are included on the CD-ROM that comes with this book.) Because all the database-specific aspects of WDB are confined to one module of the program, and the entire package is written in Perl, porting WDB to various databases has proven relatively easy. WDB has been successfully ported to a number of database servers and to many versions of UNIX, including Linux. If you're UNIX and Perl-oriented, as I am, WDB should be very high on your short list of preferred free solutions, especially because the excellent shareware database mSQL is one of the supported SQL engines.

The heart of WDB is what its author, Bo Frese Rasmussen, calls a form definition file (FDF). For each searching form, you write a description in a relatively high-level form definition language to list the tables and fields to be used for this form, the search constraints, how you want the output formatted, and so on. WDB comes with a utility program that generates an FDF template from a database table or view by querying the metadata provided. You then edit its output. Although the program can't do your thinking for you, many details that would otherwise need tedious hand-hacking are automatically filled in by the FDF-making utility.

Given an FDF, WDB will generate an HTML search form on the fly. As with the program-generated FDFs, you may want to edit the output. When the user submits a completed search form, WDB generates the SQL query, performs the search, and returns a list of matching records, each item of which has a hot link to the full screen view of that record.

The upshot is that WDB does much of the donkey work-especially for ad hoc searches-for you. Its feature set is somewhat limited, but if you know Perl, you can probably modify it to your heart's content-the code is well-structured and well-commented. Simpler customizations can be handled by adding snippets of Perl code to the FDF because several "hooks" are provided to call your own code at various stages in the query process.

A particularly nice feature is the capability to define input and output conversion functions that apply to specified fields; WDB will automatically invoke these at the correct time in its query-generation and formatting cycle. WDB also has several features designed to help you generate URLs from database lookups, which can perform other lookups of related data when clicked. Clever use of this elegant feature of WDB can effectively turn your relational database into a gigantic hypertext document.

Information about WDB, installation instructions, and the downloadable source code can be found at the following URLs:

Web/Genera

Web/Genera, by Stanley Letovsky, Mary B. Berlyn, and others, is another public-domain software tool set that simplifies the integration of Sybase databases into the Web. It can be used to retrofit a Web front end to an existing Sybase database, or to create a new database. Similar to WDB, Web/Genera requires you to write a specification of the Sybase database and of the desired appearance of its contents on the Web by using a high-level schema notation. The Web/Genera programs process this description to generate SQL commands and formatting instructions that together extract objects from a database and format them into HTML.

Web/Genera also supports form-based relational querying and whole database formatting into text and HTML formats. The notation of Web/Genera seems to be richer than that of WDB because the former includes notions, such as set and group, for field values. This makes it possible to build pick lists on the fly. However, unlike WDB, Web/Genera doesn't provide hooks for users to write customized extensions. To make any extensions to Web/Genera, the user must change its source code, which is written mainly in C and Perl.

Information about Web/Genera, installation instructions, and the down-loadable source code can be found at the following URL:

GSQL

GSQL, written by Jason Ng at NCSA, covers much the same territory as WDB and Web/Genera, but is written in C instead of Perl. As with WDB, GSQL uses a higher-level language to write what its authors call PROC files. Unlike WDB, GSQL doesn't provide a utility to generate an initial version of the PROC file for you. A similarity with WDB is that GSQL has also been designed with porting in mind and has been ported to several different databases on various flavors of UNIX. The PROC language used by GSQL is more procedural in flavor than the FDF language used by WDB, which I would describe as more descriptive.

Overall, I consider GSQL somewhat more powerful than WDB but significantly more difficult to install and maintain. Also, customization must be done in C rather than in Perl. The more procedural nature of its schema language gives it more flexibility than WDB but also means you have to do more work.

Information about GSQL, installation instructions, and the downloadable source code can be found at the following URL:

MORE

MORE is an acronym for Multimedia Oriented Repository Environment, which is part of the Repository Based Software Engineering Project (RBSE) led by Dave Eichmann, Terry McGregor, and Dann Danley. As you may have guessed from the acronyms, MORE is designed for very large-scale database repositories. It comprises a number of large C programs-totaling more than 30,000 lines of code-on top of a large Oracle database. It's also designed to allow a single database schema to be distributed across a number of servers, presenting a consistent user interface to a larger amount of data than any single server could handle.

In addition to the demonstration of MORE itself, its Web site also contains links to some very interesting technical papers discussing how MORE works and how the inherent limitations of current Web technology constrained the design of MORE. These papers are well worth reading-even if you never intend to build anything as complex as MORE-because they have some very lucid discussions of general Web/database design issues.

Information about MORE can be found at the following URL:

MiniSQL (mSQL) and W3-mSQL

mSQL is a shareware SQL engine (free to academic users) that has been ported to most versions of UNIX. In combination with WDB, it provides an excellent, low-cost way to build a simple relational database with a Web front end. And the combination of WDB with mSQL is known to work well under Linux.

mSQL's author, David Hughes, has also written a new program called W3-mSQL, which was in alpha at the time of writing. Although I've included both mSQL and W3-mSQL on the CD-ROM included with this book, I haven't yet tried W3-mSQL myself, so I don't know how well it works. According to its author, W3-mSQL works via HTML documents with embedded tags that perform database lookups, so you don't need to write any CGI scripts to use it.

mSQL supports only a relatively small subset of SQL, but what it does support is in strict conformance to ANSI standards. According to its author's Web site, he's working on a major upgrade of mSQL that will support a much larger set of features, but doesn't yet know when this will be released.

Information about mSQL and W3-mSQL, installation instructions, and the downloadable source code can be found at the following URLs:

DBI

The long-term future of Perl-database interfaces in many applications, including Web interfaces, may well lie with the new DBI project. The long-term goal of this project is to create a generic Perl interface with versions for every SQL database in the hope of providing greater portability for Perl database applications. It also takes advantage of the Perl 5 object-oriented extensions.

Information about the evolving DBI standard can be found at this URL:

DB Gateway System

This low-end database gateway written in Visual Basic is intended for use with Windows/Windows NT Web servers and either FoxPro or Microsoft Access 95. Unlike most Windows-based Web/database products, it doesn't use ODBC drivers. Instead, it uses the database interface built into Visual Basic, which means it can access only a database that's local to the machine on which the Web server is running. According to its authors, Philip Jones and Larry Perkins, this is because they're targeting users who don't have the money or the computing resources for a client/server database system.

Information about the DB Gateway can be found at

ODBC Tools

In the Windows/Windows NT environment, ODBC has long been one of the most popular ways to access databases from other programs. As a result, most popular Windows databases and most programming languages targeted for the Windows environment have ODBC drivers included or available. If you come from a UNIX-oriented background, you may want to use one of the ODBC drivers for NTPerl that can be found various places on the Net. If you come from a Windows background, you'll probably be happier using Visual Basic or Visual C with any of the numerous ODBC drivers available for those languages.

Information about ODBC access from Perl under NT can be found at the following URLs:

Macintosh Tools

Just as ODBC has become the lingua franca of Windows/NT Web/database integration, AppleEvents have become the most popular mechanism for CGI work on the Macintosh platform. A number of freeware, shareware, and commercial tools link various Macintosh databases to the WebSTAR server (by far the most popular Web server on Macs) via AppleEvents. However, Apple-Events provide standardization only at the level of interprocess communications; the most popular Macintosh databases don't support SQL, which means you must learn the proprietary language of your database. One exception is an excellent Macintosh SQL engine called Butler, from Everywhere, with good interface products (Tango and Bolero) for WebSTAR.

In most cases, the database will be either FileMaker Pro at the low end or 4th Dimension at the high end. FileMaker Pro is very easy to use and sufficiently powerful to handle simpler designs quite well, but it has limited functionality for more advanced work. Claris recently added some relational features to FileMaker Pro and very recently put some tools on its Web site for CGI work with FileMaker Pro.

ACI's 4th Dimension is a very powerful product with a rather steep learning curve. Its programming language has a depth and richness few other database languages can match, and its object-oriented tools for drawing the relational schema of your database and the input/output screen layouts are superb. It takes time to learn, but the results can be well worth it. For simpler applications it's probably overkill. And, from the programmer's point of view, because the language of 4D is nothing like SQL, the transferability of skills is limited between it and other database systems.

Information about Web/database integration on the Macintosh can be found at the following URLs:

Some Useful Hotlists

Many sites on the Web have pointers to various Web/database tools and projects. The following URLs are the most comprehensive listings of Web/database resources I have found in an extensive search for such information:

Problem Solving

Because a Web/database gateway involves multiple programs, possibly running on multiple machines, various things can go wrong. Your application may not work at all-or it may run, albeit far too slowly. For database-specific debugging and performance tuning, you'll obviously need to consult the documentation for your database package. Here I give a few general hints for debugging and tuning a Web/database gateway-hints that should apply to any platform.

Debugging

Debugging a Web/database gateway isn't simple because problems can occur at multiple levels. In addition to the hints for debugging any CGI script found in Chapter 25, "Testing and Debugging CGI Scripts," you need to examine the SQL generated by your CGI scripts with a hand lens. I suggest the first version of any database CGI script not try to access the database, but instead display the generated SQL code in a document that begins with the header Content-type: text/plain, followed by a blank line. When you think the generated SQL looks correct, pipe it through the command-line interface of your database. After this confirms that you can generate correct SQL, the next step is to write your own client program that sends the SQL to the server and executes it from the command line.

If you can't even get your CGI script to generate any SQL, try sending your form input to a CGI script that does nothing more than list all the information it got from the Web server. Given that list, add hard-coded assignment statements to your code so you can run it from the command line and watch how it behaves when given a simulated CGI environment. Only then should you try to combine the CGI and database interfaces into one program and run that program as a CGI script.

If it doesn't work, look at the error log of your server for any messages your program may have sent to standard error. If that tells you little or nothing, you need to think about the differences between a command-line environment and the CGI environment. Are you assuming something about the PATH environment variable, which has far fewer directories in a CGI context? Do you need other environment variables for database work? For example, most Sybase installations define an environment variable, DSQUERY, that tells Sybase clients which server to use if none is specified. In the CGI environment, this variable isn't defined, so your script will fail unless you specify the server in your script.

Finally, remember that CGI scripts usually run as a lower-privilege user ID than regular programs. If your database server uses OS-level user IDs for authentication, you may have client programs that work just fine from the command line but not as CGI scripts.

The general strategy is to divide and conquer, so you can isolate the problem to a particular part of your system. Start simply and build up to the full functionality you want one step at a time.

Performance Tuning

From the hardware perspective, database and HTTP servers tend to be I/O bound, not CPU intensive. This means the performance of your Web/database application will depend mainly on disk access and process-launching overhead. A fast hard drive and lots of memory are more important than a fast processor. However, a badly designed Web/database application can bring even the most powerful server platform to its knees.

Remember that your application is, in fact, a client/client/server application in which your CGI script connects as a client to the database engine, sending SQL to the engine and accepting data returned from the engine. Considerable overhead is associated with setting up and tearing down the connection between the CGI script and the database server. Even a well-designed CGI application will have to incur this overhead every time a request comes to the HTTP server; a single session from the viewpoint of the remote user involves multiple database logons. This is unavoidable, but a badly designed CGI program can make matters worse if it opens and closes multiple connections to the database engine in a single HTTP request.

Even if you avoid opening an excessive number of connections to the database server, you can still hurt performance by sending too much data back and forth between the CGI script and the database server. It's a nearly universal rule in client/server database programming to do as much work as possible inside the database server to minimize the overhead associated with the transferring of data between database client and database server. In CGI work where you're already incurring some extra overhead because each HTTP request requires a new connection to the database, this principle applies with particular force.

For server efficiency, observe the following rules:

These rules apply even if your database engine and your HTTP server reside on different machines. This is because all data transfer between your CGI program and the database server incurs network overhead as well, as the overhead inherent in any connection to the database server.

Recently, a colleague asked me for help with a CGI interface to an Illustra database that was taking more than 3 minutes to respond to some queries. By applying these principles and taking advantage of Illustra's unusually powerful version of SQL, he could get the response time to under 10 seconds. Most database servers can perform only internal manipulation of fixed-size data types; large text fields can be copied in or out, but must be manipulated outside the database program. Illustra's string-manipulation commands can be applied to any amount of text. We used this feature of Illustra to build very complex SQL queries that would perform extensive string manipulation inside Illustra and return the results. Most Web queries can now be handled by one or two monster SQL statements instead of by many small SQL statements.

The Future of Web/Database Interfaces

In this chapter, I have presented the fundamentals of building a Web gateway to a relational database engine, explaining the unique capabilities this combination makes possible and pointing out some of the limitations inherent in doing this kind of thing over the Web.

As you've seen, current Web technology has some serious limitations for database work-no client-side validation, no facility for extending the user interface toolkit provided by HTML, and no mechanism for the graceful handling of concurrent updates. Equally serious for multimedia database work-something for which the Web by its nature seems so well suited-are the limitations of conventional database technology, which supports only a limited set of data types. All other types of data must be stored as text or binary fields that can't be manipulated within the database itself.

On the other hand, object-oriented databases are extensible, but lack the data integrity and flexible querying features of relational databases. In my view, the new object-relational paradigm exemplified by Illustra has enormous promise because it addresses the limitations of relational and object-oriented databases. Illustra has a full SQL implementation and has facilities for defining and enforcing relational integrity constraints as with any relational database. It also can extend its built-in types in numerous and powerful ways.

In my view, object-oriented database technology-and especially object-relational database technology-may also be an excellent server-side counterpart to such client-side extensions as Java. For database work, client-side scripting promises three major advantages:

In my view, these two new technologies have the potential to revolutionize the Web-after the bugs are worked out and robust implementations are available for every common platform. And I don't say this because of all the media hype about Java-I say this because I'm all too familiar with the limitations of the current Web and database technologies and with the various kludges developers use to circumvent them. There's some real substance behind the media excitement; in the fullness of time we'll all find out whether the implementations of these new ideas live up to their promise.

SE USING CGI

Add To MyInformIT

< Back Contents Next >

Featured Author

Featured Book