Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

RFC DBIx::Handy

by techcode (Hermit)
on Sep 12, 2005 at 23:28 UTC ( [id://491418]=perlmeditation: print w/replies, xml ) Need Help??

Hello Monks - I've (actualy still am) written an module that's for now called DBIx::Handy. At some point (after adding some documentation, tests ...etc) I planed to release it on CPAN.

Reasons for writing it :
Simple solution. There are other DB abstraction modules, but they are much bigger and more complicated. I ussualy need very simple SQL (insert/update) but I hate writing it :)

What it does:
Based on data that you send it as hash (I ussualy get it from and pass it througth Data::FormValidator) and fields in specified table it generates insert and update SQL strings, prepares them and obviosly executes them.

Of course there are few more things like (dis)connecting databases, preparing statements and executing them. I've also implemented it as Singleton since I've realised that it's stupid to pass around your DBH or in this case DBIx::Handy instance. Then I realised that I will need to connect to more than one DB at same time (get data from one, put it in second) in the project I'm curently working on so I added that too :)

I'm looking for ideas what could/should be implemented in it - and comments on what and how it looks like to you.

At this point it's basicly MySQL specific - but I dont intend to leave it on that. I was thinking of moving all things that are specific to some RDBMS into a separate modules such as DBIx::Handy::mysql ... DBIx::Handy::XXyyZZ. Not quite shure how to implement that at this point. Any sugestions?

I also posted an older version of it about two weeks ago on this 487823 node - also for comments.

Code and ussage samples:

