PdoLoader as translation resource for Symfony2

PdoDumper.php

<?php

namespace Ormigo\Bundle\TranslationBundle\Translation\Dumper;

use Ormigo\Bundle\TranslationBundle\Translation\Loader\PdoLoader;

use Symfony\Component\Translation\Dumper\DumperInterface;
use Symfony\Component\Translation\MessageCatalogue;

class PdoDumper extends PdoLoader implements DumperInterface
{
    protected $insertStatement;
    protected $updateStatement;
    protected $selectStatement;

    /**
     * Dumps the message catalogue.
     *
     * @param MessageCatalogue $messages The message catalogue
     * @param array            $options  Options that are used by the dumper
     */
    public function dump(MessageCatalogue $messages, $options = array())
    {
        $this->con->beginTransaction();

        $insertStmt = $this->getInsertStatement();
        $updateStmt = $this->getUpdateStatement();
        $selectStmt = $this->getSelectStatement();

        $now = strtotime('now');

        $locale = $messages->getLocale();
        foreach ($messages->getDomains() as $eachDomain) {
            foreach ($messages->all($eachDomain) as $eachKey => $eachTranslation) {
                $selectStmt->bindValue(':locale', $locale);
                $selectStmt->bindValue(':domain', $eachDomain);
                $selectStmt->bindValue(':key', $eachKey);

                if (false === $selectStmt->execute()) {
                    throw new \RuntimeException('Could not fetch translation data from database.');
                }

                $currentTranslation = null;

                $selectStmt->bindColumn('translation', $currentTranslation);
                $selectStmt->fetch();

                $dumpStmt = null;
                if (null === $currentTranslation) {
                    $dumpStmt = $insertStmt;
                } else {
                    if ($currentTranslation === (string) $eachTranslation) {
                        continue;
                    }

                    $dumpStmt = $updateStmt;
                }


                $dumpStmt->bindValue(':key', $eachKey);
                $dumpStmt->bindValue(':translation', (string) $eachTranslation);
                $dumpStmt->bindValue(':locale', $locale);
                $dumpStmt->bindValue(':domain', $eachDomain);
                $dumpStmt->bindValue(':updated_at', $now, \PDO::PARAM_INT);

                $dumpStmt->execute();
            }
        }

        if (!$this->con->commit()) {
            $this->con->rollBack();

            throw new \RuntimeException(sprintf('An error occurred while committing the transaction. [%s: %s]', $this->con->errorCode(), $this->con->errorInfo()));
        }
    }

    protected function getInsertStatement()
    {
        if ($this->insertStatement instanceof \PDOStatement) {
            return $this->insertStatement;
        }

        $sql = vsprintf('INSERT INTO `%s` (`%s`, `%s`, `%s`, `%s`, `%s`) VALUES (:key, :translation, :locale, :domain, :updated_at)', array(
            // INSERT INTO ..
            $this->getTablename(),
            // ( .. )
            $this->getColumnname('key'),
            $this->getColumnname('translation'),
            $this->getColumnname('locale'),
            $this->getColumnname('domain'),
            $this->getColumnname('updated_at'),
        ));

        $stmt = $this->getConnection()->prepare($sql);

        $this->insertStatement = $stmt;

        return $stmt;
    }

    protected function getUpdateStatement()
    {
        if ($this->updateStatement instanceof \PDOStatement) {
            return $this->updateStatement;
        }

        $sql = vsprintf('UPDATE `%s` SET `%s` = :translation, `%s` = :updated_at WHERE `%s` = :key AND `%s` = :locale AND `%s` = :domain', array(
            // UPDATE ..
            $this->getTablename(),
            // SET ( .. )
            $this->getColumnname('translation'),
            $this->getColumnname('updated_at'),
            // WHERE ..
            $this->getColumnname('key'),
            $this->getColumnname('locale'),
            $this->getColumnname('domain'),
        ));

        $stmt = $this->getConnection()->prepare($sql);
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);

        $this->updateStatement = $stmt;

        return $stmt;
    }

    protected function getSelectStatement()
    {
        if ($this->selectStatement instanceof \PDOStatement) {
            return $this->selectStatement;
        }

        $sql = vsprintf('SELECT `%s` AS `translation` FROM `%s` WHERE `%s` = :locale AND `%s` = :domain AND `%s` = :key', array(
            // SELECT ..
            $this->getColumnname('translation'),
            // FROM ..
            $this->getTablename(),
            // WHERE ..
            $this->getColumnname('locale'),
            $this->getColumnname('domain'),
            $this->getColumnname('key'),
        ));

        $stmt = $this->getConnection()->prepare($sql);
        $stmt->setFetchMode(\PDO::FETCH_COLUMN, 0);

        $this->selectStatement = $stmt;

        return $stmt;
    }
}

