Node Oracledb Wrapper

oracledb.js

"use strict";
var oracledb = require('oracledb');
var Promise = require('bluebird');
var _ = require('lodash');
var EventEmitter = require('events');
var util = require('util');
var async = require('async');
var NodeDBConnection = require('./NodeDBConnection');
var log ;
function validateConfig(config) {
    if (_.isEmpty(config.user) || _.isEmpty(config.password)) {
        throw new Error("Username or Password can't be empty");
    }
    if (_.isEmpty(config.hostname) || !Number(config.port) || _.isEmpty(config.database)) {
        throw new Error("Hostname, Port or Database can't be empty");
    }
}
function getDataBaseConfig(config) {
    validateConfig(config);
    return {
        user: config.user,
        password: config.password,
        connectString: config.hostname + ":" + config.port + "/" + config.database
    }
}
function createPool(dbconfig) {
    return new Promise(function (resolve, reject) {
        oracledb.createPool(
            dbconfig,
            function (err, pool) {
                if (err) {
                    return reject(err);
                }
                resolve(pool);
            }
        );
    });
}
function NodeDB(config) {
    var that = this;
    EventEmitter.call(that);
    if (config.outFormat == 'array') {
        oracledb.outFormat = oracledb.ARRAY;
    }
    else {
        oracledb.outFormat = oracledb.OBJECT;
    }
    //set max connection size, default would be 10
    oracledb.poolMax = Number(config.poolMax || config.max) || 10;
    //set min connection size, default would be 3
    oracledb.poolMin = Number(config.poolMin || config.min) || 3;

    //The maximum number of rows that are fetched by the execute() call
    oracledb.maxRows = Number(config.maxRows) || 500;
    var verbose = false;
    if (typeof config.verbose !== "undefined") {
        verbose = config.verbose
    }
    log = require('./Logger').getInstance(verbose);
    that.config = config;
    that.patchConnection = false;
    if (typeof config.patchConnection != 'undefined') {
        that.patchConnection = config.patchConnection
    }
    that.pool = null;
    that.buildupScripts = [];
    that.teardownScripts = [];
    that.dbconfig = getDataBaseConfig(that.config);
}
util.inherits(NodeDB, EventEmitter);
NodeDB.OBJECT = oracledb.OBJECT;
NodeDB.ARRAY = oracledb.ARRAY;
NodeDB.EVENTS = {
    POOL_INIT: 'DB:INIT',
    POOL_INIT_ERROR: 'DB:INIT_ERROR'
};
NodeDB.prototype.init = function () {
    var that = this;
    createPool(that.dbconfig).then(function (pool) {
        that.pool = pool;
        that.emit(NodeDB.EVENTS.POOL_INIT, pool);
    }).catch(function (error) {
        that.emit(NodeDB.EVENTS.POOL_INIT_ERROR, error);
    });
    return this;
};
NodeDB.prototype.getPool = function getPool() {
    var that = this;
    return new Promise(function (resolve, reject) {
        if (that.pool) {
            return resolve(that.pool);
        }
        createPool(that.dbconfig).then(function (pool) {
            that.pool = pool;
            return resolve(pool);
        }).catch(function (error) {
            reject(error);
        });
    });
};

NodeDB.prototype.terminatePool = function terminatePool() {
    var that = this;
    return new Promise(function (resolve, reject) {
        if (that.pool) {
            that.pool.terminate(function (err) {
                if (err) {
                    return reject(err);
                }
                resolve();
            });
        } else {
            resolve();
        }
    });
};


NodeDB.prototype.addBuildupSql = function addBuildupSql(statement) {
    var that = this;
    var stmt = {
        sql: statement.sql,
        binds: statement.binds || {},
        options: statement.options || {}
    };
    that.buildupScripts.push(stmt);
};


NodeDB.prototype.addTeardownSql = function addTeardownSql(statement) {
    var that = this;
    var stmt = {
        sql: statement.sql,
        binds: statement.binds || {},
        options: statement.options || {}
    };
    that.teardownScripts.push(stmt);
};


NodeDB.prototype.getConnection = function getConnection(name, cb) {
    var that = this;
    if (typeof cb == 'undefined') {
        cb = name;
        name = "default-connection"
    }
    that.getPool().then(function (pool) {
        pool.getConnection(function (err, connection) {
            var nodedbConnection;
            if (err) {
                return cb(err);
            }
            if (that.patchConnection) {
                nodedbConnection = new NodeDBConnection(connection);
            } else {
                nodedbConnection = connection;
            }
            async.eachSeries(
                that.buildupScripts,
                function (statement, callback) {
                    nodedbConnection.execute(statement.sql, statement.binds, statement.options, function (err) {
                        callback(err);
                    });
                },
                function (err) {
                    if (err) {
                        return cb(err);
                    }
                    cb(null, nodedbConnection);
                }
            );
        });
    })
};
//No need to have context
function executeWithConnection(sql, bindParams, options, connection) {
    if (typeof connection == 'undefined') {
        connection = options;
        options = {};
    }
    if (typeof  connection == 'undefined') {
        connection = bindParams;
        bindParams = [];
    }
    return new Promise(function (resolve, reject) {
        connection.execute(sql, bindParams, options, function (err, results) {
            if (err) {
                return reject(err);
            }

            resolve(results);
        });
    });
}
NodeDB.executeWithConnection = executeWithConnection;

