TUTORIAL 02 - Master/detail
In this tutorial, we'll add to the application of tutorial 01, the
ability to store the phone(s) for each contact. This will be achieved
by making a master/detail relationship of one-to-many, e.g., each
contact can have one or more phones. Explaining the theory of
relational database is behind the scope of this lesson. Any questions
ask for your teacher Google.
Let's start from the work done in tutorial 01. Open a copy of the application and add the following components
- 01 TSqlite3Dataset
- Name: PhonesDataset
- TableName: Phones
- MasterSource: ContactsDataSource
- MasterFields: Id
- IndexFieldNames: ParentId
- SaveOnClose: True
- SaveOnRefetch: True
- 01 TDataSource
- Name: PhonesDataSource
- Dataset: PhonesDataset
- 01 TDBGrid
- Name: PhonesGrid
- DataSource: PhonesDataSource
- Columns: add 01 item (Phone)
- 02 TButtons
- Name: AddPhoneButton / DeletePhoneButton
- Caption: +/-
Further, delete SaveButton and its OnClick event handler (more on this later)
The news here are the properties required to make a master/detail relation:
- MasterSource: the TDataSource linked to the master dataset (ContactsDataset)
- MasterFields: the master dataset field(s) that identify a record, generally a primary key
- IndexFieldNames: the detail dataset field(s) that will link to a record in the master dataset, AKA foreign key.
Some notes:
1) More than one field name (separated by ";") can be set in both
MasterKeys and IndexFieldNames. The number of fields in both must match.
2) All properties relating to master/detail are changed in the detail
dataset. The master dataset properties are not touched.
Create the new table
Let's modify the OnCreate event handler to create the Phones table:
[..]
with PhonesDataset do
begin
FileName := IncludeTrailingPathDelimiter(GetAppConfigDirUTF8(True)) + 'data.db';
if not TableExists then
begin
FieldDefs.Clear;
FieldDefs.Add('Id', ftAutoInc);
FieldDefs.Add('Phone', ftString);
FieldDefs.Add('ParentId', ftInteger);
CreateTable;
end;
Open;
end;
Pretty similar to the way the Contacts table is created. Notice that the filename is the same as the ContactsDataset and that we added an extra field (the foreign key) to enable the master/detail.
Add the code to add/delete phones
In the OnClick event of AddPhoneButton put:
PhonesDataset.Append;
PhonesDataset.Post;
In the OnClick event of DeletePhoneButton put:
if not PhonesDataset.IsEmpty then
PhonesDataset.Delete;
Not many to say here, only that instead of checking for RecordCount we use the IsEmpty property.
It's also a good practice delete all phones together when a contact is deleted. To accomplish this create a BeforeDelete event handler in ContactsDataset and put this code:
while not PhonesDataset.IsEmpty do
PhonesDataset.Delete;
Done
Fine,
but previously you said that is needed to call ApplyUpdates to save the
changes and here you asked to remove the save code. Is not something
wrong?
Good question. TSqlite*Dataset has the ability to
automatically call ApplyUpdates when is necessary, basically when the
data buffer is being to be freed, i.e., before closing (Close, Destroy)
and before data refetch (RefetchData, internal master/detail
mechanism). For this it provides two properties: SaveOnClose and
SaveOnRefetch.
So, set property SaveOnClose to True in both ContactsDataset and
PhonesDataset and SaveOnRefetch for the PhonesDataset (the detail
dataset).
Some notes:
1) In near future, all boolean properties specific for sqlite, like SaveOn*, will be grouped together in a
Options property (a set of elements). The behavior will keep the same.
Again, no SQL knowledge/usage was necessary. The SQL logic work is done by TSqlite*Dataset without the need of programmer intervention.
The project file can be found here