Jifty::DBI is yet another database abstraction layer, with object-relational mapping and (more importantly to me) schema generation. It’s part of Jifty, the new web development toolkit from Best Practical (creators of RT) and it’s a re-imagining of DBIx::SearchBuilder.
Despite its history, the public distribution is fairly new and has some rough patches, mostly related to documentation and understanding. I experimented with the latest stable release (0.16) for a couple of afternoons. Here’s what I learned.
How it Works
There are two parts to Jifty::DBI. One lets you declare and manage a database schema, giving you almost pain-free access to records in your database as objects. The other lets you build queries. My ultimate goal is to write as little SQL as possible, especially to declare tables and relationships, so the first feature has great appeal to me.
You do have to get used to a few quirks of the distribution. The major one is that to do anything, it appears that you have to create and use a Jifty::DBI::Handle object to represent your connection to the database. That’s fine, except for two things. First, by the end of my experimenting, I was a bit sick of creating a separate handle and passing it to everything. In a larger system that probably wouldn’t be an issue — I would have factored this connection code to a separate subroutine or module somewhere that I could re-use it.
The second is that you have to pass it to nearly everything you want to use. Again, that’s fine — this allows the use of multiple objects and multiple databases in the same program. It’s just a bit tedious in the simple case of using a single database in a program. In a larger system, I could easily take the busywork out of this.
Another quirk surprised me with regard to the ORM features. In the world of Jifty::DBI, you first create an object for the record you want to retrieve. That is, if you want to fetch a row out of the articles table, you create an Article object (passing the ::Handle to new()). Then you call a load method of some kind on that object, which fills out that object’s attributes.
I expected the Article object to be some kind of factory that returned the objects. I’m not sure why; I just did.
The Code
I took a small schema from an existing application which manages articles and authors for me. Basically, it keeps track of who my authors are and what articles they have in progress and published. It’s neither large nor complex, but it has a couple of relationships between tables and has survived most of what I need to do with it.
The Models
I ended up writing several small programs to use the code. All used the same models (that is, the classes representing the schema and the objects):
package Article::Schema;
use strict;
use warnings;
use Jifty::DBI::Schema;
use Topic;
use Author;
use Editor;
column description => type is 'varchar(255)';
column price => type is 'float';
column scheduled_date => type is 'date';
column received => type is 'boolean';
column edited => type is 'boolean';
column published => type is 'boolean';
column topic => refers_to Topic;
column author => refers_to Author;
column editor => refers_to Editor;
package Article;
use strict;
use warnings;
use base 'Jifty::DBI::Record';
1;
package Author::Schema;
use strict;
use warnings;
use Jifty::DBI::Schema;
column first_name => type is 'varchar(25)';
column last_name => type is 'varchar(25)';
column address => type is 'varchar(50)';
column rate => type is 'float';
package Author;
use strict;
use warnings;
use base 'Jifty::DBI::Record';
sub fullname
{
my $self = shift;
my $first_name = $self->first_name() || '';
my $last_name = $self->last_name() || '';
return join(' ', $first_name, $last_name );
}
1;
package Editor::Schema;
use strict;
use warnings;
use Jifty::DBI::Schema;
column first_name => type is 'varchar(25)';
column last_name => type is 'varchar(25)';
column address => type is 'varchar(50)';
package Editor;
use strict;
use warnings;
use base 'Jifty::DBI::Record';
sub fullname
{
my $self = shift;
my $first_name = $self->first_name() || '';
my $last_name = $self->last_name() || '';
return join(' ', $first_name, $last_name );
}
1;
package Topic::Schema;
use strict;
use warnings;
use Jifty::DBI::Schema;
column name => type is 'varchar(25)';
package Topic;
use strict;
use warnings;
use base 'Jifty::DBI::Record';
1;Generating the Schemas
Each model has an associated ::Schema class. These inherit from Jifty::DBI::Schema and use a small, Perlish minilanguage (actual Perl code, not source-filtered) to define the schema in a more-or-less database-independent way. I’m not sure how well this works in practice, as my column types don’t really appear database-independent. I did generate both SQLite and MySQL schemas with a separate program, however. The documentation is slim on how this works.
Actually generating the schemas took more work than I anticipated, mostly because of the documentation issue. I ended up pillaging some Jifty code. (This is likely a temporary problem, as comments in the code from which I stole suggested moving that into the Jifty::DBI distribution somewhere.) Creating the schema is as easy as:
#!/usr/bin/perl
use strict;
use warnings;
use Article;
use Jifty::DBI::Handle;
use Jifty::DBI::SchemaGenerator;
my $handle = Jifty::DBI::Handle->new();
$handle->connect(
driver => 'SQLite',
database => 'article_db',
);
my $generator = Jifty::DBI::SchemaGenerator->new( $handle );
for my $model (qw( Author Topic Editor Article ) )
{
$generator->add_model( $model->new( $handle ) );
}
for my $statement ( $generator->create_table_sql_statements() )
{
$handle->simple_query( $statement );
}Creating the MySQL schema required only changing the arguments to connect(), though I also had to start MySQL on my machine. Even though I modified the final loop to print the statements instead of actually issuing them, Jifty::DBI still needed to connect to the database. It would be nice if I could generate schemas for databases I don’t have running or available. That would make it possible to distribute working schemas for a database-independent project.
Working with Records
Saving information to the database is pretty easy, as you might expect. First I added an author and editor record representing me, then a Perl topic. Then I added a couple of articles, without reading the documentation:
#!/usr/bin/perl
use strict;
use warnings;
use Article;
use Jifty::DBI::Handle;
my $handle = Jifty::DBI::Handle->new();
$handle->connect(
driver => 'SQLite',
database => 'article_db',
);
my $article = Article->new( $handle );
my $author = Author->new( $handle );
my $topic = Topic->new( $handle );
my $editor = Editor->new( $handle );
$author->load_by_cols( first_name => 'chromatic' );
$editor->load_by_cols( first_name => 'chromatic' );
$topic->load_by_cols( name => 'Perl' );
for my $prefix ( qw( A Another ))
{
$article->create(
author => $author,
editor => $editor,
topic => $topic,
description => "$prefix Review of Jifty::DBI",
price => 'free',
scheduled_date => '20060318',
received => 1,
edited => 0,
published => 0,
);
}That just worked, with Jifty::DBI figuring out the relationships between columns for me. Retrieving data from a single table is reasonably easy too (as you can see in the previous code). Retrieving data from multiple tables is more difficult, and this was my biggest source of frustration with the current version of the distribution.
Joined Records
So far, everything looked good, but I was curious about many-to-many relations. One article can have multiple authors. That ought to change the schema somewhat though, using a link table. There’s nothing in the documentation that suggests the idea however. Does it work?
Digging around more in the documentation, I decided that creating a Jifty::DBI::Collection class for these relationships was the right approach. It didn’t suggest much beyond that, though. I ended up creating a new Article::Collection package in lib/Article.pm to override two methods:
package Article::Collection;
use base 'Jifty::DBI::Collection';
sub table { 'articles' }
sub record_class { 'Article' }I don’t know why that was necessary, as it should be almost trivial to find the mapping by the name. Jifty::DBI tried to use a class named Article:: however.
Without clearer documentation, my first approach was to do the fetch myself.
my $author = Author->new( $handle );
$author->load_by_cols( first_name => 'chromatic' );
my $collection = Article::Collection->new( handle => $handle );
$collection->record_class( 'Article' );
$collection->limit( column => 'author', value => $author->id() );
while ( my $article = $collection->next() )
{
printf "Article \"%s\" about %s is on schedule for %s\n" .
"written by %s and edited by %s\n",
$article->description(),
$article->scheduled_date(),
$article->topic->name(),
$article->author->fullname(),
$article->editor->fullname();
}I didn’t like that. Even though Jifty::DBI knows that articles have authors (at least one), I hated to use the id() method in specific.
After more exploration, I figured out how to do everything in one SQL statement (as if I should have to care how it does it behind the scenes). It’s more code:
my $collection = Article::Collection->new( handle => $handle );
my $author_alias = $collection->limit(
table => 'authors', column => 'first_name', value => 'chromatic'
);
my $article_alias = $collection->new_alias( 'articles' );
$collection->join(
alias1 => $article_alias, column1 => 'author',
alias2 => $author_alias, column2 => 'id',
);
while ( my $article = $collection->next() )
{
printf "Article \"%s\" about %s is on schedule for %s\n" .
"written by %s and edited by %s\n",
$article->description(),
$article->scheduled_date(),
$article->topic->name(),
$article->author->fullname(),
$article->editor->fullname();
}This works by creating the collection, then setting a limit (that is, the representation of a WHERE clause) on the authors table, then setting a JOIN between articles.author and authors.id. The extra bookkeeping is managing the table aliases — one returned from the limit() call (an alias for the authors table) and one generated specifically by new_alias() for the articles table.
In theory, using aliases here alleviates the problem of ambiguous references, especially in the case of recursive joins. In practice, this is a lot of bookkeeping. Hopefully this is just a temporary thing.
Caveats
It wasn’t an issue for me, but Jifty::DBI has plenty of dependencies. Many of them make the magical column handling work, while others build the class model and mean that users have to write less code than they would otherwise. The distribution installed easily through CPAN. (It does use Module::Install, so if that module is a hassle for you as it can be sometimes, beware. This does mean that it is possible to build an all-in-one bundle to download, build, and install though.)
A bigger issue is that the documentation is incomplete and confusing in places. The distribution does have a version number way below 1.0, but if you prefer not to experiment (and end up reading the code and searching existing projects for example usage), you might want to wait for future versions.
Finally, I found some of the code I had to write more verbose than I wanted. This surprised me; for as little code as I had to write to manage my schema, something I have to do once, it takes a lot more to write multi-table filtered searches, of which an application might need several.
I’m not sure if I’m using the code ineffectively (lacking better documentation) or if I’m exploring part of the code that’s not perfect yet (being a fairly new release).
To be more specific, consider my example code again. The schema objects for these classes already know about the relationships between tables, so why do I need to make the join manually? Granted, there’s nothing in the schema that says an author can have multiple articles or, even better, that an article can have multiple authors, but given the choice of putting more metadata in the schema (and never having to write join tables myself) or writing code like this for all of the joins, I’ll write metadata every time.
It’s a pity that this breaks the encapsulation — suddenly I have to know more about the column names for the created schema than I like.
Final Thoughts
The biggest flaw with this distribution is the lack of documentation. After I left the comforting example code, I was never sure if I was using it correctly or even thinking accurately about what I should be able to do. Again, this is a new release and I doubt that its authors expect anyone to use it in production code without being in close contact with the Jifty project. That’s completely fair — and I’ve seen a few documentation patches in the recent past.
Jifty::DBI has some tutorial information, but it may be unclear without more code. The three main places to look for more information are Jifty::DBI::Schema, Jifty::DBI::Record, and Jifty::DBI::Handle.
Jifty::DBI::Schema has too little documentation on the available column types. It also doesn’t explain refers_to, which seems like a handy property.
I really like the idea of schema generation. The object-relational mapping is decent. The ability to write Collection objects representing complex queries is nice — but merging that with the ORM seems like a big feature the distribution doesn’t have yet.
If you have time to experiment, I recommend exploring this distribution, especially as new versions come out. I really look forward to a 1.0 release.
Take a look at DBIx::Class - which has schema generation via SQL-T, and has a decent amount of documentation. Multi-way joins are trivial, as are many-to-many and prefetching.
I just skimmed this article, but some of the earlier code reminded me a lot of my first attempts to play with Class::DBI, before I learned that I could have it figure out for itself what the database looks like. I really hate repeating my database design; is there any way within Jifty to do this automatically?
There are a lot of database wrappers out there, from Class::DBI to DBIx::Simple, etc. I'd certainly appreciate a comparision of features and API, as I tend to stick with what I know.
A, I didn't see a way to make
Jifty::DBIscan an existing database. The documentation lead me to believe that the preferred approach is to declare your schema in Perl withJifty::DBI::Schemaand generate your database from there.Chromatic is correct. The "Jifty Way" is to use Jifty to manage your database schema. Jifty::DBI is very much "the first part" of Jifty (jifty.org) that was made publicly available. When using Jifty, at least a couple of the warts mentioned go away ;) We thought long and hard about whether to even release the module on its own, but enough people asked that we decided to do it. But for something that feels coherent, I'd strongly recommend using Jifty::DBI in the context of Jifty itself
Jifty::DBI uses DBIx::DBSchema already, and DBIx::DBSchema does know how to scan and reverse-engineer the schema of an existing database... (new_native and new_odbc)
domacrol