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!