Re: Re: Abstracting sql

[BUU]

I was under the impression that Class::Dbi was primarily used as a method for storing objects in a relational database. I've looked at it, but it seems entirely too "heavy" and not granular enough for what I would need and I was under the impression that its performance profile left a little something to be desired..?

[edoc]

well, yeh, kinda.. but maybe it sounds better if you turn that around to say that Class::DBI turns your records into objects. I think you would find it to be a huge head start for what you want from your abstraction layer.

As for granular, once you've got your classes set up it's quite simple to create custom sql queries.

The main performance hit that I watch is if you are doing a search that will return a large number of results. In this case Class::DBI takes the data from each record and initialises it into an object. If I just want the data then I write a customised query and method to go with it and return either the select handle or an array of hashes (or array of arrays..) which avoids 90% of the extra overhead Class::DBI would naturally impose.

__PACKAGE__->set_sql( list => "select uname,fname,sname from users" );

sub list{
  my ($class) = @_;
  my $sth = $class->sql_list();
  $sth->execute();
  return $sth->fetchall_arrayref({});
}

Update: oops.. corrected code above..

and when you want the user list..

my @users = My::CDBI::User->list;

With the common task of grabbing one record the automatic objectifying is great and makes it really simple to access/modify records.

my $user = My::CDBI::User->retrieve(123);
$user->fname('Fred');
$user->sname('Flintstone');
$user->update;

cheers,

J

[perrin]

Class::DBI really does sound like what you want here. It's not very heavy at all (the code is quite short) and it lets you address your tables as objects quickly and easilly. It also has very good performance.

[trs80]

Class::DBI Intro
This is the second time this week I have seen a reference to Class::DBI being used for storing objects this is simply not what it was made for. Class::DBI allows for working with the data as objects, basically one record = one object. It also allows for relationship integrity based on keys at an application level rather then the engine level. As for the performance issue, it would be subjective at best depending on size of project.