.\" Automatically generated by Pod::Man 2.28 (Pod::Simple 3.29)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBIx::DBStag::SQLTemplate 3pm"
.TH DBIx::DBStag::SQLTemplate 3pm "2016-05-29" "perl v5.22.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
.Vb 1
\&  DBIx::DBStag::SQLTemplate \- A Template for an SQL query
.Ve
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\&  # find template by name
\&  $template = $dbh\->find_template("mydb\-personq");
\&
\&  # execute this template, filling in the \*(Aqname\*(Aq attribute
\&  $xml = $dbh\->selectall_xml(\-template=>$template, 
\&                             \-bind=>{name => "fred"});
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
A template represents a canned query that can be parameterized.
.PP
Templates are collected in directories (in future it will be possible
to store them in files or in the db itself).
.PP
To tell DBStag where your templates are, you should set:
.PP
.Vb 1
\&  setenv DBSTAG_TEMPLATE_DIRS "$HOME/mytemplates:/data/bioconf/templates"
.Ve
.PP
Your templates should end with the suffix \fB.stg\fR, otherwise they will
not be picked up
.PP
You can name templates any way you like, but the standard way is to
use 2 or 3 fields
.PP
.Vb 1
\&  SCHEMA\-OBJECT
.Ve
.PP
or
.PP
.Vb 1
\&  SCHEMA\-OBJECT\-QUALIFIERS
.Ve
.PP
(with underscores used within fields)
.PP
A template file should contain at minimum some \s-1SQL\s0; for example:
.IP "Example template 1" 4
.IX Item "Example template 1"
.Vb 12
\&  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))))
.Ve
.Sp
That's all! However, there are ways to make your template more useful
.IP "Example template 2" 4
.IX Item "Example template 2"
.Vb 12
\&  :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
.Ve
.Sp
By including \fB:\fR at the beginning it makes it easier for parsers to
assemble \s-1SQL \s0(this is not necessary for DBStag however)
.Sp
After the // you can add tag: value data.
.Sp
You should set \fBschema:\fR if you want the template to be available to
users of a db that conforms to that schema
.SS "\s-1GETTING A TEMPLATE\s0"
.IX Subsection "GETTING A TEMPLATE"
The DBIx::DBStag object gives various methods for fetching
templates by name, by database or by schema
.SS "\s-1VARIABLES\s0"
.IX Subsection "VARIABLES"
\&\s-1WHERE\s0 clause variables in the template look like this
.PP
.Vb 1
\&  &foo&
.Ve
.PP
variables are bound at query time
.PP
.Vb 2
\&  my $set = $dbh\->selectall_stag(\-template=>$t,
\&                                 \-bind=>["bar"]);
.Ve
.PP
or
.PP
.Vb 2
\&  my $set = $dbh\->selectall_stag(\-template=>$t,
\&                                 \-bind=>{foo=>"bar"});
.Ve
.PP
If the former is chosen, variables are bound from the bind list as
they are found
.SS "\s-1OPTIONAL BLOCKS\s0"
.IX Subsection "OPTIONAL BLOCKS"
.Vb 1
\&  WHERE [ foo = &foo& ]
.Ve
.PP
If foo is not bound then the part between the square brackets is left out
.PP
Multiple option blocks are \fB\s-1AND\s0\fRed together
.PP
An option block need not contain a variable \- if it contains no
\&\fB&variable&\fR name it is automatically \fB\s-1AND\s0\fRed
.SS "\s-1BINDING OPERATORS\s0"
.IX Subsection "BINDING OPERATORS"
The operator can be bound at query time too
.PP
.Vb 1
\&  WHERE [ foo => &foo& ]
.Ve
.PP
Will become either
.PP
.Vb 1
\&  WHERE foo = ?
.Ve
.PP
or
.PP
.Vb 1
\&  WHERE foo LIKE ?
.Ve
.PP
or
.PP
.Vb 1
\&  WHERE foo IN (f0, f1, ..., fn)
.Ve
.PP
Depending on whether foo contains the % character, or if foo is bound
to an \s-1ARRAY\s0
.SH "METHODS"
.IX Header "METHODS"
.SS "name"
.IX Subsection "name"
.Vb 3
\&  Usage   \- $name = $template\->name
\&  Returns \- str
\&  Args    \-
.Ve
.PP
every template has a name that (should) uniquely identify it
.SS "desc"
.IX Subsection "desc"
.Vb 3
\&  Usage   \- $desc = $template\->desc
\&  Returns \- str
\&  Args    \-
.Ve
.PP
templates have optional descriptions
.SS "get_varnames"
.IX Subsection "get_varnames"
.Vb 3
\&  Usage   \- $varnames = $template\->get_varnames
\&  Returns \- listref of strs
\&  Args    \-
.Ve
.PP
Returns the names of all variable used in this template
.SH "WEBSITE"
.IX Header "WEBSITE"
<http://stag.sourceforge.net>
.SH "AUTHOR"
.IX Header "AUTHOR"
Chris Mungall <\fIcjm@fruitfly.org\fR>
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
Copyright (c) 2003 Chris Mungall
.PP
This module is free software.
You may distribute this module under the same terms as perl itself