PdoLoader as translation resource for Symfony2

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 <[email protected]>
 * @author Hugo Hamon <[email protected]>
 */
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_ASSOC);

        $this->translationsStatement = $stmt;

        return $stmt;
    }

    /**
     * Retrieves 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_ASSOC);

        $this->resourcesStatement = $stmt;

        return $stmt;
    }

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

    public function isFresh($timestamp)
    {
        $stmt = $this->getFreshnessStatement($timestamp);

        // 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 (Boolean) $count;
    }

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

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

        $stmt = $this->con->prepare($sql);
        $stmt->bindParam(':timestamp', $timestamp, \PDO::PARAM_INT);

        $this->freshnessStatement = $stmt;

        return $stmt;
    }

    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' }