I'm about to upload a new module to CPAN, I just wanted to OK the
namespace with everybody.
The module is DBIx::DBStag. It is for converting between relations and
tree-like datastructures. The closest module I can find is
XML::Generator::DBI (which used to be DBIx::XML_RDB) - I think there
enough differences to warrant another DBIx module. It is significantly
different in that DBStag can take any SQL SELECT query result and
decompose the cartesian product back into nested relations. DBStag also
differs in that it uses a simpler subset of XML - XML can actually be
ignored altogether.
Anyway, here is the POD
---
=head1 NAME
DBIx::DBStag - Mapping between databases and Stag tree structures
=head1 SYNOPSIS
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 = 'sci-fi' AND star.lastname = 'Fisher';
];
my $dataset = $dbh->selectall_stag($sql);
my @studios = $dataset->get_studio;
foreach my $studio (@studios) {
printf "STUDIO: %s\n", $studio->get_name;
my @movies = $studio->get_movie;
foreach my $movie (@movies) {
printf " MOVIE: %s (genre:%s)\n",
$movie->get_name, $movie->get_genre;
my @stars = $movie->get_star;
foreach my $star (@stars) {
printf " STARRING: %s:%s\n",
$star->firstname, $star->lastname;
}
}
}
# manipulate data then store it back in the database
my @allstars = $dataset->get("movie/studio/star");
$_->set_fullname($_->get_firstname.' '.$_->get_lastname)
foreach(@allstars);
$dbh->storenode($dataset);
>From the command line:
unix> selectall_xml -d 'dbi:Pg:dbname=spybase' 'SELECT * FROM studio NATURAL JOIN
movie'
=cut
=head1 DESCRIPTION
This module is for mapping from databases to Stag objects (Structured
Tags - see Data::Stag). It has two main uses:
=over
=item Querying
This module can take the results of any SQL query and decompose the
flattened results into a tree data structure which reflects the
underlying relational schema. It does this by looking at the SQL query
and introspecting the schema, rather than requiring metadata or an
object model.
In this respect, the module works just like a regular DBI handle, with
extra methods provided.
=item Storing Data
DBStag objects can store any tree-like datastructure (such as XML
documents) into a database using normalized schema that reflects the
structure of the tree being stored. This is done using little or no
metadata.
XML can also be imported, and a relational schema automatically generated.
=back
=head2 HOW QUERYING WORKS
This is a general overview of the rules for turning SQL query results
into a tree like data structure.
=head3 Relations
Relations (i.e. tables and views) are elements (nodes) in the
tree. The elements have the same name as the relation in the database.
=head3 Attributes
Attributes (i.e. columns) of a relation are nested(sub) elements of
the relation element. These elements will be data elements (terminal
nodes). Only the attributes selected for in the SQL query will be
present.
=head3 Table aliases
If an ALIAS is used in the FROM part of the SQL query, the relation
element will be nested inside an element with the same name as the
alias. For instance, the query
SELECT name FROM person AS author;
Will return a data structure like this:
(author
(person
(name "...")))
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.
SELECT *
FROM person AS employee
INNER JOIN
person AS boss ON (employee.boss_id = boss.person_id)
Will generate the default tree -
(employee
(person
(person_id "...")
(name "...")
(foo "...")
(boss
(person
(person_id "...")
(name "...")
(foo "...")))))
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 SQL query dynamically modifies the schema/structure of
the resulting tree.
=head3 Nesting of relations
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.
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 nested A under B if there is an
intermediate linking table that is required at the relational level
but not required in the tree structure.
DBStag will guess a structure/schema based on the ordering of the
relations in your FROM clause. However, this guess can be overwritten
at either the SQL level (using DBStag specific SQL extensions) or at
the API level.
The default algorithm is to nest each relation element under the
relation element preceeding it in the FROM clause; for instance:
SELECT * FROM a NATURAL JOIN b NATURAL JOIN c
will generate the structure
(set
(a
(a_foo "...")
(b
(b_foo "...")
(c
(c_foo "...")))))
assuming there is only one row in each.
(where 'n_foo' is a column in relation 'n')
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. You can
guide by bracketing your FROM clause like this:
!!##
!!## NOTE - THIS PART IS NOT SET IN STONE - THIS MAY CHANGE
!!##
SELECT * FROM (a NATURAL JOIN b) NATURAL JOIN c
This will generate
(set
(a
(a_foo "...")
(b
(b_foo "..."))
(c
(c_foo "..."))))
Now b and c are siblings in the tree. The algorithm is similar to
before: nest each relation element under the relation element
preceeding it; or, if the preceeding item in the FROM clause is a
bracketed structure, nest it under the first relational element in the
bracketed structure.
(Note that in MySQL you may not bracket the FROM clause in this way)
Another way to achieve the same thing is to specify the desired tree
structure using a DBStag specific SQL extension. The DBStag specific
component is removed from the SQL before being presented to the
DBMS. The extension is the 'USE NESTING' clause, which should come at
the end of the SQL query (and is subsequently removed before
processing by the DBMS).
SELECT *
FROM a NATURAL JOIN b NATURAL JOIN c
USE NESTING (set (a (b)(c)));
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.
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.
If you are using the DBStag API directly, you can pass in the nesting
structure as an argument to the select call; for instance:
my $seq =
$dbh->selectall_xml(-sql=>q[SELECT *
FROM a NATURAL JOIN b
NATURAL JOIN c],
-nesting=>'(set (a (b)(c)))');
or the equivalent -
my $seq =
$dbh->selectall_xml(q[SELECT *
FROM a NATURAL JOIN b
NATURAL JOIN c],
'(set (a (b)(c)))');
If you like, you can also use XML here (only at the API level, not at
the SQL level) -
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>
]);
As you can see, this is a little more verbose.
Most command line scripts that use this module should allow
pass-through via the '-nesting' switch.
=head2 Conformance to DTD/XML-Schema
DBStag returns L<Data::Stag> structures that are equivalent to a
simplified subset of XML (and also a simplified subset of lisp
S-Expressions).
These structures are examples of semi-structured data - a good
reference is this book -
Data on the Web: From Relations to Semistructured Data and XML
Serge Abiteboul, Dan Suciu, Peter Buneman
Morgan Kaufmann; 1st edition (January 2000)
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 structure of the query itself.
=head1 CLASS METHODS
=head2 connect
Usage - $dbh = DBIx::DBStag->connect($DSN);
Returns - L<DBIx::DBStag>
Args - see L<DBI>
=cut
=head2 selectall_stag
Usage - $stag = $dbh->selectall_stag($sql);
Returns - L<Data::Stag>
Args - sql string, [nesting string]
Executes a query and returns a L<Data::Stag> structure
An optional nesting expression can be passed in to control how the
relation is decomposed into a tree
=cut
=head2 selectall_xml
Usage - $xml = $dbh->selectall_xml($sql);
Returns - string
Args - sql string, [nesting string]
As selectall_stag(), but the results are transformed into an SQL string
=cut
=head2 selectall_sxpr
Usage - $sxpr = $dbh->selectall_sxpr($sql);
Returns - string
Args - sql string, [nesting string]
As selectall_stag(), but the results are transformed into an
S-Expression string; see L<Data::Stag> for more details.
=cut
=head2 selectall_sax
Usage - $dbh->selectall_sax(-sql=>$sql, -handler=>$sax_handler);
Returns - string
Args - sql string, [nesting string], handler SAX
As selectall_stag(), but the results are transformed into SAX events
[currently this is just a wrapper to selectall_xml but a genuine event
model will later be used]
=cut
=head2 storenode
Usage - $dbh->storenode($stag);
Returns -
Args - L<Data::Stag>
Recursively stores a tree structure in the database
=cut
=head1 COMMAND LINE SCRIPTS
DBStag is usable without writing any perl, you can use command line
scripts and files that utilise tree structures (XML, S-Expressions)
=over
=item selectall_xml.pl
selectall_xml.pl -d <DSN> [-n <nestexpr>] <SQL>
Queries database and writes decomposed relation as XML
=item selectall_html.pl
selectall_html.pl -d <DSN> [-n <nestexpr>] <SQL>
Queries database and writes decomposed relation as HTML with nested
tables indicating the nested structures.
=item stag-storenode.pl
stag-storenode.pl -d <DSN> <file>
Stores data from a file (Supported formats: XML, Sxpr, IText - see
L<Data::Stag>) in a normalized database. Gets it right most of the time.
TODO - metadata help
=item stag-autoddl.pl
stag-autoddl.pl [-l <linktable>]* <file>
Takes data from a file (Supported formats: XML, Sxpr, IText - see
L<Data::Stag>) and generates a relational schema in the form of SQL
CREATE TABLE statements.
=back
=head1 BUGS
This is alpha software! Probably several
If you want to select from views, you need to hack DBIx::DBSchema (as of v0.21)
There are probably a few cases the SQL SELECT parsing grammar cannot deal with
=head1 TODO
Use SQL::Translator to make SQL DDL generation less Pg-specific; also
for deducing foreign keys (right now foreign keys are guessed by the
name of the column, eg table_id)
Can we cache the grammar so that startup is not so slow?
Improve algorithm so that events are fired rather than building up
entire structure in-memory
Tie in all DBI attributes accessible by hash, i.e.: $dbh->{...}
Error handling
=head1 WEBSITE
L<http://stag.sourceforge.net>
=head1 AUTHOR
Chris Mungall <F<[EMAIL PROTECTED]>>
=head1 COPYRIGHT
Copyright (c) 2002 Chris Mungall
This module is free software.
You may distribute this module under the same terms as perl itself
=cut
