node-oracledb/test/inBandNotification.js

263 lines
9.0 KiB
JavaScript

/* Copyright (c) 2024, Oracle and/or its affiliates. */
/******************************************************************************
*
* This software is dual-licensed to you under the Universal Permissive License
* (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
* 2.0 as shown at https://www.apache.org/licenses/LICENSE-2.0. You may choose
* either license.
*
* If you elect to accept the software under the Apache License, Version 2.0,
* the following applies:
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* NAME
* 308. inBandNotification.js
* No special setup is required but the test makes use of debugging packages
* that are not intended for normal use.
* dbms_tg_dbg.set_session_drainable, here debug package (dbms_tg_dbg) is used to simulate an inband notification.
*/
'use strict';
const oracledb = require('oracledb');
const assert = require('assert');
const dbConfig = require('./dbconfig.js');
const testsUtil = require('./testsUtil.js');
describe('308. Inband Notification', function() {
let isRunnable = false;
const DBA_config = dbConfig.test.DBA_PRIVILEGE ? {
user: dbConfig.test.DBA_user,
password: dbConfig.test.DBA_password,
connectString: dbConfig.connectString,
privilege: oracledb.SYSDBA
} : null;
let dbaConn;
before(async function() {
isRunnable = await testsUtil.checkPrerequisites(1800000000, 1800000000);
if (!dbConfig.test.DBA_PRIVILEGE || !isRunnable) this.skip();
dbaConn = await oracledb.getConnection(DBA_config);
await dbaConn.execute(`GRANT EXECUTE ON dbms_tg_dbg TO ${dbConfig.user}`);
await dbaConn.execute(`GRANT SELECT ON v_$session TO ${dbConfig.user}`);
});
after(async function() {
if (dbConfig.test.DBA_PRIVILEGE && isRunnable) {
await dbaConn.execute(`REVOKE EXECUTE ON dbms_tg_dbg FROM ${dbConfig.user}`);
await dbaConn.execute(`REVOKE SELECT ON v_$session FROM ${dbConfig.user}`);
await dbaConn.close();
}
});
describe('308.1 Standalone Connection', function() {
let conn;
beforeEach(async function() {
conn = await oracledb.getConnection(dbConfig);
});
afterEach(async function() {
await conn.close();
});
it('308.1.1 test standalone connection is marked unhealthy', async function() {
assert.strictEqual(conn.isHealthy(), true);
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
const result = await conn.execute("SELECT user FROM dual");
assert.strictEqual(result.rows[0][0], dbConfig.user.toUpperCase());
assert.strictEqual(conn.isHealthy(), false);
}); // 308.1.1
it('308.1.2 test connection health after executing a long-running query', async function() {
assert.strictEqual(conn.isHealthy(), true);
const majorDBVersion = await testsUtil.getMajorDBVersion();
// Appropriate sleep procedure based on the database version
const sleepProc = (majorDBVersion >= 18) ? 'dbms_session.sleep' : 'dbms_lock.sleep';
await conn.execute(`
BEGIN
${sleepProc}(5);
END;
`);
assert.strictEqual(conn.isHealthy(), true);
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn.isHealthy(), false);
}); // 308.1.2
it('308.1.3 test connection health after a PL/SQL error', async function() {
assert.strictEqual(conn.isHealthy(), true);
await assert.rejects(
async () => await conn.execute(`
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Test error');
END;
`),
/ORA-20001/ // Test error
);
assert.strictEqual(conn.isHealthy(), true);
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn.isHealthy(), false);
}); // 308.1.3
it('308.1.4 test connection health after setting session drainable multiple times', async function() {
assert.strictEqual(conn.isHealthy(), true);
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn.isHealthy(), false);
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn.isHealthy(), false);
}); // 308.1.4
it('308.1.5 test connection health after ORA-00001 unique constraint error', async function() {
const tableName = 'emp';
const sql = `CREATE TABLE ${tableName} (
id NUMBER primary key,
VALUE varchar(30)
)`;
await conn.execute(testsUtil.sqlCreateTable(tableName, sql));
await assert.rejects(
async () => await conn.execute(`
BEGIN
INSERT INTO ${tableName} (id, value) VALUES (1, 'test');
INSERT INTO ${tableName} (id, value) VALUES (1, 'test'); -- Will cause ORA-00001
END;
`),
/ORA-00001/
);
assert.strictEqual(conn.isHealthy(), true);
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn.isHealthy(), false);
}); // 308.1.5
it('308.1.6 test connection health after multiple drainable sessions in quick succession', async function() {
assert.strictEqual(conn.isHealthy(), true);
for (let i = 0; i < 5; i++) {
await conn.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn.isHealthy(), false);
}
}); // 308.1.6
});
describe('308.2 Pooled Connection', function() {
let pool;
before(async function() {
pool = await oracledb.createPool({
...dbConfig,
poolMin: 1,
poolMax: 2,
poolIncrement: 1
});
});
after(async function() {
await pool.close(0);
});
it('308.2.1 test pooled connection that is marked unhealthy', async function() {
/* Skip this test for clients before 23.1 due to inconsistent behavior
of OCI_ATTR_SERVER_STATUS on subsequent calls.
*/
if (!oracledb.thin && testsUtil.getClientVersion() < 2301000000)
this.skip();
const conn1 = await pool.getConnection();
assert.strictEqual(conn1.isHealthy(), true);
await conn1.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
const info = await getSidSerial(conn1);
assert.strictEqual(conn1.isHealthy(), false);
await conn1.close();
const conn2 = await pool.getConnection();
assert.strictEqual(conn2.isHealthy(), true);
const newInfo = await getSidSerial(conn2);
assert.notStrictEqual(newInfo, info);
await conn2.close();
}); // 308.2.1
it('308.2.2 test multiple connections in a pool with inband notifications', async function() {
const conn1 = await pool.getConnection();
const conn2 = await pool.getConnection();
assert.strictEqual(conn1.isHealthy(), true);
assert.strictEqual(conn2.isHealthy(), true);
await conn1.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn1.isHealthy(), false);
assert.strictEqual(conn2.isHealthy(), true);
await conn1.close();
await conn2.close();
}); // 308.2.2
it('308.2.3 test connection reuse after marking unhealthy', async function() {
const conn1 = await pool.getConnection();
assert.strictEqual(conn1.isHealthy(), true);
await conn1.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn1.isHealthy(), false);
await conn1.close();
const conn2 = await pool.getConnection();
assert.strictEqual(conn2.isHealthy(), true);
await conn2.close();
}); // 308.2.3
it('308.2.4 test pool draining with multiple sessions', async function() {
const conn1 = await pool.getConnection();
const conn2 = await pool.getConnection();
assert.strictEqual(conn1.isHealthy(), true);
assert.strictEqual(conn2.isHealthy(), true);
await conn1.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn1.isHealthy(), false);
await conn2.execute("BEGIN dbms_tg_dbg.set_session_drainable; END;");
assert.strictEqual(conn2.isHealthy(), false);
await conn1.close();
await conn2.close();
const conn3 = await pool.getConnection();
assert.strictEqual(conn3.isHealthy(), true);
await conn3.close();
}); // 308.2.4
});
async function getSidSerial(conn) {
const result = await conn.execute(
"SELECT sid || ',' || serial# AS info FROM v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID')"
);
return result.rows[0][0];
}
});