API vs GUI language code inconsistency or how to best use ISO 639-3?

Question

I am comparing the values that appear in GUI Administration > Localization > Preferred Language options with the ones returned with the API. There you can see that Chinese Taiwan and Chinese China have the same value zh so that they cannot be distinguished.

Then when you use the API:

$result = civicrm_api3('OptionValue', 'get', array(
  'sequential' => 1,
  'option_group_id' => "languages",
  'options' => array('limit' => 0),
  'id' => array('IN' => array(557, 558)),
));

Then it will return beside this GUI 'value' also 'name':

{
    "is_error": 0,
    "version": 3,
    "count": 2,
    "values": [
        {
            "id": "557",
            "option_group_id": "78",
            "label": "Chinese (China)",
            "value": "zh",
            "name": "zh_CN",
            "is_default": "0",
            "weight": "137",
            "is_optgroup": "0",
            "is_reserved": "0",
            "is_active": "1"
        },
        {
            "id": "558",
            "option_group_id": "78",
            "label": "Chinese (Taiwan)",
            "value": "zh",
            "name": "zh_TW",
            "is_default": "0",
            "weight": "138",
            "is_optgroup": "0",
            "is_reserved": "0",
            "is_active": "1"
        }
    ]
} 

The Administration GUI keeps this 'name' hidden and I cannot change it nor add it in new Languages, but it is this what is stored as preferred language for a contact, which again can be seen using the API and 'value' is not used anywhere.

May be I am missing something obvious here, but am I right that the GUI does not provide any facility to change code to for instance ISO-639-3? What would be the preferred way to get rid of Civi's default language 'name' options and use an ISO-639-3 equivalent in stead? Setting the 'value' is useless. Is there anyone that has been doing this successfully? Should I pursue using the API or direct MySQL manipuation to import my own Language options?

Answer 1

• First I needed to find a source of ISO-639-3 to import in MySQL. I found this one at the SIL website
• I used phpmyadmin to import the table, used \t as column delimiter and used iso_639_3 as table name
• I used phpmyadmin to discover that (in my case?) the language option_group_id equals 78. Just search for a language for instance Dutch and you will find the row with the value.

• Knowing this value you can now delete all default language options:

  DELETE FROM civicrm_option_value WHERE option_group_id = 78

• Alternatively you can also deactivate them:

  UPDATE civicrm_option_value SET is_active=0 WHERE option_group_id = 78

• I used this query to import all languages in active state:

  SET @weight := 0; INSERT INTO civicrm_option_value ( option_group_id, label, value, name, weight, is_active ) SELECT 78, Inverted_Name, Id, Id, (@weight := @weight + 1 ), 1 FROM iso_639_3 ORDER BY Inverted_Name ASC

• Alternatively I found that this query allows me to import all iso-639-3 options but only a subset activated:

  SET @weight := 0; INSERT INTO civicrm_option_value ( option_group_id, label, value, name, weight, is_active ) SELECT 78, Inverted_Name, Id, Id, (@weight := @weight + 1 ), (SELECT Id IN('nld','eng','fra','deu','ita','por','spa')) FROM iso_639_3 iso ORDER BY Inverted_Name ASC