Sunday, December 7, 2008

Audit Data Modifications with Zend_DB

One of the requirements for my electronic medical record prototype is ability to track data modifications as users interact with the system. I wanted to see how difficult this would be to accomplish using the Zend Framework. This exercise ranged from being extremely trivial with Ruby on Rails to quite convoluted using the Java Persistence API (JPA). A quick web search landed me at zed23 where I found an example by Ryan Brooks that was pretty close to what I was looking for.

Audit Row Class
The first class is OpenEprs_Audit_Row. This class extends Zend_Db_Table_Row_Abstract and overrides three protected methods: _postInsert(), _postUpdate(), and _postDelete(). These methods are key to capturing the events generated by our CRUD operations.
class OpenEprs_Audit_Row extends Zend_Db_Table_Row_Abstract {
    /**
     * Allows post-insert logic to be applied to row
     *
     * @return void
     */
    protected function _postInsert() {
        // Check if any rows have been created and if so, record the changes
        if (!empty($this->_data)) {
            $this->_logModifications(OpenEprs_Audit_Factory::ACTION_CREATE);
        }
    }

    /**
     * Allows post-update logic to be applied to row
     *
     * @return void
     */
    protected function _postUpdate() {
        // Check if any rows have been modified and if so, record the changes
        if (!empty($this->_cleanData)) {
            $this->_logModifications(OpenEprs_Audit_Factory::ACTION_UPDATE);
        }
    }

    /**
     * Allows post-delete logic to be applied to row
     *
     * @return void
     */
    protected function _postDelete() {
        // Check if any rows have been deleted and if so, record the changes
        if (!empty($this->_cleanData)) {
            // Get the primary key field, this will NOT be set as modified
            $info = $this->getTable()->info();
            $pkField = $info['primary']['1'];

            // Set all fields as modified so they are captured
            foreach ($this->_cleanData as $key => $value) {
                if ($key != $pkField) {
                    $this->_modifiedFields[$key] = true;
                    $this->_data[$key] = null;
                }
            }

            // Record the deletions
            $this->_logModifications(OpenEprs_Audit_Factory::ACTION_DELETE);
        }
    }

    /**
     * Writes an audit record to the database for the given $actionMethod
     *
     * @see OpenEprs_Audit_Factory
     * @param string $actionMethod OpenEprs_Audit_Factory action method
     * @return void
     */
    protected function _logModifications($actionMethod) {
        // Get metadata about the table and its columns
        $info = $this->getTable()->info();

        // Log the modifications
        OpenEprs_Audit_Factory::getInstance()->logModify(
            $actionMethod,
            $this->_modifiedFields,
            $this->_cleanData,
            $this->_data,
            $info
        );
    }
}
The above works because we use _cleanData, _data and _modifiedFields to determine what has happened to our data. While this great for inserts and updates, we should note that a bit of hacking was required to get a deleted record recognized. In the case of a delete, _cleanData and _data are the same with no information in _modifiedFields.

Audit Factory Class

The next class is OpenEprs_Audit_Factory. This class extends Zend_Db_Table_Abstract and implements the logic required for creating and storing the actual audit records. Although most of this class is shamelessly lifted from Ryan's code, I added the following:
  1. Support for auditing inserts and deletes in addition to updates
  2. Public constants defining insert, update and delete action methods
  3. Ability to track which user initiated an action
class OpenEprs_Audit_Factory extends Zend_Db_Table_Abstract {
    /** Audit factory create action */
    const ACTION_CREATE = 'CREATE';

    /** Audit factory update action */
    const ACTION_UPDATE = 'UPDATE';

    /** Audit factory delete action */
    const ACTION_DELETE = 'DELETE';

    /** @var array Table name */
    protected $_name = 'eprs_system_audit';

    /** @var mixed Primary key column or columns */
    protected $_primary = 'audit_id';

    // Transaction ID for audit records
    private static $_transactionId = null;

    /**
     * Returns an instance of this class
     *
     * @return OpenEprs_Audit_Factory instance
     */
    public static function getInstance() {
        // Initialize instance variable
        static $instance = null;

        // If the instance is null, create it
        if(is_null($instance)) {
            $instance = new self;
        }

        // Create an audit transaction ID
        if(is_null(self::$_transactionId)) {
            self::$_transactionId = md5(microtime());
        }

        return $instance;
    }

