Mapping
This chapter provides you information about how to map your classes to the database. While a mapping can be made so simple using a single automapping attribute, it can be fully configurable and might need lots of concepts to be done the way you need. Several mapping attributes are available, you can also create your classes using special types like Nullable<T> and TBlob, and so on.
The topics below describe all the mapping mechanism in TMS Aurelius.
Attributes
Object-Relational Mapping in Aurelius is done by using attributes. With this approach you can do your mapping directly when coding the classes, and by browsing the source code you can easily tell how the class is being mapped to the database.
Basically you just add attributes to the class itself, or to a field or property:
[Table('Customer')]
TMyCustomer = class
private
[Column('Customer_Name')]
FCustomerName: string;
...
For column and associations mapping Aurelius accepts mapping attributes in either class field or class property (but not both of course). We recommend using mapping attributes in fields whenever it's possible, for several reasons:
Attributes are kept in private section of your class, leaving the public section clean and easily readable.
Fields represent better the current state of the object. Properties can have getter and setters based on other data that it's not exactly the object state for persistance.
Some Aurelius features are better suited for fields. For example, lazy-loaded associations requires the use of a Proxy type, which makes more sense to be uses in fields (although you can use it in properties).
Still, there are situations where creating mapping attributes in properties are interesting, when for example you want to save the result of a runtime calculation in database.
Available attributes (declared in unit Aurelius.Mapping.Attributes
):
- Basic Mapping
- Association Mapping
- Many-Valued Association Mapping
- Behavior Mapping
- DB Structure Mapping
- Inheritance Mapping
- Automapping
- Concurrency Control
- Other attributes
Entity
Indicates that the class is an entity class, which means it can be persisted.
Level: Class Attribute
Description
Every class that you want to be persisted in database must have this
attribute. It's also used by Aurelius for automatic class registration.
When automatic registration is active in
global configuration, every class
marked with Entity attribute will be automatically registered as an
entity class.
Constructor
constructor Create;
Parameters
None.
Usage
[Entity]
TCustomer = class(TObject)
AbstractEntity
Indicates that the class is an abstract entity: it can hold some mapping information that is inherited by concrete entity classes, but it will not persisted in the database.
Level: Class Attribute
Description
An abstract entity is a class that can have mapping information, but will not be persisted to the database. It allows you to have an entity classs that descend from an abstract entity class and inherit mapping information from it. This way you can have a class hiearchy without having to persist the ancestor classes, like you would have to do using the single-table or joined-tables inheritance strategies.
Not every mapping information can be used in abstract entities. In abstract entities you can:
- map primitive type columns (using Column attribute)
- map the entity id (like attributes Id and UnsavedValue)
- map associations
- use attributed-based events
- add attribute-based validation
- use global filters (using attributes like FilterDef and FilterDefParam).
Not supported:
- table-specific mapping attributes like Table, Sequence, UniqueKey, DBIndex, ForeignKey
- attributes related to inheritance strategy like Inheritance
- DiscriminatorColumn
- DiscriminatorValue
- PrimaryJoinColumn.
Constructor
constructor Create;
Parameters
None.
Example
[AbstractEntity]
[Automapping]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TBaseEntity = class
strict private
FId: Integer;
[Column('CUSTOM_TAG', [])]
FCustomTag: Integer;
FCreatedAt: TDateTime;
FUpdatedAt: Nullable<TDateTime>;
protected
[OnInserting]
procedure OnInserting;
[OnValidate]
function ValidateDates: IValidationResult;
public
property Id: Integer read FTheId write FTheId;
property CustomTag: Integer read FCustomTag write FCustomTag;
property CreatedAt: TDateTime read FCreatedAt write FCreatedAt;
property UpdatedAt: Nullable<TDateTime> read FUpdatedAt write FUpdatedAt;
end;
[AbstractEntity]
[Filter('Multitenant')]
[FilterDef('Multitenant', '{TenantId} = :tenantId')]
[FilterDefParam('Multitenant', 'tenantId', TypeInfo(string))]
TMultitenantEntity = class(TBaseEntity)
private
[Column('TENANT_ID', [], 50)]
FTenantId: string;
public
property TenantId: string read FTenantId write FTenantId;
end;
// TCustomer will inherit all mapping information
// from TMultitenantEntity and TBaseEntity classes
[Entity]
[Automapping]
TCustomer = class(TMultitenantEntity)
strict private
FName: string;
public
property Name: string read FName write FName;
end;
Id
Specifies the Identifier of the class.
Level: Class Attribute
Description
Every object must be uniquely identified by Aurelius so that it can
properly save and manage it. The concept is similar to a primary key in
database. This attribute allows you to specify which field (or property)
in the class will be used to uniquely identify the class. The value of
that field/property must be unique for every object, and you can specify
how that value will be generated for each object.
In addition, if you are creating the database structure from the mapped classes, Aurelius will create a primary key in the database corresponding to the field/column mapping.
If you are using inheritance, you must only declare the Id attribute in the base class of the hierarchy (the ancestor class). The inherited child classes can't have their own Id attribute.
For composite id's, specify as many Id attributes as you need to build the composite identifier.
Constructor
constructor Create(AMemberName: string; AGenerator: TIdGenerator);
Parameters
AMemberName: Contains the name of field or property that identifies the object.
AGenerator: Indicates how the Id value will be generated. Valid values are (prefixed by TIdGenerator):
None: Id value will not be automatically generated. Your application must assign a value to it and be sure it's unique.
IdentityOrSequence: Aurelius will ask the database to generate a new Id. If the database supports sequences and a sequence is defined, then Aurelius will use the sequence to generate the value. Otherwise, it will use identity (auto-numerated) fields. If no sequence is defined and database doesn't support identity fields, an exception will be raised. The name of the sequence to be created and used by Aurelius can be defined using the Sequence attribute. The type of the property that identifies the entity should be integer.
Guid: Aurelius will generate a GUID (Globally Unique Identifier) value as the entity identifier. The type of the property that identifies the entity should be TGuid or string.
Uuid38: Aurelius will generate a 38-length UUID (Universally Unique Identifier) value as the entity identifier. An UUID is just a string representation of a GUID value, with the format "{550e8400-e29b-41d4-a716-446655440000}" (with hyphens and curly brackets). The type of the property that identifies the entity should be string (with a minimum length of 38 characters).
Uuid36: Aurelius will generate a 36-length UUID (Universally Unique Identifier) value as the entity identifier. An UUID is just a string representation of a GUID value, with the format "550e8400-e29b-41d4-a716-446655440000" (with hyphens but no curly brackets). The type of the property that identifies the entity should be string (witha minimum length of 36 characters).
Uuid32: Aurelius will generate a 32-length UUID (Universally Unique Identifier) value as the entity identifier. An UUID is just a string representation of a GUID value, with the format "550e8400e29b41d4a716446655440000" (no hyphens and no curly brackets). The type of the property that identifies the entity should be string (with a minimum length of 32 characters).
SmartGuid: Sequential GUID (Globally Unique Identifier) value optimized for the database being used. The generated sequential GUID will minimize clustered index fragmentation, which is an usual problem when using regular GUID's, causing performance loss. Aurelius will choose the best algorithm to generate the GUID sequence depending on the database being used. For most of them, the GUID will be sequential in its string format, which is optimum for most databases and also when you use string properties. For Microsoft SQL Server, for example, it will choose a different algorithm (sequential in the last bytes) which is best given the way SQL Server sorts GUID's internally. In general you should use SmartGuid generator instead of Guid since both achieve the same results but SmartGuid performs better.
Note
For composite id's the AGenerator parameter is ignored and None is used.
Usage
[Id('FId', TIdGenerator.IdentityOrSequence)]
TCustomer = class(TObject)
private
[Column('CUSTOMER_ID')]
FId: integer;
Table
Specifies the database table where the objects will be saved to.
Level: Class Attribute
Description
Use the Table attribute to map the class to a database table. Every
object instance saved will be a record in that table.
If you are using inheritance with single table strategy, you must use the Table attribute in the ancestor class only, since all classes will be saved in the same table.
If you are using inheritance with joined tables strategy, you must use Table attribute in all classes, since every class will be saved in a different table.
Constructor
constructor Create(Name: string); overload;
constructor Create(Name, Schema: string); overload;
Parameters
Name: The name of the table in database.
Schema: Optionally you can specify the schema of the database.
Usage
[Table('Customers')]
TCustomer = class(TObject)
private
[Table('Orders', 'dbo')]
TOrder = class(TObject)
private
Column
Specifies the table column where the field/property value will be saved to.
Level: Field/Property Attribute
Description
Use Column attribute to map a field/property to a table column in the
database. When saving an object, Aurelius will save and load the
field/property value in the specified table column. Only
fields/properties mapped using a Column attribute will be saved in the
database (unless class is automapped using
Automapping attribute).
Aurelius will define the table column data type automatically based on type of field/property being mapped.
Constructor
constructor Create(Name: string); overload;
constructor Create(Name: string; Properties: TColumnProps); overload;
constructor Create(Name: string; Properties: TColumnProps; Length: Integer); overload;
constructor Create(Name: string; Properties: TColumnProps;
Precision, Scale: Integer); overload;
Parameters
Name: Contains the name of table column in the database where the field/property will be mapped to.
Properties: A set containing zero or more options for the column. TColumnProps and TColumnProp are declared as follow:
TColumnProp = (Unique, Required, NoInsert, NoUpdate, Lazy); TColumnProps = set of TColumnProp;
Unique: Values of this column must be unique. Aurelius will create an unique key (index) in the database to ensure unique values for this column. The index name will be the same as the column name. If you want to define a different name, do not set this flag and use UniqueKey attribute instead.
Required: Column must be NOT NULL. Values are required for this field/property.
NoInsert: When inserting a record in the database, do not include this column in the INSERT command. The value of this field/property will not be saved in the database in INSERT commands. Note that for Id fields using identity (autogenerated), Aurelius will automatically not include the field in the INSERT statement, regardless if NoInsert is specified or not.
NoUpdate: When updating a record in the database, do not include this column in the UPDATE command. The value of this field/property will not be saved in the database in UPDATE commands. This flag is usually used for Id fields which once inserted should not be changed anymore.
Lazy: Used for blob fields only. Indicates that lazy-loading will be used for the blob, i.e., the content of the blob will only be retrieved from the database when needed. If the property is not of type TBlob, this option will be ignored.
Length: Used for string field/property. It's the maximum length of the table column. Usually this is mapped to the VARCHAR type, i.e., if Length is 30, the data type of table column will be VARCHAR(30). It it's not specified, Aurelius will use the default length for string data types.
Precision, Scale: Used for numeric field/property. Specifies the precision and scale of numeric columns in the database table. If not specified, default values will be used.
Usage
Column('MEDIA_NAME', [TColumnProp.Required], 100)]
property MediaName: string read FMediaName write FMediaName;
[Column('DURATION', [])]
property Duration: Nullable<integer> read FDuration write FDuration;
Model
Specifies the model where the entity/class belongs to, in a multi-model design. It's an optional attribute.
Level: Class Attribute
Description
Use the Model attribute to tell Aurelius the model where that entity
(class) belongs to. This attribute allows you to build
multi-model applications, so that
you can separate your mapping in multiple models. By using the Model
attribute you can easily do it in a declarative way, specifying the
model of each class.
You can add multiple Model attributes to the class, meaning that the class belongs to more than one model.
This attribute is optional and if omitted the class will be considered to belonging to the default model.
Constructor
constructor Create(Name: string);
Parameters
- Name: The name of the model.
Usage
[Entity, Automapping]
[Model('Sample')]
TCustomer = class(TObject)
[Entity, Automapping]
[Model('Sample')]
[Model('Security')]
TUserInfo = class(TObject)
Association
Specifies a many-to-one association (relationship).
Level: Field/Property Attribute
Description
Use Association attribute to indicate that the field/property represents
a many-to-one association with another class. For example, if you have
property Customer of type TCustomer, it means that your object is
associated with one (and only one) customer. Associations can only be
defined for fields and properties of class types, and the associated
class must also be an Entity class, so you
can have a relationship between one class and another (between tables,
at database level).
You must always use Association attribute together with JoinColumn attribute. While the former is used to define generic, class-level meta-information about the association, the latter is used to define database-level relationships (fields that will be foreign keys).
Constructor
constructor Create; overload;
constructor Create(AProperties: TAssociationProps); overload;
constructor Create(AProperties: TAssociationProps; Cascade: TCascadeTypes); overload;
Parameters
AProperties: Specifies some general properties for the association. Valid values are:
TAssociationProp = (Lazy, Required); TAssociationProps = set of TAssociationProp;
Lazy: The associated object is not loaded together with the current object. Lazy-Loading is used. In a SELECT operation, Aurelius will only retrieve the Id of the associated object. The object will only be loaded when the application effectively needs it (e.g., when user references property MyObject.AssociatedObject). When it happens, Aurelius will perform another SELECT in the database just to retrieve the associated object data. Only at this point the object is instantiated and data is filled.
If Lazy is not specified, the default behavior is eager-mode loading. It means that when the object is loaded, the associated object is also fully loaded. Aurelius will perform a INNER (or LEFT) JOIN to the related tables, fetch all needed fields, create an instance of the associated object and set all its properties. This is the default value.Required: Associated object is required. This is logical information for the model itself (metadata). This flag will not be used by Aurelius to set the NOT NULL flag of the underlying database field(s). You will still have to set the column as required in the JoinColumn attribute, if needed.
Cascade: Defines how Aurelius will behave on the association when the container object is saved, deleted or updated.
It's recommended that you use one of the predefined cascades, like CascadeTypeAll, CascadeTypeAllButRemove or CascadeTypeAllRemoveOrphan. For associations, CascadeTypeAllButRemove is the most recommended one.TCascadeType = (SaveUpdate, Merge, Remove, RemoveOrphan, Refresh, Evict, Flush); TCascadeTypes = set of TCascadeType; CascadeTypeAll = [Low(TCascadeType)..High(TCascadeType)] - [TCascadeType.RemoveOrphan]; CascadeTypeAllRemoveOrphan = CascadeTypeAll + [TCascadeType.RemoveOrphan]; CascadeTypeAllButRemove = CascadeTypeAll - [TCascadeType.Remove];
SaveUpdate: When object is saved (inserted), or updated, the associated object will be automatically saved/updated. The associated object is actually saved before the container object, because the Id of associated object might be needed to save the container object.
Merge: When object is merged, the associated object will also be merged.
Remove: When object is removed from database, the associated object will also be removed.
Refresh: When object is refreshed from database, the associated object will also be refreshed.
RemoveOrphan: Used only in Many-Valued Associations.
Evict: When object is evicted from manager, the associated object will also be evicted.
Flush: If an object is flushed explicitly, the associated object will also be flushed. This cascade doesn't have any effect if Flush is called for all objects in manager (without parameter).
Usage
[Association([], CascadeTypeAllButRemove)]
[JoinColumn('ID_SONG_FORMAT', [])]
property SongFormat: TSongFormat read FSongFormat write FSongFormat;
[Association([TAssociationProp.Lazy], [TCascadeType.SaveUpdate])]
[JoinColumn('ID_ARTIST', [])]
FArtist: Proxy<TArtist>;
Note
In the previous example, the Proxy<TArtist> type is used because association was declared as lazy (see Associations and Lazy-Loading). Alternatively you can declare FArtist field just as TArtist, and in this case association will not be lazy-loaded.
JoinColumn
Specifies the table column used as foreign key for one association.
Level: Field/Property Attribute
Description
Use JoinColumn attribute to map a field/property to a table column in
the database. The field/property must also have an
Association attribute defined for it.
The table column defined by JoinColumn will be created as a foreign key to the referenced association. By default, the relationship created by Aurelius will reference the Id of the associated object. But you can reference another value in the object, as long as the value is an unique value.
The data type of the table column defined by JoinColumn will be the same as the data type of the referenced column in the associated table.
When the association is a class with composite Id's, specify as many JoinColumn attributes as the number of columns in the primary key of association class. For example, if the associated class has three table columns in the primary key, you must specify three JoinColumn attributes, one for each column.
Constructor
constructor Create(Name: string); overload;
constructor Create(Name: string; Properties: TColumnProps); overload;
constructor Create(Name: string; Properties: TColumnProps;
ReferencedColumnName: string); overload;
Parameters
Name: Contains the name of table column in the database used to hold the foreign key.
Properties: A set containing zero or more options for the column. TColumnProps and TColumnProp are declared as follow:
TColumnProp = (Unique, Required, NoInsert, NoUpdate, Lazy); TColumnProps = set of TColumnProp;
Unique: Values of this column must be unique. Aurelius will create an unique key (index) in the database to ensure unique values for this column. In practice, if this flag is set the relationship will become a one-to-one relationship.
Required: Column must be NOT NULL. Values are required for this field/property. This flag must be set together with the Required flag in Association attribute.
NoInsert: When inserting a record in the database, do not include this column in the INSERT command. The value of this field/property will not be saved in the database in INSERT commands.
NoUpdate: When updating a record in the database, do not include this column in the UPDATE command. The value of this field/property will not be saved in the database in UPDATE commands.
Lazy: Not used. This option is only used in Column attribute.
ReferencedColumnName: Indicates the column name in the associated table that will be referenced as foreign key. The referenced column must be unique in the associated table. This parameter is optional, if it's not specified (and usually it won't), the name of Id will be used - in other words, the primary key of the associated table will be referenced by the foreign key.
Usage
[Association]
[JoinColumn('ID_SONG_FORMAT', [])]
property SongFormat: TSongFormat read FSongFormat write FSongFormat;
[Association([TAssociationProp.Lazy], [])]
[JoinColumn('ID_ARTIST', [])]
FArtist: Proxy<TArtist>;
Note
In the previous example, the Proxy<TArtist> type is used because association was declared as lazy (see Associations and Lazy-Loading). Alternatively you can declare FArtist field just as TArtist, and in this case association will not be lazy-loaded.
ManyValuedAssociation
Specifies an one-to-many association (relationship), or in other words, a collection of objects.
Level: Field/Property Attribute
Description
Use ManyValuedAssociation attribute to indicate that the field/property
represents a one-to-many association - a collection of objects of the
same class. For example, if you have property Addresses of type
TList<TAddress>, it means that each object in collection is
associated with the container object. Many-valued associations can only
be defined for fields and properties of type TList<class>,
and the associated class must also be an Entity
class, so you can have a relationship between one class and another
(between tables, at database level).
Defining a collection of child objects like this will require that the table holding child objects records will have a foreign key column referencing the container object. This can be done in two ways.
Use ForeignJoinColumn attribute to define a foreign key in the child object class.
Create an Association in the child object class and then use MappedBy parameter to indicate the field/property that holds the association. This will become a bidirectional association, since you have the child object referencing the parent object though an Association, and the parent object holding a collection of child objects through a ManyValuedAssociation.
Constructor
constructor Create; overload;
constructor Create(AProperties: TAssociationProps); overload;
constructor Create(AProperties: TAssociationProps; Cascade: TCascadeTypes); overload;
constructor Create(AProperties: TAssociationProps; Cascade: TCascadeTypes;
MappedBy: string); overload;
Parameters
AProperties: Specifies some general properties for the association. Valid values are:
TAssociationProp = (Lazy, Required); TAssociationProps = set of TAssociationProp;
Lazy: The associated list is not loaded together with the current object. Lazy-Loading is used. In a SELECT operation, Aurelius will only retrieve the Id of the parent object. The list will only be loaded when the application effectively needs it (e.g., when user references property MyObject.AssociatedList). When it happens, Aurelius will perform another SELECT in the database just to retrieve the associated object data. Only at this point the object is instantiated and data is filled.
If Lazy is not specified, the default behavior is eager-mode loading. It means that after the parent is loaded, the associated list will be immediately load, but still with another SELECT statement. For lists, since eager mode will not improve performance, it's always recommended to use Lazy mode, unless you have a very specific reason for not doing so, like for example, you will destroy the object manager after retrieving objects and lazy-loading the lists will not be further possible.Required: This option is ignored in Many-valued Associations.
Cascade: Defines how Aurelius will behave on the association list when the container object is saved, deleted or updated.
It's recommended that you use one of the predefined cascades, like CascadeTypeAll, CascadeTypeAllButRemove or CascadeTypeAllRemoveOrphan. For many-valuded associations, CascadeTypeAll or CascadeTypeAllRemoveOrphan are the recommended ones.TCascadeType = (SaveUpdate, Merge, Remove, RemoveOrphan, Refresh, Evict, Flush); TCascadeTypes = set of TCascadeType; CascadeTypeAll = [Low(TCascadeType)..High(TCascadeType)] - [TCascadeType.RemoveOrphan]; CascadeTypeAllRemoveOrphan = CascadeTypeAll + [TCascadeType.RemoveOrphan]; CascadeTypeAllButRemove = CascadeTypeAll - [TCascadeType.Remove];
SaveUpdate: When object is save (inserted) or updated, the associated object list will be automatically saved. First the parent object is saved, then all objects in the collection are also saved.
Merge: When object is merged, all the associated objects in the object list are also merged.
Remove: When object is removed from database, all objects in the list are also removed.
Refresh: When object is refreshed from database, the associated list will be reloaded. If the list is proxied (lazy-loaded), then the proxy will be reset (unloaded), and objects in list won't be refreshed. If the list is not proxied, objects in list will be refreshed.
RemoveOrphan: When a detail (child) object is removed from a list, it will also be deleted (removed from database and destroyed). If RemoveOrphan is not present, then the child object will not be deleted, just the association with the parent object will be removed (i.e., the foreign key column will be set to null).
Evict: When object is evicted from manager, the associated object will also be evicted.
Flush: If an object is flushed explicitly, the associated objects in the list will also be flushed. This cascade doesn't have any effect if Flush is called for all objects in manager (without parameter).
MappedBy: This parameter must be used when the association is bidirectional, i.e., the associated class referenced in the list has also an Association to the object containing the list, see Description above.
This parameter must contain the name of field or property, in the child object class, that holds an Association referencing the container object.
Usage
Example using MappedBy parameter:
TMediaFile = class
private
[Association([TAssociationProp.Lazy], [])]
[JoinColumn('ID_ALBUM', [])]
FAlbum: Proxy<TAlbum>;
TAlbum = class
public
[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FAlbum')]
property MediaFiles: TList<TMediaFile> read FMediaFiles write FMediaFiles;
Example using ForeignJoinColumn attribute (in this example, TTC_InvoiceItem class does not have an association to TTC_Invoice class, so "INVOICE_ID" field will be created in InvoiceItem table):
TTC_Invoice = class
private
[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan)]
[ForeignJoinColumn('INVOICE_ID', [TColumnProp.Required])]
FItems: TList<TTC_InvoiceItem>;
Note
In the previous example, the Proxy<TAlbum> type is used because association was declared as lazy (see Associations and Lazy-Loading). Alternatively you can declare FAlbum field just as TAlbum, and in this case association will not be lazy-loaded.
ForeignJoinColumn
Specifies the table column used as foreign key in the child object, for a many-valued-association.
Level: Field/Property Attribute
Description
Use ForeignJoinColumn attribute to map a field/property to a table
column in the database. The field/property must also have an
ManyValuedAssociation
attribute defined for it.
The table column defined by ForeignJoinColumn will be created as a foreign key to the referenced association. Note that the column will be created in the child table, and it will reference the parent table, i.e, the "container" of the object list.
By default, the relationship created by Aurelius will reference the Id of the associated object. But you can reference another value in the object, as long as the value is an unique value.
The data type of the table column defined by ForeignJoinColumn will be the same as the data type of the referenced column in the associated table.
This attribute must only be used if the ManyValuedAssociation is unidirectional. If it's bidirectional, you should not use it, and just the MappedBy parameter when declaring the ManyValuedAssociation attribute.
When the association is a class with composite Id's, specify as many ForeignJoinColumn attributes as the number of columns in the primary key of association class. For example, if the associated class has three table columns in the primary key, you must specify three ForeignJoinColumn attributes, one for each column.
Constructor
constructor Create(Name: string); overload;
constructor Create(Name: string; Properties: TColumnProps); overload;
constructor Create(Name: string; Properties: TColumnProps;
ReferencedColumnName: string); overload;
Parameters
Name: Contains the name of table column in the database used to hold the foreign key.
Properties: A set containing zero or more options for the column. TColumnProps and TColumnProp are declared as follow:
TColumnProp = (Unique, Required, NoInsert, NoUpdate, Lazy); TColumnProps = set of TColumnProp;
Unique: Values of this column must be unique. Aurelius will create an unique key (index) in the database to ensure unique values for this column.
Required: Column must be NOT NULL. Values are required for this field/property.
NoInsert: When inserting a record in the database, do not include this column in the INSERT command. The value of this field/property will not be saved in the database in INSERT commands.
NoUpdate: When updating a record in the database, do not include this column in the UPDATE command. The value of this field/property will not be saved in the database in UPDATE commands.
Lazy: Not used. This option is only used in Column attribute.
ReferencedColumnName: Indicates the column name in the associated table that will be referenced as foreign key. The referenced column must be unique in the associated table. This parameter is optional, if it's not specified (and usually it won't), the name of Id field will be used - in other words, the primary key of the associated table will be referenced by the foreign key.
Usage
TTC_Invoice = class
private
[ManyValuedAssociation([], CascadeTypeAll)]
[ForeignJoinColumn('INVOICE_ID', [TColumnProp.Required])]
FItems: TList<TTC_InvoiceItem>;
OrderBy
Specifies the default order of the items in a many-valued association.
Level: Field/Property Attribute
Description
Use OrderBy attribute to define in which order the objects in a
many-valued association (collection) will be loaded from the database.
If no OrderBy attribute is present, no order will be defined (no ORDER
BY clause will be added to the SELECT statement that retrieves the
records) and items will be loaded according to the default ordering used
by the database server. Note that after the items are loaded from the
database, no further ordering is performed - this attribute only
enforces ordering at database level, not memory level. Thus, if you
later manually add new items to the collection in an unsorted order,
they will remain that way.
You can specify one or more member names (property or field names) in this attribute (not database column names). Multiple member names must be separated by comma (,). You can use the same member names that you can use when ordering results in a query.
The default order direction is ascending. You can specify a descending order by appending " DESC" (with space) after the member name.
You can also order by members of associated objects. To do that, prefix the member name with the name of the association field/property followed by a "." (dot). Nested associations can be used. For example, if your class has a property "Customer" which in turn has a property "Country", you can order by country's name using "Customer.Country.Name".
Constructor
constructor Create(MemberNames: string);
Parameters
- MemberNames: Contains the name(s) of the member(s) used to order the collection.
Multiple member names must be separated by comma. Associated members must be prefixed with association name followed by dot. You can optionally use "DESC" suffix to order by descending direction.
Usage
TTC_Invoice = class
private
[ManyValuedAssociation([], CascadeTypeAll)]
[OrderBy('Product.Name, Category DESC')]
FItems: TList<TTC_InvoiceItem>;
Where
Specifies a SQL expression to be added the WHERE clause of the final SQL used to retrieve entities or the items of a many-valued association.
Level: Class or Field/Property Attribute
Description
Use Where attribute to define additional filter (SQL expression) to the
final SQL used to retrieve a specified entity, a list of entities or
items of a many-valued association.
Just like in the query SQL condition, be aware that the SQL clause will be just injected in the SQL statement, you must be sure it will work. You can also use property names between curly brackets. Write the name of the property inside curly brackets and Aurelius will translate it into the proper alias.fieldname format according to the context. For example, "{Deleted} = 'F'".
If you apply the attribute to a field/property, it must be a many-valued association, and the WHERE clause will only apply for that list.
If you apply the attribute to a class, it will apply to any situation where entities of that class are retrieved. When you find a single entity, or when you query entities of that class, the filter will be applied. Even if the entity is an association (many-to-one) of a parent entity, the filter will be applied. For example, suppose a TInvoice class has a Customer property of type TCustomer. If TCustomer entity has a [Where] attribute it will be applied when retrieving the Customer of that TInvoice instance. Even if the associated customer exists in the database, if it's filtered out by the WHERE clause, the TInvoice.Customer property will come as nil value.
You can add multiple Where attributes in same class or property. They will all be combined with the AND operator.
Constructor
constructor Create(const ASqlClause: string);
Parameters
- ASqlClause: The SQL expression that will be added to the WHERE clause to filter the entity or many-valued association.
Usage
TCustomer entities will not be retrieved if the Deleted field is equal to 'T':
[Entity, Automapping]
[Where('{Deleted} <> ''T''')]
TCustomer = class
private
FId: integer;
FName: string;
The FNewCustomers list will only bring TCustomer objects if the Status field is equal to 'New'. Note that the Where clause above of the TCustomer entity will still apply, meaning the Status must be 'New' and Deleted must not be 'T':
TParent = class
private
[ManyValuedAssociation([], CascadeTypeAll)]
[Where('{Status} = ''New''')]
FNewCustomers: TList<TCustomer>;
Inheritance
Identifies the class as the ancestor for a hierarchy of entity classes.
Level: Class Attribute
Description
Use Inheritance attribute to allow persistence of the current class and
all its descendants (if they are marked with Entity attribute).
If you have a class hierarchy and want Aurelius to save all of those classes, you must add the Inheritance attribute to the top level (parent) class of all the hierarchy in order to use a specific inheritance strategy. If you are using single table strategy, you also need to define a DiscriminatorColumn attribute in the base class, and a DiscriminatorValue attribute in each descendant class. If you are using joined tables strategy, you need to define a PrimaryJoinColumn attribute and a Table attribute in each descendant class.
Constructor
constructor Create(Strategy: TInheritanceStrategy);
Parameters
Strategy: Specifies the inheritance strategy to be used in the class hierarchy. Valid values are (prefixed by TInheritanceStrategy):
SingleTable: Use single table strategy for the class hierarchy. You must also define a DiscriminatorColumn attribute in the class and a DiscriminatorValue attribute in each descendant class.
JoinedTables: Use joined tables strategy for the class hierarchy. In this strategy for each descendant class you must define a PrimaryJoinColumn and Table attribute.
Usage
[Inheritance(TInheritanceStrategy.SingleTable)]
[DiscriminatorColumn('MEDIA_TYPE', TDiscriminatorType.dtString)]
TMediaFile = class
DiscriminatorColumn
Specifies the column table to be used as class discriminator in a single table inheritance strategy.
Level: Class Attribute
Description
Use DiscriminatorColumn attribute to specify the column in the table
used as class discriminator. When you use Inheritance
attribute and set strategy to single table,
you must also define this attribute. In single table strategy, all classes
are saved in the same table, and the value of discriminator column is the way
Aurelius use to tell the class representing each record in the table.
For example, if you have both classes TCar and TMotorcycle inheriting
from TVehicle and all classes being saved in the same table, when
Aurelius reads a record it must tell if it represents a TCar or
TMotorcycle. It does that using the value specified in the discriminator
column. Each descending class must declare the attribute
DiscriminatorValue that will define what is the value
to be saved in the discriminator column that will represent the specified class.
Constructor
constructor Create(Name: string; DiscriminatorType: TDiscriminatorType); overload;
constructor Create(Name: string; DiscriminatorType: TDiscriminatorType;
Length: Integer); overload;
Parameters
Name: The name of the table column that will hold discriminator values which will identify the class. This column will be created by Aurelius if you create the database.
DiscriminatorType: Specifies the column data type. Valid values are (prefixed by TDiscriminatorType):
dtString: Discriminator column type will be string. Discriminator values must be strings.
dtInteger: Discriminator column type will be integer. Discriminator values must be integer numbers.
Length: Specifies the length of column data type, only used when DiscriminatorType is string. If not specified, a default value is used.
Usage
[Inheritance(TInheritanceStrategy.SingleTable)]
[DiscriminatorColumn('MEDIA_TYPE', TDiscriminatorType.dtString)]
TMediaFile = class
DiscriminatorValue
Specifies the value that identifies a class in the discriminator column, when using single table inheritance strategy.
Level: Class Attribute
Description
Use DiscriminatorValue to define the value to be saved in the
discriminator column when the class is saved. In a
single table inheritance strategy, all
classes are saved in the same table. Thus, when a subclass is saved,
Aurelius updates an extra table column with a value that indicates that
the record contains that specific class. This value is specified in this
DiscriminatorValue attribute. It's also used by Aurelius when the
record is being read, so it knows which class needs to be instantiated
when loading objects from database.
Constructor
constructor Create(Value: string); overload;
constructor Create(Value: Integer); overload;
Parameters
- Value: The value to be used in the discriminator column. Value must be string or integer, depending on the type of the discriminator column declared in the DiscriminatorColumn attribute.
Usage
// Ancestor class:
[Inheritance(TInheritanceStrategy.SingleTable)]
[DiscriminatorColumn('MEDIA_TYPE', TDiscriminatorType.dtString)]
TMediaFile = class
// Child classes:
[DiscriminatorValue('SONG')]
TSong = class(TMediaFile)
[DiscriminatorValue('VIDEO')]
TVideo = class(TMediaFile)
PrimaryJoinColumn
Defines the primary key of a child table that will be referencing the primary key of a parent table, in a joined tables inheritance strategy.
Level: Class Attribute
Description
Use PrimaryJoinColumn attribute to specify the column that will be used
as primary key of the child table. If you specified a
joined tables inheritance strategy using the
Inheritance attribute in the base class,
then each descendant class will be saved in a different table in the
database, and it will be linked to the table containing the data of the
parent class. This relationship is one-to-one, so the child table will
have a primary key of the same data type of the parent table's primary
key. The child table's primary key will also be a foreign key
referencing the parent table. So PrimaryJoinColumn attribute is used to
define the name of the primary key column. Data type doesn't need to be
defined since it will be the same as the parent primary key.
You can omit the PrimaryJoinColumn attribute. In this case, the name of table column used will be the same as the name of table column in the base class/table.
When the ancestor is a class with composite Id's, you can specify one PrimaryJoinColumn attribute for each table column in the ancestor class primary key. If you specify less PrimaryJoinColumn attributes than the number of columns in the primary key, the missing ones will be considered default, i.e, the name of the table column in the primary key will be used.
Constructor
constructor Create(Name: string);
Parameters
- Name: The name of the child table column used as primary key and foreign key. If an empty string is provided, it will use the same name as the table column in the parent's class/table primary key.
Usage
// Ancestor class:
[TABLE('MEDIA_FILES')]
[Inheritance(TInheritanceStrategy.JoinedTables)]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TMediaFile = class
private
[Column('MEDIA_ID', [TColumnProp.Required])]
FId: integer;
// Child classes:
[TABLE('SONGS')]
[PrimaryJoinColumn('MEDIAFILE_ID')]
TSong = class(TMediaFile)
// In this case, a field with name MEDIA_ID will be created in table 'VIDEOS'
[TABLE('VIDEOS')]
[PrimaryJoinColumn('')]
TVideo = class(TMediaFile)
// In this case, a field with name MEDIA_ID will be created in table 'LIST_SHOWS'
// Since PrimaryJoinColumn attribute is not present
[TABLE('LIVE_SHOWS')]
TLiveShow = class(TMediaFile)
Sequence
Defines the sequence (generator) used to generate Id values.
Level: Class Attribute
Description
Use the Sequence attribute to define the database sequence (generator)
to be created (if requested) and used by Aurelius to retrieve new
Id values. If the database does not support
sequences, or the generator type specified in the
Id attribute does not use a database sequence,
this attribute is ignored.
Constructor
constructor Create(SequenceName: string); overload;
constructor Create(SequenceName: string; InitialValue, Increment: Integer); overload;
Parameters
SequenceName: The name of the sequence/generator in the database.
InitialValue: The initial value of the sequence. Default value: 1.
Increment: The increment used to increment the value each time a new value is retrieved from the sequence. Default value: 1.
Usage
[Sequence('SEQ_MEDIA_FILES')]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TMediaFile = class
UniqueKey
Defines an exclusive (unique) index for the table.
Level: Class Attribute
Description
Use UniqueKey if you want to define a database-level exclusive (unique)
index in the table associated with the class. Note that you do not need
to use this attribute to define unique keys for field defined in the
Id attribute, nor for columns defined as unique
in the Column attribute. Those are created
automatically by Aurelius. If you want to create a non-exclusive
(non-unique) index, use DBIndex attribute instead.
Constructor
constructor Create(Columns: string);
Parameters
- Columns: The name of the table columns that compose the unique key. If two or more names are specified, they must be separated by comma.
Usage
[UniqueKey('INVOICE_TYPE, INVOICENO')]
TTC_Invoice = class
DBIndex
Defines a non-exclusive index for the table.
Level: Class Attribute
Description
Use DBIndex if you want to define a database-level non-exclusive index
in the table associated with the class. The index will mostly be used to
improve performance when executing queries. If you want to create an
unique index, use UniqueKey attribute instead.
Constructor
constructor Create(const Name, Columns: string);
Parameters
Name: The name of the Index. When updating the database, this is what Aurelius will check to decide if the index needs to be created or not.
Columns: The name of the table columns that compose the unique key. If two or more names are specified, they must be separated by comma.
Usage
[DBIndex('IDX_INVOICE_DATE', 'ISSUEDATE')]
TTC_Invoice = class
ForeignKey
Defines the name of a foreign key.
Level: Field/Property Attribute
Description
Use ForeignKey to define a custom name for the foreign key generated by
an association or
many-valued association. This attribute is
optional even when Automapping is not
specified. When this attribute is not present, Aurelius will
automatically choose a name for the foreign key.
Constructor
constructor Create(AName: string);
Parameters
- AName: Specifies the name of the foreign key.
Usage
[Association([TAssociationProp.Lazy], [TCascadeType.SaveUpdate])]
[ForeignKey('FK_SONG_ARTIST')]
[JoinColumn('ID_ARTIST', [])]
FArtist: Proxy<TArtist>;
Enumeration
Specifies how to save an enumerated type in the database.
Level: Enumerator Attribute
Description
Use Enumeration attribute if you have fields or properties of enumerated
types and you want to save them in the database. Using Enumerator you
define how the enumerated values will be saved and loaded from the
database. The Enumerator attribute must be declared right above the
enumerated type.
Constructor
constructor Create(MappedType: TEnumMappingType); overload;
constructor Create(MappedType: TEnumMappingType; MappedValues: string); overload;
Parameters
MappedType: Indicated the type of the enumerated value in the database. Valid values are (prefixed by TEnumMappingType):
emChar: Enumerated values will be saved as single-chars in the database.
emInteger: Enumerated values will be saved as integer values. The value used is the ordinal value of the enumerated type, i.e, the first value in the enumerator will be saved as 0, the second as 1, etc..
emString: Enumerated values will be saved as strings in the database.
MappedValues: If MappedType is char or string, then you must use this parameter to specify the char/string values corresponding to each enumerated value. The values must be comma-separated and must be in the same order as the values in the enumerated type.
Usage
[Enumeration(TEnumMappingType.emChar, 'M,F')]
TSex = (tsMale, tsFemale);
Automapping
Indicates that the class is an entity class, and all its attributes are automapped.
Level: Class Attribute
Description
When Automapping attribute is present in the class, all mapping is done
automatically by Aurelius, based on the class declaration itself.
For more information about how automapping works, see
Automapping section.
If AutoMappingMode in global configuration is set to Full, then you don't need to define this attribute - every entity class is considered to be automapped.
Constructor
constructor Create;
Parameters
None.
Usage
[Entity]
[Automapping]
TCustomer = class(TObject)
Transient
Indicates a non-persistent field in an automapped class.
Level: Field Attribute
Description
When the class is being automapped using
Automapping attribute, by
default every field in the class is persisted. If you don't want an
specific field to be persisted, declare a Transient attribute before it.
Constructor
constructor Create;
Parameters
None.
Usage
[Entity]
[Automapping]
TCustomer = class(TObject)
private
[Transient]
FTempCalculation: integer;
Version
Indicates that the class member (field/property) holds the version of the entity, to be used in versioned concurrency control.
Level: Field/Property Attribute
Description
When adding this attribute to any member, Aurelius automatically enabled
versioned concurrency control on
entities of that class. This means that Aurelius will make sure that
updates on that entity will only happen if no other user changed entity
data in the meantime.
To accomplish that, the entity must hold the "version" value, so Aurelius knows which is the current version of that entity. You must thus add the Version attribute to any member of the class (field or property) so Aurelius knows where to save the version value.
The field/property type must be of Integer type.
Constructor
constructor Create;
Parameters
None.
Usage
[Entity]
[Automapping]
TCustomer = class(TObject)
private
[Version]
FVersion: integer;
Description
Allows to associate a description to the class or field/property.
Level: Class, Field or Property attribute
Description
Use Description attribute to better document your classes, fields and
properties, by adding a string description to it. Currently this
information is not used by Aurelius but this Description attribute can
be created when generating classes from database using
TMS Data Modeler tool.
You can later at runtime retrieve this information for any purposes.
Constructor
constructor Create(AText: string);
Parameters
- AText: The text to be associated with class, field or property.
Usage
[Entity]
[Automapping]
[Description('Customer data')]
TCustomer = class(TObject)
private
Automapping Feature
Automapping is an Aurelius feature that allows you to map a class without needing to specify all needed mapping attributes. Usually in an entity class you need to define table where data will be saved using Table attribute, then for each field or property you want to save you need to specify the Column attribute to define the table column in the database where the field/property will be mapped to, etc..
By defining a class as automapped, a lot of this mapping is done automatically based on class information, if it's not explicity specified. For example, the table name is automatically defined as the class name, with the "T" prefix removed.
To define a class as automapped, you just need to add the Automapping attribute to the class.
Automapping is not an all-or-nothing feature. Aurelius only performs the automatic mapping if no attribute is specified. For example, you can define a class as automapped, but you can still declare the Table attribute to specify a different table name, or you can use Column attribute in some specific fields or properties to override the default automatic mapping.
Below we list some of rules that automapping use to perform the mapping.
Table mapping
The name of table is assumed to be the name of the class. If the first character of the class name is an upper case "T", it is removed. All letters become uppercase and upcase characters in the middle of the name are preceded by underline. For example, class "TCustomer" will be mapped to table "CUSTOMER", and class "TMyInvoice" will be mapped to table "MY_INVOICE"
Column mapping
Every field in the class is mapped to a table column. Properties are ignored and not saved. If you don't want a specific class field to be saved automatically, add a Transient attribute to that class field.
The name of the table column is assumed to be name of the field. If the first character of the field name is an upper case "F", it is removed. All letters become uppercase and upcase characters in the middle of the name are preceded by underline. For example, field "FBirthday" is mapped to table column "BIRTHDAY" and field "FFirstName" is mapped to table column "FIRST_NAME".
If the class field type is a Nullable<T> type, then the table column will be optional (nullable). Otherwise, the table column will be required (NOT NULL).
For currency fields, scale and precision are mapped to 4 and 18. For float fields, scale and precision are mapped to 8 and 18, respectively. If field is a string, length used will be the default length specified in the global configuration.
If the field is an object instance instead of an scalar value/primitive type, then it will be mapped as an association, see below.
Associations
If the class field in an object instance (except a list), it will be mapped as an association to that class. The column name for the foreign key will be the field name (without "F") followed by "_ID". For example, if the class has a field:
FCustomer: TCustomer
Aurelius will create an association with TCustomer and the name of table column holding the foreign key will be "Customer_ID".
If the class field is a list type (TList<T>) it will be mapped as a many-valued association. A foreign key will be created in the class used for the list. The name of table column holding the foreign key is field name + table name + "_ID". For example, if class TInvoice has a field:
FItems: TList<TInvoiceItem>
Aurelius will create a many-valued association with TInvoiceItem, and a table column holding the foreign key will be created in table "InvoiceItem", with the name "Items_Invoice_ID".
If the field type is a Proxy<T> type, fetch type of the association will be defined as lazy, otherwise, it will be eager.
Identifier
If no Id attribute is specified in the class, Aurelius will use a field named "FID" in the class as the class identifier. If such class field does not exist and no Id attribute is defined, an error will be raised when the class is saved.
Enumerations
Enumerations are not automapped unless the auto mapping mode is configured to Full in global configuration. In this case, if an enumeration type does not have an Enumeration attribute defined, it will be automapped as string type, and the mapped value will be the name of the enumerated value.
For example, the enumerated type:
TSex = (seFemale, seMale);
will be mapped as string with mapped values 'seFemale', 'seMale'.
Sequences
If not specified, the name of the sequence to be created/used (if needed) will be "SEQ_" + table name. Initial value and increment will defined as 1.
Inheritance
Inheritance is not automapped and if you want to use it you will need explicitly define Inheritance attribute and the additional attributes needed for complete inheritance mapping.
Customizing automapping
You can customize the naming in automapping mechanism by passing an engine class to the automapping attribute:
[Entity, Automapping(TMyAutomapping)]
TMyEntity = class
TMyAutomapping
must inherit from TAutomappingEngine
, and you can then override some methods to use your custom naming:
TMyAutomapping = class(TAutomappingEngine)
strict protected
function FieldNameToSql(const Value: string): string; override;
public
function GetTableName(Clazz: TClass): string; override;
function GetSequenceName(Clazz: TClass): string; override;
end;
{ TMyAutomapping }
function TMyAutomapping.FieldNameToSql(const Value: string): string;
begin
Result := Value;
end;
function TMyAutomapping.GetSequenceName(Clazz: TClass): string;
begin
Result := 'seq' + GetTableName(Clazz);
end;
function TMyAutomapping.GetTableName(Clazz: TClass): string;
begin
Result := Copy(Clazz.ClassName, Pos('_', Clazz.ClassName) + 1);
end;
Nullable Type
Table columns in databases can be marked as optional (nullable) or required (not null). When you map a class property to a table column in the database, you can choose if the column will be required or not.
If the column is optional, the column value hold one valid value, or it
can be null. Problem is that primitive types in Delphi cannot be
nullable. Using Nullable<T> type which is declared in unit
Bcl.Types.Nullable
, you can create a property in your class that
can represent the exact value in the database, i.e., it can hold a
value, or can be nullable.
For example, suppose you have the following class field mapped to the database:
[Column('BIRTHDAY', [])]
FBirthday: TDate;
The column BIRTHDAY in the database can be null. But the field FBirthday in the class cannot be null. You can set FBirthday to zero (null date), but this is different from the NULL value in the database.
Thus, you can use the Nullable<T> type to allow FBirthday field to receive null values:
[Column('BIRTHDAY', [])]
FBirthday: Nullable<TDate>;
You can use FBirthday directly in expressions and functions that need TDate, Delphi compiler will do the implicit conversion for you:
FBirthday := EncodeDate(2000, 1, 1);
If the compiler fails in any situation, you can read or write the TDate value using Value property:
FBirthday.Value := Encode(2000, 1, 1);
To check if the field has a null value, use HasValue or IsNull property:
IsBirthdayNull := not FBirthday.HasValue;
IsBirthdayNull := FBirthday.IsNull;
There is a global Nullable variable named SNull which represents the null value, you can also use it to read or write null values:
if FBirthday <> SNull then // birthday is not null
FBirthday := SNull; // Set to null
Binary Large Objects (Blobs)
You can map binary (or text) large objects (Blobs) table columns to properties in your class. As with other properties, Aurelius will properly save and load the content of the property to the specified table column in the database. In order for it to know that the class member maps to a blob, you must declare the data type as an array of byte:
[Column('Document', [])]
FDocument: TArray<byte>;
or as the TBlob type (recommended):
[Column('Photo', [])]
FPhoto: TBlob;
In both examples above, Aurelius will check the field data type and create a blob field in the table to hold the content of the binary data. Each SQL dialect uses a different data type for holding the blobs. Aurelius will choose the most generic one, i.e, that can hold any data (binary) and the largest possible amount of data. If the blob field already exists in the database, Aurelius will just load the field content in binary format and set it in the property.
In theory, you could use the TBytes type as well (and any other type that is an array of byte), however Delphi doesn't provide RTTI type info for the TBytes specifically. It might be a bug or by design, but you just can't use it. Use TArray<byte> or any other dynamic byte array instead (or TBlob of course).
Using TBlob type you have more flexibility and features, as described in topics below.
Lazy-Loading Blobs
When declaring blob attributes in your class, you can configure them for lazy-loading. It means that whenever Aurelius tries to retrieve an object from the database, it will not include the blob field in the select, and thus the blob content will not be sent through network from server to client unless it's needed. If you access the blob content through the blob property, then Aurelius will execute an SQL statement on-the-fly only to retrieve the blob content.
To map the blob property/field as lazy, you just need two requirements:
Use the TBlob type as the field/property type.
Add TColumnProp.Lazy to the column properties in the Column attribute.
The code below indicates how to declare a lazy-loaded blob:
TTC_Customer = class
strict private
// <snip>
[Column('Photo', [TColumnProp.Lazy])]
FPhoto: TBlob;
The TBlob type is implicitly converted to an array of byte but also have methods for retrieving the blob content as TBytes, string, etc.. Whenever you try to access the blob data through the TBlob type, the blob content will be retrieved from the database.
TBlob Type
The TBlob type is used to declare blob field/properties. It's not required that you use a TBlob type, but doing so will allow you to configure lazy-loading blobs and also provides you with helper methods for handling the binary content.
Usage
TCustomer = class
private
[Column('Photo', [TColumnProp.Lazy])]
FPhoto: TBlob;
public
property Photo: TBlob read FPhoto write FPhoto;
Implicit conversion to TBytes
A TBlob implicitly converts to TBytes so you can directly use it in any method that uses it:
BytesStream := TBytesStream.Create(Customer1.Photo);
// Use BytesStream anywhere that needs a TStream
Explicitly using AsBytes property
Alternatively you can use AsBytes property to get or set the value of the blob:
// MyBytesContent is a TBytes variable
Customer1.Photo.AsBytes := MyBytesContent;
Use AsUnicodeString property to read/set the blob content as string
If you want to work with the blob content as string, you can just use AsUnicodeString property for that:
Customer1.Photo.AsUnicodeString := 'Set string directly to the blob';
If the underlying storage column is a memo, text or blob subtype text, Aurelius will make sure that the column will have the proper text value.
If it's a raw binary blob, the string will be saved using Unicode encoding.
You should also use AsUnicodeString for reading data from blobs. If the database blob has a memo value, the DB access component will use its default encoding/charset to read the text, and Aurelius will force the binary data to be kept in memory (in TBlob value) as Unicode encoding. Thus using AsUnicodeString will ensure you will read the correct string value.
For backward compatibility, you can use AsString property. That will read/save values using ANSI encoding. Unless you have a specific reason for using AsString, you should always use AsUnicodeString.
Raw access to the data using Data and Size properties
If you want to have directly access to data, for high performance operations, without having to copy a byte array or converting data to a string, you can use read-only properties Data and Size. Data is a pointer (PByte) to the first byte of the data, and Size contains the size of blob data.
The code below saves the blob content into a stream:
MyStream := TFileStream.Create('BlobContent.dat', fmCreate);
try
MyStream.Write(Customer1.Photo.Data^, Customer1.Photo.Size);
finally
MyStream.Free;
end;
Using streams to save/load the blob
You can also use TBlob.LoadFromStream and SaveToStream methods to directly load blob content from a stream, or save to a stream:
MyStream := TFileStream.Create('BlobContent.dat', fmCreate);
try
Customer1.Photo.LoadFromStream(MyStream);
Customer1.Photo.SaveToStream(AnotherStream);
finally
MyStream.Free;
end;
IsNull property
Use IsNull property to check if a blob is empty (no bytes):
if not Customer1.Photo.IsNull then
// Do something
Clearing the blob
You can clear the blob content (set blob content to zero bytes) by setting IsNull property to true, or by calling Clear method:
// Clear Photo and Description blobs content.
// Both statement are equivalent
Customer1.Photo.IsNull := true;
Customer1.Photo.Clear;
Loaded and Available properties
TBlob provides two boolean properties: Loaded and Available, and they refer to the status of data availability when blob content is configured to be lazy-loaded.
Available property allows you to check if blob content is available, without forcing the content to be loaded. If Available is true, it means that the blob content is already available in memory, even if it's empty. If it's false, it means the blob content is not available in memory and a request must be performed to load the content.
Loaded property behaves in a similar way. When Loaded is true, it means that the blob content of a lazy-loaded blob was already loaded from the database. If Loaded is false, it means the content was not loaded.
The difference between Loaded and Available is that when a new TBlob record is created, Available is true (because data is available - it's empty) and Loaded is false (because no content was loaded - because there is no content to load).
Associations and Lazy-Loading
Aurelius supports associations between objects, which are mapped to foreign keys in the database. Suppose you have the following TInvoice class:
TInvoice = class
private
FCustomer: TCustomer;
FInvoiceItems: TList<TInvoiceItem>;
The class TInvoice has an association to the class TCustomer. By using Association mapping attribute, you can define this association and Aurelius deals with it automatically - customer data will be saved in its own table, and in Invoice table only thing saved will be a value in a foreign key field, referencing the primary key in Customer table.
Also, TInvoice has a list of invoice items, which is also a type of association. You can define such lists using ManyValuedAssociation mapping attribute. In this case, the TInvoiceItem objects in the list will have a foreign key referencing the primary key in Invoice table.
Eager Loading
When an object is retrieved from the database, its properties are retrieved and set. This is also true for associations. By default, eager-loading is performed, which means associated objects and lists are loaded and filled when object is loaded. In the TInvoice example above, when a TInvoice instance is loaded, Aurelius also creates a TCustomer instance, fill its data and set it to the FCustomer field. Aurelius uses a single SQL statement to retrieve data for all associations. FInvoiceItems list is also loaded. In this case, an extra SELECT statement is performed to load the list.
Lazy Loading
You can optionally define associations to be lazy-loaded. This means that Aurelius will not retrieve association data from database until it's really needed (when the property is accessed). You define lazy-loading associations this way:
Declare the class field as a Proxy<TMyClass> type, instead of TMyClass (Proxy<T> type is declared in unit
Aurelius.Types.Proxy
).Declare the Association (or ManyValuedAssociation) attribute above the field, and define fetch mode as lazy in attribute parameters.
Declare a property of type TMyClass with getter and setter that read/write from/to the proxy value field.
Example:
TMediaFile = class
private
[Association([TAssociationProp.Lazy], [])]
[JoinColumn('ID_ALBUM', [])]
FAlbum: Proxy<TAlbum>;
function GetAlbum: TAlbum;
procedure SetAlbum(const Value: TAlbum);
public
property Album: TAlbum read GetAlbum write SetAlbum;
implementation
function TMediaFile.GetAlbum: TAlbum;
begin
Result := FAlbum.Value;
end;
procedure TMediaFile.SetAlbum(const Value: TAlbum);
begin
FAlbum.Value := Value;
end;
In the example above, Album will not be loaded when TMediaFile object is loaded. But if in Delphi code you do this:
TheAlbum := MyMediaFileObject.Album;
then Aurelius will perform an extra SELECT statement on the fly, instantiate a new TAlbum object and fill its data.
Lazy loading lists
Lists can be set as lazy as well, which means the list will only be filled when the list object is accessed. It works in a very similar way to lazy-loading in normal associations. The only difference is that since you might need an instance to the TList object to manipulate the collection, you must initialize it and then destroy it. Note that you should not access Value property directly when creating/destroying the list object. Use methods SetInitialValue and DestroyValue. The code below illustrates how to do that.
TInvoice = class
private
[ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll)]
[ForeignJoinColumn('INVOICE_ID', [TColumnProp.Required])]
FItems: Proxy<TList<TInvoiceItem>>;
private
function GetItems: TList<TInvoiceItem>;
public
constructor Create; virtual;
destructor Destroy; override;
property Items: TList<TInvoiceItem> read GetItems;
end;
implementation
constructor TInvoice.Create;
begin
FItems.SetInitialValue(TList<TInvoiceItem>.Create);
end;
destructor TInvoice.Destroy;
begin
FItems.DestroyValue;
inherited;
end;
function TInvoice.GetItems: TList<TInvoiceItem>;
begin
result := FItems.Value;
end;
Proxy<T> Available property
Available property allows you to check if proxy object is available, without forcing it be loaded. If Available is true, it means that the proxy object is already available in memory, even if it's empty. If it's false, it means the object is not available in memory and a request must be performed to load the content. In other words, Available property indicates if accessing the object will fire a new server request to retrieve the object.
Proxy<T> Key property
You can read Key property directly from the Proxy<T> value to get the database values for the foreign key used to load this proxy. This way you have access to the underlying database value without needing to force the proxy to load. Note that Key might not be always available - it will be filled by the object manager when the data is loaded from the database. If you set the proxy value manually, Key value might differ from the actualy id of the object in Proxy<T>.
Inheritance Strategies
There are currently two strategies for you to map class inheritance into the relational database:
Single Table: All classes in the hierarchy are mapped to a single table in the database.
Joined Tables: Each class is mapped to one different table, each one linked to the parent's table.
Inheritance is defined in Aurelius using the Inheritance attribute.
Single Table Strategy
With this strategy, all classes in the class hierarchy are mapped to a single table in relational database.
The concrete class of the object is indicated by the values in a special column in the table named discriminator column. This column is specified by the programmer and its content is used to identify the real class of the object. The discriminator column must be of string or integer type.
The advantage of this strategy is that the database is simple, and performance is optimized, since queries don't need to have too many joins or unions.
One disadvantage is that all columns belonging to child classes must be declared as not required, since they must be null if the row in the table corresponds to a super class.
Joined Tables Strategy
In this strategy there is one table for each class in the class hierarchy.
Each table represents a class in the hierarchy, and columns in the table are associated to the properties declared in the class itself. Even abstract classes have their own table, since they might have declared properties as well.
Tables are joined together using foreign keys. Each table representing a child class has a foreign key referencing the table representing the parent class. The foreign key is also the primary key, so the relationship cardinality between the tables is 1:1. In the previous illustration, the table Cricketer has a foreign key referencing the primary key in table Player.
The advantage of this strategy is that the database is normalized and the database model is very similar to the class model. Also, unlike the Single Table Strategy, all columns in tables are relevant to all table rows.
One disadvantage is performance. To retrieve a single object several inner or left joins might be required, becoming even worse when complex queries are used. Database refactoring is also more difficult - if you need to move a property to a different class in hierarchy, for example, more than one table needs to be updated.
Composite Id
You can use composite identifier in TMS Aurelius. Although possible, it's strongly recommended that you use single-attribute, single-column identifiers. The use of composite id should be used only for legacy applications where you already have a database schema that uses keys with multiple columns. Still in those cases you could try to add an auto-generated field in the table and use it as id.
Using composite Id's is straightforward: you just use the same attributes used for single Id: Id, JoinColumn, ForeignJoinColumn and PrimaryJoinColumn attributes. The only difference is that you add those attributes two or more times to the classes.
For example, the following TAppointment class has a composite Id using the attributes AppointmentDate and Patient (you can use associations as well):
[Entity]
[Table('PERSON')]
[Id('FLastName', TIdGenerator.None)]
[Id('FFistName', TIdGenerator.None)]
TPerson = class
strict private
[Column('LAST_NAME', [TColumnProp.Required], 50)]
FLastName: string;
[Column('FIRST_NAME', [TColumnProp.Required], 50)]
FFirstName: string;
public
property LastName: string read FLastName write FLastName;
property FirstName: string read FFiratName write FFiratName;
end;
[Entity]
[Table('APPOINTMENT')]
[Id('FAppointmentDate', TIdGenerator.None)]
[Id('FPatient', TIdGenerator.None)]
TAppointment = class
strict private
[Association([TAssociationProp.Lazy, TAssociationProp.Required],
[TCascadeType.Merge, TCascadeType.SaveUpdate])]
[JoinColumn('PATIENT_LASTNAME', [TColumnProp.Required])]
[JoinColumn('PATIENT_FIRSTNAME', [TColumnProp.Required])]
FPatient: Proxy<TPerson>;
[Column('APPOINTMENT_DATE', [TColumnProp.Required])]
FAppointmentDate: TDateTime;
function GetPatient: TPerson;
procedure SetPatient(const Value: TPerson);
public
property Patient: TPerson read GetPatient write SetPatient;
property AppointmentDate: TDateTime read FAppointmentDate write FAppointmentDate;
end;
Note that while TAppointment has a composite Id of two attributes, the number of underlying database table columns is three. This is because Patient attribute is part of Id, and the TPerson class itself has a composite Id. So primary key columns of table APPOINTMENT will be APPOINTMENT_DATE, PATIENT_LASTNAME and PATIENT_FIRSTNAME.
Also pay attention to the usage of JoinColumn attributes in field FPatient. Since TPerson has a composite Id, you must specify as many JoinColumn attributes as the number of table columns used for the referenced table. This is the same for ForeignJoinColumn and PrimaryJoinColumn attributes.
As illustrated in the previous example, you can have association attributes as part of a composite identifier. However, there is one limitation: you can't have lazy-loaded associations as part of the Id. All associations that are part of an Id are loaded in eager mode. In the previous example, although FPatient association was declared with TAssociationProp.Lazy, using a proxy, this settings will be ignored and the TPerson object will be fully loaded when a TAppointment object is loaded from the database.
When using composite Id, the generator specified in the Id attribute is ignored, and all are considered as TIdGenerator.None.
When using Id values for finding objects, for example when using Find method of object manager or using IdEq expression in a query, you are required to provide an Id value. The type of this value is Variant. For composite Id's, you must provide an array of variant (use VarArrayCreate method for that) where each item of the array refers to the value of a table column. For associations in Id's, you must provide a value for each id of association (in the example above, to find a class TAppointment you should provide a variant array of length = 3, with the values of appointment data, patient's last name and first name values).
Mapping Examples
This topic lists some code snippets that illustrates how to use attributes to build the object-relational mapping.
Basic Mapping
unit Artist;
interface
uses
Aurelius.Mapping.Attributes,
Aurelius.Types.Nullable;
type
[Entity]
[Table('ARTISTS')]
[Sequence('SEQ_ARTISTS')]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TArtist = class
private
[Column('ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.NoUpdate])]
FId: Integer;
FArtistName: string;
FGenre: Nullable<string>;
public
property Id: integer read FId;
[Column('ARTIST_NAME', [TColumnProp.Required], 100)]
property ArtistName: string read FArtistName write FArtistName;
[Column('GENRE', [], 100)]
property Genre: Nullable<string> read FGenre write FGenre;
end;
implementation
end.
Single-Table Inheritance and Associations
In the example below, TSong and TVideo inherit from TMediaFile. The TMediaFile class has two associations: Album and Artist. Both are lazy associations.
unit MediaFile;
interface
uses
Generics.Collections,
Artist, Album,
Aurelius.Mapping.Attributes,
Aurelius.Types.Nullable,
Aurelius.Types.Proxy;
type
[Entity]
[Table('MEDIA_FILES')]
[Sequence('SEQ_MEDIA_FILES')]
[Inheritance(TInheritanceStrategy.SingleTable)]
[DiscriminatorColumn('MEDIA_TYPE', TDiscriminatorType.dtString)]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TMediaFile = class
private
[Column('ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.DontUpdate])]
FId: Integer;
FMediaName: string;
FFileLocation: string;
FDuration: Nullable<integer>;
[Association([TAssociationProp.Lazy], [])]
[JoinColumn('ID_ALBUM', [])]
FAlbum: Proxy<TAlbum>;
[Association([TAssociationProp.Lazy], [])]
[JoinColumn('ID_ARTIST', [])]
FArtist: Proxy<TArtist>;
function GetAlbum: TAlbum;
function GetArtist: TArtist;
procedure SetAlbum(const Value: TAlbum);
procedure SetArtist(const Value: TArtist);
public
property Id: integer read FId;
[Column('MEDIA_NAME', [TColumnProp.Required], 100)]
property MediaName: string read FMediaName write FMediaName;
[Column('FILE_LOCATION', [], 300)]
property FileLocation: string read FFileLocation write FFileLocation;
[Column('DURATION', [])]
property Duration: Nullable<integer> read FDuration write FDuration;
property Album: TAlbum read GetAlbum write SetAlbum;
property Artist: TArtist read GetArtist write SetArtist;
end;
[Entity]
[DiscriminatorValue('SONG')]
TSong = class(TMediaFile)
private
FSongFormat: TSongFormat;
public
[Association]
[JoinColumn('ID_SONG_FORMAT', [])]
property SongFormat: TSongFormat read FSongFormat write FSongFormat;
end;
[Entity]
[DiscriminatorValue('VIDEO')]
TVideo = class(TMediaFile)
private
FVideoFormat: TVideoFormat;
public
[Association]
[JoinColumn('ID_VIDEO_FORMAT', [])]
property VideoFormat: TVideoFormat read FVideoFormat write FVideoFormat;
end;
implementation
{ TMediaFile }
function TMediaFile.GetAlbum: TAlbum;
begin
Result := FAlbum.Value;
end;
function TMediaFile.GetArtist: TArtist;
begin
Result := FArtist.Value;
end;
procedure TMediaFile.SetAlbum(const Value: TAlbum);
begin
FAlbum.Value := Value;
end;
procedure TMediaFile.SetArtist(const Value: TArtist);
begin
FArtist.Value := Value;
end;
end.
Joined-Tables Inheritance
In this example, TBird and TMammal classes inherit from TAnimal. Each class has its own table. Specific bird data is saved in "BIRD" table, and common animal data is saved in "ANIMAL" table.
unit Animals;
interface
uses
Generics.Collections,
Aurelius.Mapping.Attributes,
Aurelius.Types.Nullable,
Aurelius.Types.Proxy;
type
[Entity]
[Table('ANIMAL')]
[Sequence('SEQ_ANIMAL')]
[Inheritance(TInheritanceStrategy.JoinedTables)]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TAnimal = class
strict private
[Column('ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.DontUpdate])]
FId: Integer;
[Column('ANIMAL_NAME', [TColumnProp.Required], 50)]
FName: string;
public
property Id: Integer read FId write FId;
property Name: string read FName write FName;
end;
[Entity]
[Table('BIRD')]
[PrimaryJoinColumn('ANIMAL_ID')]
TBird = class(TAnimal)
strict private
[Column('CAN_FLY', [], 0)]
FCanFly: Nullable<boolean>;
[Column('BIRD_BREED', [], 50)]
FBirdBreed: Nullable<string>;
public
property CanFly: Nullable<boolean> read FCanFly write FCanFly;
property BirdBreed: Nullable<string> read FBirdBreed write FBirdBreed;
end;
[Entity]
[Table('MAMMAL')]
[PrimaryJoinColumn('ANIMAL_ID')]
TMammal = class(TAnimal)
strict private
[Column('LAST_PREGNANCY_DAYS', [], 0)]
FLastPregnancyDays: Nullable<integer>;
public
property LastPregnancyDays: Nullable<integer> read FLastPregnancyDays
write FLastPregnancyDays;
end;
implementation
end.
Registering Entity Classes
Aurelius doesn't require you to register the entity classes. Just by adding Entity attribute to the class it knows that the class is mapped and it will add it automatically to the default model or a model you have explicitly specified.
However, if you don't use the class anywhere in your application, the linker optimizer will remove it from the final application executable, and Aurelius will never know about it (since it retrieves information at runtime). There are situations where this can happen very often:
You have just started your application and wants Aurelius to create the database structure for you, but you still didn't use any of your classes. Aurelius will not create the tables since the classes just don't exist in executable.
You are creating a server application, especially using XData, without any specific server-side logic. You will notice that XData will respond to 404 (not found) to the URL resource addresses corresponding to your classes. This is just because XData server doesn't know about those classes.
To solve these kind of problems, all you would have to do is use the
class somewhere in your application. It could be a simple
"TMyClass.Create.Free". Nevertheless, to help you out in this task,
there is a function RegisterEntity in unit Aurelius.Mapping.Attributes
that you can use to make sure your class will be "touched" and thus
included in final executable.
So in the same unit you have your classes mapped you can optinally just call RegisterEntity in initialization section for all classes to make sure they will be present in application:
unit MyEntities;
uses {...}, Aurelius.Mapping.Attributes;
type
[Entity, Automapping]
TCustomer = class
private
FId: integer;
{...}
initialization
RegisterEntity(TCustomer);
RegisterEntity(TCountry);
RegisterEntity(TInvoice);
{...}
end.