PdoLoader.php

<?php

namespace Ormigo\Bundle\TranslationBundle\Translation\Loader;

use Symfony\Component\Config\Resource\ResourceInterface;
use Symfony\Component\Translation\Loader\LoaderInterface;
use Symfony\Component\Translation\MessageCatalogue;
use Symfony\Component\Translation\Translator;

/**
 * A translation loader retrieving data from a configured PDO connection.
 *
 * The loader is also the resource itself: the table definition.
 *
 * @author Toni Uebernickel <tuebernickel@gmail.com>
 * @author Hugo Hamon <webmaster@apprendre-php.com>
 */
class PdoLoader implements LoaderInterface, ResourceInterface
{
    protected $con;
    protected $options = array(
        'table' => 'translations',
        'columns' => array(
            // The key and its translation ..
            'key' => 'key',
            'translation' => 'translation',
            // .. for the given locale ..
            'locale' => 'locale',
            // .. under this domain.
            'domain' => 'domain',
            // The datetime of the last update.
            'updated_at' => 'updated_at',
        ),
    );

    protected $freshnessStatement;
    protected $resourcesStatement;
    protected $translationsStatement;

    public function __construct(\PDO $con, array $options = array())
    {
        $this->con = $con;
        $this->options = array_replace_recursive($this->options, $options);
    }

    /**
     * Loads a locale.
     *
     * @param mixed  $resource A resource
     * @param string $locale   A locale
     * @param string $domain   The domain
     *
     * @return MessageCatalogue
     */
    public function load($resource, $locale, $domain = 'messages')
    {
        // The loader only accepts itself as a resource.
        if ($resource !== $this) {
            return new MessageCatalogue($locale);
        }

        $stmt = $this->getTranslationsStatement();
        $stmt->bindValue(':locale', $locale, \PDO::PARAM_STR);
        $stmt->bindValue(':domain', $domain, \PDO::PARAM_STR);

        if (false === $stmt->execute()) {
            throw new \RuntimeException('Could not fetch translation data from database.');
        }

        $stmt->bindColumn('key', $key);
        $stmt->bindColumn('translation', $trans);

        $catalogue = new MessageCatalogue($locale);
        while ($stmt->fetch()) {
            $catalogue->set($key, $trans, $domain);
        }

        return $catalogue;
    }

    protected function getTranslationsStatement()
    {
        if ($this->translationsStatement instanceof \PDOStatement) {
            return $this->translationsStatement;
        }

        $sql = vsprintf('SELECT `%s` AS `key`, `%s` AS `translation` FROM `%s` WHERE `%s` = :locale AND `%s` = :domain', array(
            // SELECT ..
            $this->getColumnname('key'),
            $this->getColumnname('translation'),
            // FROM ..
            $this->getTablename(),
            // WHERE ..
            $this->getColumnname('locale'),
            $this->getColumnname('domain'),
        ));

        $stmt = $this->getConnection()->prepare($sql);
        $stmt->setFetchMode(\PDO::FETCH_BOUND);

        $this->translationsStatement = $stmt;

        return $stmt;
    }

    /**
     * Retrieve all locale-domain combinations and add them as a resource to the translator.
     *
     * @param Translator $translator
     *
     * @throws \RuntimeException
     */
    public function registerResources(Translator $translator)
    {
        $stmt = $this->getResourcesStatement();

        if (false === $stmt->execute()) {
            throw new \RuntimeException('Could not fetch translation data from database.');
        }

        $stmt->bindColumn('locale', $locale);
        $stmt->bindColumn('domain', $domain);

        while ($stmt->fetch()) {
            $translator->addResource('pdo', $this, $locale, $domain);
        }
    }

    protected function getResourcesStatement()
    {
        if ($this->resourcesStatement instanceof \PDOStatement) {
            return $this->resourcesStatement;
        }

        $sql = vsprintf('SELECT DISTINCT `%s` AS `locale`, `%s` AS `domain` FROM `%s`', array(
            // SELECT ..
            $this->getColumnname('locale'),
            $this->getColumnname('domain'),
            // FROM ..
            $this->getTablename(),
        ));

        $stmt = $this->getConnection()->prepare($sql);
        $stmt->setFetchMode(\PDO::FETCH_BOUND);

        $this->resourcesStatement = $stmt;

        return $stmt;
    }

