jql information in database

Question

Here is a jql query that I got the result of

assignee in membersOf("project")

This would return the issues of members belonging to project.

I would like to know in which table of jira database would this data(or this link of which member belong to which proj) is stored?

Answer 1

Group Membership

The group memberships are stored in the CWD_MEMBERSHIP table.

Example:

SELECT LOWER_CHILD_NAME 
FROM CWD_MEMBERSHIP 
WHERE MEMBERSHIP_TYPE = 'GROUP_USER' 
AND LOWER_PARENT_NAME = 'jira-administrators';

Example2, to fetch the user infos as well:

SELECT
   U.*
FROM
   CWD_MEMBERSHIP M
INNER JOIN CWD_USER U
ON
   M.LOWER_CHILD_NAME = U.LOWER_USER_NAME
WHERE
   M.MEMBERSHIP_TYPE   = 'GROUP_USER' AND
   M.LOWER_PARENT_NAME = 'jira-administrators';

Project Role Membership

The project role memberships however are in the PROJECTROLE and PROJECTROLEACTOR tables.

Example:

SELECT A.ROLETYPEPARAMETER AS USERNAME, R.NAME AS ROLENAME, P.PKEY || ' - ' || P.PNAME AS PROJECTNAME
FROM PROJECTROLEACTOR A
INNER JOIN PROJECTROLE R ON A.PROJECTROLEID = R.ID
INNER JOIN PROJECT P ON A.PID = P.ID
WHERE P.PKEY = 'YOUR_PKEY_COMES_HERE'
ORDER BY 3, 1, 2;

Example2, to get users that are explicitly assigned to project roles (not through groups):

SELECT A.ROLETYPEPARAMETER AS USERNAME, R.NAME AS ROLENAME, P.PKEY || ' - ' || P.PNAME AS PROJECTNAME
FROM PROJECTROLEACTOR A
INNER JOIN PROJECTROLE R ON A.PROJECTROLEID = R.ID
INNER JOIN PROJECT P ON A.PID = P.ID
INNER JOIN CWD_USER U ON LOWER(A.ROLETYPEPARAMETER) = U.LOWER_USER_NAME
ORDER BY 3, 1, 2;

Issue Change History

To get the issue history, you'll need the changegroup and changeitem tables joined to jiraissue. Changegroup stores who changed and when, changeitem contains the olda and new data, alongside what field was changed.

Example of listing ex-assignees:

SELECT
   CG.AUTHOR   AS CHANGE_USER ,
   CG.CREATED  AS CHANGE_WHEN ,
   CI.FIELD    AS CHANGED_WHAT,
   CI.OLDVALUE AS CHANGED_FROM,
   CI.NEWVALUE AS CHANGED_TO
FROM
   JIRAISSUE JI
INNER JOIN CHANGEGROUP CG
ON
   JI.ID = CG.ISSUEID
INNER JOIN CHANGEITEM CI
ON
   CG.ID = CI.GROUPID
WHERE
   JI.PROJECT   = 10100 AND
   JI.ISSUENUM  = 1234 AND
   CI.FIELDTYPE = 'jira' AND
   CI.FIELD     = 'assignee'
ORDER BY
   CG.CREATED ASC;

The last row's (newest created) newvalue must match jiraissue.assignee-s value.