* globalization for DB strings

7 files changed, 294 insertions, 5 deletions

diff --git a/app/frontend/i18n/DbGlobalization.php b/app/frontend/i18n/DbGlobalization.php
new file mode 100644
index 0000000..4ab4b9e
--- /dev/null
+++ b/app/frontend/i18n/DbGlobalization.php
@@ -0,0 +1,255 @@
+<?php
+
+class DbGlobalization extends TModule {
+
+    const CACHE_PREFIX = 'DbGlobalization';
+
+    private $_connection;
+
+    public function setConnection($dbModule) {
+        $dbModule = $this->Application->getModule($dbModule);
+        if (!($dbModule instanceof TDataSourceConfig)) {
+            throw new TConfigurationException(
+                Prado::localize(
+                    'Invalid DB module ID for DbGlobalization'
+                )
+            );
+        }
+        $this->_connection = $dbModule->DbConnection;
+    }
+
+    public function getConnection() {
+        if (!($this->_connection instanceof TDbConnection)) {
+            throw new TConfigurationException(
+                Prado::localize(
+                    'Connection ID for DBGlobalization not set'
+                )
+            );
+        }
+        return $this->_connection;
+    }
+
+    private $_tableName = 'translations';
+    private $_languageColumn = 'language';
+    private $_categoryColumn = 'category';
+    private $_stringColumn = 'string';
+    private $_translationColumn = 'translation';
+
+    public function getTableName() {
+        return $this->_tableName;
+    }
+
+    public function getLanguageColumn() {
+        return $this->_languageColumn;
+    }
+
+    public function getCategoryColumn() {
+        return $this->_categoryColumn;
+    }
+
+    public function getStringColumn() {
+        return $this->_stringColumn;
+    }
+
+    public function getTranslationColumn() {
+        return $this->_translationColumn;
+    }
+
+    public function setTableName(string $value) {
+        $this->_tableName = $value;
+    }
+
+    public function setLanguageColumn(string $value) {
+        $this->_languageColumn = $value;
+    }
+
+    public function setCategoryColumn(string $value) {
+        $this->_categoryColumn = $value;
+    }
+
+    public function setStringColumn(string $value) {
+        $this->_stringColumn = $value;
+    }
+
+    public function setTranslationColumn(string $value) {
+        $this->_translationColumn = $value;
+    }
+
+    private $_cache;
+
+    public function setCache($value) {
+        if (TPropertyValue::ensureBoolean($value)) {
+            $this->_cache = $this->Application->getCache();
+        }
+    }
+
+    public function getCache() {
+        return $this->_cache;
+    }
+
+    protected $_cacheContent;
+
+    protected function _loadCache(string $lang) {
+        if ($this->Cache) {
+            if (!$this->_cacheContent) {
+                $this->_cacheContent = $this->Cache->get(self::CACHE_PREFIX . '.' . $lang);
+            }
+        }
+        return $this->_cacheContent;
+    }
+
+    protected function _getTranslationFromCache(string $string, string $category, string $language=NULL) {
+        if (!$language) {
+            $language = $this->Application->getGlobalization()->getCulture();
+        }
+        $cache = $this->_loadCache($language);
+        if (!$cache) {
+            return NULL;
+        }
+        if (isset($cache[$category]) && isset($cache[$category][$string])) {
+            return $cache[$category][$string];
+        }
+        return NULL;
+    }
+
+    public function rebuildCache() {
+        if ($this->Cache && $this->_rebuildCache) {
+            $dbTranslations = $this->_getTranslationsFromDB();
+            $cache = [];
+            foreach ($dbTranslations as $translation) {
+                $language = $translation[$this->LanguageColumn];
+                $category = $translation[$this->CategoryColumn];
+                $string = $translation[$this->StringColumn];
+                if (!isset($cache[$language])) {
+                    $cache[$language] = [];
+                }
+                if (!isset($cache[$language][$category])) {
+                    $cache[$language][$category] = [];
+                }
+                $cache[$language][$category][$string] = $translation[$this->TranslationColumn] ?: '';
+            }
+            foreach ($cache as $lang => $langCache) {
+                $this->Cache->set(self::CACHE_PREFIX . '.' . $lang, $langCache);
+            }
+        }
+    }
+
+    protected function _initTranslationDB() {
+        $this->Connection->setActive(TRUE);
+        try {
+            $tableInfo = $this->Connection->DbMetaData->getTableInfo(
+                $this->TableName
+            );
+        } catch (TDbException $e) {
+            $sql = 'CREATE TABLE %s ('
+                . '%s VARCHAR(255),'
+                . '%s VARCHAR(255),'
+                . '%s VARCHAR(255),'
+                . '%s TEXT,'
+                . 'PRIMARY KEY (%s, %s, %s),'
+                . 'INDEX (%s),'
+                . 'INDEX (%s),'
+                . 'INDEX (%s)'
+                . ')';
+            $tableName = $this->Connection->quoteTableName($this->TableName);
+            $languageColumn = $this->Connection->quoteColumnName($this->LanguageColumn);
+            $categoryColumn = $this->Connection->quoteColumnName($this->CategoryColumn);
+            $stringColumn = $this->Connection->quoteColumnName($this->StringColumn);
+            $translationColumn = $this->Connection->quoteColumnName($this->TranslationColumn);
+            $this->Connection->createCommand(
+                sprintf(
+                    $sql,
+                    $tableName,
+                    $languageColumn, $categoryColumn, $stringColumn, $translationColumn,
+                    $languageColumn, $categoryColumn, $stringColumn,
+                    $languageColumn,
+                    $categoryColumn,
+                    $stringColumn
+                )
+            )->execute();
+        }
+    }
+
+    protected function _getTranslationsFromDB(string $string=NULL, string $category=NULL, string $language=NULL) {
+        $this->_initTranslationDB();
+        $criteria = [];
+        if ($string) {
+            $criteria[$this->StringColumn] = $string;
+        }
+        if ($category) {
+            $criteria[$this->CategoryColumn] = $category;
+        }
+        if ($language) {
+            $criteria[$this->LanguageColumn] = $language;
+        }
+        $sql = 'SELECT * FROM ' . $this->Connection->quoteTableName($this->TableName);
+        $params = [];
+        if ($criteria) {
+            $sql .= ' WHERE ';
+            $sqlCriteria = [];
+            foreach ($criteria as $column => $value) {
+                $sqlCriteria[] = sprintf(
+                    '%s = ?',
+                    $this->Connection->quoteColumnName($column)
+                );
+                $params[] = $value;
+            }
+            $sql .= implode(' AND ', $sqlCriteria);
+        }
+        $command = $this->Connection->createCommand($sql);
+        foreach ($params as $index => $param) {
+            $command->bindValue($index + 1, $param);
+        }
+        return $command->query()->readAll();
+    }
+
+    protected function _insertTranslation(string $string, string $category, string $language) {
+        $sql = sprintf(
+            'INSERT INTO %s(%s, %s, %s) VALUES(?, ?, ?)',
+            $this->Connection->quoteTableName($this->TableName),
+            $this->Connection->quoteColumnName($this->LanguageColumn),
+            $this->Connection->quoteColumnName($this->CategoryColumn),
+            $this->Connection->quoteColumnName($this->StringColumn)
+        );
+        $command = $this->Connection->createCommand($sql);
+        $command->bindValue(1, $language);
+        $command->bindValue(2, $category);
+        $command->bindValue(3, $string);
+        $command->execute();
+    }
+
+    protected function _getTranslationFromDB(string $string=NULL, string $category=NULL, string $language=NULL) {
+        if (!$language) {
+            $language = $this->Application->getGlobalization()->getCulture();
+        }
+        $dbResult = $this->_getTranslationsFromDB($string, $category, $language);
+        if ($dbResult) {
+            return $dbResult[0][$this->TranslationColumn] ?: '';
+        } else {
+            $this->_insertTranslation($string, $category, $language);
+            return NULL;
+        }
+        return NULL;
+    }
+
+    private $_rebuildCache = FALSE;
+
+    public function getTranslation(string $string, string $category, string $language=NULL) {
+        $cacheString = $this->_getTranslationFromCache($string, $category, $language);
+        if ($cacheString !== NULL) {
+            return $cacheString ?: $string;
+        } else {
+            $dbString = $this->_getTranslationFromDB($string, $category, $language);
+            $this->_rebuildCache = TRUE;
+            return $dbString ?: $string;
+        }
+        return $string;
+    }
+
+    public function init($config) {
+        parent::init($config);
+        $this->Application->attachEventHandler('OnEndRequest', [$this, 'rebuildCache']);
+    }
+}
+
+?>
diff --git a/app/frontend/i18n/config.xml b/app/frontend/i18n/config.xml
index 52f5b7e..d04f78b 100644
--- a/app/frontend/i18n/config.xml
+++ b/app/frontend/i18n/config.xml
@@ -12,5 +12,9 @@
                    source="Application.i18n"
                    cache="true" />
     </module>
