Monday 20 September 2010

Database Migrations with the Zend Framework

If you're a user of the Zend Framework then you're no doubt aware that it was developed by Zend Technologies in response to the growing popularity of other frameworks (most notably Ruby on Rails and Spring) that don't use PHP. Worried that PHP would lose developer mind share to Ruby and Java, Zend developed the Zend Framework to give PHP developers a well designed foundation to build their PHP applications on.

Over the last 5 years, the Zend Framework has steadily grown into a fine framework to rival its competitors but for me there are a few areas where it is still lacking. If you're familiar with the Rails framework, then you'll know that one tool that Rails provides but that ZF doesn't is managed database migrations.

For these of you who don't know what a database migration in Rails is, consider your approach to designing your application database. Think about how you change a database table design and how you communicate those changes to your colleagues. You might use a WYSIWYG designer like MySQL workbench (and there's nothing wrong with that) to design and modify individual tables but how do you make sure that your team all update their local database schemas? How do you know which version of the database design should the staging server or the live server have?

This is the problem that the database migration paradigm aims to solve. It asserts that the design for the database should sit in the same code base as the whole application, and be versioned by your Revision Control System. Unfortunately, the Zend Framework doesn't help solve this problem and Rails has ZF beat here. In Rails, you drop to your command line, and using Rake (Ruby's make tool) you issue a command to create a migration. This command creates a file that you open in your editor and enter the changes you want to make to the database (in a rather lovely Domain Specific Language). Then you pop back to your command line, issue another command, and Rake applies these changes to your local database. Should you need to roll the changes back, you can do that too.

There have been attempts to bring database migrations to ZF before, notably Rob Allen's Akrabat project. In fact, the only issue I have with Rob Allen's implementation is that it passes the actual database adapter to the migration object, and the migration script interacts with the database directly. To my mind, this isn't quite right; I want the migration script to return a number of SQL statements and to have the migration system manage any actual interaction with the database. One benefit of this is that it makes the migration plugin more flexible; for example, it can then run in what Zend calls pretend mode (where it runs the migration but doesn't actually commit the changes).

The Mooduino Implementation


I've uploaded the first version of my migration plugin to my github (http://github.com/michaelhodgins/Mooduino). It doesn't do everything that I want it to do as yet but it is functional and I've started using it in my own work.

Installation is quite straight forward. First, download the Mooduino library from github. I put my libraries in my home directory on my development PC, so Mooduino would end up in the directory '/home/michael/programming/php/mooduino/'.

Open a terminal and issue these two commands:

zf --setup storage-directory
zf --setup config-file

These will create a file called .zf.ini in your home directory (or in a directory called .zf). Open the .zf.ini file and add the location of your local copy of the Mooduino library to the php.include_path value. This value should also include the path to your local copy of the Zend Framework.

You also need a new line telling the zf command line tool about the Mooduino migration provider. Add the following:

basicloader.classes.0 = "Mooduino_Db_Migrations_MigrationProvider"

If you already have a value for basicloader.classes.0, make it basicloader.classes.1. As an aside, if there is a value for php.includepath, this is a bug (related to Netbeans I think) and it should be php.include_path instead.

To check that this worked, return to your terminal and issue this command:

zf ? migration

This should print the following list, showing you the commands that you can use with the migration plugin.

zf generate migration name env[=development] base-class[=default]
zf redo migration step[=1] env[=development]
zf undo migration step[=1] env[=development]
zf current migration env[=development]
zf update migration to[=latest] env[=development]
zf show migration revision[=list] env[=development]
zf clear migration env[=development]

In operation


To actually use the plugin, you'll need a ZF project with the database configured in application/config/application.ini. The database will also need to have been created.

Use the first command in the above list to generate a migration file. For example:

zf generate migration Notes

creates a file called scripts/migrations/1283772577_Notes.php and this file contains a class called Migration_1283772577_Notes. The long number is a timestamp in seconds and obviously depends on when you issue the generate command. This timestamp is used to sequence the files. This class extends Mooduino_Db_Migrations_Migration_Abstract and contains two empty methods, up() and down(). These two methods are expected to return either a string or an array of strings; the strings are expected to be valid SQL statements for the application's RDBMS. The statements returned by the down() method are expected to reverse the effects of the statements returned by the up() method.

For example, if I wanted to create the notes table from my previous blog post, I'd fill in the migration as follows.

class Migration_1283772577_Notes extends Mooduino_Db_Migrations_Migration_Abstract {

  public function __construct() {
    parent::__construct('Notes', 12837725773
);
  }

