MasterDetail/ja

From Free Pascal wiki
Jump to navigationJump to search

概要

リレーショナル データベースでは、マスター/ディテールの概念は 1 対多または 1:N とも呼ばれる。これは、テーブル/データセット内の 1 つのレコードが、別のテーブル/データセット内の多数のレコードに関連付けられる可能性があることを意味する。これは、あるデータセットのデータを使用して別のデータセットのデータをフィルター処理するモデルである。データのフィルタリングに使用されるデータセットはマスターと呼ばれ、フィルタリングされたデータセットはディテールと呼ばれる。たとえば、国には多くの都市があります。 国を選択し、そのすべての都市を表示したい場合は、マスター/ディテールに適した仕事である。国テーブルをマスター、都市テーブルをディテールにすることができる。フィルタリングが機能するのは、ディテール表にマスター表の識別子 (通常はマスター表の主キー) が含まれているためである。 したがって、このキーはマスターテーブルとディテールテーブルの両方に存在する。 詳細テーブルでは外部キーと呼ばれる。

マスター/ディテール関係の典型的な例は次のとおりである:

  • 顧客と注文
  • 注文した商品を含む注文

以下の例では、customer テーブルと order テーブルを操作する。

SQLDB 実装

詳細データセットの datasource プロパティを使用して、マスター データセットにリンクできる。

以下のアプローチは、組み込みの SQLdb Package データベース層を使用する Lazarus と FPC コードの両方に適用される。 他のデータベース層 (Zeo など) にも適用される場合がある。 ドキュメントを確認されたい。

EMPLOYEE データベースの例 (SQLdb_Tutorial0/ja および他のチュートリアルで使用されているもの):

  • 整数の主キー CUST_NO とその他のフィールドを持つ CUSTOMER テーブル
  • CUSTOMER テーブルの CUST_NO フィールドにリンクする外部キーである CUST_NO 整数フィールドを持つ SALES テーブル

フォーム上で:

  • 1 つの接続、1 つのトランザクションを使用するが、2 つのクエリ、2 つのデータ ソースを使用する
  • qryCustomers というマスター クエリは CUSTOMER テーブルから選択する
  • SALES テーブルから選択する qrySales というディテールクエリ

ディテールクエリqrySalesで:

  • 通常通り、データベースプロパティを設定する
  • データベースプロパティをマスターデータソースを示すように設定する
  • クエリ SQL では、WHERE クエリを使用して選択を制限する。 詳細フィールド名を使用し、マスター テーブル内のフィールドの名前を持つパラメーターに制限する。
SELECT * from SALES WHERE SALES.CUST_NO=:CUST_NO

この場合、たまたまマスター フィールド CUST_NO と同じフィールド名を持つ SALES.CUST_NO フィールドを使用するが、そうである必要はない。マスター データソースはマスター クエリ qryCustomers の現在のレコードを追跡するため、FPC はマスター/qryCustomers クエリの CUST_NO フィールドの現在の値への参照として CUST_NO パラメーターを確認できる。

マスター クエリにリンク すべきではない 追加のパラメーターをクエリで使用する場合は、マスター データセットを開く前に、その Bound プロパティが true であることを確認すること。doc:fcl/db/tparam.bound.htmlを参照のこと。

フィールドを検索できるように、ディテールクエリの前にマスター クエリが開いていることを確認すること。

適切な外部キーを使用したディテールレコードの追加

ディテールレコードはマスター レコードとともにスクロールしますが、追加のコードが必要である。

新しいディテールレコードを追加するとき、SALES.CUST_NO フィールドは、値を入力しない限り NULL のままである。したがって、qrySales の AfterInsert イベント ハンドラーを設定する必要がある: 

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

マスターデータを使用してフィルタリングしないディテールパラメータの使用

デフォルトでは、詳細データセット内のすべてのパラメーターの値はマスター データセットによって提供される。 詳細データセットに個別にフィルター処理する別のパラメーターがあるとする:

Master Query: Same as above

Detail query: 

SELECT * FROM SALES WHERE SALES.CUST_NO=:CUST_NO AND OVERDUE=:CREDITOVERDUE

解決策は、次のいずれかの方法を使用して、SQLDB に CREDITOVERDUE パラメータを「バインド」するように指示することである:

  1. オブジェクトインスペクタでパラメータの値を設定する。
  2. マスター データセットを開く前に、パラメーターを (値に) バインド済みとしてマークする: qryDetail.Params.ParamByName('CREDITOVERDUE').Bound:=True; Bound property documentation参照。
  3. マスター データセットを開く前にパラメーター値を設定する:

qryDetail.Params.ParamByName('CREDITOVERDUE').AsBoolean:=true;

ディテール ブックマーク

注意: TBufDataset および bufdataset の派生 (SQLQuery など)、マスター データセットのアクティブ レコードが移動するたびに詳細データセットが再ロードされる; したがって、既存のディテール ブックマークは、たとえ有効であっても、移動後は同じディテール レコードを指さなくなる。


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 SQLite Studio. http://sqlitestudio.pl

  • First 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.

The "countries" table: 

COUNTRY_ID (Primary key, integer, autoincrement)
COUNTRY_NAME (VARCHAR(50), not null)

Add two countries to the table:

  • Sweden
  • Norway

Because COUNTRY_ID is autoincrement, each country is assigned an ID automatically.

Then make the "cities" table: 

CITY_ID (Primary key, integer, autoincrement)
CITY_NAME (VARCHAR(50), 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. (The parameter name is not arbitrary: it is the relevant field name in the Master Table)

  • Set its Datasource property to Datasource1.

This does not yet allow us to add cities; to do this:

  • Please use the information under #Adding detail records with the right foreign key above.


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 (as above) the Detail Query's DataSource must be set to the Master DataSource. (DataSource1 in our example)


If you still didn't 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

Delete all Detail records if Master record is deleted

This is easy to do with Cascading Deletes. Once again, this is for SQLite, but other systems work similarly.

  • First turn on foreign keys for SQLite because it's off by default. Do this by adding FOREIGN_KEYS=ON to the Params property of TSQLite3Connection.
  • Then make sure the detail table is created so the foreign key (country_id) references the master table. In this case it is the cities table:
  try
    { Master table }
    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS countries (' +
                        ' country_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +
                        ' country_name VARCHAR(30) ' +
                        ')');
    { Detail table }
    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS cities (' +
                        ' city_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +
                        ' city_name VARCHAR(30), ' +
                        ' country_id INTEGER REFERENCES countries ON DELETE CASCADE' +
                        ')');
    { Sample data }
    Conn.ExecuteDirect('INSERT INTO countries(country_name) VALUES(''Sweden'')');
    Conn.ExecuteDirect('INSERT INTO cities(city_name, country_id) VALUES(''Stockholm'', 1)');
    Conn.ExecuteDirect('INSERT INTO cities(city_name, country_id) VALUES(''Malmo'', 1)');

    Conn.ExecuteDirect('INSERT INTO countries(country_name) VALUES(''Norway'')');
    Conn.ExecuteDirect('INSERT INTO cities(city_name, country_id) VALUES(''Oslo'', 2)');
    Conn.ExecuteDirect('INSERT INTO cities(city_name, country_id) VALUES(''Bergen'', 2)');

    Conn.Transaction.Commit;
    Conn.Close;
  except
    on E:Exception do
    begin
      TX.Rollback;
      ShowMessage( E.Message );
    end;
  end;

Thats it. From now on, all detail records are deleted automatically whenever the master record is deleted.


Note: DON'T try to turn on foreign keys this way: 

Conn.ExecuteDirect('PRAGMA foreign_keys = ON');

TSQLite3Connection starts a transaction before the first query is sent to the database, even in case of a ExecuteDirect. It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect. Source: Ludob/SQLite documentation.

One more example

There is a complete running example with source code on this forum topic:

How to Append a new line and save it in DBGrid?

http://forum.lazarus.freepascal.org/index.php/topic,42088.msg293305.html#msg293305

See also

Retrieved from "http://wiki.freepascal.org/index.php?title=MasterDetail/ja&oldid=159509"