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 (3)

  1. BertrandH says:

    Hi,

    Great post, I missed it.

    I recommend you cross post your stuff to http://blogs.msdn.com/vstsqualitytools

    Thanks

  2. thiru V says:

    TestResult table does not contain column name "TestPlanID". It would be greate if you can update the table structure

  3. Apurva Goyal says:

    Really great and helpful post !!