Extending the Database Features of Visual Studio
You can extend Visual Studio Premium or Visual Studio Ultimate by creating feature extensions. These feature extensions enable you to extend features of Visual Studio Premium or Visual Studio Ultimate such as Refactoring, Data Generation, Unit Testing, and Database Code Analysis. When you create feature extensions, you are using an existing framework for the base feature, so the amount of code that you have to write is greatly reduced.
Feature extensions have well-defined extensibility points. You do not have to have the Visual Studio SDK to create feature extensions for Visual Studio Premium or Visual Studio Ultimate.
Common High-Level Tasks
High-Level Task |
Supporting Content |
|---|---|
Learn about concepts in database extensibility: Before you extend those features, you should understand how extensibility works in Visual Studio Premium or Visual Studio Ultimate. A database schema provider implements all the services that are specific to a particular brand and version of a database (such as SQL Server 2008). This includes the parser that reads and writes the scripts for that database; the Script Domain Object Model (Script DOM) that represents the scripts; and the Schema Model that models the objects, relationships, and properties of the database objects. In Visual Studio Premium or Visual Studio Ultimate, whenever you interact with a feature or feature extension, those features and feature extensions operate on some combination of the DSP services, the Script DOM, and the Schema Model. |
|
Add support for new types of database refactoring: You can create feature extensions for database refactoring to enable new types of database refactoring. Each new refactoring type also requires one or more new refactoring contributors. |
|
Add support for new targets of database refactoring: You can enable an existing type of database refactoring to update a new type of artifact, such as a text file, or output from a third-party application that contains database information. If you create a new refactoring type, you must implement one or more refactoring contributors to enable that type to operate on the artifacts that are contained in your database project. |
|
Define new database code analysis rules: You can define new database code analysis rules that look for issues that are not detected by the rules that are included with Visual Studio Premium or Visual Studio Ultimate. |
|
Create custom data generators: Custom data generators can be used to generate realistic test data that does not disclose sensitive information. You can create custom data generators to supplement the data generators that are contained in Visual Studio Premium or Visual Studio Ultimate. |
|
Add custom database unit test conditions: By defining a custom test condition, you can verify the behavior of a database object in ways that the built-in unit test conditions do not support. |
|
Customize database project behavior: By defining a custom database project feature, you can modify project behavior in ways that the built-in database projects do not support. |
Modeling a Database
In order to model a database, Visual Studio models both the scripts that make up the Data Definition Language (DDL) of the database and the database that would result if you executed those scripts. The model of the DDL scripts is provided by the Script DOM. The model of the resulting database is provided by the Schema Model.
Data Flow Between Extensibility Components
The following diagram shows how the data flows through these components.
Data Flow Between Extensibility Components
.png "Data Flow between Extensibility Components")
The definitions of the database objects are persisted in the database project in the form of DDL scripts. When you open a database project, those scripts are read and two models are populated: the Script DOM model and the Schema Model. Features of Visual Studio Premium interact with both models, and when you save changes to database objects, those changes are persisted back out into DDL scripts.
Extensibility Components in Database Edition
The following diagram shows the components that interact to enable you to extend the features of Database Edition.
Communication Between Extensibility Components
.png "Extensibility Components of Database Edition")
When you open or create any database project, the extension manager component loads any registered database schema providers. When you use particular features for a database schema provider (DSP), the extension manager component loads the features and their extensions that support that DSP. For example, when you use the rename refactoring features to rename objects in a SQL Server 2008 database, the refactoring feature is loaded, together with the refactoring types and contributors for SQL Server 2008.
The Extension Manager
When you run Visual Studio Premium or Visual Studio Ultimate, all database projects, schema providers, features, and feature extensions interact with the singleton ExtensionManager. The extension manager loads a single instance derived from DatabaseSchemaProvider for each database project. For example, when Visual Studio Premium or Visual Studio Ultimate opens a Microsoft SQL Server 2005 project, the extension manager loads an instance of Sql90DatabaseSchemaProvider (which it derived from DatabaseSchemaProvider).
The extension manager loads extensions based on the type of interface implemented by those extensions. For example, when you use the database unit testing feature, the extension manager returns a list of registered extensions that inherit the TestCondition base class, and whose extensions are compatible with the database schema provider for the database project.
Feature Extension Compatibility
A feature, such as refactoring or static code analysis, is composed of components that are specific to a DSP and components that support all DSPs (DSP-agnostic components). When you define a feature extension, you declare the compatibility of that extension with a specific DSP or with a base DSP so that the extension is only loaded for appropriate project types. For example, you could declare that your extension was compatible either with Sql90DatabaseSchemaProvider (to restrict it to SQL Server 2005 projects) or with SqlDatabaseSchemaProvider (the base class for SQL Server 2005 and SQL Server 2008 DSPs). You can also declare an extension to be compatible with multiple, specific DSPs. You might use this approach if you are not sure whether future releases might break your feature. To declare a feature to be compatible with all DSPs, declare it as compatible with the DatabaseSchemaProvider base class.
Examples
Define extension compatible with one DSP:
// SqlSchemaObjectDesigners is defined as compatible with all Sql
// database services providers.
[DatabaseSchemaProviderCompatibility (typeof(Sql90DatabaseSchemaProvider))]
internal class SqlSchemaObjectDesigners : ISchemaObjectDesigners
{
}
Define extension compatible with multiple DSPs:
// Extension InconclusiveCondition is defined as compatible with all
// SQL Server database services providers and Oracle database
// services providers.
[DatabaseSchemaProviderCompatibility (typeof(SqlDatabaseSchemaProvider))]
[DatabaseSchemaProviderCompatibility (typeof(OracleDatabaseSchemaProvider))]
public sealed class InconclusiveCondition : TestCondition
{
}
Define extension compatible with all DSPs:
// Extension ReportingService is defined as compatible with all
// database services providers.
[DatabaseSchemaProviderCompatibility (typeof(DatabaseSchemaProvider))]
internal class ReportingService : IReportingService
{
}
Define extension compatible with none of the DSPs:
// Extension ExecutionTimeCondition is defined as compatible with no
// database services providers. That means if a feature
// has an ExtensionManager constructed with null, it will load
// those extensions defined as binding to
// DspCompatibilityCategory.None
[DatabaseSchemaProviderCompatibility (DspCompatibilityCategory.None)]
public sealed class ExecutionTimeCondition : TestCondition
{
}
Types of Feature Extensions
You can create feature extensions that enhance the capabilities of several of the Visual Studio Premium or Visual Studio Ultimate features. The following table describes the types of extensions that you can create.
Feature |
Extension Type |
Description |
|---|---|---|
Database Unit Testing |
Unit Test Conditions |
You can add custom assertions to determine the success or failure of your tests. Most of the unit testing APIs are public but do not represent an extensibility point. Those APIs are used to create database unit tests that are written in managed code, such as Visual C# or Visual Basic. For more information, see Define Custom Conditions for Database Unit Tests. |
Data Generation |
Data Generators |
You can use the extensibility API to create custom data generators if the data generators are provided with Visual Studio Premium or Visual Studio Ultimate. For more information, see Generate Specialized Test Data with a Custom Data Generator. |
Database Code Analysis |
Code Analysis Rules |
You can define your own code analysis rules to check for specific issues in your database code. For more information, see Create and Register Additional Rules for Analyzing Database Code. |
Database Refactoring |
Refactoring Targets |
You can extend existing refactoring types to operate on new targets, such as new file types. For more information, see Walkthrough: Extending Database Rename Refactoring to Operate on Text Files. |
Database Refactoring |
Refactoring Types |
You can create new refactoring types, such as replacing nested conditionals with guard clauses. For more information, see Create Custom Database Refactoring Types or Targets. |
Core Components of Database Schema Providers
A database schema provider (DSP) is composed of three groups of components:
Script DOM — an object model and supporting services that model an arbitrary SQL script that includes both DDL and DML statements.
Schema Model — an object model and supporting services that model the objects in a database instance, such as tables, views, and stored procedures.
User Interaction Services — a collection of services that enable core components to access user interface resources, such as the strings that represent the names of objects, the icons that represent the object types and categories, and the hierarchies in which to display those objects.
The primary functions of a DSP are to enable processing of DDL scripts into both Script DOM and Schema Model representations, and to enable the reverse function of reproducing scripts from the two model representations.
The Script DOM
A Script DOM provides implementation that parses a DDL script into an object model that represents the script. The Script DOM also provides implementation to reproduce the original script from the model.
The following diagram shows how data flows through the Script DOM.
Data Flow through Script DOM
.png "Data flow through the Script DOM")
The Script DOM parser translates the script, stored in an unstructured text file, into an object that inherits the IScriptFragment interface. The script generator in Script DOM takes an object that inherits the IScriptFragment interface and produces the original script. In the database schema providers for SQL Server, which are included with Visual Studio Premium or Visual Studio Ultimate, IScriptFragment abstracts an Abstract Syntax Tree (AST) and a token stream.
Tokens provide an unstructured representation of a script. Each token in the collection has:
The token type (such as keyword or string literal)
The token string
The source file in which the token occurred
The offset within that file at which the token occurred
Abstract Syntax Trees (ASTs) provide a structured representation of the script. Each node in the AST represents a batch of statements, a statement, or a component of a statement (such as an expression). ASTs are used to analyze scripts after they have been parsed. ASTs are also used to programmatically build scripts.
The Schema Model
The Schema Model representation is an interface-based object model that models a live database instance. Interfaces are arranged in a derivation hierarchy that includes a single abstract layer that is shared by all DSPs, together with any number of abstract layers targeting more-specific model details. For example, the ISql90Table interface inherits ISqlTable, which inherits the abstract layer interface IDatabaseTable. The Schema Model takes the IScriptFragment objects and translates them into Schema Model elements and also performs the reverse conversion, from Schema Model Elements into IScriptFragment objects. The Schema Model is composed of some number of Model Composer implementations. Each implementation creates a model from some other resource in the system. For example, the ScriptModelComposer composes a model from the script files maintained in the database project.
The Schema Model infrastructure (ModelStore and its supporting classes) implements integrated Script DOM references directly from model elements. This enables modeling of objects, such as stored procedures, which contain arbitrary scripts.
A Schema Model consists of a collection of elements and annotations. Elements describe the artifact being modeled (such as tables, views, stored procedures, triggers, columns, and so on). Annotations are used to associate arbitrary data with the model. Annotations are used by the database project core components, and they can also be used by project features and feature extensions. Both elements and annotations can be named or anonymous.
Model Elements
Elements are composed of properties and relationships. Properties represent basic data such as integers, Boolean values, and strings, and they are used to capture the details of the model. Relationships represent named and typed connections between elements. For example, an ISqlTable element maintains a relationship of type ISqlColumn named "Columns" that associates the table with its columns.
There are three basic types of relationships:
Peer — represents a dependency by one element on another element in an arbitrary manner. In SQL Server, the relationship between a view and a table would best be modeled as a peer relationship.
Composing — represents one element that consists of other elements. In SQL Server, the relationship between a table and its columns is best modeled as a composing relationship. A key principle of a composing relationship is that the two elements are created at the same time, as a single action. The elements do not have a dependency order, for creation or removal. However, the dependency direction of parent to child is maintained by the model in order to enable associative dependencies. For example, if a column has a dependency on a particular type, the parent table’s dependency on its composing column means that the table also depends on the type.
Hierarchical — represents a hierarchy. Hierarchical relationships differ from composing relationships because the dependency direction is from child to parent (instead of the other way around). An example in SQL Server is the relationship between a schema and an owned object such as a table or a view. A table participates in a hierarchical relationship with its schema.
The following diagram shows the three different types of relationships that can be represented in the Schema Model.
Object Relationships in Schema Model
.png "Object relationships in the schema model")
Each relationship in a model declares whether it is a multi-relationship or a single relationship. In all cases, an element can maintain an empty relationship. Models can be incomplete models of the real artifact.
Elements are interface-based in order to support the following features in a store implementation:
Strong element typing (identity)
Multiple inheritance
To support both DSP-agnostic and DSP-specific parts of the system
To support sharing of "qualities" across otherwise unrelated element types
Version flexibility and extensibility
All model element classes implement the public IModelElement interface. Properties, relationships, and annotations can be accessed by using the ModelStore metadata API from this class. The interfaces, such as IDatabaseTable, serve to provide access (bound at compile time) to properties and relationships in a simpler and more-maintainable manner.
Model Annotations
Like elements, annotations are composed of properties. Unlike elements, annotations do not participate in relationships. Instead, annotations are attached to elements or to the model store itself. Annotations are strongly typed, and a single instance of an annotation can be attached to multiple elements in addition to the model store. Model-attached annotations represent an annotation instance that is "global" to the model.