Using the AddStandardPatterns Stored Procedure

[This is prerelease documentation and is subject to change in future releases. Blank topics are included as placeholders.]

The [Repository.Item].[AddStandardPatterns] stored procedure adds multiple “Oslo” repository design patterns to existing database tables and views. For more information about specific design patterns, see "Oslo" Repository Design Patterns. The [Repository.Item].[AddStandardPatterns] stored procedure is a convenience to users that want to apply the most common design patterns with a single call. Note that it is not required to call this procedure to add the design patterns. In some scenarios, a user might want to apply only a subset of the standard patterns to a data model. This can be done by calling the specific stored procedures related to each pattern. This topic discusses the requirements for using the [Repository.Item].[AddStandardPatterns] stored procedure and the patterns that this procedure applies.

Requirements

The [Repository.Item].[AddStandardPatterns] stored procedure operates on an individual table and view pair. The table and view must have the following characteristics:

  • The table's primary key must be integer-based (int or bigint).

  • The table's primary key must not be an IDENTITY column.

  • The table must have a column named Folder of type int.

  • The table name must follow the convention of using a plural base name appended by the word "Table".

  • The view name must use the plural base name.

  • The view should filter rows based on a join with the [Repository.Item].[ReadableFolders] function.

Many of these requirements are basic design patterns for tables and views. For more information, see Table Design Patterns and View Design Patterns. The following T-SQL example provides definitions for a [Contact].[AddressesTable] table and a [Contact].[Addresses] view that meet these basic requirements.

Tip

Note that if this schema has been previously added to the database, these statements may fail. You can completely remove the Contact schema and its associated objects using a custom stored procedure. For more information, see  Dropping an "Oslo" Repository SQL Schema.

use Repository
go
create schema [Contact] authorization [RepositoryOwner]
go
create table [Contact].[AddressesTable](
   [Id] bigint NOT NULL,
   [Folder] [Repository.Item].[FolderId] NOT NULL,
   [Street] nvarchar(max) NOT NULL,
   [City] nvarchar(max) NOT NULL,
   [State] nvarchar(max) NOT NULL,
   [ZipCode] int NOT NULL,
   constraint [PK_Addresses] primary key clustered 
   (
      [Id] asc
   ) on [PRIMARY]
) on [PRIMARY]
go

create view [Contact].[Addresses] 
    ([Id], [Folder], [Street], [City], [State], [ZipCode])
as
select top (9223372036854775807) [AT].[Id], [AT].[Folder], [AT].[Street], 
    [AT].[City], [AT].[State], [AT].[ZipCode]
from [Contact].[AddressesTable] as [AT] with (readcommitted)
inner join [Repository.Item].[ReadableFolders]() as [RCV]  on [AT].[Folder] = [RCV].[Folder];
go

Usage

The [Repository.Item].[AddStandardPatterns] stored procedure accepts the following parameters.

Parameter Description

@schema

The schema containing the target table and view.

@baseName

The base name for the target table and view.

@sequenceSchema

The schema that owns the sequence object. This parameter is optional and defaults to the value of the @schema parameter.

@sequence

The sequence object used to create new unique identifiers. This parameter is optional and defaults to the value of the @baseName parameter.

The following T-SQL example adds the standard design patterns to the previously created [Contact].[AddressesTable] table and [Contact].[Addresses] view.

exec [Repository.Item].[AddStandardPatterns] @schema = [Contact], @baseName = [Addresses]

Standard Design Patterns

This section describes the patterns applied by the [Repository.Item].[AddStandardPatterns] stored procedure. The following changes are applied to the target table and view:

See Also

Concepts

Designing Domains and Models using T-SQL
"Oslo" Repository Design Patterns

Fill out a survey about this topic for Microsoft.