Our application has now most of its core functionality. It manages agents and departments and shows the majority of the key performance indicators and statistics. There are a few more bits to complete. In this chapter, we will finish them and demonstrate some additional features of Aurelius.
Inheritance
Inheritance is one of the most widely used features of OOP. It is useful in numerous cases; it saves time and effort and, above all, leads to well-designed object systems that support complicated concepts. Inheritance, at the same time, poses a problem for database administrators. The way inherited objects can be stored and retrieved in a database is the topic of an almost ongoing debate. This case is an example where the way developers think does not, easily, map to database structure.
Aurelius allows developers to design their classes in any way they need and prefer, and then the framework takes responsibility and, with minimum modifications, entities can be easily managed. In order to understand how this works, we will add to our database information about who (user) and when (date/timestamp) an entry was created and modified. This is a common set of fields that can (or should) be present in all the tables, but to keep things simple and clean we will implement it for the TAgent table only. You may have already noticed that, in the MainForm, we have already added the fields to display this information. As before, please check the Call Centre – Inheritance for the code.
The next step is to inform Aurelius there is an entity that inherits properties from another one. This is, naturally, done with the use of appropriate attributes that decorate the classes. There are two options when it comes to inheritance in Aurelius: to represent it as a single table in the database or to normalize the database and create linked tables. There are both pros and cons for these two approaches; please refer to the manual for details.
In the figure, the TAgent and TDepartment entities inherit from TBase class. Aurelius puts all the fields in one table and adds a discriminatory column, which allows it to retrieve the correct values. Note that since all the fields are consolidated in one table, you cannot have fields with the same name (compare AgentDescription and DepartmentDescription to Description fields in the original classes). Additionally, the primary fields (ID) are removed from the successors, and it is only required in the TBase class.
- 1.In Entities.pas, go to TBase class and decorate it with the Inheritance attribute to indicate that we want the JointTable strategy to be implemented. In the following code snippet, I have also added all the attributes to configure the entity for use by Aurelius:...type[Entity][Table('Base')][Id('FID', TIdGenerator.Guid)][Inheritance(TInheritanceStrategy.JoinedTables)]TBase = classprivate[Column('ID', [TColumnProp.Required])]FID: TGuid;[Column('CreateUser', [], 50)]FCreateUser: Nullable<string>;[Column('CreateTS', [])]FCreateTS: Nullable<TDateTime>;[Column('ModifyUser', [], 50)]FModifyUser: Nullable<string>;[Column('ModifyTS', [])]FModifyTS: Nullable<TDateTime>;publicproperty ID: TGuid read FID write FID;property CreateUser: Nullable<string> read FCreateUser write FCreateUser;property CreateTS: Nullable<TDateTime> read FCreateTS write FCreateTS;property ModifyUser: Nullable<string> read FModifyUser write FModifyUser;property ModifyTS: Nullable<TDateTime> read FModifyTS write FModifyTS;end;...initialization...RegisterEntity(TBase);...end.
- 2.
TBase is the ancestor class; now, we need to move to the inherited class (TAgent) and add the PrimaryJoinColumn attribute .
We, also, need to delete the primary key property (lines in comment tags) because TAgent inherits the ID property from TBase class; Aurelius uses the primary key of the ancestor as the primary key of the successor. The two entities are now linked in the code level, but they are separate tables at database level. The question arises about how Aurelius is able to link the two. The answer is provided by the PrimaryJoinColumn attribute; in the example, we pass BaseID and we, effectively, indicate to the framework that a new field named BaseID will be the foreign key for the Base table. If PrimaryJoinColumn is omitted, Aurelius assumes the name of the identifier field.
- 3.
Removing the primary key from TAgent has the knock-off effect of destroying any associations in which TAgent is involved unless we omit PrimaryJoinColumn . We need to update them and use BaseID instead.
- 4.In MainForm.pas, populate the CreateUser and ModifyUser to reflect the name of the user and the actions. As a simple approach, we use the user "user" when the user adds or modifies an agent and the user "system" when we import entities from external sources.procedure TFormMain.btAddAgentClick(Sender: TObject);...begin...if form.ShowModal = mrOk thenbeginsession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=TAgent.Create;agent.Description:=Trim(form.edEntity.Text);agent.CreateUser:='user';TDatabaseUtilities<TAgent>.edit(session.objectManager, agent);updateAgents;end;...end;...procedure TFormMain.btEditAgentClick(Sender: TObject);...begin...if Assigned(agent) thenbegin...if form.ShowModal = mrOk thenbeginagent.Description:=Trim(form.edEntity.Text);agent.ModifyUser:='user';TDatabaseUtilities<TAgent>.edit(session.objectManager, agent);updateAgents;end;...end;end;
- 5.In Database.Import.pas, update CreateUser field to system.procedure importData (const aFilename: string; const aConnection: IDBConnection; const aImportFrame: TFrameImport);...begin...for agentName in agentsDictionary.Keys dobeginagent:=TAgent.Create;agent.Description:=agentName;...TDatabaseUtilities<TAgent>.bitmapToBlob(agentPhoto, 'png', agentBlob);agent.Photo:=agentBlob;agent.CreateUser:='system';...end;...end;
- 6.Back in MainForm.pas, create a new procedure to show these fields to the form.interface...typeTFormMain = class(TForm)...private...procedure loadAgentMetadata (const aGUID: string);public...end;...implementation...procedure TFormMain.loadAgentMetadata(const aGUID: string);varsession: IDatabaseSession;agent: TAgent;beginAssert(Trim(aGUID) <> ");session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);agent:=session.objectManager.Find<TAgent>(Trim(aGUID));if Assigned(agent) thenbeginif agent.CreateUser.HasValue thenlbCreateUser.Text:=agent.CreateUser.ValueelselbCreateUser.Text:='Not Assigned';if agent.ModifyUser.HasValue thenlbModifyUser.Text:=agent.ModifyUser.ValueelselbModifyUser.Text:='Not Assigned';if agent.CreateTS.HasValue thenlbCreateTS.Text:=FormatDateTime('DD/MM/YYYY, HH:MM', agent.CreateTS.Value)elselbCreateTS.Text:='Not Assigned';if agent.ModifyTS.HasValue thenlbModifyTS.Text:=FormatDateTime('DD/MM/YYYY, HH:MM ', agent.ModifyTS.Value)elselbModifyTS.Text:='Not Assigned';end;end;
- 7.In OnSelectCell event of the agent’s grid, call loadAgentMetadata to update the form.procedure TFormMain.sgAgentsSelectCell (Sender: TObject; const ACol, ARow:Integer; var CanSelect: Boolean);begin...loadAgentMetadata(sgAgents.Cells[2, ARow]);updateAgentList;end;
Events
When we used inheritance to keep track of the user who creates and updates an agent, we did not touch the timestamps of those actions. Obviously, it is very simple to add two or three lines to populate the create and modify timestamp fields. In this case, however, we are going to turn our attention to Aurelius events. Events is a general term used in software engineering that indicates a mechanism where several entities exchange messages.
- 1.
The application that uses Aurelius provides a callback procedure and attaches (subscribes) it to the events manager.
- 2.
Every time the application performs a transaction in the database, Aurelius event manager posts a message to the subscribers by triggering the callback procedure.
- 3.
The callback procedure gives to the application access to the entities and other details related to the transaction.
- 4.
When it is suitable (e.g., when the application exits), the application should unsubscribe from Aurelius events mechanism.
OnInserting and OnUpdating Events
- 1.Add a new unit to the project and save it as Database.Events.Types.pas and declare the IDatabaseEvents interface.unit Database.Events.Types;interfacetypeIDatabaseEvents = interface['{369927E5-976A-4263-9E66-31355C3E7C2C}']procedure subscribeEvents;procedure unsubscribeEvents;end;implementationend.
- 2.Add another unit under the name Database.Events.pas and add the following code:unit Database.Events;interfaceusesDatabase.Events.Types,Aurelius.Events.Manager;typeTDatabaseEvents = class (TInterfacedObject, IDatabaseEvents)privatefInsertingProc: TInsertingProc;fUpdatingProc: TUpdatingProc;publicprocedure subscribeEvents;procedure unsubscribeEvents;end;implementationend.
In the implementation of IDatabaseEvents interface, we declare the two procedures that allow us to subscribe and unsubscribe to Aurelius’ event manager. We also declare two private variables (fInsertingProc and fUpdatingProc) that represent the callback procedure; that is, when an entity is about to be inserted or updated in the database, Aurelius will trigger these two procedures.
- 3.Registering (and unregistering) them is done in the following code:unit Database.Events;interface...implementationusesAurelius.Mapping.Explorer;procedure TDatabaseEvents.subscribeEvents;beginTMappingExplorer.Default.Events.OnInserting.Subscribe(fInsertingProc);TMappingExplorer.Default.Events.OnUpdating.Subscribe(fUpdatingProc);end;procedure TDatabaseEvents.unsubscribeEvents;beginTMappingExplorer.Default.Events.OnInserting.Unsubscribe(fInsertingProc);TMappingExplorer.Default.Events.OnUpdating.Unsubscribe(fUpdatingProc);end;end.
We have come across the mapping explorer before when we dealt with the views and different database scheme models. We use the OnInserting and OnUpdating gateways from the Events property of the TMappingExplorer to subscribe (and unsubscribe) our custom callback procedures to Aurelius.
If you check the technical manual, you may notice that you can pass an anonymous method directly to the Subscribe and Unsubscribe methods of the mapping explorer, which eliminates the need for separate variables. The reason I chose to introduce the variables is that I want to be able to clean up the procedures manually rather than rely to the framework to nil-ify them. This is more of a personal choice that I feel leads to good coding practice but, admittedly, without any strong advantages to support this argument.
One last note worth mentioning: as you can see in the code, we attached the callback procedures to the Default model of the database. This means that our procedures will be triggered only when an entity in our default model is involved in a transaction. In our case, thankfully, we only have the view definition outside the default model, so we are safe.
You may, however, have a different situation in a full-scale application. It was mentioned before that models can be useful to organize database scheme, and a common use is to group together entities related to security and authorization. Attaching the events to the default model will isolate any triggering from the Security model entities, as an example. If you want to subscribe and unsubscribe events in a different model, you can do it as in the following code:TMappingExplorer.Get('Security').Events.OnInserting.Subscribe(fInsertingProc); - 4.It is time now to define our event procedures. Add a typical constructor and destructor to the TDatabaseEvent class and define fInsertingProc.unit Database.Events;interface...typeTDatabaseEvents = class (TInterfacedObject, IDatabaseEvents)private...publicconstructor Create;...end;implementationuses...,Entities,System.SysUtils,Aurelius.Mapping.Explorer;constructor TDatabaseEvents.Create;begininherited;fInsertingProc:= procedure (Args: TInsertingArgs)beginif Args.Entity is TAgent thenbegin(Args.Entity as TAgent).CreateTS:=Now;end;end;end;
We use the TInsertingArgs in the declaration of our procedure. TInsertingArgs provides details about the entity and the object manager that are involved in the database transaction that triggers this event.
As we are interested in updating the agents’ data, we filter the calls by checking that the TInsertingArgs.Entity property is of TAgent. When the correct entity is identified, we update the CreateTS field.
- 5.In a similar way, we define the fUpdatingProc and populate the ModifyTS field.unit Database.Events;interface...implementation...constructor TDatabaseEvents.Create;...begin...fUpdatingProc:= procedure (Args: TUpdatingArgs)beginif Args.Entity is TAgent thenbegin(Args.Entity as TAgent).ModifyTS:=Now;Args.RecalculateState:=True;end;end;end;
- 6.
This procedure is the same as before with only one subtle but important difference. When Aurelius triggers an OnUpdating event, it has completed the mapping of the entity and the parsing of any changes, and it is ready to push the modifications to the database. If you inspect Args.OldColumnValues and Args.NewColumnValues properties, you can identify the changes in the entity. However, this opens up a gap; if any changes of entity’s properties take place in the event itself, as we do in the preceding code, Aurelius misses the opportunity to consume the modifications, and consequently these changes will not be saved in the database. The way to resolve this is to set Args.RecalculateState to true as this property will force Aurelius to reparse the entity.
- 7.In the destructor, we clean things up by unsubscribing the procedures from the events manager and setting them to nil....typeTDatabaseEvents = class (TInterfacedObject, IDatabaseEvents)private...publicdestructor Destroy; override;...end;implementation...destructor TDatabaseEvents.Destroy;beginunsubscribeEvents;fInsertingProc:=nil;fUpdatingProc:=nil;inherited;end;
- 8.The last thing left to do is to manage the events in the main form. Open MainForm.pas and update the OnCreate event. Similarly, amend the code in the OnDestroy event of the form as follows:unit MainForm;interfaceuses...,Database.Events.Types;type...TFormMain = class(TForm)...privatedbEvents: IDatabaseEvents;...end;...implementationuses...,Database.Events;procedure TFormMain.FormCreate(Sender: TObject);...begin...dbEvents:=TDatabaseEvents.Create;dbEvents.subscribeEvents;setupGUI;end;procedure TFormMain.FormDestroy(Sender: TObject);begin...dbEvents.unsubscribeEvents;end;
OnInserted and OnUpdated Events
- 1.We first declare two variables to hold the event procedures.unit Database.Events.Alternative;interface...typeTDatabaseEvents = class (TInterfacedObject, IDatabaseEvents)privatefInsertedProc: TInsertedProc;fUpdatedProc: TUpdatedProc;public...end;
- 2.The implementation of the procedures is as follows:constructor TDatabaseEvents.Create;varstatement: IDBStatement;objManager: TObjectManager;sqlScript: string;begininherited;fInsertedProc:= procedure (Args: TInsertedArgs)beginif Args.Entity is TAgent thenbeginstatement:=(Args.Manager as TObjectManager).Connection.CreateStatement;sqlScript:='update Base set CreateTS = '+Double(Now).ToString+' where ID = '+QuotedStr(TAgent(Args.Entity).ID.ToString);statement.SetSQLCommand(sqlScript);statement.Execute;end;end;fUpdatedProc:= procedure (Args: TUpdatedArgs)beginif Args.Entity is TAgent thenbeginstatement:=(Args.Manager as TObjectManager).Connection.CreateStatement;sqlScript:='update Base set ModifyTS = '+Double(Now).ToString+' where ID = '+QuotedStr(TAgent(Args.Entity).ID.ToString);statement.SetSQLCommand(sqlScript);statement.Execute;end;end;end;
- 3.
In this approach, we use direct SQL calls to modify the properties. We build an update SQL query and execute it in the same way we did when we managed the definition of the view we used in the previous chapter.
In order to execute the SQL query, we need access to the associated object manager. TInsertedArgs.Manager provides the right instance of it (note the need to cast this property to get access to the object manager).
You may wonder why we did not use the usual approach of instantiating an IDatabaseSession and use a typical TObjectManager.Save to update the entity. What stops us from this is the fact that the OnInserted event is triggered before the insert transaction is fully completed and released. This means that the database is locked to the specific entity we are trying to modify in the event. Therefore, this approach is unusable.
Additionally, even if we were able to bypass the database locking, a call to Save from within the event would generate subsequent calls to the same event leading to a repetitive loop of Save and OnInserted calls .
TAureliusDataSet
Note
The code in this section requires the TeeChart TDBChart, which is not part of the standard TeeChart package that comes with Delphi. You need to visit TeeChart’s web site, download and install the trial version (FMX component) if you want to run the code files.
Aurelius provides a dataset descendant (TAureliusDataSet) to facilitate data binding with visual controls (data-aware). It is cross-platform and, because it is based on Delphi’s own TDataSet component, it can be used to link to any controls that employ TDataSet’s functionality. If you need to look at the details, please visit the technical manual. You will be able to find up-to-date information.
- 1.
Open the CallCentre project, drop two TAureliusDataSet components from the tool palette, and name them as adsAbandonRate and adsSatisfaction (Figure 6-3).
- 2.Add a new private method updateCharts in the MainForm. We will use this method to generate the data for the charts. For now, it is empty; we will populate it in the next steps. Add a call to the method in updateDashboard....interfacetypeTFormMain = class(TForm)...private...procedure updateCharts (const aWeek: TWeeks);public...end;...implementation...procedure TFormMain.updateCharts(const aWeek: TWeeks);begin// We are going to fill this in in the next partsend;procedure TFormMain.updateDashboard(const aWeek: TWeeks);begin...updateCharts(aWeek);end;
Criteria-Based Data Set
As you can observe, we are not doing anything sophisticated. We create a new instance of the IDatabaseSession and initialize it as we have done many times already. Then, we construct the query based on TCriteria. We have done this as well before. The only new twist here is that we pass the criteria directly to TAureliusDataSet by, simply, calling SetSourceCriteria.
View-Based Data Set
For the second chart, we are going to use a view to demonstrate the flexibility of TAureliusDataSet. First, let’s create the view; we need the abandon rate grouped by department. Because of the way we have designed CallCentre project, we, also, need it grouped by week.
In this case, we use SetSourceList to pass data to TAureliusDataSet. The way we build rateList is familiar and consistent with what we have done up to this point. Note that in this case we need to explicitly free rateList; otherwise we will induce memory leaks. However, we do not have to do the same with TCriteria as it is intrinsically managed by the data set.
Summary
In this chapter, we looked at how object-oriented methodologies blend with the functionality of Aurelius as an ORM framework. The inheritance property of objects and the events provide opportunities to automate processes and expand functionality. In addition, this chapter demonstrated how we can use a graphical component (TAureliusDataSet) to pass information to third-party data-aware components.