Skip to content
Application Database Loadbalancing for MySQL
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
tests
LICENSE
README.md
adldb.class.php
adldbexceptions.class.php
autoload.php
changelog.md
phpunit.xml.dist

README.md

ADL

Application Database Load balancing for MySQLi.

(c) Copyright 2018 Israel Iglesias (adlquestions@gmail.com).

Released under the GPL license v3.

REQUIREMENTS

PHP 5.3.3+ and MySQLi.

INSTALLATION

Unpack to the directory of your choice.

Include/require the autoload.php file in your application, or if you prefer just require the class file itself: require "adldb.class.php"

USAGE

Object instantiation and server connection pool:

<?php
use ADL\DB;

require 'adldb.class.php';

// define master and/or slave servers
$dbpool["write"] = array();
$ndx = 0;
$dbpool["write"][$ndx]              = array();
$dbpool["write"][$ndx]["host"]      = "127.0.0.1";
$dbpool["write"][$ndx]["user"]      = "adl";
$dbpool["write"][$ndx]["password"]  = "qwerty";
$dbpool["write"][$ndx]["database"]  = "adl";
$dbpool["write"][$ndx]["encoding"]  = "utf8";
$dbpool["write"][$ndx]["max_lag"]   = -1;
$ndx++;
$dbpool["write"][$ndx]              = array();
$dbpool["write"][$ndx]["host"]      = "127.0.0.1";
$dbpool["write"][$ndx]["user"]      = "adl";
$dbpool["write"][$ndx]["password"]  = "qwerty";
$dbpool["write"][$ndx]["database"]  = "adl";
$dbpool["write"][$ndx]["encoding"]  = "utf8";
$dbpool["write"][$ndx]["max_lag"]   = 0;

// define read only databases
$dbpool["read"]	= array();
$ndx = 0;
$dbpool["read"][$ndx]               = array();
$dbpool["read"][$ndx]["host"]       = "127.0.0.1";
$dbpool["read"][$ndx]["user"]       = "adl";
$dbpool["read"][$ndx]["password"]   = "qwerty";
$dbpool["read"][$ndx]["database"]   = "adl";
$dbpool["read"][$ndx]["encoding"]   = "utf8";
$dbpool["read"][$ndx]["max_lag"]    = 0;
$ndx++;
$dbpool["read"][$ndx]               = array();
$dbpool["read"][$ndx]["host"]       = "127.0.0.1";
$dbpool["read"][$ndx]["user"]       = "adl";
$dbpool["read"][$ndx]["password"]   = "qwerty";
$dbpool["read"][$ndx]["database"]   = "adl";
$dbpool["read"][$ndx]["encoding"]   = "utf8";
$dbpool["read"][$ndx]["max_lag"]    = 0;

$db = new ADLDB($dbpool);

As you probably already noticed the connection array above states a database to be used (adl). It is not mandatory to specify a database in your connections array. If you do not state a database you can use the useDatabase() method or include the database in your queries. Please note that when using the useDatabase() method you must also state whether you want a read or a write server. This is because the class has no way of knowing if you will be using read or write queries.

Example:

<?php
use ADL\DB;

$db = new ADLDB($dbpool, $config);
$db->useDatabase('adl', ADLDB::READ_WRITE);

You can also initialize the object with specific configuration directives:

<?php
use ADL\DB;

$dbpool["write"] = array();
...

$config["cacheDir"] = "/var/tmp/adl/";
$config["disableServerCache"] = false;
$config["defaultWriteIndex"] = 0;
$config["lagCheckErrorThrowsException"] = true;
$config["defaultDeadServerTimeout"] = 5;
$config["lockTimeoutForCacheFile"] = 250;

$db = new ADLDB($dbpool, $config);

Query Execution

You have three available methods for query execution, exec(), query() and row().

exec() should be used when you do not expect any results from your query (for example an UPDATE or INSERT statement).

query() will return all fetched results from your query.

row() will return only the first result from your query.

<?php
use ADL\DB;

$db = new ADLDB($dbpool, $config);

$sql = "UPDATE mytable SET n=10 WHERE id=1";
$res1 = $db->exec($sql);

$sql = "SELECT * FROM newtable";
$res2 = $db->query($sql);

$sql = "SELECT * FROM newtable limit 0,1";
$res3 = $db->row($sql);

// You can also fetch your results again by calling
$lastQueryResults = $db->getResultSet();

Operational Modes The class will automatically detect if your query is a read operation (such as SELECT) or a write operation (such as INSERT). If you prefer you can also force the operational mode to either read or write.

For example:

<?php
use ADL\DB;

$db = new ADLDB($dbpool, $config);

// force READ ONLY operations (targets read only servers)
$res = $db->query($sql, ADLDB::READ_ONLY);

// force READ WRITE operations (targets write servers)
$res = $db->query($sql, ADLDB::READ_WRITE);

The operational mode will be used until the next query is run. If you want to lock operations for a number of queries you can lock them using the LOCK_OPER constant:

<?php
use ADL\DB;

$db = new ADLDB($dbpool, $config);

// force READ WRITE operations (targets write servers)
$sql = "INSERT INTO mytable (fname, lname, balance) VALUES ('first name', 'last name', 100)";

if ( $dbconn->exec($sql, ADLDB::READ_WRITE|ADLDB::LOCK_OPER) ) {
    $userId = $db->getInsertID();
    $sql = "SELECT * FROM products WHERE cost < 100";
    $res = $db->query($sql);
    ...
    $sql = "UPDATE mytable SET balance=$newBalance WHERE id=$userId";
    $db->exec($sql);
}

To remove the operational lock simply run your next query with either ADLDB::READ_ONLY or ADLDB::READ_WRITE.

Transactions

You can run transactions using the beginTransaction() method. Transactions will always run in a write server, so the READ_WRITE operational mode will be set and operations will be locked until the end of the transaction.

It is not possible to change the operational mode while a transaction is running. Any attempt to change the operational mode will throw an exception.

Running a transaction:

<?php
use ADL\DB;

$db = new ADLDB($dbpool, $config);

$db->beginTransaction();

$sql = "INSERT INTO mytable (fname, lname, balance) VALUES ('first name', 'last name', 100)";
$db->exec($sql);
$userId = $dbconn->getInsertID();
$sql = "SELECT * FROM products WHERE cost < 100";
$res = $db->query($sql);
...
$sql = "UPDATE mytable SET balance=$newBalance WHERE id=$userId";
$db->exec($sql);
$db->commit();

At the end of your transaction you should either commit() or rollback().

You can’t perform that action at this time.