DBIx::...emplate(3pm)

💖 DBIx::DBStag::SQLTemplate — a template for an SQL query 💖

okay bestie, so this page is DBIx::...emplate(3pm) from the User Contributed Perl Documentation, and it's literally all about DBIx::DBStag::SQLTemplate — canned queries that you can fill in with values. it's giving "mad libs but for SQL" and honestly? iconic. 🎀

---

✨ SYNOPSIS ✨

first up, the vibe check — how you actually use one of these bad boys:

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

that's it. that's the whole flow. slay. 💅

---

🌸 DESCRIPTION 🌸

so a template is basically a canned query that can be parameterized. like, you write it once, and then you plug different values into the little blanks. lowkey genius.

templates get collected in directories (and fr fr, in future it will be possible to store them in files or in the db itself — but not yet, don't come for me).

to tell DBStag where your templates are, you should set:

setenv DBSTAG_TEMPLATE_DIRS "$HOME/mytemplates:/data/bioconf/templates"

⚠️ your templates have to end with the suffix .stg, otherwise they will not be picked up. periodt. no .stg, no service. 💀

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 — yes, dashes between fields, underscores inside them, iykyk)

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 — though ngl this is not necessary for DBStag itself. it's just a nice-to-have.

after the // you can add tag: value data. so cute, so organized.

you should set schema: if you want the template to be available to users of a db that conforms to that schema. like, gatekeeping who gets to use it. 💅

🔍 GETTING A TEMPLATE

the DBIx::DBStag object gives various methods for fetching templates by name, by database or by schema. choose your fighter.

---

🌈 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 — like a lil queue, first-come-first-served. 💖

---

🌟 OPTIONAL BLOCKS 🌟

WHERE [ foo = &foo& ]

if foo is not bound then the part between the square brackets is left out. poof, gone, no notes. ✨

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. yes, the operator. we're flexing now:

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. three plot twists, one template. 😭

---

👑 METHODS 👑

name

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

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

desc

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

templates have optional descriptions. optional, but like, please add them.

get_varnames

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

returns the names of all variable used in this template. all of them. every single one. 🔥

---

🌐 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.

---

and that's the whole page, bestie — brought to you by perl v5.36.0, dated 2022-10-14, and lovingly filed under DBIx::...emplate(3pm). go write some .stg templates and eat. 🫶✨