Querying Microsoft.Uml2 for Class Data – Part 2

In part 1 of this series I illustrated querying the Microsoft.Uml2 domain to get class data. In particular, I demonstrated how to query Microsoft.Uml2 in such a way to return the data in a manner that is conceptually similar to the way UML class data is displayed on the canvas of a UML authoring tool. Part 1 was about querying Microsoft.Uml2 in a manner to retrieve the attributes of a class. In this post I’ll continue along this path by building on the previous query work to include not just attribute data, but also operation data as well. As before, the T-SQL code in this post is authored to work with the SQL Server Modeling Services Nov 2009 CTP Release 2

Refresher

As a refresher from last time, the following UML class represents the running example for the queries against the UML domain:

 Here’s the T-SQL from part 1:

    1: select C.[Id] as ClassName, C.[Folder] as FolderId, C.[ElementKind] as ClassKind, C.[Name] as ClassName,
    2:        P.[Id] as PropertyId, P.[Name] as PropertyName, P.[Visibility], 
    3:        T.[Id] as TypeId, T.[Name] as TypeName
    4: from [Microsoft.Uml2].[Classes] C
    5:     inner join [Microsoft.Uml2].[Properties] P on (C.[Id] = P.[Class])
    6:     inner join [Microsoft.Uml2].[Types] T on (T.[Id] = P.[Type])
    7: where C.[Name] = 'individual' and C.[Folder] = [Repository.Item].[PathsFolder] ('MyModels/FourPlusOne')

As I mentioned in the last post, I’m purposefully giving up some efficiency in my queries with the goal of returning the data for a UML class that mimics the UML diagram above. The snippet above illustrates this concept by returning some redundant data with each class attribute.

Querying for Operations

As one would expect, the UML Superstructure specifies that a class owns a collection of UML operations. Therefore, to get data relating to the GetFirstName(), SetFirstName(), or GetLastName() operations, I need to access the Microsoft.Uml2.Operations view. With the following T-SQL I can start to get back the data I need regarding the Individual class’ operations:

    1: select C.[Id] as ClassName, C.[Folder] as FolderId, C.[ElementKind] as ClassKind, C.[Name] as ClassName,
    2:        O.[Id] as OperationId, O.[Name] as OperationName, O.[Visibility], O.[ClassPosition]
    3: from [Microsoft.Uml2].[Classes] C
    4:     inner join [Microsoft.Uml2].[Operations] O on (O.[Class] = C.[ID])
    5: where C.[Name] = 'individual' and C.[Folder] = [Repository.Item].[PathsFolder] ('MyModels/FourPlusOne')

As the snippet above shows, I added some aliases to assist with readability. Executing the query above in Microsoft SQL Server Management Studio (SSMS) gives the following results:

Not bad, the results above illustrate the list of Operations on the Individual class. In case you were curious, the ClassPosition column denotes the order of the operations within the class. Now I just need to get the params for the operations.

Adding Parameters

Getting the parameters for an operation is a straightforward join between the Microsoft.Uml2.Operations and Microsoft.Uml2.Parameters views. The following T-SQL snippet illustrates:

    1: select C.[Id] as ClassName, C.[Folder] as FolderId, C.[ElementKind] as ClassKind, C.[Name] as ClassName,
    2:        O.[Id] as OperationId, O.[Name] as OperationName, O.[Visibility], O.[ClassPosition],
    3:        Params.[Id] as ParameterID, Params.[Name] as ParamName, Params.[Direction] as ParamDirection,
    4:        Params.[OwnerFormalParamPosition] as ParamPosition
    5: from [Microsoft.Uml2].[Classes] C
    6:     inner join [Microsoft.Uml2].[Operations] O on (O.[Class] = C.[ID])
    7:         inner join [Microsoft.Uml2].[Parameters] Params on (Params.[Operation] = O.[Id])
    8: where C.[Name] = 'individual' and C.[Folder] = [Repository.Item].[PathsFolder] ('MyModels/FourPlusOne')

The above query produces the following result set. Similar to the ClassPosition column, the ParamPosition column below denotes the position of the parameter within the operation:

Sweet, if I add one last piece of data – the parameter type – then the returned data for operations on the class will be complete. One more quick join and I’ll be good to go. Here’s the updated T-SQL:

    1: select C.[Id] as ClassName, C.[Folder] as FolderId, C.[ElementKind] as ClassKind, C.[Name] as ClassName,
    2:        O.[Id] as OperationId, O.[Name] as OperationName, O.[Visibility], O.[ClassPosition],
    3:        Params.[Id] as ParameterID, Params.[Name] as ParamName, Params.[Direction] as ParamDirection,
    4:        Params.[OwnerFormalParamPosition] as ParamPosition,
    5:        T.[Name] as ParamType
    6: from [Microsoft.Uml2].[Classes] C
    7:     inner join [Microsoft.Uml2].[Operations] O on (O.[Class] = C.[ID])
    8:         inner join [Microsoft.Uml2].[Parameters] Params on (Params.[Operation] = O.[Id])
    9:             inner join [Microsoft.Uml2].[Types] T on (T.[Id] = Params.[Type])
   10: where C.[Name] = 'individual' and C.[Folder] = [Repository.Item].[PathsFolder] ('MyModels/FourPlusOne')

And the complete Operations result set:

Right, the above result set conceptually maps to the data that is typically displayed for a class’ operations on a diagramming canvas.

Next Time

I’ve now got two queries that, taken together, represent the complete picture of a UML class – complete from the perspective of UML diagramming canvas, that is. However, I think it might be interesting to combine the two queries and play around with the idea of what a combined query would look like.

As the Program Manager for the UML Domain, I’d be highly interested in any feedback (good, bad, or indifferent) from the community about making the UML domain more usable. Do the two queries I’ve built so far seem interesting? Useful? Could you see them being used in SQL Server Reporting Services (SSRS) reports? If not, what queries would be useful?

Until next time, thanx for reading!