Zend Framework: Zend_Db_Gateway Component Proposal

Proposed Component Name	Zend_Db_Gateway
Developer Notes	http://framework.zend.com/wiki/display/ZFDEV/Zend_Db_Gateway
Proposers	My E-mail Address
Revision	1.1 - 22 January 2008: Proposal created. (wiki revision: 10)

Current Zend_Db_Table implementation provides a limited interface for database data retrieval. In most real world scenarios, queries often require several tables to be joined, as related data is split for normalization's sake, and also for specific columns to be retrieved (creating 'views'). This component provides an interface to creating complicated queries - reducing the overhead of writing them by hand, relies on Zend_Db_Table reference maps to define table relationships and abstracts explicit table names.

This component will provide an interface for query composition.
This component will allow the creation of 'views' (partial column retrieval).
This component will abstract table names so they shall not be declared explicitly.
This component will use Zend_Db_Table reference maps for defining table relationships.
This component will not be coupled to Zend_Db_Table, despite borrowing design features from it.
This component will use Zend_Db_Select as a lower-level query composition tool.

4. Dependencies on Other Framework Components

Zend_Db_Select
Zend_Db_Adapter_Abstract

5. Theory of Operation

The class provides a base interface for composing cross table queries (joins) that is intended to be abstracted further into meaningful method names by extending classes. The extending methods can than be used be reused to create different query composition combinations.

When instantiated, this component is provided with a instance of Zend_Db_Adapter, a table name for which this instance will act as gateway, and an optional reference map.

Class API methods share a single Zend_Db_Select object (class member), which can be reset (full/partial) on demand.

Query process begins with an initialization method or alternatively a Zend_Db_Select instance may be provided with pre-configured query options.

Table names are fetched from their corresponding table classes, and then cached for the lifetime of the object.

6. Milestones / Tasks

Milestone 1: [DONE] Submit proposal
Milestone 2: [DONE] Provide documented working prototype and use cases
Milestone 3: Re-design based on provided community feedback
Milestone 4: Provide unit tests.
Milestone 5: Proposal accepted into incubator.

7. Class Index

Zend_Db_Gateway

8. Use Cases

UC 01

Files is a file management class for a web application - it allows users to upload files, organize files by folders, share files to other users and to have a basic versioning mechanism.
Apart from the file itself, all relevant data is stored in the database in four distinct tables:

user_files: Contains file details, including hash, title, description and an ID of the uploading user (owner).

user_folders: Contains file folders details. File folders have names, a unique numeric ID and a parent ID for creating a tree-like hierarchy.

users_to_files: Provides many-to-many relationship between files and users.

user_file_versions: Is similar to user_files and contains different versions of specific files from user_files.

Building views from data retrieved from all four tables is done in the following manner:

First, extending the Gateway class and creating meaningful and reusable query composition methods:

/**
 * A table gateway to file module classes
 * 
 * Provides chainable interface methods for creating file module database views
 * 
 * @package Octabox
 * @subpackage Files
 * @copyright Octabox Ltd. 2007
 * @author Eran Galperin
 */
class FilesGateway extends Octabox_Db_Gateway 
{
	/**
	 * Extends parent initialization method
	 *
	 * @param array $cols
	 * @return self
	 */
	public function getAll($cols = array('*'))
	{
		parent::getAll($cols);
		return $this -> by(array('o' => 'Owner'),array('owner' => 'name'));
	}
	/**
	 * Order by file version, descending
	 *
	 * @return self
	 */
	public function orderByVersion()
	{
		$this -> order("created DESC",'UserFileVersions');
		return $this;
	}
	/**
	 * Add versions
	 *
	 * @return self
	 */
	public function withVersions()
	{
		$this -> by('Versions',array('ver_hash' => 'hash','version','ver_time' => 'created','ver_user' => 'user_id'),'joinLeft');	
		$this -> group('hash',self::LASTJOINED);
		return $this -> with('Owner',array('username' => 'name'),'joinLeft');
	}
	/**
	 * Add number of users a file is shared to
	 *
	 * @return self
	 */
	public function withShares()
	{
		$this -> by('Shared',array('shared' => 'COUNT(' . $this -> getTableName('UsersToFiles') . '.user_id)'),'joinLeft');
		$this -> group('hash');
		return $this;
	}
	/**
	 * Get shared files (to current user)
	 * 
	 * @param array $cols
	 * @return self
	 */
	public function byUserShares($cols = array())
	{
		$this -> by('Shared',$cols);
		$this -> where("user_id='" . Auth::getId() . "'",self::LASTJOINED);
		return $this;
	}
	/**
	 * Get files by folder
	 *
	 * @param integer $id
	 * @return self
	 */
	public function byFolder($id = null)
	{
		if($this -> isValidId($id)){
			return $this -> where("folder_id='" . $id . "'");
		} else {
			return $this -> where('folder_id IS NULL');
		}
	}
}

