Node.js で Oracle DBのテーブル変更を検知する

## TL;DR

データベース変更通知機能 Oracle Database Continuous Query Notification を利用して実現可能。
https://docs.oracle.com/database/121/JJDBC/dbchgnf.htm#JJDBC28815

## データが変わったことをプログラム側で検知する

データが変わったことを検知する方法は、大きく分けて`Pull`と`Push`の２通りが考えられます。

1. `Pull`は、クライアントプログラムで一定時間ごとにデータを取得し、データが変わっているかどうかを判定します。主にポーリングと呼ばれます。
2. `Push`は、データが変わったことをクライアントプログラムにサーバーから通知してもらう方法です。通知受付用APIやリスナーをクライアント側に用意します。

それぞれの良し悪しはありますが、今回はデータ変更の検知スピードが重要視されていたので`Push`で実現方法を検討します。

## DBサーバーで変更を検知する(TRIGGER) -> 失敗

データの変更といえば`TRIGGER`ですね。
変更を検知したらクライアントプログラムにHTTPで通知します。

```sql
CREATE OR REPLACE TRIGGER TRIGGER_HOGE_TABLE
AFTER INSERT OR UPDATE OR DELETE
ON HOGE_TABLE
DECLARE
  vResponse VARCHAR2(100);
  v_errcode number;
  v_errmsg  varchar2(100);
BEGIN
  INSERT INTO HOGE (HOGE) VALUES ('start');
  vResponse := UTL_HTTP.REQUEST('http://162.168.0.2:3000/fuga');
  INSERT INTO HOGE (HOGE) VALUES (vResponse);
EXCEPTION
  WHEN OTHERS THEN
    v_errcode := sqlcode;
    v_errmsg  := substr(sqlerrm, 1, 100);
    INSERT INTO HOGE (HOGE) VALUES ('error');
    INSERT INTO HOGE (HOGE) VALUES (TO_CHAR(v_errcode));
    INSERT INTO HOGE (HOGE) VALUES (v_errmsg);
END;
/
```

Oracle 11g以降で`UTL_HTTP.REQUEST`を使うためには権限が必要です。

```
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
```

```sql
BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    ACL => 'NETWORK_ACL_HOGE',
    DESCRIPTION => 'ACL for REST',
    PRINCIPAL => 'スキーマ名',
    IS_GRANT => TRUE,
    PRIVILEGE => 'connect',
    START_DATE => NULL,
    END_DATE => NULL);
    COMMIT;
END;
/
```

```sql
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => '/sys/acls/acl_smtp.xml',
host => 'ホスト名',
lower_port => 3001,
upper_port => NULL);
END;
/
```

```sql
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'NETWORK_ACL_1F858DA0B2597454E0538FF412AC543C',
principal => 'スキーマ名',
is_grant => TRUE,
privilege => 'connect');
END;
/
```

```sql
SELECT * FROM DBA_NETWORK_ACLS;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;
```

なんやかんや頑張った挙句、**トリガーが発動するタイミングはコミット時ではない**ということに気付く。
TRIGGERはデータが変わったタイミングで発動するので、その時点ではまだコミットされていない。
クライアントに通知された後でロールバックされると、ピンポンダッシュになる。

コミット時にリフレッシュされるMaterialized Viewを使って、無理やり実現することはできたが、どうもにも気持ちが悪い。しかも遅い。

## DBサーバーで変更を検知する(CQN) -> 本題

データベース変更通知機能 Oracle Database Continuous Query Notification を利用して実現できそうだったので、これを試してみることにした。
https://docs.oracle.com/database/121/JJDBC/dbchgnf.htm#JJDBC28815


まずは権限付与。

```sql
GRANT CHANGE NOTIFICATION TO HOGE_SCHEMA;
```

DB側の設定はこれで終わり。
クライアント側から監視対象を登録する形となる。
今回のクライアントプログラムは`Node.js`だったので、`node-oracledb`を利用することになる。

https://github.com/oracle/node-oracledb

こちらの記事を参考に…
https://blogs.oracle.com/opal/demo-oracle-database-continuous-query-notification-in-nodejs

