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

2017-06-02

大容量のSQLテーブルもOK! ― FileMaker 16 を帳票作成ツールとして使う

 FileMaker Server 16 (以下、FMS16)ではPDF出力をサポートするようになりました。FileMaker Pro はもともと帳票/レポートを簡単に作成できるツールして利用されてきましたが、サーバサイドでPDFが作成できるようになり、面倒なWeb帳票の設計・実装が実に簡単、且つ短時間のうちに行えるようになりました。 データベースは FileMaker でも SQL/XMLデータベースでもOKです。 本稿では MySQL の1500万件のテーブルからデータを抽出し、遅延無くPDFを出力する方法の概要を記します。

開発・運用環境

FileMaker Pro (Advanced) 16 ― 帳票作成ツール*1
FileMaker Server 16/IIS/PHP ― サーバ*2

 *1 帳票を作り、簡単なスクリプトを書くだけであればFileMaker Pro 16(¥4万前後)で事足りますが、複雑なスクリプトを書くには開発用(デバッガ付)の FileMaker Pro Advanced (¥7万前後)が必要です。
 *2 FileMaker Server 16 はFM社直売で¥99,000 (税抜)です。

 FileMaker Server 16 (の カスタムWeb公開 =CWP)の理論上の最大接続数は 2,000、検証接続数は500 ですので(詳細はこちら)、¥20万弱で本格的な帳票開発・運用環境が構築できる可能性があります。

本稿の目的と概要図

Webブラウザ上でから 入出庫システム(バックエンドはMySQL)にアクセスし、入庫伝票をPDF形式で作成・表示することを目的とします。 帳票を構成するテーブルに1000万件単位レコードが存在する場合にも、ユーザにストレスを与えない程度の実行速度で、入庫伝票PDFを表示することも目指します。以下が実装手法の概要図となります。



実装手法

入出庫明細テーブルには 約1500万のレコードがあります。 FileMaker で数十万件を超すテーブルからデータを抽出してPDF帳票を遅延無く作成するには、上図の FMS ⇔ MySQL 間の設計がキモとなります。今回は一般的な FileMaker ESS(External SQL Source 機能)を使う手法 と、原始的な ODBC データ取込を使う2つの手法を実装・テストしました。

実装手法1 ―  FileMaker ESS を使用  データ件数が多くなると低速化する

  1. 本手法では ESS を使用し、MySQLのテーブルを FileMaker のファイル内に直接配置します。下図の「_入庫」や「_取引先_入庫」などがそれに相当し、これらはシャドウテーブルとも呼ばれます。
    ESSは FileMaker 内から直接 MySQL のテーブルを読み書きでき便利なのですが、数十万行を超す大きなテーブルを扱う時には速度が低下するなどの問題があります。ESSの問題点については、古い記事になりますがこちらを参考にしてください。

  1. 入庫伝票レイアウトの開発は FileMaker Pro (Advanced) のGUIを利用し、テーブル内のフィールドや罫線、テキストなどのオブジェクトを配置しながら作成すると、慣れた人なら 短時間で完成すると思います。  
FileMaker Pro により作成した入庫伝票イメージ

  1. 次に FileMaker のスクリプトメーカーで、PHP から渡ってきた入庫No による検索、入庫伝票レイアウトの選択、PDF保存などを行うスクリプト(「実装手法概要図」の「特定のスクリプト」)を作成します。
  2. 最後に PHPファイルからそのスクリプトを実行するように指定します。

実装手法2 ― ODBCデータ取込 を使用 データ件数にかかわらず高速

  1. 本手法では独立した FileMaker ファイルを一つ用意し、その中に FileMaker のテーブル(vw入庫伝票、下図参照)を用意します。

    このテーブルに MySQL から必要最低限のデータを取り込み、そのデータを FileMaker レイアウトを使用してPDF形式で出力します。

    注:ESS は使用せず、したがって MySQL のシャドウテーブルも配置しません。
  2. MySQL側に入庫伝票に必要なテーブル(本例では、入出庫明細、取引先、入庫、商品の4テーブル)を結合した入庫伝票用ビューを作成します。

    ビューの作成は必須ではありませんが、ビューが無いとFileMaker内に4つのテーブルを用意し、4回SELECTクエリを発行して用意したテーブルに対応するMySQLのテーブルデータを取り込む必要があります。ビューを使用すれば、テーブル、クエリー、取込とも1つ/1回で済むので、実行速度的にも有利です。特別な事情がなければ、ビューを使用しましょう。

  3. FileMaker を使用して入庫伝票レイアウトの作成します。テーブル構成が異なるので実装手法1とはその作成方法は異なりますが、FM開発者にとっては容易な作業と思います。
  4. 次にPHPから渡ってきた 入庫No を使用し、 ビューに対して SELCTクエリーを発行し、結果を vw入庫伝票テーブルに取り込み、入庫伝票レイアウトを選択して、PDFを保存するスクリプトを作成します。
  5. PHPからこの FileMaker スクリプトを実行して、PDFを作成・表示する部分は実行手法1と同様です。

テスト結果

上記2つの手法による実装をブラウザからテストしてみました。HTTPリクエストからPDFが表示されるまでの時間は以下の通りです(テストは各5回ずつ実行し、その平均値を記載しています)。

実装手法1: 77秒
実装手法2: 1.2秒 

 その差は圧倒的でした。シャドウテーブルを配置するESSは、SQLクエリーの発行を FileMaker がすべてやってくれるので便利な反面、膨大なクエリーを発行するため速度低下を招きます。 その点、 ファイル内にESSシャドウテーブルを配置せず、SQL SELECT文を自身で指定し、その結果を FileMaker テーブルに取り込む ODBCデータ取込は大きなSQLテーブルに対しては圧倒的に有利です。

思ったこと

実装手法2であれば、数千万件のデータを持つSQLデータベースの帳票作成環境として、その使用に耐えそうです。 実装手法1は10万~20万件程度のテーブルであれば利用可能と思われます。

本稿では SQLデータベースを取り上げましたが、旧FileMaker Server の Webシステムを温存しつつ、下図のように FMS16 を併行運用し、FMS 16 は PDF出力用サーバとして使用する方法も考えられます。

PDF出力をしたいが、 FM16へのアップグレードはできない場合、検討の価値はあるかと思います。





(土屋)




2016-08-30

Record navigation in FileMaker PDO Custom Web Publishing is so slow... why?


Searching records in FileMaker Custom Web Publishing(CWP) using PDO(PHP Data Objects) can become pretty frustrating as a table grows much bigger, say the table has one million records.


A table with one million records is not THAT huge when it is of a MySQL table, but it is obviously too much to handle for FileMaker if you want to do some CWP using PDO.

FileMaker is compatible with ODBC, and PDO supports ODBC.  This means you can perform SQL queries via PDO to retrieve data from FileMaker databases.

We will be talking about this issue using PDO in this post.
Here are a few things to consider if your tables have thousands of records:

  1. Do not use SELECT COUNT (*)

    select count(*) causes a server time out, and worse yet, the existing fmxdbc_listener.exe process eats up CPU usage and RAM while counting records, and then goes unresponsive.

    This is serious, because you will end up killing and starting fmxdbc_listener.exe process manually, or restarting the FileMaker Server.

    SELECT COUNT ( fieldname ) would not make the situation any better. :(
  2. Do not use the ORDER BY clause

    This also causes a server timeout and fmxdbc_lisner.exe turns into a CPU/RAM eating monster if you use the ORDER BY  clause for a large volume of recordset.
  3. Try not to retrieve a large recordset by using SELECT.

    The query response becomes worse if the found recordset is huge, even if indexed fields are specified in a WHERE clause.


How would you navigate records in a found set?


You may find some different ways to navigate records in a found set if you google it.
However, when it comes to FileMaker CWP, your options are quite limited after you consider those don't's we have already explained above.

Method 1:  Use PDO scrollable cursors


Here is a sample php script for retrieving the last record in a found set using a PDO scrollable cursor.


<?php //sets up a connection
try {

$dsn= "odbc:Driver={FileMaker ODBC};host=127.0.0.1;Database=test";
$pdo = new PDO( $dsn, "cgi", "pwd" );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );

} catch ( PDOException $e ) {

exit( "Database connection failed.". $e->getMessage() );

}

//builds a query string
$sql = "select recId, invoiceNo from invoice";

//prepares a PDO statement and runs it
$stmt = $pdo->prepare(  $sql, array( PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL ) );
$stmt->execute();

//retrieves the last record in a found set
$record = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_LAST );

//script goes on using $record....

?>


PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL defines a scrollable cursor, and PDO::FETCH_ORI_LAST moves the cursor to the last row in a found set.

You may also consider using the following predefined PDO constants:

PDO::FETCH_ORI_FIRST --- moves the cursor to the first row in a found set.
PDO::FETCH_ORI_ABS --- moves the cursor to the specified row in a found set. The absolute position is specified in the third argument.
Example: $record = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS,$absPosi);

For more information, please visit http://php.net/manual/en/pdo.constants.php


Method 2:  Use FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS Clauses


Here is a sample php script for retrieving the 5000th record in a found set using FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS clauses.


<?php //sets up a connection
try {

$dsn= "odbc:Driver={FileMaker ODBC};host=127.0.0.1;Database=test";
$pdo = new PDO( $dsn, "cgi", "pwd" );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$pdo->setAttribute( PDO::ATTR_CASE, PDO::CASE_NATURAL );

} catch ( PDOException $e ) {

exit( "Database connection failed.". $e->getMessage() );

}

//defines the offset value of 4999
$offset = 4999;
//builds a query string
$sql = "select recId, invoiceNo from invoice OFFSET ".$offset." ROWS";

$sql .= " FETCH FIRST 1 ROWS ONLY";

//prepares a PDO statement and runs it
$stmt = $pdo->prepare( $sql );
$stmt->execute();

//retrieves a record
$record = $stmt->fetch( );

//script goes on using $record....

?>

$offset = 4999 defines to go to the 4999th row in a found set.
FETCH FIRST 1 ROWS ONLY defines to retrieve one row.
Thus, the 5000th row will be set to $record variable.

The Response is SO Slow Whatever the Method is....


Practically speaking, requiring five seconds to retrieve JUST one record is simply ridiculous.
But unfortunately this happens with FileMaker CWP when tables get bigger.

We have created sample scripts using those two data retrieving methods to navigate records in a found set, in order to show you how slow the FileMaker server response gets.

Method 1:  Using  PDO scrollable cursors


First button

The response is fairly good since the query does not have a WHERE clause.


Next button

It took 5.3859 seconds just to show the second record out of 939,623 records.
This outcome is pretty disappointing but this happens when the query has a WHERE clause and the found set is huge.

In this example, cRecId > 17538 results in 922,084 records and FileMaker Server's response goes slow, even though cRecId field is indexed.


Previous button

It took 5.4028 seconds when clicking Previous button to retrieve the previous record from the very last position.
This is another disappointing outcome because this huge found set caused the slow response.

In this example, cRecId < 957160 results in 939,622 records and FileMaker Server's response goes slow, even though cRecId field is indexed.


Last button

The response is alright, since the query does not require a WHERE clause.


Method 2: Using FileMaker's OFFSET n ROWS and FETCH FIRST n ROWS Clauses


This method requires two separate queries:

  1. A query to get the record count from a table with SELECT clause.
  2. A query to set the starting position (offset)  in a found set and fetch the specified number of rows of records.

Performing two queries does not appear to be a very smart, however, there is no better way to know how many records there are in a table(especially when you want to know the total record count), and then perform OFFSET and FETCH ONLY n ROWS clauses.


First button

It took 1.0043 seconds to get the first row.
Please note the first query (QUERY1 in the figure below)  does not have a WHERE clause.



Next button

It took 5.7659 seconds just to show the second record out of 939,623 records.
In this example, the first query's (QUERY1 in the figure below) cRecId > 17538 results in 922,084 records and FileMaker Server's response goes slow, even though cRecId field is indexed.


Previous button

It took 6.854 seconds when clicking Previous button to retrieve the previous record from the very last position.

In this example, the first query's (QUERY1 in the figure below) cRecId < 957160 results in 939,622 records and FileMaker Server's response goes slow, even though cRecId field is indexed.




Last button

It took 2.0451 seconds to get the very last row.
The first query (QUERY1 in the figure below)  does not have a WHERE clause, so the response is faster than the Next and Previous buttons.


(亀/turtle)






2016-07-14

FileMaker によるレスポンシブな出庫画面プロトタイプ

 FileMaker をバックエンドに使用したレスポンシブな出庫画面を作ってみました。invoice.php という一つのファイルで PC、タブレット、スマホ(Androidを含む)に対応しています。FileMaker のようにデバイス毎にレイアウトを用意する必要が無いところが良いところです。 いまのところ照会専用(更新不可)です。しばらくの間、下記で公開しますので、興味のある方はお試しください。

レスポンシブな FMEasy在庫 プロトタイプの試用会場へ
(しょっちゅう弄っているのでエラーを起こすことがあります。スミマセン。)
Windows Edge


 以下では本プロトタイプの諸機能を説明します。が、まだ照会部分がおおよそできただけで、動作しないモノが多いです。また、エクストリーム・プログラミング(別名:行き当たりバッタリ開発)っぽく開発しているので、ここに書いてる仕様は無かったことになる可能性もあります><。ご了承ください。


オブジェクトのスライドと伸縮


 以下は iPad Air 2 を横置した場合と縦置した場合です。スクリーンの幅が狭くなると右側にある[伝票番号]などのオブジェクト群が下にスライドします。また、オブジェクトによっては、左右に伸縮します。

iPad Air 2, Safari


表示項目数が変わるアコーディオンメニュー


 同じデバイスでもスクリーン幅に応じて、縦置き、横置きでメニューに表示される項目数が変化します。下図左は Nexus 7 Android 縦置の画面になりますが、同じNexus 7 でも横置にするとメニューの項目数が増えます(図右上)。 上下にスクロールしてもこのメニューは常時表示されますが、メニューの左の青いバーをクリックするとメニューが折り畳まれ(下図右下)、商品情報表示を邪魔しないようにします。青いバーを再度タップすると、メニューは再度表示されます。

Nexus 7, Android

接続手段( API )について


 本プロトタイプには接続手段( API )を選択し、それぞれの実行速度を計測する機能が用意されています。選択できるAPIは以下の通りです。

  1. FileMaker API for PHP
  2. fx.php
  3. FileMaker Server Custom Web Publishing with XML (以降 fm.xml)
  4. PDO(ODBC)

 いずれも、バックエンドは 同一のFileMaker Server  で、アクセスするデータベースは同一の「FMEasy在庫 R1.5」となっています。


API の応答時間


 応答時間は、画面右上端に表示されます。


 
 この時間は、出庫データの問い合わせからページ表示までに要した時間を表しています。


APIの切り替え


 使用中の API 情報は、画面右上で確認できます。
 以下の例では、PDO を使用中で、このページの表示が完了するまでに 0.552 秒かかったことがわかります。




 API を切り替える場合は、メニューボタンエリアの右下端のボタンをクリック(タップ)します。
 すると、API 選択用のドロップダウンメニューが表示されます。メニューを展開させてから、希望の API を選択します。


 たとえば、FM API for PHP (専用)を選択すると、使用中の API が下図のように変更されます。



 API を切り替えてもデータの自動読み込みは行われませんので、 ボタンを実行することによって、新しい API でのデータ読み込みを行ってください。
 尚、表示幅が狭くボタンが表示されない場合は、レコード移動用のボタンや ボタンを実行してください。

注:
上記のドロップダウンメニューで(既存)は「FMEasy在庫 R1.5」の既存のレイアウトを使用し、(専用)はWebアプリに不要な要素を排除し、レイアウトテーマに「クラッシック」を使用したカスタムWebに最適化されたレイアウトを使用しています。



出庫明細部


 出庫明細部(商品情報が繰り返し表示されるエリア)の表示項目は、[項目選択…]から選択することができます。お使いのデバイスのスクリーンに合わせて、表示項目を選択してください。

その他

 ボタンにはチップツールがついているので、PCでマウスポインタを合わせるとそのボタン機能がなんとなくわかりますが、現状、多くのボタンが開発中です><

 左のボタンですが、ヘッダ部にある場合と、取引先や商品に隣接している場合があります。 ヘッダ部にある場合はクリックすると検索画面に移動するので、ユーザが検索条件を入力して実行すると検索結果が一覧表示され、目的のレコードを選択すると元の画面に戻り、選択したレコードが表示される仕様となります。一方、取引先や商品に隣接している場合は一覧表示されて選択するところまでは同様ですが、こちらの場合は選択した取引先(部署)または商品が入力されます(入力支援機能)。このあたりは、FileMaker Pro、インスタントWeb、WebDirect の機能に準じるものになる予定です。 


2016-07-06

FileMaker でレスポンシブなWebアプリを作る

 FileMaker(以下、FM)のデータベースを FileMaker Pro 以外のクライアント ― ブラウザ、iPhone/iPad、Android ― でも運用する場合、開発環境をFMにすべきか、カスタムWeb にすべきかを考えると思いますが、その際に考慮べき事項を比較表にまとめてみました。

×: terrible,  △: limited,  〇: OK,  ◎: Excellent
 
Go
WebDirect
Custom Web
開発費用
(Devlopment cost)
×
ライセンス費用
(License cost)
×
×
サーバ機費用
(Server machine cost)
×
×
ライセンス体系
(License policy)
×
×
開発の容易さ
(Ease of development)
×
コードの再利用性
(Code reusability)
×
×
開発の柔軟性
(Development flexibility)
×
×
レスポンシブ性
(Responsive Web Design)
×
×
Android等非iOS機との互換性
(Android compatibility)
×
スケーラビリティ
(Scalabilty)
×
他DBへの移植
(Migration to other DBs)


この表からは、
圧倒的ではないか、カスタムWebは

という感じですが、FMの世界では「開発費用」と「開発の容易さ」が他の要素を圧倒し、Go やWebDirect が採用されやすいのではないでしょうか。逆に言えば、他の要素が重要なプロジェクトでは、FMは使用されないのです。
 
 とは言いいつつ、 FM のカスタムWeb という需要は少なからずあり、またスマホ普及に伴う Mobile First な要望もあり、小社としてもまたーりとそれらにお応えしたい、と愚考している次第です。
 そんなわけで、『FMEasy在庫 IWP/WD R1.5』(以下、FMEasy在庫)をバックエンドに使用し、 iPhone/iPad だけではなく Android 等の非iOS デバイスにも対応したレスポンシブな Webアプリのプロトタイプ開発にトライしていきたいと思います。

開発環境

使用データベース: FileMaker(fmp12)
使用ライブラリ: jQuery、jQuery Mobile
対応デバイス: PC、タブレット、スマホ(非iOSデバイスを含む)


開発方針・目標

第1フェイズ


以下の方向で、FMEasy在庫の出庫関連機能をWebアプリ化→プロトタイプを作成する。
  1. マルチデバイス対応、RWD(Responsive Web Design)採用
    たとえばこんな感じで……。

    【PC利用時】

    Windows Chrome


    【タブレットPC利用時:縦置き】

    Nexus 7, Android, Chrome --- Portrait


    【タブレットPC利用時:横置き】

    Nexus 7, Android, Chrome --- Landscape


    【スマートフォン利用時】

    FreeTel Priori 3, Android, Chrome

  2. XML/fx.php/FM API for PHP/PDO(ODBC)の比較テストを適時実施
     →最適なAPIを決める(努力をする)
  3. 出庫伝票照会機能作成
  4. 出庫伝票追加・更新機能作成
  5. 出庫伝票検索機能及び一覧表示機能作成
  6. 出庫伝票PDF出力機能作成

第2フェイズ

  1. 第1フェイズで作成した機能をライブラリ化
  2. ライブラリを他のDBへ対応させる(PDOなら簡単なんじゃないかな)

第3フェイズ

  1. 上記ライブラリを使用することにより、お客様にレスポンシブなシステムをリーズナブルな価格で提供する
    \(^_^)/

 いろいろ書いてますが、予定は未定ですw


本ブログの関連記事について

 本ブログで今後公開予定の RWD によるカスタムWeb関連の記事は、カスタムWeb による開発を検討されているお客様に小社で開発したプロトタイプの仕様やテスト結果をご覧いただき、プロジェクト検討の一助になることを目指しています。


ということで、次回はこのプロトタイプの照会機能についてご紹介したいと思います。

(亀) 

2016-06-06

API 別/サーバ別FileMaker カスタムWebパフォーマンス比較

 FileMaker で カスタムWeb を構築する場合、どの API が一番高速なのか、と疑問を持たれてきた開発者の方も多いかと思いますが、小社もその一員です><。「FM社がバンドルしてるんだから FileMaker API for PHP なら、まぁ、間違いないだろう」位のノリでそれを採用してしまったりとか。FileMaker API for PHP(以下、FM API for PHP) や FX.php なら、ネット上の情報量も多いですし。
 そんな折、FM API for PHP を使用してWeb システムを構築していたわけですが、JMeter で想定される最大負荷をかけたところ、Web サーバがダウンしてしまうことがありました。 幸い、実運用ではそのような状況には今のところ陥っていないのですが、「これは一度、4つの API で比較テストしておこう」ということになり、今回、その運びとなりました。 また、同テストを FileMaker Server 12 と 15 でも実施しました。以下にその方法と結果を公開いたします。 1ユーザによる郵便番号データの検索と表示という限られたテストなのですが、多少なりとも参考になればウレシイかも、です。

1. テスト方法

全国の郵便番号データを入れた FileMaker データベース(今回は弊社製品『FMEasy在庫』の郵便番号テーブル)を用意し、FileMaker Server 12 と 先月リリースされたばかりの FileMaker Server 15 でそれぞれ公開。
 このデータベースにアクセスして郵便データを検索するための簡単な php ページを作成しました。

 たとえば、FileMaker API for PHP で「北海道」を含む郵便データを検索するときのイメージはこのようになります。

検索実行中!



 そして下図が実行結果となります。
 FileMaker API for PHP による「北海道」郵便データ検索結果は 8242 件(データはかなり古いので、最新のデータでは違う結果が返ると思います)で、このレコードを取得してくるのに 2.9935 秒かかったことがわかります。



2. テスト結果

FileMaker Server 12 と 15 を使用し、それぞれのサーバ上で XML、FileMaker API for PHP、fx.php、POD(ODBC) の 4 種類の API を使って 10 回ずつ「北海道」の検索を実施しました。
 その測定結果は次の通りとなりました。



 FileMaker Server 12 と 15 のバージョン間の比較では、FileMaker Server 15 のパフォーマンスが約30% 良い結果となりました。

 パフォーマンス平均をグラフ化すると、このようになります。

API 別 FMS15 vs FMS 12 パフォーマンス比較


 次に、API の比較ですが、本テストに限っては、PDO が圧倒的に高速でした。北海道に属する全8242レコードの検索・描画の最高速は 0.938秒。 正直、FileMaker の ODBC を介したアクセスは使い物にならない、と過去の経験から思い込んでいたので(その記事がこちら)、この結果は驚きでした(FMさん、御免なさい r( ̄_ ̄;))。 また、2つの php ライブラリでは、FX.php がほんの少し有利で、php ライブラリと 生XML の比較では、生XML が約40% 高速となりました。


 php ライブラリは、Web プログラマにとって扱いやすいというのがメリットですが、処理が関数化されていることによって、その分サーバのパフォーマンスが相当程度犠牲になっているようです。
少ない人数の運用では何ら問題が無かったのに、ユーザ数が増えた途端に業務に支障をきたすようになった、みたいな時は、DBへのクエリやコードの見直しに加えて、XML や PDO への乗り換えも選択肢になるかもしれません。


参考サイト:
FileMaker×PHPで作る、簡単・便利なWebアプリ ― とっても力作なサイト

過去記事:
ODBC ドライバ DataDirect SequeLink を使って FileMaker Pro にアクセスしたときの問題点


(亀)