Upload translations to Localazy using Invantive SQL

In this topic you will learn how you can connect to a cloud-platform API for which Invantive SQL provides no connector out-of-the box using SQL-statements. The statements can be run on the Free Plan of Invantive products, such as using Invantive Cloud or Invantive Query Tool.

The sample will be based on uploading and downloading texts from applications to be translated exchanging data between Localazy and Invantive Studio.

Localazy is an online tool for continuous translation of application in multiple target languages using computer-aided translation, machine translation and virtual/professional translator inside and outside the development team. Localazy eases global roll-out of small to large applications.

Invantive SQL is a programming language which real-time connects over 75 (cloud)platforms using familiar ANSI SQL statements. Invantive SQL provides a procedural option.

Invantive Studio is a CASE-tool for real-time datawarehousing and OLTP-applications with time travel on basis of Invantive SQL. The storage is similar to data vault with fat satellites.

Application translation process

The developers enter new text as resource keys in Invantive Studio using the Windows frontend or direct access to the Invantive Studio APIs.

The Invantive Studio repository in total approximately 660.000 resources with approximately 3,1 million words across ten common languages and a number of applications. Most application use US English as source language, which reduces translation costs and availability of translators.

A background job uploads the repository every hour to Localazy as virtual “files” to import. The background jobs re-uploads existing translations, but also new resource keys and/or new translations in the source language or other languages coming from other sources than Localazy or Invantive Studio.

Localazy has currently no option to upload advanced translation assets such as glossaries.

Localazy has a strong focus on translating files. Translations are grouped per resource key. Localazy does not provide segmentation of the contents of individual resources. The translations are combined for Localazy in files with a JSON structure. One file is defined as the month in which the resource was registered in a translation for the source language. Files can be quite large, with thousands of translations, but Localazy only allows 1.000 resources to be uploaded per JSON API call. However, the contents of files can be distributed across multiple API calls, so each file is reconstructed during the API upload process.

Translators on the Localazy platform get a message when there is work to be done. The work is done either directly in the Localazy website or using a custom download mechanism.

When their work is done, the background job incorporates the translated texts in the Invantive Studio repository as a new release.

The individual applications and SQL-drivers share large portions of the translations and extract what they need from the Invantive Studio repository at build time.

Typically, the turnaround time between translations decreases an order of magnitude when comparing this process with a previously used process with OmegaT and Git, such as from days to hours.

Localazy API Upload Program

The upload program for Localazy consists of a few procedures and the actual program.

The following code in Invantive SQL defines a procedure get_localazy_project_id to get the Localazy project ID based upon the API key and project name. Please note that Localazy currently has an API-key per project.

--
-- Pre-define temporary table.
--
create or replace table Work@inmemorystorage
as
select '-' apn_code
,      '-' lge_code
,      '-' key
,      '-' value
,      1 tln_id
,      '-' tln_text
  
create or replace function get_localazy_project_id
( p_localazy_project_name varchar2
, p_localazy_api_key      varchar2
)
return varchar2
--
-- Get the Localazy project ID for a Localazy project name.
--
-- Note that the API key is *currently* per project.
--
is
  l_localazy_project_id varchar2;
begin
  --
  -- Retrieve Localazy project ID based upon name.
  --
  select jte.id
  into   l_localazy_project_id
  from   httpdownload@datadictionary
         ( url => 'https://api.localazy.com/projects'
         , contentType => 'application/json'
         , method => 'GET'
         , headers => 'Authorization=Bearer ' || p_localazy_api_key 
         ) htp
  join   jsontable
         ( '[*]'
           passing htp.contents_char
           columns id             varchar2 path 'id'
           ,       orgId          varchar2 path 'org_id'
           ,       name           varchar2 path 'name'
           ,       slug           varchar2 path 'slug'
           ,       image          varchar2 path 'image'
           ,       url            varchar2 path 'url'
           ,       restricted     varchar2 path 'restricted'
           ,       tone           varchar2 path 'tone'
           ,       role           varchar2 path 'role'
           ,       sourceLanguage varchar2 path 'sourceLanguage'
           ,       description    varchar2 path 'description'
         ) jte
  on     jte.name = p_localazy_project_name
  ;
  return l_localazy_project_id;
end;

The procedure invantive_studio_to_localazy extracts the current translations in source language and other languages from the Invantive Studio repository, and uploads them to Localazy respecting the rate limit.

Note the use of httpdownload@datadictionary to send translations to the Localazy API.

create or replace procedure invantive_studio_to_localazy
( p_localazy_project_id          varchar2
, p_localazy_api_key             varchar2
, p_file_prefix                  varchar2
, p_resource_code_regex          varchar2
, p_studio_application_code      varchar2
, p_studio_primary_language_code varchar2
)
is
  --
  -- Upload translations in Invantive Studio repository to Localazy.
  --
  -- Upload throughput is approximately 400 resources per second.
  --
  g_upload_size_api       number   := 1000;