    /**
     * Logs modified records to the application's system audit table.
     *
     * @param string $actionMethod   OpenEprs_Audit_Factory action method
     * @param array  $modifiedFields columns where data has been updated
     * @param array  $oldRow         row before modification
     * @param array  $newRow         row after modification
     * @param mixed  $tableInfo      Zend_Db_Table information
     * @return boolean true on success, false otherwise
     */
    public function logModify($actionMethod, $modifiedFields, $oldRow, $newRow, $tableInfo) {
        // If nothing is modified get out...
        if(empty($modifiedFields))
            return false;

        // Get the user's ID
        $auth = Zend_Auth::getInstance();
        $userId = $auth->getIdentity()->user_id;

        // Start the audit process
        try {
            // Get the current datetime
            $actionDate = new Zend_Db_Expr('NOW()');

            // Process each modified filed
            foreach($modifiedFields as $key => $value) {
                // If the new value is actually different from the old value
                if($oldRow[$key] !== $newRow[$key]) {
                    // Get the primary key field
                    $objPrimaryKey = $tableInfo['primary']['1'];
                    
                    // Select the table ID based on the action method
                    $tableId = '';
                    switch ($actionMethod){
                        case self::ACTION_CREATE:
                            $tableId = $newRow[$objPrimaryKey];
                            break;
                        case self::ACTION_UPDATE:
                            $tableId = $oldRow[$objPrimaryKey];
                            break;
                        case self::ACTION_DELETE:
                            $tableId = $oldRow[$objPrimaryKey];
                            break;
                    }

                    // Create a new audit row
                    $record = $this->createRow();

                    // Set the audit fields
                    $data['user_id'] = $userId;
                    $data['transaction_id'] = self::$_transactionId;
                    $data['table_name'] = $tableInfo['name'];
                    $data['table_id'] = $tableId;
                    $data['action_method'] = $actionMethod;
                    $data['action_date'] = $actionDate;
                    $data['field_name'] = $key;
                    
                    if($tableInfo['metadata'][$key]['DATA_TYPE'] == 'text') {
                        // Field is of type 'text' -> store in text fields
                        $data['before_value_text'] = $oldRow[$key];
                        $data['after_value_text'] = $newRow[$key];
                    } else {
                        // Field is NOT of type 'text' -> store in varchar fields
                        $data['before_value_string'] = $oldRow[$key];
                        $data['after_value_string'] = $newRow[$key];
                    }

                    // Save the audit row
                    $record->setFromArray($data)->save();
                }
            }

            return true;
        } catch (Exception $e) {
            // Build the error message
            $errMsg = "\nMessage: " . $e->getMessage() .
                      "\nStack Trace:\n" . $e->getTraceAsString();

            // Log the error
            OpenEprs_Util_Log::error($errMsg, Zend_Log::ERR);

            return false;
        }
    }
}

Database Audit Table

