# find template by name $template = $dbh->find_template("mydb-personq");
# execute this template, filling in the 'name' attribute $xml = $dbh->selectall_xml(-template=>$template, -bind=>{name => "fred"});
A template represents a canned query that can be parameterized.
Templates are collected in directories (in future it will be possible to store them in files or in the db itself).
To tell DBStag where your templates are, you should set:
setenv DBSTAG_TEMPLATE_DIRS "$HOME/mytemplates:/data/bioconf/templates"
Your templates should end with the suffix .stg, otherwise they will not be picked up
You can name templates any way you like, but the standard way is to use 2 or 3 fields
SCHEMA-OBJECT
or
SCHEMA-OBJECT-QUALIFIERS
(with underscores used within fields)
A template file should contain at minimum some \s-1SQL\s0; for example:
SELECT studio.*, movie.*, star.* FROM studio NATURAL JOIN movie NATURAL JOIN movie_to_star NATURAL JOIN star WHERE [movie.genre = &genre&] [star.lastname = &lastname&] USE NESTING (set(studio(movie(star)))) Thats all! However, there are ways to make your template more useful
:SELECT studio.*, movie.*, star.* :FROM studio NATURAL JOIN movie NATURAL JOIN movie_to_star NATURAL JOIN star :WHERE [movie.genre = &genre&] [star.lastname = &lastname&] :USE NESTING (set(studio(movie(star))))
// schema: movie desc: query for fetching movies By including : at the beginning it makes it easier for parsers to assemble \s-1SQL\s0 (this is not necessary for DBStag however) After the // you can add tag: value data. You should set schema: if you want the template to be available to users of a db that conforms to that schema
The DBIx::DBStag object gives various methods for fetching templates by name, by database or by schema
\s-1WHERE\s0 clause variables in the template look like this
&foo&
variables are bound at query time
my $set = $dbh->selectall_stag(-template=>$t, -bind=>["bar"]);
or
my $set = $dbh->selectall_stag(-template=>$t, -bind=>{foo=>"bar"});
If the former is chosen, variables are bound from the bind list as they are found
WHERE [ foo = &foo& ]
If foo is not bound then the part between the square brackets is left out
Multiple option blocks are \s-1AND\s0ed together
An option block need not contain a variable - if it contains no &variable& name it is automatically \s-1AND\s0ed
The operator can be bound at query time too
WHERE [ foo => &foo& ]
Will become either
WHERE foo = ?
or
WHERE foo LIKE ?
or
WHERE foo IN (f0, f1, ..., fn)
Depending on whether foo contains the % character, or if foo is bound to an \s-1ARRAY\s0
Usage - $name = $template->name Returns - str Args -
every template has a name that (should) uniquely identify it
Usage - $desc = $template->desc Returns - str Args -
templates have optional descriptions
Usage - $varnames = $template->get_varnames Returns - listref of strs Args -
Returns the names of all variable used in this template
<http://stag.sourceforge.net>
Chris Mungall <[email protected]>
Copyright (c) 2003 Chris Mungall
This module is free software. You may distribute this module under the same terms as perl itself