Jon Bulava Wed Jun 15 16:44:39 -0400 2011

Subject: Optionally selecting connection on a per query basis

I'm working with a high traffic application that has multiple databases set up for the purpose of separating reads and writes. I know that it is possible to set a model's static connection property, but is it possible to do so when calling a find, save, etc? Since I don't believe this is an available option, the only approach I could come up with is overriding the extended functions in my model classes and setting the model's static connection before calling the parent function. This seems slightly tedious and I'm not even sure if it would work correctly. Has anyone else come up with a solution for selecting from your predefined connections when calling model functions?

Daniel Lowrey Thu Jun 16 02:54:57 -0400 2011

Bumped for great justice.

I've spent the last hour trying to work this out with little success. It seems the most straight-forward way to do it would be how you said, extending the Model to select the correct connection when reads/writes take place. I'm not sure what the 'official' way would be.

Any guidance from the developers here? IMHO basic database replication setups are ubiquitous enough to merit some treatment on this topic -- it's generally one of the first scaling mechanism developers employ. Knowing there's a simple solution likely would go a long way towards more widespread adoption of PHP-AR.

matt dennebaum Thu Jun 16 09:56:33 -0400 2011

+1 agreed!

Daniel Lowrey Thu Jun 30 03:55:05 -0400 2011

Re-bumped for even greater justice!

I spent a bit of time today looking through the php-ar source and here's one way to select the connection on a per query basis. First off, we'll need to specify all of the connections we'll be using up front when we initialize ActiveRecord:

1 // Specify available connection strings
2 $conns = array('write' => 'mysql://user:pass@',
3   'read' => 'mysql://user:pass@');
5 // Initialize ActiveRecord configuration
6 ActiveRecord\Config::initialize(function($c) use ($conns) {
7   $c->set_model_directory('path/to/models/directory/');
8   $c->set_connections($conns);

Only the default connection is established to start, so you can specify as many different connection strings as you like. We now need to create a base model that our other models will inherit from. The code comments are self-explanatory but I'll elucidate a bit more after the snippet below:

 1 abstract class ReplicationModel extends ActiveRecord\Model {
 3   // Specify the default connection for this model
 4   static $connection = 'read';
 5   protected $master_db = 'write';
 7   /**
 8    * Changes the model's active database connection.
 9    * 
10    * An instance of the ActiveRecord ConnectionManager class
11    * singleton is used to ensure we don't open wasteful new
12    * connections all over the place.
13    * 
14    * The function returns the name of the connection being
15    * replaced.
16    *
17    * @param string $name New connection name
18    * @return string Old connection name
19    * @throws ActiveRecord\DatabaseException on invalid connection name
20    */
21   public function switch_connection($name) {
23     $cfg = ActiveRecord\Config::instance();    
24     $valid = $cfg->get_connections();
25     if ( ! isset($valid[$name])) {
26       throw new ActiveRecord\DatabaseException('Invalid connection specified');
27     }
29     // Get the name of the current connection
30     $old = self::$connection;
32     $cm = ActiveRecord\ConnectionManager::instance();
33     $conn = $cm::get_connection($name);
34     static::table()->conn = $conn;
36     return $old;
37   }
39   /**
40    * Routes save operations to "write" connection then
41    * switches back to the "read" db connection.
42    *
43    * We add the $validate parameter because the parent
44    * save method specifies its inclusion.
45    */
46   public function save($validate=TRUE) {
47     $slave_db = $this->switch_connection($this->master_db);
48     parent::save($validate);
49     $this->switch_connection($slave_db);
50   }
52   /**
53    * Routes delete operations to "write" connection then
54    * switches back to the "read" db connection.
55    */
56   public function delete() {
57     $slave_db = $this->switch_connection($this->master_db);
58     parent::delete();
59     $this->switch_connection($slave_db);
60   }
62 }

What's going on here is that we've extended the base Model class by adding a switch_connection() method that allows us to choose the model's active connection anytime we like. This new method retrieves the connection using the ActiveRecord\ConnectionManager singleton class. This is important because it keeps us from creating multiple connections to the same database.

We then override the existing save() and delete() functions so that they switch to the master ('write') database to perform their query then re-establish the slave ('read') connection as the active connection for the model. These are the only two functions we need to override because all the convenience methods map to them.

From here we have our models inherit from this new ReplicationModel class and our work is done.

1 class MyModel extends ReplicationModel {}
3 // The default connection is established when the model is instantiated
4 $m = new MyModel();
5 $m->my_property = 123;
7 // A new connection is made to the master database to save the model
8 $m->save();

A couple of notes:

-This assumes a replication environment. Your table schema should be EXACTLY the same in all the connections you're using or you're just asking for trouble.

- You could easily specify several read servers and add a function to "randomly" select a slave server for use with read queries as a primitive load-balancer.

- Models always make a connection to the default database specified by static $connection when they are instantiated. There is no way to change this without hacking up some of the php-ar internals. So, using this example you'll have two separate connections open if you perform a write query.

- You could do away with the new save() and delete() functions if you wanted and just call $m->switch_connection('write') and $m->switch_connection('read') manually before you save/delete/find etc ...