Language handling from eShop v4.5.0

MySQL table row size limitations reduced number of possible languages in OXID eShop. The number of supported languages decreased even more if UTF8 encoding is used, since the row size increases.

OXID eShop v4.5.0 will come with a solution for this by introducing "language set" tables. It works by keeping language columns, which do not fit to the main object table, in the additional "language set" tables. We chose this approach since it is most compatible alternative with the current functionality and allows to set the required mysql charset and collation on the columns.

The default number of languages per table (both the main table and language set extensions) is 8. This number can be changed by modifying iLangPerTable config variable (min value 2 - at least 2 languages per table). Please note, that this number should be changed only before adding the 8th (default languages per table number) language as the already created language tables will not be recreated. It is best to do it right after setup, before adding new languages. If updating from older OXID eShop version, which has more than 8 languages, please set iLangPerTable config variable to the number of languages in the shop. Then, by adding new language, the required language set tables, will be created automatically if they do not exist.

To get the table for specific language, one should only check the result of getLangTableName('tablename', $iLanguageId) global function. Which works by checking $iSetNr=(int)($iLanguageId / $iLangPerTable) equation. If the result is 0, the base object table will be used, otherwise - {basename}_set{$iSetNr} language set table.

To simplify the usage, new DB views are generated, which select only one language field from the alternatives e.g. oxv_oxarticles_en from the default installation will select oxtitle_1 as oxtitle from the base oxarticles table (skipping the real oxtitle, oxtitle_2, oxtitle_3 columns from the result set). For OXID eShop Enterprise Edition, view oxv_oxarticles_2_en will select only english language columns and also will filter the result for the 2nd subshop.

Sometimes it is necessary to select all table columns (e.g. for exporting), but as the table is split to the base table and language set tables, there is no direct way to do so. For the convenience of such tasks, DB views without language suffix (like oxv_oxarticles) are provided. These views join all language tables and return raw result. For OXID eShop Enterprise Edition, the additional views with only shop id suffix (e.g. oxv_oxarticles_1) are also provided to support filtering for subshops. (a lot of languages can cause multiple joins on the oxid field, this affects performance and should be used only if this functionality is really necessary).

Note: selecting from language range 0-8 does not affect eShop performance, while loading languages from further ranges can cause bigger DB load as it has to join two tables to select the required data.

Architecture changes

 * each multilanguage table will be splitted to multiple tables if language limit is reached. The additional tables will contain only oxid field and multilanguage fields for specific languages set e.g. (languages from 8th to 15th);


 * the extra tables are named after the core table with language set suffix e.g.

oxarticles_set1


 * each language has its own DB view for each DB table, e.g.

oxv_oxarticles_de, oxv_oxarticles_en


 * For OXID eShop Enterprise Edition the language views are available with shop include/exclude limiting functionality (combined) e.g.

oxv_oxarticles_1_de, oxv_oxarticles_2_de

and without it e.g.

oxv_oxarticles_de, oxv_oxarticles_en


 * Sometimes you might want to load data in non multilanguage mode. In that case you need to set setEnableMultilang(false) for oxI18n object. Then view, joining all language set tables will be used e.g.

oxv_oxarticles

These views are basically large joins on oxid, which are slower, but contain whole object as one dataset - usually used for exporting data, and not in eShop frontend.

Most important API changes

 * ::getLangTableName( $sTable, $iLangId ) - returns the language set (or base, if language id is less than iLangPerTable) table for given base table and language id.


 * ::getViewName( $sTable, $iLangId = null, $sShopId = null ) - changed to support multilanguage tables.This function returns a view name to select from, given the required object table and parameters.
 * oxI18n::getViewName - returns view name by object language. Uses global getViewName by providing internally set parameters.
 * oxBase::getSqlFieldName($sField) - replaced by oxI18n::getUpdateSqlFieldName($sField) as such functionality is only needed when updating multilingual object. (select fields are handled by DB views). Return the DB column name for update or insert operations.

Changes for modules

 * if installing module, which adds multilanguage columns or creates such tables directly in eShop DB, current language count should be checked and, if needed, language set tables should be adapted too.
 * if module wants to select all multilanguage fields of an object (e.g. for export), $iLang parameter should be -1 for getViewName. This will return a view of joined all object tables, so that mlfield, mlfield_1, ..., mlfield_x will be accessible.
 * if selecting data via direct sql, getLanguageTag should not be used - instead, the sql should select from the correct view (see examples section).
 * after adding new DB columns to tables, views should be regenerated too.

Selecting oxtitle_1 from article (english title via raw sql)
Previously:

oxDb::getDb-&gt;getOne( 'select oxtitle'.oxLang::getInstance-&gt;getLanguageTag(1) .' from '.getViewName('oxarticles') );

From 4.5.0 on:

oxDb::getDb-&gt;getOne( 'select oxtitle from '.getViewName('oxarticles', 1) );

Updating oxtitle_1 of article (english title via raw sql)
Previously:

oxDb::getDb-&gt;execute( 'update oxarticles' .' set oxtitle'.oxLang::getInstance-&gt;getLanguageTag(1).' ="value"' );

From 4.5.0 on:

oxDb::getDb-&gt;execute( 'update '.getLangTableName('oxarticles', 1) .' set oxtitle'.oxLang::getInstance-&gt;getLanguageTag(1).' ="value"' );

Using your own multilanguage tables
To use your own multilanguage tables, you had to list them in the aMultiLangTables-Array in your config.inc.php.