A recent post in the comp.infosystems.authoring.html.tools got me thinking:
database support has been the big thing in W3 publishing for quite a while, be
it in offline solutions like Microsloth's Access/Frontpage or the
quasi-standard "LAMP"
configuration—now that somebody was looking for a cheap, possibly
OpenSource, offline content management system, couldn't this be done in
HSC?
After a little over an hour's hacking it could :-) Well, it's still
just a database interface and quite some way from a real CMS, but once you have your data in an SQL
database, it's an easy way to publish it on the web.
Admittedly, this is not so much an HSC solution as a Perl one, and it's due
to Perl's great DBI.pm database interface module that this works
with almost any SQL database in the known universe, but anyway: it lets you
publish your databases with HSC, and that's why I present it here. You should
have read the Perl article if
you're not fluent in HSC.
Scripting languages are obviously the tool of choice for querying a database
from HSC, for anything else would mean lots of additional code in HSC that
doesn't have much to do with HTML processing and that would be necessarily more
or less specific to a certain make of DBMS1. Especially Perl is very well
equipped for the job, because with DBI.pm there is a module
available that provides a uniform API to loads of different DBMS. It doesn't
hide differences in SQL syntax that exist between various systems, but that's
not our concern here anyway, as the goal is to provide a means of writing your
own SQL queries in HSC and retrieving the result as HTML.
A minimal query using DBI.pm looks similar to this:
1 use DBI;
2 my $dbh = DBI->connect("DBI:mysql:test","joe","joepw") ||
3 die "Error connecting to database";
4 my $sth = $dbh->prepare("SELECT * FROM addresses") ||
5 die "Error preparing query";
6 $sth->execute;
7 defined($dbh->err) && die "Error reading records";
8 while(my $res = $sth->fetchrow_hashref) {
9 print "$_: $res->{$_}<BR>\n" foreach(keys %$res);
10 }
11 $dbh->disconnect;
The script makes a connection to a MySQL database called "test" in line 2,
using the username "joe" and the password "joepw". This connection is
represented by a database connection handle $dbh.
Then it uses this handle to prepare an SQL query to read the entire
addresses table in line 4, obtaining a statement handle
$sth, and executes the query in line 6. If there was no error, the
loop in lines 8-10 calls fetchrow_hashref to fetch the results one
by one, and prints them in the form
<fieldname>: <contents><BR>
in line 9.
Using the above simply wrapped in an HSC <PERL> macro call would already enable you to get your database contents into the HTML file! It's far from user-friendly though: you have to modify the script for every change in your query and/or layout of the result. We can do better than that!
First, let's wrap the script in a macro of its own and give it a few parameters—sorry: attributes, so we can pass in the things that might change, such as: DBMS, database, host2, query, user and password:
<$macro SQL_QUERY QUERY:string/R DATABASE:string/R
DBMS:string/R USER:string='' PASSWORD:string=''
HOST:string='localhost'>
<PERL ARGS=("'" + DBMS + "' '" + HOST + "' '" + USER + "' '" + PASSWORD +
"' '" + QUERY + "' '" + DATABASE + "'")>
use DBI;
my ($dbms,$host,$user,$pw,$query,$db) = @ARGV;
my $dbh = DBI->connect("DBI:$dbms:database=$db;host=$host",$user,$pw) ||
die "Error connecting to $dbms database $db on $host";
my $sth = $dbh->prepare($query) || die "Error preparing query: $dbh->err";
$sth->execute;
defined($dbh->err) && die "Error reading from database $db: $dbh->err";
while(my $res = $sth->fetchrow_hashref) {
print "$_: $res->{$_}<BR>" foreach(keys %$res);
}
$dbh->disconnect;
</PERL>
</$macro>
That's already a little better. Note how the first line after "use
DBI;" pulls in the parameters from HSC using the commanline arguments!
Now you can write things like
<SQL_QUERY QUERY="SELECT name,address,phone FROM addresses"
DBMS=mysql DATABASE=test USER=joe PASSWORD=joepw>
and have the results inserted in your file without much fiddling with the Perl
code.
Still, the "layout" leaves much to be desired. But of course this can be fixed: if you remember that any script's output is not inserted into HSC's result file but in the source (see the Scripting article!), it turns out that you could use further macros for formatting the database contents. This allows you to leave the Perl code alone and do all the formatting required in HSC. As you would probably do it if you had an address database to layout by hand, say, a very simple one with names, addresses and phone numbers:
<FMT-ADDRESS NAME="John Doe" ADDRESS="42 Mulholland Drive" PHONE="123-4567"> <FMT-ADDRESS NAME="Jose Velarde" ADDRESS="Malacañang, Metro Manila" PHONE="N.N.">
If you then wanted a different layout for the addresses, like a list instead
of a table row, you would only have to change the FMT-ADDRESS
macro instead of the whole set of addresses. The same scheme can be applied
here: we only need a macro that takes one parameter for each SQL column we
select, and pass the name of this macro to the Perl script. The script would
then, instead of directly formatting the entries, print out one macro-call for
every row, and HSC could continue to do the actual layout. If we add a few
attributes for convenience, like separate COLUMNS,
TABLE and COND[ition] to hide the SQL syntax (while
retaining the power to formulate complex queries), we get this:
<$macro SQL_QUERY COLUMNS:string='*' TABLE:string/R DATABASE:string/R
COND:string='' FORMATTER:string/R DBMS:string/R
USER:string='' PASSWORD:string=''
HOST:string='localhost'>
<$define QUERY:string/C=("SELECT " + COLUMNS + " FROM " + TABLE + " " +
COND + ";")>
<PERL ARGS=("'" + DBMS + "' '" + HOST + "' '" + USER + "' '" + PASSWORD +
"' '" + QUERY + "' '" + DATABASE + "' '" + FORMATTER + "'")>
use DBI;
my ($dbms,$host,$user,$pw,$query,$db,$fmt) = @ARGV;
my $dbh = DBI->connect("DBI:$dbms:database=$db;host=$host",$user,$pw) ||
die "Error connecting to $dbms database $db on $host";
my $sth = $dbh->prepare($query) || die "Error preparing query: $dbh->err";
$sth->execute;
defined($dbh->err) && die "Error reading from database $db: $dbh->err";
while(my $res = $sth->fetchrow_hashref) {
print "<$fmt";
foreach(keys %$res) {
print " $_='$res->{$_}'";
}
print ">";
}
$dbh->disconnect;
</PERL>
</$macro>
Now only TABLE, DATABASE and DBMS
are mandatory for a query, COLUMNS defaults to '*' (i.e. "all").
The new mandatory attribute is FORMATTER, which gives the name of a
macro responsible for the actual formatting. For the above example of the very
simple address database, it could look like this:
<$macro ADDRESS-FORMATTER NAME:string ADDRESS:string PHONE:string> <TR> <TD><(name)></TD> <TD><(address)></TD> <TD><(phone)></TD> </TR> </$macro>
Obviously, this would lay out the addresses in a table row. The attribute
names simply correspond to the column names in the database. To make it valid
HTML, you'd have to use the query macro inside a TABLE:
<TABLE>
<TR><TH>Name</TH><TH>ADDRESS</TH><TH>PHONE</TH></TR>
<SQL_QUERY FORMATTER=ADDRESS-FORMATTER TABLE=addresses DATABASE=test
DBMS=mysql>
</TABLE>
That's almost all there is to do about database queries—one macro of
just a few lines. The only thing left is the handling of special cases that
could still break the resulting HSC code, most notably NULLs and single quotes
in a result column. A NULL in a database column is returned as an undefined
value in Perl, which would give a warning while processing and leave the
corresponding formatter attribute without a value. So NULLs have to be replaced
with something printable, preferrably configurable via another attribute to
SQL-QUERY, so you can be technical and print a "NULL", reasonable and print
nothing (or rather an entity), or supply any other default suitable
for your database. Quotes are a little harder to handle. As we used single
quotes for the formatter attributes, double quotes could appear inside in any
number without causing problems, however, a single quote will. Theoretically,
both could appear in one result, and HSC can't handle this as a single string
at all. Neither does escaping them work, rather these strings have to be
converted into an HSC expression that concatenates strings containing only one
kind of quotes and being surrounded by the other kind. The
quotestring function that looks like line noise in the final
version of SQL_QUERY below does exactly that :-) Not in the most
efficient way for HSC (the result sometimes contains concatenations with empty
strings) but good enough for these cases that are quite rare anyway.
<$macro SQL_QUERY COLUMNS:string='*' TABLE:string/R DATABASE:string/R
COND:string='' FORMATTER:string/R NULL:string='NULL'
DBMS:string/R USER:string='' PASSWORD:string=''
HOST:string='localhost'>
<$define QUERY:string/C=("SELECT " + COLUMNS + " FROM " + TABLE + " " +
COND + ";")>
<PERL ARGS=("'" + DBMS + "' '" + HOST + "' '" + USER + "' '" + PASSWORD +
"' '" + QUERY + "' '" + DATABASE + "' '" + FORMATTER + "' '" +
NULL + "'")>
use DBI;
my ($dbms,$host,$user,$pw,$query,$db,$fmt,$null) = @ARGV;
my $dbh = DBI->connect("DBI:$dbms:database=$db;host=$host",$user,$pw) ||
die "Error connecting to $dbms database \"$db\" on $host";
my $sth = $dbh->prepare($query) || die "Error preparing query: $dbh->err";
$sth->execute;
defined($dbh->err) && die "Error reading from database $db: $dbh->err";
$null = quotestring($null);
while(my $res = $sth->fetchrow_hashref) {
print "<$fmt";
foreach(keys %$res) {
print " $_=" . (defined($res->{$_}) ? quotestring($res->{$_}) : $null);
}
print ">";
}
$dbh->disconnect;
sub quotestring {
my $s = shift;
my $sq = (-1 != substr($s,'\''));
if(-1 != substr($s,'"') and $sq) {
my %t=('"'=>'\'','\''=>'"');
$s =~ s/("|')/'+$t{$1}$1$t{$1}+'/g;
return "('$s')";
}
$sq && return "\"$s\"";
return "'$s'";
}
</PERL>
</$macro>
Oh, and of course you wouldn't want to use this very general macro with its many obligatory attributes directly. Better define yourself a wrapper that sets the attributes which are constant in your project:
<$macro MYQUERY COLUMNS:string TABLE:String COND:string FORMATTER:string/R>
<SQL_QUERY DBMS=mysql COND?=COND FORMATTER?=FORMATTER COLUMNS?=COLUMNS
TABLE?=TABLE DATABASE=test NULL="Nothing here!">
</$macro>
Last change: 21-Feb-2006, 06:43