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 on “Querying Quality Center user roles

  1. 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?

  2. 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

  3. 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.

  4. 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

Leave a Reply