1386 lines
48 KiB
JavaScript
1386 lines
48 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
|
|
* 297. jsonDualityViews7.js
|
|
*
|
|
* DESCRIPTION
|
|
* Testing JSON Relational Duality View using vector data type
|
|
*
|
|
*****************************************************************************/
|
|
'use strict';
|
|
|
|
const oracledb = require('oracledb');
|
|
const assert = require('assert');
|
|
const dbConfig = require('./dbconfig.js');
|
|
const testsUtil = require('./testsUtil.js');
|
|
|
|
describe('297. jsonDualityViews7.js', function() {
|
|
|
|
let connection = null;
|
|
let dbaConn = null;
|
|
let isRunnable = false;
|
|
|
|
const username = 'njs_jsonDv7';
|
|
|
|
before(async function() {
|
|
isRunnable = await testsUtil.checkPrerequisites(2304000000, 2304000000);
|
|
if (dbConfig.test.drcp || !(isRunnable && dbConfig.test.DBA_PRIVILEGE) || dbConfig.test.isCmanTdm) {
|
|
this.skip();
|
|
}
|
|
|
|
const dbaCredential = {
|
|
user: dbConfig.test.DBA_user,
|
|
password: dbConfig.test.DBA_password,
|
|
connectString: dbConfig.connectString,
|
|
privilege: oracledb.SYSDBA,
|
|
};
|
|
|
|
const pwd = testsUtil.generateRandomPassword();
|
|
dbaConn = await oracledb.getConnection(dbaCredential);
|
|
await dbaConn.execute(`create user ${username} identified by ${pwd}`);
|
|
await dbaConn.execute(`grant create session, resource, connect, unlimited tablespace to ${username}`);
|
|
connection = await oracledb.getConnection({user: username,
|
|
password: pwd,
|
|
connectString: dbConfig.connectString
|
|
});
|
|
});
|
|
|
|
after(async function() {
|
|
if (dbConfig.test.drcp || !(isRunnable && dbConfig.test.DBA_PRIVILEGE) || dbConfig.test.isCmanTdm) return;
|
|
await connection.close();
|
|
|
|
await dbaConn.execute(`drop user ${username} cascade`);
|
|
await dbaConn.close();
|
|
});
|
|
|
|
describe('297.1 Base table having Vector datatype column', function() {
|
|
const table_name = 'vector_base_table';
|
|
before(async function() {
|
|
const sql = `
|
|
create table ${table_name}(
|
|
vec_id number,
|
|
first varchar(128),
|
|
con_col VECTOR,
|
|
constraint vec_id_pk primary key (vec_id)
|
|
)`;
|
|
|
|
// create the vector table
|
|
const plsql = testsUtil.sqlCreateTable(table_name, sql);
|
|
await connection.execute(plsql);
|
|
});
|
|
|
|
after(async function() {
|
|
await connection.execute(`drop table ${table_name} PURGE`);
|
|
});
|
|
|
|
it('297.1.1 insert vector data in table and views', async function() {
|
|
const arr = [1, 2, 3, 4, 6];
|
|
const float32arr = new Float32Array(arr);
|
|
|
|
await connection.execute(
|
|
`insert into ${table_name} (vec_id, first, con_col) values (:1, :2, :3)`,
|
|
{
|
|
1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_NUMBER, val: 1 },
|
|
2: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: 'Harry' },
|
|
3: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32arr }
|
|
}
|
|
);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv1 AS
|
|
${table_name} @insert @update @delete {
|
|
_id : vec_id,
|
|
FIRST : first,
|
|
CON_COL : con_col
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv1`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0])._id, 1);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, arr);
|
|
}); // 297.1.1
|
|
|
|
it('297.1.2 create duality View using SQL syntax', async function() {
|
|
const arr = [1, 2, 3, 4, 6];
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv2 AS
|
|
SELECT JSON{
|
|
'_id' : a.vec_id,
|
|
'FIRST' : a.first,
|
|
'CON_COL' : a.con_col WITH (CHECK)
|
|
} from ${table_name} a with (INSERT,UPDATE,DELETE)
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv2`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0])._id, 1);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, arr);
|
|
}); // 297.1.2
|
|
|
|
it('297.1.3 create duality View using json{*} syntax', async function() {
|
|
const arr = [1, 2, 3, 4, 6];
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv3 as
|
|
select json{*} from ${table_name} a with (INSERT,UPDATE,DELETE)
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv3`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, arr);
|
|
}); // 297.1.3
|
|
|
|
it('297.1.4 create duality View using JSONIZE syntax', async function() {
|
|
const arr = [1, 2, 3, 4, 6];
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv4 as
|
|
JSONIZE(
|
|
${table_name} with (insert,update,delete){*}
|
|
)`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv4`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, arr);
|
|
}); // 297.1.4
|
|
|
|
it('297.1.5 perform insert through duality view ', async function() {
|
|
const arr = [4, 5, 6, 7];
|
|
const float32arr = new Float32Array(arr);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv as
|
|
${table_name} @insert @update @delete {
|
|
_id : vec_id,
|
|
FIRST : first,
|
|
CON_COL : con_col
|
|
}`);
|
|
|
|
await connection.execute(
|
|
`insert into ${table_name} (vec_id, first, con_col) values (:1, :2, :3)`,
|
|
{
|
|
1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_NUMBER, val: 2 },
|
|
2: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: 'Harry' },
|
|
3: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32arr }
|
|
}
|
|
);
|
|
|
|
await connection.commit();
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv adv where adv.data."_id" = 2`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0])._id, 2);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, arr);
|
|
}); // 297.1.5
|
|
|
|
it('297.1.6 inserting null to a vector column through JSON Duality view ', async function() {
|
|
const arr = [null];
|
|
const float32arr = new Float32Array(arr);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv AS
|
|
${table_name} @insert @update @delete {
|
|
_id : vec_id,
|
|
FIRST : first,
|
|
CON_COL : con_col
|
|
}`);
|
|
|
|
await connection.execute(
|
|
`insert into ${table_name} (vec_id, first, con_col) values (:1, :2, :3)`,
|
|
{
|
|
1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_NUMBER, val: 3 },
|
|
2: { dir: oracledb.BIND_IN, type: oracledb.STRING, val: 'Harry' },
|
|
3: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32arr }
|
|
}
|
|
);
|
|
|
|
await connection.commit();
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv adv where adv.data."_id" = 3`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0])._id, 3);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, [0]);
|
|
}); // 297.1.6
|
|
|
|
it('297.1.7 update the vector column ', async function() {
|
|
const arr = [1, 2, 3];
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
UPDATE jsondv adv
|
|
SET data = json_transform(data, SET '$.CON_COL' = json('[1,2,3]'))
|
|
WHERE adv.data."_id" = 2
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select json_serialize(data pretty) from jsondv adv where adv.data."_id" = 2`);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0])._id, 2);
|
|
assert.strictEqual(JSON.parse(result.rows[0][0]).FIRST, "Harry");
|
|
assert.deepStrictEqual(JSON.parse(result.rows[0][0]).CON_COL, arr);
|
|
}); // 297.1.7
|
|
|
|
it('297.1.8 select using vector column (using vector column in where clause)', async function() {
|
|
|
|
// select from JSON relational duality view
|
|
await assert.rejects(
|
|
async () => await connection.execute(`
|
|
select * from jsondv adv where
|
|
json_value(data, '$.CON_COL' returning VECTOR) = '[1,2,3]'
|
|
`),
|
|
/ORA-22848:/ // ORA-22848: cannot use VECTOR type as comparison key
|
|
);
|
|
}); // 297.1.8
|
|
|
|
it('297.1.9 create Duality View with vector column as flex column', async function() {
|
|
|
|
// Create the JSON relational duality view
|
|
// VECTOR COLUMN + FLEX COLUMN
|
|
await assert.rejects(
|
|
async () => await connection.execute(`
|
|
CREATE OR REPLACE JSON DUALITY VIEW jsondv AS
|
|
${table_name} @insert @update @delete {
|
|
_id : vec_id,
|
|
firstName : first,
|
|
con_col @flex
|
|
}
|
|
`),
|
|
/ORA-42628:/ /* ORA-42628: Flexible column 'VECTOR.CON_COL' is not JSON(Object) data type in
|
|
* JSON-Relational duality View 'VECTORDV'.
|
|
*/
|
|
);
|
|
}); // 297.1.9
|
|
});
|
|
|
|
describe('297.2 With multiple tables', function() {
|
|
|
|
before(async function() {
|
|
let sql = `
|
|
create table vector(
|
|
vec_id number,
|
|
VectorCol vector,
|
|
VectorFixedCol vector(2),
|
|
Vector32Col vector(10, float32),
|
|
Vector64Col vector(10, float64),
|
|
VectorInt8Col vector(4, int8),
|
|
VectorFlexCol vector(*, float32),
|
|
constraint pk_vector primary key (vec_id)
|
|
)`;
|
|
|
|
// create the vector table
|
|
let plsql = testsUtil.sqlCreateTable('vector', sql);
|
|
await connection.execute(plsql);
|
|
|
|
sql = `
|
|
create table vector_class (
|
|
vec_id number primary key,
|
|
vcid number,
|
|
clsid number,
|
|
constraint fk_vector_class1 foreign key (vec_id) references vector(vec_id)
|
|
)`;
|
|
|
|
// create the vector_class table
|
|
plsql = testsUtil.sqlCreateTable('vector_class', sql);
|
|
await connection.execute(plsql);
|
|
});
|
|
|
|
after(async function() {
|
|
await connection.execute(`drop table vector_class PURGE`);
|
|
await connection.execute(`drop table vector PURGE`);
|
|
});
|
|
|
|
afterEach(async function() {
|
|
await connection.execute(`TRUNCATE TABLE vector_class`);
|
|
await connection.execute(`TRUNCATE TABLE vector`);
|
|
});
|
|
|
|
it('297.2.1 insert float32 vector data in table and views', async function() {
|
|
|
|
// insert data into vector table
|
|
const FloatArray = new Float32Array([0 * 0.23987, 1 * -0.23987, 2 * -0.23987]);
|
|
|
|
// inserting a float32 array
|
|
await connection.execute(`insert into vector (vec_id, VectorCol)
|
|
values(1, :2)`, {2: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR,
|
|
val: FloatArray
|
|
}});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
assert.strictEqual(result.rows.length, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[0, -0.23986999690532684, -0.4797399938106537]);
|
|
}); // 297.2.1
|
|
|
|
it('297.2.2 insert float32 vector data with dimension 10', async function() {
|
|
|
|
// Create a Float32Array
|
|
const float32Array = new Float32Array(
|
|
[1.23, 4, -7.89, 10.11, -12.13, 14.15, -16.17, 18.19, -20.21, 9.23]);
|
|
|
|
// inserting a float32 array
|
|
await connection.execute(`insert into vector (vec_id, Vector32Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32Array }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector32Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[1.2300000190734863, 4, -7.889999866485596,
|
|
10.109999656677246, -12.130000114440918, 14.149999618530273,
|
|
-16.170000076293945, 18.190000534057617, -20.209999084472656, 9.229999542236328]);
|
|
}); // 297.2.2
|
|
|
|
it('297.2.3 insert float64 vector data', async function() {
|
|
|
|
// Create a Float64Array
|
|
const float64Array = new Float64Array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]);
|
|
|
|
// inserting a float32 array
|
|
await connection.execute(`insert into vector (vec_id, Vector64Col)
|
|
values(2, :2)`, {2: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR,
|
|
val: float64Array
|
|
}});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (2, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector64Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 2);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]);
|
|
}); // 297.2.3
|
|
|
|
it('297.2.4 insert fixed vector data', async function() {
|
|
|
|
// inserting a float32 array
|
|
await connection.execute(`insert into vector (vec_id, VectorFixedCol) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: [1, 2] }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorFixedCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[1, 2]);
|
|
}); // 297.2.4
|
|
|
|
it('297.2.5 insert int8 vector data', async function() {
|
|
|
|
// Create a int8Arr
|
|
const int8arr = new Int8Array([126, 125, -126, -23]);
|
|
|
|
await connection.execute(`insert into vector (vec_id, VectorInt8Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: int8arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[126, 125, -126, -23]);
|
|
}); // 297.2.5
|
|
|
|
it('297.2.6 insert int8 typed array to float64 vector column', async function() {
|
|
|
|
// Create a int8arr
|
|
const int8arr = new Int8Array([126, 125, -126, -23, 11, 12, -11, -12, 10, 10]);
|
|
|
|
await connection.execute(`insert into vector (vec_id, Vector64Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: int8arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector64Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[126, 125, -126, -23, 11, 12, -11, -12, 10, 10]);
|
|
}); // 297.2.6
|
|
|
|
it('297.2.7 insert int8 typed array to flex vector column', async function() {
|
|
|
|
// Create a int8arr
|
|
const int8arr = new Int8Array([126, 125, -126, -23, 11, 12, -11, -12, 10, 10]);
|
|
|
|
await connection.execute(`insert into vector (vec_id, VectorFlexCol) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: int8arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorFlexCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[126, 125, -126, -23, 11, 12, -11, -12, 10, 10]);
|
|
}); // 297.2.7
|
|
|
|
it('297.2.8 insert int8 typed array to float32 vector column', async function() {
|
|
|
|
// Create a int8arr
|
|
const int8arr = new Int8Array([126, 125, -126, -23, 11, 12, -11, -12, 10, 10]);
|
|
|
|
await connection.execute(`insert into vector (vec_id, Vector32Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: int8arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector32Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[126, 125, -126, -23, 11, 12, -11, -12, 10, 10]);
|
|
}); // 297.2.8
|
|
|
|
it('297.2.9 insert a float32 typed array into an int8 vector column', async function() {
|
|
|
|
// Create a Float32Array
|
|
const float32Array = new Float32Array(
|
|
[-5, 4, -7, 6]);
|
|
await connection.execute(`insert into vector (vec_id, VectorInt8Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32Array }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[-5, 4, -7, 6]);
|
|
}); // 297.2.9
|
|
|
|
it('297.2.10 insert a float64 into an int8 vector column', async function() {
|
|
|
|
// Create a Float32Array
|
|
const float64Array = new Float64Array(
|
|
[-5, 4, -7, 6]);
|
|
await connection.execute(`insert into vector (vec_id, VectorInt8Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float64Array }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[-5, 4, -7, 6]);
|
|
}); // 297.2.10
|
|
|
|
it('297.2.11 insert vector as clob to Int8 column', async function() {
|
|
const arr = Array(4).fill(2);
|
|
let lob = await connection.createLob(oracledb.CLOB);
|
|
|
|
// Write the buffer to the CLOB
|
|
await lob.write(JSON.stringify(arr));
|
|
|
|
await connection.execute(
|
|
`INSERT INTO vector
|
|
(vec_id, VectorInt8Col) VALUES (:id, :clob)`,
|
|
{ id: 1,
|
|
clob: lob
|
|
});
|
|
|
|
lob.destroy();
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
lob = result.rows[0][0].vector_name;
|
|
|
|
// Convert the CLOB data to a string directly
|
|
const clobData = await lob.toString('utf8');
|
|
|
|
assert.strictEqual(clobData, arr.toString('utf8'));
|
|
}); // 297.2.11
|
|
|
|
it.skip('297.2.12 insert clob with array of 65535 elements to a vector column', async function() {
|
|
/* Bug: 36455264
|
|
Sorting JSON data with a large size is not feasible.
|
|
*/
|
|
const arr = Array(65535).fill(2);
|
|
const lob = await connection.createLob(oracledb.CLOB);
|
|
|
|
// Write the buffer to the CLOB
|
|
await lob.write(JSON.stringify (arr));
|
|
|
|
await connection.execute(
|
|
`INSERT INTO vector
|
|
(vec_id, VectorCol) VALUES (:id, :clob)`,
|
|
{ id: 1,
|
|
clob: lob
|
|
}
|
|
);
|
|
|
|
lob.destroy();
|
|
await connection.execute(
|
|
`insert into vector_class values (1, 1, 1)`
|
|
);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
await assert.rejects(
|
|
async () => await connection.execute(`select * from vector_ov order by 1`),
|
|
/ORA-51862:/ /*
|
|
ORA-51862: VECTOR library processing error in 'qjsnErrHndl'
|
|
JZN-00755: vector too large for comparison or indexing operation
|
|
*/
|
|
);
|
|
}); // 297.2.12
|
|
|
|
it('297.2.13 insert clob with array of less elements to a vector column', async function() {
|
|
const arr = Array(10).fill(2);
|
|
let lob = await connection.createLob(oracledb.CLOB);
|
|
|
|
// Write the buffer to the CLOB
|
|
await lob.write(JSON.stringify (arr));
|
|
|
|
await connection.execute(
|
|
`INSERT INTO vector
|
|
(vec_id, VectorCol) VALUES (:id, :clob)`,
|
|
{ id: 1,
|
|
clob: lob
|
|
});
|
|
|
|
lob.destroy();
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
lob = result.rows[0][0].vector_name;
|
|
|
|
// Convert the CLOB data to a string directly
|
|
const clobData = await lob.toString('utf8');
|
|
|
|
assert.strictEqual(clobData, arr.toString('utf8'));
|
|
}); // 297.2.13
|
|
|
|
it.skip('297.2.14 insert a float32 vector with 65535 dimensions into a vector column of same dimensions', async function() {
|
|
/* Bug: 36455264
|
|
Sorting JSON data with a large size is not feasible.
|
|
*/
|
|
const arr = Array(65535).fill(2.5);
|
|
const float32arr = new Float32Array(arr);
|
|
|
|
await connection.execute(`insert into vector (vec_id, VectorFlexCol) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorFlexCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
await assert.rejects(
|
|
async () => await connection.execute(`select * from vector_ov order by 1`),
|
|
/ORA-51862:/ /*
|
|
ORA-51862: VECTOR library processing error in 'qjsnErrHndl'
|
|
JZN-00755: vector too large for comparison or indexing operation
|
|
*/
|
|
);
|
|
}); // 297.2.14
|
|
|
|
it('297.2.15 insert a float32 vector with less dimensions into a vector column of same dimensions', async function() {
|
|
const arr = Array(10).fill(2.5);
|
|
const float32arr = new Float32Array(arr);
|
|
|
|
await connection.execute(`insert into vector (vec_id, VectorFlexCol) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float32arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorFlexCol,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
arr);
|
|
}); // 297.2.15
|
|
|
|
it('297.2.16 insert a float64 typed array created from ArrayBuffer', async function() {
|
|
const elements = [8.1, 7.2, 6.3, 5.4, 4.5, 3.6, 2.7, 1.8, 9.9, 0.0];
|
|
const arrBuf = new ArrayBuffer(128);
|
|
|
|
// Create typed array of 10 elements from byteOffset 8
|
|
const float64Arr = new Float64Array(arrBuf, 8, 10);
|
|
|
|
// initialize
|
|
elements.forEach((element, index) => {
|
|
float64Arr[index] = element;
|
|
});
|
|
await connection.execute(`insert into vector (vec_id, Vector64Col) values (1, :1)`,
|
|
{1: { dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: float64Arr }});
|
|
|
|
await connection.execute(`
|
|
insert into vector_class values (1, 1, 1)
|
|
`);
|
|
|
|
await connection.commit();
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector64Col,
|
|
vector_class: vector_class @insert@update@delete {
|
|
vector_class_id: vcid,
|
|
vector_id: vec_id
|
|
}
|
|
}
|
|
`);
|
|
|
|
// Select data from the view
|
|
const result = await connection.execute(`select * from vector_ov order by 1`);
|
|
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.strictEqual(result.rows[0][0].vector_class[0].vector_class_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[8.1, 7.2, 6.3, 5.4, 4.5, 3.6, 2.7, 1.8, 9.9, 0.0]);
|
|
}); // 297.2.16
|
|
});
|
|
|
|
describe('297.3 Sanity DMLs', function() {
|
|
before(async function() {
|
|
let sql = `
|
|
create table vector(
|
|
vec_id number,
|
|
VectorCol vector,
|
|
VectorFixedCol vector(2),
|
|
Vector32Col vector(10, float32),
|
|
Vector64Col vector(10, float64),
|
|
VectorInt8Col vector(4, int8),
|
|
VectorFlexCol vector(*, *),
|
|
constraint pk_vector primary key (vec_id)
|
|
)`;
|
|
|
|
// create the vector table
|
|
let plsql = testsUtil.sqlCreateTable('vector', sql);
|
|
await connection.execute(plsql);
|
|
|
|
sql = `
|
|
create table vector_class (
|
|
vec_id number primary key,
|
|
vcid number,
|
|
clsid number,
|
|
constraint fk_vector_class1 foreign key (vec_id) references vector(vec_id)
|
|
)`;
|
|
|
|
// create the vector_class table
|
|
plsql = testsUtil.sqlCreateTable('vector_class', sql);
|
|
await connection.execute(plsql);
|
|
});
|
|
|
|
after(async function() {
|
|
await connection.execute(`drop table vector_class PURGE`);
|
|
await connection.execute(`drop table vector PURGE`);
|
|
});
|
|
|
|
afterEach(async function() {
|
|
await connection.execute(`TRUNCATE TABLE vector_class`);
|
|
await connection.execute(`TRUNCATE TABLE vector`);
|
|
});
|
|
|
|
it('297.3.1 insert a float32 typed array into tables from JSON DV', async function() {
|
|
const FloatArray = new Float32Array([0 * 0.23987, 1 * -0.23987, 2 * -0.23987]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorCol
|
|
}
|
|
`);
|
|
|
|
// insert data into vector table
|
|
const bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": FloatArray} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[0, -0.23986999690532684, -0.4797399938106537]);
|
|
}); // 297.3.1
|
|
|
|
it('297.3.2 insert a 65535 dimension float32 typed array into tables from JSON DV', async function() {
|
|
const arr = Array(65535).fill(2.5);
|
|
const FloatArray = new Float32Array(arr);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorFlexCol
|
|
}
|
|
`);
|
|
|
|
// insert data into vector table
|
|
const bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": FloatArray} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
arr);
|
|
}); // 297.3.2
|
|
|
|
it('297.3.3 insert a 65536 dimension float32 typed array into tables from JSON DV', async function() {
|
|
const arr = Array(65536).fill(2.5);
|
|
|
|
const FloatArray = new Float32Array(arr);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
VECTOR @INSERT@UPDATE@DELETE {
|
|
vector_id: vec_id,
|
|
vector_name: VectorFlexCol
|
|
}
|
|
`);
|
|
|
|
// insert data into vector table
|
|
const bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": FloatArray} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
arr);
|
|
}); // 297.3.3
|
|
|
|
it('297.3.4 insert a float32 typed array into tables from JSON DV wrong dimension', async function() {
|
|
const FloatArray = new Float32Array([0 * 0.23987, 1 * -0.23987]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
VECTOR @INSERT@UPDATE@DELETE {
|
|
vector_id: vec_id,
|
|
vector_name: Vector32Col
|
|
}
|
|
`);
|
|
|
|
// insert data into vector table
|
|
const bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": FloatArray} }};
|
|
await assert.rejects(
|
|
async () => await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv),
|
|
/ORA-42692:/);
|
|
}); // 297.3.4
|
|
|
|
it('297.3.5 update a float32 typed array into tables from JSON DV', async function() {
|
|
let FloatArray = new Float32Array([0 * 0.23987, 1 * -0.23987, 2 * -0.23987]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorCol
|
|
}
|
|
`);
|
|
|
|
// insert data into vector table
|
|
const bv1 = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": FloatArray} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv1
|
|
);
|
|
|
|
// Update
|
|
FloatArray = new Float32Array([10, 12, 14]);
|
|
const bv2 = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: FloatArray }};
|
|
await connection.execute(`
|
|
update vector_ov s set data = JSON_TRANSFORM(data, SET '$.vector_name'= :c) where s.data.vector_id = 1`,
|
|
bv2
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[10, 12, 14]);
|
|
}); // 297.3.5
|
|
|
|
it('297.3.6 delete a float32 typed array into tables from JSON DV', async function() {
|
|
|
|
// insert data into vector table
|
|
const FloatArray1 = new Float32Array([0 * 0.23987, 1 * -0.23987, 2 * -0.23987]);
|
|
const FloatArray2 = new Float32Array([1 * 0.23987, 2 * -0.23987, 3 * -0.23987]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorCol
|
|
}
|
|
`);
|
|
|
|
// insert FloatArray1
|
|
let bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": FloatArray1} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
// insert FloatArray2
|
|
bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 2, "vector_name": FloatArray2} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
// Delete
|
|
await connection.execute(`delete vector_ov where json_value(data,'$.vector_id')=1`);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 2);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[0.23986999690532684, -0.4797399938106537, -0.7196099758148193]);
|
|
}); // 297.3.6
|
|
|
|
it('297.3.7 insert a float64 into tables from JSON DV', async function() {
|
|
|
|
// create float64 Array
|
|
const float64Array = new Float64Array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
VECTOR @INSERT@UPDATE@DELETE {
|
|
vector_id: vec_id,
|
|
vector_name: Vector64Col
|
|
}
|
|
`);
|
|
|
|
// insert data into vector table
|
|
const bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": float64Array} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]);
|
|
}); // 297.3.7
|
|
|
|
it('297.3.8 update a float64 into tables from JSON DV', async function() {
|
|
|
|
// insert data into vector table
|
|
const float64Array = new Float64Array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
create or replace json relational duality view vector_ov
|
|
as
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector64Col
|
|
}
|
|
`);
|
|
|
|
const bv1 = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": float64Array} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv1
|
|
);
|
|
|
|
// Update
|
|
const FloatArray = new Float64Array([10, 12, 14, 16, 18, 20, 22, 24, 26, 28]);
|
|
const bv2 = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: FloatArray }};
|
|
await connection.execute(`
|
|
update vector_ov s set data = JSON_TRANSFORM(data, SET '$.vector_name'= :c) where s.data.vector_id = 1`,
|
|
bv2
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[10, 12, 14, 16, 18, 20, 22, 24, 26, 28]);
|
|
}); // 297.3.8
|
|
|
|
it('297.3.9 delete a float64 into tables from JSON DV', async function() {
|
|
|
|
// insert data into vector table
|
|
const float64Array1 = new Float64Array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]);
|
|
const float64Array2 = new Float64Array([10, 11, 12, 13, 14, 15, 16, 17, 18, 19]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: Vector64Col
|
|
}
|
|
`);
|
|
|
|
// insert FloatArray1
|
|
let bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": float64Array1} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
// insert FloatArray2
|
|
bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 2, "vector_name": float64Array2} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
// Delete
|
|
await connection.execute(`delete vector_ov where json_value(data,'$.vector_id')=1`);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 2);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[10, 11, 12, 13, 14, 15, 16, 17, 18, 19]);
|
|
}); // 297.3.9
|
|
|
|
it('297.3.10 insert a VectorInt8Col typed array into tables from JSON DV', async function() {
|
|
|
|
// Create a int8Arr
|
|
const int8arr = new Int8Array([126, 125, -126, -23]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col
|
|
}
|
|
`);
|
|
|
|
const bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": int8arr} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[126, 125, -126, -23]);
|
|
}); // 297.3.10
|
|
|
|
it('297.3.11 update a VectorInt8Col typed array into tables from JSON DV', async function() {
|
|
|
|
// Create a int8Arr
|
|
const int8arr = new Int8Array([126, 125, -126, -23]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col
|
|
}
|
|
`);
|
|
|
|
const bv1 = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": int8arr} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv1
|
|
);
|
|
|
|
// Update
|
|
const int8arr2 = new Int8Array([-33, 123, 45, 31]);
|
|
const bv2 = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_VECTOR, val: int8arr2 }};
|
|
await connection.execute(`
|
|
update vector_ov s set data = JSON_TRANSFORM(data, SET '$.vector_name'= :c) where s.data.vector_id = 1`,
|
|
bv2
|
|
);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 1);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[-33, 123, 45, 31]);
|
|
}); // 297.3.11
|
|
|
|
it('297.3.12 delete a VectorInt8Col typed array into tables from JSON DV', async function() {
|
|
|
|
// Create a int8Arr
|
|
const int8arr1 = new Int8Array([126, 125, -126, -23]);
|
|
const int8arr2 = new Int8Array([-33, 123, 45, 31]);
|
|
|
|
// Create the JSON relational duality view
|
|
await connection.execute(`
|
|
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vector_ov
|
|
AS
|
|
vector @insert@update@delete {
|
|
vector_id: vec_id,
|
|
vector_name: VectorInt8Col
|
|
}
|
|
`);
|
|
|
|
// insert int8Arr
|
|
let bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 1, "vector_name": int8arr1} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
// insert int8arr2
|
|
bv = {c: {dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_JSON, val: {"vector_id": 2, "vector_name": int8arr2} }};
|
|
await connection.execute(`
|
|
insert into vector_ov values (:c)`,
|
|
bv
|
|
);
|
|
|
|
// Delete
|
|
await connection.execute(`delete vector_ov where json_value(data,'$.vector_id')=1`);
|
|
|
|
const result = await connection.execute(`select * from vector_ov`);
|
|
assert.strictEqual(result.rows[0][0].vector_id, 2);
|
|
assert.deepStrictEqual(Array.from(result.rows[0][0].vector_name),
|
|
[-33, 123, 45, 31]);
|
|
}); // 297.3.12
|
|
});
|
|
});
|