Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Class::DBI - my way is not very sportsmanlike

by jZed (Prior)
on Dec 02, 2004 at 22:49 UTC ( #411983=perlmeditation: print w/ replies, xml ) Need Help??

I love Class::DBI for the usual reason - ability to gain abstraction by un-coupling all of the elements needed for database access. And I hate it for what I suspect are common reasons (at least for SQL freaks like me) - all of those ugly package declarations everywhere, thinking in terms of has_a and has_many syntax instead of SQL joins, having to redefine columns and primary keys (they're already in the SQL DDL, why do I need to re-specify them), using funky $class->create($data_hashref) and $class->retrieve($query_hashref) instead of SQL inserts and selects. Well, being a have-my-cake-and-eat-it-too kinda guy, I decided to try to code an example of a join that does not have any hard-coded package declarations, that does not use has_a and has_many, that does not require redefining columns and primary keys and that puts more emphasis on the SQL statements. I am not unpleased with the results shown below. I take back the bit about hating Class::DBI, I can use it without using the parts I find annoying.

My questions for the monks are these: what have I lost by using Class::DBI this way? Am I stuck in my SQL ways and just unable to see the light? Are there other, better, ways to accomplish what this script does?

The script below is pure Class::DBI, but foregoes the use of some of the more common methods such as has_a, has_many, create, retrieve. It makes use of two generic subroutines which also don't utilize those methods - get_config(), a quick hack to grab both SQL and data from the script's DATA section and setup_class(), a tiny factory that obviates the need for hard-coded package declarations. These subroutines are generic, they should work with almost any other schema although the get_config() would obviously need to be tweaked for file or database storage of the SQL statments and data.

The data set is a Professor table and a Subject table with the prof's id as the common key. The script joins the two tables and prints out a list of subjects taught by each professor. The script uses DBD::AnyData but it should work with any DBD that supports explicit join syntax. I tested with DBD::SQLite and DBD::Pg and it works fine with both.

-- start of cdbi.pl -- #!perl -w use strict; require 'setup-class.pl'; my @connect_args = ( 'dbi:AnyData:','me','mypass',{} ); my( $sql, $data ) = get_config(qw(Prof Subject)); for my $table(qw(Prof Subject)) { setup_class ( $table, $sql->{$table}, @connect_args ); eval { $table->sql_drop->execute }; $table->sql_create->execute; $table->sql_insert->execute( @$_ ) for ( @{$data->{$table}} ); } for my $row(Prof->search_query) { printf "Professor %s teaches %s.\n",$row->{pname},$row->{cname}; } $_->sql_drop->execute for qw(Prof Subject); __DATA__ create = CREATE TABLE Prof (pid INT PRIMARY KEY, pname VARCHAR(10)) insert = INSERT INTO Prof (pid,pname) VALUES(?,?) query = SELECT pname,cname FROM Prof NATURAL JOIN Subject drop = DROP TABLE Prof 1,Jones 2,Smith create = CREATE TABLE Subject(cid INT PRIMARY KEY,pid INT,cname VARCHA +R(10)) insert = INSERT INTO Subject(cid,pid,cname) VALUES(?,?,?) drop = DROP TABLE Subject 1,1,Chemistry 2,1,Biology 3,2,English -- end of cdbi.pl -- -- start of setup-class.pl -- use warnings; use strict; use Class::DBI; sub get_config { my @config = split /\n\n/,join'',<DATA>; my(%sql,%data); for my $table(@_) { my $sql_str = shift @config; my $data_str = shift @config; for my $stmt(split /\n/,$sql_str) { my($key,$value) = split /\s*=\s*/,$stmt,2; $sql{$table}->{$key}=$value; } for my $row(split /\n/,$data_str) { push @{ $data{$table} }, [split /,/,$row]; } } return \%sql, \%data; } sub setup_class { my($class,$cfg,@connect_args) = @_; my $table = delete $cfg->{table} || $class; my @columns; my $colstr = delete $cfg->{columns}; if ($colstr) { @columns = split /,/,$colstr; } elsif (my $create_str=$cfg->{create}) { $create_str =~ s/^[^\(]+\((.*)\)\s*$/$1/; @columns = map {s/^\s*(\S+)\s.*/$1/; $_} split/,/,$create_str; } else { die "No columns specified for table '$table'!\n"; } eval "Package $class"; no strict 'refs'; @{"$class\::ISA"} = ('Class::DBI'); $class->connection( @connect_args ); $class->table ( $table ); $class->columns ( All=>@columns ); $class->set_sql( $_ => $cfg->{$_}, undef, 0 ) for keys %$cfg; } 1; -- end of setup-class.pl --

Comment on Class::DBI - my way is not very sportsmanlike
Download Code
Re: Class::DBI - my way is not very sportsmanlike
by perrin (Chancellor) on Dec 02, 2004 at 23:32 UTC
    Are you aware of Class::DBI::Loader, and the Class::DBI output mode of SQL::Translator? Class::DBI::mysql/pg does some of this too. I'm not sure why you made the INSERT statement though. And I'm not sure why this seems better than has_a() and has_many() to you. Those have additional functionality that you're losing here.
      Are you aware of Class::DBI::Loader, and the Class::DBI output mode of SQL::Translator? Class::DBI::mysql/pg
      Yes, thanks, good suggestions.

      I'm not sure why you made the INSERT statement though.
      Because I like my SQL to be explicit and visible and in one place and most of the data I deal with is in arrays, not hashes.
      has_a() and has_many() ... have additional functionality that you're losing here.
      I guess that's what I'm asking. What am I losing? Is what I'm losing just some nice shortcuts, or am I losing something essential to the kind of abstraction that Class::DBI supports?
        Because I like my SQL to be explicit and visible and in one place and most of the data I deal with is in arrays, not hashes.
        Easily rectified:
        @hash{@keys} = @values;

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

        If you dislike having SQL generated for you, you may be happier just using Ima::DBI directly. It doesn't turn your data into objects, but it sounds like you don't really care about that as much as setting the SQL manually.

        If you do want to keep Class::DBI and set your INSERT manually, I think you can actually override the MakeNewObj SQL which is set through a set_sql call at the top of Class::DBI.

        The has_many() stuff does a few things for you. It handles cascading deletes, gives you a method for adding a new related object (add_to_*), and lets you restrict the set ( $prof->subjects(year => 2003) ) or set an order.

        Both has_many and has_a follow useful rules when fetching objects. They can turn your data into a Perl class (a DateTime object is the classic example), they use lazy-loading to avoid fetching data you don't usually need until you ask for it, and they will check to see if you already have any of the objects in memory and return the existing object instead of making a duplicate.

        In short, Class::DBI has grown to do quite a bit. If you want a very lightweight way of accessing data and saving it back out, there are other options that might be simpler.

Re: Class::DBI - my way is not very sportsmanlike
by danb (Friar) on Dec 03, 2004 at 20:17 UTC
    You almost made me blow my coffee all over my monitor! I've heard many references to The Princess Bride, but "my way's not very sportsman-like" is hilarious!

    -Dan

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://411983]
Approved by kvale
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2014-12-28 12:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (181 votes), past polls