  public function up() {
    return 'CREATE TABLE `notes` (
             `id` bigint(20) NOT NULL AUTO_INCREMENT,
             `text` longtext NOT NULL,
             `title` varchar(255) NOT NULL,
             `priority` int(11) NOT NULL,
             PRIMARY KEY (`id`),
             UNIQUE KEY `Search` (`title`),
             KEY `Priority` (`priority`)
            );
    ';
  }

  public function down() {
    return 'DROP TABLE `notes`;';
  }

}


If you return to your terminal, and issue the command zf show migration list, you'll see the migration details, showing that it hasn't been executed against your local database. To execute the up() method, issue the command zf update migration. This will execute all unapplied migrations unless you include the fourth parameter. If you issue this command and then look at your database, you'll see the notes table has been added, along with a table called schema_version, which the plugin uses to maintain its state.

Once you'll got a few migrations created, the plugin allows you to move your database back and forth between them. For example, the redo command rolls back a number or migrations and then reapplies them. The undo command just rolls them back. Both of these methods default to one step but for example the command zf redo migration 2 will rollback and reapply the last two migrations that were applied. The method clear rolls back all migrations. The current method shows you which migration was the last to be executed.

Lastly, notice that each method has an env parameter that defaults to development; you use this to set which database to execute the migrations on and should be an environment from your application.ini file (but that doesn't mean I recommend executing the migrations against your production database - this is a development tool, not a deployment tool).

Future developments


Though the database migration plugin is functional, it is far from feature complete. There are a number of improvements I'd like to make.

  • I've played around with using milliseconds for the timestamps (to reduce the chance of getting two migration files with the same timestamp) but this doesn't work reliably on 32bit operating systems because the integer overflows.
  • I'd like to add the pretend mode mentioned earlier.
  • I'd also like to make the database adapter object available to the migration object so that it can interact with the database directly if it needs to.
  • In a team environment, it is likely that more than one team member will be adding migration files and when it comes time to merge working copies, you might end up with missed migrations - migrations that have not been executed but that are older than your own migrations that have been executed. I'd like to add a new method to the plugin to automatically apply missed migrations.
  • Arguably the most impressive feature in the Rails migration system is the domain specific language used to script the changes to the database; rather than use SQL directly, in Rails you use a database agnostic language that builds at runtime SQL statements that are specific to the database that you are using. This means that you can use one RDBMS on your development machine (say, SQLite) and another in your staging environment (say MySQL). Creating this system would obviously be an enormous undertaking (perhaps 90% effort for 10% functionality) but it would be nice to have.

Feedback


If you find any bugs in the plugin, or have any suggestions for improvements, I'd love to hear from you.

Saturday 18 September 2010

Fat Models and Thin Controllers with the Zend Framework

In this article I'm going to discuss what I call Fat Models and how to create them using the Zend Framework. I'll also introduce an abstract base class that wraps up the idea in a reusable way. I'll finish up with a simple example showing the full MVC setup.

The idea of Fat Models is where, in the Model View and Controller (MVC) pattern, to put the code that validates whether the model object can be saved to the database. When a model object has been created or edited, usually via a form, the input is passed through one or more validation objects and only if these validations pass can the object be saved to the database.

Usually this validation code sits in the controller, either in the action method or in a private helper method. The drawback (for me anyway) is that this ties the model directly to the controller and makes testing the behaviour of the model more challenging. It also means that if a programming error is made in the controller, an invalid object might get saved to the database. We call this a fat controller, because it contains a lot of code.

In the Fat Model paradigm, the object maintains it's own validation code. The thin controller simply pumps whatever data it has into the model object and then asks the model object if it can be saved. If the validation fails, the model object populates a list of error messages, that the controller can use to show to the end user. This logic is held in an abstract class, shown below. I've also put this code into my github repository (http://github.com/michaelhodgins/Mooduino); it is under the GPLv2 licence.

abstract class Mooduino_Model_Abstract extends Zend_Db_Table_Row_Abstract { 
  private $_validation_errors = null; 
  /** 
   * Returns true if the model is valid (it passes the validation rules). 
   * @return boolean 
   */ 
  public function isValid() { 
    if (is_null($this->_validation_errors)) { 
      $errors = $this->validate(); 
      if (is_array($errors)) { 
        $this->_validation_errors = $errors; 
      } else { 
        throw new Exception('Validate method didn\'t return an array.'); 
      } 
    } 
    return count($this->_validation_errors) == 0; 
  } 
  /** 
   * Returns all validation errors if there are any or null if there are none. 
   * @return array[string]string|array[string]array[int]string 
   */ 
  public function getErrors() { 
    if (is_array($this->_validation_errors)) { 
      return $this->_validation_errors; 
    } else { 
      throw new Exception('Model has not been validated'); 
    } 
  } 
  /** 
   * Returns the validation error or array of errors for the given field name. 
   * Be sure that there is an error before calling this method by calling 
   * hasError() first. 
   * @param string $field 
   * @return string|array[int]string 
   */ 
  public function getError($field) { 
    $errors = $this->getErrors(); 
    if (array_key_exists($field, $errors)) { 
      return $this->_validation_errors[$field]; 
    } else { 
      throw new Exception('Field not found'); 
    } 
  } 
  /** 
   * Returns true if the given field name has a validation error or false if 
   * it hasn't. 
   * @param string $field 
   * @return boolean 
   */ 
  public function hasError($field) { 
    return is_array($this->_validation_errors) && array_key_exists($field, $this->_validation_errors); 
  } 
  /** 
   * An implementation of the method should validate the instance of the 
   * implementing class and return an array of error messages. If there are 
   * no errors, an empty array should be returned. 
   * @return array[string]string|array[string]array[int]string 
   */ 
  public abstract function validate(); 
  /** 
   * Overrides the save() method in Zend_Db_Table_Row_Abstract so that it is 
   * only called if $this->isValid() returns true. 
   * @return mixed 
   */ 
  public final function save() { 
    if ($this->isValid()) { 
      return parent::save(); 
    } else { 
      throw new Exception('Model can\'t be saved as it isn\'t valid'); 
    } 
  } 
  /** 
   * Given an Iterator such as a Zend_Form, this method will set any error 
   * messages to the form elements. 
   * @param Iterator $iterator 
   */ 
  public final function discoverErrors(Iterator $iterator) { 
    foreach ($iterator as $element) { 
      if ($element instanceof Iterator) { 
        $this->discoverErrors($element); 
      } elseif ($element instanceof Zend_Form_Element) { 
        $this->discoverError($element); 
      } 
    } 
  } 
  /** 
   * If there is a validation error for the given field, it will be set. 
   * @param Zend_Form_Element $element 
   */ 
  private final function discoverError(Zend_Form_Element $element) { 
    if ($this->hasError($element->getName())) { 
      $element->addErrors($this->getError($element->getName())); 
    } 
  } 
}

The last two methods (one public and one private) are used later by the controller to populate a Zend_Form object with the errors that occurred during validation. The public method is recursive so that it correctly handles fieldsets.

An example model


As you can see, the abstract class extends Zend_Db_Table_Row_Abstract and in order to make use of this class, we'll need two model classes; a Table class and a Row class (you can also set up a Rowset class but I don't tend to do that).

First the Table class; this is the class that describes the database table and it's relationships with other tables in the database. Before we can create a Table class, we need an actual database table. For this example, I'm going to use the following simple table.

CREATE TABLE `mooduino_notes`.`notes` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `text` longtext NOT NULL,
 `title` varchar(255) NOT NULL,
 `priority` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `Search` (`title`,`text`(767)),
 KEY `Priority` (`priority`)
);

