Writing "M" Files

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

Any text editor can write and modify code files that are written in the Microsoft code name “M” language, but the SQL Server Modeling CTP provides additional support for “M” authors. The SQL Server Modeling CTP provides “M” integration with Visual Studio 2010, as well as the code name “Intellipad” tool, a stand-alone tool for writing “M” code. This topic reviews general guidance and suggestions for getting started with writing “M” code by using “Intellipad”. For more information about using Visual Studio 2010 for “M” development, see "M" Integration with Visual Studio 2010.

“Intellipad”

Open “Intellipad” by going to Start Menu, All Programs, Microsoft SQL Server Modeling CTP, and then click Intellipad. The following list describes several benefits in using “Intellipad” to write your “M” source files.

• Syntax highlight for “M” keywords.

• Compilation errors and warnings without running the “M” compiler separately.

• A preview of the translation of the “M” code into SQL Server 2008 database objects.

Syntax Highlighting

Syntax highlighting helps to distinguish “M” keywords from the rest of the source code. “Intellipad” displays “M” keywords in a bold font. Consider the following “M” code.

module Shape
{
    type Square
    {
        ID : Integer32;
        SideLength : Integer32;
    } where identity ID;
    
    Squares : { Square* };
}

In the preceding code example, “Intellipad” displays the following terms in a bold font: module, type, where, and identity.

Transact-SQL Preview and the Error List

“Intellipad” has a Transact-SQL preview feature to show the compilation results of the “M” code into Transact-SQL statements. This helps you observe dynamic changes to the SQL implementation of the “M” model as you change the “M” code.

Consider the following “M” code that defines a Shape module that contains a Square type and a related extent, Squares, which has a deliberate syntax error.

module Shape
{
    type Square
    {
        ID : Integer32;
        SideLength : Integer32;
    } where identity ID;
    
    Squares : { SSquare* };
}

To see how this “M” code translates to Transact-SQL, first make sure that this code is saved as a file with a .m extension. On the M Mode menu, click T-SQL Preview . In the preview window for the preceding “M” code, there is an error stating that the preview could not be generated. To see the error, on the View menu, click Show Errors. The Error List shows the error: the compiler cannot resolve the reference to SSquare, which is a misspelled type name. Changing SSquare to Square in the previous code corrects the error and displays the Transact-SQL translation for the Shape model. The following Transact-SQL is the preview of the “M” code.

set xact_abort on;
go

begin transaction;
go

set ansi_nulls on;
go

if not exists 
(
    select *
    from [sys].[schemas]
    where [name] = N'Shape'
)
    execute [sp_executesql] N'create schema [Shape]';
go

if not exists 
(
    select *
    from [sys].[schemas]
    where [name] = N'$MRuntime.Shape'
)
    execute [sp_executesql] N'create schema [$MRuntime.Shape]';
go

create table [Shape].[Squares]
(
    [ID] int not null,
    [SideLength] int not null,
    constraint [PK_Squares] primary key clustered ([ID])
);
go

create table [$MRuntime.Shape].[Squares_Labels]
(
    [Label] nvarchar(444) not null,
    [Value] int not null,
    constraint [PK_Squares_Labels] primary key clustered ([Label]),
    constraint [FK_Squares_Labels_Value_Shape_Squares] foreign key ([Value]) references [Shape].[Squares] ([ID]) on delete cascade
);
go

create index [IR_Value] on [$MRuntime.Shape].[Squares_Labels] ([Value]);
go

create function [$MRuntime.Shape].[LookupInSquares_Labels]
(
    @name as nvarchar(max)
)
returns table
as
    return
        select top (1)
            [t3].[ID] as [ID],
            [t3].[SideLength] as [SideLength]
        from [$MRuntime.Shape].[Squares_Labels] as [p]
        cross apply 
        (
            select [$Squares2].[ID] as [ID],
                [$Squares2].[SideLength] as [SideLength]
            from [Shape].[Squares] as [$Squares2]
            where [$Squares2].[ID] = [p].[Value]
        ) as [t3]
        where [p].[Label] = @name;
go

commit transaction;
go
set xact_abort on;
go

begin transaction;
go

set ansi_nulls on;
go

create schema [Shape];
go

create table [Shape].[Squares]
(
  [ID] int not null,
  [SideLength] int not null,
  constraint [PK_Squares] primary key clustered ([ID])
);
go

commit transaction;
go

The Transact-SQL translation creates several database tables for this simple model. Some of the tables represent the model, such as [Shape].[Squares], and other tables support “M” language features, such as [MRuntime.Shape].[Squares_Labels].

See Also

Concepts

M.exe Command Line Reference

Other Resources

Using "M" Tools for Data Modeling