Skip to main content

The material on this page is out of date. For the latest information on SQL Server Modeling-related technologies, i.e. “M”, “Quadrant”, SQL Server Modeling Services and the Repository, read this update.

Modeling in Text

(Edited) Transcript of Five-Part Video Series by David Langworthy
November 2009

Editor’s note: in the video series, David Langworthy intentionally talks through every keystroke in his presentations. For brevity, then, this transcript doesn’t follow the video word for word, but generally condenses those coding segments into something more appropriate for reading.

This transcript has been updated for the November 2009 SQL Server Modeling CTP; an update of the videos themselves will be coming soon.

Also note that "M" and "Intellipad" in this document refer to Microsoft code name "M" and Microsoft code name "Intellipad", both part of the SQL Server Modeling CTP technologies.

Hello, my name is Dave Langworthy. I’m an engineer on the SQL Server Modeling team and this is Modeling in Text.


Part 1: Modeling a Language

Watch video

We’ll start by running "Intellipad" with a new untitled buffer in which we enter a statement defined in some domain:

Chris is 24 years old.

 

Now we’re going to model this statement, switching to "three-pane" mode with Ctrl+Shift+T. [To do this, you’ll need a blank grammar file called contacts.mg. This can be easily created in Notepad or "Intellipad" by saving a blank file/buffer as contacts.mg.] When prompted for a grammar file, select the empty contacts.mg.

In three-pane mode we have input statements on the left, the model in the middle, and the output on the right. In the middle pane, all models start with a module statement, and we’re going to model a language called Contacts:

module NovCTP {
    language Contacts {
    }
}

 

A language consists of a collection of rules for recognizing syntax, and it always begins with the distinguished syntax rule called Main:

module NovCTP {
    language Contacts {
        syntax Main = "Chris is 24 years old.";
    }
}

 

So now I have a statement, a model that recognizes that statement, and some data in the output pane that is extracted from that statement:

Main[
  "Chris is 24 years old."
]

 

This works, but it is very brittle: if we change anything in the statement (like "Chris" to "Pat") we get tons of errors because the model I’ve created recognizes only one exact statement.

So we want to parameterize the model to accept many statements of this form (different names and different ages). First we want to break the statement up into different pieces called tokens [the technical term for this is "tokenizing"—a token is something that’s indivisible, that is, can’t be broken into smaller parts]:

module NovCTP {
    language Contacts {
        syntax Main = "Chris" "is" "24" "years" "old.";
    }
}

 

The data (in the output pane) gets broken apart, but we have errors showing in the input pane because of whitespace. [And note that just by creating a model of the language we automatically get a syntax-directed editor in the input pane as you’d expect in a modern integrated development environment.] What we need is an interleave rule that just says to ignore the whitespace:

interleave Whitespace = " ";

 

With that we should get our data back, now broken up into little pieces. We’re still brittle, though, as changing Chris to Pat produces errors. So let’s parameterize to accept any name and any age, creating token rules for them:

module NovCTP {
    language Contacts {
        syntax Main = Name "is" Age "years" "old.";
        token Name = ('a'..'z' | 'A'..'Z')+;
        token Age = '0'..'9'+;
        interleave Whitespace = " ";
    }
}

 

Age is just one or more numbers [".." is the range operator meaning "through" and "+" means one or more; zero or more is "*"]. Name is much the same, one or more ("+") of lowercase ‘a’ through ‘z’ or (|) uppercase ‘A’ through ‘Z’. [Obviously this is not robust for names that use extended characters, but it shows the idea.]

Now we can go and use any name and any age in the input pane, such as "Pat is 23 years old" and it flows to the output.

We have a model now that’s somewhat flexible but if we input multiple statements we don’t get any data out and we have some errors:

Pat is 24 years old.
Chris is 32 years old.
Granny is 98 years old.
Billy is 3 years old.

 

The reason (as shown in the error pane) is we’re not ignoring other kinds of whitespace, like carriage returns and line feeds, so we need to add those to the Whitespace interleave:

interleave Whitespace = " " | "\r" | "\n";

 

The remaining error is because the model is written to only accept one statement. So I’m going to create another rule to factor the Main syntax rule into two, one called People so that Main is then a collection (*) of People:

syntax Main = Person*;
syntax Person = Name "is" Age "years" "old.";

 

Now the model recognizes all of those statements and produces all the appropriate data in the output pane:

