This is a manual for the CGI program WWW-SQL, written by James Henstridge. An online copy of this document exists here. It is part of the WWW-SQL web site. If you have some spare time, go to the site to fill out the WWW-SQL survey (Powered by www-sql, of course).
WWW-SQL is a CGI program designed to create web pages from information in MySQL databases on the fly. It was written by James Henstridge (james@daa.com.au). Its syntax is similar to that of W3-mSQL which is a product of Hughes Software, and was designed for mSQL. This program is distributed under the terms of the GPL, and doesn't share any code with W3-mSQL (although I did use its syntax as a model). The expression parser inside this program is just a modified version of the shell command expr from GNU shell utilities, for which the GPL also applies.
WWW-SQL is a CGI program that passes another HTML page, extracting special tags, parsing them and inserting the results in the resulting document. As you may have guessed, the commands this preprocessor uses are related to lookups of SQL databases. The command set contains conditional execution, execution of queries and expansion of CGI variables and query fields.
It should be noted that www-sql is modelled around the interface of the W3-mSQL distributed for mSQL 1.x. It is not compatible with, or similar to the command set of the W3-mSQL distributed with mSQL 2.x. If you require a tool of similar complexity, maybe you should look at PHP/FI. It is a more general purpose programming language that is embeded in HTML documants.
I have only tested this program on Linux, but it should compile without much of modification on most other modern *nixes. If you need to modify the source to get it to compile, please send a diff to me at james@daa.com.au so that I can integrate it into the next release. To help compile it on other systems, I have adapted this package to use autoconf, so first try running ./configure.
If that doesn't work, there are a number of possible causes.
/usr/local/mysql/lib or
/usr/lib/mysql or /usr/local/lib/mysql, and
it searches for mysql/mysql.h in the include search path, or
in /usr/local/include or /usr/local/mysql/include.
If they are located in a different place, either put in symbolic links so
that the files can be found, or set the shell variable MYSQL_LIBDIR
to the library dir (including a -L), and MYSQL_INCDIR to a directory so that
the include <mysql/mysql.h>. (Remember the -I)
make SQL_USER=user SQL_PASS=pass
The problem with linking to the static library has been fixed. If you used to use the --enable-static-lib-kludge, you will find that it is unneeded now. (For those who want to know, I put the libraries at the start of the linking command, rather than the end, so they weren't used to satisfy any dependencies).
The current values should work on Redhat Linux systems that used the RPMs found at the MySQL distribution site. Now run make with no parameters. If the program compiles successfully, try giving it a small test:
cat << EOF > test.in <html> <head><title>test</title></head> <body> Answer to life, the universe and everything = <!sql print $answer> </body></html> EOF env REQUEST_METHOD=GET QUERY_STRING='answer=forty+two' \ PATH_TRANSLATED=test.in REMOTE_ADDR=127.0.0.1 \ REMOTE_HOST=localhost www-sql
This should write a small HTML document saying "Answer to life, the universe and everything = forty two" to the standard output.
Now we are ready to install the binary. If your CGI-BIN
directory is not one of /home/httpd/cgi-bin,
/var/lib/httpd/cgi-bin or
/usr/local/etc/httpd/cgi-bin, you must specify the directory on
the command line. Simply type
make install CGI_DIR=cgi-dirOtherwise just type make install. If your web server is installed in a different, standard place, please notify me so I can include it in the next release. The values I have given are just those I have encountered with Redhat Linux, Slackware Linux, and the standard install directory for NCSA and Apache web servers.
Now that the binary compiled and installed, you can write WWW-SQL
documents, and place them in your document tree. Now to parse
a document that would usually be accessed by the URL
http://host/doc.html,
use the URL http://host/cgi-bin/www-sql/doc.html.
One thing you must remember when calling up this document, is
that the web browser will think that the current directory is
/cgi-bin/www-sql, and all links will be relative
to this.
If you use Apache web server, there is an elegant way round this.
After installing the binary, edit your srm.conf file,
and insert the following lines:
Action www-sql /cgi-bin/www-sql AddHandler www-sql sql
Now documents with the extension .sql will be parsed
by WWW-SQL, without having to actually reference WWW-SQL in the
URL. This also prevents people from accessing the unparsed document
and stealing passwords from the connect command.
So you could rename doc.html to doc.sql,
and use the URL http://host/doc.sql.
Now you need to set up the default user account for WWW-SQL to use. This can be achieved by typing the following commands:
mysql -uroot -ppass mysql << EOF
insert into user (Host, User, Password, Select_priv)
values ("localhost", "nobody", "", "Y");
EOF
mysqladmin -uroot -ppass reload
Where pass is the root password. This will give www-sql access to all your databases. In order to be more restrictive, use the following:
mysql -uroot -ppass mysql << EOF
insert into user (Host, User, Password)
values ("localhost", "nobody", "");
EOF
and then, for each database you want to give www-sql access to, type:
mysql -uroot -ppass mysql << EOF
insert into db (Host, Db, User, Select_priv)
values ("localhost&guot;, "db", "nobody", "Y");
EOF
and finally:
mysqladmin -uroot -ppass reload
The tags recognised by WWW-SQL must be of the following form:
<! SQL command arg1 arg2 ...
>
The meanings of the parameters after the <! SQL
are as follows
The word SQL and the command name are case independent, but the
arguments are not. Also, it doesn't matter if there is space between
the <! and the SQL.
In the eval and if commands, an expression is used as the arguments.
This expression can contain any tokens that can be passed to the
expr command, and also && and
||. In order to perform a greater than test, use
\>. Unlike the expr command, tokens
do not have to be space separated (e.g. '1<2'
is legal).
In some commands, cgi variables and query results are expanded.
The cgi variables are specified by their name, preceded by a dollar
sign (e.g. $name). The query results for the current
row are specified by the query name preceded by an @, followed
by a dot and the field number (zero being the first field. e.g.
@q1.0). Variable expansion occurs in expressions
as well. When variable expansion occurs, the escape sequences
'\n', '\t', '\$', '\@'
and '\\' are also expanded.
In the definitions of the commands, required text will be in this
font, variables will be in this font, and optional
parameters will be enclosed in square brackets.
When WWW-SQL parses a document, it sets some extra variables at times. At the moment, there are only two of these variables. When the query command is executed, it stores the size of the result in two variables:
$AFFECTED_ROWS
$NUM_FIELDS
$NUM_ROWS
$REMOTE_ADDR
$REMOTE_HOST
These variables can be accessed in the same way CGI variables may be.
<! SQL close >
This command closes the connection to the MySQL daemon. It should be called as the last WWW-SQL command.
<! SQL connect [ host [ user password
] ] >
This command connects you to the MySQL daemon. It should be used before any other SQL commands are used.
mysqld is running.
This defaults to the local host.
mysqld. If this argument
is given, so must password.
Note that the SQL commands are not sent to the client, so the only people who will be able to look at passwords embedded in the documents are people with access to the local host.
<! SQL database dbname >
This command says which database to use. The database is specified by dbname.
<! SQL dumpvars >
This command prints out the names and values of all www-sql variables set at the time of the call.
<! SQL eval expr >
This command evaluates expr, and inserts the result in the document.
<! SQL fetch qhandle
>
This command gets the next row from the query corresponding to qhandle.
<! SQL free qhandle >
This command frees the query handle qhandle.
<! SQL if expr
>
...
[ <! SQL else > ]
...
<! SQL endif >
These commands implement conditional execution. When the if
command is encountered, expr is evaluated. If expr
is evaluated to true, non zero, or a non empty, everything up
to an else or endif is parsed. When
an else is encountered, if previous commands were
being ignored, the next commands are parsed, and vice versa. When
an endif is encountered, parsing returns to normal.
The else command can be left out, and if
statements can be nested to any level.
<! SQL print text
>
This command expands variables in the argument text and includes the output in the document.
<! SQL print_rows qhandle
format >
This command is used to quickly print the results from a query
associated with qhandle. It starts from the current
row, evaluates format, outputs it, and goes on to the
next row, and repeats until it reaches the end of the table. In
order to use the results again, the seek command
must be used.
<! SQL query query qhandle
>
This command executes the query query, and associates
the result with qhandle. After the information has
finished being used, the handle must be freed with the free
command.
<! SQL qtable qhandle [borders]>
Generates a 'quick and easy' HTML table containing all of the rows
from the query represented by qhandle, headed by the field
names. The optional borders keyword switches on borders for
the table. This command is provided as an easy alternative to
print_rows.
This command was originally contributed by Martin Maisey <M.J.Maisey@webname.com>.
<! SQL qlongform qhandle>Generates a 'quick and easy' long form results page that can be used for display on browsers which don't support tables, and for queries with a lot of resulting rows. The output looks like:
Forename: John
Surname: SmithForename: Joe
Surname: Bloggs...
This command was originally contributed by Martin Maisey <M.J.Maisey@webname.com>.
<! SQL seek qhandle pos
>
This command moves the row position of qhandle to row pos.
<! SQL set name
value >
This command sets the value of the variable name to value.
<! SQL setdefault name value
>
This command checks to see if variable name exists, and if it doesn't, sets it to value.
This example shows how you might set up a page to look at an telephone
directory table. This assumes that there is a table with three
columns (surname, firstname, number), called numbers in a database
telephone. It only shows a maximum of 10 numbers per page, and
lets you page through the results. It should be called example.sql.
This example should have been included in the distribution.
<html> <head><title>Telephone Numbers</title></head> <body> <H1>Telephone Numbers</H1> <!-- Let user change query --> <form action=example.sql> <input name=sur > <input type=submit><br> </form> <! sql connect > <! sql database telephone > <! sql setdefault sur "-" > <! sql setdefault ofs 0 > <! sql query "select * from numbers where surname = '$sur' order by firstname limit $ofs,10" q1 > <! sql if $NUM_ROWS != 0 > <!-- Put in table --> <table> <tr> <th>Surname</th> <th>First Name</th> <th>Number</th> </tr> <! sql print_rows q1 "<tr> <td>@q1.0</td> <td>@q1.1</td> <td>@q1.2</td> </tr>\n" > </table> <!-- Put in navigation links --> <center> <! sql if 9 < $ofs > <! sql print "<a href=\"example.sql?sur=$sur&ofs=" ><! sql eval $ofs - 10 ><! sql print "\">">Prev</a> <! sql else > Prev <! sql endif > <! sql if $NUM_ROWS = 10 > <! sql print "<a href=\"example.sql?sur=$sur&ofs=" ><! sql eval $ofs + 10 ><! sql print "\">">Next</a> <! sql else > Next <! sql endif > </center> <! sql endif > <p> <center><em>Page produced by WWW-SQL</em></center> </body> </html>
This example gives an idea of what WWW-SQL can do. Of course it can also do simpler and more complex queries. If there is no way of doing the task, try e-mailing me at james@daa.com.au giving your idea, or even a patch.
I am currently a first year student at The University of Western Australia. I like sailing and programming. I distributed this program under the GPL because I thought others would find it useful. If you end up making lots of money from it, consider donating some to charity or me. If you just find it useful, please mail me at james@daa.com.au. This will make me more likely to release subsequent versions.
Also if you like fractals, try pointing a Java enabled browser at my fractals page.