Hi Stefan,
we parse the Array in PHP and store the language configuration in a
separate MySQL table.
The structure is as follows:
Column-Name | Data-Type | Example
-------------+--------------+---------------
baseId | int(11) | 1
isoCode | varchar(5) | en
active | tinyint(1) | 1
default | tinyint(1) | 0
title | varchar(100) | English
sort | int(11) | 0
With this solution, we're able to access the language configuration
from any programming language and technology (for example DIH from
Apache Solr).
Another advantage: You're able to get the language specific comlumn
suffix inside the SQL SELECT statement.
For example:
SELECT
`baseId`,
`isoCode`,
`active`,
`default`,
`title`,
`sort`,
IF(`baseId` > 0, CONCAT('_', `baseId`), '') columnSuffix
FROM
`oxlanguages`
ORDER BY
`sort` ASC
I think OXID should implement this feature.
Best regards
Stefan Krenz
Am 08.03.2012 10:53, schrieb Stefan Moises:
> Hi Daniel,
>
> since as you said the language mapping is stored as a serialized
> array in the oxconfig table, I don't see a way to grab it in plain
> SQL... Even getting the serialized value is a bit of a problem
> since you have to DECODE it first and therefore you need the
> "secret" key stored in core/oxconfk.php, e.g.
>
> SELECT DECODE( OXVARVALUE, <insert_secret_key_here> ) FROM
> `oxconfig` WHERE `OXVARNAME` = 'aLanguages'
>
> But then there is no real way to decode it in SQL afaik. You could
> unserialize it with Javascript, e.g. using some JS library like
> http://phpjs.org/functions/unserialize:571
>
> Concerning your Apache Solr use case: what we are doing for our
> Shoptifind search (which also uses Solr) is also to read the value
> in PHP (in a "pre-import" step) and to store it in another MySQL
> table, where we can then read it for the DIH import in Solr. We
> store it in the format "en|0@de|1" etc. and split it then with a
> regular expression, which looks something like this in the DIH
> config: <entity name="language" rootEntity="false"
> transformer="RegexTransformer"
> processor="CachedSqlEntityProcessor" query="SELECT SHOPTIFIND_VALUE
> from shoptifind_settings where
> OXSHOPID='${dataimporter.request.shopId}' AND
> SHOPTIFIND_KEY='sfLanguages'"> <field column="id"
> regex=".*${dataimporter.request.oxlang}\|([0-9])*.*"
> replaceWith="$1" sourceColName="SHOPTIFIND_VALUE" /> </entity> If
> you are really adventurous, you could try to use the DIH script
> transformer
> (http://wiki.apache.org/solr/DataImportHandler#ScriptTransformer)
> for Solr to somehow unserialize the value for the import with Java
> / Javascript:)
>
> Bye, Stefan
>
> Am 08.03.2012 10:12, schrieb Daniel Schlichtholz:
>> Hi,
>>
>> this is not the mapping of the language Ids. The language ids
>> depend on the order in which languages are added to oxid. If you
>> install English first and than later add German, the mapping is 0
>> = English, 1 = German If you do it the other way round the
>> mapping is 0 = German, 1 = English
>>
>> Now how can one get this mapping?
>>
>> We often need it because we need to import and /or export data
>> depending on the language. Normally you can map the isoCode to
>> the created views but not in all cases you can use this.
>>
>> The language mapping is stored in "oxconfig" table as a
>> serialized Array ("aLanguages" for the titles and
>> "aLanguageParams" for meta data). For now, we extract the data
>> and put it into a separate Table. The problem is, that you need
>> PHP to unserialze that array. We'd prefer Json instead of a
>> serialized php array or (better) move this mapping to an own
>> table.
>>
>> One use case: solr grabs data from mysql as the direct data
>> source (without php)
>>
>> Best regards,
>>
>> Daniel Schlichtholz
>>
>> Am 08.03.2012 08:55, schrieb Alfonsas Cirtautas:
>>> Hello,
>>>
>>> select oxid, oxisoalpha2, oxisoalpha3 from oxcountry;
>>>
>>> Alf. ________________________________________ From:
>>> [email protected]
>>> [[email protected]] on behalf of Stefan
>>> Krenz [[email protected]] Sent: Wednesday, March 07,
>>> 2012 12:44 PM To: [email protected] Subject:
>>> [oxid-dev-general] Mapping OXID language ID to ISO-Code
>>>
>>> Dear dev-list,
>>>
>>> is there a way to get the language mapping (OXID lang ID to
>>> ISO-Code) in plain SQL?
>>>
>>> Best regards Stefan Krenz
>>>
>>>
>>> -- Stefan Krenz Mayflower GmbH
>>> Gneisenaustraße 10/11 Tel.: +49 931 359 65 1163
>>> D-97074 Würzburg Fax : +49 931 359 65 28
>>> [email protected] http://www.mayflower.de
>>>
>>> Mayflower GmbH, Standort Würzburg Firmensitz: Mannhardtstraße
>>> 6, 80538 München Registergericht: Amtsgericht München, HRB
>>> 142039 Geschäftsführer: Gregor Streng, Björn Schotte, Albrecht
>>> Günther, Johann-Peter Hartmann
>>> _______________________________________________ dev-general
>>> mailing list [email protected]
>>> http://dir.gmane.org/gmane.comp.php.oxid.general
>>> _______________________________________________ dev-general
>>> mailing list [email protected]
>>> http://dir.gmane.org/gmane.comp.php.oxid.general
>>
>> _______________________________________________ dev-general
>> mailing list [email protected]
>> http://dir.gmane.org/gmane.comp.php.oxid.general
>>
>
--
Stefan Krenz Mayflower GmbH
Gneisenaustraße 10/11 Tel.: +49 931 359 65 1163
D-97074 Würzburg Fax : +49 931 359 65 28
[email protected] http://www.mayflower.de
Mayflower GmbH, Standort Würzburg
Firmensitz: Mannhardtstraße 6, 80538 München
Registergericht: Amtsgericht München, HRB 142039
Geschäftsführer: Gregor Streng, Björn Schotte,
Albrecht Günther, Johann-Peter Hartmann
_______________________________________________
dev-general mailing list
[email protected]
http://dir.gmane.org/gmane.comp.php.oxid.general
