NAME

Apache::WeSQL - Apache mod_perl module for WeSQL


SYNOPSIS

    PerlSetVar WeSQLConfig /var/www/WeSQL/somesite/conf/WeSQL.pl
    PerlModule Apache::WeSQL::AppHandler
    <FilesMatch "*.wsql">
      SetHandler perl-script
      PerlHandler Apache::WeSQL::AppHandler
    </FilesMatch>
    DocumentRoot "/var/www/WeSQL/somesite/public_html"
                DirectoryIndex index.wsql


DESCRIPTION

The Web-enabled SQL (WeSQL) Apache mod_perl module is an extension to HTML, acting as a glue between HTML and SQL. It allows the use of pure SQL queries directly in HTML files, embedded in a special tag. WeSQL translates the special tags into pure HTML, so using WeSQL is transparant for the browser. WeSQL is aimed at rapid web-database integration. WeSQL is written entirely in Perl and currently supports both MySQL and PostgreSQL as backend SQL databases.


PREREQUISITES

Prerequisites for an easy installation: a unix system with working Apache, perl, mod_perl, and MySQL or PostgreSQL. We want to interface to a database, so we need several Perl modules. Do yourself a favour and get the latest versions of:

But if you are brave, there is no reason why WeSQL should not run on any system with any webserver that can execute Perl code as cgi-scripts, and can connect to a MySQL or PostgreSQL server.


FEATURES

The following tags are supported: LIST, EVAL, PARAMCHECK, CUTFILE, INCLUDE and LAYOUT.

In addition, the following features are implemented:


RUNNING MULTIPLE WEBSITES

You can run multiple WeSQL websites (possibly accessing different databases) on the same web server. Here's how:

This is a little complicated, and the reason for that is a namespace problem. The power of WeSQL is that it makes 1 database connection per server thread and keeps it, which means that you can intermix SQL and html in your WeSQL files. But if you want to run several applications on the same server, you will want a separate database connection for each application. That is why the AppHandler module needs to be duplicated and renamed, as the persistent database connection lives in that module. I have thought long and hard about this but have not found a better solution. If you have one, let me know!!


NAMING CONVETIONS FOR .WSQL FILES

All files you want to be parsed by WeSQL should have the .wsql extension. Files with other extensions will be dealt with in the normal way by Apache.

There are a number of 'virtual' files that you can call, but don't exist on your hard-drive, the calls are intercepted by WeSQL. These are found in the display sub in WeSQL.pm, they are:

        jadd.wsql
        jupdate.wsql
        jdelete.wsql
        jform.wsql
        jdeleteform.wsql
        jdetails.wsql
        jlist.wsql
        jloginform.wsql
        jlogin.wsql
        jlogout.wsql

These files are used by the journalling code and can be controlled by the form.cf, details.cf, list.cf and permissions.cf files, as explained in the Apache::WeSQL::Display man page.

If you don't want WeSQL to print HTTP headers before sending the result of your parsed wesql file to the browser, for instance because you want to generate it yourself, or because you want to redirect to another url, just make sure the name of the file ends in redirect.wsql, e.g. like this: file1redirect.wsql (this feature was introduced in WeSQL v0.52).


WESQL.PL CONFIGURATION FILE

You will have noticed the PerlSetVar statement in the httpd.conf configuration:

    PerlSetVar WeSQLConfig /var/www/WeSQL/somesite/conf/WeSQL.pl

This line sets the parameter 'WeSQLConfig', and gives it the value '/var/www/WeSQL/somesite/conf/WeSQL.pl'. This value is the position of the WeSQL config file for your application.

Please note that this file lives outside your documentroot by default. Even though it is possible, I don't recommend putting this file under your document root, as it contains sensitive information like the username and password to connect to your database.

Any changes to the WeSQL.pl file will require a restart of Apache.

This file is website-specific, and it defines the following parameters for the website:

@commandlist

WeSQL files are html files with special tags that are understood by WeSQL. They are processed in several steps, and the result is a clean HTML file that is delivered to the browser of the client.

You can decide which parsing steps you want WeSQL to use, by changing the @commandlist array. This is the default array:

