Help to get SQL Statement

[bukanski_cn]

Hallo,

I need to build this SQL statement:

I want to read out the Test Project's name than the Test suites which are in this test project and the test cases which are in the test suites. A structure like this:

• TestProject
  • TestSuite1
    • TestCase1
    • TestCase2
    • TestCase3
  • TestSuite 2
    • TestCase1
    • TestCase2

Is this possible? Can anybody help me please?

Thanks for answer

[bukanski_cn]

Anybody an idea?

Thanks a lot.

[bukanski_cn]

Hi,

really nobody here who can help me? I'm really desperate.

Leave this topic 3 days open than it can be closed.

Thanks a lot.

[GunnarD]

A begining:

SQL to get projects (id and name)

Code: Select all

select testprojects.id, nodes_hierarchy.name from nodes_hierarchy, testprojects where nodes_hierarchy.node_type_id=1 and nodes_hierarchy.id=testprojects.id;

Then you need to get testplans from each projects.

When you have testplans id you can get testsuits id and then testcase id.

Take a look at DB schema.

[bukanski_cn]

Hello,

will try it this afternoon and give a response or ask for next help.

Thanks a lot meanwhile.

[bukanski_cn]

Hello,

i have got this Query now:

Code: Select all

$sqlNew2 = "SELECT tl_testprojects.id, tl_nodes_hierarchy.name, tl_testplans.notes  FROM tl_nodes_hierarchy, tl_testprojects, tl_testplans WHERE tl_nodes_hierarchy.node_type_id=1 AND tl_nodes_hierarchy.id=tl_testprojects.id AND tl_testplans.testproject_id=tl_testprojects.id";

The result is:

Code: Select all

1 - TestProject -
This is the first testplan for this project! 

but i cannot conenct this query so i can put out the testsuites which are in this testplan. Can you help me?

Thanks.

[fman]

your best option (and the obly one IMHO) is to dig of existent classes and try to reuse the queries.

[bukanski_cn]

Hello last time

I only need help now, to build a query where are could see which tastcases are in which testsuites.

thanks a lot for help

[bukanski_cn]

Hello,

I'm crashing my head about three weeks now with this problem and cannot find an solution for it.

Can please anybody help me how to create this sql query?

Thank you very much.

[GunnarD]

No SQL but it seems that table nodes_hiearchy is the key table to connect the different tables.

In nodes_types you can see what the different nodes_hiearchy.node_type_id means.

[bukanski_cn]

Hello and thanks for answer.

in testplans table i can see what testplans are in a project. (but only the description)
In the tcversions i can see the description of the testcases and in executions table i can see if a test case failed, passed and so on. (this two tables i can connect)
In the node_hierarchy table, how you said, i can see which headlines means what( i mean if its a project name, testsuite name, testcase name, ...)

but i really have no idea how to join that all into an query. In the node_type_id i can only see if its a testcase, testsuite, ... but cannot connect it to the other tables like testsuites, tcversions or executions.

i'm really confused :/

it would be very very simple if the node_hierarchy table has another id column which says, what rows are in ONE project. but this information is missing and its not possible to read it out.
it would also help if the .sql script contains some constraints which tables has relation to it.

[fman]

>> it would be very very simple if the node_hierarchy table has another id column which says, what rows are in ONE project. but this
Probably we have made lot of mistakes, when we did our initial db modelling, but IMHO you can not complain about the missing column ( a column that do you need NOW, but may be tomorrow you are going to complain for another column for your needs) because we have also offered an API to get info in order to avoid lot of work to users.
In addition in a previous post I've provided the advice to read our code, thing that seems you have ignored ( feedback was never provided).
Due to unlimited depth of test link tree structure for test suites and test cases, you are going very difficult to solve all your needs with a single query. (if you read the code you are going to find several methods that use recursion).

Another thing I would like to point out is that you can not (At least from my point of view) 'storm' one topic because you need help posting again and again and again. Seems that instead of asking for help you are REQUESTING HELP THAT HAS TO BE PROVIDED ASAP.

>> it would also help if the .sql script contains some constraints which tables has relation to it.
The DB MODEL IS AVAILABLE, the scripts for POSTGRES has the FK, then what is the missing info ?

As usual all the info is there, but you need to be willing to dig a little bit the installation tree.
That's what i normally do when working with open source products.

[Greco]

Hi again, pals,

Which API are you talking about fman? Is XMLRPC? I'm also finding difficult to do my own queries, and I'd like to know which method are you using to do reports, but I cannot access to the code here (trouble with the fckng ISA server, not even CNTLM works)

Can you give us some indications. Thanks in advance.

[bukanski_cn]

Hi fman,

Probably we have made lot of mistakes, when we did our initial db modelling, but IMHO you can not complain about the missing column ( a column that do you need NOW, but may be tomorrow you are going to complain for another column for your needs)

I dont want to say you've made a lot of mistakes with the design of your database and i also understand you could not put for everyone a column into tables for his/her needs. But what i want to say is that many people befor me asked for help with some queries or the db schema (that seems like the users perhaps needs this information) and the reacton only was: "its not possible".

In addition in a previous post I've provided the advice to read our code, thing that seems you have ignored ( feedback was never provided).

sorry that i do not give a response to your post, i studied of course the code and tried to understand how the tree is build but i was not able to check all.
I read the code but i really could not find the right functions etc which are building the tree.

Seems that instead of asking for help you are REQUESTING HELP THAT HAS TO BE PROVIDED ASAP.

I never said i need the information ASAP. I everytime waited a long time for a answer.

The DB MODEL IS AVAILABLE, the scripts for POSTGRES has the FK, then what is the missing info ?

In the postgres sql i also could not find any FK (version 1.9.4). Maybe i am looking not on the right script?

At the end i only want to say, if i checked at the beginning the code how the tree building works than i would not need help.

Thanks for your answer.

[fman]

On Postgres files an example is :
"id" BIGINT NOT NULL DEFAULT '0' REFERENCES /*prefix*/nodes_hierarchy (id),

you need to go to install folder and look for testlink_create_tables.sql

ONLY API IS XMLRPC.
Reports are building using some special function and other standard methods: the code to look is print* files and then from there follow the trace of different methods.