Database Connectivity

This chapter explains how you properly configure Aurelius to access the database where objects will be saved to.

To connect to a database using Aurelius, you can use:

• Adapter Mode: In this mode you will use an existing 3rd party database-access component, like FireDAC, dbExpress, ADO, etc.

• Native Driver Mode: In this mode TMS Aurelius will connect to the database directly.

The database connection is represented by the IDBConnection Interface.

TAureliusConnection component is the easiest and most straightforward way to configure a connection and retrieve the IDBConnection interface. It supports both adapter and driver mode and has design-time wizards to help you out. With the TAureliusConnection component you can also generate entities from existing database.

Alternatively, you can always create the IDBConnection interface directly from code using the component adapters or native database drivers.

You can also have the option to use the Connection Wizard to automatically create the TAureliusConnection component in a new TDataModule, including the adapted connection component if you're going to use one (FireDac, for example).

See the following topics for detailed information about database connectivity in TMS Aurelius.

Using the Connection Wizard

To connect to a database, you need an IDBConnection interface representing the database connection. The easiest way to get one is using the "TMS Aurelius Connection" wizard which is available in Delphi IDE after you installed Aurelius.

To create a new connection:

1. Choose File > New > Other and then look for "TMS Business" category under "Delphi Projects". Then double click "TMS Aurelius Connection".

2. Choose between Adapter Mode or Driver Mode.
   For Adapter Mode, select the Adapter (component to access database) and the SQL Dialect (type of database server).
   For Driver Mode, select Driver to use.

3. A new data module will be created with a TAureliusConnection component already preconfigured. If you used the adapter mode, the adapted component will also be created. Either configure the connection settings in the adapted connection (adapter mode) or directly in TAureliusConnection (for driver mode).

4. To retrieve a new IDBConnection interface from the data module, just use this code:

// The name of data module class might vary from TFireDacMSSQLConnection
// depending on selected driver and SQL Dialect
NewConnection := TFireDacMSSQLConnection.CreateConnection;

Remarks

The wizard shows the following options:

For Adapter mode

• Adapter: Choose the database component you want to use to connect to the database. You can choose any that is supported by Aurelius component adapters, like FireDac, dbExpress, dbGo (ADO), among others.

• SQL Dialect: Choose the SQL dialect to be used when executing SQL statements to the database. Some drivers support several dialects (like FireDac for example), and some support just one (for example, SQLite driver only supports SQLite dialect).

For Driver mode

• Driver: Choose the native database driver you want to use to connect to database, for example "SQLite" or "MSSQL".

You can freely configure and try the connection at design-time the usual way you do with your component, that's the purpose of it - to be RAD and working at design-time. It's always a good practice to close the connection once you have tested and configured it, though.

The name of the data module is automatically defined by the wizard and it's a combination of the driver and sql dialect you selected. In the example above, it was FireDac driver and MSSQL dialect, but could be different. You can always change this name later.

It's important to note that no instance of the data module will be auto-created. Also, the CreateConnection method always create a new instance of the data module, so if you intend to use a single global connection for the application (which is usual for client/server applications), call CreateConnection just once and save the created IDBConnection interface for further use.

IDBConnection Interface

The IDBConnection interface is the lowest-level representation of a connection to a database in Aurelius. Every object that needs to connect to a database just uses this interface to send and receive data from/to the database. As an example, when you create a TObjectManager object, you need to pass a IDBConnection interface to it so it can connect to the database.

IDBConnection wraps a component adapter or a native driver - the two ways available to connect to a database - making it transparent for the framework. Thus, regardless if you connect to the database using FireDac, dbExpress, ADO, IBX, etc., or directly using native drivers, in the end all you need IDBConnection.

To obtain an IDBConnection interface you instantiate a class of an adapter or a driver. The adapters just take an existing data access component (TFDConnection, TSQLConnection, TADOConnection, etc.) and give you back the IDBConnection interface you need to use. The native driver takes connection parameters to know how to connect to the database. To create database connections it's important to know the available adapters and drivers:

• Native Database Drivers
• Component Adapters
• SQL Dialects

In summary:

To obtain an IDBConnection interface using a native driver

Instantiate the connection class for the database you want to connect and pass the parameters in the Create method. For example, to connect to SQL Server:

uses Aurelius.Drivers.MSSQL;
{...}
var
  MyConnection: IDBConnection;
begin
  MyConnection := TMSSQLConnection.Create(
    'Server=.\SQLEXPRESS;Database=Northwnd;TrustedConnection=True');
  // Use your connection now
  Manager := TObjectManager.Create(MyConnection);
  {...}
end;

For more information about the available drivers, the class names and valid parameters, see Native Database Drivers.

To obtain an IDBConnection interface using an adapter

1. Create and configure (or even use an existing one) component that makes a connection to your database.

If you use FireDAC, for example, just drop a TFDConnection component on the form and configure it. Or you can just use the existing one you have in your application. Suppose this component is named FDConnection1.

FDConnection1: TFDConnection;

2. Instantiate an adapter passing the connection component.

uses Aurelius.Drivers.FireDac;
{...}
var
  MyConnection: IDBConnection;
begin
  MyConnection := TFireDacConnectionAdapter.Create(FDConnection1, False);
  // Use your connection now
  Manager := TObjectManager.Create(MyConnection);
  {...}
