How to retrieve statuses translations from database ?

[Ahmadi Islem]

Hello, 

I'm looking for an sql query to retrieve statuses and their translations from database. 

The "issuestatus" table doesn't have the translation column ! Any recommendation? 

Regards, 

[Ahmadi Islem · Accepted solution]

Hi, 

Just because it might help someone else. This sql query helped me to get all the translated jira statuses : 

SELECT s.pname, pe.PROPERTY_KEY, ps.propertyvalue
FROM issuestatus s, propertyentry pe, propertystring ps
WHERE pe.ENTITY_NAME = 'Status'
AND pe.ID = ps.ID
AND s.id = pe.ENTITY_ID

Regards, 

[Tuncay Senturk _Snapbytes_ · Accepted solution]

Hi @Ahmadi Islem 

It is a bit tricky because these translations are not stored in the issuestatus table. They are stored in a properties table, where they are associated with language-specific keys. Please take it with a pinch of salt but as far as I know, the translations are usually stored in the propertytext or propertyentry or propertystring tables, depending on the Jira version and database structure.

The property key for translations usually follows a specific pattern, such as jira.i18n.title or similar. I'd suggest using the following SQL as a starting point. However, please be aware that this query is not a complete, executable statement; rather it should be used as a foundation to build upon.

SELECT
  iss.pname AS status_name, prop.key, text.propertyvalue AS translation
FROM
  issuestatus iss
JOIN
  propertyentry prop ON prop.entity_id = iss.id
JOIN
  propertytext text ON prop.id = text.id
WHERE
  prop.entity_name = 'jira.issue.status' AND
  prop.property_key LIKE 'jira.i18n.title%'

[Ahmadi Islem]

Hi @Tuncay Senturk _Snapbytes_ , 

Thank you for your suggestion. 

Regards, 

[Tuncay Senturk _Snapbytes_]

You're very welcome, I hope it helps!