The CallCentre application is in a state where it provides the necessary GUI interaction to allow us to explore Aurelius’ features. In this chapter, we investigate how we can manage basic database operations and, near the end, we deal with managing a big number of transactions. The initial project to start with is in the CallCentre – Without Utilities folder.
Adding Entities
- 1.
Go to MainForm.pas, in the OnClick event of btAddDepartment button.
- 2.Add the following code:uses...,Database.Session.Types,Database.Session,Entities;...procedure TFormMain.btAddDepartmentClick(Sender: TObject);var...session: IDatabaseSession;department: TDepartment;begin...if form.ShowModal = mrOk thenbeginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);department:=TDepartment.Create;department.Description:=Trim(form.edEntity.Text);trysession.objectManager.Save(department);exceptif not session.objectManager.IsAttached(department) thendepartment.Free;end;// Here we need to update the list of departments in the formend;...end;
- 3.Then, switch to the OnClick event of btAddAgent button and add similar code to manage TAgent.procedure TFormMain.btAddAgentClick(Sender: TObject);var...session: IDatabaseSession;agent: TAgent;begin...if form.ShowModal = mrOk thenbeginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=TAgent.Create;agent.Description:=Trim(form.edEntity.Text);trysession.objectManager.Save(agent);exceptif not session.objectManager.IsAttached(agent) thenagent.Free;end;// Here we need to update the list of agents in the formend;...end;
We declare two variables: one for the database session (IDatabaseSession) and one for the relevant entity (TDepartment/TAgent). Remember, we use the IDatabaseSession interface to get access to the object manager. We pass an IDBConnection to the database session constructor as retrieved by the connection module, and we normally create an instance of the entity.
Then, we call Save, a method provided by the object manager to convert our object to a persistent one in the database. Initially, when we create a new instance of an entity, the ID field that represents the primary key in the database has the default value (0 if it is an integer or an empty GUID in our case).
Once the object is saved, this field is populated with the actual primary key. Additionally, the object manager is now aware of the existence of this entity in both the physical and virtual databases, and it is able to manage the lifetime of the object by making sure that the instance is eventually freed. Now, it works as the owner of the object.
However, in the case where something unpredictable happens while the object manager is attempting to save the entity (e.g., bad I/O operation, damaged network, or physical medium), an exception is generated. At this stage, the object manager might not have the chance to get ownership of the object depending on when the exception occurs. Consequently, the object may not be freed by the object manager. We need to do this in code. The code in the except part of the try-except structure demonstrates exactly this step; using IsAttached, we check if the object manager owns the object. If not, we free it manually.
Listing Entities
- 1.
In MainForm.pas, add two new procedures in the private section of TFormMain named updateDepartments and updateAgents.
- 2.Add the following code in the method:type...TFormMain = class(TForm)...private...procedure updateDepartments;procedure updateAgents;public...end;...procedure TFormMain.updateDepartments;varsession: IDatabaseSession;departmentList: TList<TDepartment>;department: TDepartment;beginsgDepartments.RowCount:=0;sgDepartmentDetails.RowCount:=0;session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);departmentList:=session.ObjectManager.Find<TDepartment>.OrderBy('Description').List;// A safer approach is to enclose the following lines in// BeginUpdate/EndUpdate and try/finally blocks// But we will keep things simple herefor department in departmentList dobeginsgDepartments.RowCount := sgDepartments.RowCount + 1;sgDepartments.Cells[0, sgDepartments.RowCount - 1]:= sgDepartments.RowCount.ToString;sgDepartments.Cells[1, sgDepartments.RowCount - 1]:= department.Description;sgDepartments.Cells[2, sgDepartments.RowCount - 1]:=GUIDToString(department.ID);end;departmentList.Free;btEditDepartment.Enabled:= sgDepartments.Selected>-1;btDeleteDepartment.Enabled:= sgDepartments.Selected>-1;lbDepartmentDetailsNoEntries.Visible:=sgDepartmentDetails.RowCount = 0;end;...procedure TFormMain.updateAgents;varsession: IDatabaseSession;agentList: TList<TAgent>;agent: TAgent;beginsgAgents.RowCount:=0;sgAgentDetails.RowCount:=0;session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agentList:=session.ObjectManager.Find<TAgent>.OrderBy('Description').List;for agent in agentList dobeginsgAgents.RowCount := sgAgents.RowCount + 1;sgAgents.Cells[0, sgAgents.RowCount - 1]:= sgAgents.RowCount.ToString;sgAgents.Cells[1, sgAgents.RowCount - 1]:= agent.Description;sgAgents.Cells[2, sgAgents.RowCount - 1]:= GUIDToString(agent.ID);end;agentList.Free;btEditAgent.Enabled:= sgAgents.Selected>-1;btDeleteAgent.Enabled:= sgAgents.Selected>-1;lbAgentDetailsNoEntries.Visible:=sgAgentDetails.RowCount = 0;end;
In the code, we retrieve the list of entities without any filtering. For this, we need again an instance of the object manager, which we create as we have done before. We then use the Find<T> method to retrieve the entities and we order them by Description. As you can see in the code snippet, Aurelius’ fluent interface allows us to concatenate methods (actions) in a way that generates a very readable line of code. Eventually, the object manager returns a list object of the entities.
It is worth mentioning that the object manager is able to manage the lifetime of the entities in the list. The list itself (departmentList/agentList) needs to be freed explicitly. We are able to access the entities in the list by simply iterating through the list the usual way (for loop), and we access the properties of the entities directly as we declared them non-null in the class. As we will see later on, nullable properties are treated slightly differently.
The approach we used is very typical. We created the list, iterated through the items, and destroyed it. Aurelius offers an alternative and more convenient way to achieve the same result by implementing database cursors. Cursors are interfaced objects; therefore, the need to manually free is not present. Moreover, we do not need to declare the agentList at all....for agent in .Find<TAgent>.OrderBy('Description').Open dobegin// Populate the gridend;... - 3.We now need to use the preceding methods in the appropriate places (when the user adds a new department or agents and when the user changes the tab to the departments and agents)....procedure TFormMain.btAddDepartmentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbegin...try...except...end;updateDepartments;end;...end;procedure TFormMain.btAddAgentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbegin...try...except...end;updateAgents;end;...end;procedure TFormMain.TabControl1Change(Sender: TObject);begin...if TabControl1.ActiveTab = tiAgents thenupdateAgents;if TabControl1.ActiveTab = tiDepartments thenupdateDepartments;end;
Editing (Updating) Entities
To retrieve the entity in the object manager
To make changes to the properties of the entity
To update the entity in the database
Go to the OnClick events of btEditDepartment and btEditAgent buttons and add the following code:
We use the object manager’s Find to retrieve the entity from the database in the same way we used it when we retrieved the full list. The difference now is that we need only one specific entity. Thus, we supply the ID (primary key) with the call to the Find method. Naturally, the query will now return a single entity which is managed by the object manager. Thus, there is no need to free it explicitly.
In the case where the object manager cannot find the specific entity, the object will be nil. This should not happen if you have one user accessing the database, but in multi-user environments, other users may delete entries from the database while our code is trying to acquire them. Thus, it is good idea to check against this using the Assign method as in the code.
Finally, we instruct Aurelius to push the changes back to the database by calling Flush. Note that we pass the object to Flush as we know exactly which entity instance has been modified.
Looking back at the code we have written so far, one can observe that there is a lot of repetition. When we add and edit departments and agents, we essentially write the same code by calling Save or Flush .
- 1.
Add a new unit in the project and save it as Database.Utilities.pas.
- 2.Add the following code in this unit:unit Database.Utilities;interfaceusesAurelius.Engine.ObjectManager;typeTDatabaseUtilities<T: class> = classclass procedure edit(const aObjManager: TObjectManager; const aEntity: T);end;implementationusesAurelius.Mapping.Attributes;{ TDatabaseUtilities<T> }class procedure TDatabaseUtilities<T>.edit(const aObjManager: TObjectManager; const aEntity: T);beginAssert(aObjManager <> nil);Assert(aEntity <> nil);tryaObjManager.SaveOrUpdate(aEntity);aObjManager.Flush(aEntity);exceptif not aObjManager.IsAttached(aEntity) thenaEntity.Free;end;end;end.
- 3.In MainForm.pas and in the following procedures, replace the whole try-except part with a call to TDatabaseUtilities.edit as in the following code:procedure TFormMain.btAddDepartmentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbeginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);department:=TDepartment.Create;department.Description:=Trim(form.edEntity.Text);TDatabaseUtilities<TDepartment>.edit(session.objectManager, department);updateDepartments;end;...end;procedure TFormMain.btAddAgentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbeginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=TAgent.Create;agent.Description:=Trim(form.edEntity.Text);TDatabaseUtilities<TAgent>.edit(session.objectManager, agent);updateAgents;end;...end;procedure TFormMain.btEditDepartmentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbegindepartment.Description:=Trim(form.edEntity.Text);TDatabaseUtilities<TDepartment>.edit(session.objectManager, department);updateDepartments;end;end;procedure TFormMain.btEditAgentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbeginagent.Description:=Trim(form.edEntity.Text);TDatabaseUtilities<TAgent>.edit(session.objectManager, agent);updateAgents;end;end;
Managing Blobs
- 1.When the list of the agents is updated, there is no selected row. Therefore, the image component should be empty and the add and delete photo buttons should be disabled. Go to MainForm.pas and add the following lines in updateAgents:procedure TFormMain.updateAgents;...begin...imPhoto.Bitmap:=nil;btAddPhoto.Enabled:= sgAgents.Selected>-1;btDeletePhoto.Enabled:= sgAgents.Selected>-1;end;
- 2.Blobs are, basically, streams of bytes. This, consequently, means that our photos (bitmaps) will be stored as bytes and, when retrieved, the bytes should be converted back to bitmaps. In order to achieve this, we add the next two procedures in TDatabaseUtilities in Database.Utilities.pas.uses...,FMX.Graphics,Aurelius.Types.Blob;interfacetypeTDatabaseUtilities<T: class> = class...class procedure bitmapToBlob (const aBmp: TBitmap; const aType: string;var aBlob: TBlob);class procedure blobToBitmap (const aBlob: TBlob; var aBmp: TBitmap);end;implementationuses...,FMX.Surfaces,System.Classes,System.SysUtils;class procedure TDatabaseUtilities<T>.bitmapToBlob(const aBmp: TBitmap;const aType: string; var aBlob: TBlob);varbmp: TBitmapSurface;bs: TBytesStream;beginbmp := TBitmapSurface.create;trybmp.assign(aBmp);bs := TBytesStream.create;tryTBitmapCodecManager.SaveToStream(bs, bmp, aType);aBlob.AsBytes := bs.Bytes;finallybs.free;end;finallybmp.free;end;end;class procedure TDatabaseUtilities<T>.blobToBitmap(const aBlob: TBlob;var aBmp: TBitmap);varms: TMemoryStream;beginAssert(aBmp <> nil);ms := TMemoryStream.create;tryaBlob.SaveToStream(ms);MS.Position := 0;aBmp.LoadFromStream(ms);finallyms.free;end;end;
- 3.Back in TFormMain , in MainForm.pas, we introduce a private variable named photo. We are going to use this to update the TImage component in the Agents tab. We also need to add some code in the OnDestroy event of the form in order to make sure we destroy any instances of photo.typeTFormMain = class(TForm)...procedure FormDestroy(Sender: TObject);privatephoto: TBitmap;...end;...procedure TFormMain.FormDestroy(Sender: TObject);beginFreeAndNil(photo);end;
- 4.When the user selects a row in the agent list, the agent image is shown in the TImage component . We manage this in the new private procedure loadPhoto, which gets the GUID value of the selected agent entity as a parameter. loadPhoto is called in the OnCellClick event of the agents list.typeTFormMain = class(TForm)...private...procedure loadPhoto (const aGUID: string);end;...procedure TFormMain.loadPhoto(const aGUID: string);varsession: IDatabaseSession;agent: TAgent;beginAssert(Trim(aGUID) <> '');FreeAndNil(photo);session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=session.objectManager.Find<TAgent>(Trim(aGUID));if Assigned(agent) and (not agent.Photo.IsNull) thenbeginphoto:=TBitmap.Create;TDatabaseUtilities<TAgent>.blobToBitmap(agent.Photo, photo);end;imPhoto.Bitmap:=photo;btAddPhoto.Enabled:= not Assigned(photo);btDeletePhoto.Enabled:= Assigned(photo);end;...procedure TFormMain.sgAgentsCellClick(const Column: TColumn; const Row:Integer);begin...loadPhoto(sgAgents.Cells[2, sgAgents.Selected]);end;
The procedure retrieves the agent from the database and checks if the Photo property has any content by using Aurelius’ IsNull function . If content is detected, blobToBitmap is used to load the blob content to photo. Lastly, the GUI is being taken care of by enabling the appropriate buttons.
- 5.Add a TOpenDialog component in the form and modify the OnClick event of btAddPhoto button.uses...,Aurelius.Types.Blob;...procedure TFormMain.btAddPhotoClick(Sender: TObject);varsession: IDatabaseSession;agent: TAgent;blob: TBlob;beginOpenDialog1.Filter:='PNG image files|∗.png';if OpenDialog1.Execute thenbeginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=session.objectManager.Find<TAgent>(StringToGUID(sgAgents.Cells[2,sgAgents.Selected]));if Assigned(agent) thenbeginimPhoto.Bitmap.LoadFromFile(OpenDialog1.FileName);TDatabaseUtilities<TAgent>.bitmapToBlob(imPhoto.Bitmap, 'png', blob);agent.Photo:=blob;TDatabaseUtilities<TAgent>.edit(session.objectManager, agent);end;end;end;
When the user selects a PNG file, the code loads the selected agent as before. Then, the image file is passed to the TImage component, and bitmapToBlob is called to convert the bitmap to blob. Then, the entity instance is updated and, eventually, saved in the database. In the files that come with the book, you can find avatars to try it yourself in the Misc folder.
You may wonder why we do not pass directly the agent.Photo to bitmapToBlob. We need to use a local variable because the compiler recognizes agent.Photo as a constant and, therefore, it cannot be passed to an argument which is treated as a variable.
- 6.We have one last thing to do that involves blob – the ability to delete the photo. Click the btDeletePhoto album and just set the IsNull property of the TBlob to nil as follows:procedure TFormMain.btDeletePhotoClick(Sender: TObject);varsession: IDatabaseSession;agent: TAgent;blob: TBlob;beginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=session.objectManager.Find<TAgent>(StringToGUID(sgAgents.Cells[2, sgAgents.Selected]));if Assigned(agent) thenbeginimPhoto.Bitmap:=nil;agent.Photo.IsNull:=True;TDatabaseUtilities<TAgent>.edit(session.objectManager, agent);end;end;
Deleting Entities
Importing Entities
All the code we developed earlier provides us with all the functionality we need to manage agents and departments. However, we miss the most important data in a call center: the calls. We could create buttons to manage calls in a similar way as we do with the agents and departments, but we will follow a different approach at this stage. We will import data from a csv file; this will demonstrate some additional features in Aurelius.
We need some additional elements in the forms. In the code files, open the project in CallCentre – Import folder to see the changes. I have added a separate tab to facilitate the import of the data. The tab item hosts a frame (TFrameImport) with a progress bar, a label, and a button. All this decoration is unnecessary for the essence of this book, but I wanted to create a decent user interface. The actual importing is done in the Database.Import.pas unit. The code includes manipulation of the GUI as well but, for simplicity, I will not reproduce it here. You can either check the file yourself or, if you write the code in a separate unit, you can safely focus on the parts presented here. The code should work in full.
Then, we add the agents and the departments in the database. The steps are exactly the same as those we followed earlier with the assistance of TDatabaseUtilities.
Then, the code loads the calls from the csv file and generates the TCall instances. There is some manipulation of the loaded data, but we will skip them as they are not the point of the discussion here. You can, of course, see the full code in the code files.
After we prepare the TCall object, we save it in the database with a simple call to objManager.Save. There is nothing new here; we’d been doing this already. What is different with the design of our code this time is that we need to import a good number of calls (c.1,770); it’s not that big if you consider professional settings, but it is big enough to consume unnecessary resources even in a small-scale application.
We set the object manager in a transactional state by calling BeginTransaction. The for-loop iterates through the lines from the data set. Each item has all the necessary information about a recorded call. We use this to generate a new TCall object and Save it in the cache of the object manager.
- A call to Flush is, in fact, a managed call to Commit. Flush wraps the whole try-except code we wrote in our example.
Following the preceding point, Flush appears suitable for a relatively small number of transactions. If you want to handle a good number of them, you may wish to get more control over the whole process as resources can be stretched and, additionally, you may need finer management of the situations where something goes wrong while Aurelius attempts to pass the changes to the database. Rollback, as it is demonstrated in the preceding code, is called in this case and reverts any changes delivered up to the point the error occurred. If you want to intervene at this step, the only way to do it is to set the object manager in a transactional state rather than simply call Flush.
Summary
As a matter of summarizing the chapter, please go ahead, run the code and import the data set from the csv file. You will then have populated tables in the database for the agents and the departments. Additionally, the database will have the calls imported and ready for use. We achieved all this by visiting the way that object manager manipulates entities and the associated operations. With all the data in place, we are ready to move to the next stage, to explore how Aurelius implements queries.