|
XML taglib for database queries
|
|
By sleeper22 , Section Code []
Posted on Mon Sep 17, 2001 at 12:00:00 PM PST
|
 |
|
In an effort to separate the task of the perl programmer and that
of other site authors such as graphic editors and UI specialists -- i.e. extract
the layout from the logic -- here's a box (i'm calling it dbquery) which allows
db queries to be put in xml-style markup.
|
Using this system for some areas of the site provides a way for an admin to
insert and/or manipulate html and basic sql, without knowing perl.
This is not a thorough implementation... more of a trial of concept,
to see what y'all have to say about the matter.
it only handles one select statement per "template"; a more powerful version would
support nested queries and what not, possibly following the syntax of ESQL used in
the cocoon project.
The xml-ish (or xsp-ish) markup is kept in a block, whose id is specified when
calling the dbquery box. Supported tags are
<db:select> (which uses the same format as $S->db_select)
<db:results> (whatever is in this element will be repeated with every record,
and field names in the format "|$fieldname|" will be interpolated.
a <db:no-results> tag would be handy too, whose contents should be shown
when no records match.
NEW BLOCK
1.
Make a template containing the xmlish markup. for example, here's a block
that will contain the number of current users on the system, when called
via the dbquery box.
id: num_users:
content:
<db:select what="count(*)" from="users" where="uid>0">
<db:results>
|$count(*)| registered users
</db:results>
</db:select>
2.
In any existing block: wherever you want the output to appear, use
|BOX,dbquery,<template>|
where <template> is the id of the block containing the query info in xml.
eg. num_users
NEW BOX
id: dbquery
desc: parses a block (specified as argument) that contains sql tags,
and executes the query.
content:
my $block = $ARGS[0];
my $content = $S->{UI}->{BLOCKS}->{$block} or return;
my($pre,$query,$region,$post) =
($content =~ m!^(.*?)<db:select\s([^>]+)>(.*)</db:select>(.*)$!si)
or return $content;
my($rv,$sth) = runQuery($S, $query);
$region =~ s!<db:results>(.*?)</db:results>!results($sth, $1)!se;
return $pre . $region . $post;
sub runQuery {
my($S, $q) = @_;
my $gt = '\&' . 'gt;';
my $lt = '\&' . 'lt;';
my %args;
foreach my $argname (qw(FROM WHAT WHERE DISTINCT ORDER GROUP)) {
next unless $q =~ /$argname=([^\'\"\s]+)/si or
$q =~ /$argname=([\'\"])(.+?)\1/si;
$args{$argname} = $2 \|\| $1;
$args{$argname} =~ s/$gt/>/go;
$args{$argname} =~ s/$lt/</go;
}
return ($S->db_select(\%args));
}
sub results {
my($sth, $source) = @_;
my $new = '';
while (my $data = $sth->fetchrow_hashref) {
(my $record = $source) =~ s!\W\$(\w+)\W!$data->{$1}!g;
$new .= $record;
}
return $new;
}
|
|
|
Story Views
|
|
24 Scoop users have viewed this story.
|
|
