WIQL for Test

WIQL (Work Item Query Language) is the query language for Work Items that Test Management adopts for querying test objects. In Microsoft Test Manager (MTM), WIQL queries are hidden away from users and only the query results are displayed. Nevertheless, if you ever need to use the command-line tool (TCM.exe) or to write your own code against the TCM OM, knowing the Test-specific WIQL syntaxes would become very handy.

General information about WIQL can be read from MSDN. In this post, I will discuss what you need to know when using WIQL with Test Management. If you have used WIQL before with Work Item Tracking(WIT) OM, you will find several discrepancies between the WIT and TCM WIQL queries. This is because we implemented a simplified model of the WIQL engine that suites our needs in this version.

Here is the list of differences and Test-specific information:

1. SELECT * should always be used. Unlike WIT, we don’t support returning specific values with the query results. The result set will give you either fully populated objects or skinny ones, based on the object type. The skinny objects, which have only their ID, are populated by paging when their properties are accessed.
2. Query Context is not supported. Hence, the only macros that work are the default ones.
3. The query methods of ITestManagementTeamProject and its helper interfaces are team project-scoped. This means you do not need to add [TeamProject] field in the WHERE clause and the query would still return test objects under the current team project. If you have [TeamProject] field with a different team project name, the query will return nothing.
4. The query [and find] methods of ITestManagementService return cross-team project objects. Using macro @project in cross-team project query will return no result.
5. Test point query methods of ITestPlan are test plan-scoped.
6. EVER and ASOF are not supported. We do not expose previous versions of test objects to users.
7. Security considerations: “View test runs” is the required permission to view all test objects. Without this permission, users will get no result back from a query. Test objects bound to Area nodes are also subjected to Area node permission. For example, if user A does not have read permission to Area node X, the test objects in node X will not be returned as part of the query results. The following test objects are Area node-bound: test result and test plan [hence, test point and test suite].
8. One important thing Dennis Stone pointed out: Querying test cases in TCM OM is not the same as querying other test objects. Test cases are essentially work items, so test case WIQL queries are similar to other work item WIQL queries.

What you will also want to know are the table names and the list of field names that you can use to construct your query. Here is the rule of thumb:

"In general, the table names will match the interface name minus the ‘I’ (e.g. ITestResult –> TestResult).  The field names will match the names of the corresponding properties of the object (e.g. ITestConfiguration.Name –> Name).”

The table below lists all tables and supported fields for Test-specific WIQL queries*.

Table	Fields	Notes
TestAttachment	AttachmentId AttachmentType FileName IsComplete IterationId TestResultId TestRunId TmiRunId
TestConfiguration	AreaPath Description Id IsDefault LastUpdated LastUpdatedBy Name Revision State TeamProject
TestPlan	AreaPath AutomatedTestEnvironmentId Description EndDate Iteration ManualTestEnvironmentId Owner PlanAutomatedTestSettingsId PlanId PlanLastUpdated PlanLastUpdatedBy PlanName PlanRevision PlanState PlanTestSettingsId StartDate TeamProject
TestPoint	AssignedTo Comment ConfigurationId FailureType LastResolutionStateId LastResultOutcome LastResultState LastTestResultId LastTestRunId PlanId PointId PointLastUpdated PointLastUpdatedBy PointRevision PointState RecursiveSuiteId* SuiteId SuiteState TestCaseId	* RecursiveSuiteId is used to query all test points that appear in the given suite and any suite contained in the given suite. If both RecursiveSuiteId and SuiteId are provided, the query will use the RecursiveSuiteId.
TestResult	AutomatedTestId* AutomatedTestName* AutomatedTestStorage* AutomatedTestType* AutomatedTestTypeId* Comment ComputerName ConfigurationId CreationDate DateComplete DateStarted Duration ErrorMessage FailureType LastUpdated LastUpdatedBy Outcome Owner Priority ResetCount ResolutionStateId Revision State TeamProject TestCaseArea TestCaseId TestCaseRevision TestCaseTitle TestPlanId TestPointId TestResultId TestRunId	* The Automated Test information is stored in the ITestCaseResult .Implementation property. These properties come from ITmiTestImplementation, which derives from ITestImplementation.
TestRun	BuildConfigurationId BuildFlavor BuildNumber BuildPlatform BuildUri Comment CompleteDate Controller CreationDate DropLocation DueDate ErrorMessage IsAutomated IsBvt Iteration LastUpdated LastUpdatedBy LegacySharePath* Owner PostProcessState PublicTestSettingsId Revision StartDate State TeamProject TestEnvironmentId TestMessageLogId TestPlanId TestRunId Title Type Version	* Not exposed in ITestRun
TestSettings	AreaPath CreatedBy CreatedDate Description Id IsAutomated IsPublic* LastUpdated LastUpdatedBy Name Revision TeamProject	* Not exposed in ITestSettings
TestSuite	Description Id LastUpdated LastUpdatedBy ParentId PlanId ProjectName Query* RequirementId** Revision State SuiteType Title	* Part of the IDynamicTestSuite** Part of the IRequirementTestSuite

 

 

* Many thanks to Peter Van Nuys for gathering the information in this table.

Comments

• Anonymous
  March 15, 2010
  Hi, Great post, I missed it. I recommend you cross post your stuff to http://blogs.msdn.com/vstsqualitytools Thanks

• Anonymous
  February 23, 2011
  TestResult table does not contain column name "TestPlanID". It would be greate if you can update the table structure

• Anonymous
  June 29, 2011
  Really great and helpful post !!