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: +/-
Set the SaveOnClose property of ContactsDataset to True.

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

 

Make a Free Website with Yola.