Automatic Rerun: QualityCenter Workflow

For those of you who haven't been keeping up with the View into the Automation Lab story,  we have testers who are spending way too much time babysitting tests to make sure they run.  Combine that with the fact that we can't see what is actually running and where it's running at, we end up having a huge gap in machine utilization, meaning we are not only wasting people time, but also machine time.

The Automatic Rerun

Our attempt to automate the rerun process through QualityCenter had to start with some backend processing.  First we needed a database table to store the rerun information.  I'm including the database here since it has to be done and it's pretty simple.  We implemented a single table with 5 columns added to the QualityCenter database.  The columns are id, test_id, test_name, run_position, host_group so that we can order the tests and run them on the host machine they are supposed to run on.  Next, we decided to use QualityCenter workflow to drive the work that needed to be done by writing to this table.  Once that is complete, we can process this information the way we want.

Ideal Workflow

1. Test is identified as "Rerun"
2. Tester changes the status of the run to "Rerun"
3. QualityCenter would take over and insert a table row with the test run information
4. QualityCenter would change the status from "Rerun" to "Queued" to show that the test has been picked up
5. Separate process would chug through the table and run tests as they were popped onto the table
6. This process would also be multi-threaded based on the number of available machines
7. Web page would show current test status (tests in the queue, order of tests in the queue, tests currently running and which machine they were running on)
8. Web page would allow for reordering tests in the queue
9. Web page would also show the current machine status

I haven't worked a lot with QualityCenter workflow and even less in the workflow scripting.  I knew the code I needed to put in...but WHERE?  I started my quest in the script editor after reading a few descriptions of what could be done I decided that the TestSetTests and TestSet were two that made the most sense and I needed either FieldChange or FieldCanChange.  , so add a few message boxes in and start changing test status to "Rerun" to see which function was being hit.  After a little back and forth, I was able to identify the correct Function as *** Insert the function here ***  Now when a test status is set to "Rerun" the workflow updates the status to "Queued" so that we know the test information has been sent to the next step in the process to run the test by way of writing to a database table (Steps 1-4 above)

Heavy Lifting (aka Backend Code)

Most of what we need here is setup and connection to QualityCenter because we already have the database and simple SQL gets everything we need.
Step 1: Read the first row of the table to get the Test ID and the Host
Step 2: Check the host to see if it's a group or a specific machine.

• Specific machine: we have everything we need, just send that machine name to QC
• Host Group: we need to follow a few extra steps to get an available machine for running the test
• Get the first machine name from the host group

	SELECT HO_NAME
	FROM td.HOSTS
	WHERE HO_ID IN (
	SELECT HG_HOST_ID
	FROM td.HOST_IN_GROUP
	WHERE HG_GROUP_ID IN (
	SELECT GH_ID
	FROM td.HOST_GROUP
	WHERE GH_NAME IN (
	SELECT TC_HOST_NAME
	FROM td.TESTCYCL
	WHERE TC_TESTCYCL_ID = '" & strTestCycle & "'
	)
	)
	)

view raw gistfile1.sql hosted with ❤ by GitHub

• As long as you have something returned, put the data into an array so you can easily loop through each machine to see what's available to run on, something like this:

	If sqlHostGroupReader.HasRows Then
	While (sqlHostGroupReader.Read())
	If sqlHostGroupReader.Item(0) IsNot Nothing Then
	arrHostMachines(index) = sqlHostGroupReader.Item(0)
	index = index + 1
	End If
	End While
	End If

view raw gistfile1.vb hosted with ❤ by GitHub

• Finally, the magic SQL to tell whether a machine is available to use or not:

	SELECT RN_HOST
	FROM td.RUN
	WHERE RN_STATUS <> 'Not Completed'
	AND RN_HOST IN ('" & strHostMachine & "')
	AND RN_RUN_ID IN (
	SELECT MAX(RN_RUN_ID)
	FROM td.RUN
	GROUP BY RN_HOST
	)

view raw gistfile1.sql hosted with ❤ by GitHub

Step 3: Connect to QualityCenter (this is all standard QC API stuff)

	'Create connection to Quality Center
	Try
	tdc = CreateObject("tdapiole80.tdconnection")
	If (tdc Is Nothing) Then
	Debug.Print("Could not connect to Quality Center")
	End If
	Catch e As Exception
	Debug.Print(e.Message)
	End Try
	tdc.InitConnectionEx(qcHostName)
	tdc.Login(qcUser, qcPassword)
	tdc.Connect(qcDomain, qcProject)

view raw gistfile1.vb hosted with ❤ by GitHub

