I hope that someone can help. We use OpenReports to get some information out of TestLink. Our report was working on 1.6 but since the upgrade to 1.8 it no longer works. I know this is because the database structure has changed.
I am almost there but need some help with the last bit. I need a way of linking the nodes_hierachy table to the executions table so that I can get the name against the correct tcversion_id. In 1.6 there were a number of tables that needed to be linked - component, mgtcomponent, category and mgtcategory.
here is my 1.8 SQL so far
select nodes_hierarchy.name as project_name,
builds.name as build_name,
users.login,
executions.execution_ts as daterun,
executions.status,
nodes_hierarchy.id,
nodes_hierarchy.parent_id,
nodes_hierarchy.node_type_id,
executions.tcversion_id,
tcversions.tc_external_id,
tcversions.steps,
tcversions.expected_results
from executions inner join builds on builds.id = executions.build_id
inner join users on users.id = executions.tester_id
inner join nodes_hierarchy on nodes_hierarchy.id = executions.testplan_id
inner join tcversions on tcversions.id = executions.tcversion_id
where builds.name = $P{Testlink_Build} and
nodes_hierarchy.name = $P{Testlink_Project}
and here is my 1.6 one
SELECT
project.`name` AS project_name,
build.`name` AS build_name,
results.`runby` AS results_runby,
results.`daterun` AS results_daterun,
results.`status` AS results_status,
testcase.`title` AS testcase_title,
testcase.`steps` AS testcase_steps,
testcase.`exresult` AS testcase_exresult,
mgttestcase.`id` AS mgttestcase_id,
mgtcategory.`name` AS mgtcategory_name,
mgtcomponent.`name` AS mgtcomponent_name
FROM
`build` build INNER JOIN `project` project ON build.`projid` = project.`id`
INNER JOIN `results` results ON build.`id` = results.`build_id`
INNER JOIN `testcase` testcase ON results.`tcid` = testcase.`id`
INNER JOIN `mgttestcase` mgttestcase ON testcase.`mgttcid` = mgttestcase.`id`
INNER JOIN `component` component ON component.`projid` = project.`id`
INNER JOIN `mgtcomponent` mgtcomponent ON component.`mgtcompid` = mgtcomponent.`id`
INNER JOIN `category` category ON category.`compid` = component.`id`
INNER JOIN `mgtcategory` mgtcategory ON category.`mgtcatid` = mgtcategory.`id`
WHERE
project.`name` = $P{Testlink_Project} AND
build.`name` = $P{Testlink_Build} AND
testcase.`catid` = category.`id`
ORDER BY
mgtcomponent_name,
mgtcategory_name,
mgttestcase_id
I can get all of the items i need in the nodes_hierachy table by running the sql below but can;t see how to join this to the other tables
SELECT * FROM nodes_hierarchy n where parent_id in (142221) or
parent_id in (select id from nodes_hierarchy where parent_id in (142221))
order by node_type_id, id
Any help would be greatly appeciated
)Thanks
Tim