| Excerpt | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
JIRA 7 bug
Broken portlets can be fixed individually by editing them and setting
However only the dashboard owner can do this, and it's not exactly intuitive. This page describes a SQL fix to migrate all Created vs Resolved portlets to the new preference format. |
Table of Contents
| Table of Contents |
|---|
I haven't yet upgraded. Will I be affected?
...
In JIRA 7.x, Atlassian reimplemented the Created vs Resolved gadget, and changed the parameters. Here is a before/after diff on the params for a filter-using portlet (see Using database diffs to see what JIRA is doing for details on the technique):
and a project-using portlet:
...
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration))
SELECT * From relevantprefs;
┌──────────────────────┬───────────────────┬──────────────┐
│ portletconfiguration │ projectorfilterid │ iscumulative │
├──────────────────────┼───────────────────┼──────────────┤
│ 10801 │ filter-10601 │ true │
│ 10803 │ filter-10601 │ false │
│ 10900 │ project-10800 │ true │
└──────────────────────┴───────────────────┴──────────────┘
(3 rows) |
The same, but breaking using regexp_replace() to break projectorfilterid into its constituent parts:
...
| Code Block |
|---|
WITH gadgetprefs AS (select gup.* from gadgetuserpreference gup JOIN portletconfiguration pc ON gup.portletconfiguration=pc.id WHERE pc.gadget_xml~'createdvsresolved-gadget.xml'),
filterprefs AS (select * from gadgetprefs WHERE userprefkey='projectOrFilterId'),
cumulativeprefs AS (select * from gadgetprefs WHERE userprefkey='isCumulative'),
relevantprefs AS (select portletconfiguration, filterprefs.userprefvalue AS projectOrFilterId, cumulativeprefs.userprefvalue AS isCumulative FROM filterprefs JOIN cumulativeprefs USING (portletconfiguration)),
prefs AS (select portletconfiguration, projectOrFilterId, regexp_replace(projectOrFilterId, '-\d+', '') AS type, regexp_replace(projectOrFilterId, '(filter|project)-', '') AS id, iscumulative FROM relevantprefs)
SELECT *, CASE t.userprefkey
WHEN 'operation' THEN (CASE prefs.isCumulative WHEN 'true' THEN 'cumulative' ELSE 'count' END)
WHEN 'id' THEN prefs.id
WHEN 'name' THEN CASE type WHEN 'project' THEN (SELECT pname FROM project WHERE id=prefs.id::integer) WHEN 'filter' THEN prefs.projectOrFilterId END
WHEN 'type' THEN prefs.type
END AS userprefvalue
FROM prefs,
(VALUES ('name'), ('operation'), ('id'), ('type')) AS t(userprefkey);
┌──────────────────────┬───────────────────┬─────────┬───────┬──────────────┬─────────────┬───────────────┐
│ portletconfiguration │ projectorfilterid │ type │ id │ iscumulative │ userprefkey │ userprefvalue │
├──────────────────────┼───────────────────┼─────────┼───────┼──────────────┼─────────────┼───────────────┤
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ name │ filter-10601 │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ operation │ cumulative │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ id │ 10601 │
│ 10801 │ filter-10601 │ filter │ 10601 │ true │ type │ filter │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ name │ filter-10601 │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ operation │ count │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ id │ 10601 │
│ 10803 │ filter-10601 │ filter │ 10601 │ false │ type │ filter │
│ 10900 │ project-10800 │ project │ 10800 │ true │ name │ CrowdFlower │
│ 10900 │ project-10800 │ project │ 10800 │ true │ operation │ cumulative │
│ 10900 │ project-10800 │ project │ 10800 │ true │ id │ 10800 │
│ 10900 │ project-10800 │ project │ 10800 │ true │ type │ project │
└──────────────────────┴───────────────────┴─────────┴───────┴──────────────┴─────────────┴───────────────┘
(12 rows) |
...
Inserting data: dealing with IDs
...
| Code Block |
|---|
UPDATE sequence_value_item SET seq_id = (SELECT ceil(max(id)/100)*100 FROM gadgetuserpreference) WHERE seq_name='GadgetUserPreference' RETURNING seq_id; ┌────────┐ │ seq_id │ ├────────┤ │ 12800 │ └────────┘ (1 row) |
Shameless Plug
My business, Red Radish Consulting, offers a quarterly JIRA/Confluence upgrade service. The fix described here was derived on-the-fly for a client. Save yourself the hassle of dealing with exciting bugs like this, and contact us for a quote!