Re: using callbacks with connect_cached
Both merged. Thanks! Tim. On Tue, Sep 03, 2013 at 11:07:15AM -0700, David E. Wheeler wrote: > On Aug 25, 2013, at 12:10 PM, Tim Bunce wrote: > > > p.s. I'd be delighted to get a doc patch that notes the need to use a > > lexical for the callbacks hashref on connect_cached. > > Submitted: https://github.com/perl5-dbi/dbi/pull/2 > > > p.p.s. I'd also consider a patch to add a 'connect_cached.connected' > > callback for new connections. > > Good idea! How’s this? https://github.com/perl5-dbi/dbi/pull/3 > > David >
Re: using callbacks with connect_cached
On Aug 25, 2013, at 12:10 PM, Tim Bunce wrote: > p.s. I'd be delighted to get a doc patch that notes the need to use a > lexical for the callbacks hashref on connect_cached. Submitted: https://github.com/perl5-dbi/dbi/pull/2 > p.p.s. I'd also consider a patch to add a 'connect_cached.connected' > callback for new connections. Good idea! How’s this? https://github.com/perl5-dbi/dbi/pull/3 David
Re: using callbacks with connect_cached
On Sun, Aug 25, 2013 at 01:38:57PM +0200, David E. Wheeler wrote:
> On Aug 24, 2013, at 9:40 PM, Vincent Veyron wrote:
>
> You can also use a private attribute in connect_cached.new to tell the
> connected callback to do its thing:
>
> my $cb = {
> 'connect_cached.new' => sub {
> $_[4]->{private_is_new} = 1;
> return;
> },
> connected => sub {
> my $dbh = shift;
> warn "connected\n" if delete $dbh->{private_is_new};
> return;
> }
> };
>
> for (1..3) {
> DBI->connect_cached('dbi:SQLite:', '', '', {
> PrintError => 0,
> RaiseError => 1,
> AutoCommit => 1,
> Callbacks => $cb,
> });
> }
>
> This emits "connected" only once.
If you're going to use a callback on connected and an attribute then
there's no need to also use connect_cached.new. Just this should do:
connected => sub {
my $dbh = shift;
if (++$dbh->{private_connected_count} == 1) {
... # is new connection
}
return;
}
Tim.
p.s. I'd be delighted to get a doc patch that notes the need to use a
lexical for the callbacks hashref on connect_cached.
p.p.s. I'd also consider a patch to add a 'connect_cached.connected'
callback for new connections.
Re: using callbacks with connect_cached
On Aug 24, 2013, at 9:40 PM, Vincent Veyron wrote:
> Not quite : I thought connected_cache.new would enable me to set the
> correct datestyle once for a cached connection, and then be cached with
> it, but we've seen that won't work.
>
> Using the connected callback would do a 'set datestyle to xyz' for each
> connection, even if cached. I realize it's no big deal (.5ms on my lowly
> Atom processor), but every little bit helps, as I'm sure you know.
>
> So I now set the datestyle when the user logs in (hence once, with
> $dbh->do('SET datestyle TO xyz'), and the database handle is cached and
> reused as is.
You can also use a private attribute in connect_cached.new to tell the
connected callback to do its thing:
my $cb = {
'connect_cached.new' => sub {
$_[4]->{private_is_new} = 1;
return;
},
connected => sub {
my $dbh = shift;
warn "connected\n" if delete $dbh->{private_is_new};
return;
}
};
for (1..3) {
DBI->connect_cached('dbi:SQLite:', '', '', {
PrintError => 0,
RaiseError => 1,
AutoCommit => 1,
Callbacks => $cb,
});
}
This emits "connected" only once.
Best,
David
Re: using callbacks with connect_cached
Le samedi 24 août 2013 à 21:01 +0200, David E. Wheeler a écrit :
> On Aug 24, 2013, at 1:32 AM, Vincent Veyron wrote:
>
> >> Pretty sure the connected callback will work, too.
> >
> > It certainly does, I've checked, but this callback would add a query to
> > each connection.
>
> Is that not what you want?
Hi David,
Not quite : I thought connected_cache.new would enable me to set the
correct datestyle once for a cached connection, and then be cached with
it, but we've seen that won't work.
Using the connected callback would do a 'set datestyle to xyz' for each
connection, even if cached. I realize it's no big deal (.5ms on my lowly
Atom processor), but every little bit helps, as I'm sure you know.
So I now set the datestyle when the user logs in (hence once, with
$dbh->do('SET datestyle TO xyz'), and the database handle is cached and
reused as is.
--
Salutations, Vincent Veyron
http://marica.fr/
Gestion des contrats, des contentieux juridiques et des sinistres
d'assurance
Re: using callbacks with connect_cached
On Aug 24, 2013, at 1:32 AM, Vincent Veyron wrote: >> Pretty sure the connected callback will work, too. > > It certainly does, I've checked, but this callback would add a query to > each connection. Is that not what you want? David
Re: using callbacks with connect_cached
Le vendredi 23 août 2013 à 20:33 +0200, David E. Wheeler a écrit : > Pretty sure the connected callback will work, too. > It certainly does, I've checked, but this callback would add a query to each connection. If you care to see the code in action, it's visible here : http://as-pro.biz/clients/login?nom_utilisateur=demo-gmc&mot_de_passe=demo-gmc (it's a demo, you can click away) -- Salutations, Vincent Veyron http://vincentveyron.com Logiciels de gestion des sinistres assurance et des contentieux pour le service juridique
Re: using callbacks with connect_cached
On Aug 23, 2013, at 7:13 PM, Vincent Veyron wrote: > Can't call method "do" on an undefined value > at /home/lib/Aspro/db_handle.pm > > which seems logical, since DBI.pm's documentation says : > > "connect_cached.*" callbacks are invoked before connect_cached() has > applied the connect attributes > > But I found a middle ground (setting the datestyle once at login time, > it is then kept by the cached connection) Pretty sure the connected callback will work, too. David
Re: using callbacks with connect_cached
Le vendredi 23 août 2013 à 16:25 +0200, David E. Wheeler a écrit :
> Oh, you know what? I think this is why the example in the docs and my blog
> post puts the callback into a lexical variable, then just passes that
> lexical. This is because the params passed to connect_cached(), if they vary
> at all, create different connections. You call connect_cached() three times,
> and construct the Callbacks hash reference anew each time. So it is different
> each time.
>
> Try this:
>
> my $cb = {
> 'connect_cached.reused' => sub { print "reused!!!"; return; },
> };
> for my $test (1..3) {
> my $cached_dbh = MyApp::SQLite->connect_cached(
> "dbi:SQLite:dbname=$dbfile", '', '',
> {
> RaiseError => 1,
> PrintError => 0,
> Callbacks => $cb,
> }
> );
> ...
> }
>
That did the trick! Congratulations for finding it.
I still can't use connect_cached.new for my original purpose, which was
to send an SQL query setting the correct datestyle for a new connection;
the error returned is :
Can't call method "do" on an undefined value
at /home/lib/Aspro/db_handle.pm
which seems logical, since DBI.pm's documentation says :
"connect_cached.*" callbacks are invoked before connect_cached() has
applied the connect attributes
But I found a middle ground (setting the datestyle once at login time,
it is then kept by the cached connection)
Thank you for your help.
--
Salutations, Vincent Veyron
http://marica.fr/
Gestion des contrats, des contentieux juridiques et des sinistres
d'assurance
Re: using callbacks with connect_cached
On Aug 23, 2013, at 12:22 PM, Vincent Veyron wrote:
> As you can see, no caching happens when any callback is present in the
> connect method (connected, connect_cached.new, or
> connect_cached.reused).
Oh, you know what? I think this is why the example in the docs and my blog post
puts the callback into a lexical variable, then just passes that lexical. This
is because the params passed to connect_cached(), if they vary at all, create
different connections. You call connect_cached() three times, and construct the
Callbacks hash reference anew each time. So it is different each time.
Try this:
my $cb = {
'connect_cached.reused' => sub { print "reused!!!"; return; },
};
for my $test (1..3) {
my $cached_dbh = MyApp::SQLite->connect_cached(
"dbi:SQLite:dbname=$dbfile", '', '',
{
RaiseError => 1,
PrintError => 0,
Callbacks => $cb,
}
);
...
}
Best,
David
Re: using callbacks with connect_cached
Hi David, To make sure my problem was not tied to mod_perl, I modified sligthly the SQLite app that you published on this page : http://www.justatheory.com/computers/databases/sqlite/ so that it uses connect_cached, connecting 3 times for a simple select on the 'try' table. Here is a paste of the modified code, followed by its output : http://pastebin.com/aR8YH1zR As you can see, no caching happens when any callback is present in the connect method (connected, connect_cached.new, or connect_cached.reused). -- Salutations, Vincent Veyron http://marica.fr/ Gestion des contrats, des contentieux juridiques et des sinistres d'assurance
Re: using callbacks with connect_cached
Le jeudi 22 août 2013 à 23:32 +0200, David E. Wheeler a écrit :
> What if you change the callback to return a true value?
>
Do you mean like this (return 1 instead of simply return)?
my $dbh = DBI->connect_cached( "DBI:Pg:dbname=aspro", 'www-data',
undef, {
PrintError =>1,
RaiseError => 1,
AutoCommit => 1,
pg_bool_tf => 't',
private_preferred_datestyle => $preferred_datestyle,
Callbacks => {
"connect_cached.new" => sub {
warn "new handle";
return 1;
},
}
} )
No change, a new handle is created on every request.
--
Salutations, Vincent Veyron
http://marica.fr/
Gestion des contrats, des contentieux juridiques et des sinistres
d'assurance
Re: using callbacks with connect_cached
On Aug 22, 2013, at 10:53 PM, Vincent Veyron wrote: >> Note that DBI will reset all the attributes every time connect_cached is >> called. Maybe you need to delete one or more of the attributes in a >> connect_cached.reused callback? > > The problem is that connect_cached.reused never gets triggered (hence > the absence of caching) Right, duh. What if you change the callback to return a true value? David
Re: using callbacks with connect_cached
Le jeudi 22 août 2013 à 18:52 +0200, David E. Wheeler a écrit :
> On Aug 22, 2013, at 8:28 AM, Vincent Veyron wrote:
> So, do you get a new handle for every query *without* the callback?
>
Caching works as advertised if I take out the callback. Here's how
CachedKids looks after a couple dozen requests :
'cached_kids' => '$VAR1 = {
\'dbname=aspro!www-data!!AutoCommit='1',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='SQL,
dmy'\' => bless( {}, \'DBI::db\' )
};
Only two handles, as expected.
> Note that DBI will reset all the attributes every time connect_cached is
> called. Maybe you need to delete one or more of the attributes in a
> connect_cached.reused callback?
The problem is that connect_cached.reused never gets triggered (hence
the absence of caching)
Re: using callbacks with connect_cached
On Aug 22, 2013, at 8:28 AM, Vincent Veyron wrote: >> Try the connected callback, instead: >> >> http://justatheory.com/computers/databases/postgresql/execute-on-select.html >> > > > Hi David, > > I did study your fine article for my research, thank you for that :-) > > But you must have overlooked the part of my post where I write that the > connected callback creates a new handle for each query in the database; > no caching happens as one can see with CackedKids after a few queries Oy, yes, sorry, I did miss that. > Apache is configured with MaxClients=1, so that only one process is > active. Stranger and stranger. > There should be only two cached handles (one with > private_preferred_datestyle ='iso', one 'SQL, dmy') : So, do you get a new handle for every query *without* the callback? Note that DBI will reset all the attributes every time connect_cached is called. Maybe you need to delete one or more of the attributes in a connect_cached.reused callback? http://www.justatheory.com/computers/programming/perl/dbi-connect-cached-hack.html HTH, David
Re: using callbacks with connect_cached
Le jeudi 22 août 2013 à 00:08 +0200, David E. Wheeler a écrit :
> Try the connected callback, instead:
>
> http://justatheory.com/computers/databases/postgresql/execute-on-select.html
>
Hi David,
I did study your fine article for my research, thank you for that :-)
But you must have overlooked the part of my post where I write that the
connected callback creates a new handle for each query in the database;
no caching happens as one can see with CackedKids after a few queries
Apache is configured with MaxClients=1, so that only one process is
active.
There should be only two cached handles (one with
private_preferred_datestyle ='iso', one 'SQL, dmy') :
'cachedkids' => '$VAR1 = {
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fb1c480)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919faf2c78)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fad0768)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fb0dd68)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='SQL,
dmy'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fb0d000)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fb2c9f0)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fa76b88)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='iso'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit='1',Callbacks='HASH(0x7f919fab1d00)',PrintError='1',RaiseError='1',Username='www-data',dbi_connect_method='connect_cached',pg_bool_tf='t',private_preferred_datestyle='SQL,
dmy'\' => bless( {}, \'DBI::db\' )
};
--
Salutations, Vincent Veyron
http://marica.fr/
Gestion des contrats, des contentieux juridiques et des sinistres
d'assurance
Re: using callbacks with connect_cached
On Aug 21, 2013, at 8:09 PM, Vincent Veyron wrote:
>my $dbh = DBI->connect_cached( "DBI:Pg:dbname=aspro", 'www-data',
> undef, {
> PrintError =>1,
> RaiseError => 1,
> AutoCommit => 1,
> pg_bool_tf => 't',
> private_preferred_datestyle => $preferred_datestyle,
>
> Callbacks => {
> "connect_cached.new" => sub {
> shift->do(qq{
> SET datestyle TO $preferred_datestyle
> });
>return;
> }
>}
>
> } )
> or croak "Cannot connect to db: $DBI::errstr";
>
>return $dbh;
>
> }
Try the connected callback, instead:
http://justatheory.com/computers/databases/postgresql/execute-on-select.html
Best,
David
using callbacks with connect_cached
Hi list,
I use a package to return a cached connection for a mod_perl application
connected to a Postgresql db, like so :
my $dbh_data = Aspro::db_handle::get_dbh_data($preferred_datestyle) ;
where $preferred_datestyle is either 'iso' or 'SQL, dmy', and is used for
setting date formats in the user's session.
I then do :
$dbh_data->do( 'SET datestyle TO ?', undef, ( $preferred_datestyle ) ) ;
to set the datestyle.
Following the documentation, I would like to use instead a callback to set the
date format at dbh creation time as you can see in my package's code below, but
can't get it right.
Using the "connect_cached.new" key for the callback returns an error :
Can't call method "do" on an undefined value at /home/lib/Aspro/db_handle.pm
Using the "connected" key works, but creates a new db handle for each
connection, as shown by Data::Dumper in CachedKids (apologies for the strangely
encoded characters)
'cachedkids' => '$VAR1 = {
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f642d80)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'iso\\\'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f645990)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f6449b8)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f641208)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34fb80aa0)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f63fcf0)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f643980)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=sessions!www-data!!AutoCommit=\\\'1\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff34f643b18)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'iso\\\'\'
=> bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff350e68e58)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff350e447b8)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff350de4990)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' => bless( {}, \'DBI::db\' ),
\'dbname=aspro!www-data!!AutoCommit=\\\'1\\\',Callbacks=\\\'HASH(0x7ff350e69368)\\\',PrintError=\\\'1\\\',RaiseError=\\\'1\\\',Username=\\\'www-data\\\',dbi_connect_method=\\\'connect_cached\\\',pg_bool_tf=\\\'t\\\',private_preferred_datestyle=\\\'SQL,
dmy\\\'\' =>
