Working with Data Definition Language (DDL)

Telerik Data Access supports data definition language (DDL). This allows you to create or delete a database instance based on the connection string and the metadata provided by the OpenAccessContext.

In this topic:

• Obtaining the Schema Handling Instance
• Schema Migration Details
  • CheckExtraIndexes
  • Calculating the IsTrivial, IsExtending and IsComplex Properties
• Forcing Schema Update

Obtaining the Schema Handling Instance

The OpenAccessContext exposes a method named GetSchemaHandler that returns the schema handling instance. With the schema handler in hand, there are several methods you can use to automatically create a database script, see if the database exists, and/or create the database.

• CreateDatabase - creates the database using the current connection string and the metadata provided by the OpenAccessContext.
• DatabaseExists - checks if the database that is specified as the database in the current connection string exists on the data source.
• CreateDDLScript - generates a data definition language (DDL) script that creates schema objects (tables, primary keys, foreign keys) by using the metadata provided by the OpenAccessContext.
• CreateUpdateDDLScript - generates a migration data definition language (DDL) script.
• ExecuteDDLScript - executes a data definition language (DDL) script.

Additionally, the ISchemaHandler interface exposes a method named CreateUpdateInfo, which provides information about the statements included in the script.

The following example shows you how to generate a database based on an existing model.

using (FluentModel dbContext = new FluentModel())
{
   Telerik.OpenAccess.ISchemaHandler schemaHandler =  dbContext.GetSchemaHandler();
   string script = null;
   if (schemaHandler.DatabaseExists())
   {
       script = schemaHandler.CreateUpdateDDLScript(null);
   }
   else
   {
       schemaHandler.CreateDatabase();
       script = schemaHandler.CreateDDLScript();
   }
   if (!string.IsNullOrEmpty(script))
   {
       schemaHandler.ExecuteDDLScript(script);
   }                
}

Using dbContext As New FluentModel()
 Dim schemaHandler As Telerik.OpenAccess.ISchemaHandler = dbContext.GetSchemaHandler()
 Dim script As String = Nothing
 If schemaHandler.DatabaseExists() Then
  script = schemaHandler.CreateUpdateDDLScript(Nothing)
 Else
  schemaHandler.CreateDatabase()
  script = schemaHandler.CreateDDLScript()
 End If
 If Not String.IsNullOrEmpty(script) Then
  schemaHandler.ExecuteDDLScript(script)
 End If
End Using

Schema Migration Details

In the previous example, the SchemaHandler API generates a DDL script in the form of a string containing the needed SQL statements to synchronize the database. However, sometimes this could introduce breaking changes for the application already running against the current schema. And there is no way to be aware of those changes without parsing/looking at the script itself.

The ISchemaHandler interface exposes a method named CreateUpdateInfo. It returns a new SchemaUpdateInfo object. This object contains not only the DDL script but many Boolean flags, which indicate what kind of changes are contained in the script. This way you could easily detect whether a breaking change will be introduced and cancel the database migration.

The most important properties exposed by the SchemaUpdateInfo class are:

• IsTrivial - the generated script contains schema artifacts without influencing the existing schema.
• IsExtending - the generated script contains schema artifacts that extend the existing schema.
• IsComplex - the generated script contains schema artifacts with structural changes.

Other properties exposed by the SchemaUpdateInfo class are:

• Script - a string containing the generated SQL statements.
• HasScript - it is calculated based on the Script property. Returns true, if the generated script contains statements.
• AddsColumnOnExistingTable - returns true if the generated script will add a new column to an existing table.
• AddsFKConstraintOnExistingTable - returns true will add a foreign key constraint to an existing table.
• AddsFKConstraintOnNewTable - returns true if the generated script will if the generated script will add a foreign key constraint to a new table, the create table statement is included as well.
• AddsIndexOnExistingTable - returns true if the generated script will add an index to an existing table.
• AddsIndexOnNewTable - returns true if the generated script will add an index definition for a new table, the create table statement is included as well.
• AddsNewTable - returns true if the generated script will add a new table.
• AddsOther - returns true if the generated script contains artifacts that adds structures independent of existing.
• AddsPKConstraintOnExistingTable - returns true if the generated script will add a primary key constraint to an existing table.
• AddsPKConstraintOnNewTable - returns true if the generated script will add a primary key constraint to an new table, the create table statement is included as well.
• ModifiesColumnOnExistingTable - returns true if the generated script will modify a column definition on an existing table.
• ModifiesOther - returns true if the generated script contains artifacts that modifies existing structures.
• RemovesColumnOnExistingTable - returns true if the generated script will remove a column from an existing table.
• RemovesFKConstraintFromExistingTable - returns true if the generated script will remove a foreign key constraint from an existing table.
• RemovesIndexOnExistingTable - returns true if the generated script will remove an index on an existing table.
• RemovesPKConstraintFromExistingTable - returns true if the generated script will remove a primary key constraint from an existing table.
• UsesTemporaryTable - returns true if the generated script contains artifacts that uses a temporary table to migrate existing data during structural table changes.

