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

[Brandon Black]

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