Writing Custom Database Services

This document is intended for the advanced user who wishes to replace Junction's default database abstraction object (DAO) with a custom implementation. For example, the user may wish to use a direct layer and by-pass the abstraction in exchange for efficiency.

The Interfaces

To start let's look at the three interfaces which you'll need to implement. They can all be found in Db/Common.

Db/Common/Adapter.php
Db/Common/ResultSet.php
Db/Common/Service.php

Quick Overview

• The adapter is responsible for creating the actual connection to the database. It will pass this connection in whatever format the Service expects.
• The result set is an iterator for pulling query results from the database.
• The service will execute queries and return the results. It also has several methods for reading other information from the database such as last insert id.

Please review these interfaces either by opening them in a text editor or by consulting the JunctionApi.

Writing a MySQL Service

Now let's start implementing a service which calls on PHP's native MySQL functions. This will improve performance by cutting away the overhead associated with the DAO but may reduce portability (or at least will require a new service if you try to switch databases).

Native Adapter

<?php
JunctionFileCabinet::using("Junction_Db_Common_Adapter");
JunctionFileCabinet::using("Junction_Db_Common_Exception");

/**
 * Adapter for creating MySQL connections.
 * 
 * @package junction.db.mysql
 */
class Junction_Db_MySQL_Adapter implements Junction_Db_Common_Adapter {
	
	private $_conn;
	
	/**
	 * Create a new adapter for a MySQL connection.
	 * 
	 * @throws Junction_Db_Common_Exception
	 *
	 * @param String $type will be ignored, MySQL only
	 * @param String $host the databse host e.g. localhost
	 * @param String $database the database name
	 * @param String $user
	 * @param String $pass
	 */
	public function __construct($type,
								$host,
								$database,
								$user,
								$pass) {
		try {
			if (!$this->_conn = mysql_connect($host, $user, $pass)) {
				throw new Exception(mysql_error($this->_conn));
			}
			mysql_select_db($database, $this->_conn);
		} catch (Exception $e) {
			throw new Junction_Db_Common_Exception($e->getMessage());
		}
	}
	
	/**
	 * Fetch the adapter's DAO.
	 *
	 * @return resource
	 */
	public function getDao() {
		return $this->_conn;
	}
}
?>

Native Service

Note that for simplicity this example does not implement the Service interface correctly; the save and select methods will not properly handle the optional $params. It is important that these methods be correctly implemented in a real service.

<?php
JunctionFileCabinet::using("Junction_Db_Common_Exception");
JunctionFileCabinet::using("Junction_Db_Common_Service");
JunctionFileCabinet::using("Junction_Db_MySQL_Adapter");
JunctionFileCabinet::using("Junction_Db_MySQL_ResultSet");

/**
 * Implementation of db-common-service for dealing with a MySQL database.
 * 
 * @package junction.db.mysql
 */
class Junction_Db_MySQL_Service implements Junction_Db_Common_Service {
	
	/**
	 * Creole database handle.
	 *
	 * @var Connection
	 */
	private $_dbh;
	
	/**
	 * Construct a new database abstraction service.
	 *
	 * @param Junction_Db_Common_Adapter $dao
	 */
	public function __construct(Junction_Db_Common_Adapter $dao) {
		$this->_dbh = $dao->getDao();
	}
	
	/**
	 * Perform a query which effects the database
	 *
	 * @throws Junction_Db_Common_Exception
	 * @param String $query
	 * @param array $params = null
	 * @return boolean
	 */
	public function save($query, array $params = null) {
		try {
			$result = mysql_query($query, $this->_dbh);
			return ($result === true);
		} catch (Exception $e) {
			throw new Junction_Db_Common_Exception($e->getMessage());
		}
	}
	
	/**
	 * Perform a query which returns a result set
	 *
	 * @throws Junction_Db_Common_Exception
	 * @param String $query
	 * @param array $params = null
	 * @return Junction_Db_Common_ResultSet
	 */
	public function select($query, array $params = null) {
		try {
			$result = mysql_query($query, $this->_dbh);
			if ($result === false) {
				throw new Exception(mysql_error($this->_dbh));
			}
			return new Junction_Db_MySQL_ResultSet($result);
		} catch (SQLException $e) {
			throw new Junction_Db_Common_Exception($e->getMessage());
		}
	}
	
	/**
	 * Returns the number of rows affected by the previous query
	 *
	 * @return int
	 */
	public function affectedRows() {
		return mysql_affected_rows($this->_dbh);
	}
	
	/**
	 * Returns the key for the previously inserted row
	 *
	 * @return int
	 */
	public function lastInsertId() {
		return mysql_insert_id($this->_dbh);
	}
}
?>

Native Result Set

The result set is a trivial class as it delegates its main responsibility to an iterator, which you will have to implement later.

<?php
JunctionFileCabinet::using("Junction_Db_Common_ResultSet");
JunctionFileCabinet::using("Junction_Db_MySQL_Iterator");

/**
 * Result set for the MySQL result set
 * 
 * @package junction.db.mysql
 */
class Junction_Db_MySQL_ResultSet implements Junction_Db_Common_ResultSet {
	
	/**
	 * MySQL resource
	 *
	 * @var resource
	 */
	private $_resultSet;
	
	/**
	 * Query string which produced this result set.
	 *
	 * @var String
	 */
	private $_query;
	
	public function __construct($results, $query) {
		$this->_resultSet = $results;
		$this->_query = $query;
	}
	
	/**
	 * Retrieve an iterator over the rows retrieved from the database.
	 *
	 * @return Iterator
	 */
	public function getIterator() {
		return new Junction_Db_MySQL_Iterator($results);
	}
	
	/**
	 * Fetch the query which yielded this result set.
	 *
	 * @return String
	 */
	public function getQuery() {
		return $this->_query;
	}
}
?>

Native Iterator

Finally, we need a new iterator in order for the result set to function. Here's a sample implementation which uses the native MySQL functions to step through a query's result set.

<?php
/**
 * Iterator for MySQL result sets.
 *
 * @package junction.db.mysql
 */
class Junction_Db_MySQL_Iterator implements Iterator {
	
	private $_results;
	private $_current;
	private $_count;
	private $_position;
	
	public function __construct($results) {
		$this->_results = $results;
		$this->_count = mysql_num_rows($this->_results);
		$this->_current = mysql_fetch_assoc($this->_results);
		$this->_position = 1;
	}
	
	public function next() {
		$this->_current = mysql_fetch_assoc($this->_results);
		$this->_position++;
	}
	
	public function current() {
		return $this->_current;
	}
	
	public function key() {
		return $this->_position;
	}
	
	public function valid() {
		return ($this->_position < $this->_count + 1);
	}
	
	public function rewind() {
		 mysql_data_seek($this->_results, 0);
	}
}
?>

Once you have written these new classes you are ready to swap out Junction's existing DAO implementation with your own. To do this you need to configure Junction to use your new service, see JunctionConfiguration. After properly configuring Junction it is encouraged that you run the JunctionTests to verify that your service works in tandem with the rest of the application.