@commandlist = ( 'dolayouttags($body)', 'dolanguages($body)', 'dosubst($body,``PR_'',%params)', 'dosubst($body,``ENV_'',%ENV)', 'dosubst($body,``COOKIE_'',%cookies)', 'doeval($body,``PRE'')', 'doinsert($body)', 'doeval($body,``POSTINSERT'')', 'doparamcheck($body)', 'docutcheck($body)', 'doeval($body,``PRELIST'')', 'dolist($body,$dbh)', 'doeval($body,``POST'')', 'docutcheck($body)'

      );

These default steps process the WeSQL file in the following order:

  1. Replace the LAYOUT tags with the corresponding layout block from layout.cf
  2. (introduced in WeSQL v0.52) Deals with the LANGUAGE tags.
    Since version 0.52, WeSQL supports 'content negotiation' for languages, as defined in the HTTP/1.1 standard. Compliant browsers (Mozilla, Opera 6.0 and higher, Netscape, ...) add an 'Accept-Language' header to the requests for files. WeSQL now understands those headers, and can serve the content in the correct language to the browser - provided, of course, that the content is available in this language.

    Examples of language strings are 'en', 'nl', 'fr', 'de', etc.

    In order to create content in other languages than the default language, for instance Dutch, you need to create a 'layout.nl.cf' file. If there is no need for a specific language version of your layout.cf file, just create a symlink to layout.cf.

    In your .wsql files, you can now put text between <nl> and </nl> tags, which will make this text be sent to the browser _only_ if the browser asks for a version of the language in Dutch. This is done with the call to the 'dolanguages' sub in WeSQL.pm. If you forget to create a layout.nl.cf file, you will see the content between the <nl> and </nl> tags for requests in all languages.

    If you are upgrading from an earlier WeSQL installation, you will need to add a line to the @commandlist in conf/WeSQL.pl. Preferably just after the dolayouttags call, like this:

      @commandlist = (
          'dolayouttags($body)',
          'dolanguages($body)',                     <<<<<< Add this line
          'dosubst($body,"PR_",%params)',

    Here is the WeSQL decision path for which language to serve the requested document in:

    1. Is there a language requested in the URI?

    In order to get a specific language version of a file, for instance English, you can requests 'index.en.wsql'. This file does not exist on disk, but WeSQL will determine from this request that you really want the index.wsql file in English, and honour that request, provided the layout.en.cf file exists. If not, the layout.cf file will be used.

    The side effect of this request, is that the default session language (see below) will be set to English. This has the effect of switching languages in the site, without having to worry about subsequent URLs.

    Else: 2. Is there a language stored in the session?

    If there is a session variable with name 'language' and a value different from '', WeSQL will serve the document in the language specified in the session variable.

    This session variable is set after a call to a page requesting a specific language in the URI, as described under 1.

    Else: 3. Is there a content negotiation 'Accept-Language' header?

    If the browser sends an Accept-Language header, it will be respected, provided the corresponding layout.xx.cf file exists.

    Else: 4. Fallback to layout.cf

  3. Substitute PR_, ENV_, and COOKIE_ style parameters by their respective values
  4. Execute any EVALs with the PRE tag
  5. Insert another WeSQL file (note that the inserted file will have PR_, ENV_, and COOKIE_ style parameters replaced by their respective values!)
  6. Execute any EVALs with the POSTINSERT tag
  7. Process the PARAMCHECK tags
  8. Process the CUTFILE tags
  9. Execute any EVALs with the PRELIST tag
  10. Process the LIST tags
  11. Execute any EVALs with the POST tag
  12. Process the CUTFILE tags once more (there might be new ones from the EVAL blocks or the LAYOUT step!)

Reducing the number of steps will improve performance, and disabling a particular step can be very useful for debugging - e.g. when LIST tags are being built dynamically in EVAL blocks.

You can add your own steps. Create a sub (preferably in your own module that you include in the Apache::WeSQL module - this will minimize problems if you upgrade your WeSQL) that takes some text as input (the $body parameter will be the input text) and returns the parsed $body. Then insert a call to your sub somewhere in the @commandlist. Don't forget to restart Apache for the changes to have effect.

$dbtype

This parameter determines whether you are using a MySQL or a PostgreSQL database. Set it to 0 for MySQL, and to 1 for PostgreSQL.

Example: $dbtype = 0;

$dsn

Your database dsn.

Example: $dsn = ``DBI:mysql:database=addressbook;host=localhost'';

$dbuser

The user to connect to the database as.

Example: $dbuser = ``milk'';

$dbpass

The password to connect to the database with.

Example: $dbuser = ``yoghurt'';

$authenticate, $authsuperuserdir, $noauthurls, $authsuperuser

These are parameters for the Apache::WeSQL::Auth module. See the Apache::WeSQL::Auth manpage for more information.


WeSQL PARSING STEPS

PARAMETER SUBSTITUTION

Say we have a HTML file that looks like this:

    <html>
    <body>
    I have a parameter with the name 'beautiful' and value 'PR_BEAUTIFUL'.
    </body>
    </html>

And assume that we call this wesql file test.wsql, on a server http://www.somewhere.org.

A user comes along and requests: http://www.somewhere.org/test.wsql?beautiful=me

In this case, the resulting wsql file will become:

    <html>
    <body>
    I have a parameter with the name 'beautiful' and value 'me'.
    </body>
    </html>

In general, PR_NAME is substituted with the value of the 'name' parameter (case-insensitive, could be 'NaMe' or 'naME' or ...) passed via GET or POST. If no parameter is provided, PR_NAME will be left untouched.

You can also specify a default value, for when the parameter is not defined, like this:

    <html>
    <body>
    I have a parameter with the name 'beautiful' and value '[PR_BEAUTIFUL|people]'.
    </body>
    </html>

So when another user comes along, requesting just http://www.somewhere.org/test.wsql without parameters, (s)he will see:

    <html>
    <body>
    I have a parameter with the name 'beautiful' and value 'people'.
    </body>
    </html>

And finally look at this:

    <html>
    <body>
    I have a parameter with the name 'beautiful'[ and value 'PR_BEAUTIFUL'|, but it is not defined now].
    </body>
    </html>

Without the parameter beautiful defined, the user will see:

    <html>
    <body>
    I have a parameter with the name 'beautiful', but it is not defined now.
    </body>
    </html>

Note that you can use a closing right bracket (]) in the alternative value by just escaping it with a backslash (\). Similarly, you can escape the pipe symbol with a backslash (\).


