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).


First lets make two tables.

The "countries" table:

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

Add two countries to the table:

  • Sweden
  • Norway


Then make the "cities" table:

CITY_ID (Primary key, integer, autoincrement)
CITY_NAME (Text, not null)
COUNTRY_ID (integer, not null) (foreign key)

Add three cities to this table:

  • Stockholm with COUNTRY_ID 1
  • Gothenburg with COUNTRY_ID 1
  • Oslo with COUNTRY_ID 2


Since Sweden was added first in the empty table it will have COUNTRY_ID 1 and Norway will have with COUNTRY_ID 2. For every city you add, you must supply its COUNTRY_ID because thats what links the tables together and make the master/detail concept possible.


  • 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 should 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. So in our example the :COUNTRY_ID value is supplied by DataSource1, which is our Master DataSource.

Continue with the Detail view:

  • 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)


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

  • DBConnection
  • Transaction
  • Query1
  • Query2


Now there should be detail data in the DBGrid2


A thing to remember is that the Detail Query's DataSource must be set to the Master DataSource. Which is DataSource1 in our example.


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


To add cities, use the information under "Adding detail records with the right foreign key" above.

See also