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?
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).
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
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event