ラベル FileMaker の投稿を表示しています。 すべての投稿を表示
ラベル FileMaker の投稿を表示しています。 すべての投稿を表示

2020-03-16

FileMaker データベースをSQLスクリプトステップを使用して更新する

 FileMaker は大分以前のバージョンから ExecuteSQL関数により SELECT 句のみ実行できるようになりました。 これによりデータベースの操作に必要不可欠なCRUD(Create、Read、Upudate、Delete)の内、Readだけは、FileMaker 単体でもSQLを使用して実行できるようになりました。

 開発者、特に他言語の開発者の中には「なぜ Read はできるのに、Create、Update、Deleteはいつまで経ってもできないのか?」とお腹立ちの向きも多いと思います。 小生もその一人です。
 実は(というほどのことではありませんが)、FileMaker ODBC を使用すると、 「SQLを実行する」スクリプトステップを使用してFMのテーブルを Create(Insert)、Update、Delete することができます。
 ただ、開発者にとって非常にありがたいこのCUDを活用しているという話は寡聞にして聞きません。これは、昔の FileMaker のODBC機能がお粗末で、新しい ODBC を踏み込んで使ってみようと思う人が少ないのが一因かもしれません。小生もその一人です。

 以前、医療レセプト開発のコンサルティング業務を請け賜わったことがあるのですが、数百万行のテーブルがあり、1つのレイアウト上に多数のテーブルからデータを引っ張るといった複雑なシステムで、FileMaker のテーブルオカランスのみではすべての要求情報を1つのレイアウトに表示することはできませんでした。ご担当者はいくつのも 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
1:Client SQL
2:Server SQL
1. Update 1 field of 10K records
7.2sec
3.0sec
2.8sec
2. Update 5 fields of 10K records
33.0sec
3.2sec
2.9sec
3. Simple import of 10K records
11.3sec
6.2sec
6.1sec
※各テストはそれぞれ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倍以上高速でした。

1万件のレコードの5つのフィールドを更新

 「2.Update 5 fields of 10」では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倍高速でした。

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



「0:Script batch」のコアとなるステップは「レコードをインポートする」だけですが、このような単純な処理であっても、SQLの方が2倍弱高速でした。

過去のテスト

 小社では以前、いくつかの FileMaker 用 Web API を使用し、CRUDのテストを行っており(下記リンク参照)、その際も Create、Update、Delete については ODBC(PDO)が良い(=高速である)結果を得ています。


参考:FileMaker API別にCRUDのテストをしてみた(2016/12/01)

 今回、ODBCとSQLを実行スクリプトステップを使用しても、3年前と同様に良い結果が得られました。

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 の開発案件もマルチユーザ対応が要件であることが多いと思います。マルチユーザ対応であれば、競合発生時のエラー処理は必須です。この辺の地味な処理の重要性を発注サイドにも認識して頂き、工数を見込んで頂くと共に、検収時のチェックリストにいれましょう。

SQL実行ステップにより更新を行うメリットと課題


SQLによる更新を行うメリットをまとめると以下のようになります。

  1. 高速化
  2. スクリプトの簡略化、可読性向上
  3. 他言語からの移行組技術者は使い慣れたSQLにより開発ができる
  4. アクティブレイアウトとは無関係にコマンドを実行できる


 今回は単純な処理のみでテストを行っていますが、多数行に及ぶ複雑な更新・バッチ処理を行う場合、SQLを使用する速度的メリットはさらに大きいと思います。


 また、FileMaker は過度にレイアウト依存したシステムであるため、上記4のメリットも大きいと思います。スクリプトはレイアウトからフィールドが消去されるとエラーを起こす可能性がありますが、SQLであればその心配はありません。

スクリプトで複数レコード、複数テーブルに及ぶ処理を実装すると、いちいちテーブルが割り当ててあるレイアウトに移動し、LOOPでグルグル回しながらレコードを作成したり、更新したりするのはコーディング的にも残念ですし、画面がチラチラ無駄に切り替わるのはユーザから見ても美しくありません。


課題


 FileMaker社はWebサイトでアクセス方法別の許容ユーザ数を公開しており、ODBC/JDBC接続の最大許容値(理論値)は無制限、検証値は50(ユーザ)となっています。
今後は100~500ユーザ位を想定し、負荷が高い処理でも確実に実行されるか、テストを行いたいと思います。

以上


NuckyT



2017-11-14

FMクライアントからサーバ上のODBC/node.js経由でデータベースをCRUDする

 FileMaker(以下、FM)から FileMaker Server (以下、FMS)上のデータベースに接続し、SQLクエリでデータを CRUD する場合に考えられる方法は、通常は以下の2つです。

  1. FM クライアント機に ODBC を入れて、SQL を実行スクリプトステップを使用する
  2. FM クライアント機に ODBC は入れ、ODBCはサーバにのみ入れ、サーバサイドスクリプトで SQL を実行スクリプトステップを使用する

 1. のデメリットは、全てクライアントにODBCを入れる必要があり、保守が面倒なこと、
 2. のデメリットは、サーバサイドスクリプト内はデバッガが使用できず、またクライアントで実行する場合と動作が異なることがあること、です。
また、「\"」のエスケープが面倒で醜いというのは両者に共通した欠点と思います。

 さて、上記の2つの方法のデメリットを回避する方法として、サーバに ODBC/node.js と当方で開発した tpcsql.js を置き、FM クライアントから FMS に対して SQL を発行して CRUD する方法をご紹介します。

 以下で詳述のデータベースサーバ側でクエリ文を待ち受けする Node.js スクリプト tpcsql.js は、以下のページより無償ダウンロードいただけます。

tpcsql.js を使った CRUD テスト方法

以下、環境構築の方法を記します。

注:
  • cURLを使用するため、FileMakerクライアント は Ver.16 以降が必須となります。
  • FMSは Ver16 でのみ検証を行っています。  
  1. FMS 16 の Admin Console で ODBC/JDBC を有効にします。

  2. FM 付属の FileMaker ODBC ドライバを、サーバにインストールします。
    クライアントへのODBCのインストールは不要です。
  3. FMS サーバ環境に Node.js をインストールします。
  4. 上記のサイトより、tpcsql.js をダウンロードし、サーバ 上の任意の場所に配置します。
  5. tpcsql.js に付属の Readme.txt に従って、Node.js 関連パッケージ群をインストールします。
  6. コマンドプロンプト画面から、node tpcsql.js と入力し、Enter キーを押下します。
    以下のようなメッセージが画面に表示されたら、クエリ待ち受け状態となります。

    ここでは、Web サーバのデフォルトポート 80 で待ち受けしますが、すでに他の Web サーバが稼働中の場合は、tpcsql.js に記載されているポート番号を変更(例: 80→8080)し、再起動してください。
  7. 付属のサンプルファイル「TpcQueryTester10.fmp12」 をFMSに配置し、公開します。
    FMクライアントからアクセスできるように、必要に応じて、ファイヤウォールの設定を変更します。
  8. FMクライアントを起動し、上記7で公開したTpcQueryTester10.fmp12 を開きます。
    ログイン情報は tpcsql.js に付属のドキュメントを参照してください。
  9.  [URI]フィールドの hostname の部分を FMS16 のホスト名、または IP アドレスに変更し、“Go”ボタンを押してみてください。

    上記で、[odbcDriver]、[host]、[uid]、[pwd]、[database]フィールドは、FMS16 側から見た場合の設定をあらかじめ入力してありますので、そのまま “Go”で実行できます。
  10. 以下のようにクエリ結果が [result]フィールドに戻ってきたら成功です。
  11.  Data テーブルには 1000 件分のテストデータがあらかじめ登録されています。
    クエリ内容を変更することによって、CRUD 操作をお試しいただけます。
 本稿では データベースは FileMaker Server を使用していますが、MySQL や SQL Server など、ODBC対応であれば他のデータベースでもここに記載した方法は使用できると思います。

