How to retrieve statuses translations from database ?

Question

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,

Tuncay Senturk _Snapbytes_ · Accepted Answer

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 · Accepted Answer

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,