Page tree
Skip to end of metadata
Go to start of metadata


Jira has a pretty limited support for breaking issues down into smaller components. Rather than support arbitrary hierarchies, if you want more levels of hierarchy, you need to buy plugins:


Jira CoreJira SoftwareJira Portfolio
Levels of structure

Issues

Sub-tasks

Epics

Issues (Stories)

Sub-tasks

Programs

Initiatives

Epics

Issues

Sub-tasks

Scenario: Jira Portfolio comes and goes

Let's imagine that, feeling the need for higher-level structure, your company forked out for Jira Portfolio and grouped Epics into Initiatives. For instance, a bunch of IT-related Epics are grouped into a Refine IT Processes and Procedures Initiative:

An 'Initiative' is modelled in Jira as just another issue type, so we can view our Initiative as an issue, and see its child Epics:

Then corporate sloth reasserts itself, and the elaborate high-level structures fall into disrepair. Eventually the license for Portfolio lapses, or Portfolio is disabled.

But that's okay, because at least we still have our Jira Initiatives with linked Epics, right? After Portfolio's license lapsed, lets look again at our Initiative issue:

Oops. Initiatives lose their Child issues section as soon as Portfolio isn't validly licensed.

Where did our Initiative's child links go?

All the initial evidence suggests that the Initiative's Child links should  have been preserved:

  • the 'Child issues' section looks very similar to a standard Issue links section on issues
  • there is even a special Portfolio link type in the database

    jira=> select * From issuelinktype where pstyle ='jira_jpos_parent_child';
    ┌───────┬───────────────────┬─────────────┬──────────────┬────────────────────────┐
    │  id   │     linkname      │   inward    │   outward    │         pstyle         │
    ├───────┼───────────────────┼─────────────┼──────────────┼────────────────────────┤
    │ 10830 │ Parent-Child Link │ is child of │ is parent of │ jira_jpos_parent_child │
    └───────┴───────────────────┴─────────────┴──────────────┴────────────────────────┘
    (1 row)
  • and sometimes you will actually see issuelinks of this Portfolio link type:

    jira=> select count(*) from issuelink where linktype in (select id from issuelinktype where pstyle='jira_jpos_parent_child');
    ┌───────┐
    │ count │
    ├───────┤
    │    27 │
    └───────┘
    (1 row)
    
    

..despite all this, the Child issues section is not done with the standard issuelink  table. I suspect that it was at first, but then the developers hit some limitation and moved on.

Querying JSON in the entity_property table

The Child issues  relations are in fact stored in the entity_property  table. Specifically, as entity_property  entries on the child issues, e.g. IT-115:

jira=> select * from entity_property where entity_name='IssueProperty' and entity_id=(select jiraissue.id from jiraissue JOIN project ON project.id=jiraissue.project WHERE project.pkey='IT' and issuenum=115);
┌─[ RECORD 1 ]─┬───────────────────────────────────────┐
│ id           │ 26614                                 │
│ entity_name  │ IssueProperty                         │
│ entity_id    │ 207404                                │
│ property_key │ jpo-issue-properties                  │
│ created      │ 2017-06-27 16:31:25.103+00            │
│ updated      │ 2017-06-27 18:50:42.744+00            │
│ json_value   │ {"team_id":"26","parent_id":"207453"} │
└──────────────┴───────────────────────────────────────┘

To find all parent-child relations, we can use Postgres' JSON support:

jira=> select entity_id AS child_id, (json_value::json->>'parent_id')::numeric AS parent_id from entity_property where entity_name='IssueProperty' and property_key='jpo-issue-properties' limit 10;
┌──────────┬───────────┐
│ child_id │ parent_id │
├──────────┼───────────┤
│   271643 │         ␀ │
│   271621 │         ␀ │
│   271611 │         ␀ │
│   270022 │    246074 │
│   270021 │    270020 │
│   246076 │    270020 │
│   267996 │    267715 │
│   267931 │    261600 │
│   267894 │    267737 │
│   267807 │    267731 │
└──────────┴───────────┘
(10 rows)

This information was inferred by the eternally useful technique of using database diffs to see what JIRA is doing.

Restoring the links