Views can then be easily created from a Zend_Db_Table extending class: (easy compared to manual query composition)

class UserFiles extends Zend_Db_Table 
{
	protected $_name = 'user_files';
        protected $_primary = 'hash';

        /**
        /* Table gateway class
         */
	protected $_gatewayClass = 'FilesGateway';

        /**
        /* Reference map described table relationships and is used by the table gateway
         */
	protected $_referenceMap = array(
		'Owner' => array(
                        'columns'           => 'user_id',
                        'refTableClass'     => 'Users',
                        'refColumns'        => 'id'
         	),
        	'Shared' => array(
       	        	'columns' => 'hash',
       		        'refTableClass' => 'UsersToFiles',
        		'refColumns' => 'hash'
        	),
        	'Versions' => array(
        		'columns' => 'hash',
        		'refTableClass' => 'UserFileVersions',
        		'refColumns' => 'parent_hash'
        	)
	);

        /**
        /* Fetch files by folder, with versions and shared users, limit by user and order by version
        /* If provided folder ID is null, root folder will be fetched
        /*
        /* @param integer $id
        /* @return array
         */
        public function getByFolder($id = null)
	{
		$this -> gateway() -> getAll() -> byFolder($id) -> withVersions() -> withShares() -> byUser() -> orderByVersion();
		return $this -> gateway -> query();
	}
	/**
        /* Get files shared from other users, with files versions, order by version.
        /* Optionally limited by specific user ID
        /* 
        /* @param integer $id
        /* @return array
         */
	public function getShared($id = null)
	{
		$gateway = $this -> gateway();
		$gateway -> getAll() -> byUserShares() -> withVersions() -> orderByVersion();
		if($this -> isValidId($id)){
			$gateway -> byUser($id);
		}
		return $gateway -> query();
	}
        /**
        /* Get list of users sharing files (To the current user)
        /*
        /* @return array
         */
	public function getSharedFolders()
	{
		$gateway = $this -> gateway();
		$gateway -> getAll(array('user_id')) -> byUserShares();
		$gateway -> group('user_id');
		return $gateway -> query();
	}
        /**
        /* Search files by title OR description.
        /* Search is performed by user files or by shared files (default: user files)
        /*
        /* @param string $by
        /* @param string $what
        /* @param boolean $shared
        /* @return array
         */
	public function search($by,$what,$shared = false)
	{
		$search = array('title','description');
		$by = strtolower($by);
		if(in_array($by,$search) && is_string($what) && !empty($what)){
			$gateway = $this -> gateway();
			$gateway -> getAll() -> withVersions() -> orderByVersion();
			if($shared == true){
				$gateway -> byUserShares();
			} else {
				$gateway -> withShares() -> byUser();
			}
			$gateway -> where($by . " LIKE '" . Filter::htmle($what) . "%'");
			return $gateway -> query();
		}
	}
}

9. Class Skeletons

/**
 * Class for SQL data gateway.
 *
 * Gateway provides data retrieval query composition methods. 
 * It provides a Zend_Db_Table based API to Zend_Db_Select to allow the retrieval of complicated data sets
 * 
 * @category   Zend
 * @package    Zend_Db
 * @subpackage Gateway
 * @copyright  Copyright (c) 2005-2007 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 * @author Eran Galperin (www.octabox.com)
 */
class Zend_Db_Gateway 
{
	const ADAPTER = 'db';
	const TABLE = 'table';
	const MAP = 'map';
	
	const THIS = false;
	const LASTJOINED = true;
	
