I would like to call mysql stored procedures as if they were local php functions.

For example, let’s use the following mysql stored procedure,

DELIMITER //
CREATE PROCEDURE `usersByPage`(page_num INT, per_page INT)
BEGIN
    SET @lim_start = (page_num - 1) * per_page;
    SET @lim_end = per_page;
    PREPARE stmt FROM 'SELECT * FROM foobar_users
                       ORDER BY user_id LIMIT ?,?';
    EXECUTE stmt USING @lim_start, @lim_end;
END //
DELIMITER ;

The following unit test demonstrates a typical PDO-style call to a stored procedure.

    public function test_pdo_call() {
        $db = new DB();
        $stmt = $db->prepare('CALL usersByPage(?,?)');
        $stmt->execute(array(1,9));
        $users = $stmt->fetchAll();
        $this->assertEquals(9,count($users));
    }

I would like to call the usersByPage() procedure in php as if it were a php function. The unit tests would look something like this,

    public function test_magic_proc() {
        $db = new DB();
        $users = $db->usersByPage(1,9);
        $this->assertEquals(9,count($users));
    }

    public function test_magic_proc_static() {
        $users = DB::usersByPage(1,9);
        $this->assertEquals(9,count($users));
    }

Rather than wrap each stored procedure in a php function, we can use php magic __call() and __callStatic() methods to proxy, which can be implemented with something like the following,

    public static function _proxy($proc, $params) {
        $conn = self::connection();
        $bind_params = trim( str_repeat('?,',count($params)), ',');
        $stmt = $conn->prepare("CALL $proc($bind_params)");
        $params ? $stmt->execute($params) : $stmt->execute();
        return $stmt->fetchAll();
    }

    public function __call($method, $params) {
        return self::_proxy($method, $params);
    }

    public static function __callStatic($method, $params) {
        return self::_proxy($method, $params);
    }

This approach can easily be integrated with the DB adapter discussed previously, e.g.,

<?php
use config\DB as conf;

/**
 * DB adapter, supports:
 * * PDO interface
 * * encapsulate database handles
 * * dynamically choose read or write handle per call
 * * magic invocation and lazy connection loading
 * * magic calls to stored procedures
 *
 **/
class DB {

    private static $wdb = false;
    private static $rdb = false;

    public static function write_master() {
        if (!self::$wdb) {
            self::$wdb = new \PDO(conf::WDSN, conf::WDB_USER, conf::WDB_PASSWORD);
        }
        return self::$wdb;
    }

    public static function read_slave() {
        if (!self::$rdb) {
            self::$rdb = new \PDO(conf::RDSN, conf::RDB_USER, conf::RDB_PASSWORD);
        }
        return self::$rdb;
    }

    /**
     * dynamically select write master or read slave
     *
     **/
    public static function connection($hint = false) {
        if ($hint and
                stripos($hint,'select') === 0 and
                stripos($hint,'last_insert_id') == false) {
            return self::read_slave();
        } else {
            return self::write_master();
        }
    }

    /**
     * MAGIC, object invocation
     * * fully encapsulates connection (read vs write) handle
     * * supports both prepared statements and sql execution
     *
     * $db = new DB();
     * $array1 = $db('SELECT * FROM foo');
     * $array2 = $db('SELECT * FROM foo WHERE bar = ?', $params);
     **/
    public function __invoke($sql, $params = false) {
        $conn = self::connection($sql);
        $stmt = $conn->prepare($sql);
        $params ? $stmt->execute($params) : $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * call stored procedure
     *
     * available only through magic __call or __callStatic,
     * * e.g., to call a stored procedure 'getUserById'
     * $db = new DB();
     * $user = $db->getUserById($id);
     **/
    private static function callStoredProc($conn, $proc, $params) {
        $bind_params = trim( str_repeat('?,',count($params)), ',');
        $stmt = $conn->prepare("CALL $proc($bind_params)");
        $params ? $stmt->execute($params) : $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * MAGIC, proxy methods to appropriate PDO connection, or
     * * call stored procedure.
     *
     **/
    private static function _proxy($method, $params) {
        $sql = false;
        if (in_array($method, array('query', 'execute')) ) {
            $sql = $params[0];
        }
        $conn = self::connection($sql);
        if (method_exists($conn, $method)) {
            return call_user_func_array(array($conn,$method), $params);
        } else {
            return self::callStoredProc($conn, $method, $params);
        }
    }

    public function __call($method, $params) {
        return self::_proxy($method, $params);
    }

    public static function __callStatic($method, $params) {
        return self::_proxy($method, $params);
    }

}

/**
 * MAGIC, functional invocation
 *
 * $array = DB('SELECT * FROM foo');
 **/
function DB($sql, $params = false) {
    $db = new DB();
    return $db($sql, $params);
}

?>

Next, I would like to separate this functionality into components using a Decorator design pattern.