So we have found where Portfolio stores the Initiative's links: in entity_property . That is nice, but we do actually want issue links back in issuelink  where they belong. Let's fix this with some SQL.

Database surgery ahead! Make sure you understand what is going on, and take a backup beforehand.


First, we need a link type. Well, we already have one – it's just invisible because of that custom pstyle :

jira=> select * From issuelinktype where pstyle ='jira_jpos_parent_child';
┌───────┬───────────────────┬─────────────┬──────────────┬────────────────────────┐
│  id   │     linkname      │   inward    │   outward    │         pstyle         │
├───────┼───────────────────┼─────────────┼──────────────┼────────────────────────┤
│ 10830 │ Parent-Child Link │ is child of │ is parent of │ jira_jpos_parent_child │
└───────┴───────────────────┴─────────────┴──────────────┴────────────────────────┘

To make this link type 'normal' just remove the pstyle:

jira=> update issuelinktype set pstyle=null where pstyle='jira_jpos_parent_child';

Next we need to create a bunch of issuelink  records, one for each of those parent relations stored in JSON in entity_property

The issuelink  table looks like this:

jira=> select * From issuelink limit 5;
┌────────┬──────────┬────────┬─────────────┬──────────┐
│   id   │ linktype │ source │ destination │ sequence │
├────────┼──────────┼────────┼─────────────┼──────────┤
│  10050 │    10010 │  14415 │       14414 │        ␀ │
│  10051 │    10010 │  14354 │       11616 │        ␀ │
│  10052 │    10010 │  14418 │       14302 │        ␀ │
│ 149751 │    10230 │ 188337 │      188267 │        ␀ │
│  10054 │    10010 │  14443 │       14440 │        ␀ │
└────────┴──────────┴────────┴─────────────┴──────────┘
(5 rows)

So we just need to insert the parent-child IDs we obtained from entity_property  above.

Now for a few tedious details. You'll note the id  field - we'll need to create our new records with monotonically increasing values.  Furthermore, Jira's crusty database library Ofbiz tracks the maximum ID of each other table's ID in its sequence_value_item  table, to the nearest 10:

jira=> select * from sequence_value_item where seq_name='IssueLink';
┌───────────┬────────┐
│ seq_name  │ seq_id │
├───────────┼────────┤
│ IssueLink │ 244210 │
└───────────┴────────┘
(1 row)

jira=> select max(id) from issuelink;
┌────────┐
│  max   │
├────────┤
│ 244178 │
└────────┘
(1 row)

So our job is to calculate new IDs, then update sequence_value_item  afterwards.

Here is some SQL that does the job, including fixing the issuelinktype  if you hadn't already done so above.

-- https://www.redradishtech.com/pages/viewpage.action?pageId=14483457
begin;
create temp sequence issuelink_seq start with 99999; -- https://stackoverflow.com/questions/37057643/postgresql-starting-a-sequence-at-maxthe-column1                                                                                        
select setval('issuelink_seq', (select max(id::bigint)+1 from issuelink));
WITH jpolinks AS (
    select entity_id AS child_id, (json_value::json->>'parent_id')::numeric AS parent_id from entity_property where entity_name='IssueProperty' and property_key='jpo-issue-properties')                                                     
, newissuelinks AS (
  select nextval('issuelink_seq') AS id
        , jpolinktype.id AS linktype
        , parent_id AS source
        , child_id as destination
        ,  null::numeric AS sequence
  from jpolinks
    CROSS JOIN (select id from issuelinktype where linkname='Parent-Child Link') jpolinktype
  where parent_id is not null -- the parent_id JSON is sometimes empty
    and not exists (select * From issuelink where source=parent_id and destination=child_id and linktype=jpolinktype.id) -- don't double-insert                                                                                              
)
insert into issuelink select * from newissuelinks;
update sequence_value_item set seq_id=nextval('issuelink_seq') where seq_name='IssueLink';
update issuelinktype set pstyle=null where pstyle='jira_jpos_parent_child';
commit;

After running the above, you will have to restart Jira for the changes to take effect.

After the restart, Initiative issues will have a new is parent of link section, duplicating the links seen in the Portfolio-generated Child issues  section:

then when you disable Portfolio, the Child issues  section disappears, and you are where you want to be.