Using the PatternApplication Sample

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

There are several different design patterns that specifically apply to SQL Server Modeling Services. To use these design patterns with your Microsoft code name “M” models, you must satisfy two requirements. First, some patterns require that the “M” code follow certain naming or structural conventions. Second, you must create a Post.sql file that calls Modeling Services stored procedures for applying each pattern.

The PatternApplication sample provides a convenient way to apply Modeling Services patterns to “M” models. It uses “M” to declaratively describe which patterns to apply. A Post.sql file is still required, but it simply calls the [Repository.Item].[ApplyStandardPatternsToModels] stored procedure.

Installing the PatternApplication Sample

Before you can use the PatternApplication sample, you must first install the sample into the target Modeling Services database. For more information, see How to: Install the PatternApplication Sample. This installs several tables in the Modeling Services database within the PatternApplication schema. It also installs the [Repository.Item].[ApplyStandardPatternsToModels] stored procedure.

Using PatternApplication Extents

The PatternApplication sample contains two extents: ModulePatternApplications and EntityPatternApplications. You create instances of the ModulePatternApplications extent to describe the Modeling Services patterns to apply to your “M” modules. You create instances of the EntityPatternApplications extent to describe the Modeling Services patterns to apply to your “M” extents. The following “M” code shows how to create these instances that specify the patterns to apply to a HumanResources module and its EmployeesTable extent.

module PatternApplication
{
    import HumanResources;

    ModulePatternApplications
    {
        HumanResources_Pattern
        {
            Module => about(HumanResources.EmployeesTable).Declaration.DefinedIn,
            Pattern => { Patterns.AlterSchemaPermissions }
        }
    }
    
    EntityPatternApplications  
    {
        Employees_Pattern
        {
            Module => about(HumanResources.EmployeesTable).Declaration.DefinedIn,
            Extent => about(HumanResources.EmployeesTable).Declaration,
            Pattern =>
            {
                Patterns.CreateIdSequence,
                Patterns.AddViewsInsteadOfTriggers,
                Patterns.AddFolderForeignKey,
                Patterns.AddAuditing,
                Patterns.AddChangeTracking
            }
        }        
    }
}

In this example, the instance of the ModulePatternApplications extent applies Modeling Services patterns to the HumanResources module, and the instance of the EntityPatternApplications extent applies Modeling Services patterns to the EmployeesTable extent. Referencing the HumanResources module and its extents requires an import of the HumanResources module using the import keyword. The about keyword accesses both the module and the extent, so the “M” compiler can verify that the target module and extent exist, which eliminates errors related to misspellings. For both the ModulePatternApplications and EntityPatternApplications instances, the Pattern field specifies one or more Modeling Services patterns to apply to the respective module or extent.

The following sections provide more detail on using the ModulePatternApplications and EntityPatternApplications extents. For more information about specific Modeling Services patterns, see SQL Server Modeling Services Patterns.

The ModulePatternApplications Extent

The ModulePatternApplications extent contains the following fields.

Field	Description
Module	The Module field specifies the target module for the specified Modeling Services patterns. This field references the Modules extent in the Langauge.Catalog domain.
Pattern	The Pattern field specifies the Modeling Services patterns to apply to the target module. This field references the Patterns extent in the PatternApplication domain.

The following table describes the possible module-specific values for the Pattern field.

Value	Description
Patterns.AlterSchemaPermissions	The Patterns.AlterSchemaPermissions pattern changes permissions on the database schema that is associated with the “M” module. This pattern changes the schema owner to the RepositoryOwner user. It grants the RepositoryAdministrator role full control over the schema. It grants the RepositoryService user insert, delete, update, select, and execute permissions on the schema.

The EntityPatternApplications Extent

The EntityPatternApplications extent contains the following fields.

Field	Description
Module	The Module field specifies the module that contains the target extent. This field references the Modules extent in the Language.Catalog domain.
Extent	The Extent field specifies the extent that requires Modeling Services patterns. This field references the Declarations extent in the Language.Catalog domain.
Pattern	The Pattern field specifies the Modeling Services patterns to apply to the target extent. This field references the Patterns extent in the PatternApplication domain.

The following table describes the possible extent-specific values for the Pattern field.

Value	Description
Patterns.CreateIdSequence	The Patterns.CreateIdSequence pattern specifies that a sequence object should be created for the target extent. This pattern is required if the target extent uses the HasSequenceId or HasFolderAndSequenceId types.
Patterns.AddViewsInsteadOfTriggers	The AddViewsInsteadOfTriggers pattern creates triggers on the view that is associated with the target extent. These triggers make the view updatable and are the foundation for the Modeling Services security model. In order to use this pattern, the extent name must have the word "Table" appended to it, such as EmployeesTable. There must also be an “M” computed value with the same name without the word "Table", such as Employees. This “M” computed value becomes a view in the database, and the triggers are added to that view.
Patterns.AddFolderForeignKey	The AddFolderForeignKey pattern creates a foreign key constraint from the Folder column of the target extent to the Id column of the [Repository.Item].[FoldersTable] table. To apply this pattern, you must have a FoldersTable column in the target extent that is named Folder. This can be done manually or by using the HasFolder, HasFolderAndAutoId, or HasFolderAndSequenceId types.
Patterns.AddAuditing	The AddAuditing pattern specifies that auditing should be enabled for the target extent. This requires the Modeling Services database to have auditing enabled.
Patterns.AddChangeTracking	The AddChangeTracking pattern specifies that change tracking should be enabled for the target extent. This requires the Modeling Services database to have change tracking enabled.

The Post.sql File

The PatternApplication extent instances describe the patterns that you want to apply. But you must call the [Repository.Item].[ApplyStandardPatternsToModels] stored procedure to apply those patterns. The easiest way to call this procedure is with a Post.sql file. A Post.sql file is a Transact-SQL script that you can add to your “M” project. After the “M” loader installs the models and instances in the database, it runs the Post.sql file. When using the PatternApplication sample, the Post.sql file always makes a single call to [Repository.Item].[ApplyStandardPatternsToModels].

execute [Repository.Item].[ApplyStandardPatternsToModels];
go

For a complete walkthrough of how to use the Post.sql file and other PatternApplication concepts, see Getting Started with the SQL Server Modeling CTP (SetupApplication Tutorial).

See Also

Tasks

How to: Install the PatternApplication Sample