The "Oslo" Modeling Language Specification - SQL Mapping

Microsoft Corporation

May 2009

[This documentation targets the Microsoft "Oslo" May 2009 CTP and is subject to change in future releases. Blank topics are included as placeholders.]

Sections:
1: Introduction to "M"
2: Lexical Structure
3: Text Pattern Expressions
4: Productions
5: Rules
6: Languages
7: Types
8: Computed and Stored Values
9: Expressions
10: Module
11: Attributes
12: Catalog
13: SQL Mapping
14: Glossary

13 SQL Mapping

“M” compiles to Transact-SQL when using the /t:TSql10 switch of the compiler.  This document describes the SQL the “M” compiler emits for all supported syntax, and calls out what “M” features are unsupported in SQL compilation.

13.1 Basic Types

Scalar “M” types translate to scalar SQL types with the following mapping.  “Constraint” is used when the SQL type does not perfectly represent the “M” type—for example, when it allows a larger range of values than the corresponding “M” type.  In these cases a CHECK constraint will be emitted on the table restricting the range of the value.

 

“M” Type SQL Type Constraint

Integer8

smallint

-128 … 127

Integer16

smallint

 

Integer32

int

 

Integer64

bigint

 

Unsigned8

tinyint

 

Unsigned16

int

0 … 65535

Unsigned32

bigint

0 … 4294967295

Decimal9

decimal(9,6)

 

Decimal19

decimal(19,6)

 

Decimal28

decimal(28,6)

 

Decimal38

decimal(38,6)

 

Single

float(24)

 

Double

float(53)

 

DateTime

datetime

 

Date

date

 

Time

time

 

Logical

bit

 

Character

nchar(1)

 

Text

nvarchar(max)

 

Text#n

nvarchar(n)

 

Text where value.Count <= n

nvarchar(n)

Byte

tinyint

 

Binary

varbinary(max)

 

Guid

uniqueidentifier

 

General

sql_variant

 

 

13.1.1 Unsupported Scalar Types

No other scalar types are supported.  The unsupported scalar types are:

Type

Scientific

Unsigned

Integer

Decimal

Number

Unsigned64

13.2 Modules

 

“M”->SQL translates “M” "modules" to SQL "schemas."  Dots in module names are translated directly to schema names.  All tables, functions and views generated from extents and computed values defined in a module are placed within that schema.

 

               

M Example SQL Example

Module

module M {

    MyInt : Integer32;

}

create schema [M];

 

create table [M].[MyInt]

(

  [Item] int not null

);

Dotted Module

module M.N {

    MyInt : Integer32;

}

create schema [M.N];

 

create table [M.N].[MyInt]

(

  [Item] int not null

);

Module imports and exports are purely “M” constructs and have no effect on the SQL output by “M”->SQL.

13.3 Extents

13.3.1 Extents -> Tables

“M” extents are simply storage specifiers ("MyInts : Integer32*" means "MyInts is a place where I can hold a bunch of integers.")  “M”->SQL compilation translates extents into tables with the same name.

 

“M”->SQL supports extents with four different flavors: Scalar, collection of Scalar, Entity and collection of Entity.  All supported extents are converted to SQL tables.   The translation of “M” scalar types to SQL scalar types is covered in Basic Types.

 

SQL tables for entities have columns of the same name as the entity fields:

 

Type M Example SQL Example

Entity*

module M {

    type Foo {

        i : Integer32;

        j : Text;

    }

    Foos : Foo*;

}

create table [M].[Foos] (

  [i] int not null,

  [j] nvarchar(max) not null

);

Entity

module M {

    type Foo {

       i : Integer32;

       j : Text;

    }

    AFoo : Foo;

}

create table [M].[AFoo] (

  [i] int not null,

  [j] nvarchar(max) not null

);

SQL tables for scalars have one column named "Item":

Scalar

module M {

    AnInt : Integer32;

}

create table [M].[AnInt]

(

  [Item] int not null

);

Scalar*

module M {

    CollectionOfStrings : Text*;

}

create table [M].[CollectionOfStrings]

(

  [Item] nvarchar(max) not null

);

As you can see from the “Scalar” and “Entity” examples, tables created to hold exactly one value look the same as tables that hold many values.  “M” assumes you will always have exactly one row in such tables.  Currently it does not create constraints to enforce this (but it should and will).

 “M”->SQL does not presently support collections of collections (such as GroupsOfPeople : (Person*)*).

13.3.2 Identity columns

“M” entities can have one or more identity fields specified that make up a unique key for the entity.  This identity is the basis for entity relationships (one-to-one, one-to-many and many-to-many relationships).

 “M”->SQL supports single- or multiple-field identities, and will create a primary key containing those columns.  “M”->SQL supports any type for identity (except unconstrained Text--see below).  It also supports the identity autonumbering scheme of SQL.

“M”->SQL explicitly specifies the “clustered” keyword for the primary key even though it is the default for SQL.  The clustered index is the index in which the data for the table is actually stored (so that you can look it up very quickly when you use the primary key to look it up).

 

Type M Example SQL Example

Simple Identity

module M {

