Apache::WeSQL::Display - A library of functions to create web-pages based on a 'Journalled' SQL database.
use Apache::WeSQL::Display qw( :all );
This module contains the functions necessary to deal with the jform.wsql, jdetails.wsql, and jlist.wsql web calls. These calls read their configuration from the form.cf, details.cf, and list.cf files. Also, they use certain (see LAYOUT.CF below to know which) entries from the layout.cf file for the layout. The structure of the .cf files is outlined below.
The '.cf' files (except for layout.cf, see below) have the following syntax:
<view-name> <key>:<value> <value_line2> <key>:<value> <value_line2> <value_line3> ...
<view-name> <key>:<value> ...
Multi-line values are allowed as long as the extra lines begin with whitespace. Of course they can not be all whitespace, or they would be seen as a view separator!
In all values, several placeholders will be replaced by their respective value:
You can use the %params hash to refer to the cgi parameters passed to the script. For instance: $params{id} will be replaced by the value of the cgi parameter 'id'.
You can use the %cookies hash to refer to the cookies passed to the script. For instance: $cookies{id} will be replaced by the value of the cookie 'id'.
For the details.cf and forms.cf files (respectively used by jdetails.wsql and jform.wsql), you can also use the name of the columns are returned by the 'query' tag, prepended with a dollar sign, to represent the values of the record. For instance:
With this query, in the 'appendedit' tag, $id will be replaced by the value of the column id in the table people. Also note the use of the %params and %data replacements in the query. Note that the %data placeholder is replaced first, followed by the %params placeholder, and finally the column placeholders. This means that you can do something like $params{$data{key}} in the query above, but something like $data{$params{key}} will not work.
Please note that it is also possible to provide a 'default' value, for when the parameter/cookie/column is not defined:
query:select id,firstname,lastname,birthday from people where status='1' and id like [$params{id}|'%'] order by lastname
The [$params{id}|'%'] syntax will be evaluated to the cgi parameter id if defined, and if not, to '%'. Consider this example:
query:select id,firstname,lastname,birthday from people where status='1' and firstname like '%[$params{firstname}%|]' order by lastname
The '%[$params{id}%|]' will be evaluated to '%ward%' if there is a parameter firstname with value ward. Otherwise, it will be evaluated to '%'. So you can put other things around the $params{something} in the first part of the condition.
Or consider this:
query:select id,firstname,lastname,birthday from people where status='1' [and id like '$params{id}'|] order by lastname
This query is more efficient because the whole 'and' part will not be displayed if the parameter is not defined! Note the compulsary pipe symbol (|), used for specifying an optional alternative value. It also will reduce the chance of an accidental match of something in right brackets.
Finally, you can have the value of $params{something} url-encoded, by simply replacing it with encode($params{something}) in any of the above examples. This encoding is even safe for use with Opera 5.05 for Linux, which contains an url-decoding bug.
Similarly, you can have the value of $params{something} url-decoded, by simply replacing it with decode($params{something}) in any of the above examples. This decoding is even safe for use with Opera 5.05 for Linux, which contains an url-decoding bug.
Here is a list of possible keys for the .cf files:
The title of the page, as it will appear in the html of the page between the <title> and </title> tags in the header, and also somewhere near the top of the page (depends on the layout). Don't put html tags in 'title', because these will not be rendered within the <title> tags in the header of the document. Use the layout file instead if you want to control how to display the title on the page.
The sql select query that determines the columns that will be displayed on the page.
The buildquery key takes a perl expression as its value, that should evaluate to a proper sql query. When specifying this key, the query key (if defined) will be overwritten by the output of the eval of the buildquery key.
split(/\|/,'$params{keyword}') if defined('$params{keyword}');
my $defined = 0;
foreach (@keywords) {
if (!$defined) {
$query .= 'and (';
$defined = 1;
}
$query .= ``(k.keywordid = '$_') or ''
}
if ($defined) {
chop($query); chop($query); chop($query); chop($query);
$query .= ')';
}
$query .= ' group by p.id order by title';
return $query;
Determines the captions of the table columns.
syntax: captions:<colname>=<value>|<colname>=<value>|...
<colname> is the name of a column returned from the sql-select querey, as returned by the database. The value can be anything and may contain any character except a pipe (|). Columns without a 'captions' entry will have the name the database returns for them as caption in the page.
Allows aligning columns in a specific way.
syntax: align:<colname>=<value>|<colname>=<value>|...
<colname> is the name of a column returned from the sql-select querey, as returned by the database. The value can be 'left', 'right', or 'center'. By default there is no specific alignment (which in most browsers will show as a left alignment).
Allows aligning the 'name' and the 'value' column in a specific way. Note that for setting the alignment of specific lines of the output, you can use the 'align' parameter as described higher.
syntax: alignkey:<value> syntax: alignval:<value>
<value> can be 'left', 'right', or 'center'. Default there is no specific alignment (which in most browsers will show as a left alignment).
Allows the value of a column in a record with something else, for instance a hyperlink.
syntax: replace:<colname>=<value> OR replace:<colname>=perl;<perlvalue>
<colname> is the name of a column returned from the sql-select querey, as returned by the database. <value> or <perlvalue> can contain $<colname>, which will be translated into the value of the column.
The first variant assumes <value> is html, and will insert <value> into the html, after translating $<colname> into it's value.
The second variant recognises the 'perl;', and assumes <perlvalue> is perl code. First, any $<colname> occurences are translated into their value, and then <perlvalue> is eval'd, and the output is appended to the html. This means that you have to 'return' whatever you want to see in the html.
Allows a view-specific header. Expects pure html as its value.
Allows a view-specific footer. Expects pure html as its value.
Provides a default 'editdest' for a view. That is the destination the user will be redirected to after (s)he clicks on the button.
Controls how many records are shown per page (with the rest being accessible through an automatically generated system of 'next' and 'previous' links). Default: 10.
Some text to add to the page. See also append(no)results (list.cf) and appendedit/appendnew (form.cf)
Some text to add to the page if the query returns no results.
Some text to add to the page if the query returns results.
Columns to hide in the listing, if they match a default value.
syntax: hideifdefault:<colname>=<defaultvalue>
<colname> is the name of a column returned from the sql-select querey, as returned by the database. <defaultvalue> is the default value that, if matched, will prevent the column from being printed.
Columns to hide in the listing.
Determines the name of the column containing the unique identifier for each record in the table.
Allows to set the form element. The default type for each column is 'textbox'. Other options include 'hidden', 'password', 'select', 'textarea', 'onlyshow' and 'showandhidden'. The first four of these options are standard html form elements. 'onlyshow' will not make a form element but only display the value of the column in the table. 'showandhidden' is basically a non-editable field in the table: the value is show, but there is also a hidden form element that will assure that it is passed to the form action script.
The 'textarea' form element has some extra possible syntax. In it's simplest form, it looks like this:
form:status=textarea()
Which will result in the following html:
<textarea name=status>value_of_status</textarea>
However, between the two brackets you may supply options that will be inserted in the textarea tag, like this:
form:status=textarea(cols=50 rows=10 break=hard)
This would result in:
<textarea name=status cols=50 rows=10 break=hard>value_of_status</textarea>
The 'select' form element is a special one. You can select a limited number of options like this:
form:status=select(Yes=1,No=0)
or rather use an sql query to supply the options, like this:
form:peopleid=select(select id,firstname,lastname from people where status='1';id=peopleid;value=#id;show=#firstname #lastname)
As you can see, there are 4 paramaters between the select brackets. They are separated by semicolons. The first one is the sql-query (which has to start with 'select '!). The second parameter indicates which column from the query corresponds to the value of the form element. This is used to select the correct value in the dropdown list by default. The third parameter is what will be used as the 'value' of the select option tag. Note that you can refer to values of the query by preceding their name with a hash (#). The last parameter is what will be shown on the screen in the dropdown box. In this case, two columns are combined.
You can put more than one select() statement in such a form: line, like this (new from v0.51):
form:peopleid=select(John Miles=12,Larry Wall=13);select(select id,firstname,lastname from people where status='1';id=peopleid;value=#id;show=#firstname #lastname)
This will result in 2 fixed people in the list, followed by whoever is stored in the database.
Note that since version 0.52, the 'select' form element is also the only form: element that will be recognised in details.cf and list.cf, with identical syntax. Obviously, the output on the web page will not be a form element but rather only the right value from the select statement.
Also since version 0.52, you can now use the [something #firstname|alternate value] syntax in the 'show' part of a select statement. This works very similar to the syntax you're familiar with from the PR_ and COOKIE_ style parameters. The only difference is that 'alternate value' will be displayed if #firstname would be undefined or equal the empty string, unlike for PR_ and COOKIE_ style parameters, where the alternate value will only be displayed if the parameter is not defined.
Allows a view-specific page header. Expects pure html as its value.
Some text that will be added, depending on whether this is an edit of a record, or rather the addition of a new record.
The table the view applies to.
The 'add' key determines whether journalled adds are allowed. Possible values: 'yes' and 'no'.
The 'delete' key determines whether journalled deletes are allowed. Possible values: 'yes' and 'no'.
The 'update' key determines whether journalled updates are allowed, on a column by column basis. The value of the key is a comma-separated list of columns that may be updated. Updates to columns that are not in this list will simply be ignored (with an appropriate warning in the logs!).
The 'validate' key has a perl expression that can be evaluated to a 1 or 0 as its value. If the expression evaluates to 0, the operation (add or update) will be cancelled, and the matchin 'validatetext' (see below) will be displayed. Note that 'validate' doesn't apply to delete actions.
The 'validatetext' key holds the message corresponding to a validate expression. Validatetext and validate should aways be defined in pairs.
The 'validateifcondition' key holds a perl expression that can be evaluated to a 1 or 0 as its value. If the expression evaluates to 1, the corresponding 'validateif' key will be evaluated. If it is 0, it will be ignored.
The 'validateif' key holds a perl expression that can be evaluated to a 1 or 0 as its value. If the expression evaluates to 0, the operation (add or update) will be cancelled, and the matchin 'validateiftext' (see below) will be displayed. Note that 'validateif' doesn't apply to delete actions, and that it will only be tested if the corresponding 'validateifcondition' evaluates to 1.
The 'validateiftext' key holds the message corresponding to a 'validateif' expression. This message will be displayed if the 'validateif' condition is not met. Validateiftext, validateif and validateifcondition should aways be defined in threesomes.
The 'sqlcondition' key allows definition of an sql condition. Its value contains 4 parts, separated by a pipe symbol. The first part defines when the condition is to be met. It can be 'add', 'update', or a combination of the two, like 'addupdate'. The second part is a standard sql query. The third part is an operator. Possible values are all perl operators, for instance the numeric equality operator '=='. The fourth part is the value of the second part of the condition. Use quotes for strings.
count(*) from people where firstname='$params{firstname}' and lastname='$params{lastname}' and status='1'|==|0
The 'sqlconditiontext' key holds the message corresponding to a 'sqlcondition' expression. This message will be displayed if the 'sqlcondition' condition is not met. Sqlconditiontext and sqlcondition should aways be defined in pairs.
The 'preprocess' key holds a block of perl that will be executed _before_ the insertion of a record in the database by jAdd or jUpdate. It takes three arguments: the name of a database column, a parameter describing when to execute this code (add, update, or addupdate), and a block of perl. This is the syntax:
preprocess:<colname>=<whentoexecute>|perl;<perlcode>
Of course <perlcode> may occupy multiple lines, as long as these lines start with whitespace and as long as there are no empty lines in between the perl code.
The perl code needs to return a value that will replace the value for the column specified.
Example:
preprocess:price=add|perl;
return '$params{price}' if ('$params{price}' ne '');
my @res = &Apache::WeSQL::sqlSelect($dbh,"select price from tbl_productprices where id='$params{productpriceid}' and status='1'");
return $res[0];
The 'layout.cf' file has the following structure:
Optionally, the first line looks like:
inherit:<path-to-another-layout-file>
This line must be followed by an empty line. It will include all tags in the other layout file specified, and then process the ones defined in this file, possibly overriding already defined tags.
Limitation:
1. Nesting is limited to 10 levels, to prevent circular references. 2. The specified path is relative to the URI of the request for the original html page!
Example:
jForm, the sub that deals with jform.wsql calls, jList (jlist.wsql), and jDetails (jdetails.wsql) uses the following layout keys: listheader, listbody, liststarttable1, liststarttable2, liststoptable and listfooter
jLoginForm, the sub that displays the jloginform.wsql page, includes a reference to the 'publiclogon' layout key. By default, this key has the following value:
publiclogon
<tr><td><b>Login</b></td><td align=right><b>Password</b></td><td align=right><b>Superuser</b></td></tr>
<!-- LIST A select login, password, superuser from users where status='1' -->
<tr><td>A_LOGIN</td><td align=right>A_PASSWORD</td><td align=right><!-- EVAL POST if ('A_SUPERUSER' eq 1) { return "Yes"; } else { return "No"; } --></td></tr>
<!-- /LIST A -->
It displays a list of all valid login/password combinations for the sample application. I imagine you will want to change the value of this layout tag if you want to build a real-world system that requires logins!
If you want to do away with this key, just define it in the layout.cf file and give it a value of a single space. You can also remove it from the layout.cf file, but then you will see ugly ``Use of uninitialized value in concatenation (.) or string at /usr/local/lib/perl5/site_perl/5.6.1/Apache/WeSQL/Auth.pm line 157.'' in the log of your webserver.
WeSQL.pm exports a sub called readLayoutFile, which returns a hash with all the layout elements. Also, you can use <!-- LAYOUT TAG --> style tags in your .wsql files, where TAG is the name of the key from the layout file that you want to include in a wsql file.
This module is part of the WeSQL package, version 0.52
(c) 2000-2002 by Ward Vandewege
None by default. Possible: jForm jDetails jList
Ward Vandewege, <[email protected]>
the Apache::WeSQL::Journalled manpage, the Apache::WeSQL manpage, the Apache::WeSQL::SqlFunc manpage, the Apache::WeSQL::AppHandler manpage