Checking that the Jira mappings are correct

When performing the export/import to a new Jira instance, we match the “issue id” by default (in the form “10001”). That means we don’t check that the “issue keys” are correct (in the form “JIRA-1”), at least up to Requirement Yogi 3.8.

Therefore, the following page provides some SQL queries to cross-check which IDs are at risk of being incorrect.

Screen Shot 2024-02-27 at 14.57.59.png

Accessing the underlying table in the database

The mapping information from the screen above, is an exact representation of the contents of the table AO_42D05A_DBBACKUPMAPPING (42D05A is the prefix for RY). Example:

SELECT ID,
    BACKUPKEY,
    KEY as "ORIGINAL_ISSUE_ID",
    ISSUEKEY as "ORIGINAL_ISSUE_KEY",
    TITLE as "ORIGINAL_ISSUE_SUMMARY",
    STATUS,
    DESTINATIONVALUE,
    DESTINATIONHUMANVALUE
FROM AO_42D05A_DBBACKUPMAPPING
WHERE TYPE = 'ISSUE'
AND BACKUPKEY = 'KEY1';

It returns all mappings of type “ISSUE”, the original “issue id”, key, and title, and their destination “issue id”:

Now, the automatically-mapped “DESTINATIONVALUE” may be wrong. You can check that those IDs match the original issue keys:

To quickly see which mappings are suspicious, we can filter on those which don’t have the same ID in the new system:

SELECT M.ID,
    BACKUPKEY,
    KEY as ORIGINAL_ISSUE_ID,
    ISSUEKEY as ORIGINAL_ISSUE_KEY,
    TITLE as ORIGINAL_ISSUE_SUMMARY,
    STATUS,
    DESTINATIONVALUE,
    DESTINATIONHUMANVALUE,
    P.PKEY || '-' || J.ISSUENUM as REAL_KEY_FOR_DESTINATION_ISSUEID,
    J.SUMMARY as SUMMARY_FOR_REAL_KEY
FROM AO_42D05A_DBBACKUPMAPPING M
LEFT JOIN JIRAISSUE J ON J.ID = DESTINATIONVALUE
LEFT JOIN PROJECT P ON P.ID = J.PROJECT
WHERE TYPE = 'ISSUE'
AND BACKUPKEY = 'KEY1'
AND (
    (P.PKEY || '-' || J.ISSUENUM) <> M.ISSUEKEY -- Issue key is different
    OR J.SUMMARY <> M.TITLE -- Issue title is different (depending on whether it was renamed)
    OR J.ID IS NULL -- Issue/project doesn't even exist
)
;

Screen Shot 2024-02-27 at 15.21.39.png

Fixing the data

I recommend to only modify lines which are suspect:

UPDATE AO_42D05A_DBBACKUPMAPPING M
SET
    DESTINATIONVALUE = (
        SELECT J.ID
        FROM JIRAISSUE J
        LEFT JOIN PROJECT P ON P.ID = J.PROJECT
        WHERE (P.PKEY || '-' || J.ISSUENUM) = M.ISSUEKEY
    ),
    STATUS = 'MAPPED_MANUAL', -- This is so that the UI displays a green mark for this record. It's just an information, nothing required.
    DESTINATIONHUMANVALUE = 'Modified using SQL' -- This is free text for your own information.
WHERE
    M.ID = 804 -- It is a good practice to only modify data which is incorrect, to ensure you are not doing mistakes
    AND TYPE = 'ISSUE'
;

In our current example, this query will search for an issue with the key JIRA-29, take its ‘issueid’ and put it in DESTINATIONVALUE.

If the issue id and key match, then the line will have disappeared from the results of the previous query.

Is it what your situation needs?

Nothing is certain. When modifying SQL, it is important to perform a backup before starting and understand the data that you are modifying.