Main[
  [
   Person[
      "Pat",
     "is",
      "24",
      "years",
      "old."
    ],
    Person[
      "Chris",
      "is",
      "32",
      "years",
      "old."
    ],
    Person[
      "Granny",
      "is",
      "98",
      "years",
      "old."
    ],
    Person[
      "Billy",
      "is",
      "3",
      "years",
      "old."
    ]
  ]
]

 

Now much of the output data is rather noisy, and we want to get rid of the extra repeated information (leaving just the name and the age). We’ll do that with a projector (=>):

syntax Person = n:Name "is" a:Age "years" "old."
  => { Name => n, Age => a};

 

where n and a are variables that we bind to the tokens as with n:Name and a:Age. This gives us a cleaner set of data that looks a lot more like records [entities or name-value pairs]:

Main[
  [
    {
      Name => "Pat",
      Age => "24"
    },
    {
      Name => "Chris",
      Age => "32"
    },
    {
      Name => "Granny",
      Age => "98"
    },
    {
      Name => "Billy",
      Age => "3"
    }
  ]
]

 

One more thing now, the collection has a somewhat arbitrary name, so I’m going to name that People, where valuesof is simply how we reference a collection:

syntax Main = p:Person*
  => People { valuesof(p) };

 

So we have our statements in a domain, a model of that domain, and standardized data output that’s "M" instance data with which we can do all sort of things. And that’s what we’ll do next after we save the input as contacts.dsl and save contacts.mg.

[Here also is the completed contacts.mg:]

module NovCTP {
    language Contacts {
        syntax Main = p:Person*
            => People { valuesof(p) };
        syntax Person = n:Name "is" a:Age "years" "old."
            => { Name => n, Age => a};
        token Name = ('a'..'z' | 'A'..'Z')+;
        token Age = '0'..'9'+;
        interleave Whitespace = " " | "\r" | "\n";
    }
}

 

 

Part 2: Data

Watch video

In Part 1 we modeled a domain-specific language. Now we’ll go to the command line tools and work with that data. We have two files at this point, contacts.mg (the grammar) and contacts.dsl (the language input code). Note that title.txt is the title slide for the beginning of this video.

The first thing we need to do is compile the language with the m.exe compiler from a standard command shell:

m contacts.mg

 

This will produce an image file (contacts.mx) that we can then use with the image utility, mgx.exe to translate that .dsl file into different forms that we might be interested in:

mgx contacts.dsl /r:contacts.mx /t:xaml

 

This compiles the input file (contacts.dsl) into XAML, referencing the compiled image (contacts.mx). [The XAML is omitted here for brevity.]

We can also send this same data to SQL via "M", compiling it again using the defaults.

mgx contacts.dsl /r:contacts.mx

 

This produces a contacts.m file:

module Contacts {
    People {
        {
            Name => "Pat",
            Age => "24"
        },
        {
            Name => "Chris",
            Age => "32"
        },
        {
            Name => "Granny",
            Age => "98"
        },
        {
            Name => "Billy",
            Age => "3"
        }
    }
}

 

Here we see an "M" program that’s been generated from the DSL. It has a module, Contacts, and People, which is an extent in which are records with the names and ages. We can send this onto SQL using the "M" compiler:

m contacts.m

 

This produces another image file (contacts.mx) that we can then load into a new database using the mx.exe tool: [Note: to run this command with SQL Server Express add "/s:.\SQLEXPRESS" at the end of the line.]

mx install contacts.mx /c /d:NovCTP /s:.\SQLEXPRESS

 

where /c instructs mx.exe to create a database using the name specified with /d. Switching over to SQL Management Studio (or any tool of your choice) we can see what’s there; we see our table under Databases\mayctp\Tables\Contacts.People (along with some other catalog tables that we’ll discuss later).

Selecting the Edit Top 200 Rows menu item on this table we see all our data in SQL now, which is great! We can then write a little query over that to extract the names:

select Name from contacts.People

 

[In code name "Quadrant", you can open the NovCTP database using the File | New | Session command, then select File | New | Workpad and just enter "contacts.People" in the query bar for the same results.]

Remember here that all we typed in "Intellipad" was the contacts.dsl file and the small contacts.mg grammar: just the domain statements and the model. We then simply utilized the command line tools to bring it all into SQL where we could write transformations. Turns out we can also write these transformations in "M" itself, which we’ll do in Part 3.

 

Part 3: Transformation

Watch video

In Parts 1 and 2 we modeled a language, got some data out (using an input file in that language), and sent it off to SQL. Now we’ll write some transformations in "M" itself.

[After closing all other open files in "Intellipad"] I’ll first open contacts.m, which we generated in Part 2, and I’m going to write the same query in we saw in SQL directly in M, using LINQ-style syntax [adding it just before the last curly brace]:

Names () { from p in People select p.Name }

 

[In a real project, by the way, you’d usually have a project file with the language in another file and the transformations in another, constraints in another. But for this demonstration we’ll just have the transformation directly in this one file.]

If we were to take this and run it all the way through the toolchain, it would show up in SQL as a view. Fortunately we don’t have to do that. In "Intellipad" we can go to the "M Mode" menu and select "T-SQL Preview", and that opens a pane showing all the SQL that the module would generate.

[Note: in the May 09 CTP (and the video), there are multiple insert into clauses each with a single value; in the November 09 CTP the generated T-SQL contains a single insert into clause with multiple values as shown here. The create schema clause also appears differently.]

set xact_abort on;
go
begin transaction;
go
set ansi_nulls on;
go
if not exists
(
select *
from [sys].[schemas]
where [name] = N'contacts'
)
execute [sp_executesql] N'create schema [contacts]';
go
create table [contacts].[People]
(
  [Age] nvarchar(max) not null,
  [Name] nvarchar(max) not null
);
go
create view [contacts].[Names]
(
  [Item]
)
as
  select [p].[Name] as [Item]
  from [Contacts].[People] as [p];
go
insert into [contacts].[People] ([Name], [Age])
values (N'Pat', N'24'),
(N'Chris', N'32'),
(N'Granny', N'98'),
(N'Billy', N'3');

go
commit transaction;
go

 

Let’s take a second to see how things map from "M" to SQL. The module name Contacts projects as the schema name, the extent name People projects as a table name, and each field in an entity projects as a column in the table, as you would expect. Down below you see the insert statements for each value in the "M" file, and we see that the "computed value" (the transformation or function) becomes a view with exactly the SQL you’d expect.

Now "M" support a more compact form for common queries, so we can write the transformation like this:

Names () { People select value.Name }

 

Where value(called "infix select") ranges over each element of the collection and produces almost exactly the same SQL as before. [After the select statement you can also have any complex expression you want.] [Note: in the Nov 09 CTP the select statement in the view changes from select [p].[Name] as [Item] to select [$value].[Name] as [Item] .]

Functions can also take parameters:

PeopleNamed(n:Text) {
    from p in People where p.Name == n select p
}

 

This becomes a table value function in the generated SQL: [there are slight differences here between the May 09 CTP and the Nov 09 CTP, but they are cosmetic.]

create function [contacts].[PeopleNamed]
(
  @n as nvarchar(max)
)
returns table
  as return (
    select [p].[Name] as [Name], [p].[Age] as [Age]
    from [Contacts].[People] as [p]
    where [p].[Name] = @n
  )
go

 

Again we have a compact form for this:

PeopleNamed(n:Text) {
    People where value.Name == n
}

 

and the function is the same as before with just a slight variance in variable names: [with slight cosmetic differences between May and Nov CTP's]

create function [Contacts].[PeopleNamed]
(
  @n as nvarchar(max)
)
returns table
  as return (
    select [$value].[Name] as [Name], [$value].[Age] as [Age]
    from [Contacts].[People] as [$value]
    where [$value].[Name] = @n
  )
Go

 

And we can go even further with this: for these common cases, value can be put directly on the collection:

PeopleNamed(n:Text) {
    People.Name(n)
}

 

which will pull out the values from People where Name is equal to the value of the parameter n. We can do the same with extracting the whole column:

Names () { People.Name }

 

This results in the same view as before just again with slightly different variable names. In these compact forms, the final SQL is as follows: [again with slight cosmetic differences between May and Nov CTP's]

create function [contacts].[PeopleNamed]
(
  @n as nvarchar(max)
)
returns table
  as return (
    select [$temp].[Name] as [Name], [$temp].[Age] as [Age]
    from [Contacts].[People] as [$temp]
    where [$temp].[Name] = @n
  )
go
create view [Contacts].[Names]
(
  [Item]
)
as
  select [$temp].[Name] as [Item]
  from [Contacts].[People] as [$temp];
go

 

This is the core of transformations, and in Part 4 we’ll talk about constraints.

 

Part 4: Constraints

Watch video

Now let’s look at constraints. The data we have so is just data in the wild, coming to us in whatever form it has (and we’ve transformed and generated SQL without question). But there are many reasons why we might constrain the data, perhaps to have a more efficient storage representation or we might constrain it just to say that we expect all the information to be in a certain form.

For example, right now our model just loves all data, so we can have:

Age = "24"

or

Age = 24

 

It’s all data, it’s fine, we love it, but Age shows up in SQL as a sql_variant type which is inefficient and can be difficult to work with. So let’s put a constraint on it that says the Name field has to be text and the Age field an integer. At the top of the module, then, we add this: [The second set of curly braces between the first colon and the last semicolon are needed in the Nov 09 CTP but not the May 09 CTP]

People : {{
    Name : Text;
    Age : Integer32;
}*};

 

Where the * means that People is zero of more of the Name/Age value pairs. With just this we’ll have some errors because of the quote marks around Age values—the data does not "fit" that constraint. Fixing those will produce the SQL again. And now instead of

[Age] sql_variant not null,

 

we have:

[Age] int not null,

 

and the insert statements have all been updated appropriately. And that’s a simple constraint.

[For reference, here is the full contacts.m at this point; the Nov 09 CTP version]

module contacts {
    People : {{
        Name : Text;
        Age : Integer32;
    }*};
    People {
        {
            Name => "Pat",
            Age => 24
        },
        {
            Name => "Chris",
            Age => 32
        },
        {
            Name => "Granny",
            Age => 98
        },
        {
            Name => "Billy",
            Age => 3
        }
    }
    PeopleNamed(n:Text) {
        People.Name(n)
    }
    Names () { People.Name }
}

 

 

Part 5: Identity and Relationships

Watch video

So far we’ve been dealing with isolated facts, all of which is nice, but what’s really interesting about data are the relationships between that data.

In order to refer to a piece of data we need an identity, so we’ll add an identity field to People with a constraint: [second set of curly braces added for Nov 09 CTP]

People : {{
    Id : Integer32 => AutoNumber();
    Name : Text;
    Age : Integer32;
}*} where identity Id;

 

Note that the where clause can be used in constraints as in queries, and AutoNumber() creates an auto-increment value as you often use.

[In SQL we see this projecting as a primary key in the last line of the create table statement:]

create table [contacts].[People]
(
  [Id] int not null identity,
  [Name] nvarchar(max) not null,
  [Age] int not null,
  constraint [PK_People] primary key clustered ([Id])
);

 

We can now create a relationship for these to participate in. We’ll call this relationship Marriages: [extra curly braces needed for Nov 09 CTP]

Marriages : {{
    SpouseA : People;
    SpouseB : People;
}*};

 

and out of this we get a table with the fields of the identity type and foreign key constraints because the SpouseA and SpouseBfields must come from the People extent.

create table [Contacts].[Marriages]
(
  [SpouseA] int not null,
  [SpouseB] int not null,
  constraint [FK_Marriages_SpouseA_Contacts_People] foreign key ([SpouseA]) references [Contacts].[People] ([Id]),
  constraint [FK_Marriages_SpouseB_Contacts_People] foreign key ([SpouseB]) references [Contacts].[People] ([Id])
);
go

 

(Notice that I used an extent name as a type which says both what needs to go in there and also where it is.)

With that we can start putting values into Marriages, and I’d like to say that Pat is married to Chris. In "M" we can label fields:

Pat {
  Name => "Pat",
  Age => 24
},
Chris {
  Name => "Chris",
  Age => 32
},

 

and so on, so we can then type this:

Marriages {
    { SpouseA => People.Pat, SpouseB => People.Chris }
}

 

The compiler will figure out the order of the SQL insert statements, by the way, so despite the fact that we’ve initialized the Marriages extent before People, the insertions into People occur in the generated SQL before this insert into Marriages: [exact T-SQL differs from May to Nov CTP; Nov CTP version shown here]

insert into [contacts].[Marriages] ([SpouseA], [SpouseB])
    values (@seed_contacts_People + @increment_contacts_People * 1, @seed_contacts_People + @increment_contacts_People * 2);
go

 

Now I want to do one more thing, which is to use more sophisticated constraints to ensure that the people participating in a marriage are not married to themselves:

Marriages : {({
    SpouseA : People;
    SpouseB : People;
} where value.SpouseA != value.SpouseB)*};

 

Note the use of parentheses to make the * bind to the entire expression. In the SQL we get a function that checks the constraint for us and adds that check constraint to the table.

create function [contacts].[Check_Marriages_Func]
(
@SpouseA as int,
@SpouseB as int
)
returns bit
as
    begin
        return case
            when @SpouseA <> @SpouseB
            then 1
            else 0
        end
    end;
go

 

We could, of course, put other constraints on the relationship as well, but for now, that's the end of our overview through Modeling in Text.

Microsoft is conducting an online survey to understand your opinion of the MSDN Web site. If you choose to participate, the online survey will be presented to you when you leave the MSDN Web site.

Would you like to participate?