Absent Member.
Absent Member.
4269 views

Retrieve Folder Name and the associated test cases through SQL

Jump to solution

I want to retrieve all the folders and the associated test cases from a Project selected Project and test container via report. I have tried finding the table which has the folder names in a test container, but i could not get the table information. Can anyone help with a SQL query to retrieve this information.

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert
Hi Sriram Prabhu,

You want the direct parent folders for each test. I have modified the query a bit to give for every test the name (and ID) of the folder which is the direct parent of the test. Please mind that the query still filters for a special test container:

SELECT directParent.NodeID_pk FolderID, directParent.Name FolderName, tests.*
FROM TM_V_TestContainers tc
INNER JOIN TM_PlanTreePaths ptp ON tc.NodeID_pk = ptp.ParentNodeID_pk_fk
INNER JOIN LQM_V_SimpleTests tests ON ptp.NodeID_pk_fk = tests.TestID_pk
INNER JOIN TM_V_TestPlanNodes directParent ON tests.ParentID_fk = directParent.NodeID_pk
WHERE tc.NodeID_pk = <YOUR_TESTCONTAINERID>
ORDER BY FolderName

Some background:
- the view TM_V_TestContainers returns all test containers in your database
- table TM_PlanTreePaths holds all parent child relations, i.e. for every child you can query every parent node id (direct and indirect)
- view LQM_V_SimpleTests gives you all tests (with properties and attributes)
- TM_V_TestPlanNodes holds all test plan nodes (tests, folders, test containers)

The query above filters on test container and returns every test with its direct parent (folder).

Regards,
Hubert

View solution in original post

0 Likes
17 Replies
Micro Focus Expert
Micro Focus Expert

Hi SriramPrabhu,

 

If you want to list your tests by test container and the first level folder for each test container then you could do this:

SELECT tpn.NodeID_pk FolderID, tpn.Name FolderName, tests.*

FROM TM_V_TestContainers tc

INNER JOIN TM_V_TestPlanNodes tpn ON tc.NodeID_pk = tpn.ParentNodeID_fk

INNER JOIN TM_PlanTreePaths ptp ON tpn.NodeID_pk = ptp.ParentNodeID_pk_fk

INNER JOIN LQM_V_SimpleTests tests ON ptp.NodeID_pk_fk = tests.TestID_pk

WHERE tc.NodeID_pk = <YOUR_TESTCONTAINERID>

ORDER BY FolderName

 

Replace <YOUR_TESTCONTAINERID> with your test container id.

 

If you want the result across all test containers then replace the where clause with

WHERE tc.ProjectID_fk = <YOUR_PROJECTID>

and replace <YOUR_PROJECTID> with your project id.

 

Regards,

Hubert

0 Likes
Absent Member.
Absent Member.
Hi Hubert,

Thanks for your response.

Your Query is giving the test cases by Container but it does not give the folder name in which test cases are placed.
For example:

Test Container A has 2 sub folders 1 and 2. If i run the query i should be able to retrieve all the test cases in the sub folders 1 and 2 along with the folder names to which it is associated.

The query you have provided has the folder ID as 'ParentID_fk' but i want the folder name in place of this ID.

Thanks & Regards
Sriram Prabhu
0 Likes
Micro Focus Expert
Micro Focus Expert
Hi Sriram Prabhu,

You want the direct parent folders for each test. I have modified the query a bit to give for every test the name (and ID) of the folder which is the direct parent of the test. Please mind that the query still filters for a special test container:

SELECT directParent.NodeID_pk FolderID, directParent.Name FolderName, tests.*
FROM TM_V_TestContainers tc
INNER JOIN TM_PlanTreePaths ptp ON tc.NodeID_pk = ptp.ParentNodeID_pk_fk
INNER JOIN LQM_V_SimpleTests tests ON ptp.NodeID_pk_fk = tests.TestID_pk
INNER JOIN TM_V_TestPlanNodes directParent ON tests.ParentID_fk = directParent.NodeID_pk
WHERE tc.NodeID_pk = <YOUR_TESTCONTAINERID>
ORDER BY FolderName

Some background:
- the view TM_V_TestContainers returns all test containers in your database
- table TM_PlanTreePaths holds all parent child relations, i.e. for every child you can query every parent node id (direct and indirect)
- view LQM_V_SimpleTests gives you all tests (with properties and attributes)
- TM_V_TestPlanNodes holds all test plan nodes (tests, folders, test containers)

The query above filters on test container and returns every test with its direct parent (folder).

Regards,
Hubert

