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.
13.1.1 Unsupported Scalar Types
No other scalar types are supported. The unsupported scalar types are:
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.
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:
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).
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.
“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.
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.
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.
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.
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:
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:
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)”
“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:
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.
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
13.5.5 Date Expressions
These expressions are defined on Date, DateTime and Time.
Exceptions:
DateTime addition is not defined in “M” and is therefore not
implemented.
13.5.6 Binary Expressions
13.5.7 Byte Expressions
13.5.8 Guid Expressions
13.5.9 Logical Expressions
13.5.10 Conditionals
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
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:
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.
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:
Selectors are presently unsupported when the field is an
entity type.
13.5.14.2 Logical collections only:
13.5.14.3 Numeric collections only:
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:
“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:
The CROSS APPLY may be optimized out in simple cases (such
as anonymous entities).
So to put it all together, this query:
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:
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:
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.
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).
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.
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.
“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.
“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:
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.