+    <module id="db-strings"
+            class="Application.i18n.DbGlobalization"
+            Connection="db"
+            Cache="true" />
   </modules>
 </configuration>
diff --git a/app/frontend/i18n/en/messages.mo b/app/frontend/i18n/en/messages.mo
index 962a4ea..81c93b3 100755
--- a/app/frontend/i18n/en/messages.mo
+++ b/app/frontend/i18n/en/messages.mo
diff --git a/app/frontend/i18n/en/messages.po b/app/frontend/i18n/en/messages.po
index 18a0bf0..fc253ee 100755
--- a/app/frontend/i18n/en/messages.po
+++ b/app/frontend/i18n/en/messages.po
@@ -6,8 +6,8 @@ msgid ""
 msgstr ""
 "Project-Id-Version: PACKAGE VERSION\n"
 "Report-Msgid-Bugs-To: \n"
-"POT-Creation-Date: 2016-06-10 11:58+0200\n"
-"PO-Revision-Date: 2016-06-10 11:56+0200\n"
+"POT-Creation-Date: 2016-06-12 16:52+0200\n"
+"PO-Revision-Date: 2016-06-10 16:14+0200\n"
 "Last-Translator: mkl <emkael@tlen.pl>\n"
 "Language-Team: English\n"
 "Language: en\n"
