Show/Hide Toolbars

TMS Aurelius Documentation

Schema validation is a process that gives you the differences between the existing database schema and the needed schema to make the current application to work. You can validate the existing database structure using method ValidateDatabase. The method returns true if there are no differences in that comparison (meaning that the existing database structure has all database objects needed by the application):

 

uses

  Aurelius.Engine.DatabaseManager,

  Aurelius.Schema.Messages;

{...}

var

  DBManager: TDatabaseManager;

  SchemaMessage: TSchemaMessage;

begin

  DBManager := TDatabaseManager.Create(MyConnection);

  if DBManager.ValidateDatabase then

    WriteLn('Database strucuture is valid.')

  else

  begin

    WriteLn(Format('Invalid database structure. %d Errors, %d Warnings, %d Actions',

      [DBManager.ErrorCount, DBManager.WarningCount, DBManager.ActionCount]));

    for SchemaMessage in DBManager.Warnings do

      WriteLn('Warning: ' + SchemaMessage.Text);

    for SchemaMessage in DBManager.Errors do

      WriteLn('Error: ' + SchemaMessage.Text);

    for SchemaMessage in DBManager.Actions do

      WriteLn('Action: ' + SchemaMessage.Text);

  end;

  DBManager.Free;

end;

 

This method will:

 

a) Execute SQL statements to perform a reverse engineering in the database, retrieving the existing database schema (*).

b) Compare the existing schema with the target schema (all database objects - table, columns, etc. - need to persist the mapped entity classes.

c) Provide info about the differences between the two schema (see schema validation for details).

d) Generate the SQL Script needed to update the database schema

 

(*) Note: for Aurelius to properly import database schema, you need to register a schema importer according to the database server you are connecting to. For example, to import MySQL schema, just use the unit "Aurelius.Schema.MySQL" anywhere in your project.

 

If command execution is disabled, this method behaves exactly as the UpdateDatabase method.

 

The comparison result is provided through properties Actions, Warnings and Errors and also ActionCount, WarningCount and ErrorCount, defined as following:

 

  property Actions: TEnumerable<TSchemaAction>;

  property Warnings: TEnumerable<TSchemaWarning>;

  property Errors: TEnumerable<TSchemaError>;

  property ActionCount: integer;

  property WarningCount: integer;

  property ErrorCount: integer;

 

TSchemaAction, TSchemaWarning and TSchemaError classes inherit from TSchemaMessage class, which just has a public Text property with the information about the difference. The concept of each message type (actio, warning, error) is described as follows:

 

Actions

Actions are reported differences between the two schemas which associated SQL update statements can be safely executed by the database manager. Examples of differences that generate actions:

 

A new table

A new nullable column in an existing table

A new sequence

A new non-unique index (DBIndex)

Foreign key removal (if supported by database)

Unique key removal (if supported by database)

 

 

Warnings

Warnings are reported differences between the two schemas which associated SQL update statements can be executed by the database manager, but it might cause runtime errors depending on the existing database data. Examples of differences that generate actions:

 

A new not null column in an existing table (to be safe, when updating existing schema, try to always create new columns as nullable)

A new foreign key (usually you will create a new association, which will generate actions for new foreign key and new columns, which will not cause problem, unless the association is required. It's a warning if supported by database)

 

Errors

Errors are reported differences between the two schemas which associated SQL update statements cannot be executed by the database manager. This means that updating the schema will not make those differences disappear, and you would have to change the schema manually. The fact it is reported as "Error" does not mean the application will not work. It just means that the manager cannot update such differences. Examples of differences that generate errors:

 

Column data type change

Column Null/Not Null constraint change

Column length, precision or scale change

A new foreign key (if database does not support such statement)

Foreign key removal (if database does not support such statement)

Unique key removal (if database does not support such statement)

Changes in primary key (id fields)

Column removal

Table removal

Sequence removal

A new unique key

 

Schema comparison options

 

You can use some properties to define how Aurelius will detect changes in existing schema.

 

Properties

 

Name

Description

property IgnoreConstraintName: Boolean

When False, the validator will compare constraints (foreign key and unique key) by their name. If the name is different, they are considered different keys. This is the default for all databases except SQLite. When True, the validator will analyze the content of the foreign key, regardless the name. For example, if the foreign keys relates the same two tables, using the same fields, it's considered to be the same foreign key. You can set this option to True if you have created your database using a different tool than Aurelius, thus the foreign keys might have different names but you don't want Aurelius to recreated them.