JIRA importers from Mantis, Why is jiraJOIN tables jiraissue and customfieldvalue

[a]

I have imported issues from Mantis using the Importers plugin. Now I am trying to do some cleanup using SQL on the issue database. The issue I am currently working on is, that the Mantis categories have been imported Components and all Issues were imported as Bugs. I now want to write a little update script that updates the issue types based on components and for some individual issues based on the external issue IDs. I am starting by joining the 'jiraissue' and 'customfieldvalue' on ID where the External issue ID is the External issue ID. This is my query:

SELECT j.ID AS JiraID, f.ID AS JiraID2, CONVERT(int, f.STRINGVALUE) AS MantisID
FROM jiraissue j RIGHT OUTER JOIN customfieldvalue f ON j.ID = f.ID
WHERE 
  CUSTOMFIELD = 10317

However, it turns out that there are entries in 'jiraissue' only for very few entries in the customfieldvalue table. Am I joining with the wrong table?

[Nic Brough -Adaptavist- · Accepted solution]

I think you're joining the correct table. I'm no DBA, but to do "get custom field value from an issue", the most primitive statement is

select * from customfieldvalue where issue = <issue id from jiraissue table> and customfield = <customfield id from customfield table>

There should be no customfieldvalue entries without a matching jiraissue record - the whole point of the customfieldvalue table is that it holds data for the issues, so if the issues are missing, the implication is broken data! (Of course, it's fine to have jiraissue records with no customfieldvalues).

[a]

This is an example update statement:

/** Issues that ought be Improvements **/
UPDATE [jira_tnag_prod].[dbo].[jiraissue]
SET issuetype = 4 -- improvement
WHERE ID IN ( 
  SELECT j.ID AS JiraID
  FROM jiraissue j RIGHT OUTER JOIN customfieldvalue f ON j.ID = f.ISSUE
  WHERE
    CUSTOMFIELD = 10317 AND                  -- custom field External issue ID  
    CONVERT(int, f.STRINGVALUE) IN ( 1, 2, 3 ) );

However, the actual migration took many many more update statements for correcting various issues that were not imported as intended. This included:

* translating resolutions

* translating components to issue types (this example)

* translating the values of one of our mantis attributes to componentes

* mapping severities to priorities

* setting values for some newly introduced attributes for tracking system test / review state

* translateing free text versions to reified versions in JIRA

...

[Shinjani Gaur]

Thanks for the pointer. I haven't got it to work for me yet but it definitely gives me a starting point.

[Shinjani Gaur]

Can you please share the exact query you used? I am looking for a similar solution.

[a]

Oh, I found my error, I just joined with the wrong ID, should have been customfieldvalue.ISSUE, stupid me.