Compartir a través de

Extent Initialization (M to SQL Mapping)

  • Artículo

[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.]

Extents can have one or more initializers in Microsoft code name “M”, which state the data that must 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, but SQL fills in the values itself.

Examples

“M” Example SQL Example 

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 Var {
        i : Integer32;
        j : Text => "initial value";
    };
    SingleVar : Var { i => 10 };
}
 
create table [M].[SingleVar]
(
  [i] int not null,
  [j] nvarchar(max) not null default N'initial value'
);
 
insert into [M].[SingleVar] ([i])values (10);

Entity Collection

 
module M {
    type Var {
        i : Integer32;
        j : Text => "initial value";
    };
    Vars : Var* {{ i => 10, j => "hi" },{ i => 1 }};
}
 
create table [M].[Vars]
(
  [i] int not null,
  [j] nvarchar(max) not null default N'initial value'
);
 
insert into [M].[Vars] ([i], [j])values (10, N'hi');insert into [M].[Vars] ([i])values (1);

Nested Entity Reference

 
module M {
    type Name {
        Id : Integer32 => AutoNumber();
        x : Integer32;
        y : Text => "initial value";
    } where identity(Id);
    type Var {
        i : Integer32;
        name : Names;
    };
    Names : Name*; 
    Vars : Var* {{ i => 10, name => { x => 10 } },{ i => 1, name => { x => 20, y => "hi" } }};
}
 
create table [M].[Names]
(
  [Id] int not null identity,
  [x] int not null,
  [y] nvarchar(max) not null default N'initial value',
  constraint [PK_Names] primary key clustered ([Id])
);
create table [M].[Vars]
(
  [name] int not null,
  [i] int not null,
  constraint [FK_Vars_name_M_Names] foreign key ([name]) references [M].[Names] ([Id])
);
insert into [M].[Names] ([x])values (10);declare @M_Names_Id0 bigint = @@identity;insert into [M].[Names] ([x], [y])values (20, N'hi');declare @M_Names_Id1 bigint = @@identity;insert into [M].[Vars] ([i], [name])values (10, @M_Names_Id0);insert into [M].[Vars] ([i], [name])values (1, @M_Names_Id1);

Nested Entity Collection Reference

 
module M {
    type Name {
        Id : Integer32 => AutoNumber();
        x : Integer32;
        y : Text => "initial value";
    } where identity(Id);
    type Var {
        Id : Integer32 => AutoNumber();
        i : Integer32; names : Names*;
    } where identity(Id);
    Names : Name*; 
    Vars : Var* {{ i => 10, names => { { x => 10 }, { x => 100, y => "lo" } }},{ i => 1, names => { { x => 20, y => "hi" } }}};
}
 
create table [M].[Names]
(
  [Id] int not null identity,
  [x] int not null,
  [y] nvarchar(max) not null default N'initial value',
  constraint [PK_Names] primary key clustered ([Id])
);
create table [M].[Vars]
(
  [Id] int not null identity,
  [i] int not null,
  constraint [PK_Vars] primary key clustered ([Id])
);
create table [M].[Vars_names]
(
  [_Id] bigint not null identity,
  [Vars_Id] int not null,
  [Item] int not null,
  constraint [PK_Vars_names] primary key clustered ([_Id]),
  constraint [FK_Vars_names_Vars_Id_M_Vars] foreign key ([Vars_Id]) references [M].[Vars] ([Id]) on delete cascade,
  constraint [FK_Vars_names_Item_M_Names] foreign key ([Item]) references [M].[Names] ([Id])
);
go
insert into [M].[Names] ([x])values (10);declare @M_Names_Id0 bigint = @@identity;insert into [M].[Names] ([x], [y])values (100, N'lo');declare @M_Names_Id1 bigint = @@identity;insert into [M].[Names] ([x], [y])values (20, N'hi');declare @M_Names_Id2 bigint = @@identity;insert into [M].[Vars] ([i])values (10);declare @M_Vars_Id0 bigint = @@identity;insert into [M].[Vars] ([i])values (1);declare @M_Vars_Id1 bigint = @@identity;insert into [M].[Vars_names] ([Item], [Vars_Id])values (@M_Names_Id0, @M_Vars_Id0);insert into [M].[Vars_names] ([Item], [Vars_Id])values (@M_Names_Id1, @M_Vars_Id0);insert into [M].[Vars_names] ([Item], [Vars_Id])values (@M_Names_Id2, @M_Vars_Id1);

Any depth of nested entity reference is supported.

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