Using Folders and Ids

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

This topic discusses the features available by using SQL Server Modeling Services Folder and identifier patterns in your domain models in Microsoft code name “M”.

Folders and Identifiers

You use the Folder pattern in “M”, SQL Server 2008, or by modifying values in “Quadrant” to group data in arbitrary ways regardless of the particular extent to which they conform. You use the identifier patterns to declare which values identify a particular instance of an extent. Together they allow you to group data and make use of Modeling Services patterns that secure and version those data groups and reference distinct data instances.

Although the projection of “M” code that uses these patterns can take any form, the default output of the current release targets T-SQL for use with SQL Server 2008. In SQL Server, the Folder pattern partitions data horizontally by any category you choose (instead of vertically by structure as tables do). In database terms, Folders are row labels.

Identifiers are fields that provide identification within some scope of data for a particular extent. (In SQL Server, it is typically the primary key identifier for a row of data.)

Folders

As data sets grow in an enterprise environment, database tables can become seas of vertically partitioned data that are related to the model – the table or tables – to which the rows of data belong. Although vertical partitioning creates data that is easily categorized by structure, it doesn’t easily categorize data by any other category that makes sense to you or your organization unless that category is represented by a SQL table or tables. Even if the tables in your organization do reflect some internal organization you currently have, creating new categorizations in the future would potentially require an entirely new database design.

The Modeling Services Folder pattern allows to you to identify specific groups of data for any purpose by adding a Folder field to an extent that can be associated with any data taxonomy that you want or that you have already. (In SQL terminology, using the Folder pattern implements logical horizontal partitioning – the categorization of data across tables and views.) The Folder pattern supports many features that file system Folders do – their categorized contents can be secured, versioned, and so on – hence the pattern name.

Using the Folder pattern to partition data horizontally for robust organizational labeling also enables support for the following Modeling Services features:

Applying security to a specific Folder. This supports the ability to prevent specific rows from being altered or read, or included in any replication scheme if the data should not be replicated.
Versioning your data. You can create new Folders for newer versions of an application and copy older data into them, enabling newer applications and older applications to continue to operate.

Using Folders to organize your data across models is a logical thing to do for your data taxonomy, whether you need any Modeling Services features or not. For example, the common language runtime (CLR) domain stores metadata about .NET assemblies. Because the CLR domain models use the Folder pattern you specify new Folders for each specific version of a .NET assembly that you load into the Modeling Services. You can then use “Quadrant” to view only those types from one version of the assembly -- even if you have the data from tens or hundreds of versions. This example does not depend on any other Modeling Services features except for the ability to label one set of data differently from another to make the viewing and management of that data easier.

The Folder pattern, however, becomes much more powerful when it is used to support Modeling Services security, versioning, globalization, and data management features based on the Folder and id patterns. An organization might classify Modeling Services data for security and manageability reasons, for example. To do so, it might:

Develop a Folder hierarchy that represents how it thinks about the data that is stored in the Modeling Services. For example, data about version 2.0 of the Paystub application might be in the Folder /Finance/Reporting/PayStub/2.0.
Assign Folder permissions to accounts and domain groups. For example, the operations staff responsible for the finance department’s reporting applications might be given read-write permissions on the Folder /Finance/Reporting and the PayStub service account might be given read permissions on /Finance/Reporting/PayStub/2.0.

The Folder pattern makes versioning data and applications logical, because you can deploy new data and the applications that use them in-place. For example, an organization that chooses to use the /Finance/Reporting/PayStub/1.0 Folder hierarchy can deploy a new version of the application by creating a new subfolder of /Finance/Reporting/PayStub/2.0. In this case, the old version of the application can continue to run against the Folder /Finance/Reporting/PayStub/1.0 while the new version can target the Folder /Finance/Reporting/PayStub/2.0. Once the deployment of the new PayStub application version was complete, the old version’s Folder can be either left in-place for reference purposes or removed. Patches to the 2.0 version of the application can happen in place.

The following instructions show one way to do Folder creation. A good walkthrough of the process using Visual Studio 2010 and the PatternApplication sample is Adding Modeling Services Patterns to the SetupApplication Model. For another example, see Loading Domain Models with Data.

For a more complete discussion of the Folder patterns and their location in the development lifecycle, see SQL Server Modeling Services Patterns.

With the current release, you explicitly control which Modeling Services patterns to apply to your “M” models. The following procedure shows one way to add the Folder pattern to a model. Note that this step focuses on the Folder pattern and does not add updatable views that control security. To see an example that includes the security pattern, see the Getting Started with the SQL Server Modeling CTP (SetupApplication Tutorial).

Using the Folder and Id Patterns

Creating Models that use the Folder and Id Patterns in “M”
1. First import that System and Repository.Item modules in order to use the types associated with Folders.
2. Create an extent in “M” that has a Folder field of type FoldersTable. The easiest way of doing this is to use one of the system-provided types in your extent definition. The type used in this example is HasFolderAndAutoId. For more information about other types, see Adding Folders (Modeling Services).
3. Create one or more initial values for the extent. These become rows in the database table for the extent. Assign the Folder field to a value of FoldersTable(PathsFolder("CustomFolderNameHere")), where CustomFolderNameHere is the name of the Folder with which the data is to be associated.
  
  Note
  
  The PathsFolder reference results in the invocation of the PathsFolder Function (Repository.Item), which returns the Folder id for the Folder string.
  
  The following code example shows a simple “M” example of the preceding steps.
```
module FolderSample
{
    import System;
    import Repository.Item;

    People  :
    {( 
        HasFolderAndAutoId &
        {
            Name : Text;

            Summary : Text;
        }
    )*};

    People
    { 
        { 
            Folder => FoldersTable(PathsFolder("SamplePersonFolder")),
            Name => "John",
            Summary => "Description for John"
        },
        {
            Folder => FoldersTable(PathsFolder("SamplePersonFolder")),
            Name => "Frida",
            Summary => "Description for Frida"
        }
    }
}
```
Projecting the Models and Data into the Modeling Services Database
1. At a SQL Server Modeling Services CTP Command Prompt, call mx createFolder CustomFolderNameHere /d:Repository (where CustomFolderNameHere is the name of the Folder) to add the targeted custom Folder to the default Modeling Services database. With the preceding sample code, the command is mx createFolder SamplePersonFolder /d:Repository.
2. Create a .sql file in which AddFolderForeignKey Procedure (Repository.Item) is called for the target module and extent name. An example using the preceding .m file is:
```
use Repository
execute [Repository.Item].[AddFolderForeignKey] N'FolderSample', N'People'; 
go
```
3. Compile the file to an image, referencing Repository.mx (for the HasFolderAndAutoId type and the PathsFolder function). Use the postSql switch to reference the Transact-SQL script that calls AddFolderForeignKey to add support for cascade delete. The postSql switch embeds the script into the image file. For example, a possible command string using the preceding code and .sql file is:
```
m.exe People.m /r:"C:\Program Files\Microsoft Oslo\1.0\bin\Repository.mx" /postSql:AddFolderForeignKey.sql
```
4. Install the image using Mx.exe and the d switch to specify the Modeling Services database. For example, a possible command string using the image created by the preceding step is:
```
mx install People.mx /d:Repository
```
  Warning
  
  In the current release, the use of the postSql switch results in an installed image that cannot be uninstalled. Iterative development in this scenario requires recreating the target database. For more information, see How to: Update an Existing Model by Recreating the SQL Server Modeling Services Database.

Identifiers

To be submitted.

Using Folders and Ids

Folders and Identifiers

Folders

Identifiers

See Also

Other Resources

Additional resources