『売上猫くん on MySQL』開発日記 - 番外9 - 外部キー制約設定でのたうちまわる

システム開発、運用に様々なトラブルは付き物で、ストレスの元。　特に「マニュアル通りにやってるはずなのにできない、直らない」というのは凹む。　それでも最近は「トラブルも経験のうち」、「(自社の)ナレッジベースに入れてノウハウにしよう」、「ブログに書いて世の中に貢献しよう(笑)」とか、達観しはじめた。が、あまり長い時間、しょんもないことで時間を浪費すると、凄まじく凹む。

さて、今回は外部キー制約の設定について。ここでも凹んだというか、疲弊した。FileMakerのリレーションのところでそれらしきことは簡単にできる。　ただ、このシステムは折角MySQLをデータベースとして使うのだから、PHPやフレームワーク等の多言語、他環境での開発も視野に入れている（というより、実は、当初はFileMaker よりもCakePHPでのWeb開発が先行していたが、大人の事情で優先順位を変更した）。　であれば、MySQL側でできることはできるかぎりMySQLに任せた方が、開発効率が（システムのパフォーマンスも）上がる。　閑話休題。


データが入力されているテーブルに外部キーを設定しようと以下を実行。

alter table neko.estimatedtls
add foreign key estimatedtls(estimateId) references estimates(ID)
ON DELETE cascade ON UPDATE NO ACTION
,add index estimateId (estimateId);


いろいろシンタクスを弄ってみたが駄目。　そこでデータが無い同様のテーブルを作ってやってみると旨くいく。どうやら既存のデータに問題があるらしいことがわかったので、テーブルを一旦空にして外部キー制約を設定、データを取り込み直そうとするとエラーが出る。　MySQL有名某サイトにそれらしい記事を発見。　苦節２日、どうにか原因は分かった。親テーブルの参照キー（ID）にない外部キー（estimateID）が子テーブルにあることが原因らしい。　そこで、そのはぐれ者の子レコードを削除しようと以下を実行。

delete from estimatedtls where
(select ID from estimatedtls where estimateId not in
(select ID from estimates))

と、「Error Code : 1093 You can't specify target table 'estimatedtls' for update in FROM clause」と出る。 どうもこういうサブクエリはUpdataやDeleteでは使用できないらしい。しかたなく、サブクエリの結果をテンポラリテーブルに一旦納めて、それをwhere句に使用することにした。

create temporary table temp(id int);
insert into temp select ID from estimatedtls
where estimateId not in (select ID from estimates);
delete from estimatedtls where Id in (select id from temp)；

結果、成功。　親テーブルに属さないはぐれ者の子レコードを削除できた。　そこで、上記の Alter table以下を再実行すると、めでたく外部キー制約を設定できた。　これにより、FileMakerのリレーションでの設定に関わりなく、見積を削除すると見積明細も自動的に削除されるようになった。
以上、めでたし。

追記（10/08/23）
MySQL Workbench 5.2.16 Beta のGUIを利用して外部キー制約を設定しようとすると、［Column］でフィールドのチェックボックスをチェックできないことがある。　どうもWorkbenchのバグのようだ。　この場合はしょうがないので、外部キーのSQL文を書いて実行すること。　尚、Workbenchの最新版では解消している可能性も。


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

『売上猫くん 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 追記＆修正

以上

『売上猫くん 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』開発日記の記事一覧