View solution in original post

0 Likes
Absent Member.
Absent Member.
Hello Hubert,

Thanks a lot. This solved my problem and i'm good if it filters with Special Container.
Just a curious question, is it possible to retrieve the along with Parent Child relationship. I have tried myself but since i do not have a defined number sub-folders, so it didn't work in the way i wanted.

Thanks,
Sriram Prabhu
0 Likes
Micro Focus Expert
Micro Focus Expert
Hi Sriram Prabhu,

Sorry, I do not understand your question. Could you please explain what data you want to select?

Regards,
Hubert
0 Likes
Absent Member.
Absent Member.
Hi Hubert,

The query you have provided is able to provide the test case list and the direct parent folder name in which it is associated.

My question was, is it possible to retrieve along with the folder structure as it is present in Silk Central, i.e., as shown shown below, if the test cases is present under 3 level of sub folders from the main container, i would need the report to have all the sub folder names in the report. But the problem is that, the number sub folder level is not constant, some test case will be under 1st level folder and some will be 5th level folder.

For example:

Container Folder --> Folder 1 --> Folder 2 --> folder 3 --> Test case 1
0 Likes
Absent Member.
Absent Member.
thanks for posting Sriram and Hubert, This query is useful and I could find some benefits using it. Question on an error I am getting....I am getting an error with LQM_V_SimpleTests when trying to run the query and wondering what I need to do to fix it:

ERROR Invalid query: Invalid object name 'LQM_V_SimpleTests'.

thanks!
0 Likes
Micro Focus Expert
Micro Focus Expert
Hi James,

The view LQM_V_SimpleTests is available since

SC 16.5 Hotfix 7
SC 17.0 Hotfix 1
SC 17.5

Which version of Silk Central are you using? If you are using a version before one of the above, do you have any chance to upgrade?

Regards,
Hubert
0 Likes
Micro Focus Expert
Micro Focus Expert

Hi SriramPrabhu,

 

For MS SQL Server the following query returns for all tests within a certain test container the hierarchy in column "NameWithHierarchy":

 

WITH TestHierarchy(NodeID_pk, Level, NameWithHierarchy)

AS (

  SELECT NodeID_pk, 1 AS Level, CONVERT(NVARCHAR(1000), Name) AS NameWithHierarchy

  FROM TM_TestPlanNodes WHERE ParentNodeID_fk IS NULL AND ProjectID_fk IS NOT NULL

  UNION ALL

  SELECT n.NodeID_pk, Level+1, CONVERT(NVARCHAR(1000), NameWithHierarchy + ' --> ' + n.Name) AS NameWithHierarchy

  FROM TM_TestPlanNodes n

  INNER JOIN TestHierarchy r ON r.NodeID_pk = n.ParentNodeID_fk

  WHERE Level < 100

)

SELECT h.NameWithHierarchy, tests.*

FROM TM_V_TestContainers tc

INNER JOIN TM_PlanTreePaths ptp ON tc.NodeID_pk = ptp.ParentNodeID_pk_fk

INNER JOIN LQM_V_SimpleTests tests ON ptp.NodeID_pk_fk = tests.TestID_pk

INNER JOIN TestHierarchy h ON tests.TestID_pk = h.NodeID_pk

WHERE tc.NodeID_pk = <YOUR_TESTCONTAINERID>

ORDER BY h.NameWithHierarchy

 

Regards,

Hubert

0 Likes
Absent Member.
Absent Member.
we are on 17.0 at the moment...we will need to get upgraded to 17.5. thanks Hubert for the info!
0 Likes
Absent Member.
Absent Member.
Hi Hubert, I am just following up on this. we are not going to be upgrading our Silk Central environment until later in 2018, so in the mean time can you please let me know what table(s) I need to change to be consistent with running the below SQL in silk central v17 with hotfix 4.

I was getting the error on the table 'LQM_V_SimpleTests', what table could be used to run in v17?

SELECT directParent.NodeID_pk FolderID, directParent.Name FolderName, tests.*
FROM TM_V_TestContainers tc
INNER JOIN TM_PlanTreePaths ptp ON tc.NodeID_pk = ptp.ParentNodeID_pk_fk
INNER JOIN LQM_V_SimpleTests tests ON ptp.NodeID_pk_fk = tests.TestID_pk
INNER JOIN TM_V_TestPlanNodes directParent ON tests.ParentID_fk = directParent.NodeID_pk
WHERE tc.NodeID_pk = <YOUR_TESTCONTAINERID>
ORDER BY FolderName
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.