Using Work Item Queries for Automation

A lot of desirable custom automation for Rational Team Concert is for managing work items. Especially as the number of work items grow over time. I found it very useful to be able to run work item queries and use them to feed my automation. This post shows how to do this.

The Wiki Page QueryDevGuide shows more example code and also how to create queries using the API.

Queries are related to Expressions which are content of the posts Using Expressions for Automation and Using an Expression to Synchronize Attributes for Work Items of a Specific Type.

I am working with some fairly big IBM internal projects that have very mature processes and, due to the huge amount of items, desire a lot of automation. The code in this post originates from working with Susan Hanson who came up with the majority of the code. I found it so useful that I use it in several places.

License and how to get started with the RTC API’S

As always, our lawyers reminded me to state that the code in this post is derived from examples from Jazz.net as well as the RTC SDK. The usage of code from that example source code is governed by this license. Therefore this code is governed by this license, which basically means you can use it for internal usage, but not sell. Please also remember, as stated in the disclaimer, that this code comes with the usual lack of promise or guarantee. Enjoy!

If you just get started with extending Rational Team Concert, or create API based automation, start with the post Learning To Fly: Getting Started with the RTC Java API’s and follow the linked resources.

You should be able to use the following code in this environment and get your own automation or extension working.

To keep it simple this example is, as many others in this blog, based on the Jazz Team Wiki entry on Programmatic Work Item Creation and the Plain Java Client Library Snippets. The example in this blog shows RTC Client API.

Find A Query

If trying to use a work item query for automation, it is necessary to find the query first. To find a query there are basically two scenarios to consider. A query can be a personal query of the user that runs the automation, or it can be a shared query that was predefined and shared by a project area or team area. The first code block below shows how to find a personal query for a given query name provided as string.

public static IQueryDescriptor findPersonalQuery(IProjectArea projectArea, 
		String queryName, IProgressMonitor monitor)
		throws TeamRepositoryException {
	// Get the required client libraries
	ITeamRepository teamRepository = (ITeamRepository)projectArea.getOrigin();
	IWorkItemClient workItemClient = (IWorkItemClient) teamRepository.getClientLibrary(
		IWorkItemClient.class);
	IQueryClient queryClient = workItemClient.getQueryClient();
	// Get the current user.
	IContributor loggedIn = teamRepository.loggedInContributor();
	IQueryDescriptor queryToRun = null;
	// Get all queries of the user in this project area.
	List queries = queryClient.findPersonalQueries(
		projectArea.getProjectArea(), loggedIn,
		QueryTypes.WORK_ITEM_QUERY,
		IQueryDescriptor.FULL_PROFILE, monitor);
	// Find a query with a matching name
	for (Iterator iterator = queries.iterator(); iterator.hasNext();) {
		IQueryDescriptor iQueryDescriptor = (IQueryDescriptor) iterator.next();
		if (iQueryDescriptor.getName().equals(queryName)) {
			queryToRun = iQueryDescriptor;
			break;
		}
	}
	return queryToRun;
}

The code to call the operation for a personal query “All Work Items” would look as below.

IQueryDescriptor query = findPersonalQuery(projectArea, "All Work Items", monitor);

It is also possible to access the shared queries. The code would look as follows.

public static IQueryDescriptor findSharedQuery(	IProjectArea projectArea, 
		List sharingTargets, String queryName,  IProgressMonitor monitor)
		throws TeamRepositoryException {
	// Get the required client libraries
	ITeamRepository teamRepository = (ITeamRepository)projectArea.getOrigin();
	IWorkItemClient workItemClient = (IWorkItemClient) teamRepository.getClientLibrary(IWorkItemClient.class);
	IQueryClient queryClient = workItemClient.getQueryClient();
	IQueryDescriptor queryToRun = null;
	List queries = queryClient.findSharedQueries(projectArea.getProjectArea(),
		sharingTargets, QueryTypes.WORK_ITEM_QUERY,
	IQueryDescriptor.FULL_PROFILE, monitor);
	// Find a query with a matching name
	for (Iterator iterator = queries.iterator(); iterator.hasNext();) {
		IQueryDescriptor iQueryDescriptor = (IQueryDescriptor) iterator.next();
		if (iQueryDescriptor.getName().equals(queryName)) {
			queryToRun = iQueryDescriptor;
			break;
		}
	}
	return queryToRun;
}