@@ -53,6 +53,16 @@ msgstr "Timezone preference change impossible for guest user"
 msgid "Page not found"
 msgstr "Page not found"
 
+#: app/frontend/i18n/DbGlobalization.php:12
+#: app/frontend/i18n/DbGlobalization.php:14
+msgid "Invalid DB module ID for DbGlobalization"
+msgstr "Invalid DB module ID for DbGlobalization"
+
+#: app/frontend/i18n/DbGlobalization.php:23
+#: app/frontend/i18n/DbGlobalization.php:25
+msgid "Connection ID for DBGlobalization not set"
+msgstr "Connection ID for DbGlobalization not set"
+
 #. app/frontend/controls/CalendarDetails.tpl:8
 #: cache/gettext/app/frontend/controls/CalendarDetails.c:2
 msgid "Source website"
diff --git a/app/frontend/i18n/messages.pot b/app/frontend/i18n/messages.pot
index ee5f6b2..fef8a3c 100644
--- a/app/frontend/i18n/messages.pot
+++ b/app/frontend/i18n/messages.pot
@@ -7,7 +7,7 @@ msgid ""
 msgstr ""
 "Project-Id-Version: PACKAGE VERSION\n"
 "Report-Msgid-Bugs-To: \n"
-"POT-Creation-Date: 2016-06-10 11:58+0200\n"
+"POT-Creation-Date: 2016-06-12 16:52+0200\n"
 "PO-Revision-Date: YEAR-MO-DA HO:MI+ZONE\n"
 "Last-Translator: FULL NAME <EMAIL@ADDRESS>\n"
 "Language-Team: LANGUAGE <LL@li.org>\n"
@@ -53,6 +53,16 @@ msgstr ""
 msgid "Page not found"
 msgstr ""
 
+#: app/frontend/i18n/DbGlobalization.php:12
+#: app/frontend/i18n/DbGlobalization.php:14
+msgid "Invalid DB module ID for DbGlobalization"
+msgstr ""
+
+#: app/frontend/i18n/DbGlobalization.php:23
+#: app/frontend/i18n/DbGlobalization.php:25
+msgid "Connection ID for DBGlobalization not set"
+msgstr ""
+
 #. app/frontend/controls/CalendarDetails.tpl:8
 #: cache/gettext/app/frontend/controls/CalendarDetails.c:2
 msgid "Source website"
diff --git a/app/frontend/i18n/pl/messages.mo b/app/frontend/i18n/pl/messages.mo
index 06ed14b..f786383 100755
--- a/app/frontend/i18n/pl/messages.mo
+++ b/app/frontend/i18n/pl/messages.mo
diff --git a/app/frontend/i18n/pl/messages.po b/app/frontend/i18n/pl/messages.po
index de775da..dff42b6 100755
--- a/app/frontend/i18n/pl/messages.po
+++ b/app/frontend/i18n/pl/messages.po
@@ -6,8 +6,8 @@ msgid ""
 msgstr ""
 "Project-Id-Version: PACKAGE VERSION\n"
 "Report-Msgid-Bugs-To: \n"
-"POT-Creation-Date: 2016-06-10 11:58+0200\n"
-"PO-Revision-Date: 2016-06-10 11:57+0200\n"
+"POT-Creation-Date: 2016-06-12 16:52+0200\n"
+"PO-Revision-Date: 2016-06-10 16:15+0200\n"
 "Last-Translator: mkl <emkael@tlen.pl>\n"
 "Language-Team: Polish\n"
 "Language: pl\n"
@@ -57,6 +57,16 @@ msgstr ""
 msgid "Page not found"
 msgstr "Nie znaleziono strony"
 
+#: app/frontend/i18n/DbGlobalization.php:12
+#: app/frontend/i18n/DbGlobalization.php:14
+msgid "Invalid DB module ID for DbGlobalization"
+msgstr "Błędny moduł bazy danych dla DbGlobalization"
+
+#: app/frontend/i18n/DbGlobalization.php:23
+#: app/frontend/i18n/DbGlobalization.php:25
+msgid "Connection ID for DBGlobalization not set"
+msgstr "Nie ustawiono modułu bazy danych dla DbGlobalization"
+
 #. app/frontend/controls/CalendarDetails.tpl:8
 #: cache/gettext/app/frontend/controls/CalendarDetails.c:2
 msgid "Source website"