FileMakerのESS機能がどのように動いているのか調べてみた

MySQLサーバを挟んで、FileMakerとデータ連携するシステムを開発することになり、その実現方法の一つとしてESS(External SQL Source)機能を、FileMakerの担当ではないが、知識がないので調べてみました。

調査環境

• MacBook Pro 2020版 (CPU: Intel Core i7 クアッドコア 2.3GHz, メモリ: 32GB)

  • macOS Monterey (v12.4)

• VirtualBox v6.1.36

• Ubuntu server v20.04 (vCPU: 1コア, メモリ: 8GB)

• MySQL v8.0.30

  • my.cnfにSQLログの出力を設定。

    [mysqld]
    general_log=1
    general_log_file=/var/log/mysql/query.log
    

• FileMaker Pro v19.5.3

• FileMaker Server v19.5.3

  • Ubuntuへのインストールは FileMaker Server のインストール（Ubuntu Linux） に従いました。
  • ESS機能のためのセットアップは FileMaker Server for LinuxでのESS を参考にしました。
    • Connector/ODBCのインストールは以下の手順で実施。

      # wget https://dev.mysql.com/get/mysql-apt-config_0.8.23-1_all.deb
      # apt install ./mysql-apt-config_0.8.23-1_all.deb
          ★ リポジトリの設定を2回聞かれるけど、全てOK
      # apt update            
      # wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.30-1ubuntu20.04_amd64.deb
      # apt install ./mysql-connector-odbc_8.0.30-1ubuntu20.04_amd64.deb
      

    • データソース名は MySQL８ に変更。

ESS機能対象のMySQL上のテーブル

• FileMakerのオブジェクトフィールドを格納するため、blob型のカラムを用意したが、ESS機能ではオブジェクトフィールドはサポート対象外でした。
• このテーブルに１００万件のレコードを挿入。

FileMaker側にMySQL上のテーブルを追加

ホストにアップロードしたFileMakerファイルを開き、データベースのリレーションシップでデータソースを選択すると、

存在するテーブル名を取得しているのが確認できました。

2022-09-17T02:43:28.354342Z	   11 Query	SELECT TABLE_NAME,TABLE_COMMENT,IF(TABLE_TYPE='BASE TABLE', 'TABLE', TABLE_TYPE),TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' )  ORDER BY TABLE_SCHEMA, TABLE_NAME

続けてテーブルを選択すると、カラムの情報とレコード件数を取得して

2022-09-17T03:24:18.365476Z	   11 Prepare	select TABLE_SCHEMA as TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_TYPE as TYPE_NAME,IF(ISNULL(CHARACTER_MAXIMUM_LENGTH), IF(DATA_TYPE LIKE 'bit',CAST((NUMERIC_PRECISION+7)/8 AS UNSIGNED),NUMERIC_PRECISION), CHARACTER_MAXIMUM_LENGTH) as COLUMN_SIZE,CHARACTER_OCTET_LENGTH as BUFFER_LENGTH,NUMERIC_SCALE as DECIMAL_DIGITS,IF(ISNULL(NUMERIC_PRECISION), NULL, 10) as NUM_PREC_RADIX,IF(EXTRA LIKE "%auto_increment%", "YES", IS_NULLABLE) as NULLABLE,COLUMN_COMMENT as REMARKS,IF(ISNULL(COLUMN_DEFAULT), "NULL", IF(ISNULL(NUMERIC_PRECISION), CONCAT("'", COLUMN_DEFAULT, "'"),COLUMN_DEFAULT)) as COLUMN_DEF,0 as SQL_DATA_TYPE,NULL as SQL_DATA_TYPE_SUB,CHARACTER_OCTET_LENGTH as CHAR_OCTET_LENGTH,ORDINAL_POSITION,IF(EXTRA LIKE "%auto_increment%", "YES", IS_NULLABLE) AS IS_NULLABLE,CAST(CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH AS SIGNED) AS CHAR_SIZE FROM information_schema.COLUMNS c WHERE 1=1 AND c.TABLE_SCHEMA  LIKE ? AND c.TABLE_NAME LIKE ? ORDER BY ORDINAL_POSITION
2022-09-17T03:24:18.365712Z	   11 Execute	select TABLE_SCHEMA as TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_TYPE as TYPE_NAME,IF(ISNULL(CHARACTER_MAXIMUM_LENGTH), IF(DATA_TYPE LIKE 'bit',CAST((NUMERIC_PRECISION+7)/8 AS UNSIGNED),NUMERIC_PRECISION), CHARACTER_MAXIMUM_LENGTH) as COLUMN_SIZE,CHARACTER_OCTET_LENGTH as BUFFER_LENGTH,NUMERIC_SCALE as DECIMAL_DIGITS,IF(ISNULL(NUMERIC_PRECISION), NULL, 10) as NUM_PREC_RADIX,IF(EXTRA LIKE "%auto_increment%", "YES", IS_NULLABLE) as NULLABLE,COLUMN_COMMENT as REMARKS,IF(ISNULL(COLUMN_DEFAULT), "NULL", IF(ISNULL(NUMERIC_PRECISION), CONCAT("'", COLUMN_DEFAULT, "'"),COLUMN_DEFAULT)) as COLUMN_DEF,0 as SQL_DATA_TYPE,NULL as SQL_DATA_TYPE_SUB,CHARACTER_OCTET_LENGTH as CHAR_OCTET_LENGTH,ORDINAL_POSITION,IF(EXTRA LIKE "%auto_increment%", "YES", IS_NULLABLE) AS IS_NULLABLE,CAST(CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH AS SIGNED) AS CHAR_SIZE FROM information_schema.COLUMNS c WHERE 1=1 AND c.TABLE_SCHEMA  LIKE 'ess_test' AND c.TABLE_NAME LIKE 'sample_ess' ORDER BY ORDINAL_POSITION
2022-09-17T03:24:18.368342Z	   11 Close stmt
2022-09-17T03:24:18.368802Z	   11 Query	describe `sample_ess`
2022-09-17T03:24:18.371105Z	   11 Query	describe `sample_ess`
2022-09-17T03:24:18.373642Z	   11 Query	SHOW KEYS FROM `ess_test`.`sample_ess`
2022-09-17T03:24:18.376255Z	   11 Query	SELECT COUNT(*) FROM ess_test.sample_ess

