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 !!