MasterDetail/ja

概要

リレーショナル データベースでは、マスター/ディテールの概念は 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 など)、マスター データセットのアクティブ レコードが移動するたびに詳細データセットが再ロードされる; したがって、既存のディテール ブックマークは、たとえ有効であっても、移動後は同じディテール レコードを指さなくなる。

---

国と都市の例

このチュートリアルは SQLite を使用して作成されたが、原則は他のデータベースでも同じである。

もしSQLiteの管理プログラムを持っていない場合、SQLite Studio http://sqlitestudio.pl を推奨する。

• 初めに新しく SQLite データベースを作り、database.db3として保存する。

(別のデータベース システムを使用する場合は、慣れているようにテーブルを作成し、例に適切なコンポーネントを使用するだけだ)。

ここで、2つのテーブルを作る

「国」テーブル:

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

テーブルに2つの国を追加する:

• Sweden
• Norway

Because COUNTRY_ID が自動的に加算されるので、それぞれの国は自動的にIDがふられる。

「都市」テーブルを作る:

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

3つの都市をこのテーブルに追加する:

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

空のテーブルに最初にスウェーデンが追加されたため、スウェーデンは COUNTRY_ID 1 を持ち、ノルウェーは COUNTRY_ID 2 を持つ。追加する都市ごとに、COUNTRY_ID を指定する必要がある。COUNTRY_ID によってテーブルがリンクされ、マスター/詳細の概念が可能になるためである。

• Lazarus で新しいプロジェクトを作成し、MasterDetail として保存する。
• TSQLite3Connection をフォームに配置する(SQLdb tab)。
• TSQLite3Connection の DatabaseName プロパティに、作成したばかりの SQLite データベース ファイルのフル パスとファイル名を入力する。
• TSQLTransaction をフォームに配置する(SQLdb tab)。
• そのデータベース プロパティを: SQLite3Connection1へ。
• TSQLite3Connectionに戻り、TransactionプロパティをSQLTransaction1に設定する。
• TSQLite3Connection.Connected を true にする。

これでデータベースに接続できるようになり、次のステップに進むことができます。

• TSQLQueryをフォームに配置(SQLdb tab)。
• そのデータベースプロパティを: SQLite3Connection1にする
• Transaction プロパティを: SQLTransaction1にする。
• SQLステートメントに、以下を入力する:

select * from countries

• TDataSourceをフォームに配置(Data Access tab)。
• このデータセットを: SQLQuery1にする。

• TDBGridをフォームに配置(Data Controls tab)。
• そのDataSourceプロパティを: DataSource1へ設定。

• SQLQuery1 に戻り、Active を trueにする。

国テーブル (マスター) の内容が DBGrid1 に表示されるようになる。

• 次に都市テーブルに進みます(ディテール)
• 2つ目のTSQLQueryをフォームに配置(SQLdb tab)。
• そのデータベースプロパティを: SQLite3Connection1へ設定する。
• そのTransaction プロパティを: SQLTransaction1へ設定する。
• SQL ステートメントに、以下を入力する:

select *
from cities
where cities.COUNTRY_ID = :COUNTRY_ID

COUNTRY_ID のコロン記号は、これが変数パラメーターであり、値が他の場所から取得されることを意味する。それはマスター データソースから値を取得する。したがって、この例では、:COUNTRY_ID 値はマスター データソースである DataSource1 によって提供される(パラメータ名は任意ではない。マスターテーブル内の関連するフィールド名である)。

• そのDatasourceプロパティをDatasource1

これではまだ都市を追加できない、これをする:

• 上記の #正しい外部キーを使用した詳細レコードの追加 にある情報を使用のこと。

ディテールビューを続行する:

• 2つ目のTDataSourceをフォームに配置。
• そのDataSetプロパティをQuery2 (ディテール クエリ)にする。
• 2つ目のDBGridをフォームに配置(ディテール グリッド)にする。
• そのDataSourceを DataSource2 (ディテール ソース)にする。

次に、グリッドにデータを流し込む。 これらをすべてアクティブ/接続済みに設定する:

• DBConnection
• Transaction
• Query1
• Query2

これで、DBGrid2にディテールデータがあるはずだ。

覚えておくべきことは、(上記のように) 詳細クエリのデータソースをマスター データソースに設定する必要があるということである (この例では DataSource1)。

それでも理解できなかった場合は、次の連結スキームを見ること:

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

マスターレコードが削除された場合は、すべての詳細レコードを削除する

これは、カスケード削除を使用すると簡単に実行できる。 繰り返すが、これは SQLite 用だが、他のシステムでも同様に動作する。

• SQLite の外部キーはデフォルトでオフになっているため、まず SQLite の外部キーをオンにする。 これを行うには、TSQLite3Connection の Params プロパティに FOREIGN_KEYS=ON を追加する。

• 次に、外部キー (country_id) がマスター テーブルを参照するように詳細テーブルが作成されていることを確認する。この場合、それは都市テーブルである:

  try
    { マスターテーブル }
    Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS countries (' +
                        ' country_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +
                        ' country_name VARCHAR(30) ' +
                        ')');
    { ディテールテーブル }
    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' +
                        ')');
    { サンプルデータ }
    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;

以上である。 今後は、マスター レコードが削除されるたびに、すべての詳細レコードが自動的に削除される。

注意: この方法で外部キーを有効にしようとしないこと:

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

TSQLite3Connection は、ExecuteDirect の場合でも、最初のクエリがデータベースに送信される前にトランザクションを開始する。 複数ステートメントのトランザクションの途中では (SQLite が自動コミット モードではない場合)、外部キー制約を有効または無効にすることはできない。 そうしようとしてもエラーは返されない。 それは単に効果がない。 出典: Ludob/SQLite ドキュメント。

さらにもう1つの例

このフォーラムのトピックには、ソース コードを含む完全な実行例がある。

新しい行を追加して DBGrid に保存する方法

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

以下も参照のこと

• http://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html Documentation on using parameters, including implementation for master/detail
• http://docwiki.embarcadero.com/RADStudio/XE3/en/Establishing_Master-detail_Relationships_Using_Parameters The documentation for the equivalent Delphi feature
• http://forum.codecall.net/topic/62683-master-detail-datasets/
• Lazarus Tdbf Tutorial#Master table relations Example of using master/detail with the TDBF components
• SQLdb Tutorial0 - Set up instructions for sample data for the subsequent database tutorials. Has master/detail tables