end;

For more information about how to create adapters, see Component Adapters.

To obtain an IDBConnection interface from a TAureliusConnection component

Once you have configured your TAureliusConnection component (which also provide an adapter mode or native driver mode), just create a new IDBConnection interface by using the CreateConnection method:

var
  MyConnection: IDBConnection;
begin
  MyConnection := AureliusConnection1.CreateConnection;
end;

TAureliusConnection Component

TAureliusConnection component is a RAD and easy way to configure the connection to your database, at both design-time and runtime. In the end, the main purpose of this component is also to provide the IDBConnection interface that is used by the whole framework, using the CreateConnection method:

var MyConnection: IDBConnection;
...
  MyConnection := AureliusConnection1.CreateConnection;

Configuring the connection using Connection Editor

Easiest way to configure TAureliusConnection component is double clicking the component at design-time, to open the connection editor. You can then choose if it will connect to the database with an existing database connection - through a component adapter - or directly using native database driver.

To use an adapter, click "Use an existent data-access component (Adapter Mode)":

For that mode, choose an existing data-access component in the "Adapted Connection" combo. The dialog will list all the supported components. The component to be adapted must be placed in the same form or data module as TAureliusConnection. Components in other forms or data modules won't be displayed.

One adapted connection is chosen, "Adapter Name" and "SQL Dialect" will often be selected automatically. If they don't, just explicitly set the adapter name and the SQL dialect to be used.

To use a native database driver, click "Use native driver support (Driver Mode)":

Then choose the native "Driver Name". Once it's selected, the valid parameters for the driver will be displayed. Fill in the parameters accordingly. Refer to "Native Database Drivers" topic for the full list of the drivers with their respective driver names and parameters.

You can always use the "Test Connection" button to check if your settings are valid.

Configuring the connection using properties

You can configure the connection directly by setting properties, either at runtime from code, or at design-time using the object inspector.

To connect using a component adapter (adapter mode), set properties AdaptedConnection, AdapterName and SQLDialect. For example:

AureliusConnection1.AdaptedConnection := FDConnection1;
AureliusConnection1.AdapterName := 'FireDac';
AureliusConnection1.SQLDialect := 'PostgreSQL';

To connection using a native database driver (driver mode), set properties DriverName and use Params to set the parameters:

AureliusConnection1.DriverName := 'MSSQL';
AureliusConnection1.Params.Values['Server'] := '.\SQLEXPRESS';
AureliusConnection1.Params.Values['Database'] := 'NORTHWND';
AureliusConnection1.Params.Values['TrustedConnection'] := 'True';

Using the connection

To use TAureliusConnection, use CreateConnection method to create a new IDBConnection interface and use it:

var 
  MyConnection: IDBConnection;
  Manager: TObjectManager;
begin
  MyConnection := AureliusConnection1.CreateConnection;
  Manager := TObjectManager.Create(MyConnection);
end;

Each call to CreateConnection will create a new IDBConnection interface. If you are using a component adapter, it will also clone the existing adapted connection. To achieve that, TAureliusConnection will clone the owner of the adapted connection. For example, if you are adapting the FireDac TFDConnection component, and that component is placed in a data module named TMyDataModule, each type CreateConnection is called it will create a new instance of TMyDataModule, and then adapt the TFDConnection component in it. When the IDBConnection interface is not referenced anymore and is destroyed, the instance of TMyDataModule will also be destroyed.

Generate Entities From Existing Database

TMS Aurelius is an ORM framework which means you need to declare entity classes and map them to the database. If you have an existing database, you have the option to generate those classes automatically from the existing database.

First way this can be achieved is using the great TMS Data Modeler tool. It's a database modeling tool which can import existing database structure to the model, and then generate Delphi source code with TMS Aurelius classes. It's very powerful, with a scripting system to customize the source code output, ability to separate classes by units, among other things.

But if you don't want to use a separate tool, and not even leave Delphi IDE, you can quickly generate entity classes using TAureliusConnection component. Simply configure the database connection on it, then right-click the component and choose "Generate entities from database...".

This will connect to the database, import the existing database structure, and open the export dialog with several options to customize the output source code. You can then select tables to export, choose naming policy for classes and properties, among other options. You can even preview the final source code in the "Preview" tab, before confirming. When you click "Ok" button, a new unit with the declares entities will be created in the same project of TAureliusConnection component.

In "Mapping" tab you can choose the tables to export.

In "Advanced Settings" tab you can use the following options:

Naming options

You can define the default rule for naming classes, property/fields, associations and many-valued associations.

Class name comes from table name, property name comes from database field name. Those are the "base names". For associations you have "Use name from" field which specifies what will be used for the "base name". From the base name, the Format Mask will be applied. The "%s" in the format mask will be replaced by the base name. For example, the defualt Format Mask for class naming is "T%s" which means the class name will be the base name (usually Table Caption) prefixed with "T".

Additionally, some naming options allow you to:

• Camel Case: The first character of the base name or any character followed by underling will become upper case, all the other will become lower case. For example, if the base name in model is "SOME_NAME", it will become "Some_Name".

• Remove underline: All underlines will be removed. "SOME_NAME" becomes "SOMENAME". If combined with camel case, it will become "SomeName".

• Singularize: If the base name is in plural, it will become singular. "Customers" become "Customer", "Orders" become "Order". It also applies specified singularization rules for English language (e.g., "People" becomes "Person", etc.).

