软件英才网软件行业驰名招聘网站
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。