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 することができます。尚、本稿では触れませんが、CRATE/ALTER TABLE といった DDL ― Data Definition Language も一部サポートされていて、テーブルの作成・変更も限定的に実行できます。

 ただ、開発者にとって非常にありがたいこの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



2019-07-12

iBeacon/Raspberry Pi による室内移動体位置監視モデル 3 ~ 二円指向三点測位の拡張(TCOT)と測定結果 ~

 前稿では 碁盤目状(正方形型)に配置した Raspberry Pi (以下、端末、と言うことがあります)により iBeacon(以下、ビーコン、と言うことがあります)の信号強度(RSSI)を測定し、「二円指向三点測位」という方法でビーコンの位置を算出しました。

 本稿では前稿の「二円指向三点測位」を拡張し、正方形型に限定されず端末を自由に配置できるようにし、費用対効果(費用対測定精度)を上げ、またシステム導入施設・場所の状況に合わせた端末配置を行えるようにします。

旧版の二円指向三点測位のイメージ


 前稿の二円指向三点測位(以下、旧版、と呼びます)では端末を正方形型に配置し(図1のA~I)、ビーコン(図1のB)のRSSIを測定して端末とビーコン間の距離(=半径)を算出しました。通常の三点測位では3円が重なっていないとエラーとなりますが、本測位モデルでは C1 と C2 (図では E と F)で x 座標を、C1 と C3(図では E と B) で y 座標を算出することにより、ビーコン(B)の位置と推定しました。

図1:二円指向三点測位(旧版)のイメージ
Image of 2 circles-oriented trilateration (old ver)
 なお、旧版の二円指向三点測位については前稿を参照してください。

二円指向三点測位の拡張(TCOT)


 旧版の問題の一つは、端末が常に正方形型に配置されて、位置測定に関わる各端末が水平、及び垂直に存在することを前提にしていた点です。このことは保守及びコスト面の問題につながります。
 そこで今回は端末を柔軟に配置できるように二円指向三点測位を拡張(この拡張版を以後、TCOT と呼ぶことがあります)してみました。

TCOTのイメージ

 TCOT について説明します。図2は図1の各正方形の中心部に一つずつ端末を追加したものです。13個ある端末(A~M)のうち、ビーコン(B)を最も近距離に感知した端末は、より近距離に感知した順に E(円C1)、K(円C2)、B(円C3)だったとします。

 このとき、C1とC2に着目します(二円指向)。センタ無の正方形型配置のときは、C1 と C2 から x 軸(横軸)のみを算出しましたが、今回は 円C1 と 円C2 が垂直に位置しないため、この時点で x 軸を決定してしまうのは抵抗があります。そこで、円C1と円C2の交わった青いエリアをビーコン位置の候補とします。
 次に円C3とその半径により、青いエリア内からビーコンの位置を特定します。円C3が青いエリアまで届いていないため、青いエリア内で円C3に最も近いの地点をビーコンの位置と決定します。
 C1とC2で候補エリアを、C3がそのエリア中から最終座標を決定する、というのがTCOTのおおまかなイメージです。
図2: 二円指向三点測位拡張版 (TCOT)

グリッドタイプ

端末の配置型をグリッド、或いはグリッドタイプと呼びます。

図3:グリッドタイプ

Grid type
 
 「正方形+センタ型」は端末の増加を抑えつつ、測位の精度向上を目的とします。例えば、16mの「正方形型」で測位精度が出ない場合、8mグリッドに変更するのは費用的且つメンテ的にも大変なので、各正方形の中心に端末を追加することによって、測位精度向上を図ります。

 「正三角形型」がグリッドはシステム導入場所の形状に合わせて使用します。

 TCOT は「正方形+センタ型」や「正三角形型」に対応しますが、1つのグリッドタイプに限定されず、端末を自由に配置できるように設計されています。
 尚、本稿では、正三角形型は取り扱いません。
 TCOT の計測結果を記す前に、今回行ったテストのシステム構成概要を記しておきます。

システム構成概要

製品名説明
iBeaconAplix社製 MyBeacon® Pro MB004Ac-DR2、50台(補正用固定ビーコンを含む)
Raspberry Pi Zero W/WH(受信機)小型コンピュータ、iBeacon が発する信号の受信端末として13台を使用。node.js 参照
SQLite 3データベース、node.js 参照
Python 3/sym.py開発プラットフォーム、Raspberry Pi がデータベースに記録した情報をもとに、二円指向三点測位(TCOT)を実行するプログラムを開発。
node.js
・開発プラットフォーム、iBeacon からの信号受信、加工、データベースへの書き込みを行うプログラムを開発。本プログラムは Raspberry Pi 上に常駐し、PC等のクライアントのリクエストに応じて上記処理を実行する。
・plotly と連動して Raspberry Pi、ビーコンの位置情報をプロットするプログラムを開発。本プログラムはサーバ上に常駐し、PC等のWebクライアントのリクエスト応じて上記処理を実行する。
plotly.js node.js 参照
Fabric PC等クライアントから複数の(本テストでは13台の) Raspberry Pi に対して一斉にコマンドを実行するツール。  


計測テストの結果


写真1:計測テストの現場
今回は上述のグリッドタイプで「正方形型」と「正方形+センタ型」を対象にして、グリッド幅をそれぞれ 4m、8m、16m、32m* に設定してテスト測定を実施しました。
 「正方形型」については二円指向三点測位の旧版を、「正方形+センタ型」については二円指向三点測位の TCOT を適用しています。
 旧版では 41個のビーコンを、TCOT では 37個ビーコンを計測対象としています。旧版と TCOT で数が違うのは、TCOTでは4個を補正用ビーコン(固定ビーコン)として追加使用しているためです。

 下の2つの図がその結果で、ビーコンの実際の座標と計測された座標の誤差に関する値を数値及びグラフで表しています。

 個々のビーコンの実際の位置座標と、計測された座標の誤差(Distance error)は下記の式で算出しています。

Distance error = |actual_x - estimated_x| + |actual_y - estimated_y|

 actual_x, actual y がビーコンの実際の座標、estimated_x, estimated_y がシステムにより算出された座標です。この値が0であれば、実際の座標と計測された座標が一致していることを示します。個々のビーコンの誤差を求め、その平均値(Error Ave)、最大値(Error Max)、エラー率を表示しています。尚、最小値は旧版、TCOT 共に おおよそ 0 となったので、省略しています。

 エラー率(Error rate)は下記の式で求めています。

Error rate = Error Ave / (Grid distance *2)

 この数値は、誤差の平均値をグリッド距離(水平+垂直)で割って誤差率を算出しています。この数値が小さいほど、グリッド距離に比してビーコン位置が良り正しく算出されていることになります。

図4: 旧版/9端末/正方形型で実行時の誤差
Distance errors when using old ver, 9 terminals, square grid + cnter
* 32mグリッドは16mグリッドのデータを転用し、B, D, E, F, H の端末を除外して4つの端末のデータのみを使用して座標を算出しています。

 上図は二円指向三点測位の旧版を使用していることに留意してください。

 誤差率はグリッド幅が広くなるに従い低くなります(改善する)が、32mグリッドでは高くなります(つまり悪化します)。これは今回のテスト環境では、ビーコン信号と端末間の距離が30m位になると、もともと精度が低いRSSIがさら劣化することを示唆しています。

図4: TCOT/13端末/正方形型+センタ で実行時の誤差


* 32mグリッドは16mグリッドのデータを転用し、B, D, F, H, J, K, L, M の端末を除外して5つの端末のデータのみを使用して座標を算出しています。


 上図は二円指向三点測位の拡張版 TCOT を使用していることに留意してください。

 正方形型と旧版による測位よりも、正方形 + センタ型と TCOT(ver01) による測位の方が計測精度が向上しています。

 下図は旧版と TCOT(図では ver01) を比較したグラフです。 


赤線は改善率を示しています。旧版に比し TCOT(ver01) が 15%~30%超改善していることがわかります。


個別プロット

 上記のTCOTにより取得・生成されたビーコンの位置情報データをプロットしたファイルを、以下に公開しました。 TCOTは旧来の三点測位とは異なり、3円が交わらなくても位置座標を算出しますが、このプロット図ではTCOTが結果が可視化されています。

二円指向三点測位バージョン
(TCOT ver. used)
グリッドタイプ
(Grid type)
グリッドサイズ (Grid size) ダウンロード
 (Download)
旧版(Old) 正方形(Square) 4m × 4m 4mGrid_R9I_old.pdf
旧版(Old) 正方形(Square) 8m × 8m 8mGrid_R9I_old.pdf
旧版(Old) 正方形(Square) 16m × 16m   16mGrid_R9I_old.pdf
旧版(Old) 正方形(Square) 32m × 32m   32mGrid_R9I_old.pdf
TCOT(ver01)  正方形+センタ(Square + center) 4m × 4m 4mGrid_R13c_ver01.pdf
TCOT(ver01)  正方形+センタ(Square + center) 8m × 8m  8mGrid_R13c_ver01.pdf
TCOT(ver01)  正方形+センタ(Square + center) 16m × 16m 16mGrid_R4Ir_ver01.pdf
TCOT(ver01)  正方形+センタ(Square + center) 32m × 32m 32mGrid_R5c_ver01.pdf


全ビーコン一括プロット(2020/7/27追記)

 上記の個別プロットとは異なり、本プロット図にはすべてのビーコンの実際の位置とTCOTにより算出された推定位置が表示されます。

 TCOTには個別プロットと全ビーコン一括プロットの機能があります。

凡例:● の矢印の出元のは実際のビーコンの位置、矢印先のは推定位置、は補正用固定ビーコン


TPC ISP (Protoype) 


 上記のテストを自動化するため、TPC IPS の屋内測位システムのプロトタイプを開発しています。TPC IPS は大きく分けてFetchB と TCOT という2つのモジュールで構成されます。


FetchBについて

 FetchB は アプリケーションサーバ上に配置され、Raspberry Pi(受信機、RP)にビーコンのスキャンを行なわせ、RPが取得したUUIDやRSSI等のデータをデータベースに記録します。
 FetchB は1回の実行で多数の RP に対してコマンドを実行することができます。


TCOTについて

 TCOT は FetchB によりデータベースに記録されたビーコン情報を抽出し、上述の二円指向三点測位に基づく座標計算を行い、各ビーコンの座標を出力あるいはプロットします。 以下の動画では TPC IPS の TCOTモジュールについて解説します。




以上



(土屋)



IPS関連のBlog記事