@kcom/chkdba-oracle

Overview

This package contains reusable code that is used by Kcom chkdba processe to download and compare Oracle schemas.

Initialisation

Call app.init() with the Oracle connection and credentials object, which is used simply for logging, however the 'user' property is required.
Call app.use() and provide either an array of modules or a single module. Pass the list of default modules ordinarily.
Call app.download() to select the user schema and store to JSON file.
Call app.check() to select the user schema and compare to a prior downloaded JSON file.
Call app.outputStatements() to log to the console the differences, including what remeial action to take.

Sample Invocation

const dotenvResult = require('dotenv').config({ silent: false });
const fs = require('fs-extra');
const oracledb = require('oracledb');
const debugModule = require('debug');
const cmdlne = require('commander');
const kcomchkdba = require('@kcom/chkdba-oracle');

const { app } = kcomchkdba;

// Use bluebird to get better stack traces when using async/wait
// They're not 100% great but at least you get a bit more.
global.Promise = require('bluebird');

process.env.BLUEBIRD_LONG_STACK_TRACES = 1;

const debug = debugModule('chkora');
const trace = debugModule('chkora:trace');

/* eslint no-console: "off" */
/* eslint max-len: "off" */

/* these lines ensure logging (debug & trace) are output to stdout instead
   of the default stderr. This is really only to allow VSCode to show logging
   when debugging, since the debug console doesn't appear to show stderr */
debug.log = console.log.bind(console);
trace.log = console.log.bind(console);

function error(s) {
  console.error(s);
}

cmdlne
  .version('1.0.0', '-v, --version')
  .option('-c, --check', 'check schema against reference json file. Used at deployment time, not build.')
  .option('-d, --download', 'download reference schema, which is used only at project build time.')
  .option('-u, --user [username]', 'optional username. Overrides ORA_USR environment variable')
  .option('-p, --password [password]', 'optional password. Overrides ORA_PWD environment variable')
  .option('-h, --hostname [hostname]', 'optional hostname. Overrides ORA_HST environment variable')
  .option('-s, --service [service]', 'optional service name. Overrides ORA_SID environment variable')
  .option('-f, --filename [filename]', 'optional input|output filename. Default=results.json')
  .option('--debug', 'optional enabling of debug output level')
  .option('--trace', 'optional enabling of trace output level')
  .option('--synchronous', 'forces synchronous working which is slower but helps with debugging and tracing')
  .parse(process.argv);

/* optionally turn on trace logging if needed */
if (cmdlne.trace) {
  /* if tracing, enable trace and debug */
  debugModule.enable('chkora*');
} else if (cmdlne.debug) {
  /* if debug required, enable debug logging but not trace */
  debugModule.enable('chkora*,-chkora*trace');
}

if (cmdlne.synchronous) {
  process.env.synchronous_chkora = '1';
}

if (dotenvResult.error) {
  error('WARNING : .env file not found!');
  // throw dotenvResult.error;
} else {
  /* output the customised parameters after parsing .env file */
  debug(`.env file parsed:`);
  const arrEnvs = Object.entries(dotenvResult.parsed);
  arrEnvs.forEach((env) => {
    trace(`${env[0]}=${env[1]}`);
  });
}

/* this is a pollyfil that allows us to use .finally in promise chains. Not currently used in this app at time of writing. */
if (!Promise.prototype.finally) {
  Promise.prototype.finally = function p(cb) {
    const res = () => this;
    const fin = () => Promise.resolve(cb()).then(res);
    return this.then(fin, fin);
  };
}

/* setup Oracle connection credentials, based upon environment variables or command line options */
const dbconfig = {
  user: cmdlne.user || process.env.ORA_USR,
  password: cmdlne.password || process.env.ORA_PWD,
  connectString: `${cmdlne.hostname || process.env.ORA_HST}/${cmdlne.service || process.env.ORA_SID}`
};

/* close database connection, safely without exceptions being thrown. */
async function doRelease(connection) {
  if (connection) {
    try {
      await connection.close();
    } catch (err) {
      error(`exception closing connection - ${err.message}`);
    }
  }
}

/**
 * called when downloading or checking mode.
 * it will conntact to the given oracle instance and execute all the SQL statements before processing
 * and then storing in a single object which is then returned from this function.
 *
 * Failure to connect will result in an exception being thrown, otherwise this function will return the
 * object that contains all of the resultset data.
 *
 * This function is written using 'async' which means it should be called using await
 */
async function doDownload() {
  let connection;
  let result = null;

  try {
    debug(`connecting to ${dbconfig.user}/${dbconfig.password}@${dbconfig.connectString}`);
    connection = await oracledb.getConnection(dbconfig);
    debug('connected ok');

    app.init(connection, dbconfig);
    /* use default modules */
    app.use(kcomchkdba.modules);
    /* add any local modules here if needed */

    try {
      result = await app.download();
    } catch (err) {
      trace(err.message);
      err.stack += (new Error()).stack;
      throw err;
    }
  } catch (err) {
    trace(err.message);
    err.stack += (new Error()).stack;
    throw err;
  } finally {
    doRelease(connection);
  }
  /* return data */
  return result;
}

/* default filename is results.json otherwise override from command line */
const filename = cmdlne.filename || 'results.json';

/**
 * main function that either downloads data from reference schema and saves to json (download)
 * or downloads data from comparand schema and compares it to the reference schema and outputs
 * the statements of difference.
 */
(async function main() {
  let dataOrigin;

  try {
    if (cmdlne.download) {
      // get data from origin schema to save to disk
      dataOrigin = await doDownload();
      if (!dataOrigin) {
        error(`unable to download origin data`);
        process.exit(1);
      }
      debug(`origin data downloaded ok`);
      // write combined result to file
      try {
        await fs.writeFile(filename, JSON.stringify(dataOrigin, null, 2));
      } catch (err) {
        trace(err.message);
        err.stack += (new Error()).stack;
        throw err;
      }
      debug(`origin data successfully written to ${filename}`);
    } else {
      // load data from disk which is the origin reference schema
      debug(`loading origin data from ${filename}`);
      try {
        const d = await fs.readFile(filename);
        dataOrigin = JSON.parse(d);
        debug(`origin data successfully loaded from ${filename}`);
      } catch (err) {
        trace(err.message);
        err.stack += (new Error()).stack;
        throw err;
      }
    }
    if (!dataOrigin) {
      error(`no origin data loaded.`);
      process.exit(1);
    }

    // if want to check then do it
    if (cmdlne.check) {
      // get data from comparand (not-origin) schema to save to disk
      const dataComparand = await doDownload();
      if (!dataComparand) {
        error(`unable to download comparand data.`);
        process.exit(1);
      }
      debug(`comparand data downloaded ok`);
      // write comparand result to file
      await fs.writeFile(`comparand-${filename}`, JSON.stringify(dataComparand, null, 2));
      debug(`origin data successfully written to comparand-${filename}`);

      // do the comparison
      app.doCompare(dataOrigin, dataComparand);

      // output the statements identifying the differences and remedial actions
      app.doOutputStatements();
    }

    if (!cmdlne.download && !cmdlne.check) {
      cmdlne.outputHelp();
    }
  } catch (ex) {
    /* output stack trace */
    error(ex.stack);
    /* exit with error */
    process.exit(1);
  }
}());