テーブルに対応する簡単なレイアウトも作成されます。

• カラムや件数を取得するSQLは、初回だけでなく、[データベースの管理]画面を(OKボタンでもキャンセルボタンでも)閉じる度に発行されます。

FileMaker側からMySQL上のテーブルを検索

検索の事前準備として、MySQL上のテーブルに100万件のレコードを追加しました。

• Javaで作った簡単なツールで、１回のトランザクションで、1回のSQLで１００レコードを、1万回insertして、約5０秒かかりました。

この時点でFileMaker側に変化はなく、MySQL側の状態がリアルタイムに反映されることは無いことが確認できました。
また6時間ほど放置してみましたが、FileMaker側から定期的にSQLを発行する動きも確認できませんでした。

そして文字列を検索すると、、、

まず文字列の部分一致を条件に主キーだけを取得し、続いて主キーを約１０００件前後指定しながらレコードを取得し、

2022-09-17T11:31:14.978604Z        11 Query     SELECT id FROM ess_test.sample_ess WHERE text LIKE '%12%'
2022-09-17T11:31:15.455748Z        11 Query     SELECT id,text,`number`,`date`,`time`,`timestamp` FROM ess_test.sample_ess WHERE id IN (13,113,121, 〜 省略 〜 ,12964,12965,12966)
 〜 省略 〜
2022-09-17T11:31:17.176862Z        11 Query     SELECT id,text,`number`,`date`,`time`,`timestamp` FROM ess_test.sample_ess WHERE id IN (968126,968127,968128, 〜 省略 〜 ,999713,999813,999913)

検索結果が表示されました。

件数が0から変わって、11,111/49,401となってますが、49,401は'%12%'に該当する件数で、11,111は何を示しているのか不明です。
期待としては 49,401/1,000,000 と表示されて欲しかったです。

SQLのログを良く見てみると、49,401件全てのレコードは取得していないので、何らかの制限でレコードの取得を止めた時の件数が11,111なのかもしれません。

FileMaker側からレコードを追加

新規レコードボタンをクリックし、値を設定し、Enterキーを押したタイミングでSQLが発行されました。トランザクションが効いてます。

2022-09-17T11:56:18.588160Z	  384 Connect	root@172.18.0.1 on ess_test using SSL/TLS
2022-09-17T11:56:18.588775Z	  384 Query	SET NAMES utf8mb4
2022-09-17T11:56:18.589459Z	  384 Query	SET character_set_results = NULL
2022-09-17T11:56:18.589971Z	  384 Query	SET SQL_AUTO_IS_NULL = 0
2022-09-17T11:56:18.590616Z	  384 Query	SET AUTOCOMMIT=0
2022-09-17T11:56:18.591231Z	  384 Query	set @@sql_select_limit=DEFAULT
2022-09-17T11:56:18.591610Z	  384 Query	SET SQL_MODE='STRICT_ALL_TABLES'
2022-09-17T11:56:18.595113Z	  384 Prepare	INSERT INTO ess_test.sample_ess(text,`number`,`date`,`time`,`timestamp`) VALUES(?,123,{d '2022-09-17'},{t '20:55:00'},{ts '2022-09-17 20:55:00'})
2022-09-17T11:56:18.595305Z	  384 Execute	INSERT INTO ess_test.sample_ess(text,`number`,`date`,`time`,`timestamp`) VALUES('aaaa',123,{d '2022-09-17'},{t '20:55:00'},{ts '2022-09-17 20:55:00'})
2022-09-17T11:56:18.596436Z	  384 Close stmt
2022-09-17T11:56:18.597114Z	  384 Query	SELECT LAST_INSERT_ID()
2022-09-17T11:56:18.598323Z	  384 Query	COMMIT
2022-09-17T11:56:18.603132Z	  384 Query	ROLLBACK
2022-09-17T11:56:18.603681Z	  384 Quit
2022-09-17T11:56:18.608286Z	   11 Query	SELECT id,text,`number`,`date`,`time`,`timestamp` FROM ess_test.sample_ess WHERE id IN (1000001)