begin
  create or replace table TranslationsAll@inmemorystorage
  as
  select tlnall.tln_id
  ,      tlnall.tln_date_created
  ,      tlnall.tln_resource_code
  ,      tlnall.lge_code
  ,      tlnall.tln_text
  from   itgen_translations_v@ora tlnall
  where  tlnall.apn_code          = p_studio_application_code
  and    regexp_instr(tlnall.tln_resource_code, p_resource_code_regex) != 0
  ;
  --
  -- Group translations in files, with one file per month.
  -- A translations are placed in the file for the month
  -- the original English translation was created.
  --
  create or replace table TranslationsToDo@inmemorystorage
  as
  select tln.tln_id
  ,      tln.tln_date_created
  ,      tln.tln_resource_code
  ,      p_file_prefix
         || '-'
         || to_char(tln.tln_date_created, 'YYYYMM')
         filenamegroup
  from   TranslationsAll@inmemorystorage tln
  where  tln.lge_code = p_studio_primary_language_code
  ;
  --
  -- Files can be uploaded distributed across multiple Localazy API requests.
  -- An effort is made to upload 1.000 translations per API call.
  --
  create or replace table Upload@inmemorystorage
  as
  select filenamegroup
  ,      lge_code
  ,      apicallgroup
  ,      listagg
         ( jsonencode(tln_resource_code) 
           || ':'
           || case 
              when tln_text is null 
              then '""' 
              else jsonencode(tln_text) 
              end
         , ', '
         )
         translations_json
  ,      count(*) cnt
  from   ( select filenamegroup
           ,      lge_code
           ,      tln_resource_code
           ,      tln_text
           ,      filenamegroup
                  || '-'
                  || to_char
                     ( ( filenamegroup_row_number - mod(filenamegroup_row_number, g_upload_size_api) )
                       / g_upload_size_api
                     ) 
                  apicallgroup
  from   ( select tlnen.filenamegroup
           ,      tlnall.lge_code
           ,      tlnall.tln_resource_code
           ,      tlnall.tln_text
           ,      row_number(tlnen.filenamegroup)
                  filenamegroup_row_number
           from   TranslationsToDo@inmemorystorage tlnen
           join   TranslationsAll@inmemorystorage tlnall
           on     tlnall.tln_resource_code = tlnen.tln_resource_code 
         )
  )
  group
  by     filenamegroup
  ,      lge_code
  ,      apicallgroup
  ;
  create or replace table UploadStatuses@inmemorystorage
  as
  select htp.*
  ,      msg_json
  from   ( select '{ "importAsNew": false, "forceCurrent": true, "filterSource": false, "files": '
                  || '[ '
                  || '{ "name": '
                  || jsonencode(filenamegroup || '.json')
                  || ', "path": "' || p_studio_application_code || '"'
                  || ', "content": {'
                  || t.json
                  || ' }}]}'
                  msg_json
           from   ( select apicallgroup
                    ,      filenamegroup
                    ,      listagg
                           ( jsonencode(lge_code)
                             || ': {'
                             || translations_json
                             || '}'
                           , ', '
                           ) 
                           json
                    ,      sum(cnt) cnt
                    from   Upload@inmemorystorage
                    group
                    by     apicallgroup
                    ,      filenamegroup
                  ) t
         )
  join   httpdownload@datadictionary
         ( url => 'https://api.localazy.com/projects/' || p_localazy_project_id || '/import'
         , contentType => 'application/json'
         , method => 'POST'
         , headers => 'Authorization=Bearer ' || p_localazy_api_key
         , textPayload => msg_json
         , ignoreWebError => false
         --
         -- 100 requests per minute, 20 extra to make sure not going too fast.
         --
         , minimumDurationMs => 60 * 1000 / (100-20)
         ) htp
  ;
end;

The procedure localazy_to_invantive_studio_prepare downloads all translations from Localazy as files and puts them in the table Work@InMemoryStorage for further uploading.