The Table class is very simple in this example because we only have one table; it simply sets which table in the database to point to and which class to use as the Row class.

class Application_Model_DbTable_Notes extends Zend_Db_Table_Abstract {
  protected $_name = 'notes';
  protected $_rowClass = 'Application_Model_Note';
}

The Row class isn't much more complicated; it implements the abstract validate() method from the base class, and it encapsulates the database table columns names with getters and setters. I do this so that the code in the view doesn't need any knowledge of the database columns. Notice how the validate() method employs the Zend_Validate package to check the three fields in the model.

class Application_Model_Note extends Mooduino_Model_Abstract {
  /**
   * Returns an list of error messages, if there are any generated
   * while validating the note.
   * @return array
   */
  public function validate() {
    $errors = array();
    
    $titleValidator = new Zend_Validate_StringLength(array(
      'min'=>3,
      'max'=>255
    ));
    if (!$titleValidator->isValid($this->getTitle())) {
      $errors['title'] = $titleValidator->getMessages();
    }
    $textValidator = new Zend_Validate_StringLength(array('min'=>1));
    if (!$textValidator->isValid($this->getText())) {
      $errors['text'] = $textValidator->getMessages();
    }
    $priorityValidator = new Zend_Validate_Int();
    if (!$priorityValidator->isValid($this->getPriority())) {
      $errors['priority'] = $priorityValidator->getMessages();
    }
    return $errors;
  }
  /**
   * Returns the id.
   * @return int
   */
  public function getId() {
   return $this->id;
  }
  /**
   * Returns the title.
   * @return string
   */
  public function getTitle() {
    return $this->title;
  }
  /**
   * Sets the title.
   * @param string $title
   */
  public function setTitle($title) {
    $this->title = $title;
  }
  /**
   * Returns the text.
   * @return string
   */
  public function getText() {
    return $this->text;
  }
  /**
   * Sets the text.
   * @param string $text
   */
  public function setText($text) {
    $this->text = $text;
  }
  /**
   * Returns the priority.
   * @return int
   */
  public function getPriority() {
    return $this->priority;
  }
  /**
   * Sets the priority.
   * @param int $priority
   */
  public function setPriority($priority) {
    $this->priority = $priority;
  }
}

