LoginSignup
9
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@nekijak(KAJIKEN)in株式会社ウィズオノウェア

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

Last updated at Posted at 2019-04-11

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で通知します。

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)

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;
/

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

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

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

まずは権限付与。

GRANT CHANGE NOTIFICATION TO HOGE_SCHEMA;

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

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

// 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();

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

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

SELECT * FROM USER_CHANGE_NOTIFICATION_REGS

余談

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

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

REVOKE CHANGE NOTIFICATION FROM HOGE_SCHEMA;

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

9
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?