NAME

         DBIx::DBStag::SQLTemplate - A Template for an SQL query

SYNOPSIS

         # 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"});

DESCRIPTION

       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 SQL; for example:

       Example template 1
             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))))

           That's all! However, there are ways to make your template more useful

       Example template 2
             :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 SQL (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

   GETTING A TEMPLATE
       The DBIx::DBStag object gives various methods for fetching templates by name, by database
       or by schema

   VARIABLES
       WHERE 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

   OPTIONAL BLOCKS
         WHERE [ foo = &foo& ]

       If foo is not bound then the part between the square brackets is left out

       Multiple option blocks are ANDed together

       An option block need not contain a variable - if it contains no &variable& name it is
       automatically ANDed

   BINDING OPERATORS
       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 ARRAY

METHODS

   name
         Usage   - $name = $template->name
         Returns - str
         Args    -

       every template has a name that (should) uniquely identify it

   desc
         Usage   - $desc = $template->desc
         Returns - str
         Args    -

       templates have optional descriptions

   get_varnames
         Usage   - $varnames = $template->get_varnames
         Returns - listref of strs
         Args    -

       Returns the names of all variable used in this template

WEBSITE

       <http://stag.sourceforge.net>

AUTHOR

       Chris Mungall <cjm@fruitfly.org>

COPYRIGHT

       Copyright (c) 2003 Chris Mungall

       This module is free software.  You may distribute this module under the same terms as perl
       itself