Dictionary

Data Modeler can also generate a dictionary with metadata for the classes. This dictionary can be used in queries in TMS Aurelius. To generate check "Generate Dictionary". You can also specify:

• Global Var Name: Defines the name of Delphi global variable to be used to access the dictionary.

Defaults

Defines some default behaviors when translating tables/fields into classes/properties. You can override this default behaviors individually for each class/property in the "Mappings" tab.

Field	Description
Association Fetch Mode	The default fetch mode used for associations. Default value is Lazy.
Association Cascade Type	The default cascade definition for associations. Options are "None" (no cascade) and "All but Remove" (all cascade options like save, update, merge, except remove cascade). Default value is None.
Many-Valued Association Fetch Mode	The default fetch mode used for many-valued associations. Default is Lazy.
Map One-to-One Relationship As	Defines how 1:1 relationships will be converted by default. A 1:1 relationship can be converted as a regular association (property) or can be considered an inheritance between two classes. Default value is Association.
Ancestor Class	Specifies the name of the class to be used as base class for all entity classes generated. Default value is empty, which means no ancestor (all classes will descend from TObject).
Dynamic Props Container Name	Specifies the default name for the property that will be a container for dynamic properties. If empty, then by default no property will be created in the class.
Check for Missing Sequences	Defines if exporting must abort (raise an error) if a sequence is not defined for a class. Options are: - If supported by database: if database supports sequences/generators, then raise an error if a sequence is not defined (default); - Always: always raise an error if a sequence is not specified; - Never: ignore any sequence check.

Options

Defines some other general options for exporting.

Field	Description
Generate Dictionary	Defines if the dictionary will be generated.
Register Entities	When checked, the generated unit will have an initialization section with a call to RegisterEntity for each class declared in the script (e.g., RegisterEntity(TSomeClass);). This will make sure that when using the generated unit, classes will not be removed from the final executable because they were not being used in the application. This option is useful when using the entity classes from a TMS XData server, for example.
Don't use Nullable<T>	By default, non-required columns will be generated as properties of type Nullable<T>. Check this option if you don't want to use Nullable, but instead use the primitive type directly (string, integer, etc.).

Component Adapters

There is an adapter for each data-access component. For dbExpress, for example, you have TDBExpressConnectionAdapter, which is declared in unit Aurelius.Drivers.dbExpress. All adapters are declared in unit Aurelius.Drivers.XXX where XXX is the name of data-access technology you're using. You can create your own adapter by implementing IDBConnection interfaces, but Aurelius already has the following adapters available:

Technology	Adapter class	Declared in unit	Adapted Component	Vendor Site
Absolute Database	TAbsoluteDBConnectionAdapter	Aurelius.Drivers.AbsoluteDB	TABSDatabase	https://www.componentace.com
AnyDac	TAnyDacConnectionAdapter	Aurelius.Drivers.AnyDac	TADConnection	https://www.da-soft.com/anydac
dbExpress	TDBExpressConnectionAdapter	Aurelius.Drivers.dbExpress	TSQLConnection	Delphi Native
dbGo (ADO)	TDbGoConnectionAdapter	Aurelius.Drivers.dbGo	TADOConnection	Delphi Native
Direct Oracle Access (DOA)	TDoaConnectionAdapter	Aurelius.Drivers.Doa	TOracleSession	https://www.allroundautomations.com
ElevateDB	TElevateDBConnectionAdapter	Aurelius.Drivers.ElevateDB	TEDBDatabase	https://elevatesoftware.com/
FIBPlus	TFIBPlusConnectionAdapter	Aurelius.Drivers.FIBPlus	TFIBDatabase	https://github.com/madorin/fibplus
FireDac	TFireDacConnectionAdapter	Aurelius.Drivers.FireDac	TFDConnection	Delphi native
IBObjects (IBO)	TIBObjectsConnectionAdapter	Aurelius.Drivers.IBObjects	TIBODatabase	https://www.ibobjects.com/
Interbase Express (IBX)	TIBExpressConnectionAdapter	Aurelius.Drivers.IBExpress	TIBDatabase	Delphi Native
NativeDB	TNativeDBConnectionAdapter	Aurelius.Drivers.NativeDB	TASASession	https://www.nativedb.com
NexusDB	TNexusDBConnectionAdapter	Aurelius.Drivers.NexusDB	TnxDatabase	https://www.nexusdb.com
SQL-Direct	TSQLDirectConnectionAdapter	Aurelius.Drivers.SqlDirect	TSDDatabase	https://www.sqldirect-soft.com
SQLite	TSQLiteNativeConnectionAdapter	Aurelius.Drivers.SQLite	(not applicable)	TMS Aurelius Native
UniDac	TUniDacConnectionAdapter	Aurelius.Drivers.UniDac	TUniConnection	https://www.devart.com/unidac
Unified Interbase (UIB)	TUIBConnectionAdapter	Aurelius.Drivers.UIB	TUIBDatabase	https://sourceforge.net/projects/uib/
TMS RemoteDB Server	TRemoteDBConnectionAdapter	Aurelius.Drivers.RemoteDB	TRemoteDBDatabase	https://www.tmssoftware.com/site/remotedb.asp
ZeosLib	TZeosLibConnectionAdapter	Aurelius.Drivers.ZeosLib	TZConnection	https://sourceforge.net/projects/zeoslib

Creating the adapter

To create the adapter, you just need to instantiate it, passing an instance of the component to be adapted. In the example below, a FireDAC adapter constructor receives a TFDConnection component.

MyConnection := TFireDacConnectionAdapter.Create(FDConnection1, False);

The adapter usually detects the SQL Dialect automatically, but you can force the adapter to use a specific dialect, using one of the following overloaded constructors.

Overloaded constructors

There are some overloaded versions of the constructor for all adapters:

constructor Create(AConnection: T; AOwnsConnection: boolean); overload; virtual;
constructor Create(AConnection: T; ASQLDialect: string;
  AOwnsConnection: boolean); overload; virtual;
constructor Create(AConnection: T; OwnedComponent: TComponent); overload; virtual;
constructor Create(AConnection: T; ASQLDialect: string;
  OwnedComponent: TComponent); overload; virtual;

• AConnection: specify the database-access component to be adapted.

• AOwnsConnection: if true, the component specified in AConnection parameter will be destroyed when the IDBConnection interface is released. If false, the component will stay in memory.

• ASQLDialect: defines the SQL dialect to use when using this connection. If not specified, Aurelius will try to discover the SQL Dialect based on the settings in the component being adapted.

• OwnedComponent: specifies the component to be destroyed when the IDBConnection interface is released. This is useful when using data modules (see below).

Memory Management

Note the second boolean parameter in the Create constructor of the adapter. It indicates if the underlying connection component will be destroyed when the IDBConnection interface is destroyed. In the example above ("Creating the adapter"), the FDConnection1 component will remain in memory, even after MyConnection interface is out of scope and released. If you want the component to be destroyed, just pass the second parameter as true. You will usually use this option when you create a connection component just for Aurelius usage. If you are using an existing component from your application, use false. Quick examples below:

var
  MyConnection: IDBConnection;
begin
  MyConnection := TDBExpressConnectionAdapter.Create(SQLConnection1, False);
  // ...
  MyConnection := nil;
  { MyConection is nil, the TDBExpressConnectionAdapter component is destroyed, 
    but SQLconnection1 component remains in memory}    
end;


var
  MyConnection: IDBConnection;
  SQLConnection1: TSQLConnection;
begin
  SQLConnection1 := TSQLConnection.Create(nil);
  // Set SQLConnection1 properties here in code
  MyConnection := TDBExpressConnectionAdapter.Create(SQLConnection1, True);
  // ...
  MyConnection := nil;
  { MyConection is nil, the TDBExpressConnectionAdapter component is destroyed, 
    and SQLConnection1 is also destroyed }
end;

Alternatively, you can inform a component to be destroyed when the interface is released. This is useful when you want to create an instance of a TDataModule (or TForm) and use an adapted component that is owned by it. For example:

MyDataModule := TConnectionDataModule.Create(nil);
MyConnection := TDBExpressConnectionAdapter.Create(MyDataModule.SQLConnection1, MyDataModule);

The previous code will create a new instance of data module TConnectionDataModule, then create a IDBConnection by adapting the SQLConnection1 component that is in the data module. When MyConnection is released, the data module (MyDataModule) will be destroyed. This is useful if you want to setup the connection settings at design-time, but want to reuse many instances of the data module in different connections (for multi-threading purposes, for example).

Referencing original component

If the component adapter also implements the IDBConnectionAdapter interface, you can retrieve the original adapted component. For example, given an IDBConnection that you know was created from a TFireDacConnectionAdapter, you can retrieve the TFDConnection object using the following code:

var
  MyConnection: IDBConnection;
  FDConnection: TFDConnection;
{...}
  FDConnection := (MyConnection as IDBConnectionAdapter).AdaptedConnection as TFDConnection;

Native SQLite Adapter

Aurelius provides native SQLite database adapter. You just need to have sqlite3.dll in a path Windows/Mac can find. Creating SQLite adapter is a little different than other adapters, since you don't need to pass a component to be adapter. With the SQLite adapter, you just pass the name of the database file to be open (or created if it doesn't exist):

MySQLiteConnection := TSQLiteNativeConnectionAdapter.Create(
  'C:\Database\SQLite\MyDatabase.sdb');

TSQLiteNativeConnectionAdapter class also has two additional methods that you can use to manually disable or enable foreign keys in SQLite (foreign keys are enforced at connection level, not database level in SQLite!).

procedure EnableForeignKeys;
procedure DisableForeignKeys;

So if you want to use SQLite with foreign keys, do this to retrieve your connection:

var 
  SQLiteAdapter: TSQLiteNativeConnectionAdapter;
  MySQLiteConnection: IDBConnection;
begin
  SQLiteAdapter := TSQLiteNativeConnectionAdapter.Create('C:\Database\SQLite\MyDatabase.sdb');
  SQLiteAdapter.EnableForeignKeys;
  MySQLiteConnection := SQLiteAdapter;
  // Use MySQLiteConnection interface from now on

dbGo (ADO) Adapter

Currently dbGo (ADO) is only officially supported when connecting to Microsoft SQL Server databases. Drivers for other databases might work but were not tested.

Native Database Drivers

Aurelius provides native database connectivity. That means for some databases, you don't need to use a 3rd-party component adapter to access the database, but instead access it directly through the database client libraries.

The table below shows the existing native drivers and the connection classes.

Database	Driver Name	Connection class	Declared in unit
Microsoft SQL Server	MSSQL	TMSSQLConnection	Aurelius.Drivers.MSSQL
SQLite	SQLite	TSQLiteConnection	Aurelius.Drivers.SQLite

Creating a connection

To use the native driver from code, you usually just create an instance of the specific connection class passing to it a connection string that specifies how to connect to the database. The connection class implements the IDBConnection interface which you can then use. For example:

Conn := TMSSQLConnection.Create(
  'Server=.\SQLEXPRESS;Database=Northwnd;TrustedConnection=True');
Manager := TObjectManager.Create(Conn);

The connection string is a sequence of ParamName=ParamValue separated by semicolons (Param1=Value1;Param2=Value2). The param names are specific to each database driver as following.

SQLite Driver

Driver name is "SQLite", and the following parameters are supported:

Parameter	Type	Value	Example values
Database	String	A path to an SQLite database file to be open. Must be a valid SQLite file name, or even ":memory:" for in-memory databases.	C:\sqlite\mydb.sqlite :memory:
EnableForeignKeys	Boolean	Enables enforcement of foreign key constraints (using PRAGMA foreign_keys). Default is false.	True / False

Example:

Conn := TSQLiteConnection.Create('Database=C:\sqlite\mydb.sqlite;EnableForeignKeys=True');

MSSQL Driver (Microsoft SQL Server)

Driver name is "MSSQL", and the following parameters are supported:

Parameter	Type	Value	Example values
Server	String	The name of a SQL Server instance. The value must be either the name of a server on the network, an IP address, or the name of a SQL Server Configuration Manager alias.	localhost .\SQLEXPRESS localhost,1522
Database	String	Name of the default SQL Server database for the connection.	northwnd
UserName	String	A valid SQL Server login account.	sa
Password	String	The password for the SQL Server login account specified in the UID parameter.	mypassword
TrustedConnection	Boolean	When "true", driver will use Windows Authentication Mode for login validation. Otherwise instructs the driver to use a SQL Server username and password for login validation, and the UserName and Password parameters must be specified. Default is False.	True/False
MARS	Boolean	Enables or disables multiple active result sets (MARS) on the connection. Default is False.	True/False
OdbcAdvanced	String	Semicolon-separated param=value pairs that will be added to the raw connection string to be passed to the SQL Server client.	StatsLog_On=yes;StatsLogFile=C:\temp\mssqlclient.log
LoginTimeout	Integer	Number of seconds to wait for a login request to complete before returning to the application.	10
Driver	String	Specifies the SQL Server driver name (native or ODBC) to be used to connect to the SQL Server. Default is empty, which forces Aurelius to automatically select the most recent driver installed. You should mostly leave this empty, unless you have a reason to use a specific driver.	ODBC Driver 13 for SQL Server

Example:

Conn := TMSSQLConnection.Create(
  'Server=.\SQLEXPRESS;Database=Northwnd;TrustedConnection=True');

SQL Dialects

To save and manipulate objects in the database, TMS Aurelius internally build and execute SQL statements. The SQL statements are automatically adjusted to use the correct dialect, according to the database server being used by the programmer.

When you create an IDBConnection interface using a component adapter, usually the adapter will automatically specify to Aurelius the SQL dialect to use. For example, if you are using FireDac components, the adapter will look to the DriverID property and tell which db server you are using, and then define the correct SQL dialect name that should be used.

However, the SQL dialect must be explicitly registered in the global settings for Aurelius. This is by design so you don't need to load units for SQL dialects you won't use. To register an SQL dialect, just use a unit named Aurelius.SQL.XXX where XXX is the name of the SQL dialect. The following table lists all current SQL dialects supported, the exact string identifier, and the unit you must add to your project in order for the dialect to be registered.

SQL dialect	String identifier	Declared in unit	Database Web Site
Absolute Database	AbsoluteDB	Aurelius.Sql.AbsoluteDB	http://www.componentace.com
DB2	DB2	Aurelius.Sql.DB2	http://www.ibm.com
ElevateDB	ElevateDB	Aurelius.Sql.ElevateDB	http://www.elevatesoftware.com
Firebird	Firebird	Aurelius.Sql.Firebird	http://www.firebirdsql.org
Firebird3 (*)	Firebird3	Aurelius.Sql.Firebird3	http://www.firebirdsql.org
Interbase	Interbase	Aurelius.Sql.Interbase	http://www.embarcadero.com
Microsoft SQL Server	MSSQL	Aurelius.Sql.MSSQL	http://www.microsoft.com/sqlserver
MySQL	MySQL	Aurelius.Sql.MySql	http://www.mysql.com
NexusDB	NexusDB	Aurelius.Sql.NexusDB	http://www.nexusdb.com
Oracle	Oracle	Aurelius.Sql.Oracle	http://www.oracle.com
PostgreSQL	PostgreSQL	Aurelius.Sql.PostgreSQL	http://www.postgresql.org
SQL Anywhere	SqlAnywhere	Aurelius.Sql.SqlAnywhere	https://www.sap.com/products/sql-anywhere.html
SQLite	SQLite	Aurelius.Sql.SQLite	http://www.sqlite.org

Note that in some situations, the adapter is not able to identify the correct dialect. It can happen, for example, when you are using ODBC or just another data access component in which is not possible to tell which database server the component is trying to access. In this case, when creating the adapter, you can use an overloaded constructor that allows you to specify the SQL dialect to use:

MyConnection := TDBExpressConnectionAdapter.Create(SQLConnection1, 'MSSQL', False);

When using a native database driver, the SQL dialect is implicit from the driver you use and there is no need to specify it. The native driver already uses the sql dialects and schema importer units automatically.

(*) The difference between Firebird and Firebird3 is that the latter uses boolean fields and identity fields by default. Please check Configuring SQL Dialects for more details on how to configure specific SQL dialects.

Configuring SQL Dialects

Some SQL Dialects have configuration options that you can use to fine tune how they work. For that you need to retrieve the original SQL Dialect object and then change specific properties. This is the pattern you use to retrieve a generator:

uses
  Aurelius.Sql.Register, Aurelius.Sql.Firebird;

var
  Generator: TFirebirdSQLGenerator;
begin
  Generator := (TSQLGeneratorRegister.GetInstance.GetGenerator('Firebird')
    as TFirebirdSQLGenerator);
  // Set Generator properties
end;

For all dialects you have the following options:

Properties

Name	Description
EnforceAliasMaxLength: Boolean	Makes sure that the field aliases used by Aurelius in SQL SELECT statements are not longer than the maximum size for field names. When this property is false, the field alias could be longer than maximum allowed for database and would cause errors in some databases, mainly Firebird. This property is there to avoid backward compatibility break, but usually you should always set it to true.
UseBoolean: Boolean	Specifies how boolean values will be represented in database. If False (default), boolean fields will be represented by CHAR(1) type. If True, boolean fields will be represented by BIT/TINYINT type.

For other dialects, you can just replace "Firebird" occurrences by the name of the different dialect. The following sections show the dialects that have specific properties you can configure:

MSSQL (Microsoft SQL Server)

Sample:

uses Aurelius.Sql.Register, Aurelius.Sql.MSSQL;
{...}
(TSQLGeneratorRegister.GetInstance.GetGenerator('MSSQL')
  as TMSSQLSQLGenerator).UseBoolean := True;

Properties

Name

Description

WorkaroundInsertTriggers: Boolean

Specifies if Aurelius should add statement to retrieve Identity values. Basically it would SET NOCOUNT ON and use a temporary table to retrieve the value. More technical info here: https://stackoverflow.com/a/42393871. This property is true by default to make sure things will work in most situations. But setting it to false might increase performance or work better when identity values are greater than 32 bits. In this case you could set it to false.

Firebird3 (Firebird 3.x)

Sample:

uses Aurelius.Sql.Register, Aurelius.Sql.Firebird3;
{...}
(TSQLGeneratorRegister.GetInstance.GetGenerator('Firebird3')
  as TFirebird3SQLGenerator).UseBoolean := False;
(TSQLGeneratorRegister.GetInstance.GetGenerator('Firebird3')
  as TFirebird3SQLGenerator).UseIdentity := False;

The code above makes the Firebird3 dialect to behave like the regular Firebird dialect (which is targeted at Firebird 2.x).

Properties

Name	Description
UseBoolean: Boolean	Specifies how boolean values will be represented in database. If False, then booleans will be represented by CHAR(1) type. If True, booleans will be represented by BOOLEAN type. Default is True.
UseIdentity: Boolean	Specifies how ID generators of type SequenceOrIdentity will behave. If False, then Sequences will be used. If True, Identity fields will be used. Default is True.

Schema Importers

To be able to update and validate database schema, Aurelius needs to perform reverse engineering in the database. This is accomplished by using schema importers that execute specific SQL statements to retrieve the database schema, depending on the database server being used. To find the correct importer, Aurelius searches for a list of registered schema importers, using the same SQL Dialect used by the current connection. So, for example, if the current SQL Dialect is "MySQL", Aurelius will try to find a schema importer named "MySQL".

By default, no schema importers are registered. You must be explicity register a schema importer in the global settings for Aurelius. This is by design so you don't need to load units for schema importers you won't use. To register an schema importer, just use a unit named Aurelius.Schema.XXX where XXX is the name of the SQL dialect associated with the schema importer. The following table lists all current schema importers supported, the exact string identifier, and the unit you must add to your project in order for the dialect to be registered.

Schema Importer for	String identifier (associated SQL Dialect)	Declared in unit
Absolute Database	AbsoluteDB	Aurelius.Schema.AbsoluteDB
DB2	DB2	Aurelius.Schema.DB2
ElevateDB	ElevateDB	Aurelius.Schema.ElevateDB
Firebird	Firebird	Aurelius.Schema.Firebird
Interbase	Interbase	Aurelius.Schema.Interbase
Microsoft SQL Server	MSSQL	Aurelius.Schema.MSSQL
MySQL	MySQL	Aurelius.Schema.MySql
NexusDB	NexusDB	Aurelius.Schema.NexusDB
Oracle	Oracle	Aurelius.Schema.Oracle
PostgreSQL	PostgreSQL	Aurelius.Schema.PostgreSQL
SQL Anywhere	SqlAnywhere	Aurelius.Schema.SqlAnywhere
SQLite	SQLite	Aurelius.Schema.SQLite

Note

When using a native database driver, the schema importer is implicit from the driver you use. The native driver already uses the sql dialects and schema importer units automatically.

Components and Databases Homologation

The following table presents which data-access component can be used to access each relational database server. Note that some components can access more databases than what's described here (especially dbGo (ADO) which can access several databases through OleDB drivers). However, the table below shows what has been tested and is officially supported by TMS Aurelius.

	Native	Absolute	AnyDac	dbExpress	dbGo	DOA	ElevateDB	FireDac	FIBPlus	IBO	IBX	NativeDB	NexusDB	SQLDirect	UniDac	UIB	ZeosLib
AbsoluteDB		x
DB2			x		x			x						x	x
ElevateDB							x
Firebird			x	x				x	x	x					x	x	x
Interbase			x	x				x		x	x			x	x	x
MS SQL Server	x		x	x	x			x						x	x		x
MySQL			x	x				x							x		x
NexusDB													x
Oracle			x	x		x		x						x	x		x
PostgreSQL			x					x							x
SqlAnywhere								x				x
SQLite	x		x					x							x

Database versions used for homologation are listed below. TMS Aurelius tries to use no syntax or features of an specific version, its internal code uses the most generic approach as possible. Thus, other versions will most likely work, especially newer ones, but the list below is provided for your reference.

Database	Version
AbsoluteDB	7.05
DB2	9.7.500
ElevateDB	2.08
Firebird	2.5.1
Interbase	XE (10.0.3)
MS SQL Server	2008 R2 (10.50.1600)
MySQL	5.5.17 (Server) 5.1.60 (Client)
NexusDB	3.0900
Oracle	10g Express (10.2.0.1.0)
PostgreSQL	9.1
SqlAnywhere	17
SQLite	3.7.9

Analog to databases above, in table below we list data-access components used for homologation and respective versions. Newer versions should work with not problems.

Component Library	Versions
AbsoluteDB	7.05
AnyDac	5.0.3.1917
dbExpress	16.0
dbGo	Delphi 2010 and up
Direct Oracle Access	4.1.3.3
ElevateDB	2.32
FIBPlus	7.2
FireDac	Delphi XE5 and up
IBObjects	4.9.14
IBX	Delphi 2010 up to XE2
NativeDB	1.98
NexusDB	4.5023
SQL-Direct	6.3
UniDac	8.3.1
Unified Interbase (UIB)	2.5 revision 428 (01-Feb-2013)
ZeosLib	7.3

Database Manager - Creating/Updating Schema

If you have an existing database, you can use Aurelius on it. You can map your existing or new classes to the tables and fields of existing databases, and that's it. But for new applications, you might consider just modeling the classes, and let Aurelius build/update the database structure for you, creating all database objects needed to persist the objects. To do that, just create a TDatabaseManager object (declared in unit Aurelius.Engine.DatabaseManager) the same way you create a TObjectManager, and use one of the methods available to manager the schema (database structure). Common usage is as following:

uses
  Aurelius.Engine.DatabaseManager;
{...}
var
  DBManager: TDatabaseManager;
begin
  DBManager := TDatabaseManager.Create(MyConnection); // use default mapping explorer
  // operate on database schema using DBManager
  DBManager.Free;
end;

Alternatively, you can also pass a TMappingExplorer instance, which holds a custom mapping setup.

DBManager := TDatabaseManager.Create(MyConnection, MyMappingExplorer);

The following topics explain how to use the database manager object.

TAureliusDBSchema Component

The TAureliusDBSchema component is a non-visual, design-time component that encapsulates the TDatabaseManager class, used to build, update and validate the schema structure of the database (tables, fields, foreign and primary keys, etc.).

TAureliusDBSchema and TDatabaseManager have equivalent functionality; the main purpose for TAureliusDBSchema component is to provide an alternative RAD approach: instead of instantiating a TDatabaseManager from code, you just drop a TAureliusDBSchema component, connects it to a TAureliusConnection component, and you are ready to go.

Key properties

Name	Description
Connection: TAureliusConnection	Specifies the TAureliusConnection component to be used as the database connection. TAureliusConnection acts as a connection pool of one single connection: it will create a single instance of IDBConnection and any manager using it will use the same IDBConnection interface for the life of the TAureliusConnection component. The IDBConnection interface will be passed to the TDatabaseManager constructor to create the instance that will be encapsulated.
ModelNames: string	The name(s) of the model(s) to be used by the manager. You can leave it blank, if you do it will use the default model. Two or more model names should be separated by comma. From the model names it will get the property TMappingExplorer component that will be passed to the TDatabaseManager constructor to create the instance that will be encapsulated.
DBManager: TDatabaseManager	The encapsulated TDatabaseManager instance used to perform the database operations.

Usage

As mentioned, TAureliusDBSchema just encapsulates a TDatabaseManager instance. So for all functionality (methods, properties), just refer to TDatabaseManager documentation and related topics that explain how to build, update and validate the database schema.

The encapsulated object is available in property DBManager. If you miss any specific method or property in TAureliusDBSchema, you can simply fall back to DBManager instance and use it from there. For example, the following methods are equivalent:

AureliusDBSchema1.UpdateDatabase;
AureliusDBSchema1.DBManager.UpdateDatabase;

Actually, the first method is just a wrapper for the second one. Here is how TAureliusDBSchema.UpdateDatabase method is implemented, for example:

procedure TAureliusDBSchema.UpdateDatabase;
begin
  DBManager.UpdateDatabase;
end;

Memory management

Here is the lifecycle of the encapsulated TDatabaseManager instance itself:

• The TDatabaseManager instance will be created on demand, i.e., when TAureliusDBSchema is created, the TDatabaseManager is not yet created. It will only be instantiated when needed.

• If the connection or model name is changed, the encapsulated TDatabaseManager instance will be destroyed. A new TDatabaseManager instance will be created with the new connection/model, when needed.

Creating New Schema

You can create a new schema from an empty database using method BuildDatabase:

uses
  Aurelius.Engine.DatabaseManager;
{...}
var
  DBManager: TDatabaseManager;
begin
  DBManager := TDatabaseManager.Create(MyConnection);
  DBManager.BuildDatabase;
  DBManager.Free;
end;

This method will execute all SQL statements that create the whole database structure needed to persist the mapped entity classes. It does not take into account the existing database schema, so if tables already exist, an "object already exists" error will happen in database server when executing the statement. You can alternatively just generate the SQL script without executing it.

Even though this method does not perform any reverse engineering to check existing database structure, a schema validation result is available. Results are provided as if the existing database is empty.

Updating Existing Schema

You can update the existing database structure using method UpdateDatabase:

uses
  Aurelius.Engine.DatabaseManager;
{...}
var
  DBManager: TDatabaseManager;
begin
  DBManager := TDatabaseManager.Create(MyConnection);
  DBManager.UpdateDatabase;
  DBManager.Free;
end;

This method will:

1. Perform a schema validation, which consists of:

   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.

2. Execute the SQL Script in the database, unless command execution is disabled (see Generating SQL Script).

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 ValidateDatabase method.

Since this method performs on a database that has existing object and data, it has some limitations. First, if you are unsure of the effects of schema update, it's strongly recommended that you check schema validation results before updating. Errors might occur when updating the schema, for example, if new schema requires a foreign key creating but existing data doesn't fit into this new constraint. See schema validation for a list of current valid operations and limitations.

Note that UpdateDatabase is a non-destructive method. This means that even if the validation reports that a data-holding object (table or column) needs to be dropped, the SQL statement for it will not be performed.

Dropping Existing Schema

You can drop the whole database structure from an existing database using method DestroyDatabase:

uses
  Aurelius.Engine.DatabaseManager;
{...}
var
  DBManager: TDatabaseManager;
begin
  DBManager := TDatabaseManager.Create(MyConnection);
  DBManager.DestroyDatabase;
  DBManager.Free;
end;

This method will execute all SQL statements that destroy the whole database structure needed to persist the mapped entity classes. It does not take into account the existing database schema, so if tables were already dropped, an "object does not exist" error will happen in database server when executing the statement. You can alternatively just generate the SQL script without executing it.

Even though this method does not perform any reverse engineering to check existing database structure, a schema validation result is available. Results are provided as if the existing database is complete, with all objects, and target database structure is empty.

Schema Validation

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 (action, 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 warnings:

• 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

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.

Generating SQL Script

All TDatabaseManager methods that perform some operation in the database schema generate an SQL script, available in the SQLStatements property. Most methods also execute such statements (like BuildDatabase, UpdateDatabase and DropDatabase). Some methods do not execute, like ValidateDatabase. But in all cases, the associated SQL script is available.

In TDatabaseManager you have the option to disable execution of SQL statements. This way you have the freedom to execute the statements as you want, using you our error handling system, your own graphical user interface to execute them, etc. To do that, just set SQLExecutionEnabled property to false.

Examples:

uses
  Aurelius.Engine.DatabaseManager;
{...}
var
  DBManager: TDatabaseManager;

  procedure OutputSQLScript;
  var
    SQLStatement: string;
  begin
    for SQLStatement in DBManager.SQLStatements do
      WriteLn(SQLStatement);
  end;

begin
  DBManager := TDatabaseManager.Create(MyConnection);
  DBManager.SQLExecutionEnabled := false;

  // Output an SQL Script to build a new database
  DBManager.BuildDatabase;
  OutputSQLScript; 

  // Output an SQL to drop the full database
  DBManager.DropDatabase;
  OutputSQLScript;

  // Output an SQL script to update the existing database
  DBManager.UpdateDatabase;
  OutputSQLScript;  

  DBManager.Free;
end;

Note that when SQLExecutionEnabled property is false, calling UpdateDatabase is equivalent to calling ValidateDatabase, so this code:

// Output an SQL script to update the existing database
DBManager.SQLExecutionEnabled := false;
DBManager.UpdateDatabase;
OutputSQLScript;

Could also be written just as:

// Output an SQL script to update the existing database
// Regardless of value of SQLExecutionEnabled property
DBManager.ValidateDatabase;
OutputSQLScript;

Other Properties and Methods

List of TDatabaseManager methods and properties not coverered by other topics in this chapter.

Properties

Name	Description
UseTransactions: Boolean	When True, all operations performed by TDatabaseManager will be executed in a transaction, i.e., the manager will automatically start a new transaction, and commit it at the end of operations, or rollback if there is an error. Nesting apply (if a transaction was already open, no commit or rollback will be performed). Default is False.