create or replace procedure localazy_to_invantive_studio_prepare
( p_localazy_project_id     varchar2
, p_localazy_api_key        varchar2
, p_studio_application_code varchar2
)
as
begin
  --
  -- Load translated resources from Localazy into the Invantive Studio repository.
  --
  -- First retrieve all files.
  --
  create or replace table LocalazyFiles@inmemorystorage
  as
  select jte.id
  ,      jte.name
  ,      jte.path
  ,      jte.type
  from   httpdownload@datadictionary
         ( url => 'https://api.localazy.com/projects/' || p_localazy_project_id || '/files'
         , contentType => 'application/json'
         , method => 'GET'
         , headers => 'Authorization=Bearer ' || p_localazy_api_key 
         ) htp
  join   jsontable
         ( '[*]'
           passing htp.contents_char
           columns id   varchar2 path 'id'
           ,       name varchar2 path 'name'
           ,       path varchar2 path 'path'
           ,       type varchar2 path 'type'
         ) jte
  ;
  --
  -- Then retrieve the resource file contents for all languages usable in a user interface.
  --
  create or replace table NewTtranslations@inmemorystorage
  as
  select ale.apn_code
  ,      ale.lge_code
  ,      jte.key
  ,      jte.value
  from   LocalazyFiles@inmemorystorage fle
  join   itgen_apn_languages_v@ora ale
  on     ale.ale_usable_in_ui_flag = 'Y'
  and    ale.apn_code              = p_studio_application_code
  --
  -- Restrict languages further for the moment.
  --
  and    ale.lge_code in ('nl', 'de', 'es', 'fr')
  join   httpdownload@datadictionary
         ( url => 'https://api.localazy.com/projects/' || p_localazy_project_id || '/files/' || fle.id || '/keys/' || ale.lge_code
         , contentType => 'application/json'
         , method => 'GET'
         , headers => 'Authorization=Bearer ' || p_localazy_api_key
         --
         -- 100 requests per minute, 20 extra to make sure not going too fast.
         --
         , minimumDurationMs => 60 * 1000 / (100-20) 
         , diskCache => true
         , diskCacheMaxAgeSec => 3600
         ) htp
  join   jsontable
         ( 'keys[*]'
           passing htp.contents_char
           columns id     varchar2 path 'id'
           ,       key    varchar2 path 'key[0]'
           ,       value  varchar2 path 'value'
           ,       vid    varchar2 path 'vid'
         ) jte
  ;
end;

The procedure localazy_to_invantive_studio_upload does the actual insert and update of updated translations from Localazy into the Invantive Studio repository.

create or replace procedure localazy_to_invantive_studio_upload
as
begin
  --
  -- Merge translations from Localazy with existing translations
  -- where available in a new table 'work'.
  --
  create or replace table Work@inmemorystorage
  as
  select ntn.apn_code
  ,      ntn.lge_code
  ,      ntn.key
  ,      ntn.value
  ,      tln.tln_id
  ,      tln.tln_text
  from   NewTtranslations@inmemorystorage ntn
  left
  outer
  join   itgen_translations_v@ora tln
  on     tln.apn_code          = ntn.apn_code
  and    tln.lge_code          = ntn.lge_code
  and    tln.tln_resource_code = ntn.key
  --
  -- Not yet clear why sometimes 'value' is null.
  --
  where  ntn.value is not null
  ;
  --
  -- Insert the complete new translations (resource code plus language combination)
  -- in the Invantive Studio repository.
  --
  insert into itgen_translations_v@ora
  ( apn_code
  , lge_code
  , tln_resource_code
  , tln_text
  )
  select wrk.apn_code
  ,      wrk.lge_code
  ,      wrk.key
  ,      wrk.value
  from   Work@inmemorystorage wrk
  where  wrk.tln_id is null
  ;
  --
  -- Update translations.
  --
  for r 
  in
  ( select wrk.apn_code
    ,      wrk.lge_code
    ,      wrk.key
    ,      wrk.value
    ,      wrk.tln_text
    ,      wrk.tln_id
    from   Work@inmemorystorage wrk
    where  wrk.tln_id is not null
    and    wrk.tln_text != value
  )
  loop
    update itgen_translations_v@ora
    set    tln_text          = r.value
    where  apn_code          = r.apn_code
    and    lge_code          = r.lge_code
    and    tln_id            = r.tln_id
    and    tln_resource_code = r.key
    ;
  end loop;
end;

The actual program to bi-directionally exchange the translations is:

declare
  l_localazy_project_id varchar2;
  --
  g_studio_application_code varchar2 := 'repos/itgen/trunk';
begin
  --
  -- Fill two Localazy projects:
  --
  -- * itgen: all available translations in the Invantive Producer product family.
  -- * itgen_cloud: a small sub-set with maybe 150K words for various small products.
  --
  -- First translate everything in itgen_cloud project. After that merge the changes into itgen,
  -- add some more products and over time the itgen-project completes.
  --
  for r
  in
  ( select sorting_order
    ,      name
    ,      api_key
    ,      resource_code_regex
    ,      upload_to_studio
    from   ( select 10 sorting_order
             ,      'itgen_cloud' name
             ,      'secret' api_key
             ,      '^itgen_(cloud|bridge|product|web)' resource_code_regex
             ,      false upload_to_studio
             union all
             select 20 sorting_order
             ,      'itgen' name
             ,      'secret' api_key
             ,      '^itgen' resource_code_regex
             ,      true upload_to_studio
           )
    order
    by     sorting_order
    ,      name
  )
  loop
    l_localazy_project_id := get_localazy_project_id(r.name, r.api_key);
    invantive_studio_to_localazy(l_localazy_project_id, r.api_key, r.name, r.resource_code_regex, g_studio_application_code, 'en');
    --
    if r.upload_to_studio
    then
      localazy_to_invantive_studio_prepare(l_localazy_project_id, r.api_key, g_studio_application_code);
      localazy_to_invantive_studio_upload;
    end if;
  end loop;
end;