In a similar fashion, COOKIE_ and ENV_ style strings will be replaced by respectively the cookie or environment
variable with the corresponding name.

There are 2 special ENV_ variables available for your use:

    ENV_FILE_SIZE
    ENV_FILE_LAST_MODIFIED

These two respectively hold the size and the last modification time (in seconds since EPOCH) of the current WeSQL document. Of course you can also access them through the %ENV hash in EVAL blocks.

INCLUDE

Syntax:

    <!-- INCLUDE [prefix] file -->

The include tag includes another WeSQL file. You should provide a prefix, which can be used to have a different set of variables available to the included WeSQL file. An example might clarify things a bit more. Let's assume we have a script called yoghurt.wsql, which - amongst other interesting stuff - contains the following line:

    <!-- INCLUDE I1_ milk.wsql -->

Our yoghurt.wsql script is called as follows: http://diary.org/yoghurt.wsql?type=strawberry&I1_type=skimmed

When yoghurt.wsql is processed, there will be 2 parameters available to it: type and I1_type, just like you would expect. When the include tag is processed, WeSQL will see that the I1_type parameter starts with the prefix mentioned in the include tag, and pass the parameter I1_type to the milk.wsql file - but after translating it to type. So the milk.wsql file will see only one parameter, with the name type and the value skimmed.

If you want to use the same parameters for the included file as for the 'parent' file, just omit the 'prefix' parameter.

PARAMETER VALIDATION