SchemaUpdateInfo updateInfo = dbContext.GetSchemaHandler()
    .CreateUpdateInfo( new SchemaUpdateProperties() );

Dim updateInfo As SchemaUpdateInfo = dbContext.GetSchemaHandler() _
    .CreateUpdateInfo(New SchemaUpdateProperties())

The CreateUpdateInfo method accepts a single parameter of type SchemaUpdateProperties. SchemaUpdateProperties controls switches for the database schema migration. It allows you to configure:

• CheckConstraint - controls if constraints are migrated.
• CheckExtraColumns - controls if additional (non-schema described) columns are migrated.
• CheckIndex - controls if indexes are migrated.
• CheckLength - controls if length checks are performed during migration.
• CheckNulls - controls if nullability checks are performed during migration.
• CheckPK - controls if primary key checks are performed during migration.
• CheckScale - controls if column scale checks are performed during migration.
• CheckType - controls if column type checks are performed during migration.
• CheckExtraIndexes - controls if additional (non-schema described) indexes are migrated.

CheckExtraIndexes

The default value for this property is True, i.e. additional indexes on the existing database are migrated. Several migration actions can be distinguished:

• Replace Index - the following assumptions must be true in order to replace the index:
  • The database index name is the same as the index name in the Telerik Data Access model.
  • The column definition is different.
  • No other Telerik Data Access indexe matches the same column definition as the database index.
• Drop Extra Index - this action is only valid if CheckExtraIndexes is set to False. There are situations where it is necessary to drop the index regardless of the configured property. The indexes are not recreated because of possible missing information during schema read:
  • An existing column is changed that causes an existing index (which is not a Telerik Data Access index) to be deleted if it contains this column.
  • For a temp table migration, every existing index is deleted. Only Telerik Data Access indexes are re-created.
• Keep Index - if there is a database index that matches any Telerik Data Access index, regardless of the name, then we keep the database index.
• Create Index - a new database index will be created if the configured Telerik Data Access index is not found in the database.

The following table shows the desired behavior. DB means the index defined on the database. OA means the definition in Telerik Data Access.

Calculating the IsTrivial, IsExtending and IsComplex Properties

These three properties are calculated based on the other bool flags and represent the level of complexity of the generated script.

For example, the IsComplex property is calculated in the following way:

public bool IsComplex
{
   get
   {
       if (((!this.UsesTemporaryTable && !this.RemovesColumnOnExistingTable) &&
           (!this.ModifiesColumnOnExistingTable && !this.RemovesIndexOnExistingTable)) &&
           (!this.RemovesFKConstraintFromExistingTable && !this.RemovesPKConstraintFromExistingTable))
       {
           return this.ModifiesOther;
       }
       return true;
   }
}

Public ReadOnly Property IsComplex() As Boolean
 Get
  If (((Not Me.UsesTemporaryTable) AndAlso (Not Me.RemovesColumnOnExistingTable)) AndAlso
   ((Not Me.ModifiesColumnOnExistingTable) AndAlso (Not Me.RemovesIndexOnExistingTable))) AndAlso
  ((Not Me.RemovesFKConstraintFromExistingTable) AndAlso (Not Me.RemovesPKConstraintFromExistingTable)) Then
   Return Me.ModifiesOther
  End If
  Return True
 End Get
End Property

The IsExtending property is calculated in the following way:

public bool IsExtending
{
   get
   {
       if (this.IsComplex)
       {
           return false;
       }
       if ((!this.AddsColumnOnExistingTable && !this.AddsIndexOnExistingTable) &&
           (!this.AddsFKConstraintOnExistingTable && !this.AddsPKConstraintOnExistingTable))
       {
           return this.AddsOther;
       }
       return true;
   }
}

Public ReadOnly Property IsExtending() As Boolean
 Get
  If Me.IsComplex Then
   Return False
  End If
  If ((Not Me.AddsColumnOnExistingTable) AndAlso (Not Me.AddsIndexOnExistingTable)) AndAlso
   ((Not Me.AddsFKConstraintOnExistingTable) AndAlso (Not Me.AddsPKConstraintOnExistingTable)) Then
   Return Me.AddsOther
  End If
  Return True
 End Get
End Property

The IsTrivial property is calculated in the following way:

public bool IsTrivial
{
   get
   {
       return (!this.IsComplex && !this.IsExtending);
   }
}

Public ReadOnly Property IsTrivial() As Boolean
 Get
  Return ((Not Me.IsComplex) AndAlso (Not Me.IsExtending))
 End Get
End Property

Forcing Schema Update

By default the schema migration is done only if there are no open contexts for particular Database object. If there are open context, there is a risk that the database is unsynchronized with the metadata used by the existing contexts, which could lead to exceptions. If the user explicitly wants to update the database, he can do that using the new "Force" methods exposed by the ISchemaHandler interface:

• ForceExecuteDDLScript - executes a DDL script on a used database instance.
• ForceUpdateSchema - executes DDL statements on a database instance with open contexts.