How to query on JIRA DB to get this:
<colgroup> <col width="76"> <col width="86"> <col width="74"> <col width="73"></colgroup>user_name | Projectname | ProjectKey | user group |
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
what do you mean by relationship between user and project?
yes, user can create/view/modify issues ?
but i want just the name of the user and which project he is part of
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I mean exactly what I said before - you need to define a precise set of rules that explains "user is part of this project"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Lalit,
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, all the projects which user has the permission to view and to create/modify issues.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.