Summary

The contents displayed on Advanced Roadmap Plans are not stored in the database. Rather, they are loaded using the issue source filters whenever the Plan is called.

These queries are here to help map and associate plans with issue sources and affected teams.

Queries for Issue Sources

This section shows the relationship of tables involved in mapping different issue sources to plans

The table AO_D9132D_ISSUE_SOURCE contains the mapping of issue sources for each plan. This query will list all the issue sources mapped per plan.

SELECT plan."TITLE" as "Plan Name", plansrc."SOURCE_TYPE" as "Source Type", plansrc."SOURCE_VALUE" as "Source ID"
FROM "AO_D9132D_ISSUE_SOURCE" plansrc, "AO_D9132D_PLAN" plan
WHERE plansrc."PLAN_ID" = plan."ID";

There are three possible issue sources for Plans. These queries cross-reference the issue source based on the IDs from the plan's issue source.

• Board

  SELECT board."NAME" as "Board Name", board."OWNER_USER_NAME" as "Board Owner", filter.filtername as "Filter name", filter.authorname as "Filter Owner", filter.reqcontent as "Filter Query"
  FROM "AO_60DB71_RAPIDVIEW" board, searchrequest filter
  WHERE filter.id = board."SAVED_FILTER_ID" AND board."ID" IN (<board IDs from query above>);

• Filter

  SELECT filter.id as "Filter ID", filter.filtername as "Filter name", filter.authorname as "Filter Owner", filter.reqcontent as "Filter Query"
  FROM searchrequest filter
  WHERE filter.id in (<filter ids from query above>);

• Project

  SELECT p.id as "Project ID", p.pname as "Project Name", p.lead as "Project Lead"
  FROM project p
  WHERE p.id IN (<project IDs from query above>);

Queries for Teams

This section shows the relationship of tables involved in mapping teams to parent issues or plans

The table entity_property contains the mapping of teams to parent issues. The KB on viewing an issue from the database using SQL contains the query on how to get the jiraissue id of the parent issue.

SELECT * FROM entity_property WHERE json_value like '%team_id%' and entity_id in <id of parent issue in jiraissue>;

To get the list of teams mapped to the parent issue, use the query below:

SELECT id, title FROM "AO_82B313_TEAM" WHERE "ID" in (<team_ids from previous query separated by a comma>);

To get a list of all plans, that a specific team is used by you can use (replace <team_name>  appropriately)

SELECT pt."PLAN_ID", p."TITLE" as Plan_Name, t."ID" , t."SHAREABLE", t."TITLE" as Team_Name, t."ID" FROM "AO_82B313_TEAM" t 
JOIN "AO_D9132D_PLANTEAM" pt on t."ID" = pt."TEAM_ID" 
JOIN "AO_D9132D_PLAN" p on p."ID" =pt."PLAN_ID" 
WHERE t."TITLE" = '<team_name>';

Relations between Initiatives and Epics

Advanced Roadmaps (ARJ) stores the link in two separate ways - one in the issuelink table, and, the other in the entity_property table. Either link type is valid, and, the places they are utilized are not 1:1. Which is why the two tables are expected to not have the same count of entries.

The table entity_property contains links to parents of Epics and anything above its hierarchy, while issuelink contains links from Epics to Stories.

To get the corresponding Initiative or parent issue of an Epic in Advanced Roadmaps for Jira, we can use this query below. The KB on viewing an issue from the database using SQL contains the query on how to get the jiraissue id of the EPIC issue.

SELECT * FROM entity_property WHERE entity_name = 'IssueProperty' AND property_key = 'jpo-issue-properties' AND json_value LIKE '%parent_id%' AND entity_id = <jiraissue id of the EPIC issue>

Custom Fields in Plans

To check if a Jira custom field is associated with any plan you can check the table AO_D9132D_PLAN_CUSTOM_FIELD for the FIELD_ID.

SELECT * FROM "AO_D9132D_PLAN_CUSTOM_FIELD" WHERE "CUSTOM_FIELD_ID" = <fieldID>;

Querying for Advanced Roadmaps for Jira Custom Fields

Querying the custom fields for the Advanced Roadmaps for Jira plugin might be a challenge. That happens because their values aren't stored in the customfieldvalue table, as described in https://developer.atlassian.com/server/jira/platform/database-custom-fields/. Instead, they are stored as JSON values in the entity_property table.

For example, query to find the original_story_points custom field value

SELECT
    ep.entity_id AS issue_id,
    CAST((CAST(ep.json_value AS json) ->> 'original_story_points') AS float) AS original_story_points,
	TIMESTAMP WITH TIME ZONE 'epoch' + CAST(CAST(ep.json_value AS json) ->> 'baseline_start' AS bigint) * INTERVAL '1 millisecond' AS baseline_start,
	TIMESTAMP WITH TIME ZONE 'epoch' + CAST(CAST(ep.json_value AS json) ->> 'baseline_end' AS bigint) * INTERVAL '1 millisecond' AS baseline_end,	
    CONCAT(p.pkey, '-', i.issuenum) AS issue_key
FROM
    entity_property ep
	JOIN jiraissue i ON i.id = ep.entity_id 
	JOIN project p ON p.id = i.project
WHERE
    ep.property_key = 'jpo-issue-properties';

For other Advanced roadmaps custom fields:

• Team ID > replace 'original_story_point' with 'team_id'
• Parent link > replace 'original_story_point' with 'parent_id'

Reference: Querying for Advanced Roadmaps for Jira Custom Fields