.\" 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 3pm"
.TH DBIx::DBStag 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 \- Relational Database to Hierarchical (Stag/XML) Mapping
.Ve
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 10
\&  use DBIx::DBStag;
\&  my $dbh = DBIx::DBStag\->connect("dbi:Pg:dbname=moviedb");
\&  my $sql = q[
\&              SELECT 
\&               studio.*,
\&               movie.*,
\&               star.*
\&              FROM
\&               studio NATURAL JOIN 
\&               movie NATURAL JOIN
\&               movie_to_star NATURAL JOIN
\&               star
\&              WHERE
\&               movie.genre = \*(Aqsci\-fi\*(Aq AND star.lastname = \*(AqFisher\*(Aq
\&              USE NESTING
\&               (set(studio(movie(star))))
\&             ];
\&  my $dataset = $dbh\->selectall_stag($sql);
\&  my @studios = $dataset\->get_studio;
\&
\&  # returns nested data that looks like this \-
\&  #
\&  # (studio
\&  #  (name "20th C Fox")
\&  #  (movie
\&  #   (name "star wars") (genre "sci\-fi")
\&  #   (star
\&  #    (firstname "Carrie")(lastname "Fisher")))))
\&
\&  # iterate through result tree \-
\&  foreach my $studio (@studios) {
\&        printf "STUDIO: %s\en", $studio\->get_name;
\&        my @movies = $studio\->get_movie;
\&
\&        foreach my $movie (@movies) {
\&            printf "  MOVIE: %s (genre:%s)\en", 
\&              $movie\->get_name, $movie\->get_genre;
\&            my @stars = $movie\->get_star;
\&
\&            foreach my $star (@stars) {
\&                printf "    STARRING: %s:%s\en", 
\&                  $star\->get_firstname, $star\->get_lastname;
\&            }
\&        }
\&  }
\&  
\&  # manipulate data then store it back in the database
\&  my @allstars = $dataset\->get("movie/studio/star");
\&  $_\->set_fullname($_\->get_firstname.\*(Aq \*(Aq.$_\->get_lastname)
\&    foreach(@allstars);
\&
\&  $dbh\->storenode($dataset);
\&  exit 0;
.Ve
.PP
Or from the command line:
.PP
.Vb 4
\&  unix> selectall_xml.pl \-d \*(Aqdbi:Pg:dbname=moviebase\*(Aq     \e
\&       \*(AqSELECT * FROM studio NATURAL JOIN movie NATURAL   \e
\&          JOIN movie_to_star NATURAL JOIN star            \e
\&          USE NESTING (set(studio(movie(star))))\*(Aq
.Ve
.PP
Or using a predefined template:
.PP
.Vb 1
\&  unix> selectall_xml.pl \-d moviebase /mdb\-movie genre=sci\-fi
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This module is for mapping between relational databases and Stag
objects (Structured Tags \- see Data::Stag). Stag objects can also
be represented as \s-1XML.\s0 The module has two main uses:
.IP "Querying" 4
.IX Item "Querying"
This module can take the results of any \s-1SQL\s0 query and decompose the
flattened results into a tree data structure which reflects the
foreign keys in the underlying relational schema. It does this by
looking at the \s-1SQL\s0 query and introspecting the database schema, rather
than requiring metadata or an object model.
.Sp
In this respect, the module works just like a regular \s-1DBI\s0 handle, with
a few extra methods.
.Sp
Queries can also make use of predefined \fBtemplates\fR
.IP "Storing Data" 4
.IX Item "Storing Data"
DBStag objects can store any tree-like datastructure (such as \s-1XML\s0
documents) into a database using normalized schema that reflects the
structure of the tree being stored. This is done using little or no
metadata.
.Sp
\&\s-1XML\s0 can also be imported, and a relational schema automatically generated.
.PP
For a tutorial on using DBStag to build and query relational databases
from \s-1XML\s0 sources, please see DBIx::DBStag::Cookbook
.SS "\s-1HOW QUERY RESULTS ARE TURNED INTO STAG/XML\s0"
.IX Subsection "HOW QUERY RESULTS ARE TURNED INTO STAG/XML"
This is a general overview of the rules for turning \s-1SQL\s0 query results
into a tree like data structure. You don't need to understand all
these rules to be able to use this module \- you can experiment by
using the \fBselectall_xml.pl\fR script which comes with this
distribution.
.PP
\fIMapping Relations\fR
.IX Subsection "Mapping Relations"
.PP
Relations (i.e. tables and views) are elements (nodes) in the
tree. The elements have the same name as the relation in the database.
.PP
These nodes are always non-terminal (ie they always have child nodes)
.PP
\fIMapping Columns\fR
.IX Subsection "Mapping Columns"
.PP
Table and view columns of a relation are sub-elements of the table or
view to which they belong. These elements will be \fBdata elements\fR
(i.e. terminal nodes). Only the columns selected in the \s-1SQL\s0 query
will be present.
.PP
For example, the following query
.PP
.Vb 1
\&  SELECT name, job FROM person;
.Ve
.PP
will return a data structure that looks like this:
.PP
.Vb 7
\&  (set
\&   (person
\&    (name "fred")
\&    (job "forklift driver"))
\&   (person
\&    (name "joe")
\&    (job "steamroller mechanic")))
.Ve
.PP
The data is shown as a lisp-style S\-Expression \- it can also be
expressed as \s-1XML,\s0 or manipulated as an object within perl.
.PP
\fIHandling table aliases\fR
.IX Subsection "Handling table aliases"
.PP
If an \s-1ALIAS\s0 is used in the \s-1FROM\s0 part of the \s-1SQL\s0 query, the relation
element will be nested inside an element with the same name as the
alias. For instance, the query
.PP
.Vb 1
\&  SELECT name FROM person AS author WHERE job = \*(Aqauthor\*(Aq;
.Ve
.PP
Will return a data structure like this:
.PP
.Vb 4
\&  (set
\&   (author
\&    (person
\&     (name "Philip K Dick"))))
.Ve
.PP
The underlying assumption is that aliasing is used for a purpose in
the original query; for instance, to determine the context of the
relation where it may be ambiguous.
.PP
.Vb 4
\&  SELECT *
\&  FROM person AS employee 
\&           INNER JOIN 
\&       person AS boss ON (employee.boss_id = boss.person_id)
.Ve
.PP
Will generate a nested result structure similar to this \-
.PP
.Vb 11
\&  (set
\&   (employee
\&    (person
\&     (person_id "...")
\&     (name "...")
\&     (salary  "...")
\&     (boss
\&      (person
\&       (person_id "...")
\&       (name "...")
\&       (salary  "..."))))))
.Ve
.PP
If we neglected the alias, we would have 'person' directly nested
under 'person', and the meaning would not be obvious. Note how the
contents of the \s-1SQL\s0 query dynamically modifies the schema/structure of
the result tree.
.PP
\fI\s-1NOTE ON SQL SYNTAX\s0\fR
.IX Subsection "NOTE ON SQL SYNTAX"
.PP
Right now, DBStag is fussy about how you specify aliases; you must use
\&\fB\s-1AS\s0\fR \- you must say
.PP
.Vb 1
\&  SELECT name FROM person AS author;
.Ve
.PP
instead of
.PP
.Vb 1
\&  SELECT name FROM person author;
.Ve
.PP
\fINesting of relations\fR
.IX Subsection "Nesting of relations"
.PP
The main utility of querying using this module is in retrieving the
nested relation elements from the flattened query results. Given a
query over relations A, B, C, D,... there are a number of possible
tree structures. Not all of the tree structures are meaningful or
useful.
.PP
Usually it will make no sense to nest A under B if there is no foreign
key relationship linking either A to B, or B to A. This is not always
the case \- it may be desirable to nest A under B if there is an
intermediate linking table that is required at the relational level
but not required in the tree structure.
.PP
DBStag will guess a structure/schema based on the ordering of the
relations in your \s-1FROM\s0 clause. However, this guess can be over-ridden
at either the \s-1SQL\s0 level (using DBStag specific \s-1SQL\s0 extensions) or at
the \s-1API\s0 level.
.PP
The default algorithm is to nest each relation element under the
relation element preceding it in the \s-1FROM\s0 clause; for instance:
.PP
.Vb 1
\&  SELECT * FROM a NATURAL JOIN b NATURAL JOIN c
.Ve
.PP
If there are appropriately named foreign keys, the following data will
be returned (assuming one column 'x_foo' in each of a, b and c)
.PP
.Vb 7
\&  (set
\&   (a
\&    (a_foo "...")
\&    (b
\&     (b_foo "...")
\&     (c
\&      (c_foo "...")))))
.Ve
.PP
where 'x_foo' is a column in relation 'x'
.PP
This is not always desirable. If both b and c have foreign keys into
table a, DBStag will not detect this \- you have to guide it. There are
two ways of doing this \- you can guide by bracketing your \s-1FROM\s0 clause
like this:
.PP
.Vb 1
\&  SELECT * FROM (a NATURAL JOIN b) NATURAL JOIN c
.Ve
.PP
This will generate
.PP
.Vb 7
\&  (set
\&   (a
\&    (a_foo "...")
\&    (b
\&     (b_foo "..."))
\&    (c
\&     (c_foo "..."))))
.Ve
.PP
Now b and c are siblings in the tree. The algorithm is similar to
before: nest each relation element under the relation element
preceding it; or, if the preceding item in the \s-1FROM\s0 clause is a
bracketed structure, nest it under the first relational element in the
bracketed structure.
.PP
(Note that in MySQL you may not place brackets in the \s-1FROM\s0 clause in
this way)
.PP
Another way to achieve the same thing is to specify the desired tree
structure using a DBStag specific \s-1SQL\s0 extension. The DBStag specific
component is removed from the \s-1SQL\s0 before being presented to the
\&\s-1DBMS.\s0 The extension is the \fB\s-1USE NESTING\s0\fR clause, which should come at
the end of the \s-1SQL\s0 query (and is subsequently removed before
processing by the \s-1DBMS\s0).
.PP
.Vb 3
\&  SELECT * 
\&  FROM a NATURAL JOIN b NATURAL JOIN c 
\&  USE NESTING (set (a (b)(c)));
.Ve
.PP
This will generate the same tree as above (i.e. 'b' and 'c' are
siblings). Notice how the nesting in the clause is the same as the
nesting in the resulting tree structure.
.PP
Note that 'set' is not a table in the underlying relational schema \-
the result data tree requires a named top level node to group all the
\&'a' relations under. You can call this top level element whatever you
like.
.PP
If you are using the DBStag \s-1API\s0 directly, you can pass in the nesting
structure as an argument to the select call; for instance:
.PP
.Vb 5
\&  my $xmlstr =
\&    $dbh\->selectall_xml(\-sql=>q[SELECT * 
\&                                FROM a NATURAL JOIN b 
\&                                     NATURAL JOIN c],
\&                        \-nesting=>\*(Aq(set (a (b)(c)))\*(Aq);
.Ve
.PP
or the equivalent \-
.PP
.Vb 5
\&  my $xmlstr =
\&    $dbh\->selectall_xml(q[SELECT * 
\&                          FROM a NATURAL JOIN b 
\&                               NATURAL JOIN c],
\&                        \*(Aq(set (a (b)(c)))\*(Aq);
.Ve
.PP
If you like, you can also use \s-1XML\s0 here (only at the \s-1API\s0 level, not at
the \s-1SQL\s0 level) \-
.PP
.Vb 12
\&  my $seq =
\&    $dbh\->selectall_xml(\-sql=>q[SELECT * 
\&                                FROM a NATURAL JOIN b 
\&                                     NATURAL JOIN c],
\&                        \-nesting=>q[
\&                                    <set>
\&                                      <a>
\&                                        <b></b>
\&                                        <c></c>
\&                                      </a>
\&                                    </set>
\&                                   ]);
.Ve
.PP
As you can see, this is a little more verbose than the S\-Expression
.PP
Most command line scripts that use this module should allow
pass-through via the '\-nesting' switch.
.PP
\fIAliasing of functions and expressions\fR
.IX Subsection "Aliasing of functions and expressions"
.PP
If you alias a function or an expression, DBStag needs to know where
to put the resulting column; the column must be aliased.
.PP
This is inferred from the first named column in the function or
expression; for example, the \s-1SQL\s0 below uses the minus function:
.PP
.Vb 1
\&  SELECT blah.*, foo.*, foo.x\-foo.y AS z
.Ve
.PP
The \fBz\fR element will be nested under the \fBfoo\fR element
.PP
You can force different nesting using a \fBdouble underscore\fR:
.PP
.Vb 1
\&  SELECT blah.*, foo.*, foo.x \- foo.y AS blah_\|_z
.Ve
.PP
This will nest the \fBz\fR element under the \fBblah\fR element
.PP
If you would like to override this behaviour and use the alias as the
element name, pass in the \-aliaspolicy=>'a' arg to the \s-1API\s0 call. If
you wish to use the table names without nesting, use \-aliaspolicy=>'t'.
.SS "Conformance to DTD/XML\-Schema"
.IX Subsection "Conformance to DTD/XML-Schema"
DBStag returns Data::Stag structures that are equivalent to a
simplified subset of \s-1XML \s0(and also a simplified subset of lisp
S\-Expressions).
.PP
These structures are examples of \fBsemi-structured data\fR \- a good
reference is this book \-
.PP
.Vb 3
\&  Data on the Web: From Relations to Semistructured Data and XML
\&  Serge Abiteboul, Dan Suciu, Peter Buneman
\&  Morgan Kaufmann; 1st edition (January 2000)
.Ve
.PP
The schema for the resulting Stag structures can be seen to conform to
a schema that is dynamically determined at query-time from the
underlying relational schema and from the specification of the query itself.
.PP
If you need to generate a \s-1DTD\s0 you can ause the \fBstag\-autoschema.pl\fR
script, which is part of the Data::Stag distribution
.SH "QUERY METHODS"
.IX Header "QUERY METHODS"
The following methods are for using the DBStag \s-1API\s0 to query a database
.SS "connect"
.IX Subsection "connect"
.Vb 3
\&  Usage   \- $dbh = DBIx::DBStag\->connect($DSN);
\&  Returns \- L<DBIx::DBStag>
\&  Args    \- see the connect() method in L<DBI>
.Ve
.PP
This will be the first method you call to initiate a DBStag object
.PP
The \s-1DSN\s0 may be a standard \s-1DBI DSN,\s0 or it can be a DBStag alias
.SS "selectall_stag"
.IX Subsection "selectall_stag"
.Vb 9
\& Usage   \- $stag = $dbh\->selectall_stag($sql);
\&           $stag = $dbh\->selectall_stag($sql, $nesting_clause);
\&           $stag = $dbh\->selectall_stag(\-template=>$template,
\&                                        \-bind=>{%variable_bindinfs});
\& Returns \- L<Data::Stag>
\& Args    \- sql string, 
\&           [nesting string], 
\&           [bind hashref],
\&           [template DBIx::DBStag::SQLTemplate]
.Ve
.PP
Executes a query and returns a Data::Stag structure
.PP
An optional nesting expression can be passed in to control how the
relation is decomposed into a tree. The nesting expression can be \s-1XML\s0
or an S\-Expression; see above for details
.SS "selectall_xml"
.IX Subsection "selectall_xml"
.Vb 3
\& Usage   \- $xml = $dbh\->selectall_xml($sql);
\& Returns \- string
\& Args    \- See selectall_stag()
.Ve
.PP
As \fIselectall_stag()\fR, but the results are transformed into an \s-1XML\s0 string
.SS "selectall_sxpr"
.IX Subsection "selectall_sxpr"
.Vb 3
\& Usage   \- $sxpr = $dbh\->selectall_sxpr($sql);
\& Returns \- string
\& Args    \- See selectall_stag()
.Ve
.PP
As \fIselectall_stag()\fR, but the results are transformed into an
S\-Expression string; see Data::Stag for more details.
.SS "selectall_sax"
.IX Subsection "selectall_sax"
.Vb 3
\& Usage   \- $dbh\->selectall_sax(\-sql=>$sql, \-handler=>$sax_handler);
\& Returns \- string
\& Args    \- sql string, [nesting string], handler SAX
.Ve
.PP
As \fIselectall_stag()\fR, but the results are transformed into \s-1SAX\s0 events
.PP
[currently this is just a wrapper to selectall_xml but a genuine event
generation model will later be used]
.SS "selectall_rows"
.IX Subsection "selectall_rows"
.Vb 3
\& Usage   \- $tbl = $dbh\->selectall_rows($sql);
\& Returns \- arrayref of arrayref
\& Args    \- See selectall_stag()
.Ve
.PP
As \fIselectall_stag()\fR, but the results of the \s-1SQL\s0 query are left
undecomposed and unnested. The resulting structure is just a flat
table; the first row is the column headings. This is similar to
\&\s-1DBI\-\s0>\fIselectall_arrayref()\fR. The main reason to use this over the direct
\&\s-1DBI\s0 method is to take advantage of other stag functionality, such as
templates
.SS "prepare_stag \s-1PRIVATE METHOD\s0"
.IX Subsection "prepare_stag PRIVATE METHOD"
.Vb 3
\& Usage   \- $prepare_h = $dbh\->prepare_stag(\-template=>$template);
\& Returns \- hashref (see below)
\& Args    \- See selectall_stag()
.Ve
.PP
Returns a hashref
.PP
.Vb 8
\&      {
\&       sth=>$sth,
\&       exec_args=>\e@exec_args,
\&       cols=>\e@cols,
\&       col_aliases_ordered=>\e@col_aliases_ordered,
\&       alias=>$aliasstruct,
\&       nesting=>$nesting
\&      };
.Ve
.SH "STORAGE METHODS"
.IX Header "STORAGE METHODS"
The following methods are for using the DBStag \s-1API\s0 to store nested
data in a database
.SS "storenode"
.IX Subsection "storenode"
.Vb 3
\&  Usage   \- $dbh\->storenode($stag);
\&  Returns \- 
\&  Args    \- L<Data::Stag>
.Ve
.PP
\&\s-1SEE ALSO:\s0 The \fBstag\-storenode.pl\fR script
.PP
Recursively stores a stag tree structure in the database.
.PP
The database schema is introspected for most of the mapping data, but
you can supply your own (see later)
.PP
The Stag tree/XML must be a direct mapping of the relational
schema. Column and table names must correspond to element
names. Elements may be nested. Different styles of XML-Relational
mapping may be used: XORT-style and the more compact Stag-style
.PP
\fIXORT-style mapping\fR
.IX Subsection "XORT-style mapping"
.PP
With a XORT-style mapping, elements corresponding to tables can be
nested under elements corresponding to foreign keys.
.PP
For example, if the relational schema has a foreign key from table
\&\fBperson\fR to table \fBaddress\fR, the following \s-1XML\s0 is permissable:
.PP
.Vb 7
\&  <person>
\&    <name>..</name>
\&    <address_id>
\&      <address>
\&      </address>
\&    </address_id>
\&  </person>
.Ve
.PP
The \fBaddress\fR node will be stored in the database and collapsed to
whatever the value of the primary key is.
.PP
\fIStag-style mapping\fR
.IX Subsection "Stag-style mapping"
.PP
Stag-style is more compact, but sometimes relies on the presence of a
\&\fBdbstag_metadata\fR element to specify how foreign keys are mapped
.PP
\fIOperations\fR
.IX Subsection "Operations"
.PP
Operations are specified as attributes inside elements, specifying
whether the nod should be inserted, updated, looked up or
stored/forced. Operations are optional (default is force/store).
.PP
.Vb 7
\&  <person op="insert">
\&   <name>fred</name>
\&   <address_id op="lookup">
\&    <streetaddr>..</>
\&    <city>..</>
\&   </address_id>
\&  </person>
.Ve
.PP
The above will always insert into the person table (which may be quite
dangerous; if an entry with the same unique constraint exists, an
error will be thrown). Assuming (streetaddr,city) is a unique
constraint for the address table, this will lookup the specified
address (and not modify the table) and use the returned pk value for
the \fBperson.address_id\fR foreign key
.PP
The operations are:
.IP "force (default)" 4
.IX Item "force (default)"
looks up (by unique constraints) first; if exists, will do an
update. if does not exist, will do an insert
.IP "insert" 4
.IX Item "insert"
insert only. \s-1DBMS\s0 will throw error if row with same \s-1UC\s0 exists
.IP "update" 4
.IX Item "update"
update only. \s-1DBMS\s0 will throw error if a row the with the specified \s-1UC\s0
cannot be found
.IP "lookup" 4
.IX Item "lookup"
finds the pk value using one of the unique constraints present in the
\&\s-1XML\s0 node
.IP "delete \s-1NOT IMPLEMENTED\s0" 4
.IX Item "delete NOT IMPLEMENTED"
deletes row that has matching \s-1UC\s0
.PP
Operations can be used in either \s-1XORT\s0 or Stag mode
.PP
\fIMacros\fR
.IX Subsection "Macros"
.PP
Macros can be used with either \s-1XORT\s0 or Stag style mappings. Macros
allow you to refer to the same node later on in the \s-1XML\s0
.PP
.Vb 12
\&  <person op="lookup" id="joe">
\&    <name>joe</name>
\&  </person>
\&  <person op="lookup" id="fred">
\&    <name>fred</name>
\&  </person>
\&  ...
\&  <person_relationship>
\&    <type>friend</type>
\&    <person1_id>joe</person1_id>
\&    <person2_id>fred</person2_id>
\&  </person_relationship>
.Ve
.PP
Assuming \fBname\fR is a unique constraint for \fBperson\fR, and
person_relationship has two foreign keys named person1_id and
person2_id linking to the person table, DBStag will first lookup the
two person rows by name (throwing an error if not present) and use the
returned pk values to populate the person_relationship table
.PP
\fIHow it works\fR
.IX Subsection "How it works"
.PP
Before a node is stored, certain subnodes will be pre-stored; these are
subnodes for which there is a foreign key mapping \s-1FROM\s0 the parent node
\&\s-1TO\s0 the child node. This pre-storage is recursive.
.PP
After these nodes are stored, the current node is either INSERTed or
UPDATEd. The database is introspected for \s-1UNIQUE\s0 constraints; these
are used as keys. If there exists a row in the database with matching
key, then the node is UPDATEd; otherwise it is INSERTed.
.PP
(primary keys from pre-stored nodes become foreign key values in the
existing node)
.PP
Subsequently, all subnodes that were not pre-stored are now
post-stored.  The primary key for the existing node will become
foreign keys for the post-stored subnodes.
.SS "force_safe_node_names"
.IX Subsection "force_safe_node_names"
.Vb 3
\&  Usage   \- $dbh\->force_safe_node_names(1);
\&  Returns \- bool
\&  Args    \- bool [optional]
.Ve
.PP
If this is set, then before storage, all node names are made
\&\fBDB-safe\fR; they are lowercased, and the following transform is
applied:
.PP
.Vb 1
\&  tr/a\-z0\-9_//cd;
.Ve
.SS "mapping"
.IX Subsection "mapping"
.Vb 3
\&  Usage   \- $dbh\->mapping(["alias/table.col=fktable.fkcol"]);
\&  Returns \- 
\&  Args    \- array
.Ve
.PP
Creates a stag-relational mapping (for storing data only)
.PP
Occasionally not enough information can be obtained from db
introspection; you can provide extra mapping data this way.
.PP
Occasionally you stag objects/data/XML will contain aliases that do
not correspond to actual \s-1SQL\s0 relations; the aliases are intermediate
nodes that provide information on which foreign key column to use
.PP
For example, with data like this:
.PP
.Vb 6
\&  (person
\&   (name "...")
\&   (favourite_film
\&    (film (....))
\&   (least_favourite_film
\&    (film (....)))))
.Ve
.PP
There may only be two \s-1SQL\s0 tables: person and film; person would have
two foreign key columns into film. The mapping may look like this
.PP
.Vb 2
\&  ["favourite_film/person.favourite_film_id=film.film_id",
\&   "least_favourite_film/person.least_favourite_film_id=film.film_id"]
.Ve
.PP
The mapping can also be supplied in the xml that is loaded; any node
named \*(L"dbstag_metadata\*(R" will not be loaded; it is used to supply the
mapping. For example:
.PP
.Vb 6
\&  <personset>
\&    <dbstag_mapping>
\&      <map>favourite_film/person.favourite_film_id=film.film_id</map>
\&      <map>least_favourite_film/person.least_favourite_film_id=film.film_id</map>
\&    </dbstag_mapping>
\&    <person>...
.Ve
.SS "mapconf"
.IX Subsection "mapconf"
.Vb 3
\&  Usage   \- $dbh\->mapconf("mydb\-stagmap.stm");
\&  Returns \- 
\&  Args    \- filename
.Ve
.PP
sets the conf file containing the stag-relational mappings
.PP
This is not of any use for a XORT-style mapping, where foreign key
columns are explicitly stated
.PP
See \fImapping()\fR above
.PP
The file contains line like:
.PP
.Vb 2
\&  favourite_film/person.favourite_film_id=film.film_id
\&  least_favourite_film/person.least_favourite_film_id=film.film_id
.Ve
.SS "noupdate_h"
.IX Subsection "noupdate_h"
.Vb 3
\&  Usage   \- $dbh\->noupdate_h({person=>1})
\&  Returns \- 
\&  Args    \- hashref
.Ve
.PP
Keys of hash are names of nodes that do not get updated \- if a unique
key is queried for and does not exist, the node will be inserted and
subnodes will be stored; if the unique key does exist in the db, then
this will not be updated; subnodes will not be stored
.SS "trust_primary_key_values"
.IX Subsection "trust_primary_key_values"
.Vb 3
\&  Usage   \- $dbh\->trust_primary_key_values(1)
\&  Returns \- bool
\&  Args    \- bool (optional)
.Ve
.PP
The default behaviour of the \fIstorenode()\fR method is to remap all
\&\fBsurrogate\fR \s-1PRIMARY KEY\s0 values it comes across.
.PP
A surrogate primary key is typically a primary key of type \s-1SERIAL \s0(or
\&\s-1AUTO_INCREMENT\s0) in MySQL. They are identifiers assigned automatically
be the database with no semantics.
.PP
It may be desirable to store the same data in two different
databases. We would generally not expect the surrogate IDs to match
between databases, even if the rest of the data does.
.PP
(If you do not use surrogate primary key columns in your load xml,
then you can ignore this accessor)
.PP
You should \s-1NOT\s0 use this method in conjunction with Macros
.PP
If you use primary key columns in your \s-1XML,\s0 and the primary keys are
not surrogate, then youshould set this.  If this accessor is set to
non-zero (true) then the primary key values in the \s-1XML\s0 will be used.
.PP
If your db has surrogate/auto\-increment/serial PKs, and you wish to
use these \s-1PK\s0 columns in your \s-1XML,\s0 yet you want to make \s-1XML\s0 that can be
exported from one db and imported into another, then the default
behaviour will be fine.
.PP
For example, if we extract a 'person' from a db with surrogate \s-1PK
\&\s0\fBid\fR and unique key \fBssno\fR, we may get this:
.PP
.Vb 5
\&  <person>
\&    <id>23</id>
\&    <name>fred</name>
\&    <ssno>1234\-567</ssno>
\&  </person>
.Ve
.PP
If we then import this into an entirely fresh db, with no rows in
table \fBperson\fR, then the default behaviour of \fIstorenode()\fR will create a
row like this:
.PP
.Vb 5
\&  <person>
\&    <id>1</id>
\&    <name>fred</name>
\&    <ssno>1234\-567</ssno>
\&  </person>
.Ve
.PP
The \s-1PK\s0 val 23 has been mapped to 1 (all foreign keys that point to
person.id=23 will now point to person.id=1)
.PP
If we were to first call \f(CW$sdbh\fR\->\fItrust_primary_key_values\fR\|(1), then
person.id would remain to be 23. This would only be appropriate
behaviour if we were storing back into the same db we retrieved from.
.SS "tracenode"
.IX Subsection "tracenode"
.Vb 1
\&  Usage   \- $dbh\->tracenode(\*(Aqperson/name\*(Aq)
.Ve
.PP
Traces on \s-1STDERR\s0 inserts/updates on a particular element type (table),
displaying the sub-element (column value).
.SS "is_caching_on \fB\s-1ADVANCED OPTION\s0\fP"
.IX Subsection "is_caching_on ADVANCED OPTION"
.Vb 7
\&  Usage   \- $dbh\->is_caching_on(\*(Aqperson\*(Aq, 1)
\&  Returns \- number
\&  Args    \- number
\&                   0: off (default)
\&                   1: memory\-caching ON
\&                   2: memory\-caching OFF, bulkload ON
\&                   3: memory\-caching ON, bulkload ON
.Ve
.PP
IN-MEMORY \s-1CACHING\s0
.PP
By default no in-memory caching is used. If this is set to 1,
then an in-memory cache is used for any particular element. No cache
management is used, so you should be sure not to cache elements that
will cause memory overloads.
.PP
Setting this will not affect the final result, it is purely an
efficiency measure for use with \fIstorenode()\fR.
.PP
The cache is indexed by all unique keys for that particular
element/table, wherever those unique keys are set
.PP
\&\s-1BULKLOAD\s0
.PP
If bulkload is used without memory-caching (set to 2), then only
INSERTs will be performed for this element. Note that this could
potentially cause a unique key violation, if the same element is
present twice
.PP
If bulkload is used with memory-caching (set to 3) then only INSERTs
will be performed; the unique serial/autoincrement identifiers for
those inserts will be cached and used. This means you can have the
same element twice. However, the load must take place in one session,
otherwise the contents of memory will be lost
.SS "clear_cache"
.IX Subsection "clear_cache"
.Vb 3
\&  Usage   \- $dbh\->clear_cache;
\&  Returns \- 
\&  Args    \- none
.Ve
.PP
Clears the in-memory cache
.PP
Caches are not automatically managed \- the \s-1API\s0 user is responsible for
making suring the cache does not get too big
.SS "cache_summary"
.IX Subsection "cache_summary"
.Vb 3
\&  Usage   \- print $dbh\->cache_summary\->xml
\&  Returns \-  L<Data::Stag>
\&  Args    \-
.Ve
.PP
Gives a summary of the size of the in-memory cache by keys. This can
be used for automatic cache management:
.PP
.Vb 7
\&  $person_cache = $dbh\->cache_summary\->get_person;
\&  my @index_nodes = $person_cache\->tnodes;
\&  foreach (@index_nodes) {
\&    if ($_\->data > MAX_PERSON_CACHE_SIZE) {
\&      $dbh\->clear_cache;
\&    }
\&  }
.Ve
.SH "SQL TEMPLATES"
.IX Header "SQL TEMPLATES"
DBStag comes with its own \s-1SQL\s0 templating system. This allows you to
reuse the same canned \s-1SQL\s0 or similar \s-1SQL\s0 qeuries in different
contexts. See DBIx::DBStag::SQLTemplate
.SS "find_template"
.IX Subsection "find_template"
.Vb 3
\&  Usage   \- $template = $dbh\->find_template("my\-template\-name");
\&  Returns \- L<DBIx::DBStag::SQLTemplate>
\&  Args    \- str
.Ve
.PP
Returns an object representing a canned paramterized \s-1SQL\s0 query. See
DBIx::DBStag::SQLTemplate for documentation on templates
.SS "list_templates"
.IX Subsection "list_templates"
.Vb 3
\&  Usage   \- $templates = $dbh\->list_templates();
\&  Returns \- Arrayref of L<DBIx::DBStag::SQLTemplate>
\&  Args    \-
.Ve
.PP
Returns a list of \s-1ALL\s0 defined templates \- See
DBIx::DBStag::SQLTemplate
.SS "find_templates_by_schema"
.IX Subsection "find_templates_by_schema"
.Vb 3
\&  Usage   \- $templates = $dbh\->find_templates_by_schema($schema_name);
\&  Returns \- Arrayref of L<DBIx::DBStag::SQLTemplate>
\&  Args    \- str
.Ve
.PP
Returns a list of templates for a particular schema \- See
DBIx::DBStag::SQLTemplate
.SS "find_templates_by_dbname"
.IX Subsection "find_templates_by_dbname"
.Vb 3
\&  Usage   \- $templates = $dbh\->find_templates_by_dbname("mydb");
\&  Returns \- Arrayref of L<DBIx::DBStag::SQLTemplate>
\&  Args    \- db name
.Ve
.PP
Returns a list of templates for a particular db
.PP
Requires resources to be set up (see below)
.SH "RESOURCES"
.IX Header "RESOURCES"
Generally when connecting to a database, it is necessary to specify a
\&\s-1DBI\s0 style \s-1DSN\s0 locator. DBStag also allows you specify a \fBresource
list\fR file which maps logical names to full locators
.PP
The following methods allows you to use a resource list
.SS "resources_list"
.IX Subsection "resources_list"
.Vb 3
\&  Usage   \- $rlist = $dbh\->resources_list
\&  Returns \- arrayref to a hashref
\&  Args    \- none
.Ve
.PP
Returns a list of resources; each resource is a hash
.PP
.Vb 4
\&  {name=>"mydbname",
\&   type=>"rdb",
\&   schema=>"myschema",
\&  }
.Ve
.SH "SETTING UP RESOURCES"
.IX Header "SETTING UP RESOURCES"
The above methods rely on you having a file describing all the
relational dbs available to you, and setting the env var
\&\s-1DBSTAG_DBIMAP_FILE\s0 set (this is a \fB:\fR separated list of paths).
.PP
\&\fBThis is alpha code \- not fully documented, \s-1API\s0 may change\fR
.PP
Currently a resources file is a whitespace delimited text file \-
XML/Sxpr/IText definitions may be available later
.PP
Here is an example of a resources file:
.PP
.Vb 2
\&  # LOCAL
\&  mytestdb         rdb        Pg:mytestdb                      schema=test
\&  
\&  # SYSTEM
\&  worldfactbook    rdb      Pg:worldfactbook@db1.mycompany.com  schema=wfb
\&  employees        rdb      Pg:employees@db2.mycompany.com      schema=employees
.Ve
.PP
The first column is the \fBnickname\fR or \fBlogical name\fR of the
resource/db. This nickname can be used instead of the full \s-1DBI\s0 locator
path (eg you can just use \fBemployees\fR instead of
\&\fBdbi:Pg:dbname=employees;host=db2.mycompany.com\fR
.PP
The second column is the resource type \- rdb is for relational
database. You can use the same file to track other system datasources
available to you, but DBStag is only interested in relational dbs.
.PP
The 3rd column is a way of locating the resource \- driver:name@host
.PP
The 4th column is a \fB;\fR separated list of \fBtag\fR=\fBvalue\fR pairs; the
most important tag is the \fBschema\fR tag. Multiple dbs may share the
same schema, and hence share \s-1SQL\s0 Templates
.SH "COMMAND LINE SCRIPTS"
.IX Header "COMMAND LINE SCRIPTS"
DBStag is usable without writing any perl, you can use command line
scripts and files that utilise tree structures (\s-1XML,\s0 S\-Expressions)
.IP "selectall_xml.pl" 4
.IX Item "selectall_xml.pl"
.Vb 1
\& selectall_xml.pl \-d <DSN> [\-n <nestexpr>] <SQL>
.Ve
.Sp
Queries database and writes decomposed relation as \s-1XML\s0
.Sp
Can also be used with templates:
.Sp
.Vb 1
\& selectall_xml.pl \-d <DSN> /<templatename> <var1> <var2> ... <varN>
.Ve
.IP "selectall_html.pl" 4
.IX Item "selectall_html.pl"
.Vb 1
\& selectall_html.pl \-d <DSN> [\-n <nestexpr>] <SQL>
.Ve
.Sp
Queries database and writes decomposed relation as \s-1HTML\s0 with nested
tables indicating the nested structures.
.IP "stag\-storenode.pl" 4
.IX Item "stag-storenode.pl"
.Vb 1
\& stag\-storenode.pl \-d <DSN> <file>
.Ve
.Sp
Stores data from a file (Supported formats: \s-1XML,\s0 Sxpr, IText \- see
Data::Stag) in a normalized database. Gets it right most of the time.
.Sp
\&\s-1TODO \-\s0 metadata help
.IP "stag\-autoddl.pl" 4
.IX Item "stag-autoddl.pl"
.Vb 1
\& stag\-autoddl.pl [\-l <linktable>]* <file>
.Ve
.Sp
Takes data from a file (Supported formats: \s-1XML,\s0 Sxpr, IText \- see
Data::Stag) and generates a relational schema in the form of \s-1SQL
CREATE TABLE\s0 statements.
.SH "ENVIRONMENT VARIABLES"
.IX Header "ENVIRONMENT VARIABLES"
.IP "\s-1DBSTAG_TRACE\s0" 4
.IX Item "DBSTAG_TRACE"
setting this environment will cause all \s-1SQL\s0 statements to be printed
on \s-1STDERR,\s0 as well as a full trace of how nodes are stored
.SH "BUGS"
.IX Header "BUGS"
The \s-1SQL\s0 parsing can be quite particular \- sometimes the \s-1SQL\s0 can be
parsed by the \s-1DBMS\s0 but not by DBStag. The error messages are not
always helpful.
.PP
There are probably a few cases the \s-1SQL SELECT\s0 parsing grammar cannot deal with.
.PP
If you want to select from views, you need to hack DBIx::DBSchema (as of v0.21)
.SH "TODO"
.IX Header "TODO"
Use SQL::Translator to make \s-1SQL DDL\s0 generation less Pg-specific; also
for deducing foreign keys (right now foreign keys are guessed by the
name of the column, eg table_id)
.PP
Can we cache the grammar so that startup is not so slow?
.PP
Improve algorithm so that events are fired rather than building up
entire structure in-memory
.PP
Tie in all \s-1DBI\s0 attributes accessible by hash, i.e.: \f(CW$dbh\fR\->{...}
.PP
Error handling
.SH "WEBSITE"
.IX Header "WEBSITE"
<http://stag.sourceforge.net>
.SH "AUTHOR"
.IX Header "AUTHOR"
Chris Mungall <\fIcjm \s-1AT\s0 fruitfly \s-1DOT\s0 org\fR>
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
Copyright (c) 2004 Chris Mungall
.PP
This module is free software.
You may distribute this module under the same terms as perl itself