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

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

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]

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

• The procedure looks for the existence of a sequence object defined by the @sequenceSchema and @sequence parameters. If the sequence object does not exist in the [Repository].[IdSequencesTable] table, it is created with the [Repository].[CreateIdSequence] stored procedure. This sequence object supplies unique identifiers for new rows in the target view. For more information, see  Identifier Design Patterns.
  
  
• The [Repository.Item].[AddFolderForeignKey] stored procedure adds a foreign key constraint to the Folder column of the target table that references the Id column of the [Repository.Item].[FoldersTable] table. For more information, see  Table Design Patterns.
  
  
• The [Repository.Item].[AddViewsInsteadOfTriggers] stored procedure creates three triggers on the target view that correspond to the insert, update, and delete operations. These triggers make the target view updatable, making it possible to change the data in the underlying base table using the view. For more information, see  View Design Patterns.
  
  
• The procedure grants select and update permissions on the view to the RepositoryReader and RepositoryReaderWriter roles.
  
  
• If the “Oslo” repository was installed with auditing enabled, the [Repository.Item].[AddAuditing] stored procedure enables auditing support for the target table. For more information, see  "Oslo" Repository Change Tracking.
  
  
• If the “Oslo” repository was installed with change tracking enabled, the [Repository.Item].[AddChangeTracking] stored procedure enables change tracking support for the target table. For more information, see  "Oslo" Repository Change Tracking.
  
  

Concepts