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

If you are a more functional focussed person, you are probably thinking: who cares. And if you are, please tell me why do you functional people talk so much with such a low information density? Is it thinking out loud?

Okay. Back to topic.

If you are a technical übermensch – like yours truly – you probably sh*t on people who do not see the delicate difference between a uses and an extends relationship.

In short, a tiny refresher for your brain.

• Uses (formerly known as include) indicates a “has a” relationship, and
• Extends (formerly known as extend) indicates an “is a” relationship

.

As you can see I still use pre-historic diagramming. Not only because I am wise and therefore somewhat older(fashioned), but merely because I use a freely available and (almost) open source diagramming tool StarUML. This tool unfortunately stuck at UML v1.3 Notwithstanding, an excellent tool.

Translated to a real world situation:

If you are a car driver you want to drive a car. Something you must include at least once is to start the engine. An extension of driving a car is driving a formula 1 car, which indeed is something very special.

Tweet

Sometimes, you want to do some simple validation. For example, data should not exceed the reserved storage size for the field in the database.

In this example CustomerName must be less than 10 characters. So, the client code should look like this:

Customer customer = new Customer();

// customer name must be less than 10 characters
customer.CustomerName = this.textBox1.Text;

// Validation facade
ValidationResults results = Validation.Validate<Customer>(customer);

// populate listbox with validation results
foreach (ValidationResult result in results)
{
    this.listBox1.Items.Add(result.Message);
}

The customer name is filled with input from a textbox. The complete customer object is validated using a validation facade. Finally, a list box is populated with the validation results.

Use of a facade simplifies use of validation in the client. The facade itself looks like this:

public static class Validation
{
    public static ValidationResults Validate<T>(T validationobject)
    {
        Validator<T> validator = ValidationFactory.CreateValidator<T>();
        ValidationResults results = validator.Validate(validationobject);

        return results;
    }
}

But wait! This looks exactly like the Validation Application Block. That’s a coincidence!

Indeed. Since the callings are similar you easily can replace the plumbing with the real stuff later on if you want.

But be aware that there is a small difference. This simplified version does not use attributes, but self validation by implementing an ISelfValidation interface.

public interface ISelfValidation
{
    void Validate(ValidationResults results);
}

The object in question implements ISelfValidation as follows:

class Customer : ISelfValidation
{
    public string CustomerName { get; set; }

    public void Validate(ValidationResults results)
    {
        int maxLength = 10;
        string validationString = CustomerName;

        ValidateStringLength(results, validationString, maxLength);
    }

    // this can be put into a helper class if you want
    private static void ValidateStringLength(ValidationResults results, string validationstring, int maxlength)
    {
        if (validationstring.Length > maxlength)
        {
            results.Add(new ValidationResult(String.Format("Length must be less than {0}", maxlength)));
        }
    }
}

This way simple validation becomes very easy. So, kids please do try this at home.

Download(s) : [Download not found]

Tweet

Did you ever used Eclipse? Never? Then, now is the time!

Lets generate some crap…

1. So, first we need Eclipse. Next, install the open-source modeling tool Topcased and code generator Acceleo into Eclipse.
2. Next, spend a couple of hours in finding out how it all should work together.(To be honest, it didn’t take hours but – that’s why I don’t like using Eclipse – it took me too long to configure and make it work)
3. Model something (*.umldi), for example a silly 101 school model. You see them often, especially here.
4. Create a stunning generator-template (*.mt)
5. Generate some code (*.cs), and finally
6. Compile the generated code in your – and mine, of course – ***favorite*** integrated development environment. Wow, man. I created real compilable C# code.

Thus, nice work.