SQL Queries for Quality Center Modules

One of the few good things [there are only few] about QC is that all the info is stored in a MSSQL DB, and can be easily accessed via QC OTA, Analysis Modules, etc. Here are a few samples.

A. Notes on some Components of QC Database -

• RELEASE_CYCLES - this table stores details about the releases created in the Release module.
• CYCLE - this table stores info about the test sets, and uses CY_ASSIGN_RCYC
• TESTCYCL - this table stores info about the instances of test runs, status, etc
• TEST - this table stores info about the test names, test types [manual, automated], designer, etc

 
B. Process to build SQL Queries for different Quality Center modules

All Analysis reports have to be built in the Dashboard >> Analysis module. There are 3 different types of reports that can be built, but Excel is the one we are focusing on right now, since, we can add SQL Queries only to Excel type reports.

Report type - Create a report to get the Status and Count of all the tests present under different Test Sets, for a particular Release Cycle.

Solution Steps -

• Create a Release Cycle under Management >> Release Module, based on the name of your current release. Eg., 'App_Release2_SIT'.
• Note down the value of 'Target Cycle ID' field populated in QC - this would get saved as CY_ASSIGN_RCYC field in the QC database, lets assume it as 1005.
• Create a folder in Test Lab as 'Parent_TL_Folder'. Create another subfolder called SIT under this parent folder, and assign the release cycle, 'App_Release2_SIT' to this folder.
• If there are 2 different cycles in your plan, one for Functional SIT and one for Regression, then create another cycle for Regression like 'App_Release2_Regression'. Then create a seperate folder for Regression tests, under the same 'Parent_TL_Folder' at the same level as 'SIT' folder, and assign this regression cycle 'App_Release2_Regression' to this folder.
• Now, create all the different Test Sets for this release under this Test Lab folder. Lets say there are 3 test sets called 'Artifact1' 'Artifact2' 'Artifact3'.
• Now, pull all your tests under these 3 tet sets, from the Test Plan.
• Once this all is done, all you need is the 'Target Cycle ID' for both the cycles [SIT and Regression] to query all the tests for the release.

C. Sample Queries -
--------------------------------------------------------------
--Query to get the execution Status and Count for all the tests under a given release cycle

SELECT CYCLE.CY_CYCLE ,
COUNT (*) AS "No of Tests" ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'PASSED' THEN 1 END ) AS 'Passed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'FAILED' THEN 1 END ) AS 'Failed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'BLOCKED' THEN 1 END ) AS 'Blocked' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'NO RUN' THEN 1 END ) AS 'No Run' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS IN ( 'PASSED' , 'FAILED' ) THEN 1 END ) AS 'Executed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'NOT COMPLETED' THEN 1 END ) AS 'Partially Executed'
FROM CYCLE
JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
--JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID    --Join with TEST table, can help us pull out TEST_NAME as well for the tests
WHERE 1=1
AND CYCLE.CY_ASSIGN_RCYC = '1005'
--AND CYCLE.CY_CYCLE LIKE 'BRD%'    --filter all the test sets starting with BRD
GROUP BY CYCLE.CY_CYCLE

--------------------------------------------------------------
-- Query to get the Name and ID of the Release Cycle

SELECT RCYC_NAME, RCYC_ID, *
FROM RELEASE_CYCLES
WHERE 1=1
AND RCYC_ID = '1005'

--------------------------------------------------------------
--Query to get Names of all the Test Sets under the chosen Release Cycle

SELECT CY_CYCLE AS "TEST_SET_NAME" , *
FROM CYCLE
WHERE 1=1
AND CY_ASSIGN_RCYC = '1005'
ORDER BY CY_CYCLE

--------------------------------------------------------------
--Query to get Defect related details for a Release

SELECT
BG_BUG_ID AS "DEFET ID" ,
BG_SEVERITY AS "SEVERITY" ,
BG_STATUS AS "STATUS" ,
BG_SUMMARY AS "SUMMARY" ,
BG_PRIORITY AS "PRIORITY" ,
BG_RESPONSIBLE AS "ASSIGNED TO" ,
BG_USER_11 AS "DETECTED BY" ,
BG_DETECTION_DATE AS "DETECTED ON" ,
BG_DEV_COMMENTS AS "COMMENTS"
FROM BUG
WHERE 1=1
AND BG_STATUS NOT IN ('Closed', Deferred' , 'TestReady' )
AND BG_USER_12 = 'SIT ENV'
AND BG_USER_13 = 'App_Release2_SIT'

--------------------------------------------------------------
--Query to get BPT Components for a Test. 
--The below query gets the design steps of the components part of a BPT test in the order they are in the test. Just add the TestID where shown.

SELECT
C.CO_NAME || '[' || BT.BC_CO_INSTANCE || ']',
CS.CS_STEP_NAME ,
CS.CS_DESCRIPTION,
CS.CS_EXPECTED
FROM BPTEST_TO_COMPONENTS BT,
COMPONENT C,
COMPONENT_STEP CS
WHERE 1=1
AND BT.BC_PARENT_ID = <testIDhere>
AND BT.BC_CO_ID = C.CO_ID
AND C.CO_ID = CS.CS_COMPONENT_ID
ORDER BY BT.BC_ORDER, CS.CS_STEP_ORDER

--------------------------------------------------------------

Install JBehave plugins for IntelliJ and Eclipse

For IntelliJ -

Go to add plugin section, then search the repositories for 'jbehave'
The following plugins [in blue] would be needed

JBehave Results Report format for BDD Story

How and what the JBehave result report displays, and how to update the format

• All the scenarios mentioned with the 'Scenario:' tag in the story file are counted as 'Total' number of scenarios per story file - it does not matter whether they have GWT steps defined/implemented or not, if there is a 'Scenario:' tag, it would be counted.
• Scenarios without the GWT steps defined in the story file would be counted as 'Pending' and would also be marked as 'Successful'. This makes sense because those scenarios have not failed/excluded technically because their steps are not even defined, forget implemented. Having a 'skip' tag for such scenarios does not make sense and is redundant.
• This would be also true for the scenarios with GWT steps defined but not yet fully implemented; these would be counted as 'Pending' as well. In short, if GWT steps are not defined/implemented fully, the scenario would be marked as pending, and in this case, Meta filtering to skip the test is Not necessary.
• Only those scenarios are counted as 'Excluded' which 1. have the GWT steps defined/implemented  and 2. have the skip filter added against them. Scenarios without GWT steps, even though marked as 'skip-ed' would still be counted as 'Pending' and not 'Excluded', which is correct.
• The following sections are not needed and should be removed - how exactly needs to be added here.
• AfterStories
• BeforeStories
• GivenStory Scenarios

• Run Config for IDE/Maven command-line:

-Dbrowser=FIREFOX -DrunEnv=test1 -DmetaFilters=+Regression-skip-Manual

• This will run only the scenarios with the tag "Regression"
• This will NOT run any scenarios with skip and Manual tags
• We can add as many tags as needed with corresponding +/- notations
• Refer these posts for more detail Passing and Reading Run Time Variables via Maven and Enable Meta Filtering in JBehave

How the JBehave tests should be run -

Its a good idea that the final RunConfig should be tested only via maven command-line and not via any IDE. Also, when we want to run the tests, we should fire the mvn command and not use the IDE. 

This will ensure that we are testing with the same command that we want to put on CI, and also, bring to surface any issues when running via just command-line which is how all the tests are supposed to be triggered.

Bug with JBehave results reports -

Issue - Old results are not deleted from the /target/jbehave/ dir, due to which results which are not part of the current also get included in the latest results. 

When we do a selective run of only the scenarios that we want to run [only those scenarios as per the filter provided], and only they do get run, but if we have run any other stories previously [which were not part of the current selective run] then their results keep lingering around and are present in the final result report - we should have a clean run everytime, but not have to download the dependencies or resources everytime.

Solution - For now, this has been taken care by manually deleting the contents of the /target/jbehave/ dir [the 'view' folder also gets deleted, but it gets recreated when the tests are run] as part of the BeforeStories method. 

Also, we should be just deleting the files, in the target and view folders, and not the other folders and files in these 2 folders.

A better solution would be to figure out the jbehave config that can help delete older results before each run.

• The default format that comes with the simple archetype is not good enough, and is very bland. It is sans colours and has a lot of redundant info.
• To update the Report format, the pom from the simple archetype needs to be updated with the one from CCentric, specially the element with **/*Stories.java has to be changed to **/*Scenarios.java.
• This has to be replaced with the one from CodeCentric [LT also used the one similar to CodeCentric] This is much better than the default one, but still has two issues - 1. The 'GivenStory Scenarios' section still appears in the report, and is always blank - This is not the BeforeStories section, as it BeforeStories appear along with regular stories. 2. The 'Totals' showing the number of stories in on RHS, though this is not a deal-breaker, and is a good enough version to start with.
• There is a third report format that I have seen which does not have 'GivenStory Scenarios' section, nor the incorrect Totals section. So, the goal is to have the report in this format.
• But do not try to build this format by editting the FTL files yourself - its much more time consuming and tricky than you think. Instead try to search the config that will help generate the report in the format that you need.
• Also, there are a few extra options that can be included in the reports [in the order of priority] -
• Attach the screenshots
• Have the 'Jenkins Build Console Log' link to display the log file info
• Attach the test data files - input & output - as a link for each scenario result

Info about FTL files -

• FreeMarker (Template) by FreeMarker ProjectFreeMarker is a 'template engine'; a generic tool to generate text output (anything from HTML to autogenerated source code) based on templates. It's a Java package, a class library for Java programmers. It's not an application for end-users in itself, but something that programmers can embed into their products. This association is classified as Source Code.
• This uses the .FTL files that we have under the view/ftl folder for jbehave.