Chapter 12: Interfacing a Relational Database to the Web
by Philip Greenspun, part of Database-backed Web Sites
Note: this chapter has been superseded
by its equivalent in the new edition
|
|
Chapter 12: Interfacing a Relational Database to the Webby Philip Greenspun, part of Database-backed Web Sites
Note: this chapter has been superseded
by its equivalent in the new edition
|
Remember the African Grey parrot we trained in the last chapter? The one holding down a $250,000 information systems management position saying "We're pro-actively leveraging our object-oriented client/server database to target customer service during reengineering"? The profound concept behind the "client/server" portion of this sentence is that the database server is a program that sits around waiting for another program, the database client to request a connection. Once the connection is established, the client sends SQL queries to the server, which inspects the physical database and returns the matching data. These days, all connections are made via TCP sockets even if the two programs are running on the same computer (see figures 12-1 and 12-2).
Figure 12-1: A basic relational database management system. The database client program sends SQL queries to the RDBMS server program. The RDBMS server program roots around among the data in its RAM cache and on the hard disk, then returns the requested data to the client program. The client and server programs are typically running on different physical computers and connecting over a network.
Figure 12-2: A classical RDBMS. The daemon program waits for requests from clients for connections. For each client, the daemon spawns a server program. All the servers and the daemon cache data from disk and communicate locking information via a large block of shared RAM (often as much as 256 MB). The raison d'etre of an RDBMS is that N clients can simultaneously access and update the tables. Here we could have an AOLserver database pool taking up six of the client positions, a programming using a shell-type tool such as SQL*Plus as another, an administrator using Microsoft Access as another, and a legacy CGI script as as the final client. The client processes could be running on three or four separate physical computers. The database server processes would all be running on one physical computer (this is why Macintoshes and non-NT Windows machines are not used as database servers).
For a properly engineered RDBMS-backed Web site, the RDBMS client is the Web server program, e.g., AOLServer (see Chapter 6 and/or http://www.aolserver.com/server). The user types something into a form on a Web client (e.g., Netscape Navigator) and that gets transmitted to the Web server which has an already-established connection to an RDBMS server (e.g., Oracle). The data then goes back from the RDBMS server to the RDBMS client, the Web server, which sends it back to the Web client (see Figure 12-3).
Figure 12-3: A high-performance RDBMS-backed Web site configuration. The Web server program itself is the database client. It opens a few connections to the database and keeps them open. When a web page is requested from a Web client (e.g., Netscape Navigator), the Web server program (e.g., AOLserver) finds a free database connection and uses that to look up the requested data.
Does that sound complicated and slow? Well, yes it is, but not as slow as the ancient method of building RDBMS-backed Web sites. In ancient times, people used CGI scripts. So the user would type "Submit" into his Web client (e.g., Netscape Navigator) causing the form to be transmitted to the Web server (e.g., NCSA 1.4). The Web server would fork off a CGI script. The CGI script would then ask for a connection to the RDBMS, often resulting in the RDBMS forking off a server process to handle the new request for connection. The new RDBMS server process would ask for a username and password and authenticate the CGI script as a user. Only then would the SQL transmission and results delivery commence. A lot of sites were still running this way in 1997 but either they weren't popular or they felt unresponsive (see Figure 12-4).
Figure 12-4: An old-style RDBMS-backed Web site. The Web client makes a request of the Web server. The Web server forks a CGI script to handle the request. The first thing the CGI program must do is open a connection to the relational database server. Modern computers are very fast, but think about how long it takes to start up a word processing program versus making a change to a document that is already open. Even with an enormous server computer, this kind of architecture results in a site that feels unresponsive to users. That said, the average Perl/CGI site is a lot faster and more reliable than the average site built with a special Web/RDMBS integration tool.
If you run a Web server that maintains a pool of already-open connections to one or more RDBMS systems and run your programs inside the Web server instead of as CGI scripts, then for each URL requested, you save: (1) the cost of the Unix fork for the CGI script, (2) the cost of the Unix fork for the Web server (though Oracle 7 and imitators pre-fork server processes), (3) the cost of establishing the connection to the database, including a TCP session and authentication (databases have their own accounts and passwords), (4) the cost of tearing all of this down when it is time to return data to the user.
As of March 1997, there were a handful of server vendors who'd realized this and produced more or less buggy implementations of the "hand out open RDBMS connections" code. See the product review section later in this chapter for specific product recommendations.
If you are building a richly interactive site and want the ultimate in user responsiveness, then Java is the way to go. You can write a Java applet that, after a painful and slow initial download, starts running inside the user's Web client. The applet can make its own TCP connection back to the RDBMS, thus completely bypassing the Web server program. The problems with this approach include the following:
Performance is critical to having a successful Web site, but a responsive server that is spitting out data inserted by your enemies isn't anything to email home about. So let's look at how Harry Hacker can get into your database.
If you are really lax about security, Harry could simply open a socket to the default RDBMS port number on your server and log in. Some database management systems allow you to have usernames with no passwords so Harry wouldn't even have to guess any password. You could prevent this attack by (1) changing the ports on which the database listens; (2) adding passwords; (3) moving the db server behind a firewall.
Changing ports from the defaults makes it harder for Harry to get in because he can't just try port 7599 and know that Sybase will probably be listening there. So he'll have to sweep up and down the port numbers until your server responds. Maybe he'll get bored and try someone else's site.
Because it is easy to guess certain usernames, e.g., "oracle" for Oracle or "miadmin" for Illustra, it is pretty important that nobody can connect to the database without a password as well. Unfortunately, these aren't super secure because they often must be stored as clear text in CGI scripts that connect to the database. Then anyone who gets a Unix username/password pair can read the CGI scripts and collect the database password. On a modern site where the Web server itself is the database client, the Web server configuration files will usually contain the database password in clear text. This is only one file so it is easy to give it meager permissions but anyone who can become root on your Unix box can certainly read it.
Moving the database server behind a firewall is a very good idea. This is mostly necessary because RDBMS's have such lame security notions. For example, it should be possible to tell the RDBMS "only accept requests for TCP connections from the following IP addresses...". Every Web server program since 1992 has been capable of this. However, I'm not aware of any RDBMS vendor who has figured this out. They talk the Internet talk, but they walk the Intranet walk.
Because Oracle, Informix, and Sybase forgot to add a few lines of code to their product, you'll be adding $10,000 to your budget and buying a firewall computer. This machine sits between the Internet and the database server. Assuming your Web server is outside the firewall, you program the firewall to "not let anyone make a TCP connection to port 7599 on the database server except 18.23.0.16 [your Web server]". This works great until someone compromises your Web server. Now they are root on the computer that the firewall has been programmed to let connect to the database server. So they can connect to the database.
Oops.
So you move your Web server inside the firewall too (see Figure 12-5). Then you program the firewall to allow nobody to connect to the database server for any reason. The only kind of TCP connection that will be allowed will be to port 80 on the Web server (that's the default port for HTTP). Now you're reasonably secure.
Figure 12-5: You don't want Harry Hacker connecting directly to your RDBMS. The vendors of relational databases don't allow you to restrict access based on IP address. So you need a firewall router (usually a standard Unix box running software; see the Trusted Information Systems site http://www.tis.com for explanations, free source code for a firewall toolkit, and packaged firewall products). The router selectively forwards packets from the Internet. You can instruct your firewall to reject everything except requests for connections to port 80 of a specific IP address where your Web server is listening. Then Harry Hacker's request for a connection to the port where your RDBMS is listening will be blocked.
Here's a typical AOLserver TCL API db-backed page. Tcl is a safe language and an incorrect Tcl program will not crash AOLserver or result in a denial of service to other users. This program reads through a table of email addresses and names and prints each one as a list item in an unnumbered list. If this is placed in a file named "view.tcl" then it may be referenced at a URL of the form http://www.greedy.com/view.tcl.
# send basic text/html headers back to the client
# Note that the AOLserver has already bound the Tcl
# local variable CONN to the connection for the
# user who requested this page.
ns_write $conn "HTTP/1.0 200 OK
MIME-Version: 1.0
Content-Type: text/html
"
# write the top of the page
# note that we just put static HTML in a Tcl string and then
# call the AOLserver API function ns_write
ns_write $conn "<title>Entire Mailing List</title>
<h2>Entire Mailing List</h2>
<hr>
<ul>
"
# get an open database from the AOLserver
# and set the ID of that connection to the local variable DB
set db [ns_db gethandle]
# open a database cursor bound to the local variable SELECTION
# we want to read all the columns (*) from the mailing_list table
set selection [ns_db select $db "select * from mailing_list
order by upper(email)"]
# loop through the cursor, calling ns_db getrow to bind
# the Tcl local variable SELECTION to a set of values for
# each row; it will return 0 when there are no more rows
# in the cursor
while { [ns_db getrow $db $selection] } {
# pull email and name out of SELECTION
set email [ns_set get $selection email]
set name [ns_set get $selection name]
ns_write $conn "<li><a href=\"mailto:$email\">$email</a> ($name)"
}
ns_write $conn "</ul>
<hr>
<address><a href=\"mailto:philg@mit.edu\">philg@mit.edu</a></address>
"Not exactly rocket science, was it? Still, there are some fine points here. One is that the program returns headers and the top portion of the page before asking AOLserver for a database connection or asking the database to do anything. This kind of construction ensures that users are staring at a blank Netscape window. A second fine point is that we wrap the email address in a MAILTO tag. Maybe nobody will ever actually want to send email from here, but at least nobody will say "what a bunch of losers who don't know how to use MAILTO tags". Third, a microfine point is that there is an advertised author for this page. I've put my email address at the bottom so if it isn't working, the user can tell me. Finally, even if there were a Tcl compiler, it wouldn't be able to check the correctness of this program. Tcl doesn't know anything about the database so it doesn't know if the mailing_list table exists or what the names of the columns are. So you won't find typos until you test the page or a user follows a branch you didn't test (ouch!). The plus side of this is that a Tcl program is free to construct SQL queries on the fly. Because Tcl makes no attempt to test program correctness before execution, you can easily write a Tcl program that, for example, takes a table_name argument from a user and then puts it into a query.
Let's try something similar in Oracle WebServer 2.0. We're going to use PL/SQL, Oracle's vaguely ADA-inspired procedural language that runs inside the server process. It is a safe language and an incorrect PL/SQL program will usually not compile. If an incorrect program gets past the compiler, it will not run wild and crash Oracle unless there is a bug in the Oracle implementation of PL/SQL (and I've tripped over a couple). This PL/SQL program is from a bulletin board system. It takes a message ID argument, fetches the corresponding message from a database table, and writes the content out in an HTML page. Note that in Oracle WebServer 2.0 there is a 1-1 mapping between PL/SQL function names and URLs. This one will be referenced via a truly ugly URL of the following form:
http://www.greedy.com/public/owa/bbd_fetch_msg
Nobody ever said Oracle was pretty...
-- this is a definition that we feed directly to the database
-- in an SQL*PLUS session. So the procedure definition is itself
-- an extended SQL statement. The first line says I'm a Web page
-- that takes one argument, V_MSG_ID, which is a variable length
-- character string
create or replace procedure bbd_fetch_msg ( v_msg_id IN varchar2 )
AS
-- here we must declare all the local variables
-- the first declaration reaches into the database and
-- makes the local variable bboard_record have
-- the same type as a row from the bboard table
bboard_record bboard%ROWTYPE;
days_since_posted integer;
age_string varchar2(100);
BEGIN
-- we grab all the information we're going to need
select * into bboard_record from bboard where msg_id = v_msg_id;
-- we call the Oracle function "sysdate" just as we could
-- in a regular SQL statement
days_since_posted := sysdate - bboard_record.posting_time;
-- here's something that you can't have in a declarative
-- language like SQL... an IF statement
IF days_since_posted = 0 THEN
age_string := 'today';
ELSIF days_since_posted = 1 THEN
age_string := 'yesterday';
ELSE
age_string := days_since_posted || ' days ago';
END IF;
-- this is the business end of the procedure. We
-- call the Oracle WebServer 2.0 API procedure htp.print
-- note that the argument is a big long string produced
-- by concatenating (using the "||" operator) static
-- strings and then information from bboard_record
htp.print('<html>
<head>
<title>' || bboard_record.one_line || '</title>
</head>
<body bgcolor=#ffffff text=#000000>
<h3>' || bboard_record.one_line || '</h3>
from ' || bboard_record.name || '
(<a href="mailto:' || bboard_record.email || '">'
|| bboard_record.email || '</a>)
<hr>
' || bboard_record.message || '
<hr>
(posted '|| age_string || ')
</body>
</html>');
END bbd_fetch_msg;The first thing to note in this program is that we choose "v_msg_id" instead of the obvious "msg_id" as the procedure argument. If we'd used "msg_id", then our database query would have been
select * into bboard_record from bboard where msg_id = msg_id;
which is pretty similar to
select * from bboard where msg_id = msg_id;
Does that look a little strange? It should. The WHERE clause is inoperative here because it is tautological. Every row in the table will have msg_id = msg_id so all the rows will be returned. This problem didn't arise in our AOLserver Tcl program because the Tcl was being read by the Tcl interpreter compiled into the AOLserver and the SQL was being interpreted by the RDBMS back-end. With a PL/SQL program, the whole thing is executing in the RDBMS.
Another thing to note is that it would be tough to rewrite this procedure to deal with a multiplicity of database tables. Suppose you decided to have a separate table for each discussion group. So you had photo_35mm_bboard and photo_medium_format_bboard tables. Then you'll just add a v_table_name argument to the procedure. But what about these local variable declarations?
bboard_record bboard%ROWTYPE;
This says "set up the local variable bboard_record so that it can hold a row from the bboard table". No problem. You just replace the static bboard with the new argument
bboard_record v_table_name%ROWTYPE;
and voila... you get a compiler error: "v_table_name could not be found in the database." All of the declarations have to be computable at compile time. The spirit of PL/SQL is that you give the compiler enough information at procedure definition time that you won't have any errors at run-time.
You're not in Kansas anymore and you're not Web scripting either. This is programming. You're declaring variables. You're using a compiler that will be all over you like a cheap suit if you type a variable name wrong. Any formally trained computer scientist should be in heaven. Well, yes and no. Strongly typed languages like ADA and Pascal result in more reliable code. This is great if you are building a complicated program like a fighter jet target tracking system. But when you're writing a Web page that more or less stands by itself, being forced to dot all the i's and cross all the t's can be an annoying hindrance. After all, since almost all the variables are going to be strings and you're just gluing them together, what's the point of declaring types?
I don't think it is worth getting religious over which is the better approach. For one thing, as discussed in the chapter "When is a Site Really a Database?" this is the easy part of building a relational database-backed Web site. You've developed your data model, defined your transactions, and designed your user interface. You're engaged in an almost mechanical translation process. For another, if you were running AOLserver with Oracle as the back-end database, you could have the best of both worlds by writing simple Tcl procedures that called PL/SQL functions. We would touch up the definition bbd_fetch_msg so that it was designed as a PL/SQL function, which can return a value, rather than a procedure, which is called for effect. Then instead of htp.print we'd simply RETURN the string. We could interface it to the Web with a five-line AOLserver Tcl function:
# grab the input and set it to Tcl local variable MSG_ID
set_form_variables
# get an open database connection from AOLserver
set db [ns_db gethandle]
# grab one row from the database. Note that we're using the
# Oracle dummy table DUAL because we're only interested in the
# function value and not in any information from actual tables.
set selection [ns_db 1row $db "select bbd_fetch_msg($msg_id) as moby_string from dual"]
# Since we told the ns_db API call that we only expected
# one row back, it put the row directly into the SELECTION
# variable and we don't have to call ns_db getrow
set moby_string [ns_set get selection moby_string]
# we call the AOLserver API call ns_return to
# say "status code 200; MIME type is text/html"
# and then send out the page
ns_return $conn 200 text/html $moby_stringAs I write this, the America OnLine folks who bought NaviSoft are still waiting to see if this Oracle fad catches on. Thus, they've not written an Oracle driver for AOLserver. So this combination of AOLserver TCL API and PL/SQL would only work on a Windows NT box where the ODBC standard (see below) lets AOLserver and Oracle talk to each other. Still, you could use the same approach any time you have a database server with a good procedural language (rumor has it that Oracle 8 will let you run Java inside the server in addition to PL/SQL) and a Web server with the basic ability to serve the result of an SQL query as the entire page.
I don't think I made enough enemies in Chapter 10 by saying that commercial Web/RDBMS integration products in general are inferior to thrown-together public-domain hacks. So now I'll review some specific products.
Oracle has a rich history of claiming that they understand the Web better than any other company. They took one look at HTML and Netscape Navigator and said "this stuff is no good for database applications; we'll build our own standard and our own Oracle PowerBrowser." As a sop to people who clung pathetically to the idea that HTML was going to catch on, Oracle distributed a "Web toolkit" of public domain server-side software, including Kevin Stock's Oraperl (see Chapter 10). Kevin hadn't touched the code since the old Oracle 6 days so my friend Eric was really excited to download the toolkit from the Oracle site and start using it with Oracle 7, the then-current release of the RDBMS server.
Eric couldn't get Oraperl to work with Oracle 7 so he called Oracle support.
"We don't support the Web toolkit. It is just a free add-on."
Had they paid Kevin Stock or anyone else to spend a few days making Oraperl work with the latest version of Oracle?
"No. We just collected the stuff in the toolkit from other Web sites and make it available for download in one file."
I suppose that part of the reason Oracle didn't want to buy a few days of Kevin's time is that they were shortly to come out with their own Web server: Oracle WebServer. Their PR staff worked tirelessly to convince customers to switch from all these unsupported public-domain packages and enjoy the total quality solution of Oracle software engineering and Oracle support from RDBMS to Web server to Web browser. However, the word on the street about WebServer 1.0 was so bad that I didn't bother to try it. Neither apparently did Oracle Corporation itself:
philg-sub-47> telnet www.oracle.com 80
Trying...
Connected to www-2.us.oracle.com.
Escape character is '^]'.
HEAD / HTTP/1.0
HTTP/1.0 200 Document follows
Date: Wed, 06 Mar 1996 22:37:51 GMT
Server: NCSA/1.5
Content-type: text/htmlNote: This is the standard way to find out what Web server a site is running. you TELNET from a shell to the hostname, in this case "www.oracle.com", port 80. Then you type in a request line like "GET / HTTP/1.0<return><return>". The "Server:" header in this response tells us that Oracle was using the NCSA 1.5 server as of March 6, 1996 (more than six months after introducing a competitive product). NCSA 1.5 was distributed with source code and hence I'm kind of surprised that Oracle didn't at least remove the line of C code that writes this embarrassing header.
By the time some friends of mine were sitting down to rebuild http://www.comdex.com (to handle on-line registration for the big COMDEX trade show), Oracle was touting WebServer 2.0, "even more perfect than WebServer 1.0." We decided to give it a try.
Installation and configuration took days and would not have been a success without assistance from some of my friends on the Oracle Web development. Once installed, WebServer 2.0 demands a relatively painful development cycle. Each dynamic Web page is a single PL/SQL procedure. You edit the foobar.sql file in Emacs then have to cut and paste the definition into an SQL*Plus session to define the procedure foobar in the database. Then and only then does the dynamic Web page become accessible at http://www.greedy.com/public/owa/foobar. If you are accustomed to AOLserver Tcl or Perl/CGI then you often find yourself fixing a PL/SQL bug in Emacs and then wondering why the on-line page is still broken. You usually only waste a minute or two before realizing that you need to feed Oracle the new procedure definitions.
Once we got used to the software development cycle, things proceeded moderately smoothly until we hit two interlocking WebServer 2.0 shortcomings: lack of flexibility and lack of support. Lack of flexibility meant that there was only one way to issue an HTTP 302 redirect. Lack of support meant that when we discovered a server-crashing bug in this one facility, it took six months for Oracle to deliver a fix.
Lack of flexibility also made it impossible to deal with problems like the "inadvertent hiding from search engines" that I discussed in Chapter 5. With AOLserver, I can register an arbitrary URL or families of URLs to a Tcl procedure. That's not possible in Oracle WebServer and you never know when you might need it.
Oracle's design philosophy for the API is questionable. For example, a PL/SQL procedure that sits behind an HTML form is supposed to have one argument for each INPUT name. Checkbox values come through as an array. However, they didn't think carefully about what happens if a user didn't check any of the boxes. In that case, the PL/SQL procedure aborts with an error because it didn't get enough arguments. We discovered this about 2 am one night and worked around it by putting
<INPUT TYPE=HIDDEN NAME=FOOBAR VALUE=ORACLESUCKS>
whenever there were checkbox inputs named "FOOBAR" in one of our forms. This struck us as repulsive so we asked Oracle what the correct way to do this was. After a few weeks, we got our answer: Put in a hidden variable with the same name as the checkboxes and a value of "NOBOXCHECKED". They didn't even acknowledge that this was a less than elegant implementation.
Even where the API is elegant, it is not complete. You cannot do the things that you need to do, such as send email or grab a Web page from another site. you can crash the server pretty easily from PL/SQL. Where the API is complete, it is not robust. I wrote out a couple of set-cookie headers with the API and then forgot to call the "close header" function. I should have ended up with one broken page and/or an error messgae. Instead, I got a crashed server and denial of service to all users.
For all of Oracle's talk about how great an RDBMS is, WebServer 2.0 doesn't make too much use of one. It logs into the file system, not into the database (Microsoft Internet Explorer lets you choose which you prefer). HTTP authentication usernames and passwords are not stored in database tables that you can extend, but in custom-formatted Unix files that you can't touch or query (AOLserver also uses a Unix file but at least they give you API hooks to add users, check passwords, etc.).
We were never happy with the performance we got from Oracle WebServer 2.0/Oracle7.3. Even in development on an idle workstation, our site was much less responsive than production AOLserver/Illustra-backed sites on an overloaded Unix box with a load average of 3. Oracle is a faster RDBMS than Illustra hence we laid the blame at WebServer 2.0's door. In fact, I think that my old CGI-based sites running Oraperl scripts and Oracle6 on a discarded antediluvian SPARC were significantly faster.
Can I say anything good about WebServer 2.0? Yes. If a PL/SQL procedure was accepted without errors by the Oracle compiler, almost always the Web page would run without errors. It is easier to have confidence in the correctness of a collection of PL/SQL programs than it is to have confidence in a collection of purely interpreted Tcl scripts. With the Tcl script, the interpreter won't even look at the half of a conditional that isn't relevant. A glaring syntax error can go unnoticed for months unless you are very rigorous about testing.
The bottom line? Oracle WebServer is a reasonably good product, but I wouldn't use it again. Larry Ellison spends a lot of time taunting Bill Gates and Microsoft for their ignorance of all things Internet. I could take these taunts more seriously if it weren't possible to download about 50 files from www.microsoft.com (IIS-backed) in the time that it takes to grab the first page from www.oracle.com (Oracle WebServer 2.1-backed as of March 1997).
My general rule "don't depend on any Web software from a company that hasn't figured out how to put its documentation on-line" saved me from personally flopping with the Web DataBlade, an idea that Informix acquired when it bought Illustra in early 1996. My first in-depth exposure to it came when I was asked to analyze the utter failure of a db-backed Web site built by expensive New York consultants using the Web Blade. Let's call it http://www.deadlocked.com.
The Web Blade is yet another server-side extension to HTML. If you read Chapter 9 then you know that I think this is the future of server-side programming. However, the Web Blade isn't exactly what I had in mind. Rather than a full-featured programming language with HTML syntax a la Meta-HTML (http://www.mhtml.com), Informix delivers an ad-hoc design that lets you embed SQL queries in HTML. You store these templates in a database table. In response to user queries, the WebDaemon will pull the relevant piece of extended HTML out of the database, parse it, do the SQL queries spec'd by your HTML, and then return standard HTML to the requesting client.
The first part of the Web Blade that you'll see as a developer is the easiest part to throw away: the "application builder." This lets you edit your Web Blade templates in via HTML forms. The template itself occupies a TEXTAREA in your Netscape Navigator.
If you spent the last 20 years learning Emacs then you'll find that Netscape Navigator is a very poor substitute as a text editor. The Netscape "Find" command does not even search through text in a TEXTAREA. So if you want to search for a part of a complicated page, you have to do it with your eyes only.
If you just throw out the application builder, then you end up with a development cycle similar to that of Oracle WebServer. You edit your templates in Emacs, being careful to escape all the single quotes. Then you feed the template to Illustra in an MSQL session. Then you go to your Web browser to see how it worked. Not as convenient as AOLserver Tcl or CGI, but not unusable and you can still use all of your familiar Unix software development utilities.
Assuming you are able to make peace with the Web Blade's development cycle, you are still stuck with its baroque syntax. Here's an example of grabbing the email address and subject line for a particular message from a database table of bboard messages:
<?MISQL SQL = "SELECT email, subject FROM bboard where msg_id = 37;">
$2 (from $1)
<?/MISQL>The business end of this code is the $2 (from $1). This is horrible data abstraction. It might not look that bad now, but what if you had a complicated query and then produced 30 lines of HTML. Do you really want to see $13 on line 28 and try to figure out which column it is? In Oraperl, AOLserver Tcl, or PL/SQL for Oracle WebServer 2.0, you'd instead be able to say $subject (from $email).
Conditionals and control structure are even worse. The heart and soul of this thing is declarative but a lot of times you need to do procedural stuff. Feast your eyes on this..
cond=$(OR,$(NXST,$email),$(NXST,$name),$(NXST,$subject))
I never expected to be nostalgic for Tcl or Perl . . .
You'd think that the whole point of a tool like the Web DataBlade would be that you could say "make sure this input is an integer not null and, if not, give the user a nice error message." But you can't. You have to put in all these gnarly conditionals yourself and write your own error messages. It isn't any worse than using raw Perl but the authors of Perl never made any claim that theirs was a Web/db integration tool.
Another thing you'd expect from a Web/RDBMS tool is good database integration. As documented above, Oracle WebServer has its share of shortcomings, but at least the form variables come to you the programmer as PL/SQL variables. They can contain any ASCII and you can insert them without further worry. Oracle understands RDBMS. Perl and Tcl don't so you have to manually double the apostrophes (the SQL quoting character) before attempting a string insert, i.e., change 'don't' to 'don''t'. For AOLserver, I wrote a magic little Tcl function that lets me say "$Qqsubject" when I want the double quoted version of the "subject" form variable. The Web DataBlade isn't as powerful a language as Tcl and it doesn't understand RDBMS like PL/SQL so you have to do this:
<?mivar delimit="'" replace="''" name=subject>$subject<?/mivar>
for every variable that you insert into the database. Ugh!
[Note: you can define a function call to do this a little more cleanly but it would still be much more painful than my Tcl hack and neither solution is as good as Oracle's.]
Having noted the glacial performance of other Web Blade-backed sites (including http://www.informix.com), I was prepared for my client's site to be sluggish. Informix claims that the software will work plugged into the Netscape Enterprise Server's API. However, customers report that their systems become unstable when they try this configuration. Some of the product folks at Informix swore that the Web Blade is reliable when used with NSAPI but I noticed that they are using it in CGI-mode themselves:
http://www.informix.com/infmx-cgi/Webdriver?MIval=products_and_technology
(I cut and pasted this URL on March 9, 1997; they were running the Enterprise 2.01 server at the time).
The overhead of CGI should only have degraded responsiveness by a factor of 10. However, http://www.deadlocked.com was at least a factor of 100 slower than expected. In fact, it was only able to serve one user at a time.
I discovered that Informix designed each Web Blade page to execute as a single SQL transaction. So if you SELECT FROM a table at the top of the page and then try to UPDATE or INSERT into the same table lower down, you will deadlock. That's because the SELECT grabs a read lock on the table. Another copy of the same page can be running simultaneously. It also grabs a read lock on the same table. Then the UPDATE or INSERT tries to get the write lock, which only one RDBMS connection can have at once. It waits for all read locks to be freed up. The simultaneously running copy, holding one of those read locks, is also waiting for the write lock. So two copies of the same page will wait for each other forever (or until the deadlock timer kills them both).
This isn't a problem in Oraperl or AOLserver Tcl. You probably don't need the whole page to run as a transaction and if you don't do anything special then the SELECT up top and the UPDATE down below are separate. They aren't part of the same transaction block and hence will get serialized just fine with the SELECTs and UPDATEs from simultaneously running pages. That was the whole point of using an RDBMS!
The expensive New York Web site developers apparently didn't like the Web Blade's limited procedural language facilities. So when they wanted to check an input against a regular expression, they just EXEC'd a Perl script. Yes, that means that the Unix box has to fork right in the middle of a database transaction. Worse yet, they often forked off a Perl script that then needs to look up some information in the database. They could have installed the Informix/Illustra equivalent of Oraperl and read from the database directly. Instead they had the Perl script fork MSQL, the Illustra shell-level tool. The Perl script would fork MSQL, collect the human-readable output, REGEXP like crazy to pull the data out into Perl variables, and then perhaps fork MSQL a few more times!
All of this while holding down table locks in the middle of a transaction.
I installed AOLserver and wrote a Tcl script to pull the Web Blade templates out of the RDBMS and stick them into ordinary Unix files. Now they were accessible to grep, Perl, and Emacs. I then wrote a Perl script to batch convert these files into pidgin AOLserver Tcl API URLs. I could have done this in my original AOLserver Tcl script but it turns out that Perl is a much better tool for the job than Tcl. Why? Perl offers the option of non-greedy REGEXP matching. This means that you can easily match "from to the first ". If you use {<FOO>(.*)</FOO>} as a Tcl REGEXP pattern then the "(.*)" matches the entire file between the very first "<FOO>" to the very last "</FOO>". You get the same bad behavior in Perl unless you remember to use the magic "*?" operator: <FOO>(.*?)</FOO>.
If you aren't afraid of your friends' scorn, pick up a copy of Mastering Regular Expressions (Friedl; O'Reilly 1997). I wouldn't call it great literature, but it covers Tcl, GNU Emacs, Python, and (mostly) Perl. It is a lot better than the man pages and the books on the individual languages.
I was then able to touch up the Perl output in Emacs at the rate of about 20 db-backed URLs/day. End result? The site was about 10 times more responsive at times of light load and was no longer plagued with deadlocks. The site went from being able to perform no more than 1 query at a time to being able to easily handle 10 or more queries/second.
[Note: If you have a suffering Web Blade site and would like a copy of my Tcl and Perl scripts to assist in a conversion to AOLserver, just send me some email: .]
Since the Web Blade is just an extended HTML, probably the cleanest way to port a Web Blade site to something reasonable would be to sit down for a few days with Meta-HTML (http://www.mhtml.com) and write a bunch of Meta-HTML tag definitions to correspond to the Informix tags. I didn't do this because I'm much more experienced with AOLserver and it was therefore a more conservative path to getting www.deadlocked.com back up and running.
The Web Blade is the worst-conceived worst-performing piece of Web/RDBMS integration software that I have used. I would much rather go back to 1994, Oraperl, and Oracle 6 than use this product, which has serious deficiencies in
Informix Universal Server promises to be a very nice RDBMS, but just say no to this way of connecting your Web site to it.
I covered LiveWire fairly thoroughly in Chapter 6. All of the deficiencies I noted there apply here. I should add that my repeated attempts to get LiveWire to connect to Oracle ended in failure. I had to have all kinds of Oracle software installed on both the client and server computers. It had to be exactly the right version. I eventually ran out of Oracle CD-ROMs and patience.
Since I couldn't get LiveWire to work, it seems only fair to give a real user the last word:
Date: Fri, 28 Feb 1997 20:29:13 -0500 From: Dave Mitchell <davem@magnet.com> Organization: Magnet Interactive Communications I just finished (I hope) a largish commerce project in livewire (solaris/informix) and it was HELL! Livewire blows goats! I'm still spending every day fixing yesterday's little bugs and todays big bugs and creating tomorrows little bugs that expose yesterdays bigger bugs! . . . In any case, the problems we had were with bizarre variable scoping problems (remove var statement to fix problem... sometimes), such as building an array of objects, only to find that when you're done, you've got an array of .... something. Strange things also happen if you redeclare a variable that already exists - such as "var" in a loop. Usually it just crashes when it gets there, but sometimes it just works and things appear to be fine, but the values don't get updated properly? I only wish I could provide test code to demonstrate the problems - but I can't!! Every test function I ever wrote works fine. Or how about this one - how can I find out what kind of object I have? how about what methods I can call on it? If you try to call a method on an object it doesn't have the app just crashes!
As I noted in Chapter 9, I think the future of server-side programming is semantically enhanced HTML and the best current example is Meta-HTML (http://www.mhtml.com). The "PowerStrip" version of Meta-HTML is compiled with the OpenLink ODBC libraries for transparent access to just about any RDBMS. Many of my friends from MIT have built high-traffic database-backed sites with Meta-HTML so I'm pretty sure that it works. Meta-HTML also has a lot of high level Web programming features like session variables. However, I haven't done too much myself with it because I've got so much experience and source code for AOLserver that it is more efficient for me to continue using the tools that I know.
A lot of big rich companies use WebObjects (http://www.next.com) for their dynamic sites. Most of the WebObjects-backed site I've come across have been unacceptably slow. WebObjects definitely provides for RDBMS connectivity though when you add the sluggishness of the average RDBMS to the apparently sluggishness of WebObjects, it is tough to imagine the final result being good for much besides corporate vanity.
The most common method of building an RDBMS-backed page with Internet Information Server (http://www.microsoft.com/iis) is through "Active Server Pages". You are talking to one of these any time you see a URL that ends in .asp. ASPs work with just about any scripting language or standard programming language and let you define and maintain session variables. There is specific support for JavaScript, Visual Basic, and connections through to ODBC databases. ASPs run inside the Web server process and hence avoid the forking overhead of CGI. NT Server 4.0 does at least once very clever thing which is to notice when an ASP page has changed in the file system and arrange with the running IIS to deliver the new version. The development cycle problems I noted with LiveWire have not been lost on Microsoft:
"How does Active Server Pages compare to Netscape LiveWire?
"Netscape LiveWire requires the use of JavaScript, while Active Server Pages supports the use of virtually an scripting language, with native support for VBScript and Jscript. Active Server Pages supports components written in any language while LiveWire supports only Java components.
"LiveWire applications must be manually compiled after each change, and then the application stopped and restarted. Active Server Pages recognizes when an ASP file changes, and automatically recompiles the application at the next request."
I don't have enough experience with Windows NT to evaluate IIS and this whole way of doing server-side programming. Generally every Microsoft product I've ever used has sounded much better on paper than it has worked in practice. However, I suppose that if you've already sold your soul to Microsoft then IIS + ASP + Visual Basic probably isn't a bad way to go.
I haven't kept up with all the Web "technology" being pushed by various vendors and every document of this nature is necessarily out of date. So I wouldn't be surprised if the latest tantalizingly hyped product isn't mentioned above. Try to keep in mind the caveats I laid down in Chapter 10. No junkware/middleware/Webware product can do your thinking for you. Once you've done the thinking, it isn't too hard to do the programming with standard and simple tools. Also try to keep in mind the caveats I laid down in Chapter 6 against commercial products for which source code is not distributed. You do not want to put yourself at a vendor's mercy, especially a vendor who has never built a high-volume Web site for itself.
I saved my personal choice for last. I use AOLserver, which has been tested in thousands of production sites since May 1995. I have personally used AOLserver for about a dozen heavily used RDBMS-backed sites (together they are responding to about 100 requests/second as I'm writing this sentence). AOLserver provides the following mechanisms for generating dynamic pages:
The last four mechanisms provide access to the AOLserver's continuously connected pools of connections to relational databases and thus provide users with the fastest possible Web service.
Choosing among the four is relatively easy. The C API is out because you run the risk that an error in your little program will crash the entire AOLserver. Tcl and Java are slower than compiled C, but these inefficiencies are irrelevant for most RDBMS-backed Web applications. Delays in processing by the RDBMS and delays in transmitting data over the Internet will swamp any delays caused by interpreters. I don't use the Java API because it hasn't been written yet! That leaves two varieties of Tcl. The language and the API are exactly the same but the software development cycle is different. I use the "sourced at startup" Tcl for applications like my comment server where I want db-backed URLs such as /com/philg/foobar.html ("philg" and "foobar.html" are actually arguments to a procedure). I use the "*.tcl" variety for most applications because a change to a file is reflected the next time a URL such as /bboard/fetch-msg.tcl is loaded.
The first shortcoming of AOLserver is that it doesn't provide a lot of help for things like session variables. Meta-HTML, LiveWire, and a lot of middleware/junkware systems are more programmer-friendly if you want to do things like build up shopping carts. This isn't a serious shortcoming for me because I want to keep all of my state in the relational database anyway. The code to set and read magic cookies is only a few lines of Tcl.
The second shortcoming of AOLserver is that it is mostly being developed for internal AOL consumption. That means they aren't planning to develop any new versions for Windows NT. They aren't going to renew their contract with Informix that lets them distribute a free RDBMS from their Web site (go to http://www.aolserver.com/ before June 1st if you want to grab a copy of Illustra 3.2). They are publicly committed to continuing distribution of Unix releases (at least SGI Irix, HP/UX, Digital Unix, Sun Solaris, and Linux) and the program sits behind heavily accessed AOL sites, but they are no longer making a serious effort to compete in the general-purpose server market and aren't giving away the source code so that anyone else can take up the torch.
Despite these shortcomings, as of March 1997, I think AOLserver is the best overall development environment.
Note: I maintain an entire server with example applications in AOLserver Tcl, complete with source code: http://demo.webho.com
Picking the best choice from the above-mentioned products isn't easy. Whatever your choice, be sure that it will probably be wrong a year from now. There will be a superior product on the market. However superior this new product is, be sure that it will probably be a mistake to switch. Once you build up a library of software and expertise in Tool X, you're better off ignoring Tools Y and Z for a year or so. You know all of the bugs and pitfalls of Tool X. All you know about Tool Y and Z is how great the hype sounds. Being out of date is unfashionable, but so is having a down Web server, which is what most of the leading edgers will have.
See Table 12-1 for a summary comparison of the technologies mentioned above.
Table 12-1: Comparison of technologies for building RDBMS-backed Web sites. "Software Development" looks at the life cycle of writing dynamic Web pages. The best tools for loosely spec'd constantly changing programs like Web sites require you only to edit a file. The worst tools require you to edit a file, recompile an application, and restart the Web server program. "Safety" considers how likely your programming error is to trash Web services for everyone else. A high quality API for experienced programmers is one that is very flexible with lots of powerful building blocks. A good programmer can always make an impressive application if it is possible to write bytes directly to the connection, send e-mail out of the server, grab a Web page from another site, and define new functions and data structures. Good programmers can build their own infrastructure quickly. Novice programmers need a different kind of API, though. For them it is best if the API advertises mechanisms for session variables, for example. That way they don't have to learn about the magic cookie protocol and other ways to figure out if a series of requests have been made by the same user.
An RDBMS-backed Web site is updated by thousands of users "out there" and a handful of people "back here". The users "out there" participate in a small number of structured transactions, for each of which it is practical to write a series of Web forms. The people "back here" have less predictable requirements. They might need to fix a typo in a magazine title stored in the database, for example, or delete a bunch of half-completed records because your forms processing code wasn't as good about checking for errors as it should have been.
Every RDBMS-backed Web site should have a set of admin pages. These provide convenient access to the webmasters when they want to do things like purge stale threads from discussion groups. But unless you are clairvoyant and can anticipate webmaster needs two years from now or you want to spend the rest of your life writing admin pages that only a couple of co-workers will see, it is probably worth coming up with a way for webmasters to maintain the data in the RDBMS without your help.
Some RDBMS/Web tools provide fairly general access to the database right from a Web browser. Both Oracle WebServer 2.0 and AOLServer, for example, provide some clunky tools that let the webmaster browse and edit tables. These won't work, though, if you need to do JOINs to see your data, e.g., if your data model holds user email addresses and user phone numbers in separate tables. Also, if you would like the webmasters to do things in structured ways, involving updates to several tables, then these kinds of standardized tools won't work.
Fortunately, the RDBMS predates the Web. There are literally thousands of tools for Macintosh and Windows machines that purport to save you from the horror of typing SQL into a shell-like client. Some of them will come packaged by default with your RDBMS. These fall into two broad classes.
The first class of programs make it easy to build forms by example. These forms are then intended to be used by, say, telephone sales operators typing at Windows boxes. If you're going to go to the trouble of installing a Web server and Web browsers on all the user machines, you'd probably just want to make HTML forms and server-side scripts to process them. Some of the people who sell these "easy forms for SQL" programs have realized this as well and provide an option to "save as a bunch of HTML and Web scripts."
The second class of programs makes the data in the database look like a spreadsheet. This really isn't all that difficult because, as discussed in the previous chapter, a relational database really is just a collection of spreadsheet tables. This is the kind of program that can make it very convenient to make small unanticipated changes.
Both kinds of programs connect directly to an RDBMS from a PC or a Macintosh. This probably means that you'll need additional user licenses for your RDBMS, one for each programmer or web content maintainer. If you are using software supplied by, say, Oracle then it will have been compiled with the Oracle C library and will connect directly to Oracle. It also won't work with any other brand of database management system. There are a lot of third-party software packages that will also talk to Oracle in this way. You install these programs on all of your computers, write a bunch of macros to make things more convenient for data entry, and pretty soon you've got a nice convenient system. Then you decide that you'd like the same operators to be able to work with another division's relational database. So you type their server's IP address and port numbers into your configuration files and try to connect. Oops. It seems that the other division has chosen Sybase. It would cost you hundreds of thousands of dollars to port your macros over to some product that was compiled with the Sybase C library.
Now you're upset. From your perspective, Oracle and Sybase are interchangeable. You put your SQL in; you get your data out. Why should you have to care about differences in their C libraries? Well, Microsoft had the same thought years ago and came up with an abstraction barrier between application code and databases called "ODBC". Well-defined abstraction barriers have been the most powerful means of controlling software complexity ever since the 1950s. An abstraction barrier isolates different levels and portions of a software system. In a programming language like Lisp, you don't have to know how lists are represented. The language itself presents an abstraction barrier of public functions for creating lists and then extracting their elements. The details are hidden and the language implementors are therefore free to change the implementation in future releases because there was no way for you to depend on the details. Very badly engineered products, like DOS or Windows, have poorly defined abstraction barriers. That means that almost every useful application program written for DOS or Windows will depend intimately on the details of those operating systems. It means more work for Microsoft programmers because they can't clean up the guts of the system, but paradoxically a monopoly software supplier can make more money if their products are badly engineered in this way. If every program a user has bought requires specific internal structures in your operating system, there isn't too much danger that the user will be able to switch operating systems.
Relational databases per se were engineered by IBM with a wonderful abstraction barrier: Structured Query Language (SQL). The whole raison d'etre of SQL is that application programs shouldn't have to be aware of how the database management system is laying out records. In some ways, IBM did a great job. Just ask Oracle. They were able to take away most of the market from IBM. Even when Oracle was tiny, their customers knew that they could safely invest in developing SQL applications. After all, if Oracle tanked or the product didn't work, they could just switch over to an RDBMS from IBM.
Unfortunately, IBM didn't quite finish the job. They didn't say whether or not the database had to have a client/server architecture and run across a network. They didn't say exactly what sequence of bytes would constitute a request for a new connection. They didn't say how the bytes of the SQL should be shipped across or the data shipped backed. They didn't say how a client would note that it only expected to receive one row back or how a client would say "I don't want to read any more rows from that last select." So the various vendors developed their own ways of doing these things and wrote libraries of functions that applications programmers could call when they wanted their COBOL, Fortran, or C program to access the database.
It fell to Microsoft to lay down a standard abstraction barrier in January 1993: Open Database Connectivity (ODBC). Then companies like Intersolv (http://www.intersolv.com) ( released ODBC drivers. These are programs that run on the same computer as the would-be database client, usually a PC. When the telephone operator's forms system wants to get some data, it doesn't connect directly to Oracle. Instead, it calls the ODBC driver which makes the Oracle connection. In theory, switching over to Sybase is as easy as installing the ODBC driver for Sybase. Client programs have two options for issuing SQL through ODBC. If the client program uses "ODBC SQL" then ODBC will abstract away the minor but annoying differences in SQL syntax that have crept into various products. If the client program wants to use a special feature of a particular RDBMS, e.g., Oracle Context, then it can ask ODBC to pass the SQL directly to the database management system. Finally, ODBC supposedly allows access even to primitive flat-file databases like FoxPro and dBASE.
You'd expect programs like Microsoft Access to be able to talk via ODBC to various and sundry databases. However, this flexibility has become so important that even vendors like Oracle and Informix have started to incorporate ODBC interfaces in their fancy client programs. Thus you can use an Oracle-brand client program to connect to an Informix or Sybase RDBMS server.
The point here is not that you need to rush out to set up all the database client development tools that they use at Citibank. Just keep in mind that your Web server doesn't have to be your only RDBMS client.
Here's what you might have learned from reading this chapter:
In the next chapter, we'll look at the design decisions that went into a few sites.
Note: If you like this book you can move on to Chapter 13.
I've used Oracle Web Server, Netscape Livewire and Microsoft Active Server Pages extensively. I'd have to say that my favorite of the three is Oracle Web Server.I used Oracle WOW, which was the shareware version of Web Server--before Oracle wised up and realized there was money to be made, for over a year. We connected to Netscape web servers. I was with Booz Allen, a consulting firm, where my team built a "Knowledge System" for collecting and distributing intellectual capital all over the world. This huge system runs from redundant Sparc 1000s (Web Servers) and Alpha 2100s(RDBMS).
From a programmers perspective, It is a great product because it gives you--the programmer--total control. Unfortunately, this made it difficult to involve our graphics designer (a non-programmer) in the creation of the system.
Livewire and ASP, which I am currently working with, are far less complicated, and allow you to involve non-programmers to a greater degree. I would go with ASP simply because fighting Microsoft has always been a futile effort.
Now, I prefer working in UNIX, but Microsoft's "Internet Studio" is a very user-friendly environment for building web-RDBMS systems.
-- James Tarquin, May 13, 1997
You write: Well, Microsoft had the same thought years ago and came up with an abstraction barrier between application code and databases called ODBC".In fairness, ODBC is largely derived from the work of the X/Open and SQL Access Group. MS took their specification, changed its name, made minor technical changes and promoted it as an open standard mainly to to give itself an advantage over other established dbms vendors. I think MS deserves more credit for opening up the dbms tools market than for being technically innovative.
-- Cimarron Taylor, May 26, 1997
i'm a microsoft employee (hey, that tomatoe hurt!) and would like to admit that up front. a friend of mine said that phil's book disparaged MS. I'd say the review on this page didn't do that; it simply plead ignorance of MS's IIS/ASP package. after reading phil's issues with the other packages, i'd venture to say that phil would like the MS IIS/ASP solution the best. it scales: the web-server is multi-threaded, and pages are cached after being read from disk. it uses easy to understand scripting languages (JScript, VBScript, etc). scripts can contain logic, looping, variable declaration, function declaration, and branching. database access is trivial, and any data source--rdbms, mainframes, object databases, directories, email systems...--can be accessed with the built-in database objects. pages can be "transacted": all work done on one page is part of a "transaction": committed or aborted in one action. and it uses COM, so a script can access any COM object available. Among other purposes, COM objects have been developed for file system access, SMTP sendmail, shopping packages, email access, etc.i can't give my name because i might get in trouble. will check this with my lawyer. however, i honestly believe IIS is the best web server out there. check it out.
-- anonymous anonymous, January 20, 1998
NetDynamicsYou say down with middleware. However ODBC is quite useful middleware. We are using NetDynamics middleware. We are building Databases backed web sites with NetDynamics. It does all that you ask for in terms of caching database connections and not requiring a new process each hit. We do have some performance and bug issues, it chews up heaps of resources, is expensive, there is quite a learning curve and you have to understand quite a bit about how the web works to get the most from it. Many other competing application servers exist with these features (Sybase has one). The Java servlet protocol also helps here.
-- Brendan Johnston, March 2, 1998
On the subject of RDBMS security, the free PostgreSQL (from about version 6.0 on) allows restricting TCP connections to particular IP addresses. In fact, PgSQL allows much more than that; check out http://www.postgresql.org for more info.
-- Lamar Owen, April 6, 1998
I've been working with NT 4 for about 2 years now. At work, my main directory server is a Micron Netframe, and can say, to my amazement, that the server only needs rebooting every 60 to 90 days. (BIG recomendation of Micron for anyone using NT. Detected every bit of hardware I've thrown at them, and even the common MS crashes aren't that fatal.) It carries 8 to 9 gig of various stuff for only about 40 employees, but it stays up a lot longer that any workstation does. While I'm not near a Microsoft fan, I really like using ASP. It would have to be one of the easiest ways to access a db i've seen. We haven't used SQL Server yet, as we are still accessing MS Access files, but the response times are quick. I know that 40 people isn't near the volume of any low-end website, but big scale performance isn't a factor for us at this point. On the usability side, ASP is the way to go for me.
-- Gary Dickerson, September 6, 1998
Speaking of $10K spending on firewall: save that money for Accura NSX down payment by using IPfilter package from http://cheops.anu.edu.au/~avalon/ which would allow you to restrict connections to your database server, web server, etc. It comes as part of FreeBSD, and can be used on other Unix systems.
-- Dmitry Kohmanyuk, July 17, 1999