/ 
DBIx-Schema-0.04
/ DBIx::Schema

NAME

DBIx::Schema -- An SQL Abstration layer for working with whole schemas

SYNOPSIS

use Schema;
my $schema = DBIx::Schema->new({db=>'my_db',user=>'db_user',password=>'gigglesnark'});
$sth = $schema->select({fields=>['product.*'], where=>{'product.id'=>['<',6]}});
while (my $row = $sth->fetchrow()) {
  print $row->{'name'}."\n";
  print $row->color->{'name'}."\n";
  print $row->{'price'}."\n";
  print $row->{'fish'}."\n";
}

DESCRIPTION

Basically, this module lets you construct and use DBI-style statement handles involving arbitrarily large schemas of related SQL tables without concern about how exactly they're related; in essence, it builds the join clauses for you, as necessary, from case to case. This can be a boon to programs that want to knit together their own complex, relational SQL queries on the fly; through the use of this module, if they know that some tables are somehow related, even if they're two or more steps removed from one another, they can simply name them, and start pulling out data toot-suite.

Of course, you will need to prepare your databases with some metadata tables ahead of time in order for any of this to work. See the DATABASE PREPARATION section below for more.

PREREQUISITES

You most certainly need DBI (as well as appropriate DBD modules for your setup) for this to work.

At this time, you also need Andrew Turner's DBIx::Abstract module. Much of the user-level syntax for this module is inherited from it, so it's good to be familiar with it, as well. This, like DBI and DBD, is available from CPAN.

DATABASE PREPARATION

You will need to create three SQL tables in every database to which you'd like to apply this module. These will act as a data dictionary for all contents of the database. They will be called md_table, md_field, and md_relation ('md' stands for 'metadata').

You should have received a Perl script named md_rip.pl as part of the distribution within which you got this module. Running it will create these tables inside a given database if they're not already present, or rebuild and repopulate them if they are. See its perldocs for more information on its usage.

METHODS

Schema handle Methods

new

This is the schema object constructor. It requires, as an argument, either a DBIx::Abstract database handle object, or a hashref ready for feeding to DBIx::Abstract's 'connect' method.

connect

An alias to the 'new' method. Takes the same arguments, returns the same thing.

select

Returns a statement handle object, primed with an SQL query and ready for fetchrow calls (see below).

This method takes one hashref as an argument. It must have a 'fields' key, which is an arrayref of tables (or fully-qualified, dot-notated fields, though it will ignore all but the table part of these) the schema will initially select on.

Optionally, you can have a 'where' key, which will be passed on to the underlying DBIx::Abstract object, so see that module. Note that this key's value needs only to hold the

You can also have a 'key_table' to specify which table's rows will end up stuck to the row objects generated from the statement handle's

For example:

$sth = $schema->select({fields=>['product'], where=>{'product.id'=>['<',6]}});
opt

This method's arguments get passed along to the internal DBIx::Abstract object's opt method (see that module's docs for usage information). Useful for logging and debugging.

flush_cache

The object keeps an internal cache to help it crawl through the database's relationships faster, but it doesn't check to see if the database's structure may have changed since the last time it performed a full crawl. Calling this method deletes the cache, forcing the object to reexamine the actual tables and start a new cache the next time it needs to know their structure.

Statement handle Methods

fetchrow

Returns a row object, or undef if no rows are available.

As with DBI (and DBIx::Abstract), subsequent calls to fetchrow return the next row available to this statement handle, and undef once all rows have been exhausted (or no rows were available in the first place). Thus, a common code idiom is a while() loop, something like:

while (my $row = $sth->fetchrow()) {
  # Do something with data from this row
  my $id = $row->{'id'};
  my $foo = $row->{'foo'};
  print "The value of foo for row $id is $foo. \n";
}
rows

Returns the number of rows returned from the SQL query within this statement handle.

key_table

Returns, as a string, the name of the handle's key table.

Row objects

Row object methods are special; see below.

sth

Returns the statement handle from which this row emerged.

Row objects don't have any predefined methods (except for 'sth'). You can fetch data from them through directly accessing their instance variables (hash keys), one of which will exist for each column of the row.

For example, if a row represented with object $row has a 'foo' column, that column's value is available through $row->{'foo'}.

You can also pull additional statement handles out of a row by invoking them as methods; an AUTOLOAD method inside the row object will take care of the rest for you, and return a statement handle primed with the named table as the key table, and with a where clause identical to that of the row's statement handle, with the addition of a phrase requiring that the current key table's id field match this row's value of same.

For example:

# I already have a $schema object defined.
# I'll make a simple statement handle.

$sth = $schema->select({fields=>['product'], where=>{'product.price'=>['<',6]}});
# OK, $sth is now primed to return all products costing less than
# $6.00.

while (my $product_row = $sth->fetchrow) {
  print "I am on product ".$product_row->{'name'}."\n";
  # Let's say I have a many-to-many relationship in my schema that
  # allows products to exist in any number of categories. I want to
  # display all categories to which this product belongs. The current
  # statement handle doesn't know or care about categories, so it's
  # time to pull out a new one.
  if ($product_row->category->rows) {
    print "It is in the following categories:\n";
    while (my $cat_row = $product_row->category->fetchrow) {
      print $cat_row->{'name'}."\n";
    }
  } else {
    print "It is not in any category.\n";
}

CAVEATS

I find the row object as it now stands a little sketchy due to the fact that it's essentially user-definable, since its instance variables and legal method names will depend on the nature of the data fetched from its statement handle. This requires that its actual methods, 'AUTOLOAD' and 'sth' (and whatever might be added in the future) be reserved words. So, for now, don't name your tables after the Row class's methods. (Not that you'd want to, since they'd make pretty lousy table names, in my humble opinion)

TODO

It seems to warn about 'Unknown where piece' a bit too often, and unnecessarily.

The format of the data dictionaries needs to be far more configurable than it now is.

BUGS

This software is quite young, having received testing with only a handful of database systems and Perl versions, and having only a few users at the time of this writing (though it is in use in a production environment). The author welcomes bug reports and other feedback at the email address listed below.

AUTHOR

Jason McIntosh <jmac@jmac.org>

HOMEPAGE

http://www.jmac.org/projects/DBIx-Schema/

VERSION

This documentation corresponds with version 0.04 of DBIx::Schema.

COPYRIGHT

This software is copyright (c) 2000 The Maine InterNetworks, Inc.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.