PlanetScale へ FileMaker から ODBC 接続してみる。初期設定からデータ投入まで

はじめに

前書き

• 本記事は FileMaker Advent Calendar 2022 2 日目の記事です。

• また TiDB Cloud へ FileMaker から ODBC 接続してみる。初期設定からパフォーマンス検証まで の姉妹記事にあたります。

対象読者

• Claris FileMaker ユーザ
• FileMaker で ODBC 接続して MySQL と連携させているよという人
• MySQL のクラウドサービスで AWS / GCP / OCI などのメジャーどころのベンダー以外を検討したいという人

what PlanetScale

• 公式サイト
• 説明文を公式サイトから引用

The MySQL-compatible serverless database platform.

PlanetScale 環境準備

アカウント登録

• まず以下よりアカウント登録します。
• https://auth.planetscale.com/sign-up

• 登録してメール認証を終えると以下のような画面に。

データベース作成

• 上記画面内の See how PlanetScale works ボタンの下に小さく create というリンクがあるので、そこを押すと新しくデータベースが作成できます。
• test というデータベース名にします。
• リージョンもこのように選べます。東京リージョンを選びたいので AWS のほうにしましょう。
  • 2022/10 現在 GCP はベータ版で東京リージョンがない。

• 作成中……

• 作成完了！

• データベースへの接続テストをおこなってみます。右上の Connect という白いボタンを押すと、接続情報がモーダルウィンドウで表示されます。

• 初回はパスワードが表示されますが、二回目以後はパスワードが伏せられるので、手元に記録しておきましょう。
  • 仮に紛失したら再発行することはできます。
• 何で以て接続するか、という方法をプルダウンで選べるようになっていて、それに従ってサンプルコードが表示されます。親切便利ですね。
  • ただ MySQL CLI の場合、コマンド内にパスワード含めていたり --ssl-mode=VERIFY_IDENTITY をつけた上で証明書ファイルの置き場所が誤ったりしていると、接続失敗するので、それぞれの環境に応じてのコマンドに書き換えましょう。

• 接続に成功すると以下のように表示されます。

• show databases; を実行すると以下のように。先ほど作成した test がありますね。

テーブルデータの用意

• では、接続完了したこのままの状態で test データベースにテーブルを作成していきます。
• books というテーブルを作ります。
• SQL は以下のように。

CREATE TABLE `test`.`books` (
  `id` bigint,
  `title` varchar(100),
  `type` varchar(20),
  `published_at` datetime,
  `stock` int,
  `price` int,
  PRIMARY KEY (`id`),
  INDEX (`title`, `type`)
);

• show columns from test.books; で作成結果を確認できます。

FileMaker Server の設定

MySQL ODBC Driver のインストール

• では次に FileMaker Server 側の設定へ進みます。
• 最初に MySQL ODBC Driver のインストールをおこないます。
• 詳細は以下、私の書いた過去記事をご参照ください。
  • Qiita - FileMakerServer19稼働のCentOSサーバにMySQLのODBCドライバを設定
• 簡易的にコマンドだけ貼り付けておきます。

sudo rpm -Uvh http://repo.mysql.com/mysql80-community-release-el7.rpm
sudo yum install mysql-connector-odbc

odbc.ini の準備

• 次に odbc.ini ファイルを用意しましょう。
  • Claris 公式ヘルプ - 外部 ODBC データソースへのアクセス
• 以下のようにエディタに応じたコマンドで作成／編集。

sudo vi /etc/odbc.ini

• 中身を以下のようにします。

odbc.ini

[ODBC Data Sources]
PlanetScale_FileMakerTest = MyODBC 8.0 UNICODE Driver DSN

[PlanetScale_FileMakerTest]
Driver           = /usr/lib64/libmyodbc8w.so
SERVER           = [[Connect ボタンを押した先で表示されるたとえば ap-northeast.connect.psdb.cloud など]]
PORT             = 3306
Database         = test
OPTION           = 3
INTERACTIVE      = 1
BIG_PACKETS      = 1
NO_PROMPT        = 1
COMPRESSED_PROTO = 1
AUTO_RECONNECT   = 1
MULTI_STATEMENTS = 1

FileMaker Pro から TiDB Cloud への接続

• さて今回、あらかじめ FileMaker Server 上にファイルが既にホストされている、という前提で話を進めます。そのファイルを開いて TiDB への接続設定をおこなっていきます。
• ファイル > 管理 > 外部データソース を選択。

• データソースの編集ウィンドウで タイプ を ODBC にして DNS の右端にある 指定 ボタンを押すと、以下のようなウィンドウが表示されて odbc.ini に記述したものが表示されます。

• 上記を選択して進めた次には、認証設定です。

• ファイル > 管理 > データベース > リレーションシップ を選択して、新しくテーブルオカレンスを追加しようとすると、データソースとして PlanetScale_FileMakerTest など先ほど追加したものが選べるようになっています。
• それを選ぶと、先ほど作成したテーブル books が表示されました。
  • 今回、別記事で別のデータベースサービスの books が既に追加されている状態なので PS_books という名にします。

• 進めると、以下のようにテーブルオカレンスとして出現。

• タブを フィールド に切り替えてみても、中身がしっかり定義されて出てくれています。

表示等操作のためのレイアウト用意

• この追加した PlanetScale 上の books テーブルを FileMaker Pro で表示等操作するためのレイアウトを用意します。
• 用意、といっても既にテーブルオカレンスを追加した時点でレイアウトが作成されている（設定による）ので、レイアウトを books に切り替えてみると、既にフィールドもありますね。

FileMaker Pro 側からレコードを作成

• 試しにひとつレコードを作成してみましょう。
• 『方丈記』を入力して……
• ……エラーが！

エラーを確認

• PlanetScale の管理画面から Insights を選ぶとエラーが確認できます。

• syntax error at position <position> の行を選ぶと詳細が表示されます。

• さらに error をクリックすると詳細が……
• ……って、いやいや、表示されないのか！　ツッコミが虚しく響き渡る……

• おそらく FileMaker から発行されるクエリの書式が PlanetScale 側に合ってくれないということなのでしょうね……
• レコードのインポートはどうだろうか、と試してみましたが、アウツ！

• FileMaker Pro 上から Ctrl + N やインポートなどで新規レコード作成できない、という時点で、運用難しいですね。でも、一応続きを進めてみます……

動作検証

軽いデータの投入と確認

• まず大事なこととして PlanetScale は local_infile を有効化できないそうです。残念。
  • Qiita - PlanetScaleにローカルデータベースをインポートしたい

• ですので、ひとまず 10 レコードほどのデータを直接 insert してみます。

INSERT INTO test.books
VALUES
(1,"本の名前1","Novel","0101/01/02",1,1000),
(2,"本の名前2","Novel","0101/01/03",1,1000),
(3,"本の名前3","Novel","0101/01/04",1,1000),
(4,"本の名前4","Novel","0101/01/05",1,1000),
(5,"本の名前5","Novel","0101/01/06",1,1000),
(6,"本の名前6","Novel","0101/01/07",1,1000),
(7,"本の名前7","Novel","0101/01/08",1,1000),
(8,"本の名前8","Novel","0101/01/09",1,1000),
(9,"本の名前9","Novel","0101/01/10",1,1000),
(10,"本の名前10","Novel","0101/01/11",1,1000);

• select * from test.books; を実行すると、以下のように表示されます。なので問題なく投入はできたようです。

FileMaker Pro 側で確認

• では、この投入されたデータが FileMaker Pro 側で表示されるかどうか……
• ウインドウ内容の再表示 スクリプトを実行してみるなどしてから……

• あ、え……ダメ……！！？？
• ……と思って、FileMaker ファイルを一度閉じ、開き直し……ということをやってみたところ、表示されるようになりました🎉　良かった……

何ができて、何ができないのか

レコードの作成／複製

• 先ほど見たとおり、できませんでした。

レコードの値の編集

• 値の編集なら問題なくできるようです。

レコードの検索

• 検索も問題なくおこなえるようです。

レコードのソート

• ソートも問題なく。降順にしてみました。

現在レコードの削除

• 作成はできないものの、削除には成功。

対象レコードの削除

• まとめて削除できるかどうか。できました。

まとめ

• ということで、レコードを新規で作成すること以外は問題なさそうです。肝心なところが欠けてしまうとは……

重いデータの投入

• では 100 万レコードの insert をおこなってみます。
• 先ほどのような bulk insert 用のコマンドを書いた .sql ファイルを用意して実行します。

source /home/username/books_data.sql;

• ……しかし 100 万だと、多すぎるのか、原因不明のエラーということで怒られます。チマチマと繰り返し insert していくしかなさそう……
• 10 万でもダメでした。
• 5 万ならいける！

• ……ということで……検証用には 5 万レコードでいいかな……

実際の検証

• さて、実際の検証なのですが……後編記事に続く！　ということで、以下内容の検証編を次回おこないます。
  • レコードの検索
  • レコードのソート
  • フィールド内容の全置換

おわりに

後編記事

• 用意できたらリンク貼ります

雑感

• レコード作成ができなくて実質使いものにならないというのは、つらいところですね……
• 上記の問題以外、つまり FileMaker 連携を考えなければ、かなり使いやすいサービスなのですが
  • ちなみに FileMaker 連携と関係ないところですが、 foreign key サポートされていないのは要注意点です