How to restore Child issues links after disabling Portfolio?

[Jeff Turner]

Portfolio lets one group tasks into Initiatives. This hierarchy is visible in the Portfolio Roadmap page, but also when you view the Initiative issue - one sees a 'Child issues' section with the initiative's tasks.

Let's say a company migrates off Portfolio. They disable the plugin, or let its license lapse. What happens to those 'Child issues' links visible on the Initiative issues?

Sadly, they disappear. Despite looking like regular Jira issue links, they are not.

Is there a way to get those Initiative-Task links back?

[Jeff Turner]

(ask-and-answering my own question, SO-style, so it's documented)

Issue links created by Portfolio aren't stored in the issuelink database table as usual. Instead, they are stored as JSON in the entity_property table.

With Postgres, you can get a list of child-parent ID links with this SQL:

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';

The missing issuelink records can be created from this JSON as follows:

-- 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;

Full gory details on this blog post.