Exploring relational schema for .NET Assemblies in SQL Server Modeling Services

As I mentioned in my About SQL Server Modeling Services post, the goal of SQL Server Modeling Services (SSMoS) is to provide a shared application lifecycle metadata platform over which people, tools and runtimes can rendezvous in an application lifecycle. .NET Assemblies are just one category of assets in the lifecycle which will be modeled in the SSMoS platform.

In this blog post, let me provide you a high level overview of the schema we have created for representing .NET Common Language Runtime (CLR) managed assemblies in a relational model. We authored the schemas using “M” language. You can browse the “M” source yourself by downloading and installing SQL Server Modeling CTP – November 2009. Note that the schema is subject to change in the future public releases of SSMoS.

We believe the CLR relational model can easily become familiar to .NET developers who build managed .NET applications. There is a sea of information available on CLR on the net, but here is a quick refresher using some most frequently used concepts. The .NET Framework provides a run-time environment called the CLR which runs the application code written by developers. A .NET Assembly (.DLL and/or .EXE) is a building block of .NET application and interacts with the CLR to provide it the type implementations.

CLR Component	Corresponding “M” Extent	Description
Assembly	AssembliesTable	Contains code that CLR executes. Smallest unit of deployment.
Module	ModulesTables	Module is either a DLL or EXE file.
Type	TypesTable	Basic unit for encapsulating data and behavior. Can be an interface or class and has properties, methods and fields.
Method	MethodsTable	Public procedure that defines behavior of a class. Can have parameters and return values.
Field	FieldsTable	Variables used to hold values.
Property	PropertiesTable	Properties are like fields, but hold code behind them.
Namespace	NamespacesTable	Logically groups a set of classes. It can span multiple assemblies.

I used Microsoft Visio 2007 to reverse engineer the System.Runtime model installed in SQL Server Modeling Services database in the SQL Server 2008 R2 instance. The default diagram generated contains a bunch of lines and boxes going all across and generally not that helpful (see thumbnail below). I also believe System.Runtime model is significantly simpler than the UML CMOF model which by default has many complex relationships. Instead of just putting the entire diagram up, I have decided to break it into chunks and explain some core entities and their relationships with each other.

Most basic hierarchy for .NET code is: Assembly à Module à Type à Method.

To verify whether System.Runtime schemas are installed in your instances of the SQL Server database, you can look for tables that are prefixed with System_Runtime.* in your favorite database tool (e.g. SQL Server Management Studio). You can also check the language catalog for existance of System.Runtime model schema. Run “Select * from Language.Catalog.Runtime.Images” . If only System.Runtime schemas was installed, you will see at least the following rows:

· Catalog

· Repository

· System.Runtime

Let’s browse “M” code behind one of the entities: AssembliesTable.

Assemblies.m

module System_Runtime

{

import System;

export AssembliesTable;

AssembliesTable :

{(

HasFolderAndId &

AssemblyInfo &

{

PublicKey : Binary?;

Attributes : Xml?;

Documentation : Xml?;

}

)*};

}

Notice that there are few additional attributes in the actual AssembliesTable table. They came from the following two additional “M” declarations in AssemblyInfo.m and HasFolderAndId.m. HasFolderAndId is part of the base domain library of SSMoS.

AssemblyInfo.m

module System_Runtime

{

type AssemblyInfo :

{

Name : IndexableText;

VersionMajor : PositiveInteger;

VersionMinor : PositiveInteger;

VersionRevision : PositiveInteger;

VersionBuild : PositiveInteger;

Culture : Text where value.Count <= 85;

PublicKeyToken : (Binary where value.Count == 8)?;

};

}

The use of HasFolderAndId allows the model author to opt-in into one of the core SSMoS repository patterns: Folder. SSMoS Folders provide a partitioning mechanism for data in the SSMoS database.

The following ER diagram shows additional entities in the System.Runtime schema.

In my next post, I will drill-into other schema entities of the CLR model.