In Part I of this series we looked at the tool Telerik is building and how to model an entity in MSchema and MGraph. Part II dug deeper into the modeling process and we saw the value of MGraph and data visualization to help your model along. We did a little refactoring and are now more or less happy with the model for these domain entities. After modeling the application in M, I realize the power of a textual modeling language. Boxes and lines in UML does not excite me, but a textual modeling language makes complete sense.

So far we have ignored the fact that these entities will live in a database. You can push your M into the repository or you can push it to plain old TSQL. Let’s do that today.

Inside of iPad you can go to the “M Mode” menu option and choose “Generic TSQL Preview.” This will split your code with the M code on one side and the TSQL on the other as shown below.  (Note, you can also choose M Mode|Repository TSQL Preview, however I am still avoiding the Oslo repository at the moment. I have my M files under source control in TFS and will push to the repository a little alter in the process. Once again, I am still learning, so this may or may not be a best practice.)

Let’s take a look at the TSQL produced.

This type that we build in Part I:

//mschema to define a user type
type ApplicationUser
{
    UserID : Integer64=AutoNumber();
    FirstName :Text#15;
    LastName : Text#25;
    Password : Text#10;      
} where identity UserID;

Will produce a CREATE TABLE TSQL statement like this:

create table [Telerik.MigrationTool].[ApplicationUserCollection]
(
  [UserID] bigint not null identity,
  [FirstName] nvarchar(15) not null,
  [LastName] nvarchar(25) not null,
  [Password] nvarchar(10) not null,
  constraint [PK_ApplicationUserCollection] primary key clustered ([UserID])
);
go

Ok, a few things here. First my table name is [modulename].[MGraph instance name]

Ug! ApplicationUserCollection is a horrible name for a table. I incorrectly assumed that the type name would be what we have as a table name. (I guess I should have actually done the M labs at the last SDR instead of goofing off with Michelle Bustamante.) Well this is new technology, so live and learn. :) I have to refactor all my types and instances. I guess I have learned pretty quickly that “collection” is not a good name.

Here is the renamed base type, I named it “UserType” since I can’t think of a good name, however,  I will do this with all my types:

//mschema to define a user type
type UserType
{
    UserID : Integer64=AutoNumber();
    FirstName :Text#15;
    LastName : Text#25;
    Password : Text#10;      
} where identity UserID;

Here is the new MGraph, I am using ApplicationUser here instead of ApplicationUserCollection:

//mgraph to get some test data in
    ApplicationUser : UserType*; ApplicationUser
    {
        //using a named instance (Steve, etc)
        Steve {
        FirstName="Stephen",
        LastName="Forte",
        Password="Telerik"
        },
        Vassimo {
        FirstName="Vassil",
        LastName="Terziev",
        Password="123"
        },
        Zarko {
        FirstName="Svetozar",
        LastName="Georgiev",
        Password="456"
        },
        Todd {
        FirstName="Todd",
        LastName="Anglin",
        Password="789"
        }
    }

Now the M Mode|Generic TSQL Preview will show this:

create table [Telerik.MigrationTool].[ApplicationUser]
(
  [UserID] bigint not null identity,
  [FirstName] nvarchar(15) not null,
  [LastName] nvarchar(25) not null,
  [Password] nvarchar(10) not null,
  constraint [PK_ApplicationUser] primary key clustered ([UserID])
);
go

And the insert statements are also generated:

insert into [Telerik.MigrationTool].[ApplicationUser] ([FirstName], [LastName], [Password])
values (N'Stephen', N'Forte', N'Telerik');
declare @Telerik_MigrationTool_ApplicationUser_UserID0 bigint = @@identity;

insert into [Telerik.MigrationTool].[ApplicationUser] ([FirstName], [LastName], [Password])
values (N'Vassil', N'Terziev', N'123');

insert into [Telerik.MigrationTool].[ApplicationUser] ([FirstName], [LastName], [Password])
values (N'Svetozar', N'Georgiev', N'456');
declare @Telerik_MigrationTool_ApplicationUser_UserID2 bigint = @@identity;

insert into [Telerik.MigrationTool].[ApplicationUser] ([FirstName], [LastName], [Password])
values (N'Todd', N'Anglin', N'789');

I ran the entire TSQL and then the next step is to load it into the database.

I opened SQL Management Studio and created a new database called oslotest1 as shown here:

create database oslotest1
go

Now I will copy and paste the TSQL in the preview pane of iPad and run it. Fingers crossed. :)

As you can see in the image below, all my tables were created successfully.

Let’s take a look at some of the sample data. A simple SELECT * FROM ApplicationUser shows us:

As you can see MGraph creates a SQL Server schema [Telerik.MigrationTool] out of the module name in our M file. This is a pretty cool feature (SQL 2005/08 schemas are not used enough, there is too much DBO floating around out there.) I guess I can use an easier to work with schema in the future like migrationtool instead of telerik.migrationtool.

Let’s now query some of the sample data in SQL Server. Here is the result of a query looking at the results of Project ID #1 and the first run of that project, all from the data that we modeled in MGraph:

I am pretty satisfied with the results of my model. I think the next step is to hand off the user stories and M code to the developers and get started. I will post their reactions, they know nothing about Oslo besides what they read in this blog. :) I will also post my progress and thinking on the repository. I think that now we are going to be working with a team (and a team in another country than me), we can get some benefits by using the repository.