	/**
	 * Internal query composition tool
	 * A Zend_Db_Select instance
	 *
	 * @var Zend_Db_Select
	 */
	protected $_select;
	/**
	 * Gateway table name
	 * 
	 * @var string 
	 */
	protected $_name;
	/**
     * Zend_Db_Adapter_Abstract object.
     *
     * @var Zend_Db_Adapter_Abstract
     */
	protected $_db;
	/**
	 * Last joined table name (Cache)
	 *
	 * @var string
	 */
	protected $_lastJoin = null;
	/**
	 * Join types whitelist
	 *
	 * @var array
	 */
	protected $_joinTypes = array(
		'joinLeft','joinRight','joinFull','joinCross','joinNatural','join'
	);
	/**
	 * Tables name cache
	 *
	 * @var array
	 */
	protected $_nameCache = array();
	/**
     * Constructor.
     *
     * Supported params for $config are:
     * - db              = user-supplied instance of database adapter
     * - table           = Gateway table name.
     * - map		     = array structure to declare relationship
     *                     to gateway table.
     * @param  mixed $config Array of user-specified config options or just the database adapter
     * @return void
     */
	public function __construct($config = array())
	{
		if (!is_array($config)) {
            $config = array(self::ADAPTER => $config);
        }
		foreach ($config as $key => $value) {
            switch ($key) {
                case self::ADAPTER:
                    $this-> _setAdapter($value);
                    break;
                case self::TABLE:
                    $this-> _setTable($value);
                    break;
                case self::MAP:
                	$this -> _map = $value;
                default:
                	break;
            }
		 }
	}
	/**
	 * Set database adapter
	 *
	 * @param Zend_Db_Adapter_Abstract $db
	 */
	protected function _setAdapter(Zend_Db_Adapter_Abstract $db)
	{
		$this -> _db = $db;
	}
	/**
	 * Get database adapter
	 *
	 * @return Zend_Db_Adapter_Abstract
	 */
	protected function getAdapter()
	{
		if(!isset($this -> _db)){
			$this -> _db = Zend_Db_Table::getDefaultAdapter();
		}
		return $this -> _db;
	}
	/**
	 * Set base Gateway table name
	 *
	 * @param string / Zend_Db_Table $table
	 */
	protected function _setTable($table)
	{
		if($table instanceof Zend_Db_Table){
			$name = $table -> getTableName();
		} else if(is_string($table)){
			$name = $table;
		} else {
			throw new Zend_Db_Exception('Provided table parameter should be string OR instance of Zend_Db_Table');
		}
		$this -> _name = $name;
	}
	/**
	 * Get gateway query compositor
	 *
	 * - A partially initialized Zend_Db_Select object may be injected
	 * - Passing a boolean true will force the creation of a new compositor
	 * 
	 * @param mixed Zend_Db_Select $select / boolean
	 * @return Zend_Db_Select
	 */
	public function select($select = null)
	{
		if($select instanceof Zend_Db_Select){
			$this -> _select = $select;
		} else if($select === true || is_null($this -> _select)){
			$this -> _select = new Zend_Db_Select($this -> getAdapter());
		}
		return $this -> _select;
	}
	/**
	 * Internal Zend_Db_Select reset method
	 *
	 * @param string $part
	 */
	public function reset($part = null)
	{
		if($this -> _select instanceof Zend_Db_Select){
			$this -> _select -> reset($part);
		}
	}
	/**
	 * Query database with prepared statement
	 *
	 * @return array
	 */
	public function query()
	{
		return $this -> getAdapter() -> fetchAll($this -> select());
	}
	/**
	 * Query database with prepared statement, retrieve one row
	 *
	 * @return array
	 */
	public function queryOne()
	{
		return $this -> getAdapter() -> fetchRow($this -> select());
	}
	/**
	 * Get all rows from gateway table
	 * 
	 * - Optional columns array may be provided
	 * - Common start to most query compositions before additional clauses are added
	 * - Returns self for fluid interface
	 * @param array $cols
	 * @return self
	 */
	public function getAll($cols = array('*'))
	{
		$this -> select(true) -> from($this -> _name,$cols);
		return $this;
	}
	/**
	 * Add where clause
	 *
	 * - Parses table name via self::parseTableName() method
	 * - Returns self for fluid interface
	 * 
	 * @param string $clause
	 * @return self
	 */
	public function where($clause,$tableName = null)
	{
		$table = $this -> parseTableName($tableName);
		$this -> select() -> where($table . '.' . $clause);
		return $this;
	}
	/**
	 * Add group by clause
	 *
	 * - Parses table name via self::parseTableName() method
	 * - Returns self for fluid interface
	 * 
	 * @param string $clause
	 * @return self
	 */
	public function group($clause,$tableName = null)
	{
		$table = $this -> parseTableName($tableName);
		$this -> select() -> group($table . '.' . $clause);
		return $this;
	}
	/**
	 * Add order by clause
	 *
	 * - Parses table name via self::parseTableName() method
	 * - Returns self for fluid interface
	 * 
	 * @param string $clause
	 * @return self
	 */
	public function order($clause,$tableName = null)
	{
		$table = $this -> parseTableName($tableName);
		$this -> select() -> order($table . '.' . $clause);
		return $this;
	}
	/**
	 * Parse table name
	 * 
	 * 	 - self::THIS or null to return gateway table name
	 * 	 - self::LASTJOINED to return last joined table name
	 *   - Existing table class name to retrieve table name via class
	 *
	 * @param mixed $tableName
	 * @return string
	 */
	protected function parseTableName($tableName)
	{
		if(is_null($tableName) || $tableName === self::THIS){
			$table = $this -> _name;
		} else if($tableName === self::LASTJOINED){
			$table = $this -> getLastJoined();
		} else if(is_string($tableName)){
			$table = $this -> getTableName($tableName);
		}
		return $table;
	}
	
