Why not annotating your database schema ?

Annotation are now widely used among a lot of languages and development frameworks.

At the begining, annotatations where used as standards for describing the code, and parsed by most IDEs in order to provide autocompletion:

Today, annotations in the code can do much more as they will actively induce some behavior:

/**
 * @doBefore(checkLogged)
 */
 public function displayProfile(){
  ...
}

/**
 * @db_column role_id 
 * @foreign_key role.id 
*/
public $roleId();
    

Now, here's the point : why not using annotations in table or columns descriptions, directly in the data model, and use them to implement similar or new behaviors?

We were facing a simple problem : how to define a default sort column when retrieving the rows from a table ? We finally came to the decision to use annotations in some tables to define which column should be used by default for sorting results...

The Big Picture

Let's say you need to load a user form, that contains a role select box. By default, the role items will be displayed from the oldest to the newest role (i.e by ascending role id). Most often, you want to display the roles in a alphanumeric order. To do this, we will create a @defaultSort annotation, and write it into the name column of the role table.

	→
We don't want this ...		... But this !

How To ?

The ORM plugin of the Mouf Framework (TDBM) analyses the data model, then generates DAOs: for each table, 2 DAOs are generated. For example, about the role table will have associated a RoleDaoBase, and a RoleDAO which is in fact an empty class extending the RoleDaoBase. DaoBases are re-generated each time the DB model changes, whereas the child DAO class can be filled with custom methods. Now here is the code for retrieving the lines from the role table :

class RoleDaoBase implements DAOInterface{

  /**
   * Get all Role records. 
   *
   * @return array<App\Dao\Bean\RoleBean>
   */
  public function getList() {
    return $this->tdbmService->getObjects('role', null, null, null, null, 'App\Dao\Bean\RoleBean');
  }
}

Here is the definition of the TdbmService::getObjects() method:

/*
 * @param string $table_name The name of the table queried
 * @param mixed $filter_bag The filter bag (WHERE clauses and other constraints)
 * @param mixed $orderby_bag The order bag (ORDER BY clauses)
 * @param integer $from The offset
 * @param integer $limit The maximum number of rows returned
 * @param string $className Optional: The name of the class to instanciate. This class must extend the TDBMObject class. If none is specified, a TDBMObject instance will be returned.
 * @param unknown_type $hint_path Hints to get the path for the query (expert parameter, you should leave it to null).
 * @return TDBMObjectArray A TDBMObjectArray containing the resulting objects of the query.
 */
public function getObjects($table_name, $filter_bag=null, $orderby_bag=null, $from=null, $limit=null, $className=null, $hint_path=null)

So, we cannot use the generated getList() method since it doesn't specify any "Order By" parameter. Therefore, we implement the getListOrderByLabel() method in the RoleDao class:

class RoleDao extends RoleDaoBase{

  /**
   * Get all Role records, ordered by role label 
   *
   * @return array
   */
  public function getListOrderByLabel() {
    return $this->tdbmService->getObjects('role', null, new OrderByColumn('role', 'label', 'asc'), null, null, 'App\Dao\Bean\RoleBean');
  }
}

This is quite annoying because the most often, when we retrieve a list of roles, we need it to be sorted by roles' labels. Therefore it would be great if the getList() method had a default sort uppon the label column of the role table :

class RoleDaoBase implements DAOInterface{

  /**
   * If set, deefines the default sort columns where retrieving lines in the role table
   * @var string
   */
  private $defaultSortColumn = 'label';

  /**
   * Get all Role records. 
   *
   * @return array
   */
  public function getList() {
    if ($this->defaultSortColumn){
      $sort = new OrderByColum('role', $this->defaultSortColumn, $this->defaultSortDirection);
    }else{
      $sort = null;
    }
    
    return $this->tdbmService->getObjects('role', null, $sort, null, null, 'App\Dao\Bean\RoleBean');
  }
}

The last point explains how we came to the idea of using anotations in our database schema in order to parse them and assign the defaultSortColumn variable to the RoleDaoBase class :

Now, when analysing the Database's structure, we simple parse the columns' comments, looking for a @defaultSort annotation. If set, the associated column will be defined as the $defaultSort variable of the table's DaoBase...

Going Further ...

Now, that's the first step, setting annotation in tables' and/or columns' descriptions offers a broad perspective :

• Define edit and view rights for a table's column
• Define which column should be used for displaying a line (this could be used for implementing a toString() method)
• Any other idea ? Please feel free to make some suggestions!