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!

Comments (3)
  1. Joe says:

    I find it kind of ironic that the modeling data is being accessed directly out of SQL Server and not through an abstraction.  Even an abstract meta-model based on the Entity Framework would be better that querying the DB directly – wouldn’t it?

    How does SQL Server Modeler help with branching/merging and synchronization to source control?  Why is this approach better than using the UML tools in VS2010?

  2. Dave Langer says:

    @Joe

    First – thanx for reading, much appreciated!

    As currently framed in the CTP, the UML Domain isn’t intended to be a UML authoring environment. As such, scenarios relating to source control are out of scope for the Domain.

    The intent of the UML Domain is to function as a common repository for UML data and as an analysis platform over consolidated UML data. Think "UML Data Mart". The "UML Data Mart" paradigm explains the use of the SQL Server platform (and the relational model) for the UML Domain.

    However, I’m very intrigued by your comment about EF. Currently the UML Domain’s views align to the UML Superstructure specification. An EF model over these views would number in the hundreds of classes. Would you find this useful/desirable, or would you hope for a higher level of abstraction (ala the idea in the post)?

    Thanx again for reading and the feedback!

    Dave

  3. Dave Langer says:

    @Joe

    BTW – The following PDC video illustrates the analysis aspect of the UML Domain in case you are interested:

    http://microsoftpdc.com/Sessions/SVR19

    Thanx,

    Dave

Comments are closed.

Skip to main content