A Coding Fool :: Zend Framework and Stored Procedures

Zend Framework and Stored Procedures

Friday, August 21st, 2009

Filed under Programming | Share | Comments (0) | Trackbacks (0)

Tags

I'm been playing with the Zend Framework and, whenever I'm faced with a new data abstraction layer [Zend_Db in this case], I cannot help but rethink the great debate over whether to use (or not use) stored procedures [more].

I must admit that I've been predominantly in the "stored procedure" camp (and I won't even consider embedding a query into my code). But ever since I started to use LINQ to SQL (in C# and .NET), I've begun to rethink this philosophy and have begun to find a place for data abstraction layers (DALs) and object relational mappings (ORMs). And while I find that Zend_Db covers 95% of my database needs, it is still very difficult to perform very complex queries or any sort of complex data manipulation (ones that are best done in the database than in code).

And while it is possible to execute stored procedures using the underlying database adapters within Zend_Db, Zend_Db itself does not contain any classes for use with stored procedures. As a result, I have created one and would like to share it with you.

<?php

abstract class App_Db_Procedures
{
//==============================================================================
// App_Db_Procedures variables
//==============================================================================

protected static $_instances = array();
protected static $_defaultDb;
protected static $_defaultCache = null;

protected $_db = null;
protected $_cache = null;
protected $_metadata = array();
protected $_prefix = '';
protected $_query = null;

//============================================================================
// App_Db_Procedures :: getDefaultAdapter()
//----------------------------------------------------------------------------
// Gets the default database adapter
//============================================================================

public static function getDefaultAdapter()
{
return self::$_defaultDb;
}

//============================================================================
// App_Db_Procedures :: setDefaultAdapter()
//----------------------------------------------------------------------------
// $db: The database adapter to set
//----------------------------------------------------------------------------
// Sets the default database adapter
//============================================================================

public static function setDefaultAdapter($db = null)
{
self::$_defaultDb = self::_setupAdapter($db);
}

//============================================================================
// App_Db_Procedures -> getAdapter()
//----------------------------------------------------------------------------
// Gets the database adapter
//============================================================================

public function getAdapter()
{
if ($this->_db === null && self::$_defaultDb !== null)
{
return self::$_defaultDb;
}

return $this->_db;
}

//============================================================================
// App_Db_Procedures -> setAdapter()
//----------------------------------------------------------------------------
// $db: The database adapter to set
//----------------------------------------------------------------------------
// Sets the database adapter
//============================================================================

public function setAdapter($db)
{
$this->_db = self::_setupAdapter($db);
return $this;
}

//============================================================================
// App_Db_Procedures :: _setupAdapter()
//----------------------------------------------------------------------------
// $db: The database adapter to be set up
//----------------------------------------------------------------------------
// Sets up the database adapter
//============================================================================

protected static function _setupAdapter($db)
{
if ($db === null) return null;
if (is_string($db)) $db = Zend_Registry::get($db);
if (!$db instanceof Zend_Db_Adapter_Abstract)
{
throw new Zend_Db_Exception('Argument must be of type Zend_Db_Adapter_Abstract, ' .
'or a Registry key where a Zend_Db_Adapter_Abstract ' .
'object is stored');
}

return $db;
}

//============================================================================
// App_Db_Procedures :: getDefaultMetadataCache()
//----------------------------------------------------------------------------
// Gets the default metadata cache
//============================================================================

public static function getDefaultMetadataCache()
{
return self::$_defaultCache;
}

//============================================================================
// App_Db_Procedures :: setDefaultMetadataCache()
//----------------------------------------------------------------------------
// $cache: The cache to set
//----------------------------------------------------------------------------
// Sets the default metadata cache
//============================================================================

public static function setDefaultMetadataCache($cache = null)
{
self::$_defaultCache = self::_setupMetadataCache($cache);
}

//============================================================================
// App_Db_Procedures -> getMetadataCache()
//----------------------------------------------------------------------------
// Gets the metadata cache
//============================================================================

public function getMetadataCache()
{
if ($this->_cache === null && self::$_defaultCache !== null)
{
return self::$_defaultCache;
}

return $this->_cache;
}

//============================================================================
// App_Db_Procedures -> setMetadataCache()
//----------------------------------------------------------------------------
// $cache: The cache to set
//----------------------------------------------------------------------------
// Sets the metadata cache
//============================================================================

public function setMetadataCache($cache = null)
{
$this->_cache = self::_setupMetadataCache($cache);
return $this;
}

//============================================================================
// App_Db_Procedures :: _setupMetadataCache()
//----------------------------------------------------------------------------
// $cache: The cache to be set up
//----------------------------------------------------------------------------
// Sets up the metadata cache
//============================================================================

protected static function _setupMetadataCache($cache = null)
{
if ($cache === null) return null;
if (is_string($cache)) $cache = Zend_Registry::get($cache);
if (!$cache instanceof Zend_Cache_Core)
{
throw new Zend_Db_Exception('Argument must be of type Zend_Cache_Core, ' .
'or a Registry key where a Zend_Cache_Core ' .
'object is stored');
}

return $cache;
}

//============================================================================
// App_Db_Procedures -> _setupMetadata()
//----------------------------------------------------------------------------
// Discovers metadata (from the cache or retrieved and added to the cache)
//============================================================================

protected function _setupMetadata()
{
if (count($this->_metadata) > 0) return;

$cache = $this->getMetadataCache();
if ($cache !== null) $id = get_class() . '_' . md5("$this->_prefix");
if ($cache === null || (!$meta = $cache->load($id)))
{
$meta = array_merge
(
$this->getAdapter()->fetchAll('SHOW PROCEDURE STATUS', null, Zend_Db::FETCH_OBJ),
$this->getAdapter()->fetchAll('SHOW FUNCTION STATUS', null, Zend_Db::FETCH_OBJ)
);

if ($cache !== null && !$cache->save($meta, $id))
{
throw new Zend_Db_Exception('Failed saving metadata to metadataCache');
}
}

$this->_metadata = $meta;
}

//============================================================================
// App_Db_Procedures -> _setupMetadataParams()
//----------------------------------------------------------------------------
// Discovers the number of parameters for the specified procedure and adds it
// to the cache
//============================================================================

protected function _setupMetadataParams($proc)
{
if (!isset($proc->ParamCount))
{
$query = $this->getAdapter()->prepare("SHOW CREATE $proc->Type $proc->Name");
$query->execute();
$query->bindColumn(3, $procBody);
$query->fetch(Zend_Db::FETCH_BOUND);
$query->closeCursor();

if ($procBody === null)
{
throw new Zend_Db_Exception('Unable to cache stored procedure parameters');
}

preg_match('/(?:' . $proc->Type . '.+$(.*)$\sBEGIN)/msU', $procBody, $matches);
if (count($matches) != 2)
{
throw new Zend_Db_Exception('Unable to cache stored procedure parameters');
}

$params = trim($matches[1]);
if (empty($params)) $proc->ParamCount = 0;
else
{
$params = explode(',', $params);
$proc->ParamCount = count($params);
}

$cache = $this->getMetadataCache();
if ($cache !== null)
{
$id = md5("procedures.$this->_prefix");
if (!$cache->save($this->_metadata, $id))
{
throw new Zend_Db_Exception('Failed saving metadata to metadataCache');
}
}
}
}

//============================================================================
// App_Db_Procedures -> _getProcedure()
//----------------------------------------------------------------------------
// $name: The name of the procedure to get
//----------------------------------------------------------------------------
// Search the metadata for a procedure with the specified name
//============================================================================

protected function _getProcedure($name)
{
$this->_setupMetadata();
foreach ($this->_metadata as $proc)
{
if ($proc->Name == $name)
{
$this->_setupMetadataParams($proc);
return $proc;
}
}
return null;
}

//============================================================================
// App_Db_Procedures -> __call()
//----------------------------------------------------------------------------
// $method: The name of the method to call
// $params: An array of parameter used to invoke the method
//----------------------------------------------------------------------------
// Calls the specified method
//============================================================================

public function __call($method, $params)
{
if (method_exists($this, $method))
{
return call_user_func_array(array($this, $method), $params);
}

$name = $this->_prefix . strtolower(preg_replace('/([A-Z][a-z0-9]+)/U', '_$1', $method));
$proc = $this->_getProcedure($name);
if ($proc === null)
{
throw new Zend_Db_Exception("The requested procedure '$name' is not found");
}

$count = count($params);
$binds = ($proc->ParamCount == 0 ? '':
implode(', ', array_fill(0, $proc->ParamCount, '?')));
if ($count < $proc->ParamCount)
{
$params = array_merge($params, array_fill(0, $proc->ParamCount - $count, null));
}

if ($proc->Type == 'PROCEDURE')
{
$this->_query = $this->getAdapter()->prepare("call $name($binds)");
$this->_query->execute($params);
return $this->_query;
}
else
{
$this->_query = $this->getAdapter()->prepare("SELECT $name($binds)");
$this->_query->execute($params);
return $this->_query->fetchColumn();
}
}

//============================================================================
// App_Db_Procedures :: __callStatic()
//----------------------------------------------------------------------------
// $method: The name of the method to call
// $params: An array of parameter used to invoke the method
//----------------------------------------------------------------------------
// Calls the specified method on the current instance of the object
//============================================================================

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

//============================================================================
// App_Db_Procedures :: getInstance()
//----------------------------------------------------------------------------
// Gets the current instance of the object
//============================================================================

public static function getInstance()
{
$child = get_called_class();
if (!isset(self::$_instances[$child])) self::$_instances[$child] = new $child();
return self::$_instances[$child];
}
}

?>

This class is designed to automatically discover and cache the available stored procedure definitions and allows these procedures to be executed without the need to explicitly define a function for each (but you will still need to set up the default database adapter and cache in your bootstrap). Through the use of PHP's __call magic function, the class will attempt to execute the stored procedure matching the called function (in addition, it ensures that the count of parameters matches that of the stored procedure). All you need to do it create a class that extends this class in order to begin using your stored procedures.

Child classes may define a $_prefix variable to indicate any prefix you may use in your stored procedure names. In addition, the class will convert the called camel-cased method name to a lowercased underscored-delimited stored procedure name (this is my preferred naming convention for database entities). Therefore, functions will be mapped to stored procedure like so:

Public_Model_Procedures_Auth::getUserCredentials(...) => auth_get_user_credentials(...)
Public_Model_Procedures_Auth::authenticateUser(...) => auth_authenticate_user(...)

It should be noted, that this file is designed to use PHP 5.3 as it utilizes both the __callStatic() and get_called_class() functions. This allows stored procedures to be called via static methods and allows singleton instances to be created from within the abstract parent class. In older versions of PHP, you will need to make the following changes to your child classes:

class Public_Model_Procedures_Auth extends App_Db_Procedures
{
//==============================================================================
// Public_Model_Procedures_Auth variables
//==============================================================================

protected static $_instance = null;
protected $_prefix = 'auth_';

//============================================================================
// Public_Model_Procedures_Auth :: getInstance()
//----------------------------------------------------------------------------
// Gets the current instance of the object
//============================================================================

public static function getInstance()
{
if (self::$_instance == null) self::$_instance = new self();
return self::$_instance;
}
}

With this modification, stored procedures must be executed like so:

Public_Model_Procedures_Auth::getInstance()->getUserCredentials(...)
Public_Model_Procedures_Auth::getInstance()->authenticateUser(...)

While I may do most of my database work with DALs (and the debate over stored procedures still rages on), I still find the need to stored procedures now and then and this class will give you a very simple interface to your procedures.

I hope you find it helpful.