ドキュメントDBとしてのRDBMS

# はじめに
この記事は[NTTテクノクロス Advent Calendar 2018](https://qiita.com/advent-calendar/2018/ntt-tx)の5日目の記事です🙌

[NTTテクノクロス](https://www.ntt-tx.co.jp/?utm_source=qiita&utm_medium=ac2018&utm_campaign=05)の @yamamo-i です🤗
普段はApache Igniteや[PostgreSQLの技術検証](https://www.ntt-tx.co.jp/products/osscloud/?utm_source=qiita&utm_medium=ac2018&utm_campaign=05)をしています💪
本日はRDBMSをドキュメントDBとして利用する技術について触れたいと思います📖
PostgreSQL🐘をメインにしていますが今日はMySQL🐬の話をします😇

# TL;DR

* MySQLの[X Protocol](https://dev.mysql.com/doc/internals/en/x-protocol.html)を試してみました。
  * MySQLのX Pluginの構築とPythonでX DevAPIを使ってみました。
* ドキュメントDBとしてMySQLのX Protocolも選択肢に入れてみてよいと思います。

# ドキュメント(json)管理

最近は[MongoDB](https://www.mongodb.com)などのNoSQLデータベースがドキュメントのデータストアとして台頭しています。
しかし、RDBMSもドキュメント管理のインターフェースを用意しています。

## RDBMSでのドキュメント管理

* RDBMSでもドキュメントを管理するインターフェースを用意しています
  * [PostgreSQLのjson/jsonb型](https://www.postgresql.jp/document/10/html/datatype-json.html)
  * [MySQLのjson型](https://dev.mysql.com/doc/refman/8.0/en/json.html)
  * json操作の関数
      * https://www.postgresql.jp/document/10/html/functions-json.html
      * https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
* ただし、独特のSQLを発行する必要があるため、アプリケーションエンジニアからの敷居が高く感じます。

## MySQL X Protocol

MySQLではX Protocolというドキュメント管理(json型)用の操作Protocolを提供しています。
https://dev.mysql.com/doc/refman/8.0/en/document-store.html
以下の図がX Protocolの概念になります。
X DevAPIを利用してPython, JavaScript, Node.js, ShellなどからX Protocolを利用できます。
X Pluginはドキュメント管理用のMySQL Serverのインターフェースを用意します。
> ![x-protocol](https://dev.mysql.com/doc/internals/en/images/x-protocol-overview.png)
引用元(https://dev.mysql.com/doc/internals/en/x-protocol.html)

MySQLの公式から提供されることで以下のことが期待できます。

* トランザクション操作ができる
* json型用のSQLをアプリケーションから隠蔽できる

本記事はその構築方法や操作方法を記述します。

### X Pluginの構築

MySQLをデフォルトで起動してもX Protocol(33060ポート)はLISTENしません。
MySQLコミュニティから[パッケージが提供](https://dev.mysql.com/downloads/shell/)されているので追加します。
今回はDocker(18.06.1-ce-mac73)を用いて構築しました。
Dockerfileは以下になります。

```dockerfile
FROM mysql:8.0.13

EXPOSE 33060
RUN set -x \
    && apt update \
    && apt install -y wget net-tools libpython2.7 \
    && apt --fix-broken install \
    && (cd tmp && wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_8.0.13-1debian9_amd64.deb && dpkg -i mysql-shell_8.0.13-1debian9_amd64.deb) \
    && (cd tmp && wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-dbgsym_8.0.13-1debian9_amd64.deb && dpkg -i mysql-shell-dbgsym_8.0.13-1debian9_amd64.deb) \
    && rm /tmp/*.deb
```

ビルドと起動は以下のコマンドを発行します。

```shell
# Docker Imageの作成
$ docker build -t mysql:8.0.13-x .

# Docker Containerの起動
$ docker run --name mysql -d -p33060:33060 --env MYSQL_ROOT_PASSWORD='password' mysql:8.0.13-x

# 33060ポートがLISTENします
$ docker exec -it mysql netstat -tan
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp6       0      0 :::33060                :::*                    LISTEN
tcp6       0      0 :::3306                 :::*                    LISTEN

# Pluginのmysqlxが有効化しています
$ docker exec -it mysql mysql -uroot -p -e"SHOW plugins"
Enter password:
+---------------------------------+----------+--------------------+---------+---------+
| Name                            | Status   | Type               | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
.
(中略)
.
| mysqlx                          | ACTIVE   | DAEMON             | NULL    | GPL     |
.
+---------------------------------+----------+--------------------+---------+---------+
```

これでX Protocolが開いたMySQLが起動しました。

### PythonによるX DevAPIの操作

Python(今回は3.7.1)からX DevAPIを操作するには[mysql-connector-python](https://pypi.org/project/mysql-connector-python/)を使用します。
インストールは`pip`を利用します。

```shell
# 今回は最新版の8.0.13を使用しました
$ pip install mysql-connector-python==8.0.13
```

まずはコネクションを取得して操作を始めます。

```python
# X DevAPI用のライブラリをインポート
import mysqlx

# MySQLのX Protocolを使用してコネクションを張る
session = mysqlx.get_session({'host': '127.0.0.1', 'port': 33060, 'user': 'root', 'password': 'password'})
#  `mysql` スキーマに接続
schema = session.get_schema('mysql')
```

これでデータベースの操作ができるようになりました。

#### DDL(Data Definition Language)

X DevAPIからドキュメント用のテーブルを作成できます。

```python
# テーブルの作成
schema.create_collection('my_collection')
```
作成したテーブルは一意キーの`_id`とjson管理用の`doc`の2カラムが定義されます。
このテーブルでドキュメントを管理します。

```sql
mysql> show create table my_collection\G
*************************** 1. row ***************************
       Table: my_collection
Create Table: CREATE TABLE `my_collection` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
```

#### DML(Data Manipulation Language)

##### INSERT

レコードの挿入は`add`メソッドで操作します。
Pythonのdictをそのまま挿入できるので便利です。

```python:insert_record.py
# 挿入レコードを用意
record = {"company": {
    "NTT-TX": [
        "Soft",
        "AT",
        "IT"
    ]
}}
# データ挿入処理
collection.add(record).execute();
```

挿入したレコードは以下の様になっています。
`doc`カラムのjsonには自動で`_id`要素が付与されます。

```sql
mysql> select * from my_collection;
+--------------------------------------------------------------------------------------+------------------------------+
| doc                                                                                  | _id                          |
+--------------------------------------------------------------------------------------+------------------------------+
| {"_id": "00005c034a580000000000000001", "company": {"NTT-TX": ["Soft", "AT", "IT"]}} | 00005c034a580000000000000001 |
+--------------------------------------------------------------------------------------+------------------------------+
1 row in set (0.00 sec)
```
MySQLに発行されているSQL[^1]は以下であり、`_id`カラムで管理しているidを自動でjsonに付与しています。

```sql
SELECT @@mysqlx_document_id_unique_prefix,@@auto_increment_offset,@@auto_increment_increment;
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_SET(JSON_OBJECT('company',JSON_OBJECT('NTT-TX',JSON_ARRAY('Soft','AT','IT'))), '$._id', '00005c034a580000000000000001'));
```

また`_id`をユーザーが指定したり、複数レコードの挿入もできます。

```python:bulk_insert.py
# レコードの生成
record_list = [{"_id": _id, "age": _id * 5} for _id in range(10)]
# listでも複数引数でも挿入可能
collection.add(*record_list).execute();
```
```sql
mysql> select * from my_collection;
+-----------------------+-----+
| doc                   | _id |
+-----------------------+-----+
| {"_id": 0, "age": 0}  | 0   |
| {"_id": 1, "age": 5}  | 1   |
| {"_id": 2, "age": 10} | 2   |
| {"_id": 3, "age": 15} | 3   |
| {"_id": 4, "age": 20} | 4   |
| {"_id": 5, "age": 25} | 5   |
| {"_id": 6, "age": 30} | 6   |
| {"_id": 7, "age": 35} | 7   |
| {"_id": 8, "age": 40} | 8   |
| {"_id": 9, "age": 45} | 9   |
+-----------------------+-----+
10 rows in set (0.00 sec)
```
```sql
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_OBJECT('_id',0,'age',0)),(JSON_OBJECT('_id',1,'age',5)),(JSON_OBJECT('_id',2,'age',10)),(JSON_OBJECT('_id',3,'age',15)),(JSON_OBJECT('_id',4,'age',20)),(JSON_OBJECT('_id',5,'age',25)),(JSON_OBJECT('_id',6,'age',30)),(JSON_OBJECT('_id',7,'age',35)),(JSON_OBJECT('_id',8,'age',40)),(JSON_OBJECT('_id',9,'age',45));
```

##### READ

レコードの読み込みは`find`, `bind`, `limit` で操作します。

```python:select_records.py
record = collection.find("age >= :param").bind("param", 21).limit(2).execute()
for rec in record.fetch_all():
    print(rec)

# 実行結果
# {"_id": 5, "age": 25}
# {"_id": 6, "age": 30}
```
発行されるSQLは以下になります。

```sql
SELECT doc FROM `mysql`.`my_collection` WHERE (JSON_EXTRACT(doc,'$.age') >= 21) LIMIT 0, 2;
```

##### UPDATE

レコードのUPDATEは`modify`, `patch`メソッドで操作します。

```python:update_records.py
collection.modify("age <= 21").patch('{"_is": "young"}').execute()
collection.modify("age > 21").patch('{"_is": "old"}').execute()
```
発行されるSQLとレコードは以下のようになります。

```sql
mysql> select * from my_collection;
+---------------------------------------+-----+
| doc                                   | _id |
+---------------------------------------+-----+
| {"_id": 0, "_is": "young", "age": 0}  | 0   |
| {"_id": 1, "_is": "young", "age": 5}  | 1   |
| {"_id": 2, "_is": "young", "age": 10} | 2   |
| {"_id": 3, "_is": "young", "age": 15} | 3   |
| {"_id": 4, "_is": "young", "age": 20} | 4   |
| {"_id": 5, "_is": "old", "age": 25}   | 5   |
| {"_id": 6, "_is": "old", "age": 30}   | 6   |
| {"_id": 7, "_is": "old", "age": 35}   | 7   |
| {"_id": 8, "_is": "old", "age": 40}   | 8   |
| {"_id": 9, "_is": "old", "age": 45}   | 9   |
+---------------------------------------+-----+
10 rows in set (0.00 sec)
```

```sql
UPDATE `mysql`.`my_collection` SET doc=JSON_SET(JSON_MERGE_PATCH(doc,JSON_OBJECT('_is','young')),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$.age') <= 21);
UPDATE `mysql`.`my_collection` SET doc=JSON_SET(JSON_MERGE_PATCH(doc,JSON_OBJECT('_is','old')),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$.age') > 21);
```

##### DELETE

レコードのDELETEは`remove`メソッドで操作します。

```python:delete_records.py
collection.remove("age >= 31").execute()
```
発行されるSQLとレコードは以下のようになります。

```sql
mysql> select * from my_collection;
+---------------------------------------+-----+
| doc                                   | _id |
+---------------------------------------+-----+
| {"_id": 0, "_is": "young", "age": 0}  | 0   |
| {"_id": 1, "_is": "young", "age": 5}  | 1   |
| {"_id": 2, "_is": "young", "age": 10} | 2   |
| {"_id": 3, "_is": "young", "age": 15} | 3   |
| {"_id": 4, "_is": "young", "age": 20} | 4   |
| {"_id": 5, "_is": "old", "age": 25}   | 5   |
| {"_id": 6, "_is": "old", "age": 30}   | 6   |
+---------------------------------------+-----+
7 rows in set (0.00 sec)
```
```sql
DELETE FROM `mysql`.`my_collection` WHERE (JSON_EXTRACT(doc,'$.age') >= 31);
```

#### DCL(Data Control Language)

X DevAPIはMySQLがベースであるからこそトランザクション操作も実現できます。
トランザクションは`session.start_transaction`, `session.commit`,`session.rollback`メソッドで操作できます。
以下のようなコードで書けます。

```python:transaction.py
# セッションの確立
session = mysqlx.get_session({'host': '127.0.0.1', 'port': 33060, 'user': 'root', 'password': 'root'})

try:
    # テーブル情報の取得
    schema = session.get_schema('mysql')
    schema.create_collection('my_collection', True)
    collection = schema.get_collection('my_collection')

    # トランザクションの開始
    session.start_transaction()
    collection.add({"_id": 1, "NTT": "TX"}).execute()

    # トランザクションのコミット
    session.commit()
except Exception:
    # 例外時はトランザクションをロールバック
    session.rollback()
finally:
    session.close()
```
上記のコードを実行した時、発行されるSQLは以下になります。

```sql
START TRANSACTION;
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_OBJECT('_id',1,'NTT','TX'));
COMMIT;
```
故意に例外を発生させるとROLLBACKが発行されます。

```sql
START TRANSACTION;
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_OBJECT('_id',1,'NTT','TX'));
ROLLBACK;
```

#### その他

MySQL X DevAPIは他にも以下の操作ができます。

* [ConnectionPooling](https://dev.mysql.com/doc/dev/connector-python/8.0/tutorials/connection_pooling.html)
* [TransactionのSavepoint処理](https://dev.mysql.com/doc/dev/connector-python/8.0/tutorials/transactions.html#savepoints)
* [INDEXの作成](https://dev.mysql.com/doc/dev/connector-python/8.0/tutorials/creating_indexes.html)
* [明示的なLock](https://dev.mysql.com/doc/dev/connector-python/8.0/tutorials/locking.html)

RDBMSで一般的な操作がドキュメントDBとして実現されています。

# まとめ

X Protocolを使ってMySQLをドキュメントDBとして操作してみました。
メリットは以下に感じました。

* RDBMSなので使い慣れている
* トランザクション機能が使える
* json型のIFを隠蔽してくれる
* MySQLが公式にリリースしている

いきなりドキュメントDBを導入するのではなくRDBMSの機能を使うのもよいのではないでしょうか？
明日からの[NTTテクノクロス Advent Calendar 2018](https://qiita.com/advent-calendar/2018/ntt-tx)もお楽しみに🎉
明日の担当は @j-yama さんです🙏

[^1]: MySQLに発行されているSQLの調査は`slow_query_log`を有効かつ`long_query_time`を0にして調査しました。