JDS Australia

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.

Quality Center takes a slightly different approach….

USERS table

Viewing the contents of the table…

1
2
3
4
5

SELECT
  US_USERNAME,
  US_GROUP
FROM
  GROUPS

…gives…

GROUPS table

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…

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.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.