view release on metacpan or  search on metacpan

DBIx/DBStag/Cookbook.pm  view on Meta::CPAN

  where c1.continent != c2.continent
  order by c1.name, c2.name
  use nesting (set(c1(c2)));

(the final clause is a DBStag SQL extension - it nests country c2
under country c1)

Then query for XML

  selectall_xml.pl -d dbi:Pg:dbname=cia -f myquery.sql > myresults.xml

=head3 SQL to XML via the Interactive Query Shell

Query the data using the stag query shell (qsh). You type in SQL
queries, and get results back as XML (or any other tree format, such
as indented text or S-Expressions).

The following can be cut and pasted directly onto the unix command
line:

Simple query rooted at B<country>:

  stag-qsh -d dbi:Pg:dbname=cia
  \l
  SELECT * FROM country INNER JOIN country_coasts USING (country_id)
  WHERE country.name = 'France';

(type \q to quit stag-qsh)

Or a more advanced query, still rooted at B<country>

  stag-qsh -d dbi:Pg:dbname=cia
  \l
  SELECT *
  FROM country
       LEFT OUTER JOIN religions USING (country_id)
       LEFT OUTER JOIN languages USING (country_id)
       INNER JOIN continent ON (continent.name=country.continent)
  WHERE continent.ciaid = 'australia'
  USE NESTING (set(country(religions)(languages)(continent)));

See L<DBIx::DBStag> for more details on fetching hierarchical data
from relational database

=head2 USING TEMPLATES

If you have a particular pattern of SQL you execute a lot, you can
reuse this SQL by creating B<templates>

=head3 Creating Templates

First create a place for your templates:

  mkdir ./templates

(do not change directory after this)

The following command specifies a colon-separated path for directories
containing templates (all templates must end with .stg)

  setenv DBSTAG_TEMPLATE_DIRS ".:templates:/usr/local/share/sql/templates"

Auto-generate templates (you can customize these later):

  stag-autoschema.pl -w sxpr cia-pp2.xml > cia-stagschema.sxpr
  stag-autotemplate.pl -no_pp -s cia -dir ./templates  cia-stagschema.sxpr

The first command creates an S-Expression representation of the
Schema; the second generates SQL templates from these.

You may wish to examine a template:

  more templates/cia-country.stg

You can hand generate as many templates as you like; see
L<DBIx::DBStag::SQLTemplate> for more details

For more example templates for this schema, see
L<http://www.godatabase.org/cgi-bin/wfb/ubiq.cgi>

=head3 Executing Templates from the Command Line

now execute a template from the command line:

  selectall_xml.pl -d dbi:Pg:dbname=cia /cia-country country_name=Austria

You should get back a tree (rooted in B<country>), that looks similar
to this:

  <set>
    <country>
      <country_id>3</country_id>
      <government>federal republic</government>
      <population>8023244</population>
      <total_area>83850</total_area>
      <name>Austria</name>
      <inflation>2.3</inflation>
      ...
      <languages>
        <languages_id>1</languages_id>
        <name>German</name>
        <num>100</num>
        <country_id>3</country_id>
      </languages>
      ...

=head3 Executing Templates with the Stag Query Shell

You can also do this interactively using qsh

First, we need to inform stag-qsh what the schema is. The schema is
used to determine which templates are appropriate. Later we will
discover how to set up a resources file, which will allow stag to
infer the schema.

Call qsh from command line:

  stag-qsh -d dbi:Pg:dbname=cia -s cia

Interactive perl/qsh: