Url: http://wilderminds.blob.core.windows.net/downloads/StudentSchema.zip

As most of you know, I run a small training company. We are in the midst of a re-write of our main website to allow for more cohesive registration and information about our classes. For this project, I am completely re-creating my database schema (as the old one was a bit ‘off the cuff’). What a great opportunity to try out MSchema to build the new schema.

I am going to show you some features in three parts.  In this first part I will show you how to define some interesting data types in MSchema. In the second part I will show you how to define your static or test data in MGraph that can be inserted into the database along with your schema. Then finally in the third part I will show you how to use the command-line tools to build your schema from the M files.  Onto the first part!

I’ll admit, I hate visual editors. In fact, in his PDC talk, it was as if Doug Purdy was talking to me when he said we have an emotional connection to our text editors. I have spent some time with emacs.NET IntelliPad and so far I am impressed. It is taking some getting used to but the extensibility is pretty impressive.

I like the way that MSchema forces me to create a type then push it into an extent (e.g. to make it a table). For example, I started with a Workshop type that defined a training topic:

module Training 
{
  type WorkshopDef
  {
    WorkshopId : Integer32 = AutoNumber();
    Name : Text#100;
    Description : Text?;
    Markup : Text?;
    Length : Integer32;
    MaxAttendees : Integer8;
    DefaultPrice : Decimal19;
  } where identity WorkshopId;
}

I start with a module which just creates something like a namespace for our types and extents.  This module will become the Schema in SQL Server for our tables and views. Next, I created a type with the fields I wanted.  You notice that I post-pended “Def” on the type. This is mostly just a convention I made up so that I could name the collections singular (we’ll see more about that later when we see the generated schema). You can see the fields for my type and the constraints I am specifying (e.g. Text#100 is the same as saying Text where value.length <= 100). Like in C#, the question mark says that the field is nullable. Finally, I specify that the type has an identity using the field(s) that make up my primary key.

At this point all I have is a type, no tables or view or anything really. I need to extend this into a collection:

  Workshop : WorkshopDef*;

This extent simply says create some place to hold things that look like WorkshopDef and call it Workshop.  The asterisk at the end of WorkshopDef implies zero or more (like in regular expressions). This set of schema would yield a small amount of SQL:

create schema [Training];
go

create table [Training].[Workshop]
(
  [WorkshopId] int not null identity,
  [DefaultPrice] decimal(19,6) not null,
  [Description] nvarchar(max) null,
  [Length] int not null,
  [Markup] nvarchar(max) null,
  [MaxAttendees] int not null,
  [Name] nvarchar(100) not null,
  constraint [PK_Workshop] primary key clustered ([WorkshopId])
);
go

You can see the schema created closely matches our type definitions. That’s the cool factor for me.  Let’s do something more interesting. For each Workshop I have a list of Topics (and eventually sub-topics). These are bullet point items that show what is covered in a workshop. So I can add a new type like so:

type TopicDef
{
  TopicId : Integer32 = AutoNumber();
  WorkshopId : WorkshopDef;
  Name : Text#100;
} where identity TopicId;

Note that when I define that I want a WorkshopId to be a reference to another type, I specify it as the type I am referring to. This means in our extent for Topics we can create the foreign key like so:

Topic : TopicDef*
  where item.WorkshopId in Workshop;

This essentially says that we want topics, but be sure that WorkshopId are in the Workshop table (the foreign key). The next thing to show is the real power of constraints. If you saw the Doug Purdy talk you probably saw that you could create a constraint for simple data like so:

  type AttendeeDef
  {
    AttendeeId : Integer32 = AutoNumber();
    FirstName : Text#50;
    LastName : Text#50;
    MiddleName : (Text#50)?;
    DateRegistered : Date;
    HasCancelled : Logical = false;
    Phone : (Text#50)?;
    Email : (Text#100)?;
    TShirtSize : Integer32 where value <= 5;
  } where identity AttendeeId;

In the TShirtSize, notice how I am constraining the value to a range of numbers from zero to five. This may be a magic way of translating from small to 3x. The SQL actually works as it creates a check constraint:

create table [Training].[Attendee]
(
  [AttendeeId] int not null identity,
  [DateRegistered] date not null,
  [Email] nvarchar(100) null,
  [FirstName] nvarchar(50) not null,
  [HasCancelled] bit not null default 0,
  [LastName] nvarchar(50) not null,
  [MiddleName] nvarchar(50) null,
  [Phone] nvarchar(50) null,
  [TShirtSize] int not null,
  constraint [PK_Attendee] primary key clustered ([AttendeeId]),
  constraint [Check_Attendee] check ([Training].[Check_Attendee_Func](
    [AttendeeId], 
    [DateRegistered], 
    [Email], 
    [FirstName], 
    [HasCancelled], 
    [LastName], 
    [MiddleName], 
    [Phone], 
    [TShirtSize]) = 1));
go

The Check_Attendee_Func is a generated function to test for constraints (including the size of TShirtSize. But that is making TShirtSize a magic number.  I don’t like magic numbers.  Let’s create something like an enumeration instead:

  type TShirtSizeEnum
  {
    "Small",
    "Medium", 
    "Large",
    "X-Large",
    "2X",
    "3X"
  };

  type AttendeeDef
  {
    AttendeeId : Integer32 = AutoNumber();
    FirstName : Text#50;
    LastName : Text#50;
    MiddleName : (Text#50)?;
    DateRegistered : Date;
    HasCancelled : Logical = false;
    Phone : (Text#50)?;
    Email : (Text#100)?;
    TShirtSize : TShirtSizeEnum;
  } where identity AttendeeId;

The new TShirtSizeEnum type can be used to constrain the value of the TShirtSize field.  How do we do that?  We simply tell MSchema that we are going to store a TShirtSizeEnum in that field and it creates it in the generated SQL.