Using TL 1.9.1; I like to report on how many test cases (per project) have been created in the past week; and how many have been updated.
I'm finding it difficult to do efficiently...
* If I use the API; I have no way to get Test Cases by date. I can only call getTestCasesForTestSuite including the "full" parameter to get all of the test cases and then use Python to loop through all the test cases and pick out ones with timestamps in the past week. This works but it is really slow; and as our TL database grows will only get slower
* If I use SQL; I can easily get test cases in the last week from the tcversions table; but I don't know how to link them back to their test project. In "nodes_hierarchy"; the test case will link back to the project via an unknown number of test suite branches. It might be one test suite; it might be two; it might be ten! Is there any clever SQL I can use here?
Currently I am considering some way to get a list of IDs via SQL (very fast) then use API or multiple smaller SQL calls to get the project and other details for each ID
I'm already using the schema diagram from that bug; thanks.
The problem I have is that I don't know how to SQL join a testcase_version in nodes_hierarchy with a testproject. I can join the parent_id of testcase_version to give the testcase; then I can use that parent_id to get the testsuite; but then I don't know if there's 0,1,2,or more parent test suites before the parent_id gives the testproject!
I think my best choice at this point is to do a simple SQL query to get the IDs of the test cases I want; then write some Python code to get the individual details for each test
>> but then I don't know if there's 0,1,2,or more parent test suites before the parent_id gives the testproject!
that's why best option IMHO is to learn how to use our PHP Classes, or use the API because info is stored in a tree and the root of tree (test project) is not stored on each node.
Here's the code I'm using.
I use simple SQL queries; then a while loop to repeatedly get the parent in nodes_hierarchy until the type of the parent is "testproject" and not "testsuite"
import _mysql
import time, datetime
import calendar
import cgi
import re
import operator
class TestCaseDetails:
""" Store the details of a test case """
database_id = 0
testcase_id = 'Uninitialised'
testcase_title = 'Uninitialised test case title'
test_suite = 'Uninitialised'
test_project = 'Uninitialised'
def __init__(self):
database_id = 0
testcase_id = 'Uninitialised'
testcase_title = 'Uninitialised test case title'
test_suite = 'Uninitialised'
test_project = 'Uninitialised'
# Print all test case authoring
# aStart - start date
# aEnd - end date
# aDB - a TestLink database connection
def PrintTestCaseAuthoring(aStart, aEnd, aDB):
#Get New Test Cases
query = "select id from tcversions where creation_ts between '" + aStart + "' and '" + aEnd + "' and version = '1';"
aDB.query(query)
result = aDB.store_result()
newTestCaseIDs = []
newTCRow = result.fetch_row()
while(newTCRow):
newTestCaseIDs.append(newTCRow[0][0])
newTCRow = result.fetch_row()
newTestCaseDetails = GetTestCaseDetailsFromTestCaseIDList(aDB, newTestCaseIDs)
#Get Modified Test Cases
query = "select id from tcversions where creation_ts between '" + aStart + "' and '" + aEnd + "' and version > '1';"
aDB.query(query)
result = aDB.store_result()
modifiedTestCaseIDs = []
modTCRow = result.fetch_row()
while(modTCRow):
modifiedTestCaseIDs.append(modTCRow[0][0])
modTCRow = result.fetch_row()
modifiedTestCaseDetails = GetTestCaseDetailsFromTestCaseIDList(aDB, modifiedTestCaseIDs)
#Sort and print the arrays
newTestCaseDetails = sorted(newTestCaseDetails, key=operator.attrgetter('test_project','test_suite'))
modifiedTestCaseDetails = sorted(modifiedTestCaseDetails, key=operator.attrgetter('test_project','test_suite'))
print '<h3>Created:</h3></br><table><tr><td><b>Project</b></td><td><b>Test Suite</b></td><td><b>Test Title</b></td></tr>'
for tcd in newTestCaseDetails:
print '<tr><td>' + tcd.test_project + '</td><td>' + tcd.test_suite + '</td><td>' + tcd.testcase_title + '</td></tr>'
print '</table>'
print '<h3>Modified:</h3></br><table><tr><td><b>Project</b></td><td><b>Test Suite</b></td><td><b>Test Title</b></td></tr>'
for tcd in modifiedTestCaseDetails:
print '<tr><td>' + tcd.test_project + '</td><td>' + tcd.test_suite + '</td><td>' + tcd.testcase_title + '</td></tr>'
print '</table>'
# Get Test Case Details from a list of IDs
# this gets its own method as the code can be re-used for new and modified tests
def GetTestCaseDetailsFromTestCaseIDList(aDB, aListOfTestCaseIDs):
resultList = []
for tcID in aListOfTestCaseIDs:
tcDetails = TestCaseDetails()
tcDetails.database_id = tcID
# Get the TC title from nodes_hierarchy
query = 'select name, parent_id from nodes_hierarchy where id in (select parent_id from nodes_hierarchy where id = ' +
tcID + ')'
aDB.query(query)
result = aDB.store_result()
tcRow = result.fetch_row()
tcDetails.testcase_title = tcRow[0][0]
# Get the project for this TC
# Keep getting the parent_id from nodes_hierarchy until it is 1 (testproject)
node_type = 2
next_id = tcRow[0][1]
project_name = ''
while (int(node_type) == 2):
query = 'select name, parent_id, node_type_id from nodes_hierarchy where id=' + str(next_id)
aDB.query(query)
result = aDB.store_result()
nodeRow = result.fetch_row()
next_id = nodeRow[0][1]
node_type = nodeRow[0][2]
#print "<p>" + str(next_id) + " " + str(node_type)
if int(node_type) == 2:
if tcDetails.test_suite != "Uninitialised":
tcDetails.test_suite = nodeRow[0][0] + " / " + tcDetails.test_suite
else:
tcDetails.test_suite = nodeRow[0][0]
if int(node_type) == 1:
tcDetails.test_project = nodeRow[0][0]
resultList.append(tcDetails)
return resultList
# Main code
tlDB = _mysql.connect("SERVER", "USER", "PASS", "SCHEMA")
PrintTestCaseAuthoring(datetime.datetime(2012,11,01), datetime.datetime(2012,11,22), tlDB)
