Obtaining value of the Resolution field

[f***@atlassian.com]

Hello,
i would like to obtain an information (using sql statment in the query), on what statuses of the workflows the fileld Resolution is defined and what the value has been set to it.
I have investigated all the sql tables of the jira database on SQL Server and the only information i have finded out about relation Workflows-Resolution(values) is set in the table [jiraschema].[jiraworkflows] in column DESCRIPTOR (xml description).
Could You help me in that matter?
--
Post by jacek - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

select * from jiraissue join resolution on resolution.id = jiraissue.resolution;

That will list issues and their current resolutions (bear in mind that it can of course, be null).

However,

Post by f***@atlassian.com
on what statuses of the workflows the fileld Resolution is defined and what the value has been set to it

That is not actually relevant. Although it looks like it is linked, the Resolution field has absolutely no direct physical relationship with Status. Logically, they are linked by your configuration.

It is perfectly valid to define a workflow, field and screen setup so that an issue never has any resolution at all, even if it's got valid states of "resolved" and/or "closed". Or you can create a situation where an issue has a resolution as soon as you create it.
--
Post by broughn2 - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

Nic,
thx for answer.
I guess, information concerning configuration of workflow and post-function settings are saved in jira somehow.
For example if reporting workflow using jira functionality: Dashboard->Browse Projects->Workflow Report, we can see workflow draw and on the statuses where Resolution is set to FIXED the big black dot is presented. I attached the print screen with it.
I am affried that this information is retrived from xml parsing ([jiraschema].[jiraworkflows] in column DESCRIPTOR), but maybe sql database keep pure information (relation Workflow-Status-Post-Function) about it.

I will be very graceful for advise.

Regards
Jacek
--
Post by jacek - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

Post by f***@atlassian.com
maybe sql database keep pure information (relation Workflow-Status-Post-Function) about it.

Nope, that's what I already said. There is no relationship between them in the database.

I think Jamie had to use the xml to try to work out where the resolution *might* be set when he wrote the workflow diagram plugin. I'm guessing there are libraries available to help interpret the xml and work it out, and I'd guess at them being included in Jira. But it's not in the database structure.
--
Post by broughn2 - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

Nic,
Thank You v. much.

Jacek
--
Post by jacek - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

Hi there...

As Nic says, no relationship exists at all between status and resolution... a situation which causes continual problems and confusion at every client I have worked at.

The workflow plugin actually uses the workflow API functions, not the XML, but yes it works out on which actions the resolution is set or unset in order to try to show potential problems with workflows - like when you set it on one transition but forget to unset it on the reverse transition.

The information is in the database, but only as XML, ie the same xml you see when you download a workflow as XML. Therefore to get the same information from SQL would be very awkward... you'd need a DBMS capable of running xpath queries as well as SQL I would guess (and there are a few around that can do that).

cheers, jamie
--
Post by JamieEchlin - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

Ah, I thought it might be! I didn't want to post my guesswork without being entirely clear that I was making "educated" guesses.

Post by f***@atlassian.com
no relationship exists at all between status and resolution... a situation which causes continual problems and confusion at every client I have worked at

Same here. My instinct is to change the logic from resolved = "resolution field != null" to "issue is in status with flag/property that says resolved".
--
Post by broughn2 - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118

[f***@atlassian.com]

Jamie,
thank You.
--
Post by jacek - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=46118