As part of the Solar-1.0.0alpha2 release, we included an SQL adapter that lets you connect to master/slave MySQL installations. Read on for how to set it up using just the config file.

"Regular" MySQL Setup

With Solar, you connect to SQL databases using the Solar_Sql factory class, which returns a Solar_Sql_Adapter class for you.

Most developers only need to connect to a single MySQL server. For example, you could do this as a piece of standalone code:

<?php
// create an SQL connection object
$sql = Solar::factory('Solar_Sql', array(
    'adapter' => 'Solar_Sql_Adapter_Mysql',
    'host'    => 'localhost',
    'user'    => 'username',
    'pass'    => 'password',
    'name'    => 'dbname',
));

// query with placeholders
$stmt = "SELECT foo FROM bar WHERE baz = :baz";
$data = array(
    'baz' => 'dib';
);
$list = $sql->fetchAll($stmt, $data);

// insert a row
$table = 'bar';
$data  = array(
    'foo' => 'another foo',
    'baz' => 'another dib',
);
$sql->insert($table, $data);
?>

In that example you pass the configuration array directly to the factory call, which means the configuration will apply just to that one object.

Savvy Solar developers know that if you want to set a default configuration, you place it in your config file:

<?php
// tell Solar_Sql what adapter to use
$config['Solar_Sql'] = array(
    'adapter' => 'Solar_Sql_Adapter_Mysql',
);

// tell the adapter how to set itself up
$config['Solar_Sql_Adapter_Mysql'] = array(
    'host' => 'localhost',
    'user' => 'username',
    'pass' => 'password',
    'name' => 'dbname',
);
?>

Then whenever you call Solar::factory('Solar_Sql') without a configuration, it will use the defaults from the config file.

"Replicated" MySQL Connection

However, when you get into a situation where you need to scale up, you might need a replicated MySQL database setup. In such cases, there is one "master" server that handles reads and writes, and there are one or more "slave" servers that are read-only. This means you need to pick which server you're going to connect to based on the kind of statement you need to issue (SELECT, INSERT, UPDATE, etc).

The Solar_Sql_Adapter_MysqlReplicated adapter handles all the connections and switching-around for you, and you don't need change a single line of application code. All you need to do is modify your configuration file to tell it where your master and slave servers are.

<?php
// tell Solar_Sql to use the MysqlReplicated adapter
$config['Solar_Sql'] = array(
    'adapter' => 'Solar_Sql_Adapter_MysqlReplicated',
);

// point to one master server and two slave servers
$config['Solar_Sql_Adapter_MysqlReplicated'] = array(
    // master server; also acts as default values for
    // the slave servers
    'host' => '192.168.1.100',
    'port' => null,
    'user' => 'mainuser',
    'pass' => 'mainpass',
    'name' => 'dbname',

    // the slave servers. note that they use the master
    // server values as their defaults, so if the username,
    // password, and database values are the same on all
    // servers, you don't need to re-enter them here.
    'slaves' => array(
        0 => array(
            'host' => '192.168.1.101',
        ),
        1 => array(
            'host' => '192.168.1.102',
        ),
    ),
);
?>

The adapter now looks at the first few characters of every statement to see if it's a "read" or "write":

• If the statement is a "read" (i.e. a SELECT) then the adapter randomly picks a slave server, lazy-connects to it, and issues the statement against that slave server. The adapter maintains this first connection, so once it's "on" a slave server, it stays there.

• If the statement is a "write" (e.g. INSERT, UPDATE, DELETE) then the adapter creates a second internal connection pointing to the master, and then issues the statement against the master server. This connection is maintained separately from the slave connection.

Our example application code from above does not need to change at all:

<?php
// query with placeholders.
// uses the slave server automatically.
$stmt = "SELECT foo FROM bar WHERE baz = :baz";
$data = array(
    'baz' => 'dib';
);
$list = $sql->fetchAll($stmt, $data);

// insert a row.
// uses the master server automatically.
$table = 'bar';
$data  = array(
    'foo' => 'another foo',
    'baz' => 'another dib',
);
$sql->insert($table, $data);

// another query with placeholders.
// uses the slave server automatically.
$stmt = "SELECT dib FROM zim WHERE gir = :gir";
$data = array(
    'gir' => 'irk';
);
$list = $sql->fetchAll($stmt, $data);
?>

Questions? Comments? Please leave them below, or feel free to join the Solar mailing-list and IRC channel for more extensive discussion.