Querying Quality Center user roles

Quality Center stores the user/role relationship in a strange way in the database. This Tech Tip shows you one way you could write an SQL query for this relationship if, for some reason, you did not want to use the QC Admin view.

Recently I wrote a Tech Tip on querying the Quality Center database. Poking around the QC database reveals a few code oddities, one of which is how user roles are represented in the database.

Most people who were writing an application where there were multiple users and multiple roles, and users were mapped to one or more roles, would implement that many-to-many relationship using a junction table. Finding a user’s role (or roles) would be a matter of writing a simple join query.

An example of a many-to-many database table relationship

Quality Center takes a slightly different approach….

USERS table

Column Name Data Type
US_ADDRESS text
US_FULLNAME varchar
US_GROUP varchar
US_MAIL_ADDRESS varchar
US_PASSWORD varchar
US_PHONE varchar
US_USERNAME varchar

Viewing the contents of the table…

1
2
3
4
5
SELECT
  US_USERNAME,
  US_GROUP
FROM
  GROUPS

…gives…

US_USERNAME US_GROUP
alice 101000
bob 010000
carol 000100
stuart 000001

GROUPS table

Column Name Data Type
GR_GROUP_ID int
GR_GROUP_NAME varchar
GR_IS_SYSTEM varchar
GR_PREDEF_FILTER text

Viewing the contents of the table…

1
2
3
4
5
6
SELECT
  GR_GROUP_ID,
  GR_GROUP_NAME,
  GR_IS_SYSTEM
FROM
  GROUPS

…gives…

GR_GROUP_ID GR_GROUP_NAME GR_IS_SYSTEM
0 TDAdmin Y
1 QATester Y
2 Project Manager Y
3 Developer Y
4 Viewer Y
5 Performance Tester N

Now there is no obvious mapping of USERS.US_GROUP to GROUPS.GR_GROUP_ID that could be used to join the two tables…but if you think of the US_GROUP field as an array where each 1 will turn on the GROUP_ID that matches that position, you can easily determine the correct groups. So Alice, who has a US_GROUP of “101000” (position 0 and position 2) will have the role of TDAdmin and Project Manager.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- This query shows all users and their roles. Note that if custom roles have been created, they must be added to this query with additional UNION statements (like "Performance Tester" has been).
-- 0 TDAdmin
  SELECT
    US_USERNAME AS 'User Name', US_FULLNAME AS 'Full Name', 'TDAdmin' AS 'Group Name'
  FROM USERS
  WHERE SUBSTRING(US_GROUP,1,1) = '1'
UNION
-- 1 QA Tester
  SELECT
    US_USERNAME AS 'User Name', US_FULLNAME AS 'Full Name', 'QA Tester' AS 'Group Name'
  FROM USERS
  WHERE SUBSTRING(US_GROUP,2,1) = '1'
UNION
-- 2 Project Manager
  SELECT
    US_USERNAME AS 'User Name', US_FULLNAME AS 'Full Name', 'Project Manager' AS 'Group Name'
  FROM USERS
  WHERE SUBSTRING(US_GROUP,3,1) = '1'
UNION
-- 3 Developer
  SELECT
    US_USERNAME AS 'User Name', US_FULLNAME AS 'Full Name', 'Developer' AS 'Group Name'
  FROM USERS
  WHERE SUBSTRING(US_GROUP,4,1) = '1'
UNION
-- 4 Viewer
  SELECT
    US_USERNAME AS 'User Name', US_FULLNAME AS 'Full Name', 'Viewer' AS 'Group Name'
  FROM USERS
  WHERE SUBSTRING(US_GROUP,5,1) = '1'
UNION
-- 5 Performance Tester
  SELECT
    US_USERNAME AS 'User Name', US_FULLNAME AS 'Full Name', 'Performance Tester' AS 'Group Name'
  FROM USERS
  WHERE SUBSTRING(US_GROUP,6,1) = '1'

Unfortunately this is a little ugly. I would appreciate anyone giving me better code to deal with this odd way of relating users to groups.

11 comments

The query is great.

SELECT USERS.US_USERNAME, GROUPS.GR_GROUP_NAME FROM USERS, GROUPS WHERE(INSTR(US_GROUP,1,1,1)-1) =GROUPS.GR_GROUP_ID order by gr_group_name

One more requirement will be to get the same result but grouped by User name?

56345345345345

If anyone wants to use Mirkos solution on MSSQL instead of Oracle, please use this:

SELECT USERS.US_USERNAME,
GROUPS.GR_GROUP_ID,
GROUPS.GR_GROUP_NAME
FROM USERS, GROUPS
WHERE(CHARINDEX(‘1’, US_GROUP,1)-1) =GROUPS.GR_GROUP_ID

i wanted to share a screenshot of the issue i m facing..but i m not able to .. can nyone please help

Stuart Moncrieff
Stuart Moncrieff

Sorry, this is not really a technical support forum. Maybe you could try the HP Support website.

Cheers,
Stuart.

i have a query.. can anyone please answer for me. i m facing a grid view issue. The test set is not visible in the grid view of test lab in HPQC 10. even though the tree view is working fine in Test lab. can anyone please help me on dis.. wil really appreciate.

Here the better solution, which doesn’t require not built-in functions:

SELECT US_USERNAME, GR_GROUP_NAME
FROM USERS, GROUPS
WHERE SUBSTRING(US_GROUP, GR_GROUP_ID + 1, 1) = 1

Thanks so much Riga !
Your SQL coding works perfectly for me.

Mirko,
You are a genius. This was the exact query I was looking for.

Thanks for this post. I found it very interesting since I was having the same issue. I figured out a simpler way to realize the join between the USERS and GROUPS table. For me it’s working fine:

SELECT USERS.US_USERNAME,
GROUPS.GR_GROUP_ID,
GROUPS.GR_GROUP_NAME
FROM USERS, GROUPS
WHERE(INSTR(US_GROUP,1,1,1)-1) =GROUPS.GR_GROUP_ID

Mirko

Stuart Moncrieff
Stuart Moncrieff

Actually, there is a posting on that website where someone has used the same technique to store whether an “agent” is allowed to sell a “product line”.

http://thedailywtf.com/Articles/Pretty-Simple.aspx

This makes writing a feature to ‘Find Agency by Product Line’ harder than it should be.

Cheers,
Stuart.

This is a good example of how NOT to create a database many-to-many relationship. I can’t believe that a mature product that is used by thousands of people has code like this in it. Definitely deserves to be on The Daily WTF.

Leave a Reply