tpcsql.jsの他のプラットフォームでの利用

 当初、tpcsql.js は Raspberry Pi 等で iBeaconの情報を収集・加工し、リモートのデータベースをSQLで簡単にCRUDするという M2M な利用を意図して企画したのですが、「FMクライアントから使えたら便利かも?」と思い、公開することにしました。
 もともと、FMのサーバサイドで「SQLを実行」がサポートされているので、「?」な方もいらっしゃるかと思いますが、「役にたったよ」という方が現れたらウレシイです。
 尚、tpcsql.js のラズパイでの利用は、稿を改めてご紹介できればと思います。


(亀澤)

IoT/M2M関連リンク

2017-11-05

iBeacon の適用モデルを考える 3 ― 定位置ビーコン監視モデル(2)

 前稿では、iOS と FileMaker Go を使用して定位置にあるビーコンを監視する方法について記しました。この方法は簡単でとても導入しやすいのですが、ビーコンを監視する端末が多数となる場合、iPad や FileMaker の費用負担がのしかかってきます。また、数十台から百台を超える端末を管理するとしたら、iPad/iPhoneでは大変でしょう。 そこで小社では、FileMaker Go に依存せず、様々なプラットフォームで利用可能なビーコン情報収集サブシステム(以下、TpcBScan.js)を作りました。

多様なデバイスで動作する TpcBScan.js  

TpcBScan.jsは node.js、express.js、nobe.js等の node.jsファミリー群の環境下で稼働する ビーコン情報収集サブシステムで、Windows、Macintosh、iOS、Android、Linux 等をOSとするデバイスで動作します*1。デバイスはBLE対応である必要があり、BLEアダプタ未搭載の場合、BLEアダプタ(ドングル)が必要となります。
 その機能は FileMaker の RangeBeacons関数に準じますが、http リクエストに対して戻り値を返します。 戻り値にはRangeBeacons形式とJSON形式を指定できます。Macintosh/Windows の FileMaker から TpcBScan.js を利用する場合は、「URLから挿入」スクリプトステップを使用します。

*1 Windows (WebDirectを含む)、Maintosh、iPad、Linux (Raspbian) で動作することを確認しています


「FMEasy在庫 IWP/WD R1.5(開発版)」のユーザの皆様には、TpcBScan.js β版 をダウンロード頂けます。詳しくはこちらをご参照ください。(2017/11/8更新)

Raspberry Pi をビーコン監視端末にする

今回、ビーコン監視を行う端末には、安定性、廉価性、保守性に優れるRaspberry Pi(BLE と LANに対応する機種)を使用することにしましたが、BLE/LANであれば Raspberry 以外のデバイスでも構いません。

 システムを構築するにあたっては、各種処理をサーバを中心に行わせる方法と、 端末を中心に実行させる方法の2種類が想定されます。

左がWi-Fi/BLE付の Raspberry Pi Zero W(¥3,000位)、右が Wi-Fi/BLEアダプタを取り付けた Pi Zero(無印)


端末駆動型システム構成

2種類の方法の1つが「端末駆動型」(下図)で、Raspberry端末がビーコン管理に必要な情報(端末が担当するビーコンのUUID/Major/Minorのリストや 、テーブルに保存されている存否情報のリスト=ManagedBeaconsList)をアプリケーションサーバを経由しデータベースサーバから取得します。端末は担当するビーコンが発信する情報(ActiveBeaconsList)を TpcBScan.js を使用して取得します。端末は取得した ManagedBeaconsList と ActiveBeaconsList を比較し、差異があれば アプリケーションサーバに対してデータベース更新等のリクエストを行います。差異が全くない場合は、サーバに更新リクエストは行いません。端末が多くの処理を行うのに対し、アプリケーションサーバの処理はレコードの送信と更新に限られます。 この端末駆動型は端末数やビーコン数が多くサーバ負荷を軽減したい場合に有効なシステム構成と思われます。


図1

サーバ駆動型システム構成

もう一つの方法が下図のサーバ駆動型で、この場合、Rapberry 端末はサーバからのリクエストに応じて自身が担当するビーコンの情報(ActiveBeaconsList)をサーバに返すのみです。一方サーバは、上述の「監視端末駆動型」で端末が担った ActiveBeaconsList 作成処理以外の全てを行うことになります。サーバは各端末に順繰りにリクエストを送り、戻り値を処理していくため、端末が増えると「監視端末駆動型」に比べてサーバの負荷はグッと増えます。この構成は監視端末の数が少なく、サーバが過負荷にならない場合に有効なシステム構成と思われます。
図2

サーバ駆動型を FileMaker Server でやってみる

実は当初、「端末駆動型」システムでも「サーバ駆動型」システムでも、Raspberry(Linux) を使うのであれば、node.js/JavaScript や PHP 等によるWebプログラミングは必須だと思いこんでいたのですが、下図の構成であれば、FileMaker による開発のみで、「サーバ駆動型」を実現できることに気づきました。というのは FileMaker スクリプトには、「URLから挿入」と言うスクリプトステップがあり、このステップは FileMaker Server から実行できるからです。具体的には FileMaker Server から この「URLから挿入」を使用して、各 Raspberry 端末上の node.js に対して、ビーコン情報を返すように http リクエストを送ります。あとは「サーバ駆動型システム構成」で書いたように、FileMaker Server がすべての処理を行います。

図3

 ということで、小社では上図のシステム構成に基づき、多端末対応のFileMaker プロトタイプを作成し、4つの Raspberry と1つのWindows ― 計5台のビーコン監視端末を使用してテストを行いました。本来であれば500台程ビーコンを用意して、各端末に100台ずつビーコンを割り当ててテストしたいところですが、手持ちのビーコンが21台しかないため、各端末が同じ21台のビーコンを監視するように設定しています。

 下図が今回のテストで FileMaker で作成したプロトタイプの画面です。監視端末があるビーコンの情報を受信しない場合は当該ビーコンの[存否]フィールドに「×」が、受信した場合は「OK」が入力されます。


図4 FMPから“Multi-term. TpcBScan”を実行すると、後述のサーバサイドスクリプトが実行される

英語版の動画




 実際にFileMaker からサーバサイドのビーコン監視スクリプトを実行した時の FileMaker Server Console 画面が以下です。通常、[クライアント]にはコンピュータ名が表示されるのですが、サーバサイドスクリプトを実行している場合は、実行されているスクリプト名が表示されるます。 赤枠部がサーバから実行されているスクリプトで、このスクリプト([srv]subTpcB監視~)が5台の端末に対して ActiveBeaconsList (ビーコンから受信した情報)を FileMaker Server に送信するようにリクエストし、それを受信すると ManagedBeaconsList と比較して、必要に応じてデータベースの更新や、メール/SMSの送信等の必要な処理を行います。本「定位置監視ビーコンモデル」は継続してビーコンを監視し続けるモデルですので、赤枠部の各スクリプトは中止命令を受けるまで、常駐して処理を続けます。


図5

 さて、当初の予想ではこのシステム構成は サーバに大きな負荷がかかると思っていたのですが、実際にCPUのパフォーマンス(下図)を見てみると、予想ほどの負荷はかからないようです。
 FileMaker Server 16 は理論上は無制限(検証値的には500)の接続が可能ですので、理論上は無制限のビーコン監視端末を管理できます。

図6

SQL データベースの利用

前項では、FileMaker Server (FMS)は、ビーコン情報を取得し、ビーコンの存否を判断し、データベースを更新するというアプリケーションサーバとデータベースサーバの2つの機能を兼ねています。 一方、図3右下の点線部のように、ODBC対応のデータベースサーバ(以下、SQL DB)を利用する場合は、FMS をアプリケーションサーバとして使用することも可能と思われます。この場合、FMS は SQL DB に対する CRUD を担うことになりますが、CRUD には FileMaker の ESS と サーバサイドからの「SQL を実行」が使えます。以前、 FMS をSQL DB の帳票作成サーバとして使うという記事を書きましたが、FMSをアプリケーションサーバとして使用すると、いろんなことが簡単にできる可能性が広がると思います。


以上


(土屋)



関連リンク