query.js

'use strict';

var Utils = require('../../utils')
  , AbstractQuery = require('../abstract/query')
  , uuid = require('node-uuid')
  , sequelizeErrors = require('../../errors.js');

module.exports = (function() {
  var Query = function(connection, sequelize, callee, options) {
    this.connection = connection;
    this.callee = callee;
    this.sequelize = sequelize;
    this.uuid = uuid.v4();
    this.options = Utils._.extend({
      logging: console.log,
      plain: false,
      raw: false
    }, options || {});

    var self = this;
    this.checkLoggingOption();
  };

  Utils.inherit(Query, AbstractQuery);

  Query.prototype.getInsertIdField = function() {
    return 'id';
  };

  Query.prototype.run = function(sql) {
    var self = this;
    this.sql = sql;

    if (this.options.logging !== false) {
      this.sequelize.log('Executing (' + this.connection.uuid + '): ' + this.sql);
    }

    var promise = new Utils.Promise(function(resolve, reject) {

      console.log(self.sql);

    //   self
    //     .connection
    //     .lib
    //     .step('',{ query: self.sql })
    //     .error(function (err) {
    //       console.log('err:', err);
    //       err.sql = sql;
    //       reject(self.formatError(err));
    //     })
    //     .end(function (result) {
    //       console.log('res', result.query);
    //       resolve(self.formatResults(result.query));
    //     });
    // });

    var request, transCommand;
    if (self.connection._transaction) {

      request = new self.connection.Request(self.connection._transaction);

      if (self.sql === 'COMMIT TRANSACTION;') {
        transCommand = 'commit';
      } else if (self.sql === 'ROLLBACK TRANSACTION;') {
        transCommand = 'rollback';
      }

      if (self.sql === 'COMMIT TRANSACTION;' ||
          self.sql === 'ROLLBACK TRANSACTION;') {
        self.connection._transaction[transCommand](function (err, result) {
          if (err) {
            console.log(err.message);
            reject(self.formatError(err));
          } else {
            resolve(self.formatResults(result));
          }
        });
        return promise;
      }
    } else {
      request = new self.connection.Request();
    }

    request.query(self.sql, function(err, recordset) {
        promise.emit('sql', self.sql, self.connection.uuid);
        if(err){
          console.log(err.message);
          reject(self.formatError(err));
        } else {
          resolve(self.formatResults(recordset));
        }
      });
    });
    return promise;
  };

    /**
   * High level function that handles the results of a query execution.
   *
   *
   * Example:
   *  query.formatResults([
   *    {
   *      id: 1,              // this is from the main table
   *      attr2: 'snafu',     // this is from the main table
   *      Tasks.id: 1,        // this is from the associated table
   *      Tasks.title: 'task' // this is from the associated table
   *    }
   *  ])
   *
   * @param {Array} data - The result of the query execution.
   */
  Query.prototype.formatResults = function(data) {
    var result = this.callee;
    //console.log(data);
    if(data){
      if (this.isInsertQuery(data)) {
        this.handleInsertQuery(data);
      } else if (this.isShowTableQuery()) {
        result = this.handleShowTableQuery(data);
      } else if (this.isShowOrDescribeQuery()) {
        result = data;
        if (this.sql.toLowerCase().indexOf("select c.name, t.name as 'type', c.is_nullable as isnull") === 0) {
          result = {};
          data.forEach(function(_result) {
            if(_result.Default)
              _result.Default = _result.Default.replace('(\'','').replace('\')','').replace(/'/g,'');
            result[_result.Name] = {
              type: _result.Type.toUpperCase(),
              allowNull: _result.IsNull,
              defaultValue: _result.Default
            };
          });
        } else if (this.isShowIndexesQuery()) {
          result = data;
        }
      } else if (this.isSelectQuery()) {
        result = this.handleSelectQuery(data);
      } else if (this.isCallQuery()) {
        result = data[0];
      } else if (this.isBulkUpdateQuery() || this.isBulkDeleteQuery()) {
        result = data.affectedRows;
      }
    }else{
      result = null;
    }
    return result;
  };

  Query.prototype.formatError = function (err) {
    var match;

    switch (err.errno || err.code) {
      case 1062:
        match = err.message.match(/Duplicate entry '(.*)' for key '?(.*?)$/);

        return new sequelizeErrors.UniqueConstraintError({
          fields: null,
          index: match[2],
          value: match[1],
          parent: err
        });

      case 1451:
        match = err.message.match(/FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)(?: ON .*)?\)$/);

        return new sequelizeErrors.ForeignKeyConstraintError({
          fields: null,
          index: match[3],
          parent: err
        });

      case 1452:
        match = err.message.match(/FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)(.*)\)$/);

        return new sequelizeErrors.ForeignKeyConstraintError({
          fields: null,
          index: match[1],
          parent: err
        });

      default:
        return new sequelizeErrors.DatabaseError(err);
    }
  };
  Query.prototype.isShowOrDescribeQuery = function() {
    var result = false;

    result = result || (this.sql.toLowerCase().indexOf("select c.name, t.name as 'type', c.is_nullable as isnull") === 0);
    result = result || (this.sql.toLowerCase().indexOf('select tablename = t.name, name = ind.name,') === 0);

    return result;
  };

  Query.prototype.isShowIndexesQuery = function () {
    return this.sql.toLowerCase().indexOf('select tablename = t.name, name = ind.name,') === 0;
  };

  Query.prototype.handleShowIndexesQuery = function (data) {
    // Group by index name, and collect all fields
    data = Utils._.foldl(data, function (acc, item) {
      if (!(item.Key_name in acc)) {
        acc[item.Key_name] = item;
        item.fields = [];
      }

      acc[item.Key_name].fields[item.Seq_in_index - 1] = {
        attribute: item.Column_name,
        length: item.Sub_part || undefined,
        order: item.Collation === 'A' ? 'ASC' : undefined
      };
      delete item.column_name;

      return acc;
    }, {});

    return Utils._.map(data, function(item) {
      return {
        primary: item.Key_name === 'PRIMARY',
        fields: item.fields,
        name: item.Key_name,
        tableName: item.Table,
        unique: (item.Non_unique !== 1),
        type: item.Index_type,
      };
    });
  };

  AbstractQuery.prototype.handleInsertQuery = function(results, metaData) {
    if (this.callee) {
      // add the inserted row id to the instance
      var autoIncrementField = this.callee.Model.autoIncrementField
        , id = null;
      id = id || (results && results[0][this.getInsertIdField()]);
      id = id || (metaData && metaData[this.getInsertIdField()]);
      this.callee[autoIncrementField] = id;
    }
  };

  return Query;
})();