Joins
=====
First, I would like to apologize for my ignorance of Aurelius features (in a previous message): by adding
TMappedClasses.GetInstance.RegisterClass(<entity class name>) in the initialization section of the unit
we avoid the problem I mentioned when the code is only devoted to build the database.
I feel a lot of respect for Aurelius team's work and I think it's an important step in Delphi's history.
Now, let's come to the point.
1) ----------------
The ability to define unforeseen table joins without modifying the existing entities is essential from the developer's point of vue.
Example:
Your application uses entity
<code>
[Entity]
[Table('MEMBER')]
[Sequence('SEQ_MEMBER')]
[Id('FId', TIdGenerator.IdentityOrSequence)]
TMember = class
private
[Column('ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.NoUpdate])]
FId: Integer;
[Column('NAME', [TColumnProp.Required], 50)]
FName: string;
[Column('CARD_ID')]
FCard_Id: Integer;
public
property Id: Integer read FId write FId;
property Name: string read FName write FName;
property Card_Id: Integer read FCard_Id write FCard_Id;
end;
</code>
where CARD_ID contains the member's card number.
In middle of your project, you want to store member's card data, as registration date etc.
You define a new entity
<code>
[Entity]
[Table('CARD')]
[Id('FId', TIdGenerator.None)]
TCard = class
private
[Column('ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.NoUpdate])]
FId: Integer;
[Column('REGISTRATION_DATE', [TColumnProp.Required])]
FRegistration_Date: TDate;
public
property Id: Integer read FId write FId;
property Registration_Date: TDate read FRegistration_Date write FRegistration_Date;
end;
</code>
You are tempted to change entity TMember by replacing
[Column('CARD_ID')]
FCard_Id: Integer;
by
[Association]
[JoinColumn('CARD_ID', [TColumnProp.Required])]
FDepartment: TMember;
or, eventually:
[Association([TAssociationProp.Lazy])]
[JoinColumn('CARD_ID')]
FFunction: Proxy<TMember>;
but, as you suppress TMember Card_Id property, you break your existing code. Definitely forget it.
2) ----------------
How to join tables in Aurelius framework?
In version 1.4, the interesting TDynamicProperty concept does not handle instance properties.
By adding a TDynamicProperties property to an entity, you dynamically complete it with new members
which allow you to reach non previously mapped columns in a table.
Unfortunately, you cannot define in TMember a TDynamicProperty object of type TCard.
Instead, you can build a new entity derived from TMember:
<code>
[Table('MEMBER')]
TMember2 = class(TMember)
private
[Association([TAssociationProp.Required])]
[JoinColumn('CARD_ID')]
FCard: TCard;
public
property Card: TCard read FCard write FCard;
end;
</code>
(or
[Table('MEMBER')]
TMember2 = class(TMember)
private
[Association([TAssociationProp.Lazy])]
[JoinColumn('CARD_ID')]
FCard: Proxy<TCard>;
public
property Card: Proxy<TCard> read FCard write FCard;
end;
)
REMARK:
We omit [Entity] attribute at the top of class definitions.
The class will be mapped/registered later.
How to make use of this new entity?
3) ----------------
First, we need to register TMember2 class as a mapped entity:
TMappedClasses.GetInstance.RegisterClass(TMember2);
oMgr := TObjectManager.Create(GetDBConnection); // GetDBConnection returns an IDBConnection object
agt2 := oMgr.Find<TMember2>(1);
...
oMgr.Free;
Here is the generated SQL query:
SELECT A.ID AS A_ID, A.NAME AS A_NAME, A.CARD_ID AS A_CARD_ID, A.CARD_ID AS A_CARD_ID, B.ID AS B_ID, B.REGISTRATION_DATE AS B_REGISTRATION_DATE
FROM MEMBER A
LEFT JOIN CARD B ON (B.ID = A.CARD_ID)
WHERE A.ID = :p_0
:p_0 = 1
Apart the "A.CARD_ID AS A_CARD_ID" repetition singularity, this method works.
But there is a problem.
In some cases, especially when entity subclasses have been already invoked, the following instruction:
agt2 := oMgr.Find<TMember2>(1);
will raise an EListError exception.
Why?
This is the reason.
Aurelius framework contains a TClassHierarchyExplorer class which handles the entity hierarchy tree.
If entity class B is derived from entity class A, the unique TClassHierarchyExplorer instance knows it and instructs the central TMappingExplorer class instance
how to find, process the right tables, fields, etc.
Registering/unregistering an entity alters the entity hierarchy tree.
Users should be able to refresh TClassHierarchyExplorer when the entity hierarchy tree changes, but this class has no method to do it:
TClassHierarchyExplorer internal dictionaries are built once and for all. Rebuilding then from outside is clearly impossible.
Programmers usually don't like other persons to patch their code. But imagine a public Refresh method added to TClassHierarchyExplorer:
procedure TClassHierarchyExplorer.Refresh;
var
V: TValue;
C: TClass;
begin
for V in FDirectSubClasses.Values do
V.AsObject.Free;
FDirectSubClasses.Clear;
for V in FAllSubClasses.Values do
V.AsObject.Free;
FAllSubClasses.Clear;
// Load direct classes lists
for C in TMappedClasses.GetInstance.Classes do
FDirectSubClasses.Add(C, TList<TClass>.Create);
for C in TMappedClasses.GetInstance.Classes do
if FDirectSubClasses.ContainsKey(C.ClassParent) then
FDirectSubClasses[C.ClassParent].Add(C);
// Load all classes list
for C in TMappedClasses.GetInstance.Classes do
FAllSubClasses.Add(C, FindSubClasses(C));
end;
(this method mimics the PrivateCreate method.)
Previous code for joining tables MEMBER and CARD becomes:
TMappedClasses.GetInstance.RegisterClass(TMember2);
TClassHierarchyExplorer.GetInstance.Refresh; // rebuild the entity hierarchy tree
oMgr := TObjectManager.Create(GetDBConnection); // GetDBConnection returns an IDBConnection object
agt2 := oMgr.Find<TMember2>(1);
...
oMgr.Free;
The EListError exception is not raised any more.
Conclusion
Creating a derived class containing an instance member (TCard in the example) is an eligible solution to the table join problem
if you want to keep intact existing entities.
However, a cosmetic change must be brought to Aurelius code in order to make it work.
4) ----------------
Final remark
In many situations, Aurelius build internal lists and dictionaries only when they are needed.
Suppose you want to build tables in an database from a list of entities, all of them except TPerson entity.
Very likely, you will code this:
<code>
procedure TfrmMain.btnCreateDBClick(Sender: TObject);
var
dMgr: TDatabaseManager;
sMsg: string;
begin
dMgr := TDatabaseManager.Create(GetDBConnection);
try
TMappedClasses.GetInstance.UnRegisterClass(TPerson);
dMgr.BuildDatabase;
dMgr.Free;
TMappedClasses.GetInstance.RegisterClass(TPerson);
except
on E: Exception do
begin
dMgr.Free;
sMsg := 'Error while creating tables.' + #13#10 + E.Message;
MessageDlg(sMsg, mtError, [mbOK], 0);
Exit;
end;
end;
sMsg := 'Tables created.';
MessageDlg(sMsg, mtInformation, [mbOK], 0);
end;
</code>
If you call btnCreateDBClick() at the very beginning of you program execution, instruction
TMappedClasses.GetInstance.UnRegisterClass(TPerson);
will fail (PERSON table will be also created) because TMappedClasses class FRegisteredClasses internal list is not already initialized at this stage.
To bypass this difficulty, you can force the execution of TMappedClasses.AutoSearchClasses
- by checking that TGlobalConfigs.GetInstance.AutoSearchMappedClasses is set to True (default value)
- by invoking property Classes which calls GetClasses in something like
with TMappedClasses.GetInstance.Classes do;
You can even write more meaningful code by defining a procedure:
procedure TfrmMainObjState.RefreshMappedClasses;
var
b: boolean;
begin
b:= TGlobalConfigs.GetInstance.AutoSearchMappedClasses;
TGlobalConfigs.GetInstance.AutoSearchMappedClasses := true;
with TMappedClasses.GetInstance.Classes do;
TGlobalConfigs.GetInstance.AutoSearchMappedClasses := b;
end;
The new version of btnCreateDBClick:
<code>
procedure TfrmMain.btnCreateDBClick(Sender: TObject);
var
dMgr: TDatabaseManager;
sMsg: string;
begin
RefreshMappedClasses;
dMgr := TDatabaseManager.Create(GetDBConnection);
try
TMappedClasses.GetInstance.UnRegisterClass(TPerson);
dMgr.BuildDatabase;
dMgr.Free;
TMappedClasses.GetInstance.RegisterClass(TPerson);
except
on E: Exception do
begin
dMgr.Free;
sMsg := 'Error while creating tables.' + #13#10 + E.Message;
MessageDlg(sMsg, mtError, [mbOK], 0);
Exit;
end;
end;
sMsg := 'Tables created.';
MessageDlg(sMsg, mtInformation, [mbOK], 0);
end;
</code>
Etienne,
first of all, I appreciate your kind words about Aurelius and our team. Can we maybe quote you on our Aurelius page?
Second, I'm impressed and happy that you went deep into Aurelius source code and understand it very well. It's useful for you and also interesting for us because we can exchange ideas. Having said that, my comments:
1. I'm glad you solved your problem for now. All your comments are correct given the current state of Aurelius source code. If it's working for you (and it requires no change in Aurelius as I understood it) then let's keep it this way for now.
2. Yes, dynamic properties do not support associations and it was intentional at this point. However, since the beginning they were designed having associations in mind. It's a little bit more complex and I'm not sure how many users would benefit from it (compared to the other many features we're working on) but it's possible and code is prepared for that. If it comes to a point where associations in dynamic properties become an important and demanded feature, we might add it.
3. As I said, your analysis of the code and your solutions are ok. But be aware you are dealing with unsupported and unofficial features. See the "important notice" in this topic of Aurelius manual: http://www.tmssoftware.com.br/aurelius/doc/web/index.html?getting_support.htm. One reason for this notice was to prevent advanced users like you to dig into source code, discover "nice" classes to use, and then have all your code break in a future release, then claim that we broke it. Another reason for that is not because we don't want to support it, on the contrary, it's because we intend to improve the code a lot in future and we can't afford that keeping backward compatibility would prevent us for moving Aurelius further.
4. Many of the classes you mentioned, and its limitations (singleton classes, not refreshable, etc.) will change in future for a better architecture. That's why they're not documented, the current format will change. If you have the curiosity to check changes between 1.3 and 1.4, when we released dynamic properties, you will see a lot of changes in this area. In 1.3, TMappingExplorer was singleton. Now it's not, and you can create instances of object manager passing an instance of TMappingExplorer, making it possible to have different managers having different "setups" (since a TMappingExplorer is a representation of a TMappingSetup). TMappingSetup class has a whole chapter dedicated to it, and it was all included in 1.4 to support dynamic properties. In a future version, the class hierarchy explorer will move to TMappingExplorer, and registration/unregistration of classes will probably be in TMappingSetup. So you will have full control over what's registered and what's not, and you will be able to have a configuration with a TPerson class, and another configuration without it.