QC資料庫表結構

有時候QC的報表不能滿足我們的要求，需要自己從QC的資料庫中查詢資料，放到Excel中進行統計並生成圖表，因此有必要熟悉一下QC的表結構：

 

Requirement (REQ)

• Req ID (RQ_REQ_ID)
• Name (RQ_REQ_NAME)
• Description (RQ_REQ_COMMENT)
• Requirement Type (RQ_TYPE_ID)
• Is Folder (RQ_IS_FOLDER) – beware. This contains incorrect data (folders with value “N”)
• Req Father ID (RQ_FATHER_ID) e.g. the folder id that the requirement is in.
• Risk (RQ_USER_03)

Requirement Types (REQ_TYPE)

• Requirement Type ID (TPR_TYPE_ID) – is foreign key for REQ.RQ_TYPE_ID
• Requirement Type Name (TPR_NAME) – e.g. “Folder”, “Business”, “Functional” etc

Requirement Coverage (REQ_COVER) – maps requirements to test cases

• Requirement (RC_REQ_ID)
• Covering Entity ID (RC_ENTITY_ID)
• Coverage Type (RC_ENTITY_TYPE) – always seems to be “TEST”. Do not put this in a WHERE clause or you will filter out requirements that do not have an associated test case.

Test (TEST) – contains test cases

• Test ID (TS_TEST_ID)
• Test Name (TS_NAME)
• Execution Status (TS_EXEC_STATUS)

 

下面SQL指令碼查出所有需求項：

-- This query shows all requirements in all folders.

SELECT

  REQ.RQ_REQ_ID AS 'Requirement ID',

  REQ.RQ_USER_01 AS 'Req ID Project', -- this has the original REQ ID from project doco

  REQ.RQ_REQ_NAME AS 'Requirement Name',

  REQ.RQ_REQ_COMMENT AS 'Requirement Description',

  REQ_TYPE.TPR_NAME AS 'Requirement Type',

  REQ.RQ_USER_03 AS 'Risk'

FROM

  REQ

  JOIN REQ_TYPE

    ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID -- join to get the name of the requirement type e.g "Folder"

WHERE

  TPR_NAME != 'Folder' -- Only retrieve requirements of type "Business" or "Functional"

ORDER BY

  RQ_REQ_ID

 

 

下面SQL指令碼查出所有測試用例：

-- This query shows all test cases in all folders.

SELECT

  TEST.TS_TEST_ID AS 'Test ID',

  TEST.TS_NAME AS 'Test Name',

  TEST.TS_EXEC_STATUS AS 'Execution Status'

FROM

  TEST

ORDER BY

  TEST.TS_TEST_ID

 

 

下面SQL指令碼查出哪些有測試用例覆蓋的需求項：

-- This query shows all requirements with any test cases that are mapped to the requirement.

-- If there are 2 test cases mapped to a requirement, then both will be included.

SELECT

  REQ.RQ_REQ_ID AS 'Requirement ID',

  REQ.RQ_USER_01 AS 'Req ID Project', -- this has the original REQ ID from project doco

  REQ.RQ_REQ_NAME AS 'Requirement Name',

  REQ.RQ_REQ_COMMENT AS 'Requirement Description',

  REQ_TYPE.TPR_NAME AS 'Requirement Type',

  REQ.RQ_USER_03 AS 'Risk',

  TEST.TS_TEST_ID AS 'Test Case ID',

  TEST.TS_NAME AS 'Test Case Name',

  TEST.TS_EXEC_STATUS AS 'Execution Status'

FROM

  REQ

  LEFT JOIN

    REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID -- join requirements to test cases (REQ to REQ_COVER)

  LEFT JOIN

    TEST ON REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID -- join requirements to test cases (REQ_COVER to TEST)

  JOIN

    REQ_TYPE ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID -- join to get the name of the requirement type e.g "Folder"

WHERE

  REQ_TYPE.TPR_NAME != 'Folder' -- Only retrieve requirements of type "Business" or "Functional"

ORDER BY

  REQ.RQ_REQ_ID

 

參考：

http://www.jds.net.au/tech-tips/quality-center-database-queries/