NodeDB.prototype.releaseConnection = function releaseConnection(nodedbConnection, cb) {
    var that = this;
    var connection;
     if (typeof cb != 'function') {
        cb = function none() {};
    }
   if(!nodedbConnection){
        var error = new Error("Error while releasing connection..connection object not defined");
        console.error(error.message);
        cb(error);
    }
     if(that.patchConnection){
        connection = nodedbConnection.connection
    }else{
        connection = nodedbConnection;
    }
    connection.release(function (err) {
        if (err) {
            console.error("Error while releasing connection..", err);
        }
        cb(err);
    });
};

NodeDB.prototype.executeQuery = function executeQuery(sql, bindParams, options, cb) {
    var that = this;
    log("Executing query:: " + (sql || "").replace(/\s+/g, "  "));
    log("Binding params:: " + JSON.stringify(bindParams));
    log("Options:: " + JSON.stringify(options));
    that.getConnection(function (error, connection) {
        if (error) {
            return cb(error);
        }
        executeWithConnection(sql, bindParams, options, connection)
            .then(function (results) {
                log("No of rows found:" + (results.rows ? results.rows.length : 0));
                log("No of rows updated:" + (results.rowsAffected ? results.rowsAffected : 0));
                process.nextTick(function () {
                    that.releaseConnection(connection);
                });
                cb(null, results);

            })
            .catch(function (err) {
                process.nextTick(function () {
                    that.releaseConnection(connection);
                });
                cb(err);
            });
    })
};
NodeDB.prototype.execute = function execute(sql, bindParams, options, cb) {
    var that = this;
    if (arguments.length < 3) {
        throw new Error("IllegalArgumentProvide! Expecting at least 3 arguments");
    }
    if (typeof cb == 'undefined') {
        cb = options;
        options = {};
    }
    options.autoCommit = true;
    if (typeof  bindParams !== 'object') {
        bindParams = [];
    }
    that.executeQuery(sql, bindParams, options, function (error, result) {
        if (error) {
            return cb(error, result);
        }
        return cb(error, result);
    });
};
NodeDB.prototype.oracleErrorKey = function(err) {
    var errorDetails = err.message && err.message.split(":") ; //Generated Error Key
    switch (errorDetails[0]) {
        case 'ORA-00001':
            return "ORA_UNIQUE_KEY_VIOLATED";
        case 'ORA-00051':
            return "ORA_RESOURCE_WAITING_TIME_OUT";
        case 'ORA-00100':
            return "ORA_NO_DATA_FOUND";
        case 'ORA-03113':
            return "ORA_END_FILE_CHANNEL";
        case 'ORA-00604':
            return "ORA_RECURSIVE_SQL";
        case 'ORA-00936':
            return "ORA_MISSING_EXPRESSION";
        case 'ORA-01555':
            return "ORA_OLD_SNAP_SHOT";
        case 'ORA-27101':
            return "ORA_SHARED_MEMORY_REALM";
        case 'ORA-00911':
            return "ORA_INVALID_CHARACTER";
        case 'ORA-00933':
            return "ORA_SQL_NOT_PROPERLY_ENDED";
            break;
        case 'ORA-01422':
            return "ORA_FETCH_MORE_RECORD";
        case 'ORA-04030':
            return "ORA_OUT_OF_PROCESS_MEMORY";
            break;
        case 'ORA-00932':
            return "ORA_INCONSISTANT_DATA_TYPE";
        case 'ORA-01031':
            return "ORA_INSUFFICIENT_PRIVILEGES";
        case 'ORA-01008':
            return "ORA_NOT_ALL_VARIABLES_BOUND";
        case 'ORA-00907':
            return "ORA_MISSING_RIGHT_PARENTHESIS";
        case 'ORA-03135':
            return "ORA_CONNECTION_LOST_CONTACT";
        case 'ORA-01034':
            return "ORA_ORACLE_NOT_AVAILABLE";
        case 'ORA-00918':
            return "ORA_COLUMN_AMBIGUITY";
            break;
        case 'ORA-00600':
            return "ORA_INTERNAL_ERROR_CODE";
        default :
            return "ORA_DEFAULT"
    }
};
//Issue with node latest version, https://github.com/oracle/node-oracledb/issues/593
//Exit process on sigint, SIGTERM
process
    .on('SIGTERM', function () {
        console.log("ORACLE-DB: Terminating");
        process.exit(0);
    })
    .on('SIGINT', function () {
        console.log("ORACLE-DB: Terminating");
        process.exit(0);
    });
module.exports = NodeDB;