HSC/SQL

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.

First attempt

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!

Parameters

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.

Formatting macros

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 &nbsp; 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>

Footnotes
  1. DataBase Management System—to distinguish the software from the database that is the actual collection of data
  2. Yes, of course you can use databases over the network. It all comes free with DBI.pm!

Last change: 21-Feb-2006, 06:43

You are not supposed to see this—arachnoids only: nude metal goth porn tits video i phone thai hentai gangbang girls i touch lolita emo slutsparis ghettoschlampen arschgeficktgay geigh salope enculée pédé