To call the operation it is necessary to provide a list of sharing targets. The sharing targets can be any project area or team areas in the project area. The code to call the operation above would look as below.

List sharingTargets = new ArrayList();
// Add desired sharing targets
sharingTargets.add(projectArea);
IQueryDescriptor sharedQuery = findSharedQuery(projectArea, sharingTargets, "Done Stories", nomitor);

Run A Query

Once the query is found, you can run it and get the result set. There are basically two possible ways to get the query results.

1. It is possible to get the results unresolved, which basically means you only get an IAuditableHandle in the result and have to resolve the work item from it to get the data.
2. It is possible to get the results as work items, resolved with a specific load profile.

*Update*

WARNING!

To avoid calamities with many other users screaming at you, because the server is down, please be very careful if using resolved query results. This is especially true when running with a FULL_PROFILE and overriding the query result limit and running queries with huge amounts of work items. In this case it is smarter to use the unresolved results and pay the performance penalty of resolving the work items individually.

The code to get the unresolved results using the IQueryClient looks as follows.

IWorkItemClient workItemClient = (IWorkItemClient) teamRepository.getClientLibrary(IWorkItemClient.class);
IQueryClient queryClient = workItemClient.getQueryClient();
IQueryResult unresolvedResults = queryClient.getQueryResults(query);

The code to get the resolved results is very similar and presented below. The difference is that it is necessary to provide a load profile to get the results resolved to the load profile.

IWorkItemClient workItemClient = (IWorkItemClient) teamRepository.getClientLibrary(IWorkItemClient.class);
IQueryClient queryClient = workItemClient.getQueryClient();
// Set the load profile
ItemProfile loadProfile = IWorkItem.SMALL_PROFILE;
IQueryResult<IResolvedResult> resolvedResults = queryClient.getResolvedQueryResults(query, loadProfile);

The load profile basically defines the attributes that are loaded and available in the work item. com.ibm.team.workitem.common.model.IWorkItem defines several profiles.

ItemProfile SMALL_PROFILE
ItemProfile DEFAULT_PROFILE
ItemProfile MEDIUM_PROFILE
ItemProfile LARGE_PROFILE=
/** The full item profile. */
ItemProfile FULL_PROFILE

I usually use SMALL_PROFILE or FULL_PROFILE. The SMALL_PROFILE provides PROJECT_AREA_PROPERTY, ID_PROPERTY, TYPE_PROPERTY, SUMMARY_PROPERTY, OWNER_PROPERTY. It is possible to create a custom load profile using createExtension and providing the attributes/properties required. For example:

IWorkItem.SMALL_PROFILE.createExtension(Arrays.asList(new String[] { STATE_PROPERTY, PRIORITY_PROPERTY, SEVERITY_PROPERTY, CREATOR_PROPERTY, WORKFLOW_SURROGATE_PROPERTY, DESCRIPTION_PROPERTY, }));

To limit the data to read, use a profile with minimal data to load, especially if running on big queries.

Query Result Size Limit

When processing the query result, you need to be aware that the CCM application has a “Maximum Query Result Set Size”, which is typically set to 1000 items. No work item query run by a user through any UI will provide more items than defined by this limit. The reason basically is to prevent the server from being overloaded by massive amounts of database accesses caused by users.

The limit also applies to the API, however, in the API it is possible to get around it to get the full result set. To do that, you have to change the limit of the result set. It is necessary to do that before you do anything else with the result set. The limit can not be changed after you accessed the results.

The code to change the limit for unresolved results looks like below:

((QueryResultIterator) unresolvedResults).setLimit(Integer.MAX_VALUE);

The code to change the limit for a resolved result set is simpler.

resolvedResults.setLimit(Integer.MAX_VALUE);

Processing the Query Result

The following method shows how to process the unresolved results

public static void processUnresolvedResults(IProjectArea projectArea , IQueryResult results,
		ItemProfile profile, IProgressMonitor monitor)
		throws TeamRepositoryException {
	// Get the required client libraries
	ITeamRepository teamRepository = (ITeamRepository)projectArea.getOrigin();
	IWorkItemClient workItemClient = (IWorkItemClient) teamRepository.getClientLibrary(IWorkItemClient.class);
	IAuditableCommon auditableCommon = (IAuditableCommon) teamRepository.getClientLibrary(IAuditableCommon.class);
	long processed = 0;
	while (results.hasNext(monitor)) {
		IResult result = (IResult) results.next(monitor);
		IWorkItem workItem = auditableCommon.resolveAuditable(
			(IAuditableHandle) result.getItem(), profile, monitor);
		// Do something with the work item here
		processed++;
	}
	System.out.println("Processed results: " + processed);
}

