How to query on JIRA DB to get this:<colgroup> <col width="76"> <col width="86"> <col width="74"> <col width="73"></colgroup>
jira version: 4.3.2
There's not enough information here to form a useful query.
First, you've not said where your user lists are held - crowd, ldap, internal etc. That's only a minor issue with a simple answer (it's likely to be cwd_user for the basic list, and then cwd_groups and cwd_membership, but we can't be sure until we know the directory)
The main problem is that you have not told us how you are defining the link between a project and user. Just saying "user is in project" is not good enough. You need a rule like "can view issues" or "is in the role of user" or "can comment on issues" - or combinations of them.
All of the rules you could define are complex, and often have many routes through your data. So there's no answer for your question until you define the relationship between project and user.
Ok. So you'll need to execute the queries to get the users and groups on the three CWD tables I already mentioned. They're quite simple to join - read cwd_membership for lines where it mentions group, join it to cwd_user for more user info, and cwd_group for the group name.
What about the relationship between user and project?
Just wondering what you really meany by "which project he is part of". Do you mean to say all the projects which the user has permission to view? (this is what Nic is asking). Once that is clear, query the permissions for a project, figure out the roles and groups that permission and then get the users who are part of it.
Ok, so, you've got quite a complex chunk of SQL to construct. I'm not a DBA, and I don't really know how to do it, but the pseudo-code would be:
... and so-on
Generally, this is not something I'd try to do in SQL, it's complex and changeable. The reason I kept asking you for rules is to establish that you need to be very very clear about exactly what you mean, so that I could show you how complex this really it.
You will find it far easier to simply look at Jira.
It would be worth reviewing why you're asking for this in SQL - it probably is not the best way to get the information you are looking for. Could you explain why you are asking?
Atlassian Summit is an excellent opportunity for in-person support, training, and networking.Learn more
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG