MasterDetail

From Lazarus wiki
Jump to navigationJump to search

Overview

In relational databases the master/detail concept is also called 1:N or one-to-many. It means that one record in a table/dataset, can be related to many records in another table/dataset. It is a model that uses data from one dataset to filter data in another dataset. The dataset that is used to filter the data is called Master and the dataset that is filtered is called Detail.

For example, countries have many cities. If you select a country and want to see all the cities, thats a good job for Master/Detail. The countries table could be master, and cities table could be detail.

The filtering works because the detail table contains an identifier from the master table, usually the master tables primary key. So this key exists in both the master and detail table. In the detail table it is called a foreign key.

Classical examples of master/detail relations are

  • customers with orders
  • orders with ordered articles

In our example below we'll work with a customer table and an order table.

SQLDB implementation

You can use the datasource property in the detail dataset to link to the master dataset.

The approach below applies to both Lazarus and FPC code that uses the built in SQLdb Package database layer. It may also apply to other database layers (e.g. Zeos); please check with their documentation.

Example from the EMPLOYEE database (as used in SQLdb Tutorial0 and other tutorials):

  • a CUSTOMER table with an integer primary key CUST_NO and other fields
  • a SALES table with a CUST_NO integer field that is a foreign key linking to the CUST_NO field in the CUSTOMER table

On the form:

  • use one connection, 1 transaction, but 2 queries, 2 data sources
  • a master query called qryCustomers selects from the CUSTOMER table
  • a detail query called qrySales that selects from the SALES table

In the detail query qrySales:

  • set the database property as usual
  • set the datasource property to point to the master datasource
  • in the query SQL, use a WHERE query to limit the select; use the detail fieldname and limit it to a parameter with the name of the field in the master table
SELECT * from SALES WHERE SALES.CUST_NO=:CUST_NO

In this case we use the SALES.CUST_NO field that happens to have the same field name as the master field CUST_NO, but that need not be the case. As the master datasource keeps track of the current record for the master query qryCustomers, FPC can see the CUST_NO parameter as a reference to the current value of the CUST_NO field in the master/qryCustomers query.

Make sure that the master query is open before the detail query so it can look up fields.

Adding detail records with the right foreign key

Although detail records scroll with master records, additional code is needed.

When adding new detail records, the SALES.CUST_NO field is still NULL unless we fill it. So we need to set up an AfterInsert event handler for qrySales:

procedure TForm1.qrySalesAfterInsert(DataSet: TDataSet);
begin
  DataSet.FieldByName('CUST_NO').AsInteger := qryCustomers.FieldByName('CUST_NO').AsInteger;
end;


Countries and cities example

This tutorial was made using SQLite but the principles are the same for other databases also.

In case you have no admin program for SQLite, I suggest SQLiteMan 1.2.2. http://sourceforge.net/projects/sqliteman/?source=directory

Create a new SQLite database and save it as database.db3.

(If you use another database system, just create your tables as you are used to and use the appropriate components for the example).


Now lets make two tables:

First the "countries" table:

COUNTRY_ID (Primary key, integer, autoincrement)
COUNTRY_NAME (Text, not null)

..and the "cities" table:

CITY_ID (Primary key, integer, autoincrement)
CITY_NAME (Text, not null)
COUNTRY_ID (integer, not null) (foreign key)
  • Create a new project in Lazarus and save it as MasterDetail
  • Drop a TSQLite3Connection on the form. (SQLdb tab)
  • In the DatabaseName property for TSQLite3Connection enter full path and filename for the SQLite Database file you just made.
  • Drop a TSQLTransaction on the form. (SQLdb tab)
  • Set its Database property to: SQLite3Connection1
  • Return to TSQLite3Connection and set its Transaction property to SQLTransaction1
  • Set TSQLite3Connection.Connected to true

You now have a connection to the database and can proceed to the next step.

Drop a TSQLQuery on the form. (SQLdb tab) Set its Database property to: SQLite3Connection1 Set its Transaction property to: SQLTransaction1 In the SQL statement, enter: select * from countries

Drop a TDataSource on the form. (Data Access tab) Set its DataSet to: SQLQuery1

Drop a TDBGrid on the form (Data Controls tab) Set its DataSource property to: DataSource1

Go to SQLQuery1 and set Active to true. The content of the countries table (master) should now be visible in DBGrid1.

Now proceed with the cities table (detail) Drop a second TSQLQuery on the form. (SQLdb tab) Set its Database property to: SQLite3Connection1 Set its Transaction property to: SQLTransaction1 In the SQL statement, enter:

select * from cities where cities.COUNTRY_ID = :COUNTRY_ID

The colon sign for :COUNTRY_ID just means that this is a variable parameter and that the value comes from somewhere else. It takes the value from the Master DataSource as you will understand when you have read this article to the end. So in our example the :COUNTRY_ID value is supplied by DataSource1, which is our Master DataSource.


Drop a second TDataSource on the form. Set its DataSet property to Query2 (detail query) Drop a second DBGrid on the form (Detail grid) Set its DataSource to DataSource2 (Detail DataSource)

To use a Connection, Transaction and a Query to display the data of one table in a database makes no trouble.

To display the content of the master table in the first dbgrid is easy. To selectively display the content of the detail table takes a little more, but is also quick and easy when you have learnt how to do it. The only tricky part to understand here is that the Detail Query's DataSource must be set to the Master DataSource. And that is DataSource1 in our example.

Now get the data flowing into the grids. Set all these to active/connected:

DBConnection Transaction Query1 Query2


If you still didnt understand, look at this wiring scheme:

Master Query.DataSource := None Master DataSource.Dataset := Master Query Master DBGrid.DataSource := Master DataSource

Detail Query.DataSource := Master DataSource Detail DataSource.Dataset := Detail Query Detail DBGrid.DataSource := Detail DataSource

Troubleshooting:

One thing easy to miss is that Detail Query.Datasource must be wired to Master DataSource

See also