Re: [fw-db] Handling replicated MySQL databases

http://www.nabble.com/Database-profiles-for-reading-writing-with-Zend_Db-to20010673.html#a20016832

-Matt

On Mon, Oct 20, 2008 at 3:24 AM, clive <clive_lists@immigrationunit.com> wrote:

Hello Greg and others,

Did you find a suitable solution for this scenario, Im sitting in the same boat as you.

Has anyone written a useful class to do Master->Slave replication with Zend Framework?

Clive

Greg Frith wrote:

HI all,

Thanks so much for everyone's contributions and solutions to this problem to date.  Unfortunately I've had to divert my attention on the project to other matters.  However, I am watching the thread with great interest and will feedback as soon as I have a solution in place.

Cheers,
Greg.

On 9 Jul 2008, at 10:15, Tuan Ngo wrote:

> class My_Db_MasterTable extends Zend_Db_Table_Abstract
> ...
> class My_Db_SlaveTable extends My_Db_Table
It seems that this approach will end up to duplicate code of "read method" because in a transaction of an action code, we may need some read/write method of model run together which will use "master connection" and in other action we only use read method of models only which use "slave connection". So we end up in having two versions of a Model:
- one based on My_Db_MasterTable which contains insert/delete/update methods and read methods. for example BuddyOnMasterModel
- one based on My_Db_SlaveTable which contains read methods only. (for example BuddyOnSlaveModel)

If my above speculation is correct, we have to identify exact kind of model (base on Master or Slave) in action code.


So I suggest that:
1. make we don't make decision of master/slave connection in models class level but controller class level by
- handle switch connection automaticaly in init() of controller base class.
- default connection should be master connection, so in the worst case, all transaction run well (as Greg mentioned).
- base on name pattern of action (for example, get*Action, search*Action), automatically switch to read connection.
- sure that connection switching is repeate after _forward($otherAction) is called.
of course, we need to enforce a convention of action name in team so this design will work.

2. provide protect mechanism (nearly the same as Bill mentioned) by
- adding one more config isMaster for adapter and raise exception if expected master connection doesn't found in CRUD method

In config.ini
[database]
db.master.adapter = PDO_MYSQL

db.master.config.host = 10.0.0.1 <http://10.0.0.1>

.....
db.master.config.isMaster = true

db.slave.adapter = PDO_MYSQL

db.slave.config.host = 10.0.0.2 <http://10.0.0.2>

...
db.slave.config.isMaster = false

In Table base class
class Master_Slave_Db_Table extends  Zend_Db_Table_Abstract {
   public function insert(array $data){
       $this->checkMasterConnection();              return parent::insert($data);
   }
     private function checkMasterConnection(){
       $config = $this->getAdapter()->getConfig();
       $isMaster = (boolean)$config["isMaster"];
       if( $isMaster == false){
           throw new Exception("Expected connection to master database");
       }          }
}

if we follow this design it seems that model and controller classes almost don't aware about which connection they use.

I also attach some example code to illustrate my idea.

- Tuan Ngo.
<ReplicateMysqlTest.zip>