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

# はじめに
## 前書き
* 本記事は [FileMaker Advent Calendar 2022](https://qiita.com/advent-calendar/2022/filemaker) 2 日目の記事です。

https://qiita.com/advent-calendar/2022/filemaker

* また [TiDB Cloud へ FileMaker から ODBC 接続してみる。初期設定からパフォーマンス検証まで](https://qiita.com/Hi_Noguchi/items/ea4eb52136ac314b1348) の姉妹記事にあたります。

https://qiita.com/Hi_Noguchi/items/ea4eb52136ac314b1348

## 対象読者
* [Claris FileMaker](https://www.claris.com/ja/filemaker/) ユーザ
* FileMaker で ODBC 接続して MySQL と連携させているよという人
* MySQL のクラウドサービスで AWS / GCP / OCI などのメジャーどころのベンダー以外を検討したいという人

## what PlanetScale
* [公式サイト](https://planetscale.com/)
* 説明文を公式サイトから引用

> The MySQL-compatible serverless database platform.


# PlanetScale 環境準備
## アカウント登録
* まず以下よりアカウント登録します。
* https://auth.planetscale.com/sign-up

https://auth.planetscale.com/sign-up

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/85e3e076-9b54-160b-5cdb-bfc609e467f7.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/67a38d9b-68be-dcca-a126-9393f08ee05d.png)

* 作成中……

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/12d57ede-c73e-d241-2b03-909adcfc0b6c.png)

* 作成完了！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/bd24fe02-ca09-a674-70ec-1250baa66ca5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/b746f14d-64b5-ffef-d403-ba2a72c25c16.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/3851c333-b909-1271-4abb-46844010dd3b.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/38267e7d-f805-7f31-4fc9-e17791d2197f.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/e85c3bb2-e468-2e4f-94ac-1d22f5ee6499.png)


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

```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;` で作成結果を確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/026725b7-9498-e5d7-ee6d-9f2b0d1e91e1.png)

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

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

## odbc.ini の準備
* 次に odbc.ini ファイルを用意しましょう。
  * [Claris 公式ヘルプ - 外部 ODBC データソースへのアクセス](https://help.claris.com/ja/server-help/content/xbdc-access.html)
* 以下のようにエディタに応じたコマンドで作成／編集。

```
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 への接続設定をおこなっていきます。
* `ファイル` > `管理` > `外部データソース` を選択。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/b291dc7c-ef75-0c2f-7785-e8f6b53e88a3.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/5d5d07f5-159e-11ac-6e60-9e17b96977ab.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/f3c63798-ba5c-7a4f-429e-f5b0a6218650.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/db341024-4eb5-2291-abf5-4d16cd97ceb8.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/7132a44d-c15d-7de0-5c64-ddbf886acef3.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/e5753f1f-b342-d3b8-2015-25a51a6a9611.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/de0c6b55-a711-9348-8835-ac73cb4a5c78.png)

## FileMaker Pro 側からレコードを作成
* 試しにひとつレコードを作成してみましょう。
* 『方丈記』を入力して……
* ……エラーが！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/5e1f7e44-0f7c-87b9-a55a-2cb2afb6d29e.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/74fcd59a-d402-9132-f8a3-b18ec9886500.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/29b886aa-229b-0f96-9a0d-5ad83af3b4b6.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/6f9bbfd2-3882-065f-cd28-210ef504d2b5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/ceda49fa-de17-4948-ad70-2d877918989a.png)

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

# 動作検証
## 軽いデータの投入と確認
* まず大事なこととして PlanetScale は `local_infile` を有効化できないそうです。残念。
  * [Qiita - PlanetScaleにローカルデータベースをインポートしたい](https://qiita.com/ike81818/questions/b95614afcea7d7f3633b)

https://qiita.com/ike81818/questions/b95614afcea7d7f3633b

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

```sql
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;` を実行すると、以下のように表示されます。なので問題なく投入はできたようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/ef5f6707-8ab3-69c7-ad8f-b4fabcb470b6.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/2e1e1bca-e3eb-3d69-3e63-8b1eb6a2840d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/536e90dd-bb62-1903-fe07-4fede9ce206f.png)

## 何ができて、何ができないのか
### レコードの作成／複製
* 先ほど見たとおり、できませんでした。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/fa164048-9de3-e231-ae08-77581718dde2.png)

### レコードの値の編集
* 値の編集なら問題なくできるようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/8914b844-a17b-5652-9ea4-8213d972fd82.png)


### レコードの検索
* 検索も問題なくおこなえるようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/2836eb7f-d387-8e0b-c578-e47d0f16dd63.png)

### レコードのソート
* ソートも問題なく。降順にしてみました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/2780c607-57a9-1607-698d-64c0c3278516.png)

### 現在レコードの削除
* 作成はできないものの、削除には成功。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/ed9a6a75-9880-06a3-d1ac-7529de1e85ab.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/d4b11d46-e3fa-d0cf-f1be-10b55960648b.png)

### 対象レコードの削除
* まとめて削除できるかどうか。できました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/f41f563e-7374-65bb-17ad-456ddecc4d49.png)

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

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

```sql
source /home/username/books_data.sql;
```

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/37739/9878d27a-15fa-1d22-6318-a5f35374e66d.png)

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

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

# おわりに
## 後編記事
* 用意できたらリンク貼ります

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