logo

Database conversion using the structure´s XML file

This article shows an example of database conversion with an XML file that passes object values from one column (integer data type) to another column (string data type).

Important
Before implementing this extension point, back up your database. In addition, we strongly recommend implementing first on test configurations of the system; otherwise, you will be running a risk of damaging the database!

DbStructureExtension base class methods

/// <summary>
/// Database provider Uid
/// </summary>
public abstract Guid ProviderUid
 
/// <summary>
/// Structure
/// </summary>
public virtual DbStructure Structure
 
/// <summary>
/// Version (be default, version of the assembly with the current class)
/// </summary>
public virtual Version Version
 
/// <summary>
/// Parent data mapper (for providers of certain DB)
/// </summary>
public virtual Type Parent 
 
/// <summary>
/// Links to DB converters, on which this exact data mapper depends
/// </summary>
public virtual Type[] References 
 
/// <summary>
/// Assembly that contains the resource with the structure (by default, assembly of the current class)
/// </summary>
protected virtual Assembly Assembly
 
/// <summary>
/// Name of the file containing the structure in the assembly resources (by default it is the same as the name of the current class, with.xml added)
/// </summary>
protected virtual string ResourceFileName

Example of implementing the DbStructureExtension class

[Component]
internal class MyModuleDbStructureCs : DbStructureExtension
{
  public override Guid ProviderUid
  {
    get { return Guid.Empty; } //If necessary, for example, for a Firebird database, you can use FirebirdProvider.UID
  }
     
  public void MoveColumn()
  {
    const string tableName = "CALLS";
    const string newColumn = "NUMBEROFCALLS";
    string oldColumn = Locator.GetServiceNotNull<DbModelUpdater>().GetDeletingColumnTemporaryName(tableName, newColumn); //Old column 
//is renamed before deleting, so it is necessary to get the new name  
// rewrite the data to the new column, then it will be deleted 
 
    var transformationProvider = Locator.GetServiceNotNull<ITransformationProvider>();
    if (transformationProvider.ColumnExists(tableName, newColumn) &&
      transformationProvider.ColumnExists(tableName, oldColumn))
    {
      transformationProvider.ExecuteNonQuery(string.Format(
        "UPDATE {0} SET {1} = CAST({2} as varchar(255))",
        transformationProvider.Dialect.QuoteIfNeeded(tableName),
        transformationProvider.Dialect.QuoteIfNeeded(newColumn),
        transformationProvider.Dialect.QuoteIfNeeded(oldColumn)));
    }
  }
}

In this example, the object CALLS has the NUMBEROFCALLS property of Integer data type; after instances were added to the object, it was required to the old property with the NUMBEROFCALLS property of the String data type. For that, the old property is deleted from the object and a new one is created with the same display name, property name and database field name but with the String data type. During database conversion, the old column is renamed and then deleted. The code written above is executed between these two events.    

For database conversion, you need an .xml, file with the same name as the one of the class inherited from DbStructureExtension (it is so by default). The extension point code and the xml file must be located in the same place. Also, the xml file must have Build Action – Embedded Resource:

Fig. 1. Location of MyModuleDbStructureCs.cs and MyModuleDbStructureCs.xml

Fig. 1. Location of MyModuleDbStructureCs.cs and MyModuleDbStructureCs.xml

In this example, the name of the xml file is MyModuleDbStructureCs.xml. Example of code:

<?xml version="1.0" encoding="utf-8" ?>
<root uid="{3B204C3F-B611-4fc3-A859-AAD3F355CE71}">
  <methods>
    <method name="MoveColumn" ExecuteTime="OnTablesDeleting"/>
  </methods>
</root>

When the server is restarted after the module’s activation, the system will look for an .xml file with the name of the class inherited from DbStructureExtension. Then, the names of the methods required to convert the database will be found in the xml file according to the <method name="" /> tags.  As you can see, the class code is using the CreateDefaultFilters method, so it is tagged correspondingly in the xml file. It is required to place the database conversion class to the same place as the xml file.  

The <method/> tags have additional parameters, for example: <method name="DataSecondPassConverting" ExecuteTime="OnTablesDeleted" AlwaysExecute="true" />

Parameters:

Parameter

Parameter value

Description

ExecuteTime

OnBeforeStart

Before starting conversion (is done in a separate transaction)

OnStart

Conversion start

OnTriggersDeleted

After deleting triggers

OnProceduresDeleted

After deleting procedures

OnViewsDeleted

After deleting views

OnIndexesDeleted

After deleting indexes

OnForeignKeysDeleted

After deleting foreign keys

OnPrimaryKeysDeleted

After deleting primary keys

OnTablesCreating

Before creating tables and columns

OnTablesCreated

After creating tables and columns

OnTablesDeleting

Before deleting unwanted tables and columns

OnTablesDeleted

After deleting unwanted tables and columns

OnForeignKeysCreated

After creating foreign keys

OnPrimaryKeysCreated

After creating primary keys

OnIndexesCreated

After creating indexes

OnViewsCreated

After creating views

OnProceduresCreated

After creating procedures

OnTriggersCreated

After creating triggers

OnComplete

After conversion is completed

AlwaysExecute

True/false

Execute every time upon start

OnDeactivate

True/false

Execute upon module deactivation

Also, in this xml you can use SQL queries. For that, use the corresponding tag:

<script name="fixTask">
      <text>
        UPDATE TaskBase SET Executor = 2 WHERE id = 1
      </text>
</script>

Additional parameters used in the <method/> tag can also be applied to the <script> tag. 

Links to API elements

DbStructureExtension

DbStructureExtension