node_modules/knex/lib/dialects/oracledb/query/compiler.js

'use strict';

var _ = require('lodash');
var inherits = require('inherits');
var Oracle_Compiler = require('../../oracle/query/compiler');
var ReturningHelper = require('../utils').ReturningHelper;
var BlobHelper = require('../utils').BlobHelper;

function Oracledb_Compiler(client, builder) {
  Oracle_Compiler.call(this, client, builder);
}
inherits(Oracledb_Compiler, Oracle_Compiler);

_.assign(Oracledb_Compiler.prototype, {
  // Compiles an "insert" query, allowing for multiple
  // inserts using a single query statement.
  insert: function insert() {
    var self = this;
    var outBindPrep = this._prepOutbindings(this.single.insert, this.single.returning);
    var outBinding = outBindPrep.outBinding;
    var returning = outBindPrep.returning;
    var insertValues = outBindPrep.values;

    if (Array.isArray(insertValues) && insertValues.length === 1 && _.isEmpty(insertValues[0])) {
      return this._addReturningToSqlAndConvert('insert into ' + this.tableName + ' (' + this.formatter.wrap(this.single.returning) + ') values (default)', outBinding[0], this.tableName, returning);
    }

    if (_.isEmpty(this.single.insert) && typeof this.single.insert !== 'function') {
      return '';
    }

    var insertData = this._prepInsert(insertValues);

    var sql = {};

    if (_.isString(insertData)) {
      return this._addReturningToSqlAndConvert('insert into ' + this.tableName + ' ' + insertData, outBinding[0], this.tableName, returning);
    }

    if (insertData.values.length === 1) {
      return this._addReturningToSqlAndConvert('insert into ' + this.tableName + ' (' + this.formatter.columnize(insertData.columns) + ') values (' + this.formatter.parameterize(insertData.values[0]) + ')', outBinding[0], this.tableName, returning);
    }

    var insertDefaultsOnly = insertData.columns.length === 0;
    sql.returning = returning;
    sql.sql = 'begin ' + _.map(insertData.values, function (value, index) {
      var parameterizedValues = !insertDefaultsOnly ? self.formatter.parameterize(value, self.client.valueForUndefined) : '';
      var subSql = 'insert into ' + self.tableName;

      if (insertDefaultsOnly) {
        // No columns given so only the default value
        subSql += ' (' + self.formatter.wrap(self.single.returning) + ') values (default)';
      } else {
        subSql += ' (' + self.formatter.columnize(insertData.columns) + ') values (' + parameterizedValues + ')';
      }

      var returningClause = '';
      var intoClause = '';
      var usingClause = '';
      var outClause = '';

      _.each(value, function (val) {
        if (!(val instanceof BlobHelper)) {
          usingClause += ' ?,';
        }
      });
      usingClause = usingClause.slice(0, -1);

      // Build returning and into clauses
      _.each(outBinding[index], function (ret) {
        var columnName = ret.columnName || ret;
        returningClause += '"' + columnName + '",';
        intoClause += ' ?,';
        outClause += ' out ?,';

        // Add Helpers to bindings
        if (ret instanceof BlobHelper) {
          return self.formatter.bindings.push(ret);
        }
        self.formatter.bindings.push(new ReturningHelper(columnName));
      });

      // Strip last comma
      returningClause = returningClause.slice(0, -1);
      intoClause = intoClause.slice(0, -1);
      outClause = outClause.slice(0, -1);

      if (returningClause && intoClause) {
        subSql += ' returning ' + returningClause + ' into' + intoClause;
      }

      // Pre bind position because subSql is an execute immediate parameter
      // later position binding will only convert the ? params
      subSql = self.formatter.client.positionBindings(subSql);
      var parameterizedValuesWithoutDefaultAndBlob = parameterizedValues.replace('DEFAULT, ', '').replace(', DEFAULT', '').replace('EMPTY_BLOB(), ', '').replace(', EMPTY_BLOB()', '');
      return 'execute immediate \'' + subSql.replace(/'/g, "''") + (parameterizedValuesWithoutDefaultAndBlob || value ? '\' using ' : '') + parameterizedValuesWithoutDefaultAndBlob + (parameterizedValuesWithoutDefaultAndBlob && outClause ? ',' : '') + outClause + ';';
    }).join(' ') + 'end;';

    sql.outBinding = outBinding;
    if (returning[0] === '*') {
      returning = returning.slice(0, -1);

      // Generate select statement with special order by
      // to keep the order because 'in (..)' may change the order
      sql.returningSql = function () {
        return 'select * from ' + self.tableName + ' where ROWID in (' + this.outBinding.map(function (v, i) {
          return ':' + (i + 1);
        }).join(', ') + ')' + ' order by case ROWID ' + this.outBinding.map(function (v, i) {
          return 'when CHARTOROWID(:' + (i + 1) + ') then ' + i;
        }).join(' ') + ' end';
      };
    }

    return sql;
  },

  _addReturningToSqlAndConvert: function _addReturningToSqlAndConvert(sql, outBinding, tableName, returning) {
    var self = this;
    var res = {
      sql: sql
    };

    if (!outBinding) {
      return res;
    }
    var returningValues = Array.isArray(outBinding) ? outBinding : [outBinding];
    var returningClause = '';
    var intoClause = '';
    // Build returning and into clauses
    _.each(returningValues, function (ret) {
      var columnName = ret.columnName || ret;
      returningClause += '"' + columnName + '",';
      intoClause += '?,';

      // Add Helpers to bindings
      if (ret instanceof BlobHelper) {
        return self.formatter.bindings.push(ret);
      }
      self.formatter.bindings.push(new ReturningHelper(columnName));
    });
    res.sql = sql;

    // Strip last comma
    returningClause = returningClause.slice(0, -1);
    intoClause = intoClause.slice(0, -1);
    if (returningClause && intoClause) {
      res.sql += ' returning ' + returningClause + ' into ' + intoClause;
    }
    res.outBinding = [outBinding];
    if (returning[0] === '*') {
      res.returningSql = function () {
        return 'select * from ' + self.tableName + ' where ROWID = :1';
      };
    }
    res.returning = returning;

    return res;
  },

  _prepOutbindings: function _prepOutbindings(paramValues, paramReturning) {
    var result = {};
    var params = paramValues || [];
    var returning = paramReturning || [];
    if (!Array.isArray(params) && _.isPlainObject(paramValues)) {
      params = [params];
    }
    // Always wrap returning argument in array
    if (returning && !Array.isArray(returning)) {
      returning = [returning];
    }

    var outBinding = [];
    // Handle Buffer value as Blob
    _.each(params, function (values, index) {
      if (returning[0] === '*') {
        outBinding[index] = ['ROWID'];
      } else {
        outBinding[index] = _.clone(returning);
      }
      _.each(values, function (value, key) {
        if (value instanceof Buffer) {
          values[key] = new BlobHelper(key, value);

          // Delete blob duplicate in returning
          var blobIndex = outBinding[index].indexOf(key);
          if (blobIndex >= 0) {
            outBinding[index].splice(blobIndex, 1);
            values[key].returning = true;
          }
          outBinding[index].push(values[key]);
        }
        if (_.isUndefined(value)) {
          delete params[index][key];
        }
      });
    });
    result.returning = returning;
    result.outBinding = outBinding;
    result.values = params;
    return result;
  },

  update: function update() {
    var self = this;
    var sql = {};
    var outBindPrep = this._prepOutbindings(this.single.update, this.single.returning);
    var outBinding = outBindPrep.outBinding;
    var returning = outBindPrep.returning;

    var updates = this._prepUpdate(this.single.update);
    var where = this.where();

    var returningClause = '';
    var intoClause = '';

    if (_.isEmpty(this.single.update) && typeof this.single.update !== 'function') {
      return '';
    }

    // Build returning and into clauses
    _.each(outBinding, function (out) {
      _.each(out, function (ret) {
        var columnName = ret.columnName || ret;
        returningClause += '"' + columnName + '",';
        intoClause += ' ?,';

        // Add Helpers to bindings
        if (ret instanceof BlobHelper) {
          return self.formatter.bindings.push(ret);
        }
        self.formatter.bindings.push(new ReturningHelper(columnName));
      });
    });
    // Strip last comma
    returningClause = returningClause.slice(0, -1);
    intoClause = intoClause.slice(0, -1);

    sql.outBinding = outBinding;
    sql.returning = returning;
    sql.sql = 'update ' + this.tableName + ' set ' + updates.join(', ') + (where ? ' ' + where : '');
    if (outBinding.length && !_.isEmpty(outBinding[0])) {
      sql.sql += ' returning ' + returningClause + ' into' + intoClause;
    }
    if (returning[0] === '*') {

      sql.returningSql = function () {

        var sql = 'select * from ' + self.tableName;
        var modifiedRowsCount = this.rowsAffected.length || this.rowsAffected;
        var returningSqlIn = ' where ROWID in (';
        var returningSqlOrderBy = ') order by case ROWID ';

        // Needs special order by because in(...) change result order
        for (var i = 0; i < modifiedRowsCount; i++) {
          if (this.returning[0] === '*') {
            returningSqlIn += ':' + (i + 1) + ', ';
            returningSqlOrderBy += 'when CHARTOROWID(:' + (i + 1) + ') then ' + i + ' ';
          }
        }
        if (this.returning[0] === '*') {
          this.returning = this.returning.slice(0, -1);
          returningSqlIn = returningSqlIn.slice(0, -2);
          returningSqlOrderBy = returningSqlOrderBy.slice(0, -1);
        }
        return sql += returningSqlIn + returningSqlOrderBy + ' end';
      };
    }

    return sql;
  }

});

module.exports = Oracledb_Compiler;