Nov 252010
 

update: The contents of this blog post has now been merged into the Doctrine 2 cookbook entry (http://www.doctrine-project.org/docs/orm/2.0/en/cookbook/sql-table-prefixes.html)

A few months ago, we started using Doctrine 2 on the project we’re developing for a customer of ours. Doctrine 2 has proven to be both fast and stable which has certainly saved us time and effort. We recently had to implement table prefixes on our tables in Doctrine 2, which turned out to be not so trivial, and led me to a journey inside the Doctrine 2 metadata parser, event hooks and relation mapper. Fun stuff!

Prefixing regular tables

The first thing I had to do was to get prefixes working. Contrary to Doctrine 1, where you could use the Doctrine_Core::ATTR_TBLNAME_FORMAT to set a table prefix (or suffix), there isn’t yet a similar feature for Doctrine 2 (looking at some bug reports like this one, it doesn’t look like it’s high up on the list of priorities either). However, the solution suggested was to add a loadClassMetadata listener to provide the table prefixes by rewriting the defined table names as they were being generated and add the prefix programatically in that event. The cookbook entry describes the procedure for implementing this in detail, and after adding the suggested lines of code to our Doctrine loader tables were being prefixed all around.

Prefixing many-to-many jointables

However, the many-to-many automatically generated jointables were *not* being prefixed, and there turned out to be a good explanation. The loadClassMetadata event was being fired in the table that defined the many-to-many joined relations, but there was no separate loadClassMetadata event fired for the generated jointables where I could rewrite the table name with the configured prefix. As Benjamin Beberlei mentioned in a follow-up comment to the bug report we filed for the cookbook entry, this was both (semi-)intended behaviour and a known limitation of the code in the cookbook entry with an “easy” fix. The fix meant I had to look into taking a peek at the table’s metadata and perform an explicit rewrite on the related jointable. Luckily that’s all documented behaviour and the pointers Benjamin gave me were more than enough to get me started.

The behaviour of the code in the cookbook was to just get the table name from the class metadata as it was being loaded, prepend the table prefix and set the table name back to the new prefixed name. Easy peasy.

$classMetadata = $eventArgs->getClassMetadata();
$classMetadata->setTableName($this->_prefix . $classMetadata->getTableName());

However, to rewrite the table name for many-to-many relation jointables, we have to also traverse the classmetadata properties, look for related tables and rewrite them if the join type is many-to-many:

foreach ($classMetadata->getAssociationMappings() as $fieldName => $mapping) {
if ($mapping['type'] == \Doctrine\ORM\Mapping\ClassMetadataInfo::MANY_TO_MANY) {
$tableName = $classMetadata->associationMappings[$fieldName]['joinTable']['name'];
$classMetadata->associationMappings[$fieldName]['joinTable']['name'] = $this->_prefix . $tableName;
}
}

Turns out that it wasn’t that much effort needed, and the result is neatly prefixed table names all around. As an extra, we of course also added a ZF application.ini entry to define the table prefix for easy configuration. But that’s a different blog post.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>