FileMaker は大分以前のバージョンからExecuteSQL関数により、SQLのCRUD（Create、Read、Upudate、Delete）の内、 Read、つまりSELECT 句のみは実行できるようになっています。
開発者、特に他言語の開発者の中には「なぜ Read はできるのに、Create、Update、Deleteはいつまで経ってもできないのか？」とお腹立ちの向きも多いと思います。 小生もその一人です。
実は（というほどのことではありませんが）、FileMaker ODBC を使用すると、 「SQLを実行する」スクリプトステップを使用してFMのテーブルを Create(Insert)、Update、Delete することができます。本稿では触れませんが、CRATE/ALTER TABLE といった DDL ― Data Definition Language も一部サポートされていて、テーブルの作成・変更も限定的に実行できます。
ただ、開発者にとって非常にありがたいこの Create、Update、Delete を活用しているという話は寡聞にして知りません。これは、昔の FileMaker のODBC機能がお粗末で、新しい ODBC を踏み込んで使ってみようと思う人が少ないのが一因かもしれません。小生もその一人です。
以前、医療レセプト開発のコンサルティング業務を請け賜わったことがあるのですが、数百万行のテーブルがあり、1つのレイアウト上に多数のテーブルからデータを引っ張るといった複雑なシステムで、FileMaker のテーブルオカランスのみではすべての要求情報を1つのレイアウトに表示することはできませんでした。元々はJavaの技術者だったご担当者はいくつもの ExecuteSQL 関数を複数のテーブルに対して実行、結果を変数に格納し、Loopに新レコード作成と値貼り付けを挟み込みむ、といった大変な開発をされていました。この時、上記のような スクリプトステップによるバッチ処理ではなく、INSERT ~ SELECT 等の利用を薦めたのですが、当方においても本手法の採用実績やテストが十分とは言えず、強くは推せませんでした。
Insert や Update を利用すれば一発でできるところを、ウインドウを開いて、レイアウトを切り替えて、ループして書き込んで、ウインドウを閉じるといった FileMaker 特有の残念な処理を黙々と組み込んでおられたわけです。
FileMaker は大分以前のバージョンからExecuteSQL関数により、SQLのCRUD（Create、Read、Upudate、Delete）の内、 Read、つまりSELECT 句のみは実行できるようになっています。
開発者、特に他言語の開発者の中には「なぜ Read はできるのに、Create、Update、Deleteはいつまで経ってもできないのか？」とお腹立ちの向きも多いと思います。 小生もその一人です。
実は（というほどのことではありませんが）、FileMaker ODBC を使用すると、 「SQLを実行する」スクリプトステップを使用してFMのテーブルを Create(Insert)、Update、Delete することができます。本稿では触れませんが、CRATE/ALTER TABLE といった DDL ― Data Definition Language も一部サポートされていて、テーブルの作成・変更も限定的に実行できます。
ただ、開発者にとって非常にありがたいこの Create、Update、Delete を活用しているという話は寡聞にして知りません。これは、昔の FileMaker のODBC機能がお粗末で、新しい ODBC を踏み込んで使ってみようと思う人が少ないのが一因かもしれません。小生もその一人です。
以前、医療レセプト開発のコンサルティング業務を請け賜わったことがあるのですが、数百万行のテーブルがあり、1つのレイアウト上に多数のテーブルからデータを引っ張るといった複雑なシステムで、FileMaker のテーブルオカランスのみではすべての要求情報を1つのレイアウトに表示することはできませんでした。元々はJavaの技術者だったご担当者はいくつもの ExecuteSQL 関数を複数のテーブルに対して実行、結果を変数に格納し、Loopに新レコード作成と値貼り付けを挟み込みむ、といった大変な開発をされていました。この時、上記のような スクリプトステップによるバッチ処理ではなく、INSERT ~ SELECT 等の利用を薦めたのですが、当方においても本手法の採用実績やテストが十分とは言えず、強くは推せませんでした。
Insert や Update を利用すれば一発でできるところを、ウインドウを開いて、レイアウトを切り替えて、ループして書き込んで、ウインドウを閉じるといった FileMaker 特有の残念な処理を黙々と組み込んでおられたわけです。
SQLを実行するステップによりFileMaker DBを更新するテスト
ということで今回、FileMaker ODBC、FileMaker Server 18、 FileMaker Pro 18 の「SQLを実行する」スクリプトステップを使用して、いくつかのテストをしてみました。その結果が下表です。
表の0:Script batch は FileMaker の通常のスクリプトステップを複数使用した場合
1:Client SQL は FileMaker の「SQLを実行する」をクライアントからサーバに対して実行
2:Server SQL は クライアントから「サーバ上のスクリプトを実行」ステップを使用し、サーバ上で「SQLを実行する」ステップを実行
「1:Client SQL」と「2:Server SQL 」では、今回のような単純なSQLクエリにおいては速度の違いはほぼありませんでしたが、サーバサイドでSQLを行う場合、サーバだけに ODBC を設定しておけば、クライアント上にODBCをインストール・設定する必要が無いのはメリットです。
1列目は実行したテスト内容で、以下で解説します。
表の0:Script batch は FileMaker の通常のスクリプトステップを複数使用した場合
「1:Client SQL」と「2:Server SQL 」では、今回のような単純なSQLクエリにおいては速度の違いはほぼありませんでしたが、サーバサイドでSQLを行う場合、サーバだけに ODBC を設定しておけば、クライアント上にODBCをインストール・設定する必要が無いのはメリットです。
1列目は実行したテスト内容で、以下で解説します。
※各テストはそれぞれ5回ずつ実行し、所要時間を平均値を載せています。
1万件のレコードの1フィールドを更新
上表の「1.Update 1 field of 10K records」では、1万レコードの1フィールドを更新しています。SQLで記述すると以下になります。
update product set "note1" = 'client sql'
これをFileMaker のスクリプトでやる場合、product テーブルが設定してあるレイアウトを開き、すべてのレコードを選択して、 note1フィールドを選択後に、「client sql」で全置換するというように、複数のステップが必要になります。全置換を使用しない場合、フィールド値設定ステップを Loop させることになりますが、これは置換ステップよりも時間を要します。
結果的にはSQLを使用した方が2倍以上高速でした。
update product set "note1" = 'client sql'
1万件のレコードの5つのフィールドを更新
「2.Update 5 fields of 10k records」では1万レコードの5つのフィールドを更新しています。SQLで記述すると以下になります。
update product set note1 = 'sql 10', note2 = 'sql 20', note3 = 'sql 30', note4 = 'sql 40', note5 = 'sql 50'
「0:Script batch」 では、置換ステップを5回実行しています。結果はSQLを使用した方が5倍高速でした。
「2.Update 5 fields of 10k records」では1万レコードの5つのフィールドを更新しています。SQLで記述すると以下になります。
update product set note1 = 'sql 10', note2 = 'sql 20', note3 = 'sql 30', note4 = 'sql 40', note5 = 'sql 50'
1万件のレコードを別テーブルにインポート
「3.Simple import of 10K records」では1万件のレコードを別テーブルにインポートしています。SQLで記述すると以下になります。
insert into invoice (prodId, prodName, price, unit, note) select ID, name, salesPrice, unit, note1 from product
insert into invoice (prodId, prodName, price, unit, note) select ID, name, salesPrice, unit, note1 from product
「0:Script batch」のコアとなるステップは「レコードをインポートする」だけですが、このような単純な処理であっても、SQLの方が2倍弱高速でした。
過去のテスト
小社では以前、いくつかの FileMaker 用 Web API を使用し、CRUDのテストを行っており（下記リンク参照）、その際も Create、Update、Delete については ODBC（PDO）が良い（＝高速である）結果を得ています。
SELECT ～ FOR UPDATE について
SELECT ～ FOR UPDATE は直接データベースを更新する構文ではありませんが、更新の前処理として重要な意味があるので、ここで触れておきたいと思います。
FileMaker®16 SQL リファレンスガイドによると、FileMaker ODBC/SQL は「FOR UPDATE 句 」をサポートしており、select ～ where ～ for update を実行すれば、where で指定された「各レコードは取得時にロックされ」る筈ですが、「SQLを実行する」ステップでは以下のようなSQLを実行しても、他ユーザは where 句で指定されたレコードを更新できてしまいます（つまりロックされない）。
select * from product where ID =1 for update [of note1, note2]
もともと「SQLを実行する」ステップで select を実行しても、文字列などの結果が返りません。サーバのログを見ても、実行直後に接続が切れています。ということで「SQLを実行する」ステップは select ～ for update （レコードロック） をサポートしていないようです。
ただ、他ユーザがあるレコードを編集している際に、 当該レコードが対象となる select ～ for update 実行すると「[FileMaker][FileMaker] (301): Record is locked by another user.」が返ります。 つまり、FOR UPDATE句により、ユーザは WHERE句 で指定するレコードセット中に編集中のモノがあるかどうかを事前に知ることだけはできます。
ちなみに、ExecuteSQL関数 はこの FOR UPDATE をサポートしていません。
更新時の他ユーザ競合エラー
複数レコードのフィールド値を更新する場合、他ユーザが更新対象のレコードを編集しているとエラーとなります。ただし、フィールド内容を置換スクリプトステップ と SQL の UPDATE ではその内容が異なります。
フィールド内容を置換ステップでは、置換対象のレコードを他のユーザが編集していると、そのレコードを除くロックされていない全レコードが置換され、FileMaker標準エラーの201（フィールドを変更できません）が返ります。置換ステップの欠点はエラーが起こったレコードが特定できないことで、これは場合によっては致命的です。このため、エラーを放置できない場合は置換ステップに替えて、処理に時間がかかりますが、Loop により各レコードを逐一更新し、エラーを起こしたレコードの主キーを記録して、ユーザに通知するか、エラーを解消するための処理を別途用意することになります。
これに対し、FileMaker SQL の UPDATE は、1レコードでもロックが発生していると WHERE句で指定されたすべてのレコードが更新されません。あたかもエラーが発生して ROLLBACKされたようにみえます。このときFileMakerは1408（拡張エラー (ODBC)、Get(最終外部エラー詳細)関数は「[FileMaker][FileMaker] (301): Record is locked by another user.」を返します。
FOR UPDATE句によりレコードセットをロックできれば、更新時の他ユーザ競合によるエラーの懸念も減少すると思うのですが、前述のような状況なのが残念です。
FileMaker の開発案件もマルチユーザ対応が要件であることが多いと思います。マルチユーザ対応であれば、競合発生時のエラー処理は必須です。この辺の地味な処理の重要性を発注サイドにも認識して頂き、工数を見込んで頂くと共に、検収時のチェックリストにいれましょう。
SELECT ～ FOR UPDATE は直接データベースを更新する構文ではありませんが、更新の前処理として重要な意味があるので、ここで触れておきたいと思います。
FileMaker®16 SQL リファレンスガイドによると、FileMaker ODBC/SQL は「FOR UPDATE 句 」をサポートしており、select ～ where ～ for update を実行すれば、where で指定された「各レコードは取得時にロックされ」る筈ですが、「SQLを実行する」ステップでは以下のようなSQLを実行しても、他ユーザは where 句で指定されたレコードを更新できてしまいます（つまりロックされない）。
FileMaker®16 SQL リファレンスガイドによると、FileMaker ODBC/SQL は「FOR UPDATE 句 」をサポートしており、select ～ where ～ for update を実行すれば、where で指定された「各レコードは取得時にロックされ」る筈ですが、「SQLを実行する」ステップでは以下のようなSQLを実行しても、他ユーザは where 句で指定されたレコードを更新できてしまいます（つまりロックされない）。
select * from product where ID =1 for update [of note1, note2]
もともと「SQLを実行する」ステップで select を実行しても、文字列などの結果が返りません。サーバのログを見ても、実行直後に接続が切れています。ということで「SQLを実行する」ステップは select ～ for update （レコードロック） をサポートしていないようです。
ただ、他ユーザがあるレコードを編集している際に、 当該レコードが対象となる select ～ for update 実行すると「[FileMaker][FileMaker] (301): Record is locked by another user.」が返ります。 つまり、FOR UPDATE句により、ユーザは WHERE句 で指定するレコードセット中に編集中のモノがあるかどうかを事前に知ることだけはできます。
ちなみに、ExecuteSQL関数 はこの FOR UPDATE をサポートしていません。
更新時の他ユーザ競合エラー
フィールド内容を置換ステップでは、置換対象のレコードを他のユーザが編集していると、そのレコードを除くロックされていない全レコードが置換され、FileMaker標準エラーの201（フィールドを変更できません）が返ります。置換ステップの欠点はエラーが起こったレコードが特定できないことで、これは場合によっては致命的です。このため、エラーを放置できない場合は置換ステップに替えて、処理に時間がかかりますが、Loop により各レコードを逐一更新し、エラーを起こしたレコードの主キーを記録して、ユーザに通知するか、エラーを解消するための処理を別途用意することになります。
これに対し、FileMaker SQL の UPDATE は、1レコードでもロックが発生していると WHERE句で指定されたすべてのレコードが更新されません。あたかもエラーが発生して ROLLBACKされたようにみえます。このときFileMakerは1408（拡張エラー (ODBC)、Get(最終外部エラー詳細)関数は「[FileMaker][FileMaker] (301): Record is locked by another user.」を返します。
FOR UPDATE句によりレコードセットをロックできれば、更新時の他ユーザ競合によるエラーの懸念も減少すると思うのですが、前述のような状況なのが残念です。
FileMaker の開発案件もマルチユーザ対応が要件であることが多いと思います。マルチユーザ対応であれば、競合発生時のエラー処理は必須です。この辺の地味な処理の重要性を発注サイドにも認識して頂き、工数を見込んで頂くと共に、検収時のチェックリストにいれましょう。
SQL実行ステップにより更新を行うメリットと課題
SQLによる更新を行うメリットをまとめると以下のようになります。
- 高速化
- スクリプトの簡略化、可読性向上
- 他言語からの移行組技術者は使い慣れたSQLにより開発ができる
- アクティブレイアウトとは無関係にコマンドを実行できる
SQLによる更新を行うメリットをまとめると以下のようになります。
今回は単純な処理のみでテストを行っていますが、多数行に及ぶ複雑な更新・バッチ処理を行う場合、SQLを使用する速度的メリットはさらに大きいと思います。
FileMaker は過度にレイアウト依存したシステムであるため、上記4のメリットも大きいと思います。スクリプトはレイアウトからフィールドが消去されるとエラーを起こす可能性がありますが、SQLであればその心配はありません。
スクリプトで複数レコード、複数テーブルに及ぶ処理を実装すると、いちいちテーブルが割り当ててあるレイアウトに移動し、LOOPでグルグル回しながらレコードを作成したり、更新したりするのはコーディング的にも残念ですし、画面がチラチラ無駄に切り替わるのはユーザから見ても美しくありません。
課題
FileMaker社はWebサイトでアクセス方法別の許容ユーザ数を公開しており、ODBC/JDBC接続の最大許容値（理論値）は無制限、検証値は50（ユーザ）となっています。
今後は100～500ユーザ位を想定し、負荷が高い処理でも確実に実行されるか、テストを行いたいと思います。
以上
NuckyT
