update_languages_translations_v1-4.sql

/*
 GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=0&v=1&c=c&f=ANSI%20Shadow&t=STAGE%20FUNCS

 SUB GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=1&v=1&c=c&f=Banner3&t=permissions
*/

select *
from start_version_update('1.4', 'Added missing function for getting translations from database',
                          _component := 'languages_translations');

/***
 *    ██╗      █████╗ ███╗   ██╗ ██████╗ ██╗   ██╗ █████╗  ██████╗ ███████╗███████╗
 *    ██║     ██╔══██╗████╗  ██║██╔════╝ ██║   ██║██╔══██╗██╔════╝ ██╔════╝██╔════╝
 *    ██║     ███████║██╔██╗ ██║██║  ███╗██║   ██║███████║██║  ███╗█████╗  ███████╗
 *    ██║     ██╔══██║██║╚██╗██║██║   ██║██║   ██║██╔══██║██║   ██║██╔══╝  ╚════██║
 *    ███████╗██║  ██║██║ ╚████║╚██████╔╝╚██████╔╝██║  ██║╚██████╔╝███████╗███████║
 *    ╚══════╝╚═╝  ╚═╝╚═╝  ╚═══╝ ╚═════╝  ╚═════╝ ╚═╝  ╚═╝ ╚═════╝ ╚══════╝╚══════╝
 *
 */

	/***
 *    ████████╗ █████╗ ██████╗ ██╗     ███████╗███████╗
 *    ╚══██╔══╝██╔══██╗██╔══██╗██║     ██╔════╝██╔════╝
 *       ██║   ███████║██████╔╝██║     █████╗  ███████╗
 *       ██║   ██╔══██║██╔══██╗██║     ██╔══╝  ╚════██║
 *       ██║   ██║  ██║██████╔╝███████╗███████╗███████║
 *       ╚═╝   ╚═╝  ╚═╝╚═════╝ ╚══════╝╚══════╝╚══════╝
 *
 */

-- recreated translation unique indexes to accommodate per-tenant translations
drop index uq_translation_code;
create unique index uq_translation_code on public.translation (tenant_id, language_code, data_group, data_object_code);
drop index uq_translation_obj_id;
create unique index uq_translation_obj_id on public.translation (tenant_id, language_code, data_group, data_object_id);

 /***
 *    ███████╗██╗   ██╗███╗   ██╗ ██████╗███████╗
 *    ██╔════╝██║   ██║████╗  ██║██╔════╝██╔════╝
 *    █████╗  ██║   ██║██╔██╗ ██║██║     ███████╗
 *    ██╔══╝  ██║   ██║██║╚██╗██║██║     ╚════██║
 *    ██║     ╚██████╔╝██║ ╚████║╚██████╗███████║
 *    ╚═╝      ╚═════╝ ╚═╝  ╚═══╝ ╚═════╝╚══════╝
 *
 */

drop function const.get_language(_language_code text);
create function const.get_language(
	_display_language_code text
, _language_code text)
	returns table
	        (
		        __code                              text,
		        __value                             text,
		        __title                             text,
		        __tenant_id                         integer,
		        __is_frontend_language              boolean,
		        __is_backend_language               boolean,
		        __is_communication_language         boolean,
		        __frontend_logical_order            integer,
		        __backend_logical_order             integer,
		        __communication_logical_order       integer,
		        __is_default_frontend_language      boolean,
		        __is_default_backend_language       boolean,
		        __is_default_communication_language boolean,
		        __custom_data                       jsonb
	        )
	language sql
as
$$
select l.code
		 , l.value
		 , coalesce(t.value, l.value)
		 , l.tenant_id
		 , l.is_frontend_language
		 , l.is_backend_language
		 , l.is_communication_language
		 , l.frontend_logical_order
		 , l.backend_logical_order
		 , l.communication_logical_order
		 , l.is_default_frontend_language
		 , l.is_default_backend_language
		 , l.is_default_communication_language
		 , l.custom_data
from const.language l
	left join public.translation t
	          on t.language_code = _display_language_code and data_group = 'language' and data_object_code = l.code
where l.code = _language_code;
$$;

create or replace function public.export_translations(_data_group text)
	returns table
	        (
		        __tenant_id    int,
		        __lang_code    text,
		        __translations jsonb,
		        __count        bigint
	        )
	stable
	language sql
as
$$
select t.tenant_id
		 , t.language_code
		 , jsonb_object_agg(t.data_object_code, t.value)
		 , count(t.translation_id)
from public.translation t
where t.data_group = _data_group
group by t.tenant_id, t.language_code
$$;

drop function public.get_group_translations(
	_language_code text
, _group_code text);
create or replace function public.get_group_translations(
	_language_code text
, _group_code text
, _tenant_id int)
	returns table
	        (
		        __translations jsonb
	        )
	stable
	language sql
as
$$
select jsonb_object_agg(data_object_code, value)
from public.translation
where tenant_id = _tenant_id
	and language_code = _language_code
	and data_group = _group_code
group by data_group
	;
$$;

select *
from stop_version_update('1.4', _component := 'languages_translations');