Archive for the ‘database’ Category

Testlink Database SQL query

July 27, 2009 2 comments

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


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 = 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 = a.tcversion_id LEFT JOIN nodes_hierarchy c ON a.tcversion_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


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.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 =

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 =
LEFT JOIN (<em>select,, 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 =</em>) d on a.tcversion_id =
WHERE a.testplan_id = '9007'

😀 Hats off Kuya Ed!

Categories: database Tags: , , , , ,