FileMakerからMySQLへのMigration

JDBCでFileMakerのDBスキーマを移行してデータをCSVでインポートした備忘録(Windows)。

### FileMakerのDBスキーマをMySQLに移す

1. ローカルでFileMakerファイルを開いて、ファイル - 共有設定 - ODBC/JDBCを有効にする - ODBC/JDBC共有をオン - 現在開いているファイルから移したいテーブルのあるテーブルオカレンスを選択して「すべてのユーザ」をチェック、ファイル - 管理 - セキュリティ - 詳細設定 - 拡張アクセス権タブ でfmxdbcが許可されていることを確認
2. [SQLWorkbench/J](　https://www.sql-workbench.eu/downloads.html) をインストール、fmjdbc.jar（FileMaker ProインストーラのExtrasフォルダにある）をSQLWorkbench64.exeと同階層にコピー後に起動して、File - Manage Drivers... でFileMakerのJDBCドライバを登録しておく
3. Select Connection Profileの画面でDriverは登録したFileMakerのドライバを選択、URLにjdbc:filemaker://localhost/iWanIF のように入れ、User NameとPasswordを設定してOKで接続する
4. 無事接続できると左側にテーブルオカレンスの一覧が出るので選択してSQL sourceタブをクリックして欲しい部分をコピーしてテーブル名.sqlなどのファイル名で保存
5. このままではMySQLで実行するとエラーになるので1行目のDROP TABLEの後に「 IF EXISTS 」を入れ、NumberはIntで置換（小数点以下が必要ならFloat、FlagはBooleanで、ContainerはMediumTextなど）、DEFAULTの計算式は削除して保存する
6. 計算やグローバルフィールドなどの不要なフィールドを削除、なぜか並びがバラバラなので整えて保存する
7. 作成した.sqlはコマンドで実行するもよし、SQLWorkbench/Jのクエリタブにペーストして実行するもよし（Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB が出る場合はコマンドで以下を実行）
```
mysql> SET GLOBAL innodb_strict_mode = 0;
```
### オブジェクトフィールドについて
移行例としてBase64への変換方法
オブジェクトフィールド1個につき5個のフィールドを作成（以下はフィールド名がimageの場合）してcsvでエクスポートする（計算フィールドにすると大変なことになるのでデータはスクリプトのLoopでオブジェクトをセットし直すなど必要に応じて作成）

|フィールド名|タイプ|オプション|計算値|
|---|---|---|---|
|Thumb|オブジェクト|計算値自動入力 既存値を置き換え|GetThumbnail ( image ; 150 ; 150 )|
|Ext|テキスト|計算値自動入力 既存値を置き換え|Let ( [ ~dot = "."; ~filename = GetContainerAttribute ( image ; "filename" ) ; ~dotcount = PatternCount ( ~filename ; ~dot ) ; ~dotstart = Position ( ~filename ; ~dot ; 0 ; ~dotcount ) + 1 ; ~ext_count = Length ( ~filename ) - ~dotstart + Length ( ~dot ) ] ; Middle ( ~filename ; ~dotstart ; ~ext_count ) )|
|ThumbExt|テキスト|計算値自動入力 既存値を置き換え|Let ( [ ~dot = "."; ~filename = GetContainerAttribute ( Thumb ; "filename" ) ; ~dotcount = PatternCount ( ~filename ; ~dot ) ; ~dotstart = Position ( ~filename ; ~dot ; 0 ; ~dotcount ) + 1 ; ~ext_count = Length ( ~filename ) - ~dotstart + Length ( ~dot ) ] ; Middle ( ~filename ; ~dotstart ; ~ext_count ) )|
|Base64|テキスト|計算値自動入力 既存値を置き換え|If ( Ext = "pdf" ; "data:application/pdf;base64," ; "data:image/" & Ext & ";base64," ) & Base64EncodeRFC ( 3548 ; image )|
|ThumbBase64|テキスト|計算値自動入力 既存値を置き換え|If ( ThumbExt = "pdf" ; "data:application/pdf;base64," ; "data:image/" & ThumbExt & ";base64," ) & Base64EncodeRFC ( 3548 ; Thumb )|

### FileMakerから出力したCSVファイルのインポート

MySQL WorkbenchのCSVインポートは遅すぎるのでHeidiSQLを使う

1. MySQL Workbenchのようにフィールド名で照合することはできないのでインポート用CSVファイルのカラムの順番をDBスキーマにあわせる（カラム数は一致しなくてもよい模様）
2. メソッドは「クライアントにあるファイルを読み込む」を選択
3. 制御文字は正しく設定する（フィールド区切り文字は「,」、囲い文字は「"」、CSVファイルがWindowsからの出力なら改行文字は¥r¥n、Macなら¥r）

「NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.」 のエラーが出たら以下を行う

MySQLのサービスを停止してmy.ini(C:\ProgramData\MySQL\MySQL Server 8.0\my.ini)のsql_modeに以下を追加した後にサービスを起動（なぜか変更に失敗してMySQLのサービスが起動できなくなることがあるのでmy.iniは変更前にコピーを取っておくこと）
```
,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
```

ただし、これは後々0000-00-00問題を引き起こすのでFileMaker側でNullは1971/01/01 00:00:00※に置換しておくか、MySQLにインポート後1000-01-01などに置換すること
※1970/01/01 00:00:00を含むそれ以前の日付では0000-00-00 00:00:00になってしまうので注意

https://soudai.hatenablog.com/entry/2018/05/12/191050

以上