Step 4: Return the test set folder object of the test you are trying to run

	Dim tsTreeMgr As TestSetTreeManager
	Dim tsFolder As TestSetFolder
	Dim strFolder As String
	' Get the test set tree manager from the test set factory.
	tsTreeMgr = tdc.TestSetTreeManager
	strFolder = "Root\<folder name here>"
	tsFolder = tsTreeMgr.NodeByPath(strFolder)
	If tsFolder Is Nothing Then
	'Disconnect from the project
	If tdc.Connected Then
	tdc.Disconnect()
	End If
	'Log off the server
	If tdc.LoggedIn Then
	tdc.Logout()
	End If
	'Release the TDConnection object.
	tdc.ReleaseConnection()
	tdc = Nothing
	Err.Raise(vbObjectError + 1, "RunTestSet", "Could not find folder")
	Exit Sub
	End If

view raw gistfile1.vb hosted with ❤ by GitHub

Step 5: Return the test set object of the test you are trying to run

	Dim tsList As List
	Dim theTestSet As TestSet
	' Search for the test set
	tsList = tsFolder.FindTestSets(strTestSet, False)
	If tsList Is Nothing Then
	Err.Raise(vbObjectError + 1, , "RunTestSet", "Looking for test set and cannot find it. ABORT!")
	End If
	If tsList.Count > 1 Then
	Err.Raise(vbObjectError + 1, , "RunTestSet", "FindTestSets found more than one test, please refine search")
	ElseIf tsList.Count < 1 Then
	Err.Raise(vbObjectError + 1, "RunTestSet", "FindTestSets " & strTestSet & " test set not found")
	End If
	theTestSet = tsList.Item(1)

view raw gistfile1.vb hosted with ❤ by GitHub

Step 6: Start the scheduler and run the test (don't forget to remove the row from the table)

	' Start the scheduler on the local machine
	Scheduler = theTestSet.StartExecution(strHostMachine)
	' Run tests on a specified remote machine
	Scheduler.TdHostName = strHostMachine
	' Run the tests
	Scheduler.Run(strTestCycle)
	'Remove the row from the table
	Try
	Dim conn = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=<database name>;server=<server name>;Connect Timeout=30")
	conn.Open()
	sqlTestCycle = New SqlCommand("DELETE FROM <table name> WHERE q_test_id =" & strTestCycle, conn)
	sqlTestCycleReader = sqlTestCycle.ExecuteReader()
	sqlTestCycleReader.Read()
	conn.Close()
	Catch ex As Exception
	Debug.Print(ex.Message)
	Exit Sub
	End Try

view raw gistfile1.vb hosted with ❤ by GitHub

Step 7: Finish up by monitoring the execution object so that you can close out all of the objects when done

	Dim execStatus As ExecutionStatus
	'Get the execution status object.
	execStatus = Scheduler.ExecutionStatus
	'Track the events and statuses.
	Dim RunFinished As Boolean, iter As Integer, i As Integer
	Dim ExecEventInfoObj As ExecEventInfo
	Dim EventsList As List
	Dim TestExecStatusObj As TestExecStatus
	iter = 0
	RunFinished = False
	While ((RunFinished = False) And (iter < 200))
	iter = iter + 1
	execStatus.RefreshExecStatusInfo("all", True)
	RunFinished = execStatus.Finished
	EventsList = execStatus.EventsList
	For Each ExecEventInfoObj In EventsList
	Debug.Print("Event: " & ExecEventInfoObj.EventDate & " " & ExecEventInfoObj.EventTime & " " & _
	"Event Type: " & ExecEventInfoObj.EventType & " [Event types: " & "1-Fail, 2-Finished, " & _
	"3-Environment Failure, 4-Timeout, 5-Manual]")
	Next
	For i = 1 To execStatus.Count
	TestExecStatusObj = execStatus.Item(i)
	Debug.Print("Iteration: " & iter & ", Test ID: " & TestExecStatusObj.TestId & ", Test Cycle ID: " & _
	TestExecStatusObj.TSTestId & ", Status: " & TestExecStatusObj.Status)
	Debug.Print(" Message: " & TestExecStatusObj.Message)
	Next i
	Threading.Thread.Sleep(10000)
	End While
	Debug.Print("Scheduler finished test " & strTestName & " around " & CStr(Now))

view raw gistfile1.vb hosted with ❤ by GitHub

Step 8: Clean up after yourself and do it all over again

	'Disconnect from the project
	If tdc.Connected Then
	tdc.Disconnect()
	End If
	'Log off the server
	If tdc.LoggedIn Then
	tdc.Logout()
	End If
	'Release the TDConnection object
	tdc.ReleaseConnection()
	tdc = Nothing

view raw gistfile1.vb hosted with ❤ by GitHub

That's it!