Connecting to Multiple Database for Master slave replication

[DevMan12]

Hi,

how do we connect to multiple database? And how would I update my models to connect to different database for reads and for writes. I am working on a master slave replication in the database that is on separate private servers. Is this possible with F3?
TIA

[ikkez]

I think it is possible.. create two database connections, and use those accordingly in your models. If you use the mappers, define a beforesave event handler for them and switch the database object before the record is updated/inserted. On aftersave, flip them back. Maybe like this:

(not tested)

// index
$f3 = \Base::instance();
$f3->DB_READ = new \DB\SQL('mysql:host=server1;port=3306;dbname=mysqldb','username','password');
$f3->DB_WRITE = new \DB\SQL('mysql:host=server2;port=3306;dbname=mysqldb','username','password');

// model
class User extends \DB\SQL\Mapper {

    public function __construct() {
        $f3 = \Base::instance();
        parent::__construct( $f3->get('DB_READ'), 'users' );

        $this->beforesave(function($self,$pkeys) use($f3) {
          //do something before inserting or updating
          $self->db = $f3->get('DB_WRITE');
        });

        $this->aftersave(function($self,$pkeys) use($f3) {
          //do something after inserting or updating
          $self->db = $f3->get('DB_READ');
        });
     }
}

NB: in some conditions the mapper refreshes the newly inserted record and loads it directly once more from the active DB_WRITE before the afterinsert handler is triggered. This is made to ensure that default column values and auto increment keys are fetched correctly and updated in the hydrated mapper object. I think that's fine.

[DevMan12]

Nice one, thanks

[Antonio Diaz]

Using Azure database and a local MySQL under Ubuntu 16.04 with PHP 7.0.22 and Apache2

The way I got them to work was like this, first define the global variables. 

[globals]

DEBUG=3
CACHE=true

db_dns=sqlsrv:Server=m.....1.database.windows.net;Database=<your database name>
db_user=<your username>
db_pass=<your password>

d1_dns=mysql:host=localhost;port=3306;dbname=<your database>
d1_user=<your username>
d1_pass=<your password>


AUTOLOAD=app/controllers/|app/models/

UI=app/views/

On the controller file you define both under __construct

class Controller {

    protected $f3;
    protected $db;
    protected $d1;

   function __construct() {

        $f3=Base::instance();

        $db=new DB\SQL(
            $f3->get('db_dns') . $f3->get('db_name'),
            $f3->get('db_user'),
            $f3->get('db_pass')
        );
        $d1=new DB\SQL(
            $f3->get('d1_dns') . $f3->get('d1_name'),
            $f3->get('d1_user'),
            $f3->get('d1_pass')
        );

        $this->f3=$f3;
        $this->db=$db;
        $this->d1=$d1;
    }

And then on the php module, you can call either $db or $d1

class Login extends DB\SQL\Mapper{

    public function __construct(DB\SQL $d1) {
        parent::__construct($d1,'bpuser');
    }

It works very nice!

Antonio