FileMakerの全テーブルバックアップを自動かつ定期的にテキスト形式で取る #FileMaker

サンプルファイル

https://www.dropbox.com/s/wow1qe5l16z56lp/export.zip?dl=0
他のFMP12ファイルからテーブルとスクリプトをインポートすることで、モジュールとして使用可能です。

やること

ExecuteSQLで、運用中の全テーブルの全フィールドの内容を、テーブルごとにテキスト形式で保存してログを取る
作成したテキストファイルを、ZIPで圧縮して保存する。テキストファイルは削除する。
以上の２点をWindows版 FileMaker Pro とフリーソフトのみで行う（有料プラグインとか余計なお金を発生させない）

作成した動機

バックアップを横断して検索したい

FileMaker Serverでデータベースを運用している場合、サーバーサイドでスケジュールを作成しておけば「毎日」「毎時」などのような定期バックアップを簡単に取ることができます。設定次第で運用中のデータベースの他に、データベースの内容を空にした「クローン」ファイルを作成したりと、通常データベースを運用する上では困ることはほぼ無いといえる強力なバックアップ機能を有しています。

ところが、このスケジュールで作成されるバックアップは、下に記載したようなディレクトリ構造を持つ、そのスケジュールごとに個別のFileMaker（*.fmp12)ファイルです。運用中のデータベースに何かしらの障害が発生し、バックアップに差し替えるなどの場合は手軽に実行できて良いのですが、時にはこれでは困ることも有ります。


>FileMaker バックアップで作成されるファイルツリーの例
>    ├─バックアップ（グループ１）_2016-01-10_2300　//バックアップ実行単位で作成（この場合毎日23:00に実行）
> │  │  ├─Cloned_by_FMS_2016-01-10_2300　        //クローンのバックアップ
> │  │  │  └─Databases
> │  │  │      └─グループ１
> │  │  │              Database1クローン.fmp12
> │  │  │              Database2クローン.fmp12
> │  │  └─Databases //データベース本体のバックアップ
> │  │      └─グループ１
> │  │          │  Database1.fmp12
> │  │          │  Database2.fmp12
> │  │          └─RC_Data_FMS                    //外部保存オブジェクト（画像など）のバックアップ
> │  │              └─Database1
> │  │                  └─Photostock
> │  │                      └─写真管理テーブル
> │  │                          └─写真
> │  │                                  RIMG0367.jpg
> │  │                                  RIMG0377.jpg
> │  │                                  RIMG0421.jpg

例えば、「入力したはずのレコードが消失している」現象が起きた場合、これらのバックアップ群から「いつ消失したか」を特定することは削除時にログを取るような仕様にでもしていない限り、非常にコストがかかる作業になります。

FileMakerの標準機能では、Grepのように複数のファイルから検索できません。行う場合は１ファイルごとにリレーションシップを設定するか、インポート用の別のデータベースを作成し、バックアップファイルごとにインポートを実行し、どのファイルからインポートしたかをレコードに記入して識別するなどの工夫が必要です。どちらも手動で行わなければならず、メンテナンスや検証のために逐一このような作業を行うのはコストがかかるので別の方策を考えなければなりません。

こうなると潔く全テーブル内容をテキストでバックアップしておいて、必要時にテキストエディタなどのGrepが使えるツールで検索実行をしたほうが低コストです。しかし、問題はどのように実装するかです。

作成手順

テキストファイルでログを保存するスクリプトの作成

FileMakerでもテキスト形式（CSV、タブ区切り、Mergeファイル）でのエクスポートは標準機能で備わっており、スクリプトから実行することもできますので、Server側でスケジュールを設定しておけば、前述のバックアップのように定期的にテーブルの全内容を書きだしたテキストファイルをバックアップとして保存することはできます。

しかし、スクリプトによるエクスポートでは対象となるテーブルとフィールドを下のようなダイアログで予め設定しておかなければならず、変数的な設定ができません。

つまり、スクリプトステップがテーブルの構造に依存することになるので、ファイル内部に複数のテーブルがある場合は、バックアップしたいテーブルごとに逐次スクリプトステップを設定することになります。また、ファイル内で保持するテーブル数・フィールド数に増減があった時に都度スクリプトステップ設定を変更しなければならない、とメンテナンス性の悪いスクリプトになる予感がします。また別の方策を考えなければなりません。

ExecuteSQLによるテーブル、レコードの取得

FileMaker関数の中にはExecuteSQLという関数があり、本来設定しなければならないテーブルオカレンスごとのリレーションシップが無くとも（どこからでも）テーブルの内容をSQLクエリで抽出した値を得ることが出来るという便利な関数があります。

ExecuteSQLではテーブルに保存された値のみならず、FileMaker システムオブジェクトという、ファイル内のテーブル構造やフィールド構造を得ることができます。具体的な例として、次のような計算式になります。

ExecuteSQL.fmp12

//ファイルに設定されているベーステーブル名を重複を除いて取得
ExecuteSQL("SELECT DISTINCT BaseTableName from Filemaker_Tables";"";"")

//あるテーブル($tablename)に設定されている集計・計算タイプを除いたフィールド名を取得
ExecuteSQL("SELECT FieldName FROM FileMaker_Fields WHERE TableName = ? AND FieldClass='Normal'  ";"";"";$tablename)

これらの式で得られた値を基に、Loopで各ベーステーブル（テーブルオカレンスの基となるテーブル。「データベースの管理／テーブルで作成するテーブル）の内容をExecuteSQLで取得し、逐次テキストに書きだすのがよさそうです。

重複を除いたテーブル名の一覧が取得できれば、ワイルドカードを用いてフィールド内容の全てを取得すれば良いような気がしますが、フィールドの中には「計算」「集計」タイプのものがあった場合、ワイルドカードではこれらのフィールドも取得の対象としてしまいます。この２タイプのフィールドは値を都度計算してエクスポートするため、処理に時間がかかる上に、エクスポートしても意味が無いことがほとんどですので除外したほうが良いでしょう。SQLクエリのWHERE句で FieldClass='Normal'を指定することで除外できます。

こうしてバックアップするべきテーブル名とフィールド名が取得できますので、あとは次のようなスクリプトでテーブルごとLoopを回して取得した値を書き出せば、テーブル毎のテキストファイルにエクスポートが可能です。

export.fmp12

