ZenArchitect.NL (Henk van Dijken)

Tweet

In this mini-series of one post, I’ll show you how to generate SQL-script from an UML model.

First, you need a model. This can be any model, but here I use an UML model. The class diagram on the left shows a simple model consisting of a parent class and a related child class. Both are stereotyped <<table>> so you can see we are talking about a data model.

Both have some attributes. Note that their types have been declared explicitly.

The interesting part is their association. As you can see it is a traditional master-detail relationship. For clarity I have named the association and both association ends, but the latter is not strictly necessary.

This UML diagram can be represented in the following XML form:

<?xml version="1.0" encoding="utf-8"?>
<EntityModel>
  <Entity Name="TestParent">
    <Property Name="TestParentName1" Type="String" />
    <Property Name="TestParentNumber" Type="Integer" />
  </Entity>
  <Entity Name="TestChild">
    <Property Name="TestChildName" Type="String" />
  </Entity>
  <Association Name="FK_TestParent_TestChild">
    <End Name="TestParent_End" Role="TestParent" Multiplicity="0..1" />
    <End Name="TestChild_End" Role="TestChild" Multiplicity="*" />
  </Association>
</EntityModel>

Why that is interesting? That is because XML easily can be transformed into something useful.

For performing the actual transformation you can use, for example:

1. XSLT stylesheet-driven transformation
2. template-driven code generator
3. code using XmlSerializer or XmlReader

Thus, it is transformed into a sql-script.

/****** Table [dbo].[TestParent] ******/
CREATE TABLE [dbo].[TestParent](
	[TestParentID] [int] IDENTITY(1,1) NOT NULL,
	[TestParentName1] [varchar](MAX) NULL,
	[TestParentNumber] [int] NULL,
 CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED ([TestParentID] ASC)
)
GO
/****** Table [dbo].[TestChild] ******/
CREATE TABLE [dbo].[TestChild](
	[TestChildID] [int] IDENTITY(1,1) NOT NULL,
	[TestParentID] [int] NULL,
	[TestChildName] [varchar](50) NULL,
 CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED ([TestChildID] ASC)
)
GO
/****** ForeignKey [FK_TestParent_TestChild] ******/
ALTER TABLE [dbo].[TestChild]
ADD CONSTRAINT [FK_TestParent_TestChild] FOREIGN KEY([TestParentID])
REFERENCES [dbo].[TestParent] ([TestParentID])
GO

The keen eye will notice that in the final sql-script primary and foreign key columns are introduced, which are not present in the original model (diagram nor xml file). The other way around, specification of associations in the original model is much richer than the resulting foreign key constraint in the sql-script. These “mismatches” must be handled and are typically present when performing O/RM mapping.

Tweet

Do you know FxCop?

FxCop is an application that analyzes managed code assemblies. Many of the issues concern violations of the programming and design rules set forth in Design Guidelines.

Today, I announce my wish for an equivalent for SQL Database Analysis.

As it happens over time a database structure grows, and grows. Sometimes, it grows like wild grass. At other times, the database is restructured by someone who smoked to much wild grass.

There are two problems here:

1. it is difficult to know in which shape the database its structure is,
2. you are desperately in ***need** of serious tooling.

For .NET code you can use FxCop or StyleCop, but serious tooling is lacking for databases.

Therefore – all you tool belt lovers – lets unite and demand for a tool called SqlCop.