    public function isFresh($timestamp)
    {
        $stmt = $this->getFreshnessStatement();
        $stmt->bindParam(':timestamp', $timestamp, \PDO::PARAM_INT);

        // If we cannot fetch from database, keep the cache, even if it's not fresh.
        if (false === $stmt->execute()) {
            return true;
        }

        $stmt->bindColumn(1, $count);
        $stmt->fetch();

        return (bool) $count;
    }

    protected function getFreshnessStatement()
    {
        if ($this->freshnessStatement instanceof \PDOStatement) {
            return $this->freshnessStatement;
        }

        $sql = vsprintf('SELECT COUNT(*) FROM `%s` WHERE `%s` > :timestamp', array(
            $this->getTablename(),
            $this->getColumnname('updated_at'),
        ));

        $stmt = $this->con->prepare($sql);
        $stmt->setFetchMode(\PDO::FETCH_COLUMN, 0);

        $this->freshnessStatement = $stmt;

        return $stmt;
    }

    public function __toString()
    {
        return 'PDOLoader::'.base64_encode($this->options);
    }

    public function getResource()
    {
        return $this;
    }

    public function getConnection()
    {
        return $this->con;
    }

    public function getTablename()
    {
        return $this->options['table'];
    }

    public function getColumnname($column)
    {
        return $this->options['columns'][$column];
    }
}

schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<database name="default" package="src.Ormigo.Bundle.TranslationBundle.Model" namespace="Ormigo\Bundle\TranslationBundle\Model" defaultIdMethod="native" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://xsd.propelorm.org/1.6/database.xsd">
    <table name="translation">
        <column name="id" type="integer" autoIncrement="true" primaryKey="true" />
        <column name="key" type="varchar" size="255" required="true" primaryString="true" />
        <column name="message" type="varchar" size="255" required="true" />
        <column name="locale" type="varchar" size="255" required="true" />
        <column name="domain" type="varchar" size="255" required="true" />

        <behavior name="versionable">
            <parameter name="log_created_at" value="true" />
            <parameter name="log_created_by" value="true" />
            <parameter name="log_comment" value="true" />
        </behavior>

        <unique>
            <unique-column name="key" />
            <unique-column name="locale" />
            <unique-column name="domain" />
        </unique>
        
        <index>
            <index-column name="locale" />
            <index-column name="domain" />
        </index>
    </table>
</database>

services.yml

services:
    connection.propel.default:
        class: PropelPDO
        factory_class: Propel
        factory_method: getConnection
        arguments:
            - "default"

    translation.loader.pdo:
        class: Ormigo\Bundle\TranslationBundle\Translation\Loader\PdoLoader
        arguments:
            - '@connection.propel.default'
            -
                table: "translation"
                columns:
                    translation: "message"
                    updated_at: "version_created_at"
        calls:
            - [ registerResources, [ @translator ] ]
        tags:
            - { name: 'translation.loader', alias: 'pdo' }

    # The above loader is not necessary anymore and can be merged with the definition for the dumper.
    # See the commented lines, how to merge this into one service.
    translation.dumper.pdo:
        class: Ormigo\Bundle\TranslationBundle\Translation\Dumper\PdoDumper
        arguments:
            - '@connection.propel.default'
            -
                table: "translation"
                columns:
                    translation: "message"
                    updated_at: "version_created_at"
#        calls:
#            - [ registerResources, [ @translator ] ]
        tags:
            - { name: 'translation.dumper', alias: 'pdo' }
#            - { name: 'translation.loader', alias: 'pdo' }

Look at my forked version: https://gist.github.com/3321943

For optimizing performances, I've lazy loaded PDOStatement object so that they are not rebuilt everytime the methods are called. A prepared statement must be created once and executed several times with several parameters.

Cheers.

Thanks for the update, I just merged the changes!

Hi, thanks for this snippet.
Do I need to do something additional/special for symfony to use isFresh method?

EDIT:
The resource (loader) is never added to MessageCatalogue. But it won't work, even with that, because Resource needs to be serialized and PDO instance won't serialize. This PR is trying to fix this issue: symfony/symfony#7230.