enebular Node-RED で RDB

Node-RED でRDBを利用したい場面、結構遭遇することがあると思います。

RDBとして　**[IBM Db2 on Cloud](https://www.ibm.com/jp-ja/cloud/db2-on-cloud)** をNode-REDから使用する際に、
いままで **[node-red-nodes-cf-sqldb-dashdb](https://flows.nodered.org/node/node-red-nodes-cf-sqldb-dashdb)** を利用させてもらっていたのですが、enebular でパレットの管理でノードをインストールするときにエラーになってしまい…

IBM Db2 on Cloud を操作するAPIが公開されているとのことなので、この際フローを自分で作ってしまおうと思い実践してみました。

パレットの管理でノード追加せずに、初期状態のenebularで動作確認することができます。

使い回しできるようにサブフローとして作成します。

# IBM Db2 on Cloud REST API
[公式リファレンス](https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4)を頼りにフローを作成していきます。

APIの呼び出し手順としては以下の通りになります。

1. IDパスワード認証と各種API呼び出しのためのトークンを取得API  [(authenticate)](https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4#authenticate)
1. SQLを実行するAPI [(runsql)](https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4#runsql)
1. SQLの結果を取得するAPI [(fetchsqlresults)](https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4#fetchsqlresults)

# フロー作成
## 全体フロー
フローの全体像は以下のようになります。

![スクリーンショット 2021-12-04 18.18.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/a677d51f-e494-d9b2-4c2d-32402d33e89e.png)


サブフローのインプットパラメータは以下の通りです。

- msg.payload : SQL文

サブフローのアウトプットパラメータは以下の通りです。

- msg.payload : SQL の実行結果APIのレスポンス


大きく分けて４つのブロックに分かれます

- Token取得部分
- SQL実行部分
- SQL結果取得部分
- エラーチェック部分

## http request ノード共通設定
フローで利用する **http request** ノードは共通で下記の設定にします。

- メソッド： 「-msg.methodに定義-」
- URL: 「空」
- 出力形式: 「JSONオブジェクト」


![スクリーンショット 2021-12-04 15.43.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/df3a984c-ee8e-b950-f25a-4073943b840d.png)


## Token取得
使用ノード

- Function
- http request 
- Switch 

![スクリーンショット 2021-12-04 15.29.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/bc71976f-6fbd-aa31-2a0d-51e4e55368d1.png)

### function
認証APIにアクセスするためのリクエストパラメータをコーディングします

```javascript:function
msg.method = "POST";

msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/auth/tokens";

msg.headers = {
    "Content-Type"    : "application/json",
    "x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") 
};

msg.payload = {
  "userid"  : "[ユーザID]",
  "password"  : "[パスワード]",
};

msg.rejectUnauthorized = false; //オレオレ証明を許可

return msg;
```

### Switch 

**Payload** にトークンが格納されています。一度取得したトークンはしばらく使い回しができるので、何度もトークンを取得しなくてもいいように **グローバルコンテキスト**に格納しておきます。

- 代入先： **global.token**
- 代入元： **msg.payload.token**

![スクリーンショット 2021-12-04 15.46.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/afbae4d0-1f1f-7960-051c-08843c43f7dc.png)

## SQL実行(runsql)
SQLを実行するAPIにアクセスするためのリクエストパラメータをコーディングします。

使用ノード

- Function
- http request 

![スクリーンショット 2021-12-04 15.54.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/cf8d2a59-3335-4c85-5834-08107e9c776f.png)

### function

```javascript:function
msg.method = "POST";

msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/sql_jobs";

msg.headers = {
    "authorization"   : "Bearer " + global.get("token"),
    "Content-Type"    : "application/json",
    "x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") ) 
};

msg.payload = {
  "commands" : "[実行したいSQL]",　// 実行したいSQLを指定
  "limit" : 300,
  "separator" : ";",
  "stop_on_error" : "no",
};

msg.rejectUnauthorized = false; //オレオレを許可

return msg;
```

## SQL結果取得(fetchsqlresults)
実行したSQLの結果を取得するAPIにアクセスするためのリクエストパラメータをコーディングします。

使用ノード

- Function
- http request 
- Switch
- delay

![スクリーンショット 2021-12-04 17.40.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/a3ab101a-47d3-8274-00b9-8009a469fa29.png)


### Function

```javascript:function
msg.method = "GET";

msg.url = "https://[Db2 on Cloud の URL]";
msg.url += "/dbapi/v4/sql_jobs/" + msg.payload.id;

msg.headers = {
    "authorization" : "Bearer " + global.get("token"),
    "content-type" : "application/json",
    "x-deployment-id" : encodeURIComponent("crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/---:***::") ) 
};
msg.payload = null;
msg.rejectUnauthorized = false; //オレオレを許可

return msg;
```

### Switch
SQLの実行に時間がかかる場合、APIの呼び出しタイミングによっては `実行中(runnning)`、もしくは `失敗(failed)` を返すこともあるので、API呼び出しが`完了(completed)`になるまで状態をチェックします。

Switchノードに設定する分岐条件は以下の通りです。

- プロパティ： **msg.payload.status**

| 条件順番 | 条件式 | 値 | 接続先ノード |
|:-----------|:------------:|:------------:| ---- |
| 1 | ==| running| delay |
| 2 | ==| failed| debug |
| 3 | その他||サブフローのアウトプット|


!![スクリーンショット 2021-12-04 17.52.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/afeafc84-9c86-87cf-6dfb-1a0ea9f3df7b.png)

### delay
APIのステータスが`実行中(running)`だった場合、再度APIを呼び出して結果を確認します。間隔を開けて結果取得APIにアクセスするために、delay ノードを挟みます。

## エラーチェック
API呼び出しがエラーだったかどうかを判定します。呼び出しのエラーが１回目の場合はAPI呼び出しを再試行し、２回目のAPI呼び出しもエラーの場合、フローをエラーとして扱います。

使用ノード

- Switch 
- change

![スクリーンショット 2021-12-04 18.01.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/83ec7310-a462-4c56-7fc9-903fa1fba051.png)

### SQLAPIコールエラー確認
APIの呼び出しがエラーだったかどうかを確認します。

- プロパティ： **msg.payload.errors**

| 条件順番 | 条件式 | 値 | 接続先ノード |
|:-----------|:------------:|:------------:| ---- |
| 1 | is not null | | 次の処理へ |
| 2 | その他|| SQL実行のFunctionノード |

![スクリーンショット 2021-12-04 18.05.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/7842942e-2c5e-f10a-4763-7b469a069232.png)

### エラー回数チェック
API呼び出しのエラー回数をカウントチェックします。

- プロパティ： **msg.error_count**

| 条件順番 | 条件式 | 値 | 接続先ノード |
|:-----------|:------------:|:------------:| ---- |
| 1 | == | 1 | Debugノード(エラー処理) |
| 2 | その他|| set msg.error_count(リトライ処理へ) |

![スクリーンショット 2021-12-04 18.06.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/668e01ea-3d27-c527-9e52-0485b65bcb82.png)


### set msg.error_count
エラーカウントを１にします

- 代入先： **msg.error_count**
- 代入元： **1**

![スクリーンショット 2021-12-04 18.10.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/1ade2eb9-88ea-cce3-a7b3-93dbbe25c614.png)

## 実行
サブフローを実行するためのメインフローを準備します

![スクリーンショット 2021-12-04 18.21.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/0f67f000-ab3e-1daf-1402-593c9f69c665.png)

`injectノード`の **ペイロード**に、実行したいSQL文を「文字列」で入力します。

![スクリーンショット 2021-12-04 18.22.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311258/6a0d376a-b323-8bf8-a047-282dc3d56cf8.png)

**「デプロイ」**したあと、`injectノード`を実行すると、デバッグウインドウにSQL実行結果が表示されます。

# フローソースコード
## メインフロー

```
[{"id":"73fafa06.250d84","type":"inject","z":"eba0be30.3bd82","name":"","topic":"","payload":"SELECT MAX(DEREPO_POSTTIME) FROM TR_DEREPO","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":40,"wires":[["e29006b9.c80008"]]}]
```

## サブフロー
```
[{"id":"e8836514.834558","type":"subflow","name":"Subflow 1","info":"","in":[{"x":60,"y":340,"wires":[{"id":"15828c28.4cfb74"}]}],"out":[{"x":980,"y":700,"wires":[{"id":"5c39894f.1f9918","port":2}]}]},{"id":"42dcae6b.83c96","type":"function","z":"e8836514.834558","name":"","func":"msg.method = \"POST\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/auth/tokens\";\n\nmsg.headers = {\n    \"Content-Type\"    : \"application/json\",\n    \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\n\nmsg.payload = {\n  \"userid\"  : [user_name],\n  \"password\"  : [pass_word],\n};\n\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":490,"y":200,"wires":[["70419f5b.d3b71"]]},{"id":"70419f5b.d3b71","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":560,"y":240,"wires":[["d30d394f.127d08"]]},{"id":"3d472c20.76c1c4","type":"inject","z":"e8836514.834558","name":"","topic":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":270,"y":120,"wires":[["42dcae6b.83c96"]]},{"id":"67cefa56.291034","type":"debug","z":"e8836514.834558","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1030,"y":640,"wires":[]},{"id":"61fd4a77.876574","type":"comment","z":"e8836514.834558","name":"Token取得","info":"","x":490,"y":160,"wires":[]},{"id":"15828c28.4cfb74","type":"change","z":"e8836514.834558","name":"SQLバックアップ","rules":[{"t":"set","p":"query","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":240,"y":340,"wires":[["1ae60db9.b8d132"]]},{"id":"d30d394f.127d08","type":"change","z":"e8836514.834558","name":"Backup token(global)","rules":[{"t":"set","p":"token","pt":"global","to":"payload.token","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":280,"wires":[["1ae60db9.b8d132"]]},{"id":"1ae60db9.b8d132","type":"function","z":"e8836514.834558","name":"SQL 実行","func":"msg.method = \"POST\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/sql_jobs\";\n\nmsg.headers = {\n    \"authorization\"   : \"Bearer \" + global.get(\"token\"),\n    \"Content-Type\"    : \"application/json\",\n    \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\n\nmsg.payload = {\n  \"commands\" : msg.query,\n  \"limit\" : 300,\n  \"separator\" : \";\",\n  \"stop_on_error\" : \"no\",\n};\n\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":520,"y":400,"wires":[["f7c40e7c.66f8e"]]},{"id":"f7c40e7c.66f8e","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":580,"y":440,"wires":[["432f6bc.ea2a394","67cefa56.291034"]]},{"id":"727e4a73.67e394","type":"function","z":"e8836514.834558","name":"結果取得","func":"msg.method = \"GET\";\n\nmsg.url = [Db2 on Cloud のURL];\nmsg.url += \"/dbapi/v4/sql_jobs/\" + msg.payload.id;\n\nmsg.headers = {\n    \"authorization\"   : \"Bearer \" + global.get(\"token\"),\n    \"content-type\" : \"application/json\",\n    \"x-deployment-id\" : encodeURIComponent([crn から始まる deployment_id]) \n};\nmsg.payload = null;\nmsg.rejectUnauthorized = false; //オレオレを許可\n\nreturn msg;","outputs":1,"noerr":18,"x":550,"y":580,"wires":[["10db9a81.3f6ee5"]]},{"id":"10db9a81.3f6ee5","type":"http request","z":"e8836514.834558","name":"","method":"use","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":600,"y":620,"wires":[["67cefa56.291034","5c39894f.1f9918"]]},{"id":"432f6bc.ea2a394","type":"switch","z":"e8836514.834558","name":"SQL API コールエラー確認","property":"payload.errors","propertyType":"msg","rules":[{"t":"nnull"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1050,"y":80,"wires":[["d5839f31.88bef"],["727e4a73.67e394"]]},{"id":"d5839f31.88bef","type":"switch","z":"e8836514.834558","name":"エラー回数チェック","property":"error_count","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1030,"y":120,"wires":[[],["907ea484.0ecd18"]]},{"id":"907ea484.0ecd18","type":"change","z":"e8836514.834558","name":"","rules":[{"t":"set","p":"error_count","pt":"msg","to":"1","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":1030,"y":160,"wires":[["42dcae6b.83c96"]]},{"id":"e4829343.1a91c","type":"comment","z":"e8836514.834558","name":"エラーチェック","info":"","x":1010,"y":40,"wires":[]},{"id":"ac06d1b2.49b19","type":"comment","z":"e8836514.834558","name":"SQLー実行","info":"","x":530,"y":360,"wires":[]},{"id":"5702cc3f.b65274","type":"comment","z":"e8836514.834558","name":"https://cloud.ibm.com/apidocs/db2-on-cloud/db2-on-cloud-v4","info":"","x":400,"y":80,"wires":[]},{"id":"5c39894f.1f9918","type":"switch","z":"e8836514.834558","name":"","property":"payload.status","propertyType":"msg","rules":[{"t":"eq","v":"running","vt":"str"},{"t":"eq","v":"failed","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":3,"x":620,"y":680,"wires":[["6000ccd7.171784"],["67cefa56.291034"],[]]},{"id":"6000ccd7.171784","type":"delay","z":"e8836514.834558","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":290,"y":580,"wires":[["727e4a73.67e394"]]},{"id":"16464055.e7f15","type":"comment","z":"e8836514.834558","name":"SQL-結果取得","info":"","x":540,"y":540,"wires":[]}]
```