The OpenEprs_Audit_Factory class expects a table with the following structure to be present in the application's database (MySQL is shown):
CREATE TABLE `eprs_system_audit` (
      `audit_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Audit ID'
    , `user_id` int(10) UNSIGNED  NOT NULL COMMENT 'User ID'
    , `transaction_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Transaction ID'
    , `table_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Table Name'
    , `table_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Table ID (Primary Key Value)'
    , `action_method` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Action Method: CREATE, UPDATE, DELETE'
    , `action_date` datetime DEFAULT '0000-00-00 00:00:00' COMMENT 'Action Date/Time'
    , `field_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Field Name'
    , `before_value_string` varchar(255) COLLATE utf8_unicode_ci NULL COMMENT 'Before Action Field Value'
    , `after_value_string` varchar(255) COLLATE utf8_unicode_ci NULL COMMENT 'After Action Field Value'
    , `before_value_text` text NULL COMMENT 'Before Action Field Value (Text Data)'
    , `after_value_text` text  NULL COMMENT 'After Action Field Value (Text Data)'
    , PRIMARY KEY (`audit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='System Audit';

Using the Audit Functionality

Now that we have the core audit classes and audit table in place, we can create a model that uses our new auditing functionality by simply setting the protected variable $_rowClass to OpenEprs_Audit_Row.
class User extends Zend_Db_Table_Abstract {
    /** @var array Table name */
    protected $_name = 'eprs_user';
    
    /** @var mixed Primary key column or columns */
    protected $_primary = 'user_id';

    /** @var string Classname for row */
    protected $_rowClass = 'OpenEprs_Audit_Row';
}

Unit Testing

It is very easy to unit test this functionality with PHPUnit. Start by creating a base test class for your models by extending PHPUnit_Framework_TestCase. This class implements common functionality such as user authentication and audit record verification.
abstract class Base_ModelTestCase extends PHPUnit_Framework_TestCase {
    /**
     * Authenticate user using $username and $password
     *
     * @param string $username name of the user to authenticate
     * @param string $password password of the user to authenticate
     * @return void
     */
    protected function _setAuth($username, $password) {
        // Get the auth adapter and authenticate the user
        $authAdapter = $this->_getAuthAdapter($username, $password);
        $auth = Zend_Auth::getInstance();
        $result = $auth->authenticate($authAdapter);

        // Verify the user was authenticated
        $this->assertNotNull($result);

        // Store the authorization data
        $authData = $authAdapter->getResultRowObject(null, 'password');
        $auth->getStorage()->write($authData);

        // Verify the data was stored successfully
        $userId = $auth->getIdentity()->user_name;
        $this->assertEquals($username, $userId);
    }

    /**
     * Set up auth adapter for interaction with the database
     *
     * @param string $username name of the user to authenticate
     * @param string $password password of the user to authenticate
     * @return Zend_Auth_Adapter_DbTable database adapter object
     */

    protected function _getAuthAdapter($username, $password) {
        // Setup auth adapter with database
        $dbAdapter = Zend_Registry::get('db');
        $authAdapter = new Zend_Auth_Adapter_DbTable($dbAdapter);
        $authAdapter->setTableName('eprs_user_auth')
                    ->setIdentityColumn('user_name')
                    ->setCredentialColumn('password');;

        // Get "salt" for better security
        $config = Zend_Registry::get('config');
        $salt = $config->auth->salt;
        $password = sha1($salt.$password);

        // Set credentials
        $authAdapter->setIdentity($username);
        $authAdapter->setCredential($password);

        return $authAdapter;
    }

    /**
     * Checks for the existence of audit records
     *
     * @param string $table  table name
     * @param int    $id     table ID
     * @param string $action audit action
     * @param int    $rowCnt expected audit row count
     * @return void
     */
    protected function _checkAuditRecords($table, $id, $action, $rowCnt) {
        // Find the audit records
        $auditTable = new OpenEprs_Audit_Factory();
        $select = $auditTable->select()
            ->where('table_name = ?', $table)
            ->where('table_id = ?', $id)
            ->where('action_method = ?', $action);
        $rows = $auditTable->fetchAll($select);

        // Verify the audit records exist
        $this->assertNotNull($rows);
        $this->assertSame($rowCnt, $rows->count());
    }
}
My unit tests are configured using a separate class. This class takes care of reading my application's configuration file, setting up the default database adapter, etc. The pieces important to our discussion are:
...

    /**
     * Sets up the default test database adapter
     *
     * @return void
     */
    public static function setupDatabaseAdapter() {
        // Load configuration
        $root = realpath(dirname(dirname(__FILE__)));
        $config = new Zend_Config_Xml($root .'/application/config/config.xml', self::$_env);
        Zend_Registry::set('config', $config);

        // Set up database connection
        $db = Zend_Db::factory($config->database);
        Zend_Db_Table_Abstract::setDefaultAdapter($db);
        Zend_Registry::set('db', $db);
    }

    /**
     * Sets up the test database to a pristine state
     *
     * @return void
     */
    public static function setupDatabase() {
        // Create the base of the mySQL commandline
        $cmd = self::$_mySqlClient .
               " --user=" . self::$_mySqlUser .
               " --password=" . self::$_mySqlPassword .
               " --database=" . self::$_mySqlDatabase;

        // Create the database schema
        system($cmd . " < db/mysql_schema.sql");

        // Populate the schema with default data
        system($cmd . " < db/mysql_data.sql");
    }

...
Now you can create your unit test.
class Model_UserTest extends Base_ModelTestCase {
    /**
     * Setup the test
     *
     * @return void
     */
    public function setUp() {
        TestConfiguration::setupDatabaseAdapter();
        TestConfiguration::setupDatabase();
    }

    /**
     * Test create user
     *
     * @return void
     */
    public function testCreateUser() {
        // Set the authentication
        $this->_setAuth('admin', 'changeit');
        
        // Create the test record
        $id = $this->_createTestRecord($this->_getTestData());

        // Verify auditing
        $this->_checkAuditRecords('eprs_user', $id,
            OpenEprs_Audit_Factory::ACTION_CREATE, 6);
    }

    /**
     * Test update user
     *
     * @return void
     */
    public function testUpdateUser() {
        // Set the authentication
        $this->_setAuth('admin', 'changeit');

        // Create the test record
        $id = $this->_createTestRecord($this->_getTestData());

        // Set the update data
        $updates['password'] = 'test2';
        $updates['date_updated'] = new Zend_Db_Expr('NOW()');
        $updates['active'] = 1;
        $updates['role_id'] = 2;

        // Update the test record
        $this->_updateTestRecord($id, $updates);

        // Verify auditing
        $this->_checkAuditRecords('eprs_user', $id,
            OpenEprs_Audit_Factory::ACTION_UPDATE, 4);
    }

    /**
     * Test delete user
     *
     * @return void
     */
    public function testDeleteUser() {
        // Set the authentication
        $this->_setAuth('admin', 'changeit');

        // Create the test record
        $id = $this->_createTestRecord($this->_getTestData());

        // Delete the test record
        $this->_deleteTestRecord($id);

        // Verify auditing
        $this->_checkAuditRecords('eprs_user', $id,
            OpenEprs_Audit_Factory::ACTION_DELETE, 6);
    }

    /**
     * Creates a test record
     *
     * @param mixed|array $data test record data
     * @return int ID of the record that was created
     */
    protected function _createTestRecord($data) {
        // Create the test record
        $userTable = new User();
        $row = $userTable->createRow($data);
        $id = $row->save();

        // Find the record that was created
        $select = $userTable->select()->where('user_id = ?', $id);
        $row = $userTable->fetchRow($select);

        // Verify a record was created
        $this->assertNotNull($row);

        return $id;
    }

    /**
     * Updates a test record
     *
     * @param int         $id      ID of the record to update
     * @param mixed|array $updates test record update data
     * @return void
     */
    protected function _updateTestRecord($id, $updates) {
        // Find the record to update
        $userTable = new User();
        $select = $userTable->select()->where('user_id = ?', $id);
        $row = $userTable->fetchRow($select);

        // Verify a record was returned
        $this->assertNotNull($row);

        // Set the updates
        if (isset($updates['user_name']))
            $row['user_name'] = $updates['user_name'];
        if (isset($updates['password']))
            $row['password'] = $updates['password'];
        if (isset($updates['date_updated']))
            $row['date_updated'] = $updates['date_updated'];
        if (isset($updates['active']))
            $row['active'] = $updates['active'];
        if (isset($updates['role_id']))
            $row['role_id'] = $updates['role_id'];

        // Save the changes
        $row->save();

        // Find the updated record
        $row = $userTable->fetchRow($select);

        // Verify the updates
        if (isset($updates['user_name']))
            $this->assertEquals($row['user_name'], $updates['user_name']);
        if (isset($updates['password']))
            $this->assertEquals($row['password'], $updates['password']);
        if (isset($updates['active']))
            $this->assertEquals($row['active'], $updates['active']);
        if (isset($updates['role_id']))
            $this->assertEquals($row['role_id'], $updates['role_id']);
    }

    /**
     * Deletes a test record
     *
     * @param int $id ID of the record to delete
     * @return void
     */
    protected function _deleteTestRecord($id) {
        // Find the record to delete
        $userTable = new User();
        $select = $userTable->select()->where('user_id = ?', $id);
        $row = $userTable->fetchRow($select);

        // Verify a record was returned
        $this->assertNotNull($row);

        // Delete the record
        $row->delete();

        // Find the deleted record
        $row = $userTable->fetchRow($select);

        // Verify a record was deleted
        $this->assertNull($row);
    }

    /**
     * Gets an array of test data
     *
     * @return mixed|array test data
     */
    protected function _getTestData() {
        $data['user_name'] = 'test';
        $data['password'] = 'test';
        $data['date_created'] = new Zend_Db_Expr('NOW()');
        $data['date_updated'] = new Zend_Db_Expr('NOW()');
        $data['active'] = 0;
        $data['role_id'] = 1;

        return $data;

    }
}
So there you have it! Implementing a data auditing solution using the Zend Framework fell somewhere between the ease of Ruby on Rails and the difficulty of doing it using the Java Persistence API.

The solution presented here has the potential to create an enormous amount of audit data depending on the application. If you are mainly interested in tracking updates, the _postInsert and _postDelete methods of the OpenEprs_Audit_Row class can be removed. Some applications store record creation data in extra table columns in lieu of storing this data in an audit table.

The reader should weigh the pros and cons of each approach based on their own particular requirements.