[Dbix-class] Different Read and Write Database Handles Supported within DBIx?

[Brian Phillips]

I recall seeing something on CPAN awhile back that might provide either an
interim solution or a starting point for working this into the DBIx::Class
core functionality.  All I can come up with now is DBIx::DBCluster (not sure
if this is what I'm remembering or not).

DISCLAIMER: I've not used this module, it's not had any activity since its
first release in June 2003 and is prominantly labeled as 'beta' code.

It claims to be a drop-in replacement for DBI that dynamically chooses from
an array of read-only and read-write DSN's for the appropriate SQL
statements.  The documentation is pretty sparse so you'd need to grok the
source code to see the implementation details.

FWIW

Brian

On 5/2/06, Brandon Black <blblack at gmail.com> wrote:
>
> On 5/2/06, Matija Grabnar <matija at literal.si> wrote:
> > Nathanial Hendler wrote:
> > > In my situation, I have a setup of one master and many slave
> databases.
> > > Each slave is used for reading, and the master is used to write to.
> > > Does DBIx::Class support this environment?  Can I create a single
> object
> > > and interact with it normally and DBIx knows that
> > > INSERTs/UPDATEs/DELETEs happen on database server A, and SELECTs
> happen
> > > on database server B?
> > >
> > > Any suggestions would be appreciated.
> > >
> > I don't remember seeing anything like that in any DBIC docs. However, if
> > you use an explicit schema with your DBIC class, you can have multiple
> > "schema handles", each with it's own connection data. So for your
> > situation, you could have an array of "read-only" schemas and one
> > "read-write" schema.
> >
> > Unfortunately, from what I can see, your code would have to decide which
> > is appropriate to use when - this level of automation does not seem to
> > be present in DBIx::Class.
> >
>
> I was discussing this (and thinking about implementing it) on the DBIC
> irc channel a little while back, but I've recently dropped off on
> public development to dedicate my time to finishing up my main project
> at my paying $job, and I haven't even been on irc at all lately, so I
> don't know if anyone else has gone further with the concepts yet or
> not.
>
> This kind of thing would be most readily implemented as a new
> DBIx::Class::Storage driver/subclass.  While the existing
> DBIx::Class::Storage::DBI is the only/main Storage class in
> DBIx::Class at the moment, it was designed to be extended (and for
> completely orthogonal Storage classes like ::LDAP to be implemented).
>
> Basically, you'd make a new DBIx::Class::Storage::DBI::Replicated (or
> whatever name, that's just a random one for the sake of conversation),
> and inherit most of the functionality from DBIx::Class::Storage::DBI.
> You'd override methods where neccesary to support passing multiple
> sets of connection information into ->connect_info, with some sort of
> designation as to which ones are readonly and which ones are readwrite
> (In the general case, there could be 0->Many of either class of
> connections, and writing operations should fail if no readwrite
> connections are defined).
>
> Then stick some logic in other overriden areas to do something
> intelligent to spread the load over multiple servers of the same class
> (this needs some careful thought, and perhaps some user-defined
> behavior parameters as well - per-proc/thread (round-robin,
> sequential, random?)? round-robin every X requests? etc), support some
> options like whether to use the readwrite entries as part of the
> readonly list as well for load-spreading purposes, etc.
>
> There's also some other things in the underlying existing Storage::DBI
> driver that need to be cleaned/tweaked first as well.  Things like the
> fork/thread support being suboptimal.  Ideally that should be
> converted from the current "recheck before doing anything" model to a
> more efficient "execute blindly and check for exceptions" model.
> Those things affect the design of the replica-capable subclass, as it
> will probably do similar things (but with slightly different behavior)
> to failover to other replicas, etc.
>
> The only pre-check type of thing that can't be handled in an exception
> style (AFAIK at the moment) is the current "$$ == saved_pid"-thing
> that's in there for watching forks (because when you fork with an open
> DBI connection, DBI actually can continue to work, but will exhibit
> broken behavior and/or warnings and/or exceptions down the line
> depending on races and whatnot).  Thread changes always trigger
> exceptions to watch though.  After any exception, check validity of
> the $dbh (via the current prechecks like ->{ping}, ->{Active}, etc) to
> decide whether to attempt reconnection and retry of the operation (or
> replay of the transaction in the case of ->txn_do?, or exception on
> the transaction if not) (or failover...) or throw upstream to the DBIC
> user for general SQL errors always of course.
>
> I'll eventually get to this if nobody else picks up these things
> before I get back, but anyone out there who feels up to it, feel free
> :)
>
> -- Brandon
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060502/cadae157/attachment-0001.htm