How to: Modify a Domain to Support SQL Server Modeling Services Patterns

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

This is the first of six tasks that apply SQL Server Modeling Services patterns to a Microsoft code name “M” model. There are many different patterns that are specific to Modeling Services. These patterns include things such as Modeling Services Folders, updatable security views, sequence objects, change tracking, and auditing. Modeling Services provides these services through its Base Domain Library (BDL). For more information, see Base Domain Library (BDL).

This topic modifies the SetupApplication model to support several Modeling Services patterns. For more information about how to create the initial model, see Creating and Using the SetupApplication Model.

To add support for Modeling Services Folders

  1. In Visual Studio 2010, open the previously created SetupApplication project. For more information about how to create this project, see Creating and Using the SetupApplication Model.

  2. To include Folders, you must reference the Repository.dll assembly. In Solution Explorer, right-click References, and then click Add Reference.

  3. In the Add Reference dialog, select the Browse tab.

  4. Navigate to the bin directory of the SQL Server Modeling CTP, and then select Repository.dll. By default, this assembly is located at C:\Program Files\Microsoft Oslo\1.0\bin\Repository.dll. Click OK.

  5. Open the SetupApplication.m source file.

  6. In the SetupApplication module, add an import of the System and Repository.Item modules.

        import System;
        import Repository.Item;
    
  7. To add Folder support, change each extent by removing the Id field and add the HasFolderAndAutoId type. Also, remove the where identity clause after each extent because this is defined in the HasFolderAndAutoId type as well. The following code shows these changes for the Products extent. Repeat these changes for each extent.

        Products :
        {(
            HasFolderAndAutoId &
            {
                Name : Text;
    
                ProductId : Guid;
    
                UpgradeCode : Guid;
    
                Language : Integer32 => 1033; // English - United States
    
                Codepage : Integer32 => 1252; // Western European Latin
    
                Version : Text;
    
                Manufacturer : Text;
                    
            }
        )*};
    

To add support for updateable security views

  1. Open the SetupApplication.m file.

  2. For each extent, append the word Table to the end of the current extent name. For example, the Products extent becomes ProductsTable. Do this for each extent definition and for any field references to extents.

  3. Add one “M” computed value for each extent that matches the original extent names. These computed values become views in the Modeling Services database. The following example shows the Products computed values that are related to the ProductsTable extent. Repeat this pattern to create “M” computed values for each extent.

    Ee698823.Tip(en-us,VS.85).gifTip:
    To save time with these modifications, copy the completed code at the end of this topic.

        Products () : {{ Id: Integer64; Folder: FoldersTable; Name: Text; ProductId: Guid;
                UpgradeCode: Guid; Language: Integer32; Codepage: Integer32;
                Version: Text; Manufacturer: Text;}*}
        {
            ProductsTable where value.Folder.Id in ReadableFoldersView().Folder
            select 
            { 
                Id => value.Id, Folder => value.Folder, Name =>value.Name,
                ProductId => value.ProductId, UpgradeCode => value.UpgradeCode,
                Language => value.Language, Codepage => value.Codepage,
                Version => value.Version, Manufacturer => value.Manufacturer
            }
        }
    

    There are several important guidelines to follow for creating these computed values. The field list following the computed values name should match the fields in the target extent. Inside the computed values, query the target extent. In the previous example, note the query of the ProductsTable extent. The query only returns rows that the caller is allowed to see by comparing the Folder value of the row to the result from the ReadableFoldersView computed value. This is the typical security pattern for updatable views.

  4. To apply Modeling Services patterns by using the PatternApplication sample, you must export the target extents. After the list of import statements, add an export statement for each extent of the SetupApplication module.

        export ProductsTable;
        export PackagesTable;
        export MediaTable;
        export FeaturesTable;
        export DirectoriesTable;
        export ComponentsTable;
        export FilesTable;
    

To build the SetupApplication project

  1. In Solution Explorer, right-click MyNotepad.m, and then click Exclude From Project.

    Ee698823.Important(en-us,VS.85).gif Note:
    You must temporarily exclude the MyNotepad.m file from the build because you have not yet changed it to reflect the changes to the SetupApplication model.

  2. On the Build menu, click Build Solution.

  3. Verify that the build succeeded.

    Ee698823.note(en-us,VS.85).gifNote:
    You might see warnings in the output window about EDM generation. EDM generation does not currently support references to external extents, like Repository.Item::FoldersTable. These warnings do not affect the remaining steps in this tutorial.

The next step uses the PatternApplication sample to describe the specific Modeling Services patterns to apply to the SetupApplication model. For more information, see How to: Describe Required Patterns with the PatternApplication Sample.

Example

The following example shows the completed modifications of the SetupApplication.m file.

module SetupApplication
{
    import System;
    import Repository.Item;

    export ProductsTable;
    export PackagesTable;
    export MediaTable;
    export FeaturesTable;
    export DirectoriesTable;
    export ComponentsTable;
    export FilesTable;

