dataTypeBlob.js

/* Copyright (c) 2015, 2023, 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
 *   41. dataTypeBlob.js
 *
 * DESCRIPTION
 *    Testing Oracle data type support - BLOB.
 *    This test corresponds to example files:
 *         blobinsert1.js, blobstream1.js and blobstream2.js
 *    Firstly, Loads an image data and INSERTs it into a BLOB column.
 *    Secondly, SELECTs the BLOB and pipes it to a file, blobstreamout.jpg
 *    Thirdly, SELECTs the BLOB and compares it with the original image
 *
 *****************************************************************************/
'use strict';

const oracledb = require('oracledb');
const fs       = require('fs');
const assert   = require('assert');
const dbConfig = require('./dbconfig.js');
const assist   = require('./dataTypeAssist.js');
const testsUtil = require(`./testsUtil.js`);
const random   = require('./random.js');

const inFileName = 'test/fuzzydinosaur.jpg';  // contains the image to be inserted
const outFileName = 'test/blobstreamout.jpg';

describe('41. dataTypeBlob.js', function() {

  let connection = null;
  const tableName = "nodb_myblobs";

  before('get one connection', async function() {
    connection = await oracledb.getConnection(dbConfig);
  });

  after('release connection', async function() {
    await connection.close();
  });

  describe('41.1 testing BLOB data type', function() {
    before('create table', async function() {
      await connection.execute(assist.sqlCreateTable(tableName));
    });

    after(async function() {
      await connection.execute("DROP table " + tableName + " PURGE");
    });

    it('41.1.1 stores BLOB value correctly', async function() {

      let result = await connection.execute(
        `INSERT INTO nodb_myblobs (num, content) VALUES (:n, EMPTY_BLOB()) RETURNING content INTO :lobbv`,
        { n: 2, lobbv: {type: oracledb.BLOB, dir: oracledb.BIND_OUT} },
        { autoCommit: false });  // a transaction needs to span the INSERT and pipe()

      assert.strictEqual(result.rowsAffected, 1);
      assert.strictEqual(result.outBinds.lobbv.length, 1);

      const inStream = await fs.createReadStream(inFileName);

      let lob = result.outBinds.lobbv[0];
      await new Promise((resolve, reject) => {
        inStream.on('error', reject);
        lob.on('error', reject);
        lob.on('finish', resolve);
        inStream.pipe(lob);
      });
      await connection.commit();
      result = await connection.execute(
        "SELECT content FROM nodb_myblobs WHERE num = :n",
        { n: 2 });

      lob = result.rows[0][0];

      await new Promise((resolve, reject) => {
        const outStream = fs.createWriteStream(outFileName);
        lob.on('error', reject);
        outStream.on('error', reject);
        outStream.on('finish', resolve);
        lob.pipe(outStream);
      });
      await connection.commit();
      const originalData = await fs.promises.readFile(inFileName);
      const generatedData = await fs.promises.readFile(outFileName);
      assert.deepStrictEqual(originalData, generatedData);
      result = await connection.execute(
        "SELECT content FROM nodb_myblobs WHERE num = :n",
        { n: 2 });
      lob = result.rows[0][0];
      const blob = await lob.getData();
      const data = await fs.promises.readFile(inFileName);
      assert.deepStrictEqual(data, blob);
      fs.unlinkSync(outFileName);
    }); // 41.1.1

    it('41.1.2 BLOB getData()', async function() {

      let result = await connection.execute(
        `INSERT INTO nodb_myblobs (num, content) ` +
        `VALUES (:n, EMPTY_BLOB()) RETURNING content INTO :lobbv`,
        { n: 3, lobbv: {type: oracledb.BLOB, dir: oracledb.BIND_OUT} },
        { autoCommit: false });  // a transaction needs to span the INSERT and pipe()

      assert.strictEqual(result.rowsAffected, 1);
      assert.strictEqual(result.outBinds.lobbv.length, 1);

      const inStream = fs.createReadStream(inFileName);
      let lob = result.outBinds.lobbv[0];

      await new Promise((resolve, reject) => {
        lob.on('error', reject);
        inStream.on('error', reject);
        lob.on('finish', resolve);
        inStream.pipe(lob);  // pipes the data to the BLOB
      });
      await connection.commit();
      result = await connection.execute(
        "SELECT content FROM nodb_myblobs WHERE num = :n",
        { n: 3 });

      lob = result.rows[0][0];

      const data = await fs.promises.readFile(inFileName);
      const blob = await lob.getData();
      assert.deepStrictEqual(data, blob);
    }); // 41.1.2

    it('41.1.3 BLOB getData(offset, len)', async function() {
      const size = 32768;
      const specialStr = "41.1.3";
      const bigStr = random.getRandomString(size, specialStr);
      const bufferStr = Buffer.from(bigStr, "utf-8");

      let result = await connection.execute(
        `INSERT INTO nodb_myblobs (num, content) VALUES (:1, :2) `,
        [4, bufferStr]);

      assert.strictEqual(result.rowsAffected, 1);
      result = await connection.execute(
        "SELECT content FROM nodb_myblobs WHERE num = :n",
        { n: 4 });

      const lob = result.rows[0][0];

      // both offset and end.
      let offset = 5;
      let len = 10;

      // Returns data from offset -1 (lob[offset - 1])
      let blob = await lob.getData(offset, len);
      assert.deepStrictEqual(bufferStr.subarray(offset - 1,
        offset + len - 1), blob);

      // end not specified gives entire data starting from offset
      offset = 5;
      blob = await lob.getData(offset);
      assert.deepStrictEqual(bufferStr.subarray(offset - 1), blob);

      // large number of bytes starting from offset 5.
      offset = 5;
      len = 9999;
      blob = await lob.getData(offset, len);
      assert.deepStrictEqual(bufferStr.subarray(offset - 1, offset + len - 1),
        blob);

      // large length which is ignored and entire lob data starting
      // from offset is returned.
      offset = 5;
      len = 99999;
      blob = await lob.getData(offset, len);
      assert.deepStrictEqual(bufferStr.subarray(offset - 1),
        blob);

      // Invalid offset, we return null.
      offset = 99999;
      len = 10;
      blob = await lob.getData(offset, len);
      assert.equal(blob, null);

    }); // 41.1.3
  }); //41.1

  describe('41.2 stores null value correctly', function() {
    it('41.2.1 testing Null, Empty string and Undefined', async function() {
      await assist.verifyNullValues(connection, tableName);
    });
  });

  describe('41.3 OSON column metadata ', function() {
    let isRunnable = false;
    const TABLE = "nodb_myblobs_oson_col";
    const createTable = (`CREATE TABLE ${TABLE} (
        IntCol number(9) not null,
        OsonCol  blob not null,
        blobCol  blob not null,
        constraint TestOsonCols_ck_1 check (OsonCol is json format oson)
        )`
    );
    const plsql = testsUtil.sqlCreateTable(TABLE, createTable);

    before('create table', async function() {
      if (testsUtil.getClientVersion() >= 2100000000 &&
        connection.oracleServerVersion >= 2100000000) {
        isRunnable = true;
      }

      if (!isRunnable) {
        this.skip();
      }

      // Allows automatically converting OSON formatted columns to JSON objects.
      oracledb.future.oldJsonColumnAsObj = true;
      await connection.execute(plsql);
    });

    after(async function() {
      oracledb.future.oldJsonColumnAsObj = false;
      await connection.execute(testsUtil.sqlDropTable(TABLE));
    });

    it('41.3.1 Verify isOson flag in column metadata', async function() {
      const result = await connection.execute(`select * from ${TABLE}`);
      assert.strictEqual(result.metaData[0].isOson, false);
      assert.strictEqual(result.metaData[1].isOson, true);
      assert.strictEqual(result.metaData[2].isOson, false);
    }); // 41.3.1

    it('41.3.2 Verify Basic encode/decode OSON on OSON format column', async function() {
      const expectedObj1 = {key1: "val1"};
      const expectedObj2 = {key2: "val2"};
      const expectedObj3 = [new Float32Array([1, 2]), [1, 2]];
      const byteBuf = Buffer.from(JSON.stringify((expectedObj1)));

      // Insert Buffer into OSON format column and verify with decode.
      let result = await connection.execute(`insert into ${TABLE}(IntCol, OsonCol, blobCol)
      values (1, :1, :2) `,
      [byteBuf, byteBuf]);
      result = await connection.execute(`select OSONCOL from ${TABLE}`);
      assert.deepStrictEqual(expectedObj1, result.rows[0][0]);

      // Generate OSON bytes and insert these bytes and verify with decode.
      const osonBytes = connection.encodeOSON(expectedObj2);
      result = await connection.execute(`insert into ${TABLE}(IntCol, OsonCol, blobCol)
      values (2, :1, :2) `,
      [osonBytes, byteBuf]);
      result = await connection.execute(`select OSONCOL from ${TABLE} where IntCol = 2`);
      assert.deepStrictEqual(expectedObj2, result.rows[0][0]);

      // Verify vector inside OSON image for 23.4 server onwards.
      if (connection.oracleServerVersion >= 2304000000) {
        result = await connection.execute(`insert into ${TABLE}(IntCol, OsonCol, blobCol)
      values (3, :1, :2) `,
        [connection.encodeOSON(expectedObj3), byteBuf]);
        result = await connection.execute(`select OSONCOL from ${TABLE} where IntCol = 3`);
        assert.deepStrictEqual(expectedObj3, result.rows[0][0]);

        // Verify LOB is returned by default (oracledb.future.oldJsonColumnAsObj = false).
        // We need to explicitly use decodeOSON to convert the LOB data into JSON object.
        oracledb.future.oldJsonColumnAsObj = false;
        result = await connection.execute(`select OSONCOL from ${TABLE} where IntCol = 3`);
        const lob = result.rows[0][0];
        const generatedObj = connection.decodeOSON(await lob.getData());
        assert.deepStrictEqual(expectedObj3, generatedObj);
      }

      // Check invalid values to decodeOSON
      assert.rejects(
        () => connection.decodeOSON(Buffer.from('invalid')),
        /NJS-113:/
      );
      assert.rejects(
        () => connection.decodeOSON('invalid'),
        /NJS-005:/
      );
      assert.rejects(
        () => connection.decodeOSON(),
        /NJS-009:/
      );
    }); // 41.3.2

  }); //41.3

});