In this topic you will learn how you can connect to a cloud-platform API for which Invantive UniversalSQL 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 UniversalSQL is a programming language which real-time connects over 75 (cloud)platforms using familiar ANSI SQL statements. Invantive UniversalSQL provides a procedural option.
Invantive Studio is a CASE-tool for real-time datawarehousing and OLTP-applications with time travel on basis of Invantive UniversalSQL. 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 UniversalSQL 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;