001: レイアウト切り替え [ 「SYS_BACKUP」 (SYS_BACKUP) ]
002: 変数を設定 [ $dirpath; 値:"Z:\log\textfile\\" //ログを出力するディレクトリパス ] 
003: 変数を設定 [ $dir; 値:Let([ %head = "file:/" ]; %head & Substitute ( $dirpath ; "\\" ; "/" ) ) //FileMaker用にパスを加工 ]
004: 変数を設定 [ $date; 値:Substitute(Get(タイムスタンプ)&"_"&Get(アカウント名);[" ";"-"];["/";"-"];[":";"-"]) //出力するファイル名]
005: 
006: #全テーブル名を取得
007: 変数を設定 [ $exclude_table; 値:"'SYS_BACKUP','TEST_TABLE'" //除外するテーブル]
008: 変数を設定 [ $table_list; 値:Let ( [ notin = "WHERE BaseTableName NOT IN("& $exclude_table &")"; query   = " SELECT  BaseTableName FROM  Filemaker_tables "&notin ] ; ExecuteSQL ( query ; "" ; "" ) ) ]
009: 変数を設定 [ $vc_table; 値:ValueCount($table_list) ]
010: 変数を設定 [ $i; 値:1 ]
011: #LoopでテーブルごとのデータをExecuteSQLで取得し、ファイルに書き出す
012: 
013: 変数を設定 [ $i; 値:1 ]
014: Loop
015:   Exit Loop If [ $i > $vc_table ]
016:   #出力するデータのヘッダ（フィールド名）を取得
017:   変数を設定 [ $table_name; 値:GetValue ( $table_list ; $i ) ]
018:   変数を設定 [ $header; 値:Let ( [ query  = " SELECT  FieldName FROM  Filemaker_Fields WHERE  TableName = ? AND FieldClass = 'Normal' " ] ; ExecuteSQL ( query ; "" ; "" ;$table_name) ) ]
019:   変数を設定 [ $vc_header; 値:ValueCount($header) ]
020:   変数を設定 [ $j; 値:1 ]
021:   #ヘッダをSQLに適したフォーマットにする
022:   Loop
023:     Exit Loop If [ $j > $vc_header ]
024:       変数を設定 [ $sql_header; 値:If($j > 1;$sql_header & ",") & Quote(GetValue($header;$j)) ]
025:       変数を設定 [ $j; 値:$j + 1 ]
026:     End Loop
027:     #取得したフィールドの値に含まれる改行区切りデータを「|」で置換する
028:     変数を設定 [ $dump; 値:Let ( [ Table = Quote ( $table_name ); Field = $sql_header; query  = " SELECT  "& FIELD &" FROM  "& TABLE &" " ] ; Substitute ( Substitute ( ExecuteSQL ( query ; Char(9) ; "~~~" ) ; "¶" ; "|" ); "~~~" ; "¶" ) ) ]
029:     If [ ValueCount ( $dump ) >= 1 ]
030:       #ヘッダとデータを結合し、Tabファイルにエクスポートする。
031:       変数を設定 [ $dump; 値:Substitute ( $header ; "¶" ; Char(9)) &"¶"& $dump ]
032:       変数を設定 [ $path; 値:$dir & $date &"_"&$table_name&".tab" ]
033:       変数を設定 [ $pathlist; 値:If ($i > 1;$pathlist &"¶")& $pathlist ]
034:       フィールド設定 [ SYS_BACKUP::TEMP; $dump ]
035:       フィールド内容のエクスポート [ SYS_BACKUP::TEMP; 「$path」 ]
036:     End If
037:     変数を設定 [ $i; 値:$i + 1 ]
038: End Loop
039: 
040: #7zをコマンドラインから実行 & 圧縮されたTABファイルを削除（7zのディレクトリを環境変数PATHに通しておくこと）
041: フィールド設定 [ SYS_BACKUP::TEMP; "7za a "& $date &".zip *.tab" ]
042: Event を送信 [ ファイル/アプリケーションを開く; "cmd /c 7za a "& $dirpath & $date &".zip " & $dirpath &" *.tab & del " & $dirpath &"*.tab" ] [ アプリケーションを手前に表示 ]

スクリプトの流れとしては、

書き出しテキストを一時保存するためのグローバルフィールドを含むレイアウト（SYS_BACKUP)に移動
ログを出力するディレクトリ名、保存するファイル名等を設定
ExecuteSQL関数でベーステーブル名を取得し、Loopでテーブルごとにフィールド名を取得
7zipをコマンドラインから立ち上げ、ZIPで圧縮した後にTabファイルを削除

となっています。本来はエラー処理やZIP圧縮、削除時に排他処理が必要になりますが、ここでは省略しています。また、出力したコマンドラインからZIP圧縮を行うために、7-Zipのコマンドライン版[7za.exe]を利用することにしました。各クライアントPCの適当な場所に[7Za.exe]を保存し、PATHを通しておきます。

この方式であれば、ExecuteSQLで取得した値はグローバルフィールドに書き込まれ、「フィールド内容のエクスポート」で逐次テキストファイルに書きだされます。書き出し用のグローバルフィールドをどこかに１つ作成しておけば、データベース構造に変更があった場合でもスクリプトの修正する必要がなくなりますし、モジュールとして他のデータベースファイルにも応用できますので、メンテナンス作業コストが下がることが期待できます。

定期的な実行

あとはこのスクリプトを定期的に実行すれば良いのですが、問題は「フィールド内容のエクスポート」がServerと互換性のないスクリプトステップなので、Server側でスケジュールを設定してバッチ処理を行うことはできません。したがって、クライアント側で定期的に実行するトリガを仕掛けておくことで定期的な実行を確保することにします。

具体的には、ファイルオプションのスクリプトトリガ「onLastWindowClose」に対して設定すれば、起動１回（終了）毎にZIPファイルが作成されます。運用の仕方にもよりますが、通常はこの方法でログを取れば、問題発生時に検証が出来るテキストファイルが生成されるはずです。頻繁に終了を行う場合は、スクリプトの先頭でバックアップの実行可否をカスタムダイアログで問い合わせるなどの対処をすればよいかと思います。