package DBIx::Handy; use strict; use DBI; use Carp; sub new { my $class = shift; defined $DBIx::Handy::_instance ? $DBIx::Handy::_instance : ($DBIx::Handy::_instance = $class->init( +@_)); } sub init { my $class = shift; my %params = @_; my $self = {_CONFIG => {}, # holds class configuration _DATABASES => {}, # holds configuration for each database _DBH => {}, # holds DBH of databases }; bless ($self,$class); # Set deafult values for config $params{config}->{auto_connect} ||= 'lazy'; $self->{_CONFIG} = $params{config}; if((ref ($_[0]) eq 'HASH') && (not defined $params{databases})){ push @{$params{databases}}, shift; } foreach( @{ $params{databases} } ){ # Set default value for this database $_->{host} ||= 'localhost'; $_->{auto_connect} ||= $params{config}->{auto_connect}; $_->{driver} ||= $params{config}->{driver}; # Die if there is no driver specified die ('No driver specified for ' . $_->{database} . ' and no defaul +t specified in config.') unless(defined $_->{driver}); my $db_identifier = $_->{host} . '.' . $_->{driver} . '.' . $_->{d +atabase}; die "Database $db_identifier already configured!" if (defined $self->{_DATABASES}->{$db_identifier}); # If we didnt received default_database setting - put this first d +atabase to be it. $self->{_CONFIG}->{default_database} = $db_identifier unless(defin +ed $self->{_CONFIG}->{default_database}); $self->{_DATABASES}->{$db_identifier} = $_; $self->connect($db_identifier) if ($_->{auto_connect} eq 'startup' +); } return $self; } sub DESTROY { my $self = shift; foreach(keys %{$self->{_DATABASES}}){ if(defined $self->{_DBH}->{$_}){ $self->disconnect($_); } } } sub connect { my $self = shift; my $db_identifier = shift; $db_identifier ||= $self->{_CONFIG}->{default_database}; # Connection configs my $c = $self->{_DATABASES}->{$db_identifier}; unless (defined $self->{_DBH}->{$db_identifier}){ $self->{_DBH}->{$db_identifier} = DBI->connect('dbi:' . $c->{driver} . ':database=' . $c->{database} . ';host=' . $c->{host}, $c->{username}, $c->{password}, $c->{options}) or die "Could not connect to database. Error me +ssage: $!"; } else { warn "Trying to connect but already connected!"; } return $self->{_DBH}->{$db_identifier}; } sub disconnect { my $self = shift; my $db_identifier = shift; $db_identifier ||= $self->{_CONFIG}->{default_database}; if (defined $self->{_DBH}->{$db_identifier}){ # Finish the STH if needed. $self->{_STH}->finish() if defined $self->{_STH}; $self->{_DBH}->{$db_identifier}->disconnect(); delete $self->{_DBH}->{$db_identifier}; } else { warn "Trying to disconnect but already disconnected! $self"; } # If it fails, it's already disconnect ... return 1; } sub dbh { my $self = shift; my $db_identifier = shift; $db_identifier ||= $self->{_CONFIG}->{default_database}; if(defined $self->{_DBH}->{$db_identifier}){ return $self->{_DBH}->{$db_identifier}; } else { warn "Trying to get DBH but not connected to database!"; return; } } sub prepare { my $self = shift; my $sql = shift; my $db_identifier = shift; $db_identifier ||= $self->{_CONFIG}->{default_database}; unless (defined $self->{_DBH}->{$db_identifier}){ die "You need to be connected to database to prepare the queries!" +; } return $self->{_DBH}->{$db_identifier}->prepare($sql); } sub do { my $self = shift; return $self->execute(sql => shift, # shifts SQL string database => shift); # shifts databse name } sub execute { my $self = shift; my %params = @_; $params{database} ||= $self->{_CONFIG}->{default_database}; # Check - should we connect unless(defined $self->{_DBH}->{$params{database}}){ $self->connect($params{database}) if $self->{_DATABASES}->{$params{database}}->{auto_connect} eq 'la +zy'; } # If we received sth in params - it's prepared earlier so we dont +do it now. $self->{_STH} = $params{sth} || $self->prepare($params{sql}, $para +ms{database}); $self->{_STH}->execute(@{$params{data}}); if(defined $params{method}){ my $method = $params{method}; return $self->{_STH}->$method( @{ $params{method_params} } ); } else { return $self->{_STH}; } } sub insert { my $self = shift; my %params = @_; $params{database} ||= $self->{_CONFIG}->{default_database}; my @fields = $self->_GET_FIELDS($params{table},$params{database}); my $data = $params{data}; my $sql = 'INSERT INTO ' . $params{table} . ' ('; my ($sql_part1, $sql_part2, @data); foreach (@fields){ if(defined($data->{$_}) && (length($data->{$_}) >= 1) && ($data->{ +$_} ne '')){ $sql_part1 .= "$_,"; $sql_part2 .= '?,'; push (@data,$data->{$_}); } } chop($sql_part1); chop($sql_part2); # to remove last , $sql_part1 .= ')'; $sql_part2 .= ')'; $sql .= $sql_part1 . ' VALUES (' . $sql_part2; return $self->execute(sql => $sql, data => \@data, database => $params{database}); } sub update { my $self = shift; my %params = @_; my @fields = $self->_GET_FIELDS($params{table}, $params{database}) +; my $data = $params{data}; my $sql = 'UPDATE ' . $params{table} . ' SET '; my @data; foreach (@fields){ if(defined($data->{$_}) && (length($data->{$_}) >= 1) && ($data->{$_} ne '') && ($_ ne $params{id_field}) ){ $sql .= $_ . ' = ?,'; push @data,$data->{$_}; } } chop($sql); # to remove last , $sql .= ' WHERE ' . $params{id_field} . ' = ?'; # where id_field = + id_value push @data, $data->{$params{id_field}}; return $self->execute(sql => $sql, data => \@data, database => $params{database}); } sub _GET_FIELDS { my $self = shift; my $results = $self->execute(sql => 'SHOW COLUMNS FROM ' . + shift , # shifts table name method_params => ['Field'], method => 'fetchall_hashref', database => shift); # shifts database name my $fields = join (" ",keys %{$results}); return(keys %{$results}); } sub _GET_DB_IDENT { my $self = shift; my $db_name = shift; my ($count, $db_identifier); my ($host, $driver, $database) = split(/\./, $db_name); unless ((defined $host) && (defined $driver) && (defined $database +)){ foreach(keys %{$self->{_DATABASES}}){ my ($db_host, $db_driver, $database_name) = split(/./, $_); if($db_name eq $database_name){ $count++; $db_identifier = $_; } } die "Couldnt decide which DB to use as there are more of them with + same name, please specify as host.driver.db_name!" if ($count > 1); } else { die "Couldnt find database with that name ..." unless (defined $self->{_DATABASES}->{$db_name}); } return $db_identifier || $db_name; } 1;

As I mostly work with CGI::Application these days, I set up the thing in main module which is inherited by all others. to be precise in cgiapp_init method which is called before anything else.

my $DB = DBIx::Handy->new({database => 'autoreorder', auto_connect => 'startup', driver => 'mysql', username => 'alex'});

Then anywhere in the application where I need it, I either call new without parameters (as it gives me original instance - Singleton) if I'm in some other package (so I wouldnt pass original reference around) or get it from CGI::App's "storage" ( $self->param('DB') ) if in some Runmode

Sample of doing an insert (similar for update - but for now you need to specify primary key field name, plan to make that also automatic if possible for that driver) :

my $r = CGI->new(); my $form_data = $r->Vars(); # Do some checking on form data - validation $DB->insert(table => 'customers', data => $form_data);
Sample of executing you'r own SQL :
my $res = $self->{_DB}->execute( sql => 'SELECT * FROM customers WHERE email = ?', method => 'fetchrow_hashref', data => [$form_data->{email}]); # OR something like this if you know it will return more results my $STH = $self->{_DB}->execute( sql => 'SELECT * FROM customers WHERE email = ?', data => [$form_data->{email}]); while($row = $STH->fetchrow_hashref()){ # .... }
Obvisoly still need some touches on supporting connections to multiple databases.

Replies are listed 'Best First'.
Re: RFC DBIx::Handy
by pg (Canon) on Sep 13, 2005 at 00:18 UTC
    "Based on data that you send it as hash (I ussualy get it from and pass it througth Data::FormValidator) and fields in specified table it generates insert and update SQL strings, prepares them and obviosly executes them."

    Very nice! It is very nice that you have this thought. One of the problem with lots of Perl modules is that there is no consistancy between data presentation of modules, so you cannot feed the output from one module directly to another module, and people have to (repeatedly) convert between data structures (on their own).

    The reason behind is obvious, as those modules were created by different people with different preferences. If the language is created by one company, it usually has a much better control over the same issue.

    I think that you had a very big nice thought.

Re: RFC DBIx::Handy
by perrin (Chancellor) on Sep 13, 2005 at 16:51 UTC
    Well, since you asked for comments:

    The name says nothing about what it does. The functionality is redundant with about 50 or so existing DBIx:: modules. Doing less is not a good enough reason to release another duplicate module. Writing it was a good exercise, but I suggest you take a long, hard look at the existing modules before releasing this one.

      I have to disagree with you. He specificaly mentioned why he made his own over useing an existing one and I think thats great. I've looked at the abstraction modules before and most of them add more complexity than just writing your own SQL. Looking in the DBIx category I see only two other modules that seem to fit this category. DBIx::Abstract and DBIx::Simple (which actualy uses DBIx::Abstract. Does DBIx::Handy provide the some overlapping functionality with DBIx::Abstract? Yep. Does being the current Abstraction module makd DBIx::Abstract the one and only needed module? That's just ridiculous. I think this is a nice simple module that doesn't have all the complexity those do. When you want abstraction you want to just be able to pass it a hashref and move on. This lets you do that while others do not. This constant concept that because there is already a solution aviable a second shouldn't be created is completely contrary to the concept of DWIM and TIMTOWTDI.

      BTW I think the name should be changed to something that has meaning at a glance. When I think 'handy' i think some extra tools for a job that will be handy. In your case then I would expect update and insert to take a DBH, table name and hashref. Then its just a handy tool to do inserts. I would still add abstract to the name. DBIx::SimpleAbstraction or DBIx::Abstract::Simple even.

      Update: fixed borked links

      Update: DBIx::Simple actualy uses SQL::Abstract not DBIx::Abstract. /me heads off to look at all the SQL modules. ;) Thanks Juerd for pointing that out.

      Eric Hodges
        They're actually mostly under SQL::, not DBIx::. For example, SQL::Abstract, SQL::Generator, and SQL::Interpolate.

        This constant concept that because there is already a solution aviable a second shouldn't be created is completely contrary to the concept of DWIM and TIMTOWTDI.

        This idea that duplicating existing modules is somehow more "DWIM" doesn't make much sense to me. Ask someone who searched for "template" on CPAN recently whether they think everyone who wrote their own module without checking CPAN carefully should upload it. TMTOWTDI is not a universally good thing.

        I'm not in favor of any sort of strict control over what gets uploaded to CPAN, but when someone asks for comments, and I think their module is a dupe, I'm not going to encourage them to upload it.

        DBIx::Simple (which actualy uses DBIx::Abstract.

        That's not true. DBIx::Simple uses SQL::Abstract, not DBIx::Abstract. See DESCRIPTION in SQL::Abstract's documentation for an overview of the most important differences.

        Juerd # { site => '', plp_site => '', do_not_use => 'spamtrap' }

Re: RFC DBIx::Handy
by Juerd (Abbot) on Sep 14, 2005 at 09:17 UTC

    Have a look at DBIx::Simple. It's just a glue module, like yours, and already does what you appear to be looking for. It uses SQL::Abstract for query abstraction, which handles the hash case very well.

    For comparison, I've translated your examples:

    # DBIx::Handy $DB->insert(table => 'customers', data => $form_data); # DBIx::Simple $db->insert('customers', $form_data); # DBIx::Handy my $href = $DB->execute(sql => $sql, method => 'fetchrow_hashref', dat +a => \@foo); # DBIx::Simple my $href = $db->query($sql, @foo)->hash; # DBIx::Handy my $sth = $DB->execute(sql => $sql, data => \@foo); while (my $row = $sth->fetchrow_hashref) { ... } # DBIx::Simple my $result = $db->query($sql, @foo); while (my $row = $result->hash) { ... }

    Note, by the way, that data from $cgi->Vars is filthy and should be carefully matched and untainted before use in databases. Even if you use placeholders. In practice, I find it easier (and a little more secure) to build a new hash (or use individual variables even).

    Juerd # { site => '', plp_site => '', do_not_use => 'spamtrap' }

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://491418]
Approved by friedo
Front-paged by planetscape
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (4)
As of 2024-05-26 15:28 GMT
Find Nodes?
    Voting Booth?

    No recent polls found