Compartir a través de

How to: Explore the Applied SQL Server Modeling Services Patterns in the Database

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

This is the fifth of six tasks that apply SQL Server Modeling Services patterns to a Microsoft code name “M” model. In this topic, you use SQL Server Management Studio to look at the implementation of some of the Modeling Services patterns for the SetupApplication model.

To view the SetupApplication schema permission changes

  1. In SQL Server Management Studio, in the Connect to Server dialog, type the Server name of the instance of SQL Server that contains the SetupApplicationDB database. If you are connecting to the instance on the current machine, you can specify (local) for the server name.

  2. Specify the authentication properties, and then click the Connect button.

  3. If the Object Explorer window is not displayed, click the View menu and then click Object Explorer.

  4. In Object Explorer, expand Databases, SetupApplicationDB, Security, and Schemas. You should see a list of schemas in the SetupApplicationDB database.

  5. Right-click the SetupApplication schema, and then click Properties.

  6. On the General page, note that the schema owner is RepositoryOwner.

  7. In the left pane, click Permissions.

  8. On the Permissions page, note that the RepositoryAdministrator role and RepositoryService user have special permissions for the SetupApplication schema.

These schema permission changes are a result of specifying the Patterns.AlterSchemaPermissions pattern for the SetupApplication module. For more information, see How to: Describe Required Patterns with the PatternApplication Sample

To examine the Folder pattern

  1. In Object Explorer, expand Databases, SetupApplicationDB, and Tables. You should see a list of tables in the SetupApplicationDB database.

  2. Expand the table named SetupApplication.ProductsTable.

  3. Expand the Keys node to see the foreign keys. Note that a foreign key has been added from the Folder column of the table to the Id column of the [Repository.Item].[FoldersTable] table.

    Note

    Folders also play an important part in securing updatable views. See the next section in this topic for more information.

This foreign key connection to the [Repository.Item].[FoldersTable] table is a result of specifying the Patterns.AddFolderForiegnKey pattern for the extent. For more information, see How to: Describe Required Patterns with the PatternApplication Sample

To examine the updateable security views

  1. In Object Explorer, collapse the Tables node and expand the Views node for the SetupApplicationDB database.

  2. Expand the view named SetupApplication.Products.

  3. Expand the Triggers node. Observe the three INSTEAD OF triggers that have been added to the view: Products_InsteadOfDelete_Trigger, Products_InsteadOfInsert_Trigger, Products_InsteadOfUpdate_Trigger. These triggers enable the views to be used like tables, allowing inserts, updates, and deletes.

  4. Right-click the SetupApplication.Products view in Object Explorer, and then click Edit Top 200 Rows.

  5. Fill in the values for a new product instance by using the field information below:

    • Folder = 1

    • Name = MyProduct

    • ProductId = d13a157f-73a9-4c5e-9499-e0b8352b1699

    • UpgradeCode = b6a3f965-3f36-4636-a922-b55a07080432

    • Language = 1033

    • Codepage = 1252

    • Version = 1

    • Manufacturer = MyCompany

    Note

    In this example, the new row is added to the system-provided Repository Folder by assigning the Folder field to a value of 1. This is done for simplicity, but you typically would not add data to the Repository Folder. For more information about how to create and use a custom Folder, see the final topic in this tutorial, How to: Create and Use Folders for New Model Instances.

  6. Press ENTER to commit the new row.

    The SetupApplication.Products view supports this update operation, because it has an INSTEAD OF trigger defined for the insert command. This trigger redirects the command to operate on the ProductsTable table. It also verifies that you have the appropriate permissions to perform this action based on Folder permissions.

  7. On the Query Designer menu, click Execute SQL to refresh the new row.

These triggers that implement the updateable security views are a result of specifying the Patterns.AddViewsInsteadOfTriggers pattern for the extent. For more information, see How to: Describe Required Patterns with the PatternApplication Sample

The final step in the tutorial creates a new Modeling Services Folder and uses that Folder to create a new instance of the SetupApplication model. For more information, see How to: Create and Use Folders for New Model Instances.

See Also

Concepts

Adding Modeling Services Patterns to the SetupApplication Model

Other Resources

Getting Started with the SQL Server Modeling CTP (SetupApplication Tutorial)