Say we have a WeSQL file that looks like this:

    <html>
    <body>
    <!-- PARAMCHECK
    <paramcheckhead>
    <center>Below are the problems that have been encountered:<p>
    </paramcheckhead>
    PR_ONE      /STOP/          <font color=#FF0000>Parameter 'one' must contain the word 'STOP'</font><br>
    PR_TWO      /^\d+$/         <font color=#FF0000>Parameter 'two' must be a number and may not be empty</font><br>
    PR_THREE    //              <font color=#FF0000>Parameter 'three' must be defined!</font><br>
    PR_THREE    !/^$/           <font color=#FF0000>Parameter 'three' may not be empty!</font><br>
    PR_THREE    !/%/            <font color=#FF0000>Parameter 'three' can not contain a %-sign!</font><br>
    PR_FOUR     !/[\/ ]/        <font color=#FF0000>Parameter 'four' can not contain a forward slash or a space!</font><br>
    PR_FIVE     !//             <font color=#FF0000>Parameter 'five' may not be defined!</font><br>
    <paramcheckfoot>
    </center>
    <!-- INCLUDE footer.wsql -->
    <!-- CUTFILE -->
    </paramcheckfoot>
    /PARAMCHECK -->
    <p>
    If you can read this, all parameters conform with the conditions.
    </body>
    </html>

The syntax of the PARAMCHECK tag is as follows:

    <!-- PARAMCHECK
    <PARAMCHECKHEAD>
    header
    </PARAMCHECKHEAD>
    ParameterRegular expressionError text
    <PARAMCHECKFOOT>
    footer
    </PARAMCHECKFOOT>
    /PARAMCHECK -->

A set of conditions are defined, which should be matched by the parameters passed to the script. If one of the conditions is not met, the 'header' is printed, then the 'Error text' of all the conditions that are not met, followed by the 'footer'.

In the above example, the footer consists of an INCLUDE tag, followed by the CUTFILE tag, so that the rest of the file will not be parsed nor printed if one of the conditions is not met.

Standard perl regular expressions may be used, and an exclamation mark (!) can be used to inverse the condition.

CUTFILE

Syntax:

<!-- CUTFILE -->

When this tag is encountered, parsing of the html is stopped, and anything following the tag is simply discarded. So this is a tag that you probably want to generate during one of the other steps. A typical example is during the PARAMCHECK step. Note that the CUTFILE tag must start on the first character of the line, and that it must be the only thing present on the line. This is the only tag with this kind of restriction. The restriction is deliberate, to make it easier to build a CUTFILE tag in an EVAL statement for instance.

LAYOUT

Syntax:

    <!-- LAYOUT header -->

This tag will be substituted by the block of html with name 'header' from the layout.cf file. For more information, see the Apache::WeSQL::Journalled manpage.

LIST

Syntax:

    <!-- LIST _IDENTIFIER_ _SQLSTATEMENT_ -->
    Statements that will be repeated for every resulting line from the _SQLSTATEMENT_
    <!-- /LIST _IDENTIFIER_ _NOMATCHTEXT_ -->

_IDENTIFIER_ can be any word of at least 1 character, but can not contain any spaces. This identifier is used to distinguish list tags from each other when they are nested. That's right, LIST tags can be nested as deep as you like. The tags are expanded from the outside to the inside.

You can use several LIST A statements in one html file, as long as no LIST A statement is nested within another one.

_SQLSTATEMENT_ is any SQL statement that can be executed by your backend SQL database.

_NOMATCHTEXT_ is the text that is displayed - just once - if there are no matches to your query. You can use html in _NOMATCHTEXT_.

Consider this example, loosely based on the sampleapp application that comes with WeSQL.

    <html>
    <body>
    <!-- LIST A SELECT name,id FROM somethings WHERE name like "%PR_NAME%" ORDER BY name -->
    <a href="/modify?id=A_ID">A_NAME</a><br>
    <!-- /LIST A <font color="#FF0000">No things that satisfy the search fields were found.</font> -->
    </body>
    </html>

If the table 'somethings' would contain the following data:

And if the 'name' parameter passed to the above piece of html would have the value 'over', then the above html would be expanded to:

    <html>
    <body>
    <a href="/modify?id=2">cover</a><br>
    <a href="/modify?id=1">Lover</a><br>
    </body>
    </html>

If the 'name' parameter would be 'perl', then the output would be:

    <html>
    <body>
    <font color="#FF0000">No things that satisfy the search fields were found.</font>
    </body>
    </html>

EVAL

EVAL tags can be either a quick one-line tag, or a longer multi-line tag. One-liner:

    <!-- EVAL XXX return "Hello World!"; -->

Multi-line tag:

    <!-- EVAL XXX
      return "Hello World!";
    /EVAL XXX -->

XXX is the identifier that will determine in which parsing step this EVAL block will be executed. XXX can be any word of at least 1 character, not containing any whitespace.

As specified higher, the XXX identifier determines when in the parsing steps this EVAL block will be evaluated. It is a good idea to choose a descriptive name for these identifiers, referring to their position in the execution chain. The default @commandlist will execute PRE, POSTINSERT, PRELIST and POST EVAL blocks. Of course you can modify the @commandlist to suit your needs.

EVAL-tags of the same identifier can not be nested for now, but you can generate one within one of another type (i.e., with another identifier).

Adding your own EVAL steps is very straightforward - choose an identifier, add the step somewhere in the @commandlist array, and you're in business!

Code example:

    <html>
    <body>
    <!-- EVAL PRELIST
    my $result; if ("PR_NAME" eq "") {
      $result = "<!-- LIST A SELECT count(*) FROM somethings -->";
    } else {
      $result = "<!-- LIST A SELECT count(*) FROM somethings WHERE name LIKE \"%[PR_NAME%|]\" -->";
    }
    return $result;
    /EVAL PRELIST -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

The following code snippet demonstrates the use of the EVAL syntax.

First, parameters passed via GET or POST are substituted. In this case, PR_NAME is replaced by the value of the parameter 'name' (case insensitive), or by an empty string if that parameter is not available. Let's assume that there was no 'name' parameter. In this case, the resulting html would look like this:

    <html>
    <body>
    <!-- LIST A SELECT count(*) FROM somethings -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

But if the parameter 'name' exists, and had the value 'love', then the resulting html would look like:

    <html>
    <body>
    <!-- LIST A SELECT count(*) FROM somethings WHERE name LIKE "%love%" -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

The next step is the evaluation of the LIST statement. Maybe by now you start to wonder about the usefulness of this example. After all, check out this html block, without the use of an EVAL statement:

    <html>
    <body>
    <!-- LIST A SELECT count(*) FROM somethings WHERE name LIKE "%[PR_NAME%|]" -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

If 'name' is supplied, PR_NAME would be substitued by the value of 'name'. If not, an empty string would be substituted, and the result would be:

    <html>
    <body>
    <!-- LIST A SELECT count(*) FROM somethings WHERE name LIKE "%" -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

Which has exactly the same results as:

    <html>
    <body>
    <!-- LIST A SELECT count(*) FROM somethings -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

Yes. But, of course, the latter is lots faster, especially on large tables... So, fully using the WeSQL syntax, we could also write the following, and do away with the whole EVAL block:

    <html>
    <body>
    <!-- LIST A SELECT count(*) FROM somethings [WHERE name LIKE "%PR_NAME%"|] -->
    A_COUNT(*)
    <!-- /LIST A -->
    </body>
    </html>

And this would have the desired results, depending on whether name is defined as a parameter or not.

This module is part of the WeSQL package, version 0.52

(c) 2000-2002 by Ward Vandewege


EXPORT

None by default. Possible: %params %cookies &redirect &error &readLayoutFile


CREDITS

I would like to thank my employer, Better Access, for allowing me to develop the early WeSQL versions (up to 0.28.02) partly in their time.

Parts of the early WeSQL library were written by Kristof Verniers, and many ideas came from Jan Jansen. I am very grateful to the both of them.


AUTHOR

Ward Vandewege, <ward@pong.be>

Copyright (c) 2000-2002 Ward Vandewege. This program is free software; you can redistribute it and/or modify it under the terms of the GPL.


SEE ALSO

the Apache::WeSQL::AppHandler manpage, the Apache::WeSQL::SqlFunc manpage, the Apache::WeSQL::Journalled manpage, the Apache::WeSQL::Display manpage, the Apache::WeSQL::Auth manpage