Undelete translationgists used as names of existing dictionaries or perspectives

[vmonakhov]

Some perspectives have lost their names and even can't be opened from /dictionaries_all. This happened due to not correct type of translationgists related to the perspectives. So those translationgists have 'Service' type instead of 'Perspective' and were deleted together with perspective itself but other eponymous perspectives became unnamed.

The task is mainly described in https://github.com/ispras/lingvodoc-react/issues/788

An SQL query to get ids of translationgists to be restored:

select translationgist.client_id, translationgist.object_id, array_agg(translationatom.content)
from translationgist
left join dictionaryperspective on
translationgist.client_id=dictionaryperspective.translation_gist_client_id and
translationgist.object_id=dictionaryperspective.translation_gist_object_id and
dictionaryperspective.marked_for_deletion='false'
left join dictionary on
translationgist.client_id=dictionary.translation_gist_client_id and
translationgist.object_id=dictionary.translation_gist_object_id and
dictionary.marked_for_deletion='false'
join translationatom on
translationgist.client_id=translationatom.parent_client_id and
translationgist.object_id=translationatom.parent_object_id
where translationgist.marked_for_deletion='true' and
(dictionary.translation_gist_client_id is not NULL or
 dictionaryperspective.translation_gist_client_id is not NULL)
group by translationgist.client_id, translationgist.object_id;

An API was created to undelete translationgist with related translationatoms by the gist's id. User must have "delete translations" permissions for calling this API.

Request example (bash script with args: ):

#!/bin/bash
tkt='123456789'
usr='9876'
curl 'http://watson.local:6543/graphql' -H 'Content-Type: application/json' -H "Cookie: locale_id=2; auth_tkt=$tkt!userid_type:int; client_id=$usr" --data-raw '{ "operationName": "UndeleteTranslationGist", "variables":{"id":['$1','$2']}, "query": "mutation UndeleteTranslationGist($id: LingvodocID!) { undelete_translationgist(id: $id) { translationgist {id}, triumph }}"}'

[vmonakhov]

Now translationgists for perspectives are created with right gist_type. After the deleted gists were restored, the problem is resolved.

[vmonakhov]

Tested, can be closed.

[myrix]

Translationgist query with better translation grouping:

with tg_cte as (
select translationgist.client_id, translationgist.object_id
from translationgist
left join dictionaryperspective on
translationgist.client_id=dictionaryperspective.translation_gist_client_id and
translationgist.object_id=dictionaryperspective.translation_gist_object_id and
dictionaryperspective.marked_for_deletion='false'
left join dictionary on
translationgist.client_id=dictionary.translation_gist_client_id and
translationgist.object_id=dictionary.translation_gist_object_id and
dictionary.marked_for_deletion='false'
where translationgist.marked_for_deletion='true' and
(dictionary.translation_gist_client_id is not NULL or
 dictionaryperspective.translation_gist_client_id is not NULL)
group by translationgist.client_id, translationgist.object_id)

select tg_cte.client_id, tg_cte.object_id, array_agg(translationatom.content)
from tg_cte
join translationatom on
tg_cte.client_id=translationatom.parent_client_id and
tg_cte.object_id=translationatom.parent_object_id
group by tg_cte.client_id, tg_cte.object_id;

Result before undeletion fix:

 client_id | object_id |                                           array_agg                                            
-----------+-----------+------------------------------------------------------------------------------------------------
         1 |        26 | {Paradigms,Paradigmat,Парадигмы}
         1 |       159 | {"Лексические входы","Leksikaalinen Merkinnät","Lexical Entries"}
        66 |      2043 | {Морфология,Morphology}
        66 |      2045 | {"Morphological Paradigms"}
       425 |         2 | {"Словарь волжского диалекта марийского языка","Dictionary of Volga dialect of Mari language"}
(5 rows)

Fixing Service -> Perspective gist type for Morphology perspective translations:

select * from translationgist where client_id = 66 and object_id in (2043, 2045);
update translationgist set type = 'Perspective' where client_id = 66 and object_id in (2043, 2045);