Posts tagged UML

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.