-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathupdate_languages_translations_v1-2.sql
223 lines (199 loc) · 7.66 KB
/
update_languages_translations_v1-2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
/*
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.2', 'Add get_group_translations', _component := 'languages_translations');
/***
* ████████╗██████╗ █████╗ ███╗ ██╗███████╗ ███████╗██╗ ██╗███╗ ██╗ ██████╗███████╗
* ╚══██╔══╝██╔══██╗██╔══██╗████╗ ██║██╔════╝ ██╔════╝██║ ██║████╗ ██║██╔════╝██╔════╝
* ██║ ██████╔╝███████║██╔██╗ ██║███████╗ █████╗ ██║ ██║██╔██╗ ██║██║ ███████╗
* ██║ ██╔══██╗██╔══██║██║╚██╗██║╚════██║ ██╔══╝ ██║ ██║██║╚██╗██║██║ ╚════██║
* ██║ ██║ ██║██║ ██║██║ ╚████║███████║ ██║ ╚██████╔╝██║ ╚████║╚██████╗███████║
* ╚═╝ ╚═╝ ╚═╝╚═╝ ╚═╝╚═╝ ╚═══╝╚══════╝ ╚═╝ ╚═════╝ ╚═╝ ╚═══╝ ╚═════╝╚══════╝
*
*/
-- drop function public.get_group_translations(_group_code text)
create or replace function public.get_group_translations(_language_code text, _group_code text)
returns table
(
__translations jsonb
)
stable
language sql
as
$$
select jsonb_object_agg(data_object_code, value)
from public.translation
where language_code = _language_code
and data_group = _group_code
group by data_group
;
$$;
create or replace function public.create_translation(_created_by text, _user_id int, _language_code text, _data_group text,
_data_object_code text default null, _data_object_id bigint default null,
_value text default null,
_tenant_id int default 1)
returns setof public.translation
language plpgsql
as
$$
declare
__last_id int;
begin
perform
auth.has_permission(_user_id, 'translations.create_translation', _tenant_id);
insert into public.translation (created_by, tenant_id, language_code, data_group, data_object_code, data_object_id, value)
values (_created_by, _tenant_id, _language_code, _data_group, _data_object_code, _data_object_id, _value)
returning translation_id
into __last_id;
perform add_journal_msg(_created_by
, _user_id
, format('Translation (data group: %s, data id/code: %s) created by user: %s'
, _data_group, coalesce(_data_object_id::text, _data_object_code), _created_by)
, 'translation', __last_id
, _data_object_code := _data_object_code
, _event_id := 60021
, _tenant_id := _tenant_id);
return query
select * from translation where translation_id = __last_id;
end;
$$;
create or replace function update_translation(_modified_by text, _user_id int, _translation_id int,
_value text, _tenant_id int default 1)
returns setof public.translation
language plpgsql
as
$$
begin
perform
auth.has_permission(_user_id, 'translations.update_translation', _tenant_id);
return query
update public.translation set
modified = now(),
modified_by = _modified_by,
value = _value
where translation_id = _translation_id and tenant_id = _tenant_id
returning *;
perform add_journal_msg(_modified_by
, _user_id
, format('Translation updated by user: %s'
, _modified_by)
, 'translation'
, _data_object_id := _translation_id
, _event_id := 60022, _tenant_id := _tenant_id);
end;
$$;
create or replace function delete_translation(_deleted_by text, _user_id int, _translation_id int,
_tenant_id int default 1)
returns setof public.translation
language plpgsql
as
$$
begin
perform
auth.has_permission(_user_id, 'translations.delete_translation', _tenant_id);
return query
delete from public.translation
where translation_id = _translation_id and tenant_id = _tenant_id
returning *;
perform add_journal_msg(_deleted_by
, _user_id
, format('Translation deleted by user: %s'
, _deleted_by)
, _tenant_id
, 'translation'
, _data_object_id := _translation_id
, _event_id := 60023);
end;
$$;
create or replace function copy_translations(_created_by text, _user_id bigint, _from_language_code text,
_to_language_code text,
_from_tenant_id int default 1,
_to_tenant_id int default 1, _overwrite bool default false,
_data_group text default null)
returns table
(
__operation text,
__rows_count bigint
)
language plpgsql
as
$$
begin
perform
auth.has_permission(_user_id, 'translations.create_translation', _to_tenant_id);
return query
with updated_rows as materialized (
update public.translation
set modified = now(),
modified_by = _created_by
from (select data_group, data_object_code, data_object_id, value
from translation st
where st.tenant_id = _from_tenant_id
and st.language_code = _from_language_code
and (_data_group is null or data_group = _data_group)) as source
where translation.tenant_id = _to_tenant_id
and translation.data_group = source.data_group
and translation.data_object_code = source.data_object_code
and translation.data_object_id = source.data_object_id
returning translation_id)
, new_rows as materialized (
insert
into public.translation (created_by, modified_by, tenant_id, language_code, data_group, data_object_code,
data_object_id, value)
select _created_by
, _created_by
, _to_tenant_id
, _to_language_code
, t.data_group
, t.data_object_code
, t.data_object_id
, t.value
from public.translation t
left join public.translation dt on t.language_code = dt.language_code and
t.data_group = dt.data_group and
t.data_object_code = dt.data_object_code and
t.data_object_id = dt.data_object_id
where t.tenant_id = _from_tenant_id
and t.language_code = _from_language_code
and (_data_group is null or t.data_group = _data_group)
and dt.language_code is null
returning translation_id)
select 'updated', count(*)
from updated_rows
union all
select 'created', count(*)
from new_rows
order by 1;
end;
$$;
create or replace function const.delete_language(_deleted_by text
, _user_id bigint
, _language_code text
, _tenant_id int default 1)
returns table
(
__code text
)
language plpgsql
as
$$
begin
perform auth.has_permission( _user_id, 'languages.delete_language',_tenant_id);
return query
delete from const.language
where code = _language_code and tenant_id = _tenant_id
returning code;
perform add_journal_msg(_deleted_by
, _user_id
, format('Language (code: %s) deleted by user: %s'
, _language_code, _deleted_by)
, 'language'
, _data_object_code := _language_code
, _event_id := 60013
, _tenant_id := _tenant_id);
end
$$;
select *
from stop_version_update('1.2', _component := 'languages_translations');