    type Person {

        PersonId : Integer32;

        Name : Text;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

Autonumbering Identity              

module M {

    type Person {

        PersonId : Integer32 => AutoNumber();

        Name : Text;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null identity,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

Guid identity

module M {

    type Person {

        PersonId : Guid => NewGuid();

        Name : Text;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] uniqueidentifier not null default newid(),

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

Multiple field identity

module M {

    type Person {

        FirstName : Text#100;

        LastName : Text#100;

    } where identity(FirstName,LastName);

    People : Person*;

}

create table [M].[People]

(

  [FirstName] nvarchar(100) not null,

  [LastName] nvarchar(100) not null,

  constraint [PK_People] primary key clustered ([FirstName], [LastName])

);

Unconstrained Text field identities are not supported because SQL cannot create a primary key for them.  In order to use a Text field as an identity, constrain its length like so: "Name : Text#50".

Collection fields and nullable fields are presently unsupported as part of a primary key.

Primary keys that would take more than 900 bytes to store are presently unsupported due to limitations in SQL and will print an error.

13.3.3 Entity References: One-To-One and One-To-Many

One-to-one and one-to-many relationships in “M” are modeled using entity references.  To implement the one-to-many pattern, you place an entity reference to the Parent in the child extent (OtherTable : OtherType).  To implement one-to-one, you place an entity reference from one of the extents to the other (OtherTable : OtherType).  Both look the same in “M”.  (To enforce the 1:1 relationship, a unique constraint can be used.)

“M”->SQL implements entity references with a column with the same type as entity's identity column, and a foreign key from the referencing table to the referenced table.  Trees are simply self-referencing one-to-many relationships.

 

Type M Example SQL Example

One-to-many

module M {

    type Person {

        Id : Integer32

            => AutoNumber();

        Name : Text;

    } where identity Id;

    People : Person*;

   

    type TaxReturn {

        Id : Integer32;

        Year : Integer16;

        Citizen : Person where value in People;

    } where identity Id;

    TaxReturns : TaxReturn*;

}

create table [M].[People]

(

  [Id] int not null identity,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([Id])

);

go

create table [M].[TaxReturns]

(

  [Id] int not null,

  [Citizen] int not null,

  [Year] smallint not null,

  constraint [PK_TaxReturns] primary key clustered ([Id]),

  constraint [FK_TaxReturns_Citizen_M_People] foreign key ([Citizen]) references [M].[People] ([Id])

);

Tree

module M {

    type Person {

        PersonId : Integer32 => AutoNumber();

        Name : Text;

        Mother : Person? where value in People;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null identity,

  [Mother] int null,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId]),

  constraint [FK_People_Mother_M_People] foreign key ([Mother]) references [M].[People] ([PersonId])

);

One-to-one

module M {

    type Person {

        PersonId : Integer32

            => AutoNumber();

        Name : Text;

    } where identity PersonId;

    People : Person*;

   

    type CurrentSession {

        Person : Person where value in People;

        StartTime : Time;

    } where unique Person;

    CurrentSessions : CurrentSession*;

}

create table [M].[People]

(

  [PersonId] int not null identity,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

 

create table [M].[CurrentSessions]

(

  [Person] int not null,

  [StartTime] time not null,

  constraint [Unique_CurrentSessions_Person] unique ([Person]),

  constraint [FK_CurrentSessions_Person_People] foreign key ([Person]) references [M].[People] ([PersonId])

);

References to entities with multiple identity fields

module M {

    type Person {

        FirstName : Text#100;

        LastName : Text#100;

        Mother : Person where value in People;

    } where identity(FirstName,LastName);

    People : Person*;

}

create table [M].[People]

(

  [FirstName] nvarchar(100) not null,

  [LastName] nvarchar(100) not null,

  [Mother_FirstName] nvarchar(max) not null,

  [Mother_LastName] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([FirstName], [LastName]),

  constraint [FK_People_Mother_FirstName_Mother_LastName_M_People] foreign key ([Mother_FirstName], [Mother_LastName]) references [M].[People] ([FirstName], [LastName])

);

“M”->SQL will generate an error if you have an entity reference without a membership constraint ("where value in OtherExtent" or some variant thereof).

13.3.4 Many-To-Many Relationships

“M”->SQL supports one-to-many (and one to one) relationships using collection fields.  Unlike other entity fields, collection fields do not translate directly to a column in the underlying SQL table.   Instead, a many-to-many "child table" is created with the name "<Extent>_<Field>", with a parent id and an "Item" column to hold the values.  If the parent has a multi-column primary key, the parent reference in the child table will be multi-column as well.

For collections of entities, the “Item” column(s) of the child table will be a foreign key to the storage in the same manner as a one-to-many relationship.

The child table has “on delete cascade” specified, so that if you delete a row in the parent table, corresponding rows in the child table will also be deleted.

 

Type M Example SQL Example

Many-to-many scalars

module M {

    type Person {

        PersonId : Integer32;

        Name : Text;

        EmailAddresses : Text*;

    };

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

 

create table [M].[People_EmailAddresses]

(

  [_Id] bigint not null identity,

  [People_Id] int not null,

  [Item] nvarchar(max) not null,

  constraint [PK_People_EmailAddresses] primary key clustered ([_Id]),

 

  constraint [FK_People_EmailAddresses_People_Id_People] foreign key ([People_Id]) references [M].[People] ([PersonId]) on delete cascade

);

Many-to-many entities

module M {

    type Person {

        PersonId : Integer32;

        Name : Text;

        Addresses : Address*;

    } where identity PersonId;

    People : (Person where value.Addresses <= Addresses)*;

   

    type Address {

        AddressId : Integer32;

        Street : Text;

        ZipCode : Integer32;

    } where identity AddressId;

    Addresses : Address*;

}

create table [M].[People]

(

  [PersonId] int not null,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

 

create table [M].[Addresses]

(

  [AddressId] int not null,

  [Street] nvarchar(max) not null,

  [ZipCode] int not null,

  constraint [PK_Addresses] primary key clustered ([AddressId])

);

 

create table [M].[People_Addresses]

(

  [_Id] bigint not null identity,

  [People_Id] int not null,

  [Item] int not null,

 

  constraint [PK_People_Addresses] primary key clustered ([_Id]),

 

  constraint [FK_People_Addresses_People_Id_People] foreign key ([People_Id]) references [M].[People] ([PersonId]) on delete cascade,

 

  constraint [FK_People_Addresses_Item_] foreign key ([Item]) references [M].[Addresses] ([AddressId])

);

Many-to-many entities referring to table with multiple identity fields

module M {

    type Person {

        FirstName : Text#100;

        LastName : Text#100;

        Friends : People*;

    } where identity(FirstName, LastName);

    People : Person*;

}

create table [M].[People]

(

  [FirstName] nvarchar(100) not null,

  [LastName] nvarchar(100) not null,

  constraint [PK_People] primary key clustered ([FirstName], [LastName])

);

create table [M].[People_Friends]

(

  [_Id] bigint not null identity,

  [People_FirstName] nvarchar(max) not null,

  [People_LastName] nvarchar(max) not null,

  [Item_FirstName] nvarchar(max) not null,

  [Item_LastName] nvarchar(max) not null,

  constraint [PK_People_Friends] primary key clustered ([_Id]),

 

 constraint [FK_People_Friends_People_FirstName_People_LastName_M_People] foreign key ([People_FirstName], [People_LastName]) references [M].[People] ([FirstName], [LastName]) on delete cascade,

  constraint [FK_People_Friends_Item_FirstName_Item_LastName_M_People] foreign key ([Item_FirstName], [Item_LastName]) references [M].[People] ([FirstName], [LastName])

);

Many-to-many scalars where parent table has multiple identity fields

module M {

    type Person {

        FirstName : Text#100;

        LastName : Text#100;

        EmailAddresses : Text*;

    } where identity(FirstName, LastName);

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

 

create table [M].[People_EmailAddresses]

(

  [_Id] bigint not null identity,

  [People_FirstName] nvarchar(max) not null,

  [People_LastName] nvarchar(max) not null,

  [Item] nvarchar(max) not null,

  constraint [PK_People_EmailAddresses] primary key clustered ([_Id]),

  constraint [FK_People_EmailAddresses_People_FirstName_People_LastName_M_People] foreign key ([People_FirstName], [People_LastName]) references [M].[People] ([FirstName], [LastName]) on delete cascade

);

Collection fields are only supported if the containing entity has an identity field (so that we can map to the parent ID).

13.3.5 Default Values

Fields can have default values in “M”, for example "MyGuid : Guid => NewGuid()" or "Z : Integer32 => 0."  “M”->SQL translates simple constant defaults into the “default” keyword for a column.  Anything more complex (references to columns, subqueries, math operators) is presently unsupported.

This means that you can do an "insert into" into the table without specifying those values, and they will be replaced with their defaults.

 

Type M Example SQL Example

Simple Default

module M {

    type Person {

        PersonId : Integer32;

        Name : Text;

        IsAlive : Logical => true;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null,

  [IsAlive] bit not null default 1,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

Autonumbering Identity              

module M {

    type Person {

        PersonId : Integer32 => AutoNumber();

        Name : Text;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null identity,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

The “AutoNumber()” default, only specifiable on an integer identity field, will cause the field type to be “identity.”

 “M”->SQL cannot presently handle default values for collection or entity columns.  Additionally, default values that refer to collection or entity columns in the same extent will not work.

13.3.6 Constraints

Aside from membership and identity constraints, all "where" constraints on a type are translated in “M”->SQL as CHECK constraints.  In order to allow a richer set of expressions, CHECK constraints are always placed in a function returning a Logical value.

Before check constraints are created, identity and foreign key constraints are removed from the expression (i.e. they will not be turned into check constraints).  It does this by breaking up the expression by &&’s.  So if the “M” has a constraint like:"where identity(Id) && Address in Addresses && Age > 10", "Age > 10" will be turned into a check constraint, but the first two will not because they are identity and foreign key constraints, respectively.

Type M Example SQL Example

Column Constraint

module M {

    type Person {

        Gender : Text where value == "M" || value == "F";

    };

    People : Person*;

}

create function [M].[People_check_1]

(

  @Gender as nvarchar(max)

)

returns bit  as

  begin

    return case

  when @Gender = N'M' or @Gender = N'F' then 1

  else 0

end

 

  end

go

 

create table [M].[People]

(

  [Gender] nvarchar(max) not null,

  check ([M].[People_check_1]([Gender]) = 1)

);

Extent Constraint

module M {

    type Person {

        Gender : Text;

    } where value.Gender == "M" || value.Gender == "F";

    People : Person*;

}

create function [M].[People_check_1]

(

  @Gender as nvarchar(max)

)

returns bit  as

  begin

    return case

  when @Gender = N'M' or @Gender = N'F' then 1

  else 0

end

 

  end

go

 

create table [M].[People]

(

  [Gender] nvarchar(max) not null,

  check ([M].[People_check_1]([Gender]) = 1)

);

Unique Constraint

module M {

    type Person {

        FirstName : Text#100;

        LastName : Text#100;

        Gender : Text;

    } where unique (FirstName, LastName);

    People : Person*;

}

create table [M].[People]

(

  [FirstName] nvarchar(100) not null,

  [Gender] nvarchar(max) not null,

  [LastName] nvarchar(100) not null,

  constraint [Unique_People_FirstName_LastName] unique ([FirstName], [LastName])

);

Identity constraint

module M {

    type Person {

        PersonId : Integer32

            => AutoNumber();

        Name : Text;

    } where identity PersonId;

    People : Person*;

}

create table [M].[People]

(

  [PersonId] int not null identity,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

Foreign key constraint

module M {

    type Person {

        PersonId : Integer32

            => AutoNumber();

        Name : Text;

    } where identity PersonId;

    People : Person*;

   

    type CurrentSession {

        Person : Person where value in People;

        StartTime : Time;

    };

    CurrentSessions : CurrentSession*;

}

create table [M].[People]

(

  [PersonId] int not null identity,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([PersonId])

);

 

create table [M].[CurrentSessions]

(

  [Person] int not null,

  [StartTime] time not null,

  constraint [FK_CurrentSessions_Person_People] foreign key ([Person]) references [M].[People] ([PersonId])

);

13.3.7  Computed Value Fields

Computed values inside an extent (computed columns in SQL-speak) are not presently supported in SQL translation.

13.3.8 Extent Initialization

Extents can have one or more initializers in “M”, that state the data that needs to be in the table.  “M”->SQL translates these initializers into insert statements.  Nested entity and collection initializers are supported.

When values that have defaults are not filled in, the corresponding SQL INSERT is not filled in either, and SQL fills in the values itself.

 

Type M Example SQL Example

Scalar

module M {

    SingleInt : Integer32 => 10;

}

create table [M].[SingleInt]

(

  [Item] int not null

);

 

insert into [M].[SingleInt] ([Item])

 values (10);

Scalar Collection

module M {

    Ints : Integer32* { 10, 20 };

}

create table [M].[Ints]

(

  [Item] int not null

);

 

insert into [M].[Ints] ([Item])

 values (10);

insert into [M].[Ints] ([Item])

 values (20);

Entity

module M {

    type Foo {

        i : Integer32;

        j : Text => "initial value";

    };

    SingleFoo : Foo { i => 10 };

}

create table [M].[SingleFoo]

(

  [i] int not null,

  [j] nvarchar(max) not null default N'initial value'

);

 

insert into [M].[SingleFoo] ([i])

 values (10);

Entity Collection

module M {

    type Foo {

        i : Integer32;

        j : Text => "initial value";

    };

    Foos : Foo* {

        { i => 10, j => "hi" },

        { i => 1 }

    };

}

create table [M].[Foos]

(

  [i] int not null,

  [j] nvarchar(max) not null default N'initial value'

);

 

insert into [M].[Foos] ([i], [j])

 values (10, N'hi');

insert into [M].[Foos] ([i])

 values (1);

Nested Entity Reference

module M {

    type Bar {

        Id : Integer32 => AutoNumber();

        x : Integer32;

        y : Text => "initial value";

    } where identity(Id);

    type Foo {

        i : Integer32;

        bar : Bar;

    };

    Bars : Bar*;

    Foos : (Foo where value.bar in Bars)* {

        { i => 10, bar => { x => 10 } },

        { i => 1, bar => { x => 20, y => "hi" } }

    };

}

create table [M].[Bars]

(

  [Id] int not null identity,

  [x] int not null,

  [y] nvarchar(max) not null default N'initial value',

  constraint [PK_Bars] primary key clustered ([Id])

);

create table [M].[Foos]

(

  [bar] int not null,

  [i] int not null,

  constraint [FK_Foos_bar_M_Bars] foreign key ([bar]) references [M].[Bars] ([Id])

);

insert into [M].[Bars] ([x])

 values (10);

declare @M_Bars_Id0 bigint = @@identity;

insert into [M].[Bars] ([x], [y])

 values (20, N'hi');

declare @M_Bars_Id1 bigint = @@identity;

insert into [M].[Foos] ([i], [bar])

 values (10, @M_Bars_Id0);

insert into [M].[Foos] ([i], [bar])

 values (1, @M_Bars_Id1);

Nested Entity Collection Reference

module M {

    type Bar {

        Id : Integer32 => AutoNumber();

        x : Integer32;

        y : Text => "initial value";

    } where identity(Id);

    type Foo {

        Id : Integer32 => AutoNumber();

        i : Integer32; bars : Bar*;

    } where identity(Id);

    Bars : Bar*;

    Foos : (Foo where value.bars <= Bars)* {

        { i => 10, bars => { { x => 10 }, { x => 100, y => "lo" } }},

        { i => 1, bars => { { x => 20, y => "hi" } }}

    };

}

create table [M].[Bars]

(

  [Id] int not null identity,

  [x] int not null,

  [y] nvarchar(max) not null default N'initial value',

  constraint [PK_Bars] primary key clustered ([Id])

);

create table [M].[Foos]

(

  [Id] int not null identity,

  [i] int not null,

  constraint [PK_Foos] primary key clustered ([Id])

);

create table [M].[Foos_bars]

(

  [_Id] bigint not null identity,

  [Foos_Id] int not null,

  [Item] int not null,

  constraint [PK_Foos_bars] primary key clustered ([_Id]),

  constraint [FK_Foos_bars_Foos_Id_M_Foos] foreign key ([Foos_Id]) references [M].[Foos] ([Id]) on delete cascade,

  constraint [FK_Foos_bars_Item_M_Bars] foreign key ([Item]) references [M].[Bars] ([Id])

);

go

insert into [M].[Bars] ([x])

 values (10);

declare @M_Bars_Id0 bigint = @@identity;

insert into [M].[Bars] ([x], [y])

 values (100, N'lo');

declare @M_Bars_Id1 bigint = @@identity;

insert into [M].[Bars] ([x], [y])

 values (20, N'hi');

declare @M_Bars_Id2 bigint = @@identity;

insert into [M].[Foos] ([i])

 values (10);

declare @M_Foos_Id0 bigint = @@identity;

insert into [M].[Foos] ([i])

 values (1);

declare @M_Foos_Id1 bigint = @@identity;

insert into [M].[Foos_bars] ([Item], [Foos_Id])

 values (@M_Bars_Id0, @M_Foos_Id0);

insert into [M].[Foos_bars] ([Item], [Foos_Id])

 values (@M_Bars_Id1, @M_Foos_Id0);

insert into [M].[Foos_bars] ([Item], [Foos_Id])

 values (@M_Bars_Id2, @M_Foos_Id1);

Any depth of nested entity reference is supported.

Initialization of nested entities and entity references is currently unsupported when the referenced / nested entity is part of an extent with multi-field identity.

13.3.9 Labeled Extent Initialization

When inserting instances, you can label the instances and refer to them in other insert statements or in expressions.  Labeled scalars are translated as constants wherever they are seen.  Labeled entities:

Type M Example SQL Example

Labeled Scalar

module M {

    Ints : Integer32* { A { 10 }, B { 20 } };

    MoreInts : Integer32* { Ints.B, Ints.A };

    F() { Ints.A + Ints.B }

}

create table [M].[Ints]

(

  [Item] int not null

);

create table [M].[MoreInts]

(

  [Item] int not null

);

create function [M].[F]

(

)

returns int  as

  begin

    return 10 + 20

  end

insert into [M].[Ints] ([Item])

 values (10)

;

insert into [M].[Ints] ([Item])

 values (20)

;

insert into [M].[MoreInts] ([Item])

 values (20)

;

insert into [M].[MoreInts] ([Item])

 values (10);

Labeled Entity

module M {

    type Person {

        Id : Integer32 => AutoNumber();

        Name : Text;

        Mother : People?;

    } where identity(Id);

    People : Person* {

        Jack { Name => "Jack", Mother => Jane },

        Jane { Name => "Jane" },

        John { Name => "John", Mother => Jane },

        Manfred { Name => "Manfred", Mother => Martha },

        Martha { Name => "Martha" },

    };

}

create table [M].[People]

(

  [Id] int not null identity,

  [Mother] int null,

  [Name] nvarchar(max) not null,

  constraint [PK_People] primary key clustered ([Id]),

  constraint [FK_People_Mother_M_People] foreign key ([Mother]) references [M].[People] ([Id])

);

insert into [M].[People] ([Name])

 values (N'Jane');

declare @M_People_Id1 bigint = @@identity;

insert into [M].[People] ([Name], [Mother])

 values (N'Jack', @M_People_Id1);

insert into [M].[People] ([Name], [Mother])

 values (N'John', @M_People_Id1);

insert into [M].[People] ([Name])

 values (N'Martha');

declare @M_People_Id4 bigint = @@identity;

insert into [M].[People] ([Name], [Mother])

 values (N'Manfred', @M_People_Id4);

References to labeled entities are not currently supported in computed values or constraints.

References to labeled entities with multiple identity fields are presently unsupported.

13.3.10 Identifiers and Collisions

All views, functions, tables, columns, constraints, and triggers created in “M” have names that are supposed to be similar to the underlying table.  However, SQL only supports 128-character names.  If the name of an identifier would be longer than 128 characters, it is truncated to 128 characters.

Identifier collisions are also detected and disambiguated.  It is possible for conflicts between autogenerated names to For example in the following “M”, it can create a conflict between the autogenerated join table and the declared "People_Addresses" extent:

 

"M" 
module M {
    type Address {
        Id : Integer32;
        Street : Text;
        City : Text;
        State : Text;
    } where identity Id;
    type Person {
        Id : Integer32;
        Name : Text;
        Addresses : Address*;
    } where identity Id;
    People : (Person where value.Addresses <= Addresses)*;
    Addresses : Address*;
    People_Addresses : Integer32;
}

When collisions are detected, a number is appended to the database object in question.  These two objects would be named “People_Addresses” and “People_Addresses1.”  How these numbers will be assigned is presently undefined.

13.4 Computed Values

 Computed Values in “M” represent "functions," expressions or queries that can be reused.

 This section only covers module-level computed values.  Computed Columns are covered in Extents (and are not presently supported).  Computed Values can have a range of expressions.  Translation of expressions is covered in Expressions; we will only include rudimentary expressions here.

 “M”->SQL will translate Computed Values into scalar functions, table-valued functions, or views, depending on their most probable usage in SQL (based on the return type and parameters to the function):

  • Scalar functions are created when the Computed Value return type is scalar (which allows you to use them like “SELECT Module.Function(2) + 1”).
  • Views are created when the Computed Value has no parameters the return type is an entity or a collection (which allows you to use them like “SELECT * FROM Module.View”).
  • Table-valued functions are created when the Computed Value has parameters and the return type is an entity or a collection (which allows you to use them like “SELECT * FROM Module.Function(10)”

Type M Example SQL Example

View

module M {

    type Foo { i : Integer32; }

    Foos : Foo*;

    BigFoos() : Foo* { Foos where value.i > 100 }

}

create view [M].[BigFoos]

as

  select [t0].[i]

  from (     select [i] as [i]

     from [M].[Foos]) as [t0]

  where [t0].[i] > 100;

Scalar Function

module M {

    MyInt : Integer32;

    DoubleMyInt() : Integer32 { MyInt * 2 }

}

create function [M].[DoubleMyInt]

(

)

returns int  as

  begin

    return (select [Item] as [Item]

from [M].[MyInt]) * 2

  end

Scalar Function

module M {

    Square(x : Integer32) : Integer32 { x * x }

}

create function [M].[Square]

(

  @x as int

)

returns int  as

  begin

    return @x * @x

  end

Table Valued Function

module M {

    type Foo { i : Integer32; }

    Foos : Foo*;

    BigFoos(minValue : Integer32) : Foo*  { Foos where value.i > minValue }

}

create function [M].[BigFoos]

(

  @minValue as int

)

returns table

  as return (

    select [t0].[i]

    from (     select [i] as [i]

     from [M].[Foos]) as [t0]

    where [t0].[i] > @minValue

  )

“M”->SQL does not presently support entity and collection parameters to Computed Values.  Only scalars are supported in parameters.

13.4.1 Query Return Types

For table-valued functions and views, the returned rowset will have the same set of columns that the corresponding table would have.  So any SQL query you can do on a table of type T, you can do the same  thing with a view or function returning type T.  See the beginning of the Extents section for the way various types are represented in tables.

13.4.2 Overloading

“M”->SQL does not presently support “M” function overloading.

 13.5 Expressions

 “M”->SQL can translate most “M” expressions including operators (*, /, +, -), queries ("from person in People where person.Age >= 21"), constants and initializers ("astring", { 1, 2, 3 }), and things like member access and function calls (person.Age).

 These expressions can be used in several different contexts, including computed values, constraints, default values, initializers.  We will not talk about those contexts except where expressions behave differently or do not work—more complete information about those contexts can be found in the Extents and Computed Values sections.

 “M” supports a wide range of expressions over a wide range of types, but “M”->SQL only supports expressions that return scalars, entities and collections of scalars or entities (the same set of types that are supported by extent -> table translation).

13.5.1 Composition

Expressions are composable--the result of one expression can be used in another expression.  This document will talk about the way in which individual expressions are turned into SQL, but will only call out particularly interesting compositions.

 So when we say:

 A + B -> A + B

A.Count -> LEN(A)

We mean that any “M” expression that can fit into A and B, so if you see MyString.Count + 10, the translated SQL will look like ( LEN(MyString) ) + 10.

Query expressions are similarly composed:

A | B -> A UNION B

Will take the SQL query expression for A and the SQL query expression for B and place them into those slots.  So if you say "Foos | Bars", it will turn into "(SELECT * FROM Foos) UNION (SELECT * FROM Bars)".

13.5.2 Constants

The majority of constants are translated verbatim.  Here is a list of “M” literals and examples of their SQL equivalents:

M Type M Example SQL Example

Decimal

1234.567

1234.567

Integer

1234

1234

Scientific

1234.567e+89

1.234567E+92

Date

2008-05-04

'2008-05-04'

DateTime

2008-05-04T18:27:36

'2008-05-04T18:27:36'

Time

18:27:36

Not yet implemented

Character

'c'

N'c'

Text

"little bunny 'fufu'"

N'little bunny ''fufu'''

Logical

true

false

1

0

Binary

0x01200340

0x01200340

Null

null

null

Guid

#[12345678-1234-1234-1234-123456789012]

'12345678-1234-1234-1234-123456789012'

13.5.3 Numeric Expressions

Numeric values have a small number of operators that can act upon them.  These operators generally translate verbatim to SQL.

 

Operator M Example SQL Example

*, /, +, -, %

A op B

A op B

+, - (unary)

op A

op A

<, >, >=, <=

A op B

A op B

==

A == B

A = B

!=

A != B

A <> B

Exceptions:

Integer8/16/32/64 and Unsigned8/16/32 divide.  These translate to “convert(decimal(x,6), A) / convert(decimal(x,6), B)” (where x is replaced by the smallest possible decimal value that can represent the entire range of values for the concrete integer type).

Single and Double modulo operators are unsupported.

13.5.4 String Expressions

Operator M Example SQL Example

Count

A#

A.Count

LEN(A)

Concatenation

A + B

A + B

Comparison

A < B

A <= B

A > B

A >= B

A == B

A != B

A < B

A <= B

A > B

A >= B

A = B

A <> B

Like

A.Like(B)

Not currently implemented

PatternIndex

A.PatternIndex(B)

Not currently implemented

13.5.5 Date Expressions

These expressions are defined on Date, DateTime and Time.

 

Operator M Example SQL Example

Addition

A + B

A + convert(datetime, B)

Equality

A == B

A = B

Inequality

A != B

A <> B

Relational

A <= B

A >= B

A < B

A > B

A <= B

A >= B

A < B

A > B

Exceptions:

DateTime addition is not defined in “M” and is therefore not implemented.

13.5.6   Binary Expressions

Operator M Example SQL Example

~

~A

Not currently implemented

&, ^, |

A op B

Not currently implemented

==

A == B

A = B

!=

A != B

A <> B

<<, >>

A op B

Not currently implemented

13.5.7  Byte Expressions

Operator M Example SQL Example

~

~A

~A

&, ^, |

A op B

A op B

==

A == B

A = B

!=

A != B

A <> B

<<, >>

A op B

Not currently implemented

13.5.8 Guid Expressions

Operator M Example SQL Example

~

~A

Not currently implemented

&, ^, |

A op B

Not currently implemented

==

A == B

A = B

!=

A != B

A <> B

<<, >>

A op B

Not currently implemented

13.5.9  Logical Expressions

Operator M Example SQL Example

Not

! A

not A

And

A && B

A and B

Or

A || B

A or B

Equal

A == B

A = B

Not Equal

A != B

A <> B

13.5.10 Conditionals

Operator M Example SQL Example

Conditional

A ? B : C

Not currently implemented

Coalesce

A ?? B

case

  when A is null then B

  else A

end

Exceptions:

Entity types: The coalesce operator currently does not support entity types.

Collection types: Collection types are not nullable and cannot be used in the coalesce operator.

13.5.11 Nullable Expressions

The == and != operators in “M” have the semantic that null == null and null != 1.  In SQL, both of these return “unknown,” a third truth value we do not support in “M”.  In order to get the “M” semantics, when one side of an operator is a nullable type (for example, F(a : Integer32?, b : Integer32?) { a == b }), we generate special case logic that checks for null values to ensure that null == null and null != 1 turn out true.

This applies to all supported scalar types.  We do not presently support equality comparison of entities, so nullable entity comparison is unsupported as well.

Nullable comparison operators (<, >, <=, >=) operating on nullable integers actually return Logical?: 1 <= 2 is true, 2 <= 1 is false, null <= null is null, and 1 <= null is null.  This applies to all supported numeric types.

Operator

M Example

SQL Example

Nullable ==

A == B

(A is null and B is null) or

(A is not null and B is not null and A = B)

Nullable !=

A != B

(A is null and B is not null) or

(A is not null and B is null) or

(A is not null and B is not null and A <> B)

Nullable Comparison

A < B

A > B

A <= B

A >= B

case

  when A is null or B is null then null

  when A <comparison op> B

then 1

  else 0

end

13.5.12 Explicit Conversion

Operator M Example SQL Example

Cast

A : T

convert(SQL type of T, A)

Exceptions:

Entity and collection types: Explicit casts (Expression : Type) are not presently supported for entity or collection types.

13.5.12.1 Implicit Conversion

“M” allows Logical values to intermingle with other Scalars, but SQL does not support that.  Logical expressions like == cannot be stored directly in a table, or returned as a scalar or as the result of a query.  We use "bit" for that instead, and therefore have to translate the result of == into a "1" or "0."  Likewise, Logical values stored in tables as "bit"s cannot be used directly in logical expressions like NOT, AND and OR (or where clauses of queries), so we have to use expr == 1.  So where necessary, expression conversion will automatically convert between these two:

 

Operation M Example SQL Example

Scalar -> Logical

module M {

    type Person { Name : Text; IsOld : Logical; }

    People : Person*;

    OldPeople { People where value.IsOld }

}

create table [M].[People]

(

  [IsOld] bit not null,

  [Name] nvarchar(max) not null

);

 

create view [M].[OldPeople]

as

  select [t0].[Name], [t0].[IsOld]

  from (     select [IsOld] as [IsOld], [Name] as [Name]

     from [M].[People]) as [t0]

  where (select [t0].[IsOld] as [Item]) = 1;

Scalar -> Logical

module M {

    NegateIt(x : Logical) : Logical { ! x }

}

create function [M].[NegateIt]

(

  @x as bit

)

returns bit  as

  begin

    return case

  when not @x = 1 then 1

  else 0

end

 

  end

13.5.13  Global References

“M”->SQL translates computed values and extents into functions, views, and tables.  Expressions are capable of referencing them as well.  When an extent is used as an expression, the expression always means "get all the data out of this expression."

Type

M Example

SQL Example

Table

module M {

    type Foo {

        i : Integer32;

        j : Integer32;

    }

    Foos : Foo*;

    FooExpression() : Foo* { Foos }

}

create table [M].[Foos]

(

  [i] int not null,

  [j] int not null

);

create view [M].[FooExpression]

(

  [i],

  [j]

)

as

  select [i] as [i], [j] as [j]

  from [M].[Foos];

View

module M {

    type Foo {

        i : Integer32;

        j : Integer32;

    }

    Foos : Foo*;

    FooView() : Foo* { Foos }

    FooExpression() : Foo* { FooView }

}

create table [M].[Foos]

(

  [i] int not null,

  [j] int not null

);

create view [M].[FooView]

(

  [i],

  [j]

)

as

  select [i] as [i], [j] as [j]

  from [M].[Foos];

create view [M].[FooExpression]

(

  [i],

  [j]

)

as

  select [i] as [i], [j] as [j]

  from [M].[FooView];

Table Valued Function

module M {

    type Foo {

        i : Integer32;

        j : Integer32;

    }

    Foos : Foo*;

    FooFunction(x : Integer32) : Foo* { Foos where value.i > 10 }

    FooExpression() : Foo* { FooFunction(10) }

}

create table [M].[Foos]

(

  [i] int not null,

  [j] int not null

);

go

create function [M].[FooFunction]

(

  @x as int

)

returns table

  as return (

    select [$value].[i] as [i], [$value].[j] as [j]

    from [M].[Foos] as [$value]

    where [$value].[i] > 10

  )

go

create view [M].[FooExpression]

(

  [i],

  [j]

)

as

  select [i] as [i], [j] as [j]

  from [M].[FooFunction]( 10);

Scalar Function

module M {

    Square(x : Integer32) : Integer32 { x * x }

    Cube(x : Integer32) : Integer32 { Square(x) * x }

}

create function [M].[Square]

(

  @x as int

)

returns int  as

  begin

    return @x * @x

  end

 

create function [M].[Cube]

(

  @x as int

)

returns int  as

  begin

    return [M].[Square](@x) * @x

  end

13.5.14  Collection Expressions

Collection expressions are expressions which work on collections.

 

Operation M Example SQL Example

Subset

A <= B

not exists (A except B)

Superset

A >= B

not exists (B except A)

Strict Subset

A < B

not exists (A except B) and exists (B except A)

Strict Superset

A > B

not exists (B except A) and exists (A except B)

Equal

A == B

not exists (A except B) and not exists (B except A)

Not equal

A != B

exists (A except B) or exists (B except A)

in

A in B

A in B

Union

A | B

A union B

Intersection

A & B

A intersect B

Where

A where B

Equivalent to “from value in A where B select value”

Select

A select B

Equivalent to “from value in A select B”

Choose

A.Choose

Not currently implemented

Count

A#

A.Count

Not currently implemented

Distinct

A.Distinct

Not currently implemented

Exceptions:

Entity types from different tables: Set comparison (subset, equal, etc.), in and intersection are not presently supported when used over entity types that come from different tables.

Entity types with different fields: Union is presently unsupported when used over entity types with different sets of fields (but they may come from different tables).

13.5.14.1 Entity collections only:

Operation M Example SQL Example

Projector

A.field

Equivalent to “from value in A select value.field”

Selector

A.field(B)

Equivalent to “from value in A where value.field = B select value”

Table Selector

A(B)

Not currently implemented

 

Selectors are presently unsupported when the field is an entity type.

13.5.14.2 Logical collections only:

Operation M Example SQL Example

All

A.All

Not currently implemented

Exists

A.Exists

Not currently implemented

13.5.14.3 Numeric collections only:

Operation M Example SQL Example

Average

A.Average

Not currently implemented

Maximum

A.Maximum

Not currently implemented

Minimum

A.Minimum

Not currently implemented

Sum

A.Sum

Not currently implemented

13.5.14.4 Queries

There is also a more complex and feature-rich query syntax for collections, the "from …" statement.  It has a number of clauses, each of which translates roughly separately from the others.

 

Query modifiers:

Modifier M Example SQL Example

from

from ident in A

from identA in A from identB in B

select … from A as ident

select … from A as identA

              cross join B as identB

join

join ident in A on B equals C

Not currently implemented

join … into

Join ident in A on B equals C into ident2

Not currently implemented

where

where A

where A where B

select … where A

select … where A and B

let

let A = B

Not currently implemented

“from” expressions bring “ident” into scope as a variable.  For scalar collections, future references to “ident” will resolve as [ident].[Item].  For entity collections, references to “ident” will resolve as (select [ident].<column1>, [ident].<column2, …)

Join supports comparison of scalars and entities with identity, but not collections or other entities.

Result gatherers are the terminus of a query.  They can be “select”, “group” or “accumulate.”  “group” and “accumulate” are presently unsupported.

13.5.14.4.1         Select

The general form of “select” is as follows:

Operation M Example SQL Example

Select

select A

select t0.a, t0.b, …

  from (<from clauses)

  cross apply A as t0

  <where clauses>

The CROSS APPLY may be optimized out in simple cases (such as anonymous entities).

So to put it all together, this query:

"M" 
module M {
       type Foo { Id : Integer32; i : Integer32; } where identity(Id);
       Foos : Foo*;
       type Bar { Id : Integer32; j : Integer32; } where identity(Id);
       Bars : Bar*;
       F() { from foo in Foos from bar in Bars where foo.i == bar.j select { i = foo.i, j = bar.j } }
}

Translates to this:

T-SQL 
select [t0].[i] as [i], [t0].[Id] as [Id]
from [M].[Foos] as [foo] -- from foo in Foos
  cross join [M].[Bars] as [bar] -- from bar in Bars
   cross apply (select [foo].[i] as [i], [bar].[j] as [j]) as [t0] -- select { i = foo.i, j = bar.j }
where [foo].[i] = [bar].[j]; -- where foo.i == bar.j

Which is then optimized to this:

T-SQL 
select [foo].[i] as [i], [bar].[j] as [j] -- select { i = foo.i, j = bar.j }
  from [M].[Foos] as [foo] -- from foo in Foos
     cross join [M].[Bars] as [bar] -- from bar in Bars
  where [foo].[i] = [bar].[j] -- where foo.i == bar.j

Select does not support collections on the right side of the select.

 

13.5.14.4.2         Continuations

Query continuations allow you to chain two queries together, storing the result of the first query in a variable that can be used in the second query.

Operation M Example SQL Example

into

Query1 into var Query2

Not currently implemented

13.5.15 Entity Expressions

Entities in “M” are a single collection of field / value pairs (in “M”->SQL parlance, a row in a table).

Operation M Example SQL Example

Equality

A == B

<ID field of A> = <ID field of B>

Inequality

A != B

<ID field of A> <> <ID field of B>

Field names

A.FieldNames

Not currently implemented

Indexer

A(B)

Not currently implemented

Cast

A : B

Not currently implemented

13.5.16  Member Access

Member access in “M” operates over a single entity.  It has several permutations depending on the type of the field you are accessing.

 

Operation M Example SQL Example

Member Access: Scalar

A.field

select field as [Item] from A

Member Access: Scalar Collection

A.field

Not currently implemented

Member Access: Entity

A.field

select <entity columns>

  from <entity storage table> as [t0]

     inner join A as [t1] on [t1].field = [t0].[Id];

(For entities with multiple identity fields, the “on” statement compares multiple columns.)

Member Access: Entity Collection

A.field

Not currently implemented

Entity and entity collection member access retrieves the entities, including all of their fields, from the table where the entities are stored.  A full example:

Member Access: Entity

module M {

    type Foo {

        Id : Integer32;

        bar : Bar;

        i : Integer32;

    } where identity(Id);

    type Bar {

        Id : Integer32;

        j : Integer32;

    } where identity(Id);

    Bars : Bar*;

    SingleFoo : Foo

        where value.bar in Bars;

    F() { SingleFoo.bar }

}

create table [M].[Bars]

(

  [Id] int not null,

  [j] int not null,

  constraint [PK_Bars] primary key clustered ([Id])

);

create table [M].[SingleFoo]

(

  [Id] int not null,

  [bar] int not null,

  [i] int not null,

  constraint [PK_SingleFoo] primary key clustered ([Id]),

  constraint [FK_SingleFoo_bar_M_Bars] foreign key ([bar]) references [M].[Bars] ([Id])

);

create view [M].[F]

(

  [Id],

  [j]

)

as

  select [t0].[Id] as [Id], [t0].[j] as [j]

  from [M].[Bars] as [t0]

     inner join [M].[SingleFoo] as [t1] on [t1].[bar] = [t0].[Id];

Member access to Computed Columns is not presently supported, due to the lack of support for the computed columns themselves.

13.5.17 Anonymous Collections

“M” supports the creation of anonymous collections in an expression (such as { 1, 2, 4, 8, 16 }).  This is supported using queries and UNION ALL so that the collections can be returned from views and used anywhere expressions can be used.

Type M Example SQL Example

Scalar collection

module M {

    Thirty : Integer32 = 30;

    V() { { 1, 10, 3, Thirty, 2, 20, 20 } }

}

create table [M].[Thirty]

(

  [Item] int not null

);

create view [M].[V]

as

    select 1 as [Item]

  union all

    select 10 as [Item]

  union all

    select 3 as [Item]

  union all

    select [Item] as [Item]

      from [M].[Thirty]

  union all

    select 2 as [Item]

  union all

    select 20 as [Item]

  union all

    select 20 as [Item];

Entity collection

module M

{

    SingleA : {

        i : Integer32;

        j : Text;

    } { i = 100, j = "hundred" };

    V() {

      {

        { i = 20, j = "twenty" },

        SingleA,

        { i = 10, j = "ten" },

        { i = 20, j = "twenty" }

      }

    }

}

create table [M].[SingleA]

(

  [i] int not null,

  [j] nvarchar(max) not null

);

 

insert into [M].[SingleA] ([i], [j])

 values (100, N'hundred');

create view [M].[V]

as

    select 20 as [i], N'twenty' as [j]

  union all

    select [i] as [i], [j] as [j]

      from [M].[SingleA]

  union all

    select 10 as [i], N'ten' as [j]

  union all

    select 20 as [i], N'twenty' as [j];

Empty collection

module M

{

    V() : Integer32* { { } }

}

create view [M].[V]

as

  select 1

  where 1 = 0;

 “M”->SQL does not yet support anonymous collections with entities of different types (different sets of columns) or collections of collections.

13.5.18 Anonymous Entities

“M” supports the creation of simple entities in an expression (i.e. an anonymous group of name/value pairs).  This is typically used in the right side of a "from … select" query.

“M”->SQL supports these by creating a SELECT statement, naming each field with the given name.  If you put an entity reference as a column, that id of that entity will be assigned to the field.

Type M Example SQL Example

Entity with scalars

module M {

    type Foo { i : Integer32; j : Integer32; }

    Foos : Foo*;

    SelectFoos() { from foo in Foos select { x = foo.i+1, y = foo.j+1, combo = foo.i+foo.j } }

}

create table [M].[Foos]

(

  [i] int not null,

  [j] int not null

);

 

create view [M].[SelectFoos]

as

  select [t0].[i] + 1 as [x], [t0].[j] + 1 as [y], [t0].[i] + [t0].[j] as [combo]

  from (     select [i] as [i], [j] as [j]

     from [M].[Foos]) as [t0];

“M”->SQL does not support collection fields or entity fields referring to extents without identity constraints (see Extents - Identity for information on the latter)

13.5.19 Global built-in functions

 ”M”->SQL only presently supports one global built-in function:

M Example SQL Example

NewGuid()

NEWID()

13.6 Other Things

 There are “M” constructs that have no translation in “M”->SQL.  "type", "import" and "export" have no corresponding meaning in SQL.  They are merely used to create the tables, views or functions, and are then thrown away.

Page view tracker