    ProductsTable :
    {(
        HasFolderAndAutoId &
        {
            Name : Text;

            ProductId : Guid;

            UpgradeCode : Guid;

            Language : Integer32 => 1033; // English - United States

            Codepage : Integer32 => 1252; // Western European Latin

            Version : Text;

            Manufacturer : Text;
                
        }
    )*};


    PackagesTable : 
    {(
        HasFolderAndAutoId &
        { 
            Product : ProductsTable;

            Keywords : {Text*};

            Description : Text?;

            Comments : Text?;

            Manufacturer : Text;

            InstallerVersion : Integer32 => 200; // Windows Installer 2.0

            Language : Integer32 => 1033; // English - United States

            Compressed : Logical => true;

        }
    )*};

    
    MediaTable : 
    {(
        HasFolderAndAutoId &
        {   
            Product : ProductsTable;

            Cabinet : Text;

            EmbedCab : Logical;

        }
    )*};
    
    
    FeaturesTable : 
    {(
        HasFolderAndAutoId &
        {   
            Product : ProductsTable;

            Level : Integer32 => 1;

            ComponentRefs : {ComponentsTable*};

        }
    )*};


    DirectoriesTable : 
    {(
        HasFolderAndAutoId &
        {  
            SpecialType : Text?;

            Product : ProductsTable;

            ParentDirectory : DirectoriesTable?;

            Name : Text?;

        }
    )*};
            


    ComponentsTable : 
    {(
        HasFolderAndAutoId &
        { 
            ComponentGuid : Guid => NewGuid();

            Directory : DirectoriesTable;

        }
    )*};


    FilesTable : 
    {(
        HasFolderAndAutoId &
        { 
            Component : ComponentsTable;
                
            Name : Text;

            Source : Text;

        }
    )*};


    Products () : {{ Id: Integer64; Folder: FoldersTable; Name: Text; ProductId: Guid;
            UpgradeCode: Guid; Language: Integer32; Codepage: Integer32;
            Version: Text; Manufacturer: Text;}*}
    {
        ProductsTable where value.Folder.Id in ReadableFoldersView().Folder
        select 
        { 
            Id => value.Id, Folder => value.Folder, Name =>value.Name,
            ProductId => value.ProductId, UpgradeCode => value.UpgradeCode,
            Language => value.Language, Codepage => value.Codepage,
            Version => value.Version, Manufacturer => value.Manufacturer
        }
    }
    
    Packages () : {{ Id: Integer64; Folder: FoldersTable; Product: ProductsTable;
            Description: Text; Comments:Text; Manufacturer: Text;
            InstallerVersion: Integer32; Language: Integer32; Compressed: Logical;}*}
    {
        PackagesTable where value.Folder.Id in ReadableFoldersView().Folder
        select
        {
            Id => value.Id, Folder => value.Folder,
            Product => value.Product, Description => value.Description, 
            Comments => value.Comments, Manufacturer => value.Manufacturer,
            InstallerVersion => value.InstallerVersion, Language => value.Language,
            Compressed => value.Compressed
        }
    }

    
    Media () : {{ Id: Integer64; Folder: FoldersTable; Product: ProductsTable;
            Cabinet: Text; EmbedCab: Logical;}*}
    {
        MediaTable where value.Folder.Id in ReadableFoldersView().Folder
        select
        {
            Id => value.Id, Folder => value.Folder,
            Product => value.Product, Cabinet => value.Cabinet,
            EmbedCab => value.EmbedCab
        }
    }

    
    Features () : {{ Id: Integer64; Folder: FoldersTable; Product: ProductsTable;
            Level: Integer32;}*}
    {
        FeaturesTable where value.Folder.Id in ReadableFoldersView().Folder
        select
        {
            Id => value.Id, Folder => value.Folder,
            Product => value.Product, Level => value.Level
        }
    }

    
    Directories () : {{ Id: Integer64; Folder: FoldersTable; SpecialType: Text;
            Product: ProductsTable; ParentDirectory: DirectoriesTable;
            Name: Text;}*}
    {
        DirectoriesTable where value.Folder.Id in ReadableFoldersView().Folder
        select
        {
            Id => value.Id, Folder=> value.Folder,
            SpecialType => value.SpecialType, Product => value.Product,
            ParentDirectory => value.ParentDirectory, Name => value.Name
        }
    }

    
    Components () : {{ Id: Integer64; Folder: FoldersTable;
            ComponentGuid: Guid; Directory: DirectoriesTable;}*}
    {
        ComponentsTable where value.Folder.Id in ReadableFoldersView().Folder
        select
        {
            Id => value.Id, Folder => value.Folder,
            ComponentGuid => value.ComponentGuid, Directory => value.Directory
        }
    }

    
    Files () : {{ Id: Integer64; Folder: FoldersTable; Component: ComponentsTable;
            Name: Text; Source: Text;}*}
    {
        FilesTable where value.Folder.Id in ReadableFoldersView().Folder
        select
        {
            Id => value.Id, Folder => value.Folder, Component => value.Component,
            Name => value.Name, Source => value.Source 
        }
    }    
}

See Also


Show: