2010-04-02

『売上猫くん on MySQL』開発日記 - 3 - FMからストアド、トリガ

単純な操作であってもFileMaker(以下、FM)からMySQLには実に多くのクエリが発せられてしまう。 これはシステムのパフォーマンスの低下を招く。 また、複数テーブルにまたがる処理とそれに伴う冗長なFMスクリプトはさらにパフォーマンスを低下させる。 よって、FMの組み込みのSQL発行機能に頼らず、ビュー、ストアドプロシージャ(以下、SP)、ストアドファンクション、トリガを用いて高速化を図ることが開発者には要求される。 (尚、高速化の手段としてい一時テーブル(Create temparary table)は、残念ながらFileMakerからは認識できない。痛い!)

◇SP(ストアドプロシージャ)を実行し、FM側で戻り値を取得する
FMには「SQLを実行」スクリプトステップがあるので、DB側で作成したSPを利用するには、クライアントPCにODBCを入れて、

   Call procedure(param1,param2...) 

と書いて、実行すればよい。 ところが問題がある。 FMはエラーを除き、実行した結果を返さない(MySQLの場合はエラーメッセージそのもので、SQLSTATEの番号は拾わないようだ)。 SQLに限らず、FM(特にWindows版)の外部アプリケーションとの連携の悪さは定評がある。実際、「メッセージ送信」を利用してVBSやコマンドプロンプトを実行しても送信しっぱなし、戻り値無し、で困った人も多いと思う。面倒な回避方法はないこともないが…
さて、今回は「SQLを実行」ステップの話である。この場合は比較的楽ちんな対策がある。SP側で結果をテーブルに書き込み、FMアプリ側から読み込めばいい。 以下、実装方法例。
  1. session(primary_key,return) テーブルを用意し、FMアプリ起動時にsession に行を追加し、FMアプリ上でそのprimary_key の値(pVal)を記憶する
  2. SPのコードの終りには、 update session set return=戻り値 where primary_key=pVal を追記する。
  3. 「SQLを実行」で、Call SP(pVal,......)  を実行。
  4. sessionテーブルレイアウトのpValのレコードに移動し、returnを取得。
かくして、FMアプリでも、SQLの戻り値を無理矢理取得できる。


◇SPをトリガから起動する
以前に書いたように上記の「SQLを実行」ステップを利用するにはクライアントPCにODBCがインストールされていなければならならい。 FMクライアント(ODBC無)→FMServer(ODBC有)→MySQL の場合、SPは使用できないのか?  答はNo、トリガからSPを起動すればよい。
具体的にはMySQL で トリガ実行専用の「trigs」テーブルを用意。FM側にもtrigsのレイアウトを作成する。trigsへのInsert発生時に上記SPが起動するように、 「CREATE TRIGGER trig_name after insert ON trigs」を書く。 最後にFM側で、上記で作成したtrigsレイアウトに移動し、レコードを新規作成(確定させた瞬間にSPが実行される)するように、スクリプトを書く。 尚、トリガでは、NEW.field を利用し、上述の pValやその他の必要情報をSPに渡す。


◇まとめ
一連の動きをまとめると以下のようになる。

FMアプリ起動→スタートアップスクリプトで、sessionテーブルにレコードを追加→sessionのユニークなpVal を取得しアプリ終了まで保持→trigsレイアウトに移動し新規レコード作成→SPが実行される→SPにより、pVal を持つ session レコードの return フィールドに戻り値が返る→その戻り値により、必要な処理を行う。

10/4/7 追記&修正

以上

2010-03-31

『売上猫くん on MySQL』開発日記 - 番外8 - insert...select構文とODBC

以下、ODBC 5.1.5だと×、5.1.6だとOK

INSERT INTO estimates(customerNo) SELECT customerNo FROM estimates WHERE ID=5

これ、痛い。 この構文使えずに困っている人、結構いるんじゃなかろうか? 回避方法も見つからないし。


参考サイト:Bug #42905


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-03-29

『売上猫くん on MySQL』開発日記 - 番外7 - FileMaker内にMySQLの一般ログを表示する

MySQL の一般ログをFileMakerで表示する方法。

1.MySQLのMySQLデータベースをDSN登録する。
2.my.ini に log-output=TABLE,FILE を指定。
3.以下をMySQLコマンドラインで実行。
mysql> SET @old_log_state = @@global.slow_query_log;
mysql> SET GLOBAL general_log = 'OFF';
mysql> ALTER TABLE mysql.general_log ENGINE = MyISAM;
mysql>ALTER TABLE `mysql`.`general_log` ADD COLUMN `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`);
mysql> SET GLOBAL general_log = @old_log_state;
4.MySQL を再起動。
5.FileMaker のリレーションシップで、上記1で作成したDSNのmysql.general_logテーブル選択。このとき、上記で作成した id を指定。(以下、略)

するとこんな感じでログを表示できる。


FMの自動発行SQLクエリで「?」が頭に点灯したら、ログをチェックするのがよろしいかと。

尚、FM標準コマンドでは general_log テーブルのログは削除できないので、truncate(テーブル内の全行削除) を使用する。以下はFMの「SQLを実行」ステップを使用して、truncate を実行。

参考サイト:一般クエリとスロー クエリのログ出力先の選択

2010/9/24 追記
ログが出力されない場合、mysqlコマンドで
SET GLOBAL general_log = 'ON';
を実行してみる。

2010/10/25 追記
id の属性に AUTO_INCREMENT を追加。

2010/10/15 追記
slow_query についても、上記の要領でFileMaker上に表示可能だが、ログ対象となるクエリ時間の閾値がデフォルトでは10(秒)になっている。この為、ログ書き込みされないと思いこんでしまう。このデフォ値は、my.ini で long_query_time = 1 とするか、set global long_query_time = 1 を実行することにより変更できる。



関連リンク:『売上猫くん on MySQL』開発日記の記事一覧