[Dancer-users] Using persistent DB connections in Dancer::Tutorial

[Gurunandan Bhat]

On Sun, Jan 9, 2011 at 12:04 AM, Puneet Kishor <punk.kish at gmail.com> wrote:

>
>
>
> On Saturday, January 8, 2011 at 12:31 PM, Gurunandan Bhat wrote:
>
>
>
> On Sat, Jan 8, 2011 at 8:26 PM, Puneet Kishor <punk.kish at gmail.com> wrote:
>
>
>
> Gurunandan Bhat wrote:
>
> If I rewrite connect_db as:
>
> my $dbh;
> sub connect_db {
>
>     if (! defined $dbh) {
>         $dbh = DBI->connect("dbi:SQLite:dbname=".setting('database')) or
>             die $DBI::errstr;
>
>     }
>     return $dbh;
> }
>
> will the application use a single connection for all connections under
> all perl webservers (in-built, mod_perl, Starman, Twiggy etc.).
>
>
>
> I am interested in a definitive and clear answer as well. I tried the above
> method a while back, and kept on getting errors on transactions (I am using
> SQLite).
>
>
> Admittedly, this (using a common database handle for all connections) might
> not neccesarily be a Good Thing precisely because of transaction issues (and
> consequently slowdowns because of locks) that it leads to.
>
> However one way to handle transaction issues could be to have a per-session
> database handle. Requestor passes a session id to connect_db, which sees if
> the session has been allocated a handle and generates a new handle if not.
> If the session is an old one, it merely picks up the correct previously
> allocated  handle and returns it. If per-session persistence is possible,
> then you should not have transaction issues.
>
> Is that possible?
>
>
>
>
> heh heh! I have no idea, although, the logic that you describe seems very,
> well, logical. Should work. I just don't know how to do it.
>
> If someone who is first-hand experienced in this, and especially with
> different web servers, would do a little write-up, that would be tremendous.
>
>
You could try something like this (NOT TESTED):

my $session_dbh;
sub connect_db {

    my $session_id = shift;

    if (! exists $session_dbh->{$session_id}) {
        my $dbh = DBI->connect("dbi:SQLite:dbname=".setting('database')) or
            die $DBI::errstr;
         $session_dbh->{$session_id} = $dbh;
    }
    return $session_dbh->{$session_id};
}





> --
> Puneet Kishor
> Sent with Sparrow <http://www.sparrowmailapp.com>
> <http://www.sparrowmailapp.com>
>
> Since I am using Apache, I added `PerlModule Apache::DBI` to my httpd.conf,
> and went back to a simple $dbh creation instead of checking for `defined
> $dbh`
>
> Knowing the correct way to implement this, perhaps even the way*s* for
> different servers (I am assuming different web servers might have different
> ways of implementing persistent connections) would be tremendously helpful.
>
> If that is true, a line to that effect in an appropriate place in the
> tutorial would emphasize the persistent features of the solution.
>
> Also, are any hooks run when the application instance is destroyed? If
> there are, implementing a graceful closing of open DB connection(s)
> might provide a valuable lesson in the Tutorial.
>
> Thank you.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.backup-manager.org/pipermail/dancer-users/attachments/20110109/00b3558d/attachment.htm>