[CALR-55] i18n multi-lingual support - retrieval of DB i18n labels

Issue migrated from JIRA: https://sdlc.review.fao.org/jira/browse/CALR-55 Creator/Reporter: Emmanuel Blondel @eblondel Assignee: Brian Ojok @brianandy Priority: Medium Status: Done Date of creation: 2022-01-14T09:32:34.000+0000

This task is in continuity of CALR-49 (i18n static labels). Here we want to retrieve i18n labels that are stored in the Calipseo/openfismis DB model. In the latter, all tables containing names have a standard structure with a NAME, DESCRIPTION and I18N columns starting with a default one I18N_DEFAULT, and followed by columns specific to each language, eg I18N_EN, I18N_FR, etc

On R Shiny side, currently, we do SQL queries, in which the NAME column is retrieved, in general it can be the species name, vessel name, etc.

The task will consist in making the SQL queries I18N compatible, so in principle, given a language specified in the configuration, we will pick up the right language I18N_ column in the DB for all queries:

first, to revise all SQL queries where a NAME is invoked, and replace this property by the default I18N_DEFAULT column name (which label in general is the same as NAME, aka the english name)
second, handle a utility function that will take a SQL query string (as character) as input, a language (ISO2 code) and will return the modified SQL query string where I18N_DEFAULT will be replaced by the corresponding language column name

Logic/Proposal to test:


applyI18nColumnName <- function(sql, language = NULL){

    if(is.null(language) return(sql)

    new_sql <- gsub("I18N_DEFAULT", sprintf("I18N_%s", toupper(language)), sql)

    return(new_sql)

}

Apply the above function within all R functions developed to access data from DB; which means all functions will have to be extended with a new 'language'. When calling the data access utils functions in the different modules, the language will be inherited from the 'appConfig'

un-fao / calipseo-shiny

[CALR-55] i18n multi-lingual support - retrieval of DB i18n labels #55