On Thu, Jan 26, 2006 at 02:04:28AM -0500, Steven Lembark wrote:
>
> > The inner handle carries all the DBI info, including CachedKids.
> > So swap_inner_handle is a simple and total brain transplant.
>
> Q: Any chance of getting "$sth->clone"?
Doubtful. Patches always welcome of course. You've got Statement,
Active, and possibly ParamValues and ParamTypes attributes to work
with so it might be possible, maybe.
> From what I can see in the doc's, the clone method
> is specific to database handles (the statement's clone
> would account for cached queries). Matching up the
> cached vs. un-cached values is doable, but getting
> the bound variables (especially lexicals) correct
> is going to seriously difficult.
Yeap.
> >> Or is some similar maintainence done automatically by the
> >> swap_inner_handle?
> >
> > swap_inner_handle just does what it says - swaps handles. That's it.
> >
> > I suspect what you'll need to do (for maximum transparency) is perform a
> > swap_inner_handle on each of the old dbh's kids to replace the now
> > defunct sth's with new ones freshly prepared using the new dbh.
>
> The trick will be matching the values of CachedKids
> to those in ChildHandles to cache the proper set of
> handles:
>
> DB<1> x $dbh->{CachedKids}
> 0 HASH(0x86900a4)
> ' select now() ' => DBI::st=HASH(0x868ff24)
> empty hash
>
> Otherwise someone using prepare and prepare_cached
> on the same sql string would end up with mismatched
> statements after the reconnect.
No need. It should be harmless for CachedKids of the new dbh to be
empty. Or...
> Q: If the cached kids are stored in $new_dbh, wouldn't I
> overwrite the statement handles when I performed the
> $old_dbh->swap_inner_handle( $new_dbh )?
True. If you swap_inner_handle for all ChildHandles then you'll
automatically deal with CachedKids as well. Simple.
> The only thing this doesn't pull across that I can think
> of is the current row state of each handle
You could use $sth->rows and fetch to the same row count
(but there's no guarantee you'll be getting the same values).
> and the bound variables.
ParamValues and ParamTypes gives you a way to redo bind_param().
But there's no equivalent for bind_param_inout(), nor for bind_columns.
Umm, bind_columns is probably do-able if the DBI gave you a way to
access the current row array. Then you could just bind the new columns
to the old ones.
> Q: From what I can see, $sth->{Type} doesn't give me
> enough information to tell if a particular sth
> is cached or not. Am I missing something?
No. $is_cached = grep { $_ == $sth } values %{$dbh->{CachedKids}};
> > If so then it's kind'a handy that the DBI now has a $h->{ChildHandles}
> > attribute.
> >
> > If you really want to get fancy you could each check $old_sth->rows and
> > then fetch that many rows from the new $sth to leave it in the same
> > 'place' (hopefully!) as the original. Just how mad do you want to be?
>
> Only risk there is accidentally restarting the query-from-hell,
> but that could be avoided with a configuration parmeter
> to DBIx::Viagra.
It's not restarting the query-from-hell that worries me, it's the fact
that rows may have been added or deleted since the first select
(assuming it is a select statement) so the application may process some
rows twice and/or miss some rows.
But if you proceed with this then I'd expect your nice CPAN module will
give people lots of hooks to express what kinds of madness they're
comfortable with :)
> Q: Is there any chance of getting a 'seek' to handle this?
> It would look sometehing like:
>
> $sth->seek( $sth->rows );
>
> and save me from retrieving the entire list of rows into
> core on the local side just to discard them.
No. At least not till someone added even basic support for scrollable
cursors.
> Aside: How likely to change is dbh->clone as of 1.50?
>
> The "clone" method was added in DBI 1.33. It is very new and likely
> to change.
The more widely it's used the less likely it is to change :)
The 'big issue' is how to deal with attribute values that have changed
since the $dbh was created.
Tim.
