Adding Folders (Modeling Services)

Article
09/22/2010

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

SQL Server Modeling Services uses Folders to organize and secure model data. This topic explains the Folder pattern and reviews the available types for adding Folders to extents.

To use these Folder patterns, you must reference the Repository.dll assembly and import either the System module or the Repository.Item module depending on the scenario. For more information, see SQL Server Modeling Services Patterns.

This topic discusses the process of adding a Folder to your “M” models. For more information about how to create Folders in the Modeling Services database, see Using Folders and Ids.

Modeling Services Folders Overview

The Repository.Item::FoldersTable extent stores hierarchical Folder details. Extents that participate in the Modeling Services Folder pattern contain a field named Folder that is of type FoldersTable. The following example demonstrates adding a Folder field to a PhoneNumbers extent:

module Contact
{
    import Repository.Item;
    
    PhoneNumbers :
    {( 
        {
            Folder : FoldersTable;

            Work : Text;

            Home : Text?;

            Mobile : Text?;
        }
    )*};
}

Adding a Foreign Key to FoldersTable

In the Modeling Services database, the “M” code in the previous example creates a foreign key that references the Id column of the [Repository.Item].[FoldersTable] table. This occurs due to the normal translation of “M” references to SQL Server database constructs. For more information about foreign keys in SQL Server, see FOREIGN KEY Constraints.

Although the foreign key exists, it does not completely comply with the Modeling Services Folder pattern. The pattern specifies that the foreign key should use cascading referential integrity. This simply means that if the Folder in the [Repository.Item].[FoldersTable] table is deleted, any rows that reference that Folder should also be deleted. For more information about cascading referential integrity, see Cascading Referential Integrity Constraints.

To correctly add a foreign key reference with a cascading referential integrity constraint, you must call the [Repository.Item].[AddFoldersForeignKey] stored procedure. The following Transact-SQL statement demonstrates how to make this call.

execute [Repository.Item].[AddFolderForeignKey]
        @schema = N'Contact',
        @baseName = N'PhoneNumbers'

You can call this from a tool like SQL Server Management Studio, or you can include this statement in a Post.sql file that gets embedded into your “M” image file.

Instead of calling the [Repository.Item].[AddFoldersForeignKey] procedure directly, you can use the PatternApplication sample. For each extent that uses Folders, add a PatternApplication::EntityPatternApplications record with the Pattern field set to Patterns.AddFolderForeignKey. For more information about the PatternApplication sample, see Using the PatternApplication Sample. The PatternApplication sample internally calls the [Repository.Item].[AddFolderForeignKey] procedure.

Adding Folders with System Types

As with the identity pattern, there are types that add the Folder field to your model. These types reside in the System domain. Several of the types also add the identity field, Id. You should use these types to ensure the consistent application of both the identity and Folder patterns in your models.

Note

Note that the details regarding each identity pattern are not covered in this topic. For more information about the identity patterns and their use, see Choosing an Identity Pattern (Modeling Services).

HasFolder

The HasFolder type contains a field named Folder of type Repository.Item::FoldersTable. The following example uses this type to add a Folder to the PhoneNumbers extent using the HasFolder type.

module Contact
{
    import System;
    
    PhoneNumbers :
    {( 
        HasFolder &
        {
            Work : Text;

            Home : Text?;

            Mobile : Text?;
        }
    )*};
}

This example imports System module to gain access to the Folder types. The specific fields of the PhoneNumbers extent are joined with the HasFolder type, which adds the Folder field.

HasFolderAndId

The HasFolderAndId type contains both a Folder field and an identity field named Id. This is equivalent to using HasFolder and HasId.

HasFolderAndAutoId

The HasFolderAndAutoId type contains both a Folder field and an identity field named Id. The identity field is automatically assigned. This is equivalent to using HasFolder and HasAutoId.

HasFolderAndSequenceId

The HasFolderAndSequenceId type contains both a Folder field and an identity field named Id. The identity field is automatically assigned using Modeling Services sequence objects. This is equivalent to using HasFolder and HasSequenceId.

Assigning Folders to New Records

When you add records to an extent that uses Folders, you must specify the Folder to use. There are several ways to accomplish this goal.

Using the FoldersTable selector

Folders have a hierarchical organization and are labeled by name. The Modeling Services database contains a [Repository.Item].[PathsFolder] function that returns the identifier of a Folder based on the name that you pass. Although you can call this function in an “M” assignment, you cannot assign this numeric identifier directly to the Folder field. Instead, you must use the FoldersTable selector to select the FolderTable entity based on that identifier. The example below assumes that the user previously created a Folder named SchoolContactsFolder in the Modeling Services database.

    PhoneNumbers
    { 
        { 
            Folder => FoldersTable(PathsFolder("SchoolContactsFolder")),
            Work => "555-555-5555",
            Home => "555-555-5555",
            Mobile => "555-555-5555" 
        } 
    }

Using the TargetFolder keyword

It is also possible to assign the Folder field to a default of TargetFolder in your “M” model. This tells the “M” compiler that you want to specify the actual Folder to use when the model is loaded into the Modeling Services database. Note that you must explicitly add the Folder field to use the TargetFolder feature. In the example below, the PhoneNumbers extent has been modified to add the Folder field and assign it to TargetFolder.

    PhoneNumbers :
    {( 
        HasAutoId &
        {
            Folder : FoldersTable => TargetFolder();

            Work : Text;

            Home : Text?;

            Mobile : Text?;
        }
    )*};

The following code creates a new PhoneNumbers record in “M”.

    PhoneNumbers
    { 
        { 
            Work => "555-555-5555",
            Home => "555-555-5555",
            Mobile => "555-555-5555" 
        } 
    }

In this example, the new record does not specify a Folder value, because the TargetFolder was used as the default value for the Folder field. To use this feature, you first compile this “M” code into an image file (.MX). The Mx.exe tool can be used to load that image into the database. Use the targetFolder parameter for the Mx.exe tool to specify the Folder that should be applied when the image is loaded.