Help with user statistics

[tomemr2]

Hello everyone!

I've been trying to gather some user statistics (tests created, modified and executed by Test Project/user), but I'm not being able to correlate Test Projects with Test Cases and Users all together...
Can anyone please help me with the query for these statistics:
- test cases created by user, grouped by test project/user, within a certain time frame (let's say year 2012)
- test cases modified by user, grouped by test project/user, within a certain time frame
- test cases executed by user, grouped by test project/user, within a certain time frame

Thanks in advance!

[fman]

due to tree like structure of test spec, you can not solve the question: to what test project a test case belong ? with a simple query.

you need to walk each test spec tree from root (test project) to a node of test case type, user info is present on nodes of tcversion type.
If you search in forum you can find some post regarding DB schema.
With the righ requirement specification and a donation, development team can take care of a custom development to solve your needs.
Or you can study our classes and use existent methods with slight changes

[tomemr2]

Thanks for your answer fman!

I have done a script to gather all the test cases IDs for a given test project, and I'm working with the DB schema by my side.
My problem is: when I try to get more info for a specific test case, lets say test case ID=3, I get several test cases and most of them are not even related to the test project I'm working with...

My query:

Code: Select all

SELECT id, tc_external_id 
FROM tcversions 
WHERE tc_external_id=3;

Result:

Code: Select all

id     tc_external_id
8	3
142	3
597	3
643	3
723	3
743	3
1720	3
1819	3
1872	3
1936	3
2090	3
2198	3
2433	3
2758	3
2809	3
3463	3
4978	3
4992	3
5404	3
9079	3
9260	3
9514	3

Shouldn't this query return only the info for test case id=3?
What can I add to the query, in order to gather only the info for test case id=3?

Thank you!

[GunnarD]

Is all versions active?

Code: Select all

SELECT id, tc_external_id, version, active FROM tcversions WHERE tc_external_id=3;

[tomemr2]

Yes they are!

Code: Select all

id  tc_external_id  version  active
8	3	1	1
142	3	1	1
597	3	1	1
643	3	1	1
723	3	1	1
743	3	1	1
1720	3	1	1
1819	3	1	1
1872	3	1	1
1936	3	1	1
2090	3	1	1
2198	3	1	1
2433	3	1	1
2758	3	1	1
2809	3	1	1
3463	3	1	1
4978	3	2	1
4992	3	3	1
5404	3	1	1
9079	3	1	1
9260	3	1	1
9514	3	1	1

[fman]

Please access info (in this forum on mantis) regarding DBSCHEMA in order to avoid wasting time.
EXTERNAL ID is ID displayed on User Interface and can be REPEATED on all Test projects.
As stated in previous post all is organized in a Tree way, and parent-child relations are 99% managed in nodes_hierarchy table and NOT ON ITEM tables
(example tcversions)