The views


We have our model, next our view. I'm going to have three; one that lists existing notes (the index action), one for adding a new note and one for editing an existing note. The index action's view, index.phtml, is as follows. Notice that I'm using Smarty syntax but you don't have to. The script checks that there are notes to display, and if there are, it lists them in a table.

<a href="/notes/new">New Note</a>
{if $notes->count() < 0}
<table>
 <thead>  
  <tr>
    <th>ID</th>
    <th>Title</th>
    <th>Priority</th>
   </tr>
 </thead>
 <tbody>
   {foreach from=$notes item=note}
   <tr>
    <td>{$note->getId()}</td>
    <td>{$note->getTitle()}</td>
    <td>{$note->getPriority()}</td>
    <td><a href="/notes/edit/id/{$note->getId()}">Edit</a></td>
   </tr>
   {/foreach}
 </tbody>
</table>
{else}
 <p>There are no notes at this time.</p>
{/if}

The new and edit action scripts both do the same thing; they render the form that they are passed by the controller. Save the following as new.phtml and edit.phtml in the scripts/notes directory.

{if isset($form)}
  {$form}
{/if}


The thin(ner) controller.


The last part of the example is of course the controller, now without any validation code. You'll notice though that it still sanitizes the user's input in the updateNoteFromRequest() method. I've used the Zend_Filter_StripTags class but you'd do whatever your application requires. In the controller you should notice that the action methods are rather short. Most of the functionality you might otherwise put in the action methods is handled elsewhere, either in private methods (like creating the Zend_Form object) or in the model classes.

class NotesController extends Zend_Controller_Action {

 public function init() {
  /* Initialize action controller here */
 }

 public function indexAction() {
  $table = new Application_Model_DbTable_Notes();
  $this->view->notes = $table->fetchAll();
 }

 public function newAction() {
  $table = new Application_Model_DbTable_Notes();
  $note = $table->fetchNew();

  if ($this->getRequest()->isPost()) {

   $this->updateNoteFromRequest($note, $this->getRequest());

   if ($note->isValid()) {
    $note->save();
    $this->_redirect('/notes/index');
   }
  }
  $this->view->note = $note;
  $this->view->form = $this->notesForm($note);
 }

 public function editAction() {
  $id = $this->getRequest()->getParam('id', 0);
  if ($id == 0) {
   $this->_redirect('/notes/index');
   exit;
  }
  $table = new Application_Model_DbTable_Notes();
  $select = $table->select()
    ->where('id = ?', $id);
  $note = $table->fetchRow($select);
  
  if ($this->getRequest()->isPost()) {

   $this->updateNoteFromRequest($note, $this->getRequest());

   if ($note->isValid()) {
    $note->save();
    $this->_redirect('/notes/index');
   }
  }
  $this->view->note = $note;
  $this->view->form = $this->notesForm($note);
 }

 /**
  * Takes the data from the request object and puts it into the note object.
  * @param Application_Model_Note $note
  * @param Zend_Controller_Request_Abstract $request
  */
 private function updateNoteFromRequest(Application_Model_Note $note, Zend_Controller_Request_Abstract $request) {
  $filter = new Zend_Filter_StripTags();

  $note->setTitle($filter->filter($request->getParam('title', '')));
  $note->setText($filter->filter($request->getParam('text', '')));
  $note->setPriority($filter->filter($request->getParam('priority', '')));
 }

 /**
  * Creates a form for the given note object.
  * @param Application_Model_Note $note
  * @return Zend_Form
  */
 private function notesForm(Application_Model_Note $note = null) {
  $form = new Zend_Form();

  $title = new Zend_Form_Element_Text('title', array('label' => 'Title'));
  $form->addElement($title);

  $text = new Zend_Form_Element_Textarea('text', array('label' => 'Text'));
  $form->addElement($text);

  $priority = new Zend_Form_Element_Text('priority', array('label' => 'Priority'));
  $form->addElement($priority);

  $submit = new Zend_Form_Element_Submit('submit', array('value' => 'Save'));
  $form->addElement($submit);

  if (!is_null($note)) {
   $title->setValue($note->getTitle());
   $text->setValue($note->getText());
   $priority->setValue($note->getPriority());

   $note->discoverErrors($form);
  }

  return $form;
 }

}