この時、件数の表示は11,112/49,402となりました。

FileMaker側からレコードを更新

レコードの値を修正し、カーソルが離れたタイミングでSQLが発行されました。FileMakerのUI的に１カラム毎に更新が行われます。

2022-09-17T12:01:02.802303Z	  385 Connect	root@172.18.0.1 on ess_test using SSL/TLS
2022-09-17T12:01:02.802822Z	  385 Query	SET NAMES utf8mb4
2022-09-17T12:01:02.803508Z	  385 Query	SET character_set_results = NULL
2022-09-17T12:01:02.804138Z	  385 Query	SET SQL_AUTO_IS_NULL = 0
2022-09-17T12:01:02.804904Z	  385 Query	SET AUTOCOMMIT=0
2022-09-17T12:01:02.805466Z	  385 Query	set @@sql_select_limit=DEFAULT
2022-09-17T12:01:02.805819Z	  385 Query	SET SQL_MODE='STRICT_ALL_TABLES'
2022-09-17T12:01:02.807113Z	  385 Query	SELECT id,text,`number`,`date`,`time`,`timestamp` FROM ess_test.sample_ess WHERE id=1000001 FOR UPDATE
2022-09-17T12:01:02.809257Z	  385 Prepare	UPDATE ess_test.sample_ess SET text=? WHERE id=1000001
2022-09-17T12:01:02.809429Z	  385 Execute	UPDATE ess_test.sample_ess SET text='bbbb' WHERE id=1000001
2022-09-17T12:01:02.810405Z	  385 Close stmt
2022-09-17T12:01:02.811405Z	  385 Prepare	SELECT id FROM ess_test.sample_ess WHERE id=1000001
2022-09-17T12:01:02.811531Z	  385 Execute	SELECT id FROM ess_test.sample_ess WHERE id=1000001
2022-09-17T12:01:02.812332Z	  385 Close stmt
2022-09-17T12:01:02.813416Z	  385 Query	COMMIT
2022-09-17T12:01:02.818016Z	  385 Query	ROLLBACK
2022-09-17T12:01:02.818492Z	  385 Quit
2022-09-17T12:01:02.840101Z	   11 Query	SELECT id,text,`number`,`date`,`time`,`timestamp` FROM ess_test.sample_ess WHERE id IN (1000001)

FileMaker側からレコードを削除

レコード削除ボタンをクリック、「削除しますか？」に同意したタイミングでSQLが発行されました。

2022-09-17T12:04:20.170546Z	   11 Query	SELECT id,text,`number`,`date`,`time`,`timestamp` FROM ess_test.sample_ess WHERE id IN (1000001)
2022-09-17T12:04:20.175690Z	  386 Connect	root@172.18.0.1 on ess_test using SSL/TLS
2022-09-17T12:04:20.176241Z	  386 Query	SET NAMES utf8mb4
2022-09-17T12:04:20.176701Z	  386 Query	SET character_set_results = NULL
2022-09-17T12:04:20.177158Z	  386 Query	SET SQL_AUTO_IS_NULL = 0
2022-09-17T12:04:20.177544Z	  386 Query	SET AUTOCOMMIT=0
2022-09-17T12:04:20.178188Z	  386 Query	set @@sql_select_limit=DEFAULT
2022-09-17T12:04:20.178629Z	  386 Query	SET SQL_MODE='STRICT_ALL_TABLES'
2022-09-17T12:04:20.179661Z	  386 Query	DELETE FROM ess_test.sample_ess WHERE id=1000001
2022-09-17T12:04:20.181213Z	  386 Query	COMMIT
2022-09-17T12:04:20.186056Z	  386 Query	ROLLBACK
2022-09-17T12:04:20.186635Z	  386 Quit

ちなみに対象レコード削除を選択すると、

対象レコードは'%12%'に該当する49,401件ではなく、あくまで11,111件の方でした。
そして削除を実施すると、SELECT時とは異なり、１レコードずつ削除が行われました。

考察

ESS機能の検索については、不明点が残る結果となりました。
今後、何らかのきっかけで手がかりが得られた時には、再調査したいと思います。