Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

How to share DBI connections between objects?

by blahblah (Friar)
on Mar 26, 2007 at 03:39 UTC ( [id://606511]=perlquestion: print w/replies, xml ) Need Help??

blahblah has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

I'm working through the design phase of my first foray into an object-oriented web app. One of the things I'm having trouble understanding is how to share an open DBI connection with all my objects. If I change my DBD I don't want to have to update all my object modules. Is this even correct - should all my objects share one connection, or should they each open their own? Do they have to all be in the same class to pull this off, or is there another way?

Some pseudo-code to roughly approximate what I'm thinking:
package My; # do the DBI connection in here. Set My->dbh to be a ref to the DBI ha +ndle? package My::Collection; # check My->dbh to see if we're connected # accesses the collections table through My->dbh package My::Collection::Asset; # check My->dbh to see if we're connected # accesses the assets table through My->dbh
Sorry, the OO thing is still very new to me - so I feel shaky on my design a bit. Is there a smarter way?

Thanks.

Replies are listed 'Best First'.
Re: How to share DBI connections between objects?
by Zaxo (Archbishop) on Mar 26, 2007 at 05:55 UTC

    You can get a global handle through a closure. That's more or less ok in OO since it's a method which accesses the handle ;-)

    package My; use DBI; { my $dbh = DBI::connect( . . .); # add error handling sub dbh () { $dbh } } # call up the handle with My::dbh(). # You don't need to import anything from DBI unless you # want to see or modify its globals package My::Collection; # . . .
    I used an empty prototype to give perl a hint when My::dbh is called without parens.

    Note that sharing a dbi connection between objects will break for threaded or forked code. In those cases a private handle should be preferred.

    After Compline,
    Zaxo

Re: How to share DBI connections between objects?
by GrandFather (Saint) on Mar 26, 2007 at 05:29 UTC

    In code I'm working on at present I have two classes of interest. One class is intended for creating a single instance from that manages the database connection. The second class is the base class for a bundle of classes that mediate access to different tables in the database.

    The table accessors don't require a connection at create time and can do a limited range of stuff without a connection being provided, but they die if you try to use them in a way that would require a connection and you've not provided one yet. That allows a Task object to be created for example with a bunch of task information, then handed over to a scheduler that gets the task queued (added to the task table).

    The table object base class provides members to insert a derived instance into its table and to update/fetch/delete entries matching various criteria. The base class will also create the table for a derived class if the table doesn't exist (the derived class provides a column spec). Typical derived class code looks like:

    package Job; require Exporter; our @ISA = ('DBObject'); our @EXPORT = qw( ); my $jobTableDef = ['jobs', # Job specifications 'id INTEGER PRIMARY KEY NOT NULL, command VARCHAR(1024) NOT NULL, maxrunmins INTEGER, numruns INTEGER, runtimetotal INTEGER, flags VCHAR(20) ' ]; sub new { my ($class, %params) = @_; $params{flags} = '' unless exists $params{flags}; return DBObject::new ($class, %params, tableDef => $jobTableDef); } 1;

    and typical usage looks like:

    sub CheckTasks { my $self = shift; my $task = Task->new (dbh => $self->{db}); my @readyTasks = $task->getOldest (); if (@readyTasks) { my $taskInfo = $task->fetch (id => $readyTasks[0][0]); my $job = Job->new (dbh => $self->{db}); my $jobInfo = $job->fetch (id => $taskInfo->{jobid});

    DWIM is Perl's answer to Gödel

      Hi GrandFather,

      I see you are not using the 'usual' way of doing classes. Usually, I use something like this:

      package Job; use base 'DBObject'; sub new { my ($class, %params) = @_; ... return $class->SUPER::new(...); }

      There's any special reason for not doing this way?

      Igor 'izut' Sutton
      your code, your rules.

        Ignorance mostly! :) Thanks for the pointer.

        I'm into my second year of writing Perl and until recently my use of OO has been pretty light. However most things seem to just work so I've only skimmed OO docs to solve immediate issues and have likely missed chunks of the big picture. I'll go back and do some of the required reading real soon now - promise.


        DWIM is Perl's answer to Gödel
Re: How to share DBI connections between objects?
by erroneousBollock (Curate) on Mar 26, 2007 at 05:33 UTC
    Apache::DBI transparently upgrades your DBI connections to pooled, persistent connections. No extra work needed.

    Personally, I use a small set of 'helper' functions to do database queries. eg:
    my @returned = withDB { # In here $_[0] is the database connection # Connection will be closed automatically when this sub exits. # Any exceptions will be caught, database handles # cleaned up, and the exception re-thrown. # Optionally pass 'handle factory' after this block. };

    Works well with other functions like:
    withDB { forSelect { # $_[0] is a hashref of current row from selection. } shift, $query, \@bindvals; };

    I'm not sure if others see this as good/bad/no-style.
    I don't use any of the Class::DBI alikes... never found them compelling.

    -David
Re: How to share DBI connections between objects?
by f00li5h (Chaplain) on Mar 26, 2007 at 04:12 UTC

    I'm pretty sure that DBI does pool connections, but i can't prove it. Update both adrianh in Re^2: How to share DBI connections between objects? and perrin in Re^2: How to share DBI connections between objects? suggest I made this up.

    In your example, I'd have a My->Db method, that returned an already connected $dbh this way all the My:: classes and modules can get a database handle when they want it, and you only have to manage the connecting in once place. Some folks call this a factory

    this also means that you can change how the connection is made etc in one place.

    @_=qw; ask f00li5h to appear and remain for a moment of pretend better than a lifetime;;s;;@_[map hex,split'',B204316D8C2A4516DE];;y/05/os/&print;
      I'm pretty sure that DBI does pool connections, but i can't prove it.

      The connect() method doesn't (unless you're using Apache::DBI). The connect_cached() method does.

      Personally I don't like using this sort of thing since it makes it harder to inject things like test databases in a particular context. I prefer to pass in a configuration object to my application that points to the database, that way I can just build custom config objects for testing.

        I'm not sure I understand. I presume you're objecting to My->Db, right?

        So instead something more like this?

        package My::Underling; our $Db; sub import { my($class, $config_ref )=@_; $Db = DBI->connect( @{$config_ref->{connect_args} } ); } # ... else where in the code import My::Underling \%global_config;
        @_=qw; ask f00li5h to appear and remain for a moment of pretend better than a lifetime;;s;;@_[map hex,split'',B204316D8C2A4516DE];;y/05/os/&print;
      Calling it "pooling" might be overstating the case. When you call connect_cached, it will check to see if the current process already has a connection open with those parameters and return it to you if it does. There is no "pool" of connections and no sharing between processes (or threads).
Re: How to share DBI connections between objects?
by CountZero (Bishop) on Mar 26, 2007 at 06:28 UTC
    Did you have a look at DBIX::Class? It has all you require (and more ...).

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: How to share DBI connections between objects?
by snoopy (Curate) on Mar 26, 2007 at 23:04 UTC
    You might want to check out Ima::DBI.

    It's a package for OO style handling of database connections and caching at the class level.

    package My::Collection; use base qw/Ima::DBI/; __PACKAGE__->set_db('foo', "dbi:pg:foo", "user", "pass", {}); __PACKAGE__->set_db('bar', "dbi:pg:bar", "user", "pass", {});
    The connection is automatically made the first time you refer to the handle.
    my $foo_dbh = My::Collection->db_foo; my $bar_dbh = My::Collection->db_bar;
      You might want to check out Ima::DBI.

      Rose::DB handles this sort of thing nicely too.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://606511]
Approved by friedo
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (5)
As of 2024-03-29 10:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found