```javascript
// Oracle DB
const oracledb = require("oracledb");

// Return Object
oracledb.outFormat = oracledb.OBJECT;
oracledb.fetchAsString = [ oracledb.DATE, oracledb.NUMBER ];

// Continuous Query Notification(CQN) Event Mode
oracledb.events = true;

// Connection Pooling
const pool = await oracledb.createPool({
"user": "HOGE_SCHEMA",
"password": "PIYO",
"connectString": `
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 168.0.0.3)(PORT = 1234))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hogedb)
    )
  )
`,
"poolMax": 20,        // 最大プール数
"poolMin": 2,         // 最小プール数
"poolIncrement": 1,   // 足りない場合に増やす数
"poolTimeout": 60,    // プールが未使用の場合にクローズするまでの秒数[default:60] 最小プール数は維持される
"queueTimeout": 60000 // 接続要求キューで待機している処理のタイムアウト ミリ秒[default:60000 ms]
});

// CQN
const cqn_con = await pool.getConnection();
await cqn_con.subscribe("hogesub", {
"callback": async message => {
  // 登録解除イベントは処理しない
  if (!message || !message.type || message.type == oracledb.SUBSCR_EVENT_TYPE_DEREG) {
    return;
  }

  // HOGEのイベント通知
  if (message.tables.some(val => val.name == "HOGE_SCHEMA.HOGE")) {
    // HOGEテーブルを取得して処理する
  }

  // FUGAのイベント通知
  if (message.tables.some(val => val.name == "HOGE_SCHEMA.FUGA")) {
    // FUGAテーブルを取得して処理する
  }

  // PIYOのイベント通知
  if (message.tables.some(val => val.name == "HOGE_SCHEMA.PIYO")) {
    // PIYOテーブルを取得して処理する
  }

  // HOGERAのイベント通知
  if (message.tables.some(val => val.name == "HOGE_SCHEMA.HOGERA")) {
    // HOGERAテーブルを取得して処理する
  }
},
"sql": "SELECT * FROM HOGE WHERE STATUS = 'Use'",
"port": 3002,
"groupingClass" : oracledb.SUBSCR_GROUPING_CLASS_TIME,
"groupingValue" : 1, // 1秒以内の通知はまとめる
"groupingType" : oracledb.SUBSCR_GROUPING_TYPE_SUMMARY
});

// 監視対象２個目以降
await Promise.all([
cqn_con.subscribe("hogesub", { "sql": "SELECT * FROM FUGA" }),
cqn_con.subscribe("hogesub", { "sql": "SELECT * FROM PIYO WHERE STATUS = 'Use'" }),
cqn_con.subscribe("hogesub", { "sql": "SELECT ID,SCRIPT_NAME,DESCRIPTION FROM HOGERA WHERE DELETE_FLG = 0" })
]);

:
:
// プログラム終了時(監視終了)
await cqn_con.unsubscribe("hogesub");
await cqn_con.release();
```

これで対象のテーブルに対して変更(コミット)があった場合に通知と処理が出来るようになりました。
しかもかなり早く検知してくれる。

監視対象の登録は以下で確認できる。

```sql
SELECT * FROM USER_CHANGE_NOTIFICATION_REGS
```

## 余談

タイムアウトまたは、初回受けたらパージするオプションを入れていない場合、DB側に登録が残り続ける。
プログラム側で解除をするのが正しいが、もし忘れた場合は上のSQLで確認したREGIDを指定して解除する。

```sql
BEGIN
  DBMS_CQ_NOTIFICATION.DEREGISTER(21);
END;
```

解除する…。いやいや、解除できないんだけど。

```
ORA-29970: Specified registration id does not exist
ORA-06512: at "SYS.DBMS_CHANGE_NOTIFICATION", line 3
ORA-06512: at "SYS.DBMS_CHANGE_NOTIFICATION", line 72
ORA-06512: at line 4
```

Oracleパッチが当たっていないと解除できないらしい。
https://stackoverflow.com/questions/46831869/delete-oracle-change-notifications

```sql
REVOKE CHANGE NOTIFICATION FROM HOGE_SCHEMA;
```

権限をはく奪すれば、無事全部消えました。セーフ。