DBIx::Schema -- An SQL Abstration layer for working with whole schemas
use Schema; my $schema = DBIx::Schema->new({db=>'my_db',user=>'db_user',password=>'gigglesnark'});
***
my $schema = DBIx::Schema->new($my_dbix_abstract_handle); $sth = $schema->select({table=>'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"; }
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.
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.
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.
Note that at this time you must name your each of tables' primary key column 'id' for md_rip.pl to work, and you also must name columns relating to them "${table_name}_id". So a column in the 'foo' table relating to the 'baz' table's primary key must be named 'baz_id'. Of course, it's not a very complicated script, so you can hack it to behave differently. :) Future versions will be more flexible.
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.
An alias to the 'new' method. Takes the same arguments, returns the same thing.
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. You must specify a table that you will be seleting from with the 'table' key. You can specify multiple tables by using the 'tables' key instead. You should only need to specify a 'tables' key if you are using a table that is invisible to schema (for instance, if it is in a scalar where).
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 limit on results, the aspect of the where necessary to join tables will be generated by schema for you.
For example:
$sth = $schema->select({table=>'product', where=>{'product.id'=>['<',6]}});
You can also specify a list of fields to be included beyond the normal ones. This allows you to do some special things like:
$sth = $schema->select({ table=>'product', fields=>[ 'lower(substring(product.name,1,1)) as 'product.letter', 'substring(product.description,1,50) as 'product.shortdesc', ], where=>{'product.id'=>['<',6']}, });
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.
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"; }
Returns the number of rows returned from the SQL query within this statement handle.
Returns, as a string, the name of the handle's key table.
Row object methods are special; see below.
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.
# I already have a $schema object defined. # I'll make a simple statement handle. $sth = $schema->select({table=>'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"; }
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)
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.
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.
Jason McIntosh <jmac@jmac.org>
http://www.jmac.org/projects/DBIx-Schema/
This documentation corresponds with version 0.06 of DBIx::Schema.
This software is copyright (c) 2000 Adelphia.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install DBIx::Schema, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Schema
CPAN shell
perl -MCPAN -e shell install DBIx::Schema
For more information on module installation, please visit the detailed CPAN module installation guide.