Feeding Zend\I18n\Translator from a database

Thursday, August 23, 2012

Sometimes you need to get translation messages from a database, for instance when you want your clients to be able to add or edit translations. By default, this is not possible with the translator, but its extendibility allows you to easily integrate it.

Let's get started with the table layout. To get full support for all features, you will need two tables:
CREATE TABLE `locales` (
  `locale_id` char(5) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `locale_plural_forms` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (`locale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `messages` (
  `message_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `locale_id` char(5) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `message_domain` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `message_key` text NOT NULL,
  `message_translation` text NOT NULL,
  `message_plural_index` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`message_id`),
  KEY `locale_id` (`locale_id`),
  KEY `message_domain` (`message_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1`
  FOREIGN KEY (`locale_id`)
  REFERENCES `locales` (`locale_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
The first table is pretty easy to explain. It contains the 5-character locale (for instance: en-EN) and the definitions for the plural forms. The plural forms are defined the same way as they are in gettext and other formats.

The second table is not much more complex. It contains a primary integer key, a reference to the locale and the message specific data. Those are for once text domain, the key, so the string which you use in your source code, the translation for it and optionally the plural index, in case the translation requires plural forms. The plural index shoudl be equivalent with the result of the plural forms evaluation.

Now after we have created our table layout, we need to write a translation loader which is able to retrieve translations from the database. A very simple implementation with Zend\Db could look something like this:
<?php
use Zend\Db\Adapter\Adapter as DbAdapter;
use Zend\Db\Sql\Sql;
use Zend\I18n\Translator\Loader\LoaderInterface;
use Zend\I18n\Translator\Plural\Rule as PluralRule;
use Zend\I18n\Translator\TextDomain;

class DatabaseTranslationLoader implements LoaderInterface
{
    protected $dbAdapter;

    public function __construct(DbAdapter $dbAdapter)
    {
        $this->dbAdapter = $dbAdapter;
    }

    public function load($filename, $locale)
    {
        $textDomain = new TextDomain();
        $sql        = new Sql($this->dbAdapter);

        $select = $sql->select();
        $select->from('locales');
        $select->columns(array('locale_plural_forms'));
        $select->where(array('locale_id' => $locale));

        $localeInformation = $this->dbAdapter->query(
            $sql->getSqlStringForSqlObject($select),
            DbAdapter::QUERY_MODE_EXECUTE
        );

        if (!count($localeInformation)) {
            return $textDomain;
        }

        $localeInformation = reset($localeInformation);

        $textDomain->setPluralRules(
            PluralRule::fromString($localeInformation['locale_plural_forms'])
        );

        $select = $sql->select();
        $select->from('messages');
        $select->columns(array(
            'message_key',
            'message_translation',
            'message_plural_index'
        ));
        $select->where(array(
            'locale_id'      => $locale,
            'message_domain' => $filename
        ));

        $messages = $this->dbAdapter->query(
            $sql->getSqlStringForSqlObject($select),
            DbAdapter::QUERY_MODE_EXECUTE
        );

        foreach ($messages as $message) {
            if (isset($textDomain[$message['message_key']])) {
                if (!is_array($textDomain[$message['message_key']])) {
                    $textDomain[$message['message_key']] = array(
                        $message['message_plural_index'] => $textDomain[$message['message_key']]
                    );
                }

                $textDomain[$message['message_key']][$message['message_plural_index']]
                    = $message['message_translation'];
            } else {
                $textDomain[$message['message_key']] = $message['message_translation'];
            }
        }

        return $textDomain;
    }
}
This loader is a little bit tricky, as we are abusing the $filename parameter to pass in the text domain we want to load. Apart from that, the code should be pretty much self-explaining. Next you need to create a factory, so that the service manager can populate the loader with a database adapter when the translator requests an instance of the loader:
<?php
use Zend\ServiceManager\FactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;

class DatabaseTranslationLoaderFactory implements FactoryInterface
{
    public function createService(ServiceLocatorInterface $serviceLocator)
    {
        return new DatabaseTranslationLoader(
            $serviceLocator->get('Zend\Db\Adapter\Adapter')
        );
    }
}
Finally we can add the translations to our application:
<?php
$translator->addTranslationFile(
    'DatabaseTranslationLoader',
    'text-domain',
    'text-domain'
);
We have to enter the text domain twice here, as you read earlier we are abusing the $filename parameter to pass it to the loader. Now the translator is ready to use. You should make sure to use caching, as loading the translations on every request is kinda heavy. You'd usually choose a long caching time, and then simply invalidate the cache everytime the database translations are updated.

I hope this post will help all of you seeking for a solution to this problem. All code examples are written down out of my head, so they may contain mistakes or something may be missing at all. In that case please gist me a corrected version of that part, so I can update it.

Be sure that more i18n related topics are following in the near future, and when you are going to ZendCon, don't miss my i18n or router talk there!

Update
I'm currently working on a database translation loader added to ZF2 itself. It will most likely be available in 2.1.

Comments to this article

  • Avatar of Philipp Reply Philipp Friday, August 24, 2012 1:20 PM

    Hey,
    very nice post. Inspired me this morning to build basically the same thing for doctrine. A couple of things though:

    1. I submitted a Pull-Request to ZF2, which allows to use translation_patterns to invoke the load, but one would loose the textdomain (https://github.com/zendframework/zf2/pull/2232)
    2. I think there is a slight bug in the foreach of the messages. If it is not an array, but you find a second matching key you create the array, but set the key to the value of the just retrieved message, which gets overwritten a couple of lines later. To circumvent this I added an equivalent of "ORDER BY message_plural_index ASC" in my code and replaced the first $message['message_plural_index'] with no key, because I can be sure that if I find a second matching key the other one would have been supposed to zero (of course on could have ommited 0 and just had 2 and 3, but then something else probably is broken)

    Best,
    Philipp

    • Avatar of Ben Scholzen 'DASPRiD' Reply Ben Scholzen 'DASPRiD' Friday, August 24, 2012 1:40 PM

      About your PR, I commented and closed it for now. About your other issue, you are right, I'm using the wrong plural index for the right message, gotta fix that one.

  • Avatar of Carsten Reply Carsten Wednesday, September 5, 2012 12:28 AM

    Hi Ben,

    thanks for your post. For some reason I'm not able to use your example with my test application, because the PluginManager in Zend\I18n\Translation is not aware of the factory I built and registered in the "service_manager"=>"factories" configuration. I also tried the Modules getServiceConfig(), no success.

    In fact, the PluginManager doesn't seem to have any information from the Service Manager at all. Surely I'm missing something obvious here, so any help would be really appreciated!

    Thanks,

    Carsten

    • Avatar of Ben Scholzen 'DASPRiD' Reply Ben Scholzen 'DASPRiD' Wednesday, October 24, 2012 1:07 AM

      Actually, you can do $pluginManager->getServiceLocator(). Found that out while at ZendCon :)

Leave a comment

Please note that your email address will not be shown, it is only used to fetch your avatar image from gravatar.com and for notifications. 

                             _           
  __ _  __ _ _ __ ___  _   _| |__   __ _ 
 / _` |/ _` | '_ ` _ \| | | | '_ \ / _` |
| (_| | (_| | | | | | | |_| | | | | (_| |
 \__, |\__,_|_| |_| |_|\__, |_| |_|\__,_|
    |_|                |___/