After adding some logic to process the work item, the method can be called like below:

processUnresolvedResults(projectArea, unresolvedResults, loadProfile, monitor);

The following method can be used to process the resolved results.

public static void processResolvedResults(IProjectArea projectArea,IQueryResult<IResolvedResult> resolvedResults,
		 IProgressMonitor monitor)
		throws TeamRepositoryException {
	// Get the required client libraries
	ITeamRepository teamRepository = (ITeamRepository)projectArea.getOrigin();
	IWorkItemClient workItemClient = (IWorkItemClient) teamRepository.getClientLibrary(IWorkItemClient.class);
	long processed = 0;
	while (resolvedResults.hasNext(monitor)) {
		IResolvedResult result = resolvedResults.next(monitor);
		IWorkItem workItem = result.getItem();
		// do something with the work item
		processed++;
	}
	System.out.println("Processed results: " + processed);
}

After adding some logic to process the work item, the method can be called like this:

processResolvedResults(projectArea, resolvedResults, monitor);

Process Paged Results

It is possible to paginate the resolved results and run on the returned pages. The code below runs some code on pages in parallel.

resolvedPResults.setPageSize(100);
ArrayList jobs = new ArrayList();
while(resolvedPResults.hasNext(null)){
	ResolvedPageJob job = new ResolvedPageJob(teamRepository, workItemClient, resolvedPResults.nextPage(null));
	jobs.add(job);
	job.setUser(true);
	// run as System Job;
	//job.setSystem(true);
	job.schedule();
}
for (ResolvedPageJob resolvedPageJob : jobs) {
	try {
		resolvedPageJob.join();
	} catch (InterruptedException e) {
	// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

The Job class would look like this.

public static class ResolvedPageJob extends Job {
	private List<IResolvedResult> fPage;
	private ITeamRepository fTeamRepository;
	private IWorkItemClient fWorkItemClient;

	public ResolvedPageJob( ITeamRepository teamRepository, IWorkItemClient workitemClient, List<IResolvedResult> results) {
		super("Run Page");
		fTeamRepository=teamRepository;
		fWorkItemClient=workitemClient;
		fPage=results;
	}

	protected IStatus run(IProgressMonitor monitor) {
		try {
			processResolvedResultsPaged(fTeamRepository, fWorkItemClient,fPage);
		} catch (TeamRepositoryException e) {
			System.out.println("Exception: " + e.getLocalizedMessage());
			return new Status(Status.ERROR,"Me","TeamRepositoryException",e);
		}
		return Status.OK_STATUS;
	}
}

And the method to process the paged resolved results would look like below.

public static void processResolvedResultsPaged(ITeamRepository teamRepository,
		IWorkItemClient workItemClient,
		List page)
		throws TeamRepositoryException {
	long processed = 0;
	for (IResolvedResult resolvedResult : page) {
		IWorkItem workItem = resolvedResult.getItem();
		// do something with the work item
		processed++;
	}
	System.out.println("Processed results: " + processed);
}

Some Observations

I optimized the code a bit by reducing the client libraries to be looked up. I basically got the client libraries in a main method and passed them to the methods.

Then I ran the methods against a test system that had 16000 work items, randomly unresolved and resolved. Some observations I made:

1. Running with resolved query results was typically quicker than running against unresolved result sets. The bigger the number of work items the smaller the difference.
2. For running parallel jobs I got a mixed bag. Sometimes it was a lot slower and sometimes it was quicker.

My experiments ran against a test system running on the same machine and using Derby as DB.

To avoid calamities with many other users screaming at you, because the production server is down, please be very careful if using resolved query results. This is especially true when running with a FULL_PROFILE and overriding the query result limit and running queries with huge amounts of work items. In this case it is smarter to use the unresolved results and pay the performance penalty of resolving the work items individually.

Summary

Using the API as described above allows you to run automation against work item queries, for example using WorkItemOperation as presented in Uploading Attachments to Work Items. I will describe some scenarios in follow up posts, for example how to “Synchronize Attributes”. I hope that these code examples will help users out there that have a need for more automation.