Home > database > Testlink Database SQL query

Testlink Database SQL query

Objective # 1: Filter all testcases under Globe Portal Capabilities testplan (testplan_id = ‘9007’) and include summary, steps and expected result in the query

testplan_tcversions

tcversions
field description
field description
id internal id for testcase versioning id unique id per testcase
testplan_id id that determines the testplan tc_external_id external id
tcversion_id unique id per testcase version latest version
node_order order of the node summary summary of testcase
urgency degree of urgency steps steps of testcase
expected_results expected result
importance degree of importance
author_id author
creation_ts date of creation
updater_id updater
modification_ts date of modification
active is active?
is_open is open?
execution_type type of execution 1-manual;2-automated

SQL query:


SELECT * FROM testplan_tcversions LEFT JOIN tcversions ON tcversions.id = testplan_tcversions.tcversion_id

WHERE testplan_tcversions.testplan_id = '9007'

Problem: Testcases have does not indicate its component/testsuite (Contacts, Search, Widgets etc.)

Objective # 2: Include Component(from nodes_hierarchy table) for each testcase in the query

nodes_hierarchy node_types
field description
field description
id unique id per testcase id id of node
name description / name description description of node
parent_id id of the parent node
node_type type of node node_types table
id description
1 testproject
2 testsuite=Component
3 testcase=Testcase Summary
4 testcase_version=Testcase Steps
5 testplan
6 requirement_spec
7 requirement

SQL query:


SELECT a.*,  b.*, c.parent_id, c.node_type FROM testplan_tcversions a LEFT JOIN tcversions b ON b.id = a.tcversion_id LEFT JOIN nodes_hierarchy c ON a.tcversion_id = c.id

WHERE testplan_tcversions.testplan_id = '9007'

Problem: parent_id for testcases with node_type = ‘4’ is not sufficient to get the testcase component/testsuite

e.g.

nodes_hierarchy
id parent_id node_type description value
9068 9067 4 testcase steps < steps >
9067 9010 3 testcase summary User can view his Contacts
9010 9006 2 testsuite/component Personalizations: Contacts
9006 1 testproject Gportal Capabilities

In our result query for id = ‘9068’, ‘9067’ will only give us the summary/title of the testcase but not its component/testsuite , our next objective is to get the parent_id of ‘9067’

Objective # 3: Include the parent_id of  each parent_id for all the testcases in the query. Just like the following

id parent_id parent_id2 name
9068 9067 9010 Personalizations: Contacts

To get the parent_id for each parent_id from the nodes_hierarchy table, we create a subquery:


SELECT x.id, x.name, x.node_type_id, x.parent_id, y.parent_id as parent_id2

FROM nodes_hierarchy x left join nodes_hierarchy y ON x.parent_id = y.id

Then add our subquery to our major query in Objective # 1:

SELECT a.*, b.*, d.*
FROM testplan_tcversions a
LEFT JOIN tcversions b on a.tcversion_id = b.id
LEFT JOIN (<em>select x.id, x.name, x.node_type_id, x.parent_id, y.parent_id as parent_id2 from nodes_hierarchy x left join nodes_hierarchy y on x.parent_id = y.id</em>) d on a.tcversion_id = d.id
WHERE a.testplan_id = '9007'

😀 Hats off Kuya Ed!

Advertisements
Categories: database Tags: , , , , ,
  1. madhu
    August 9, 2014 at 12:08 am

    Hi,
    I have your query executed on testlink DB 1.9.10 and I see that, in the query a.tcversion_id=d.id is not fetching the testcase name, instead blank is shown. Is there any change in the query required.

  2. Raghav Chandak
    January 27, 2015 at 8:16 pm

    what is the query to separate the test case suites??

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: