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:
Choose File > New > Other and then look for "TMS Business" category under "Delphi Projects". Then double click "TMS Aurelius Connection".
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.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).
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
TObject
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:
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
TObject
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
TMapping
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 TMapping |
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:
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.
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. |