Manipulating Objects
This chapter explains how to manipulate objects. Once you have properly connected to the database and configure all mapping between the objects and the database, it's time for the real action. The following topics explain how to save, update, delete and other topics about dealing with objects. Querying objects using complex criteria and projections is explained in a specific chapter only for queries.
Object Manager
The Object Manager is implemented by the TObjectManager class which is
declared in unit Aurelius.Engine.ObjectManager:
uses
{...}, Aurelius.Engine.ObjectManager;
It's the layer between your application and the database, providing methods for saving, loading, updating, querying objects. It performs memory management, by controlling objects lifetime cycle, destroying them when they are not needed anymore, and caching objects by using identity mappings to ensure a single object is not loaded twice in the same manager.
The Object Manager also keeps tracking of changes in objects - you can update the content of objects (change properties, add associations, etc.) and then call Flush method to ask the object manager to update all object changes in the database at once.
The list below is a quick reference for the main methods and properties provided by TObjectManager object. A separate topic is provided for each method listed below.
Creating a new object manager
Directly create a TObjectManager instance, passing the IDBConnection interface that represents a database connection:
Manager := TObjectManager.Create(MyConnection);
try
// perform operations with objects
finally
Manager.Free;
end;
Alternatively, you can also pass a
TMapping
Manager := TObjectManager.Create(MyConnection, MyMappingExplorer);
// or
Manager := TObjectManager.Create(MyConnection, TMappingExplorer.Get('MyModel'));
Save method
Use it to save (insert into database) new entity objects:
Customer := TCustomer.Create;
Customer.Name := 'TMS Software';
Manager.Save(Customer);
Update method
Use it to including a transient into the manager. To effectively persist updates, you need to call Flush method.
Customer := TCustomer.Create;
Customer.Id := 10;
Customer.Email := 'customer@company.com';
Manager.Update(Customer);
SaveOrUpdate method
Use it to save or update an object depending on the Id specified in the object (update if there is an Id, save it otherwise):
Customer.LastName := 'Smith';
Manager.SaveOrUpdate(Customer);
Flush method
Performs all changes made to the managed objects, usually to update objects.
Customer1 := Manager.Find<TCustomer>(1);
Customer2 := Manager.Find<TCustomer>(2);
Customer1.Email := 'company@address.com';
Customer2.City := 'Miami';
// Update Customer1 e-mail and Customer2 city in database
Manager.Flush;
Flush method for single entity
Commit to the database changes made to a single object - it's an overloaded version of Flush method that receives an object:
Customer1 := Manager.Find<TCustomer>(1);
Customer2 := Manager.Find<TCustomer>(2);
Customer1.Email := 'company@address.com';
Customer2.City := 'Miami';
// Update Customer1 e-mail - Customer2 changes are not persisted
Manager.Flush(Customer1);
Merge method
Use it to merge a transient object into the object manager and obtain the persistent object.
Customer := TCustomer.Create;
Customer.Id := 12;
Customer.Name := 'New name';
ManagedCustomer := Manager.Merge<TCustomer>(Customer);
In the example above, Merge will look in the cache or database for a TCustomer with id equals to 12. If it's not found, an exception is raised. If found, it will update the cached customer object with the new information and return a reference to the cached object in ManagedCustomer. Customer reference will still point to an unmanaged object, so two instances of TCustomer will be in memory.
Replicate method
The Replicate method behaves exactly the same as the merge method above. The only difference is that, in the example above, if no Customer with id 12 is found in the database, instead of raising an exception, Replicate will create the new customer with that id.
Find method
Use Find method to retrieve an object given its Id:
Customer := Manager.Find<TCustomer>(CustomerId);
The id value is a variant type and must contain a value of the same type of the class Identifier (specified with the Id attribute). For example, if the identifier is a string type, id value must be a variant containing a string. For classes with composite id, a variant array of variant must be specified with all the values of the id fields.
You can alternatively use the non-generic overload of Find method. It might be useful for runtime/dynamic operations where you don't know the object class at the compile time:
Customer := Manager.Find(TCustomer, CustomerId);
Remove method
Use it to remove the object from the persistence (i.e., delete it from database and from object manager cache).
CustomerToRemove := Manager.Find<TCustomer>(CustomerId);
Manager.Remove(CustomerToRemove);
Find<T> method
Use Find<T> to create a new query to find objects based on the specified criteria.
Results := Manager.Find<TTC_Customer>
.Where(Linq['Name'] = 'Mia Rosenbaum')
.List;
CreateCriteria<T> method
CreateCriteria is just an alias for Find<T> method. Both are equivalent:
Results := Manager.CreateCriteria<TTC_Customer>
.Where(Linq['Name'] = 'Mia Rosenbaum')
.List;
Evict method
Use to evict (dettach) an entity from the manager:
Manager.Evict(Customer);
IsAttached method
Checks if the specified object instance is already attached (persistent) in the object manager.
if not Manager.IsAttached(Customer) then
Manager.Update(Customer);
FindCached<T> method
Use FindCached method to retrieve an object from the manager's cache, given its Id.
Customer := Manager.FindCached<TCustomer>(CustomerId);
This method is similar to Find method but the difference is that if the object is not in manager cache, Aurelius will not hit the database to retrieve the objec - instead, it will return nil. Because of that, this method should be used only to check if the object is already in the manager - it's not useful to retrieve data from database.
You can alternatively use the non-generic overload of FindCached method. It might be useful for runtime/dynamic operations where you don't know the object class at the compile time:
Customer := Manager.FindCached(TCustomer, CustomerId);
IsCached<T> method
Checks if an object of the specified class with the specified id is already loaded in the object manager.
if not Manager.IsCached<TCustomer>(CustomerId) then
ShowMessage('Not loaded');
You can use the non-generic version as well:
if not Manager.IsCached(TCustomer, CustomerId) then
ShowMessage('Not loaded');
HasChanges method
Checks if a call to Flush will result in database operations. In other words, verifies if any object in the manager was modified since it was loaded from the database. HasChanges checks not only if properties were modified but also if any lists of the object has been modified (an item was added or removed).
Customer := Manager.Find<TCustomer>(1);
Customer.Email := 'company@address.com';
if Manager.HasChanges then
// code will enter here as Email was changed
HasChanges checks for any change in all objects in manager. You can use an overload that receives an object as parameter, to check if that specific entity was modified:
if Manager.HasChanges(Customer) then // only checks for changes in Customer
OwnsObjects property
If true (default), all managed objects are destroyed when the TObjectManager object is destroyed. If false, the objects remain in memory.
Customer := Manager.Find<TCustomer>(CustomerId);
Manager.OwnsObjects := false;
Manager.Free;
// Customer object is still available after Manager is destroyed
ProxyLoad and BlobLoad methods
Use to load a proxy object (or blob) based on meta information (see Lazy-Loading with JSON for more information).
function ProxyLoad(ProxyInfo: IProxyInfo): TObject;
function BlobLoad(BlobInfo: IBlobInfo): TArray<byte>;
UseTransactions property
When true, all internal operations peformed by the object manager (Save, Update, Merge, Remove, etc.) as enclosed between transactions (it means if no transaction is active, the manager will create one just for the operation, then later commit). This is needed because even a single manager operation can perform several SQL statements in the database (due to cascades for example).
If false, the manager won't create new transactions, and it's up to you to make sure that a transaction is active, otherwise if the internal process fails, some records might become updated in the database, while others don't.
The default value of this property is controlled globally by the
TGlobal
DeferDestruction property
When true, all manager operations that destroy objects, such as Remove, will not immediately destroy them, but instead hold them in an internal list to be destroyed when the object manager is destroyed.
This can be useful if you still have references to the removed object in places like lists or datasets, and such references might still be used until manager is destroyed. This sometimes avoids invalid pointer operations and access violations caused by referencing those destroyed instances.
For backward compatibility, default value is false.
TAureliusManager Component
The TAureliusManager component is a non-visual, design-time component that encapsulates the TObjectManager class, used to persist and retrieve objects in database.
TAureliusManager and TObjectManager have equivalent functionality, the
main purpose for TAureliusManager component is to provide an alternative
RAD approach: instead of instantiating a TObjectManager from code, you
just drop a TAureliusManager component, connects it to a
TAurelius
Key properties
| Name | Description |
|---|---|
| Connection: TAureliusConnection | Specifies the TAurelius 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 TObjectManager constructor to create the instance that will be encapsulated. |
| ModelName: string | The name of the model to be used by the manager. You can leave it blank, if you do it will use the default model. From the model name it will get the property TMapping |
| ObjManager: TObjectManager | The encapsulated TObjectManager instance used to perform the database operations. |
Usage
As mentioned, TAureliusManager just encapsulates a TObjectManager instance. So for all functionality (methods, properties), just refer to TObjectManager documentation and related topics that explain how to save objects, update them, retrieve, querying, etc.
The encapsulated object is available in property ObjManager. If you miss any specific method or property in TAureliusManager, you can simply fall back to ObjManager instance and use it from there. For example, the following methods are equivalent:
AureliusManager1.Save(Customer);
AureliusManager1.ObjManager.Save(Customer);
Actually effectively, the first method is just a wrapper for the second one. Here is how TAureliusManager.Save method is implemented, for example:
procedure TAureliusManager.Save(Entity: TObject);
begin
ObjManager.Save(Entity);
end;
TObjectManager memory management
All entities are managed inside the TObjectManager instance, using the regular memory management mechanism of such class. The only thing you should be aware is the lifecycle of the TObjectManager instance itself:
The TObjectManager instance will be created on demand, i.e., when TAureliusManager is created, the TObjectManager is not yet created. It will only be instantiated when needed;
If the connection or model name is changed, the encapsulated TObjectManager instance will be destroyed (and as a consequence all entities managed by it). A new TObjectManager instance will be created with the new connection/model, when needed.
Memory Management
Entity objects are saved and loaded to/from database using a TObjectManager object, which provides methods and properties for such operations. All entity objects cached in TObjectManager are managed by it, and you don't need to free such objects (unless you set OwnsObjects property to False). Also, entity objects retrieved from database, either loading by identifier or using queries, are also managed by the TObjectManager.
Concept of object state
In Aurelius when an object is told to be persistent (or cached, or managed) it means that the TObjectManager object is aware of that object and is "managing" it. When TObjectManager loads any object from the database, the object instances created in the loading process are persistent. You can also turn objects into persistent object when you for example call Save, Update or Merge methods.
When the TObjectManager is not aware of the object, the object is told to be transient (or uncached, or unmanaged).
Don't confuse a transient object with an object that is not saved into the database yet. You might have a TCustomer object which has been already saved in the database, but if the TCustomer instance you have is not being managed by the TObjectManager, it's transient.
Also, don't confuse persistent with saved. A persistent object means that TObjectManager is aware of it and it's managing it, but it might not be saved to the database yet.
Object lists
It's important to note that when retrieving object lists from queries, the list itself must be destroyed, although the objects in it are not. Note that when you use projections in queries, the objects returned are not entity objects, but result objects. In this case the objects are not managed by the object manager, but the list retrieved in result queries have their OwnsObjects set to true, so destroying the list will destroy the objects as well.
Unique instances
When dealing with entity objects (saving, loading, querying, etc.), object manager keeps an internal Identity Map to ensure that only one instance of each entity is loaded in the TObjectManager object. Each entity is identified by it's Id attribute. So for example, if you execute two different queries using the same object manager, and the query returns the same entity (same id) in the queries, the object instance in the both queries returned will be the same. The object manager will not create a different object instance every time you query the object. If you use a different TObjectManager object for each query, then you will have different instances of the same entity object
Manually adding ownership
The manager owns all entity objects it manages, in other words, which are persisted. But sometimes you want to tell the manager to own an object even before a persistence operation, using the AddOwnership method. This is very common when using the Save method. The object passed to Save will only be owned by the manager if the operation is successful. If the operation fails, you would have to manually destroy it.
To improve this workflow, you can explicitly tell the manager to own the entity to be saved in advance, so regardless if the Save method fails, or not, the manager will always destroy the object:
Customer := TTC_Customer.Create;
Customer.Name := 'Customer Name';
// Tell the manager to destroy Customer
ObjectManager1.AddOwnership(Customer);
// Customer will always be destroyed even
// if Save fails
ObjectManager1.Save(Customer);
Examples
The code snippets below illustrates several the different situations mentioned above.
Saving objects
Customer := TTC_Customer.Create;
Customer.Name := 'Customer Name';
ObjectManager1.Save(Customer);
// From now on, you don't need to destroy Customer object anymore
// It will be destroyed when ObjectManager1 is destroyed
Loading objects
Customer := Manager1.Find<TCustomer>(CustomerId);
Customer2 := Manager1.Find<TCustomer>(CustomerId);
// Since CustomerId is the same for both queries, the same instance will be
// returned in Customer and Customer2 (Customer = Customer2), and you don't
// need to destroy such instance, it's manager by Manager1.
Retrieving entities from queries
Results := Manager.Find<TCustomer>
.Add(Linq['Name'] = 'TMS Software')
.List;
Results.Free;
// Results is a TObjectList<TCustomer> object that needs to be destroyed
// However, the object instances it holds are not destroyed and are kept
// in Manager cache. The instances are also ensured to be unique in Manager context
Retrieving projected query results
Results := Manager.Find<TTC_Estimate>
.CreateAlias('Customer', 'c')
.Select(TProjections.ProjectionList
.Add(TProjections.Sum('EstimateNo'))
.Add(TProjections.Group('c.Name'))
)
.ListValues;
Results.Free;
// In this case the query does not return entity objects, but result objects (TCriteriaResult)
// Such result objects are not managed by TObjectManager. However, in this case,
// The Results object list is returned with its OwnsObjects property set to true. Thus, when
// you destroy Results object, the TCriteriaResult objects it holds will also be destroyed.
Using unmanaged objects
If for some reason you want to keep object instances available even after the object manager is destroyed (for example, after a query, you want to destroy object manager but keep the returned objects in memory), then just set the TObjectManager.OwnsObjects property to false:
Manager.OwnsObjects := false;
Results := Manager.Find<TCustomer>
.Where(Linq['Name'] = 'TMS Software')
.List;
Manager.Free;
// Now although Manager object was destroyed, all objects in Results list
// will be kept in memory, EVEN if you destroy Results list itself later.
Saving Objects
Using TObjectManager you can save (insert) objects using Save method. It is analog to SQL INSERT statement - it saves the object in database.
Customer1 := TCustomer.Create;
Customer1.Name := 'John Smith';
Customer1.Sex := tsMale;
Customer1.Birthday := EncodeDate(1986, 1, 1);
Manager1.Save(Customer1);
The identifier of the object (mapped using Id attribute) must not have a value, otherwise an exception will be raised - unless the generator defined in Id attribute is TIdGenerator.None. In this case, you must manually provide the id value of the object, and so of course Aurelius will accept an object with an id value. But you must be sure that there are no objects in the database with the same id value, to avoid duplicate values in the primary key.
When saving an object, associations and items in collections might be
saved as well, depending on how cascade options are set when you defined
the Association and
Many
Customer := TTC_Customer.Create;
Customer.Name := 'Customer Name';
Invoice := TTC_Invoice.Create;
Invoice.InvoiceType := 999;
Invoice.InvoiceNo := 123456;
Invoice.Customer := Customer;
Invoice.IssueDate := Date;
Manager1.Save(Invoice);
You can also use SaveOrUpdate method to save objects. The difference from Save is that if the object has an id value set, SaveOrUpdate will internally call Update method instead of Save method. So, if you use TIdGenerator.None in the Id attribute of your object class, SaveOrUpdate will not work.
Updating Objects - Flush
You modify objects using the TObjectManager method Flush. The state of all objects persisted in object manager is tracked by it. Thus, if you change any property of any object after it's loaded by the database, those changes will be updated to the database when Flush method is called. Consider the example below:
Customer1 := Manager1.Find<TCustomer>(CustomerId);
Customer1.Email := 'newemail@domain.com';
Customer2 := Manager1.Find<TCustomer>(Customer2Id);
Customer2.Email := 'another@email.com';
Manager1.Flush;
The Flush method will detect all objects which content has been changed since they were loaded, and then update them all in the database. In the example above, both customers 1 and 2 will have their e-mail changed.
It's possible that, by any reason, you want to update a detached object, in other words, an object that is not being tracked (persisted) by the manager. This might happen, for example, if you loaded an object with the manager, then destroyed the manager but kept the object reference (using TObjectManager.OwnsObjects = false). Or, for example, if you created the object instance yourself, and set its id property to a valid value. In this case the object is not in the manager, but you want to update the database using the object you have.
In this case, you can use Update method. This method will just take the passed transient instance and attach it to the TObjectManager. Then when you later call Flush, the changes will be persisted to the database. Note that when you call Update, no data is retrieved from the database. This means that the object manager doesn't know the original state of the object (data saved in database). The consequence is that all properties of the object passed to Update method will later be saved to the database when Flush is called. So you must be sure that all the persistent properties of the object have the correct value to be saved to the database.
Customer2 := Manager1.Find<TCustomer>(Customer2Id);
Manager1.OwnsObjects := false;
Manager1.Free;
Customer2.Name := 'Mary';
Customer2.Sex := tsFemale;
Manager2.Update(Customer2);
Manager2.Flush;
In the example above, a TCustomer object was loaded in Manager1. It's not attached to Manager2. When Update method is called in Manager2, all data in Customer2 object will be updated to the database, and it will become persistent in Manager2.
The cascades defined in Association attributes in your class are applied here. Any associated object or collection item that has TCascadeType.SaveUpdate defined will also be updated in database.
Merging
If you call Update method passing, say, Object1, but there was already another object attached to the TObjectManager with the same id (Object2), an exception will be raised. In this case, you can use Merge method to merge a transient object ("outside" the manager) into a persistent object ("inside" the manager).
Flushing a single object
Calling Flush might be slow if you have many entities in the manager. Flush will iterate through all entities and check if any of them is modified - and persist changes to the database. Alternatively, you can flush a single entity by using an overloaded version of Flush that receives a single object:
Customer1 := Manager1.Find<TCustomer>(CustomerId);
Customer1.Email := 'newemail@domain.com';
Customer2 := Manager1.Find<TCustomer>(Customer2Id);
Customer2.Email := 'another@email.com';
Manager1.Flush(Customer1);
In the example above, only changes made to Customer1 will be persisted. Customer2 changes will still be in memory only, and you would have to call Flush or Flush(Customer2) to persist the changes. This gives you finer control over what should be persisted and helps you increase performance of your code.
You must be careful, though, about associated objects. When you call Flush without specifying an object you are safe that all changes in the manager are persisted. You flushing a single object, associated objects might be flushed or not, depending on how the cascade options are set for that Association (or Many-Valued Association). If the association includes the TCascadeType.Flush, then it will also be flushed.
Merging/Replicating Objects
When you use Update method in a TObjectManager object, there should be no managed object with same Id in the object manager, otherwise an exception is raised. You can avoid such exception using the Merge or Replicate methods. These methods behave almost exactly the same, and will take a transient instance and merge it into the persistent instance. In other words, all the content of the transient object will be copied to the persistent object. Note that the transient object will continue to be transient.
If there is no persistent object in the object manager with the same id, the object manager will load an object from the database with the same id of the transient object being merged.
If the object has an id and no object is found in the database with that id, the behavior depends on the method called (and that is the only difference between Merge and Replicate methods):
if Merge method was called, an exception will be raised;
if Replicate method was called, a new object with the specified id will be saved (inserted).
Customer2 := TCustomer.Create;
Customer2.Id := Customer2Id;
Customer2.Name := 'Mary';
Customer2.Sex := tsFemale;
MergedCustomer := Manager2.Merge<TCustomer>(Customer2);
Manager2.Flush;
In the example above, a TCustomer object was created and assigned an existing id. When calling Merge method, all data in Customer2 will be copied to the persistent object with same id in Manager2. If no persistent object exists in memory, it will be loaded from the database. Customer2 variable will still reference a transient object. The result value of Merge/Replicate method is a reference to the persistent object in the object manager.
If the transient object passed to Merge/Replicate has no id, then a Save operation takes place. Merge/Replicate will create a new internal instance of object, copy all the contents from the passed object to the internal one, and Save (insert) the newly created object. Again, the object returned by Merge/Replicate is different from the one passed. Take a look at the following example:
NewCustomer := TCustomer.Create;
NewCustomer.Name := 'John';
MergedCustomer := Manager2.Replicate<TCustomer>(NewCustomer);
// MergedCustomer <> NewCustomer! NewCustomer must be destroyed
In the example above, NewCustomer doesn't have an id. In this case, Merge/Replicate will create a new customer in database, and return the newly created object. MergedCustomer points to a different instance than NewCustomer. MergedCustomer is the persistent one that is tracked by the object manager (and will be destroyed by it when manager is destroyed). NewCustomer continues to be a transient instance and must be manually destroyed.
Note that Merge/Replicate does nothing in the database in update operations - it just updates the persistent object in memory. To effectively update the object in the database you should then call Flush method. The only exception is the one described above when the object has no id, or when Replicate saves a new object with existing id. In those cases, a Save (insert) operation is performed immediately in the database.
The cascades defined in Association and
Many
Removing Objects
You can remove an object from the database using Remove method from a TObjectManager object. Just pass the object that you want to remove. The object must be attached to the object manager.
Customer1 := Manager1.Find<TCustomer>(CustomerId);
Manager1.Remove(Customer1);
The cascades defined in Association and
Many
The object passed to Remove method will eventually be destroyed. If TObjectManager.DeferDestruction property is false (default), the object will be destroyed immediately. If it's true, object will be destroyed when the manager is destroyed.
Finding Objects
You can quickly find (load) objects using Find method of TObjectManager. You just need to pass the Id of the object, and object manager will retrieve the instance of the object loaded in memory. If the object is not attached to the object manager (not in memory), then it tries to load the object from database. If there is no object (record) in the database with that Id, it returns nil.
Customer1 := Manager1.Find<TCustomer>(CustomerId);
// Customer1 has an instance to the loaded customer object.
The associations will be loaded depending on how the fetch mode was defined in Association attribute. They will be loaded on the fly or on demand, depending if they are set as lazy-loaded associations or not.
If you want to retrieve several objects of a class from the database using some criteria (filtering, ordering), just use Find without parameter, it will return a Criteria object which you can use to add filters, ordering and later retrieve the results:
var
Customers: TList<TCustomer>;
begin
Customers := Manager1.Find<TCustomer>.List;
// Take just the first 10 customers ordered by name
Customers := Manager1.Find<TCustomer>.Take(10).OrderBy('Name').List;
Aurelius is very powerful on querying capabilities. There is a full chapter explaining how to perform Aurelius queries.
Refreshing Objects
You can refresh an object using Refresh method from a TObjectManager object. Just pass the object that you want to refresh. The object must be attached to the object manager.
Manager1.Refresh(Customer1);
Refresh method performs operates almost the same way as Find method. The main difference is that Find method only create new instances that don't exist in the manager and if the instance already exists, it's left untouched. Refresh method, instead, will perform the SELECT statement in the database no matter what, and if the instances already exist in manager, it will update its properties and associations with data retrieved from the database, discarding the existing values in memory, if different.
Note existing transient associations will NOT be destroyed. For example, consider the following code:
Customer1 := Manager.Find<TCustomer>(1);
NewCountry := TCountry.Create;
Customer1.Country := NewCountry;
Manager.Refresh(Customer1);
In the code above a TCustomer instance is loaded from the database, and its Country property is updated to point to a transient TCountry reference. When Refresh method is called, Customer1 properties will be reloaded from the database, and thus Country property will point again to the original TCountry instance in the manager (or nil if there is no country associated with he customer). However, the instance referenced by NewCountry will not be destroyed. It's up to you to destroy the transient instances unreferenced by Refresh method.
The cascades defined in Association and
Many
Evicting Objects
In some situations you want to remove (dettach) an object from the TObjectManager, but without deleting it from database (so you can't use Remove method) and without destroying the instance.
To do that, you can use Evict method. Just pass the object that you want to evict. If the object is not attached to the manager, no operation is performed.
Manager1.Evict(Customer1);
The cascades defined in Association and
Many
Note that since the object is not in the manager anymore, you must be sure to destroy it (unless of course you attach it to another manager using for example Update method). Also pay attention to associated objects. If TCascadeType.Evict is defined for associated objects, they will also be evicted and must be destroyed as well.
Transaction Usage
You can use transactions when manipulating objects, so that you make sure all operations under the transaction are performed successfully (commit) or anything is reverted (rollback). Usage is simple and is done pretty much the same way you would do when accessing a database in a traditional way.
The transactions are started under the IDBConnection interface context. You can start a transaction using IDBConnection.BeginTransaction method, which will return a IDBTransaction interface. The IDBTransaction in turn has only two methods: Commit and Rollback.
uses {...}, Aurelius.Drivers.Interfaces;
var
Transaction: IDBTransaction;
begin
Transaction := Manager.Connection.BeginTransaction;
try
{ Perform manager operations }
Transaction.Commit;
except
Transaction.Rollback;
raise;
end;
end;
Transactions in Aurelius can be nested. This means that if a transaction was already started in IDBConnection but not commited or rolled back yet, creating a new transaction and commiting or rolling it back has no effect. For example:
OuterTransaction := Manager.Connection.BeginTransaction;
InnerTransaction := Manager.Connection.BeginTransaction;
InnerTransaction.Commit; // This has NO effect, the same for rollback.
OuterTransaction.Commit; // Commit (or Rollback) is effectively performed here
Concurrency Control
When working with multiple users/clients, it might be possible that two or more users try to change the same entity (records in database). TMS Aurelius provides some mechanisms to avoid problems in those situations.
Changed fields
When updating objects, Aurelius detects which property have changed since the entity was loaded from the database in the manager, and it only updates those columns in the database. For example, suppose two users load the same TCustomer (with same id) from the database at the same time:
// User1
User1Customer := Manager1.Find<TCustomer>(1);
// User2
User2Customer := Manager2.Find<TCustomer>(1);
Now first user changes customer's city and update, and second user changes customer's document and update:
// User1
User1Customer.City := 'New City';
Manager1.Flush;
// User2
User2Customer.Document := '012345';
Manager2.Flush;
Here are the SQL executed by Aurelius for each user (SQL were simplified for better understanding, the actual SQL uses parameters):
-- User1:
Update Customer
Set City = 'New City'
Where Id = 1
-- User2:
Update Customer
Set Document = '012345'
Where Id = 1
Even if TCustomer class has lots of customer, and some properties might be outdated in memory, it doesn't cause any trouble or data loss here, because only changed data will be commited to the database. In the end, the TCustomer object in database will have both the new city and new document correct.
This is a basic mechanism that solves concurrency problems in many cases. If it's not enough, you can use entity versioning.
Entity Versioning
It might be possible that two users change the exactly same property, in this case, one of the users will "lose" their changes, because it will be overwritten by the other user. Or some other types of operations are performed where all fields are updated (when entity is put in manager without being loaded from database for example, so the manager can't tell which properties were changed).
Or maybe you just need to be sure that the object being updated needs to hold the very latest data. A typical case is where you are updating account balance or inventory records, so you increment/decrement values and need to ensure that no other user changed that data since you loaded.
In this case, you can use entity versioning. To accomplish this, you just need to create an extra integer property in the class, map it (so it's persisted in database) and add the [Version] attribute to it:
[Entity, Automapping]
TCustomer = class
private
FId: Integer;
FName: String;
{...}
[Version]
FVersion: Integer;
{...}
end;
And that's it. Once you do this, Aurelius will make sure that if you update (or delete) an entity, data it holds is the very latest one. If it's not, because for example another user changed the database record in the meanwhile, an exception will be raised and then you can decide what to do (refresh the object for example).
Let's take a look at how it works. First, two users load the same object at the same time:
// User1
User1Customer := Manager1.Find<TCustomer>(1);
// User1Customer.Version is 1
// User2
User2Customer := Manager2.Find<TCustomer>(1);
// User2Customer.Version is 1
Then User1 updates customer:
User1Customer.City := 'New City';
User1Customer.Flush;
// User1Customer.Version becomes 2 (also in database)
This is the SQL executed by Aurelius:
Update Customer
Set City = 'New City', Version = 2
Where Id = 1 and Version = 1
Record is changed successfully because the current version in database is 1, so the actual record is updated.
Now, if User2 tries to update the old customer:
// User2Customer.Version is still 1!
User2Customer.City := 'Another city';
User2Customer.Flush;
Aurelius tries to execute the same SQL:
Update Customer
Set City = 'Another City', Version = 2
Where Id = 1 and Version = 1
However this will fail, because the version in the database is not 1 anymore, but 2. Aurelius will detect that no records were affected, and will raise an EVersionedConcurrencyControl exception.
Cached Updates
When persisting objects by calling object manager methods like Save, Flush or Remove, the manager will immediately perform SQL statements to reflect such changes in the database. For example, calling this:
Manager.Remove(Customer);
Will immediately execute the following SQL statement in the database:
DELETE FROM CUSTOMERS WHERE ID = :id
With cached updates feature, you have the option to defer the execution of all SQL statements to a later time, when you call ApplyUpdates method. This is enabled by setting CachedUpdates to true. Take the following code as an example:
Manager.CachedUpdates := True;
Manager.Save(Customer);
Invoice.Status := isPaid;
Manager.Flush(Invoice);
WrongCity := Manager.Find<TCity>(5);
Manager.Remove(City);
Manager.ApplyUpdates;
This code should perform the following SQL statements:
- INSERT (to save the customer - see "exceptions" below);
- UPDATE (to modify the status field of the invoice);
- DELETE (to delete the city).
However, all the statements will be executed one after another, when ApplyUpdates is called.
Exceptions
There are exceptions for this rule. When the id value of an entity is generated by the database during an INSERT statement, then the INSERT SQL statement will be executed immediately. So for example, the statement below:
Manager.Save(Customer);
Might be executed immediately, even when CachedUpdates property is true,
if Customer is set to be of type Identity
Note that this doesn't apply if the database uses SEQUENCE. In other words, even if the id is of type IdentityOrSequence, if the id is generated by reading the SEQUENCE value, then the SQL statement used to retrieve the SEQUENCE value will be executed, but the INSERT statement will be deferred until ApplyUpdates is called.
Cached actions
You can read the CachedCount property to know how many actions are pending and will be applied in the next call to CachedUpdates.
TotalPendingActions := Manager.CachedCount;
Batch (Bulk) Updates
Aurelius allows you to modify data in batches. For example, suppose you want to update one hundred customer records, modifying their phone number. With batch updates, a single SQL statement will be executed to modify all records at once. This improves performance significantly in such operations.
Batch updates works together with cached updates. You need to use cached updates mechanism, then just use TObjectManager.BatchSize property to tell Aurelius the maximum number of records that can be updated using a single SQL statement.
Consider the following code:
// Retrieve some customers from database
CustomerA := Manager.Find<TCustomer>(1);
CustomerB := Manager.Find<TCustomer>(2);
CustomerC := Manager.Find<TCustomer>(3);
// Set batch size and enable cache updates
Manager.BatchSize := 100;
Manager.CachedUpdates := True;
// Modify customer A, B and C
CustomerA.City := 'New York';
Manager.Flush(CustomerA);
CustomerB.City := 'Berlin';
Manager.Flush(CustomerB);
CustomerC.City := 'London';
Manager.Flush(CustomerC);
// Now apply updates
Manager.ApplyUpdates;
Aurelius will execute a single SQL statement to modify the three customers. The SQL statement will be something like this:
UPDATE Customers SET Name = :p1 WHERE Id = :p2
And the parameter values for all records ("New York" and 1, "Berlin" and 2, "London" and 3) will be sent at once together with the SQL statement.
Batch algorithm
Aurelius will perform the batch automatically. You can perform any actions you want. All actions will be cached, and if the actions build SQL statement that are the same, they will be grouped together in a batch, up until the size specified by BatchSize.
Thus, the order of actions is important. For example, if you perform:
- Insert CustomerA
- Modify CustomerA City
- Insert CustomerB
- Modify CustomerB City
No batches will be created and a SQL statement will be executed for each operation. However, if you perform the actions this way:
- Insert CustomerA
- Insert CustomerB
- Modify CustomerA City
- Modify CustomerB City
Then two batches of size 2 will be created. A single SQL statement will be executed to insert the two customers, and another SQL statement will be executed to modify the two customers.
Also note that the batch will be performed if all SQL statements are the same. For example, if you modify Customer A city, and then modify Customer B name, no batch will be created. Even though both are update actions, the first will result in a SQL statement that modifies city field, and the other will result in a SQL statement that modifies name field.
Driver-dependent behavior
The way the statement is executed depends on the underlying driver and RDBMS being used. Some drivers support the batch mechanism where all parameters are sent at once. Other drivers do not support it, in this case Aurelius will simulate such batch by executing several SQL statements using a pre-prepared one. Still it will be faster than executing a single statement for every record.
The current drivers that support real batch updates (array DML) are:
- Native Aurelius connectivity;
- FireDAC;
- UniDAC (except when connecting to Firedac, Interbase and NexusDB).
For all other drivers, the batch will be simulated. Note that you don't need to modify anything in your code, regardless of the driver and the database you are using. Aurelius will work transparently in all cases, and use the best mechanism available to perform the batch updates.