Validate database schema agains RLINQ / domain model

[Tys]

Hi there,

Quick question, is there a way to validate the database schema against the current model in my .NET project?
Practically, we develop .NET apps based on Open Access. Sometimes when updating a customer version of the application, one bumps in to the error that the customer database still doesn't have that new column that was just added the other day. I would like the application to check for this at start up.

Thanks for helping,
Best regards, Tys

[Boris Georgiev]

Hello Tys,

You can validate the database schema with your current model and also you can update the database at runtime so the model and the schema to be identical. 

A) If you are using OpenAccess Domain Model, there are 3 steps that you need to take:
A.1) Set the Update Schema property to true for every domain classes that you have.
A.2) Enable the validation for constraints. 
A.3) Validate or Update the database schema.

B) If you are using code-only mapping (fluent model), there are 2 steps:
B.1) Enable the validation for constraints. 
B.2) Validate or Update the database schema.


Here I will provide you with the instructions how to do that:

A) Using OpenAccess Domain Model:

A.1) You can set the Update Schema property to true in two different ways:
- Select one class in your model then go to properties window(or press F4), find the "Update 
property and set its value to true. You should execute this step for every class that you have in the model, as you have to know that you could select all the classes and execute it at once but you should select only the classes but not the associations.
- You could use Batch Operations Dialog to update all the classes at once. By default the operation for changing the Update Schema property is not visible in the dialog. The reasons are that we would prefer to hide some operations that might harm the model if not used correctly, but still make them available when they are really needed, we are planning to make all operations publicly available in Q2 2013.
 To access the operation before the Q2 2013 is released, you should perform one simple step. Please open the "..\Telerik\OpenAccess ORM\dsl2012\Extensibility\BatchOperationsDialog" folder(if you are using VS 2012, for VS2010 - dsl2010) and create an empty file named "Diagnostics.xml", then restart the Visual Studio. Originally it should be located in C:\Program Files or C:\Program Files(x86) depending on your operating system - x86 or x64 accordingly.
When the file is created, run Bath Operations Dialog, display Entities and choose all of them. Then from the combo box Operation choose Change Update Schema, set the value to true and execute the operation.

Implementing this step will give you the possibility to validate or update the database only for tables and columns.

A.2) If you want to be able to validate or update the constraints in the database, you should implement the next steps:
- extend your model with public partial class. If your class is EntitiesModel, create a new one EntitiesModel.Partial.cs.
- add type constructor with the following code:

1.static EntitiesModel()
2.{
3.    var container = metadataSource.GetModel();
4.    container.DefaultMapping.NullForeignKey = true;
5.}

After this step, you will be able to validate or update the constraints in the database.

A.3) The last one step is to implement the method that will validate or update the database schema according to the OpenAccess model. To implement this step you should get the schema handling instance from the context.

- To update the database schema you should generate a migration DDL script from the schema handling instance and execute the script:

01.private static void EnsureDB(EntitesModel context)
02.{
03.    var schemaHandler = context.GetSchemaHandler();
04.    string script = null;
05.    if (schemaHandler.DatabaseExists())
06.    {
07.        script = schemaHandler.CreateUpdateDDLScript(null);
08.    }
09.    else
10.    {
11.        schemaHandler.CreateDatabase();
12.        script = schemaHandler.CreateDDLScript();
13.    }
14.
15.    if (!string.IsNullOrEmpty(script))
16.    {
17.        schemaHandler.ExecuteDDLScript(script);
18.    }
19.}

- To validate the database schema you should generate a migration DDL script from the schema handling instance and then check the content of the script: 

01.public static bool ValidateDatabase(EntitiesModel context)
02.{
03.    ISchemaHandler schemaHandler = context.GetSchemaHandler();
04.    if (schemaHandler.DatabaseExists())
05.    {
06.        string script = schemaHandler.CreateUpdateDDLScript(null);
07.        return string.IsNullOrEmpty(script);
08.    }
09.
10.    return false;
11.}

Note that you could pass a SchemaUpdateParamaters object to CreateUpdateDDLScript() method so you could determine which elements of the model to be checked. When you create a new OpenAccess model the Foreign Keys are initialized as Indexes too and when CreateUpdateDDLScript() method
is executed for the first time, the script will contain a DDL to create Indexes from the Foreign Keys. To avoid the creation of Indexes in the database you could create a SchemaUpdateParamaters object, set CheckIndexes property to false and pass the object as parameter in the CreateUpdateDDLScript() method:

1.SchemaUpdateProperties schemaProperties = new SchemaUpdateProperties();
2.schemaProperties.CheckIndex = false;
3.
4.string script = schemaHandler.CreateUpdateDDLScript(schemaProperties);

B) Using code-only mapping - fluent model

B.1) To generate a script for the constraints, you should override the CreateModel() method in your FluentMetadataSource class, and the best practice in this case is:
- Add a new class to your project
- Name it for example EntitesModelFluentMetadataSource.partial.cs
- Open it and make sure you have "public partial class" before the name of the class, it will be a partial class for your FluentMetadataSource
- Inside the class, you have to override the default value we are setting to the NullForeignKey property, which is preventing the creation of constraints. In order to do that, paste the following code:

1.protected override Telerik.OpenAccess.Metadata.MetadataContainer CreateModel()
2.{
3.    MetadataContainer container = base.CreateModel();
4.    container.DefaultMapping.NullForeignKey = true;
5.    return container;
6.}

B.2) This step is the same as A.3)

I hope that helps. Please let me know if you need additional assistance or in case you have other questions.

Kind regards,
Boris Georgiev
the Telerik team