Query Microsoft.Uml2 for Class Data – Part 3

Part 2 of this series provided a query, when combined with the query from Part 1 that allows for the querying of UML class data from the SQL Server Modeling Services (SSMoS) UML Domain in a manner analogous to the class data typically displayed on a UML canvas, where the queries illustrated in this series are compatible with the SSMoS Nov 2009 CTP R2 bits. As a refresher, the following UML class diagram illustrates the desired level of abstraction for data returned from the UML Domain:

Indivudal1

 

Querying Class Attributes

In Part 1, I walked through the construction of a query to retrieve the class attributes for the Individual class from the UML Domain. In keeping with the philosophy of this series, the results are geared to mimic the data typically displayed for UML class attributes on a prototypical UML tool’s diagram canvas. The following T-SQL query and result set summarize this work:

    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')

 

SSMS3

 

Querying for Class Operations

In Part 2, I walked through the construction of a query to retrieve the class operations for the Individual class from the UML Domain. As before, the results are geared to mimic the data typically displayed for UML class operations on a prototypical UML tool’s diagram canvas. The following T-SQL query and result set summarize this work:

    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')

 

SSMS6

 

Querying for Class Data – Better Together?

While the queries above are serviceable, they don’t quite mimic the same conceptual experience as working with a UML authoring tool. To that end, I started thinking about the shape of the data if I queried both class attributes and class operations in a single query. I won’t lie, I find this idea intriguing based on the fact that I’m an unrepentant Object-Oriented bigot. While I respect the power and mathematical beauty of the relational paradigm, I’m just more comfortable in OO land. In particular, I often visualize OO designs in my mind as UML (yes, I know that I’m sad and need to get a life ;-). As such, I was wondering how ugly it would be to bend SQL Server to my OO will.

NOTE – I’ve no doubt that many of the ideas, and code, in my blogging on querying the UML Domain will offend my T-SQL/Relational/SQL Server betters. However, IMHO addressing those of us with minds crippled by OO might find dealing with suboptimal T-SQL an excellent way of deriving value from the UML Domain. Let the flame mail begin :-).

In kicking around these ideas at a high level with Anthony Bloesch (BTW, don’t hold Anthony accountable for the content of these blog posts, he is an aforementioned SQL Server better of mine), it seemed like going the route of T-SQL unions was a superior approach to using Multiple Active Result Sets (MARS). The use of unions will allow me to get all of the UML class data in a single call and will also return it as a single result set. The downside will be a large collection of columns with many NULL values scattered throughout the result set.

Accepting the potential ugliness factor of using a T-SQL union, my first requirement in the new combined query is to add the placeholder columns needed for the class operations to the class attributes query. The following T-SQL illustrates this handiwork:

    1: select C.[Id] as ClassId, C.[Folder] as FolderId, C.[ElementKind] as ClassKind, C.[Name] as ClassName,
    2:        P.[Id] as PropertyId, P.[Name] as PropertyName, P.[Visibility] as PropertyVisibility, 
    3:        T.[Id] as PropertyTypeId, T.[Name] as PropertyTypeName,
    4:        null as OperationId, null as OperationName, null as OperationVisibility, null as OperationPosition,
    5:        null as ParameterID, null as ParamName, null as ParamDirection, null as ParamPosition,
    6:        null as ParamTypeName
    7: from [Microsoft.Uml2].[Classes] C
    8:     inner join [Microsoft.Uml2].[Properties] P on (C.[Id] = P.[Class])
    9:     inner join [Microsoft.Uml2].[Types] T on (T.[Id] = P.[Type])
   10: where C.[Name] = N'individual' and C.[Folder] = [Repository.Item].[PathsFolder] (N'MyModels/FourPlusOne')

 

Running the above T-SQL produces the following result set:

SSMS7

 

Modifying the class operations query

in the same way gives the following T-SQL and result set:

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

 

SSMS8

 

Note in the above T-SQL I made some minor changes to the alias names to standardize the result sets across queries. Simple enough, now I just sandwich the T-SQL union keyword between the above two queries to get all the data back in a single call. To make things a little more usable, I’ll add a column named “RowType” to provide some metadata denoting whether a result set row is a class property or class operation (got this idea from my Dev team – Thx to Steve and Thomas). Here’s the T-SQL and result set:

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

SSMS9

 

OK, the above result set is a tad on the ugly side, but I’m still intrigued by the possibilities of getting all the data for a class (from the perspective of a UML diagram canvas, that is) in a single call. I’m thinking this is worthy of one more post’s worth of exploration.

Next Time

Next time I’m going to explore using the above union query is even more interesting if it is converted to a SQL Server table-valued user-defined function (UDF). As always, any feedback from the community would be greatly appreciated.

Thanx for reading!