	/**
	 * Perform join by reference rule
	 * 
	 * - Operates on gateway provided reference rules (if exists). Rule parameter might be an array with correlation name as the key
	 * - Optional columns array (defaults to '*')
	 * - Optional join type specification
	 * - Returns self on success, boolean false on failure
	 * 
	 * @param string / array $rule
	 * @param array $cols
	 * @param string $type
	 * @return self / boolean
	 */
	public function by($rule,$cols = array('*'),$type = 'join')
	{
		$ref = $this -> parseRule($rule,self::THIS);
		return $this -> _joinByRule($ref,$cols,$type);
	}
	/**
	 * Perform join with reference rule
	 * 
	 * - Similar to join by reference, diverges on join condition - attempts to join on previously joined table
	 * - Returns self on success, boolean false on failure
	 * 
	 * @param string / array $rule
	 * @param array $cols
	 * @param string $type
	 * @return self / boolean
	 */
	public function with($rule,$cols = array('*'),$type = 'join')
	{
		$ref = $this -> parseRule($rule,self::LASTJOINED);
		return $this -> _joinByRule($ref,$cols,$type);
	}
	/**
	 * Join by reference rule
	 *
	 * - Reference array produced by self::parseRule() method
	 * - Optional columns array (defaults to '*')
	 * - Optional join type specification
	 * - Returns self on success, boolean false on failure
	 * 
	 * @param array $reference
	 * @param array $cols
	 * @param string $type
	 * @return self / boolean
	 */
	protected function _joinByRule($reference,$cols = array('*'),$type = 'join')
	{
		if(!in_array($type,$this -> _joinTypes)){
			throw new Zend_Db_Exception('Join type ' . $type . ' is not valid');
		}
		if($reference !== false){
			$this -> select() -> $type($reference['table'],$reference['cond'],$cols);
			$this -> _lastJoin = key($reference['table']);
			return $this;
		}
		return false;
	}
	/**
	 * Is valid ID
	 * 
	 * Utility method for determining int ID validity
	 *
	 * @param int $id
	 * @return boolean
	 */
	protected function isValidId($id)
	{
		if(is_int($id) && $id > 0){
			return true;
		}
		return false;
	}
	/**
	 * Parse reference rule
	 *
	 * Returned reference array for method self::_joinByRule():
     * - table           = joined table name
     * - cond		     = ON join condition
     * 
     * Optional boolean $byPreviousParsing determines if join condition applies to gateway table or last joined table
     * 
	 * @param string / array $rule
	 * @param boolean $byPreviousParsing
	 * @return array / boolean
	 */
	protected function parseRule($rule,$byPreviousParsing = false)
	{
		if(is_array($rule)){
			$correlation = key($rule);
			$rule = current($rule);
		} else {
			$correlation = null;
		}
		if(isset($this -> _map[$rule])){
			$rule = $this -> _map[$rule];
			$tableName = $this -> getTableName($rule['refTableClass']);
			if(is_null($correlation)){
				$correlation = $tableName;
			}
			if($byPreviousParsing === self::LASTJOINED && !is_null($this -> _lastJoin)){
				$matchTable = $this -> _lastJoin;
			} else {
				$matchTable = $this -> _name;
			}
			$ref = array(
				'table' => array($correlation => $tableName),
				'cond' => $correlation . '.' . $rule['refColumns'] . '=' . $matchTable . '.' . $rule['columns']
			);
			return $ref;
		}
		return false;
	}
	/**
	 * Get table name from table class name
	 *
	 * Results are cached to prevent multiple instances of same class
	 * 
	 * @param string $tableClass
	 * @return string
	 */
	public function getTableName($tableClass)
	{
		if(!isset($this -> _nameCache[$tableClass])){
			if(!class_exists($tableClass)){
				throw new Zend_Db__Exception('Table class ' . $tableClass . ' does not exist');
			}
			$table = new $tableClass();
			$this -> _nameCache[$tableClass] = $table -> getTableName();
		}
		return $this -> _nameCache[$tableClass];
	}
	/**
	 * Get last joined table name
	 *
	 * @return string
	 */
	protected function getLastJoined()
	{
		return $this -> _lastJoin;
	}
}

Page Operations

Browse Space

Zend_Db_Gateway

Labels

Zend Framework: Zend_Db_Gateway Component Proposal

Table of Contents

1. Overview

2. References

3. Component Requirements, Constraints, and Acceptance Criteria