text: 'use strict';
  
  const _ = require('lodash');
  
  const Utils = require('./utils');
  const DataTypes = require('./data-types');
  const SQLiteQueryInterface = require('./dialects/sqlite/query-interface');
  const MSSSQLQueryInterface = require('./dialects/mssql/query-interface');
  const MySQLQueryInterface = require('./dialects/mysql/query-interface');
  const PostgresQueryInterface = require('./dialects/postgres/query-interface');
  const Transaction = require('./transaction');
  const Promise = require('./promise');
  const QueryTypes = require('./query-types');
  const Op = require('./operators');
  
  /**
   * The interface that Sequelize uses to talk to all databases
   *
   * @class QueryInterface
   */
  class QueryInterface {
    constructor(sequelize) {
      this.sequelize = sequelize;
      this.QueryGenerator = this.sequelize.dialect.QueryGenerator;
    }
  
    /**
     * Creates a schema
     *
     * @param {string} schema    Schema name to create
     * @param {Object} [options] Query options
     *
     * @returns {Promise}
     */
    createSchema(schema, options) {
      options = options || {};
      const sql = this.QueryGenerator.createSchema(schema);
      return this.sequelize.query(sql, options);
    }
  
    /**
     * Drops a schema
     *
     * @param {string} schema    Schema name to drop
     * @param {Object} [options] Query options
     *
     * @returns {Promise}
     */
    dropSchema(schema, options) {
      options = options || {};
      const sql = this.QueryGenerator.dropSchema(schema);
      return this.sequelize.query(sql, options);
    }
  
    /**
     * Drop all schemas
     *
     * @param {Object} [options] Query options
     *
     * @returns {Promise}
     */
    dropAllSchemas(options) {
      options = options || {};
  
      if (!this.QueryGenerator._dialect.supports.schemas) {
        return this.sequelize.drop(options);
      } else {
        return this.showAllSchemas(options).map(schemaName => this.dropSchema(schemaName, options));
      }
    }
  
    /**
     * Show all schemas
     *
     * @param {Object} [options] Query options
     *
     * @returns {Promise<Array>}
     */
    showAllSchemas(options) {
      options = _.assign({}, options, {
        raw: true,
        type: this.sequelize.QueryTypes.SELECT
      });
  
      const showSchemasSql = this.QueryGenerator.showSchemasQuery();
  
      return this.sequelize.query(showSchemasSql, options).then(schemaNames => _.flatten(
        _.map(schemaNames, value => value.schema_name ? value.schema_name : value)
      ));
    }
  
    /**
     * Returns database version
     *
     * @param {Object}    [options]      Query options
     * @param {QueryType} [options.type] Query type
     *
     * @returns {Promise}
     * @private
     */
    databaseVersion(options) {
      return this.sequelize.query(
        this.QueryGenerator.versionQuery(),
        _.assign({}, options, { type: QueryTypes.VERSION })
      );
    }
  
    /**
     * Create a table with given set of attributes
     *
     * ```js
     * queryInterface.createTable(
     *   'nameOfTheNewTable',
     *   {
     *     id: {
     *       type: Sequelize.INTEGER,
     *       primaryKey: true,
     *       autoIncrement: true
     *     },
     *     createdAt: {
     *       type: Sequelize.DATE
     *     },
     *     updatedAt: {
     *       type: Sequelize.DATE
     *     },
     *     attr1: Sequelize.STRING,
     *     attr2: Sequelize.INTEGER,
     *     attr3: {
     *       type: Sequelize.BOOLEAN,
     *       defaultValue: false,
     *       allowNull: false
     *     },
     *     //foreign key usage
     *     attr4: {
     *       type: Sequelize.INTEGER,
     *       references: {
     *         model: 'another_table_name',
     *         key: 'id'
     *       },
     *       onUpdate: 'cascade',
     *       onDelete: 'cascade'
     *     }
     *   },
     *   {
     *     engine: 'MYISAM',    // default: 'InnoDB'
     *     charset: 'latin1',   // default: null
     *     schema: 'public',    // default: public, PostgreSQL only.
     *     comment: 'my table', // comment for table
     *     collate: 'latin1_danish_ci' // collation, MYSQL only
     *   }
     * )
     * ```
     *
     * @param {string} tableName  Name of table to create
     * @param {Object} attributes Object representing a list of table attributes to create
     * @param {Object} [options] create table and query options
     * @param {Model}  [model] model class
     *
     * @returns {Promise}
     */
    createTable(tableName, attributes, options, model) {
      let sql = '';
      let promise;
  
      options = _.clone(options) || {};
  
      if (model) {
        options.uniqueKeys = options.uniqueKeys || model.uniqueKeys;
      }
  
      attributes = _.mapValues(
        attributes,
        attribute => this.sequelize.normalizeAttribute(attribute)
      );
  
      // Postgres requires special SQL commands for ENUM/ENUM[]
      if (this.sequelize.options.dialect === 'postgres') {
        promise = PostgresQueryInterface.ensureEnums.call(this, tableName, attributes, options, model);
      } else {
        promise = Promise.resolve();
      }
  
      if (
        !tableName.schema &&
        (options.schema || !!model && model._schema)
      ) {
        tableName = this.QueryGenerator.addSchema({
          tableName,
          _schema: !!model && model._schema || options.schema
        });
      }
  
      attributes = this.QueryGenerator.attributesToSQL(attributes, { context: 'createTable' });
      sql = this.QueryGenerator.createTableQuery(tableName, attributes, options);
  
      return promise.then(() => this.sequelize.query(sql, options));
    }
  
    /**
     * Drops a table from database
     *
     * @param {string} tableName Table name to drop
     * @param {Object} options   Query options
     *
     * @returns {Promise}
     */
    dropTable(tableName, options) {
      // if we're forcing we should be cascading unless explicitly stated otherwise
      options = _.clone(options) || {};
      options.cascade = options.cascade || options.force || false;
  
      let sql = this.QueryGenerator.dropTableQuery(tableName, options);
  
      return this.sequelize.query(sql, options).then(() => {
        const promises = [];
  
        // Since postgres has a special case for enums, we should drop the related
        // enum type within the table and attribute
        if (this.sequelize.options.dialect === 'postgres') {
          const instanceTable = this.sequelize.modelManager.getModel(tableName, { attribute: 'tableName' });
  
          if (instanceTable) {
            const getTableName = (!options || !options.schema || options.schema === 'public' ? '' : options.schema + '_') + tableName;
  
            const keys = Object.keys(instanceTable.rawAttributes);
            const keyLen = keys.length;
  
            for (let i = 0; i < keyLen; i++) {
              if (instanceTable.rawAttributes[keys[i]].type instanceof DataTypes.ENUM) {
                sql = this.QueryGenerator.pgEnumDrop(getTableName, keys[i]);
                options.supportsSearchPath = false;
                promises.push(this.sequelize.query(sql, _.assign({}, options, { raw: true })));
              }
            }
          }
        }
  
        return Promise.all(promises).get(0);
      });
    }
  
    /**
     * Drop all tables from database
     *
     * @param {Object} [options] query options
     * @param {Array}  [options.skip] List of table to skip
     *
     * @returns {Promise}
     */
    dropAllTables(options) {
      options = options || {};
      const skip = options.skip || [];
  
      const dropAllTables = tableNames => Promise.each(tableNames, tableName => {
        // if tableName is not in the Array of tables names then don't drop it
        if (skip.indexOf(tableName.tableName || tableName) === -1) {
          return this.dropTable(tableName, _.assign({}, options, { cascade: true }) );
        }
      });
  
      return this.showAllTables(options).then(tableNames => {
        if (this.sequelize.options.dialect === 'sqlite') {
          return this.sequelize.query('PRAGMA foreign_keys;', options).then(result => {
            const foreignKeysAreEnabled = result.foreign_keys === 1;
  
            if (foreignKeysAreEnabled) {
              return this.sequelize.query('PRAGMA foreign_keys = OFF', options)
                .then(() => dropAllTables(tableNames))
                .then(() => this.sequelize.query('PRAGMA foreign_keys = ON', options));
            } else {
              return dropAllTables(tableNames);
            }
          });
        } else {
          return this.getForeignKeysForTables(tableNames, options).then(foreignKeys => {
            const promises = [];
  
            tableNames.forEach(tableName => {
              let normalizedTableName = tableName;
              if (_.isObject(tableName)) {
                normalizedTableName = tableName.schema + '.' + tableName.tableName;
              }
  
              foreignKeys[normalizedTableName].forEach(foreignKey => {
                const sql = this.QueryGenerator.dropForeignKeyQuery(tableName, foreignKey);
                promises.push(this.sequelize.query(sql, options));
              });
            });
  
            return Promise.all(promises).then(() => dropAllTables(tableNames));
          });
        }
      });
    }
  
    /**
     * Drop specified enum from database, Postgres Only
     *
     * @param {string} [enumName]  Enum name to drop
     * @param {Object} options Query options
     *
     * @returns {Promise}
     * @private
     */
    dropEnum(enumName, options) {
      if (this.sequelize.getDialect() !== 'postgres') {
        return Promise.resolve();
      }
  
      options = options || {};
  
      return this.sequelize.query(
        this.QueryGenerator.pgEnumDrop(null, null, this.QueryGenerator.pgEscapeAndQuote(enumName)),
        _.assign({}, options, { raw: true })
      );
    }
  
    /**
     * Drop all enums from database, Postgres Only
     *
     * @param {Object} options Query options
     *
     * @returns {Promise}
     * @private
     */
    dropAllEnums(options) {
      if (this.sequelize.getDialect() !== 'postgres') {
        return Promise.resolve();
      }
  
      options = options || {};
  
      return this.pgListEnums(null, options).map(result => this.sequelize.query(
        this.QueryGenerator.pgEnumDrop(null, null, this.QueryGenerator.pgEscapeAndQuote(result.enum_name)),
        _.assign({}, options, { raw: true })
      ));
    }
  
    /**
     * List all enums, Postgres Only
     *
     * @param {string} [tableName]  Table whose enum to list
     * @param {Object} [options]    Query options
     *
     * @returns {Promise}
     * @private
     */
    pgListEnums(tableName, options) {
      options = options || {};
      const sql = this.QueryGenerator.pgListEnums(tableName);
      return this.sequelize.query(sql, _.assign({}, options, { plain: false, raw: true, type: QueryTypes.SELECT }));
    }
  
    /**
     * Renames a table
     *
     * @param {string} before    Current name of table
     * @param {string} after     New name from table
     * @param {Object} [options] Query options
     *
     * @returns {Promise}
     */
    renameTable(before, after, options) {
      options = options || {};
      const sql = this.QueryGenerator.renameTableQuery(before, after);
      return this.sequelize.query(sql, options);
    }
  
    /**
     * Get all tables in current database
     *
     * @param {Object}    [options] Query options
     * @param {boolean}   [options.raw=true] Run query in raw mode
     * @param {QueryType} [options.type=QueryType.SHOWTABLE] query type
     *
     * @returns {Promise<Array>}
     * @private
     */
    showAllTables(options) {
      options = _.assign({}, options, {
        raw: true,
        type: QueryTypes.SHOWTABLES
      });
  
      const showTablesSql = this.QueryGenerator.showTablesQuery();
      return this.sequelize.query(showTablesSql, options).then(tableNames => _.flatten(tableNames));
    }
  
    /**
     * Describe a table structure
     *
     * This method returns an array of hashes containing information about all attributes in the table.
     *
     * ```js
     * {
     *    name: {
     *      type:         'VARCHAR(255)', // this will be 'CHARACTER VARYING' for pg!
     *      allowNull:    true,
     *      defaultValue: null
     *    },
     *    isBetaMember: {
     *      type:         'TINYINT(1)', // this will be 'BOOLEAN' for pg!
     *      allowNull:    false,
     *      defaultValue: false
     *    }
     * }
     * ```
     *
     * @param {string} tableName table name
     * @param {Object} [options] Query options
     *
     * @returns {Promise<Object>}
     */
    describeTable(tableName, options) {
      let schema = null;
      let schemaDelimiter = null;
  
      if (typeof options === 'string') {
        schema = options;
      } else if (typeof options === 'object' && options !== null) {
        schema = options.schema || null;
        schemaDelimiter = options.schemaDelimiter || null;
      }
  
      if (typeof tableName === 'object' && tableName !== null) {
        schema = tableName.schema;
        tableName = tableName.tableName;
      }
  
      const sql = this.QueryGenerator.describeTableQuery(tableName, schema, schemaDelimiter);
  
      return this.sequelize.query(
        sql,
        _.assign({}, options, { type: QueryTypes.DESCRIBE })
      ).then(data => {
        // If no data is returned from the query, then the table name may be wrong.
        // Query generators that use information_schema for retrieving table info will just return an empty result set,
        // it will not throw an error like built-ins do (e.g. DESCRIBE on MySql).
        if (_.isEmpty(data)) {
          return Promise.reject('No description found for "' + tableName + '" table. Check the table name and schema; remember, they _are_ case sensitive.');
        } else {
          return Promise.resolve(data);
        }
      });
    }
  
    /**
     * Add a new column into a table
     *
     * ```js
     * queryInterface.addColumn('tableA', 'columnC', Sequelize.STRING, {
     *    after: 'columnB' // after option is only supported by MySQL
     * });
     * ```
     *
     * @param {string} table     Table to add column to
     * @param {string} key       Column name
     * @param {Object} attribute Attribute definition
     * @param {Object} [options] Query options
     *
     * @returns {Promise}
     */
    addColumn(table, key, attribute, options) {
      if (!table || !key || !attribute) {
        throw new Error('addColumn takes atleast 3 arguments (table, attribute name, attribute definition)');
      }
  
      options = options || {};
      attribute = this.sequelize.normalizeAttribute(attribute);
      return this.sequelize.query(this.QueryGenerator.addColumnQuery(table, key, attribute), options);
    }
  
    /**
     * Remove a column from table
     *
     * @param {string} tableName      Table to remove column from
     * @param {string} attributeName  Columns name to remove
     * @param {Object} [options]      Query options
     *
     * @returns {Promise}
     */
    removeColumn(tableName, attributeName, options) {
      options = options || {};
      switch (this.sequelize.options.dialect) {
        case 'sqlite':
          // sqlite needs some special treatment as it cannot drop a column
          return SQLiteQueryInterface.removeColumn.call(this, tableName, attributeName, options);
        case 'mssql':
          // mssql needs special treatment as it cannot drop a column with a default or foreign key constraint
          return MSSSQLQueryInterface.removeColumn.call(this, tableName, attributeName, options);
        case 'mysql':
          // mysql needs special treatment as it cannot drop a column with a foreign key constraint
          return MySQLQueryInterface.removeColumn.call(this, tableName, attributeName, options);
        default:
          return this.sequelize.query(this.QueryGenerator.removeColumnQuery(tableName, attributeName), options);
      }
    }
  
    /**
     * Change a column definition
     *
     * @param {string} tableName          Table name to change from
     * @param {string} attributeName      Column name
     * @param {Object} dataTypeOrOptions  Attribute definition for new column
     * @param {Object} [options]          Query options
     *
     * @returns {Promise}
     */
    changeColumn(tableName, attributeName, dataTypeOrOptions, options) {
      const attributes = {};
      options = options || {};
  
      if (_.values(DataTypes).indexOf(dataTypeOrOptions) > -1) {
        attributes[attributeName] = { type: dataTypeOrOptions, allowNull: true };
      } else {
        attributes[attributeName] = dataTypeOrOptions;
      }
  
      attributes[attributeName] = this.sequelize.normalizeAttribute(attributes[attributeName]);
  
      if (this.sequelize.options.dialect === 'sqlite') {
        // sqlite needs some special treatment as it cannot change a column
        return SQLiteQueryInterface.changeColumn.call(this, tableName, attributes, options);
      } else {
        const query = this.QueryGenerator.attributesToSQL(attributes);
        const sql = this.QueryGenerator.changeColumnQuery(tableName, query);
  
        return this.sequelize.query(sql, options);
      }
    }
  
    /**
     * Rename a column
     *
     * @param {string} tableName        Table name whose column to rename
     * @param {string} attrNameBefore   Current column name
     * @param {string} attrNameAfter    New column name
     * @param {Object} [options]        Query option
     *
     * @returns {Promise}
     */
    renameColumn(tableName, attrNameBefore, attrNameAfter, options) {
      options = options || {};
      return this.describeTable(tableName, options).then(data => {
        if (!data[attrNameBefore]) {
          throw new Error('Table ' + tableName + ' doesn\'t have the column ' + attrNameBefore);
        }
  
        data = data[attrNameBefore] || {};
  
        const _options = {};
  
        _options[attrNameAfter] = {
          attribute: attrNameAfter,
          type: data.type,
          allowNull: data.allowNull,
          defaultValue: data.defaultValue
        };
  
        // fix: a not-null column cannot have null as default value
        if (data.defaultValue === null && !data.allowNull) {
          delete _options[attrNameAfter].defaultValue;
        }
  
        if (this.sequelize.options.dialect === 'sqlite') {
          // sqlite needs some special treatment as it cannot rename a column
          return SQLiteQueryInterface.renameColumn.call(this, tableName, attrNameBefore, attrNameAfter, options);
        } else {
          const sql = this.QueryGenerator.renameColumnQuery(
            tableName,
            attrNameBefore,
            this.QueryGenerator.attributesToSQL(_options)
          );
          return this.sequelize.query(sql, options);
        }
      });
    }
  
    /**
     * Add index to a column
     *
     * @param {string|Object}  tableName Table name to add index on, can be a object with schema
     * @param {Array}   [attributes]     Use options.fields instead, List of attributes to add index on
     * @param {Object}  options          indexes options
     * @param {Array}   options.fields   List of attributes to add index on
     * @param {boolean} [options.unique] Create a unique index
     * @param {string}  [options.using]  Useful for GIN indexes
     * @param {string}  [options.type]   Type of index, available options are UNIQUE|FULLTEXT|SPATIAL
     * @param {string}  [options.name]   Name of the index. Default is <table>_<attr1>_<attr2>
     * @param {Object}  [options.where]  Where condition on index, for partial indexes
     * @param {string}  [rawTablename]   table name, this is just for backward compatibiity
     *
     * @returns {Promise}
     */
    addIndex(tableName, attributes, options, rawTablename) {
      // Support for passing tableName, attributes, options or tableName, options (with a fields param which is the attributes)
      if (!Array.isArray(attributes)) {
        rawTablename = options;
        options = attributes;
        attributes = options.fields;
      }
      // testhint argsConform.end
  
      if (!rawTablename) {
        // Map for backwards compat
        rawTablename = tableName;
      }
  
      options = Utils.cloneDeep(options);
      options.fields = attributes;
      const sql = this.QueryGenerator.addIndexQuery(tableName, options, rawTablename);
      return this.sequelize.query(sql, _.assign({}, options, { supportsSearchPath: false }));
    }
  
    /**
     * Show indexes on a table
     *
     * @param {string} tableName table name
     * @param {Object} [options]   Query options
     *
     * @returns {Promise<Array>}
     * @private
     */
    showIndex(tableName, options) {
      const sql = this.QueryGenerator.showIndexesQuery(tableName, options);
      return this.sequelize.query(sql, _.assign({}, options, { type: QueryTypes.SHOWINDEXES }));
    }
  
    getForeignKeysForTables(tableNames, options) {
      if (tableNames.length === 0) {
        return Promise.resolve({});
      }
  
      options = _.assign({}, options || {}, { type: QueryTypes.FOREIGNKEYS });
  
      return Promise.map(tableNames, tableName =>
        this.sequelize.query(this.QueryGenerator.getForeignKeysQuery(tableName, this.sequelize.config.database), options)
      ).then(results => {
        const result = {};
  
        tableNames.forEach((tableName, i) => {
          if (_.isObject(tableName)) {
            tableName = tableName.schema + '.' + tableName.tableName;
          }
  
          result[tableName] = _.isArray(results[i])
            ? results[i].map(r => r.constraint_name)
            : [results[i] && results[i].constraint_name];
  
          result[tableName] = result[tableName].filter(_.identity);
        });
  
        return result;
      });
    }
  
    /**
     * Get foreign key references details for the table.
     *
     * Those details contains constraintSchema, constraintName, constraintCatalog
     * tableCatalog, tableSchema, tableName, columnName,
     * referencedTableCatalog, referencedTableCatalog, referencedTableSchema, referencedTableName, referencedColumnName.
     * Remind: constraint informations won't return if it's sqlite.
     *
     * @param {string} tableName table name
     * @param {Object} [options]  Query options
     *
     * @returns {Promise}
     */
    getForeignKeyReferencesForTable(tableName, options) {
      const queryOptions = Object.assign({}, options, {
        type: QueryTypes.FOREIGNKEYS
      });
      const catalogName = this.sequelize.config.database;
      switch (this.sequelize.options.dialect) {
        case 'sqlite':
          // sqlite needs some special treatment.
          return SQLiteQueryInterface.getForeignKeyReferencesForTable.call(this, tableName, queryOptions);
        case 'postgres':
        {
          // postgres needs some special treatment as those field names returned are all lowercase
          // in order to keep same result with other dialects.
          const query = this.QueryGenerator.getForeignKeyReferencesQuery(tableName, catalogName);
          return this.sequelize.query(query, queryOptions)
            .then(result => result.map(Utils.camelizeObjectKeys));
        }
        case 'mssql':
        case 'mysql':
        default:
        {
          const query = this.QueryGenerator.getForeignKeysQuery(tableName, catalogName);
          return this.sequelize.query(query, queryOptions);
        }
      }
    }
  
    /**
     * Remove an already existing index from a table
     *
     * @param {string} tableName             Table name to drop index from
     * @param {string} indexNameOrAttributes Index name
     * @param {Object} [options]             Query options
     *
     * @returns {Promise}
     */
    removeIndex(tableName, indexNameOrAttributes, options) {
      options = options || {};
      const sql = this.QueryGenerator.removeIndexQuery(tableName, indexNameOrAttributes);
      return this.sequelize.query(sql, options);
    }
  
    /**
     * Add constraints to table
     *
     * Available constraints:
     * - UNIQUE
     * - DEFAULT (MSSQL only)
     * - CHECK (MySQL - Ignored by the database engine )
     * - FOREIGN KEY
     * - PRIMARY KEY
     *
     * @example <caption>UNIQUE</caption>
     * queryInterface.addConstraint('Users', ['email'], {
     *   type: 'unique',
     *   name: 'custom_unique_constraint_name'
     * });
     *
     * @example <caption>CHECK</caption>
     * queryInterface.addConstraint('Users', ['roles'], {
     *   type: 'check',
     *   where: {
     *      roles: ['user', 'admin', 'moderator', 'guest']
     *   }
     * });
     *
     * @example <caption>Default - MSSQL only</caption>
     * queryInterface.addConstraint('Users', ['roles'], {
     *    type: 'default',
     *    defaultValue: 'guest'
     * });
     *
     * @example <caption>Primary Key</caption>
     * queryInterface.addConstraint('Users', ['username'], {
     *    type: 'primary key',
     *    name: 'custom_primary_constraint_name'
     * });
     *
     * @example <caption>Foreign Key</caption>
     * queryInterface.addConstraint('Posts', ['username'], {
     *   type: 'foreign key',
     *   name: 'custom_fkey_constraint_name',
     *   references: { //Required field
     *     table: 'target_table_name',
     *     field: 'target_column_name'
     *   },
     *   onDelete: 'cascade',
     *   onUpdate: 'cascade'
     * });
     *
     * @param {string} tableName                  Table name where you want to add a constraint
     * @param {Array}  attributes                 Array of column names to apply the constraint over
     * @param {Object} options                    An object to define the constraint name, type etc
     * @param {string} options.type               Type of constraint. One of the values in available constraints(case insensitive)
     * @param {string} [options.name]             Name of the constraint. If not specified, sequelize automatically creates a named constraint based on constraint type, table & column names
     * @param {string} [options.defaultValue]     The value for the default constraint
     * @param {Object} [options.where]            Where clause/expression for the CHECK constraint
     * @param {Object} [options.references]       Object specifying target table, column name to create foreign key constraint
     * @param {string} [options.references.table] Target table name
     * @param {string} [options.references.field] Target column name
     * @param {string} [rawTablename]             Table name, for backward compatibility
     *
     * @returns {Promise}
     */
    addConstraint(tableName, attributes, options, rawTablename) {
      if (!Array.isArray(attributes)) {
        rawTablename = options;
        options = attributes;
        attributes = options.fields;
      }
  
      if (!options.type) {
        throw new Error('Constraint type must be specified through options.type');
      }
  
      if (!rawTablename) {
        // Map for backwards compat
        rawTablename = tableName;
      }
  
      options = Utils.cloneDeep(options);
      options.fields = attributes;
  
      if (this.sequelize.dialect.name === 'sqlite') {
        return SQLiteQueryInterface.addConstraint.call(this, tableName, options, rawTablename);
      } else {
        const sql = this.QueryGenerator.addConstraintQuery(tableName, options, rawTablename);
        return this.sequelize.query(sql, options);
      }
    }
  
    showConstraint(tableName, constraintName, options) {
      const sql = this.QueryGenerator.showConstraintsQuery(tableName, constraintName);
      return this.sequelize.query(sql, Object.assign({}, options, { type: QueryTypes.SHOWCONSTRAINTS }));
    }
  
    /**
     *
     * @param {string} tableName       Table name to drop constraint from
     * @param {string} constraintName  Constraint name
     * @param {Object} options         Query options
     *
     * @returns {Promise}
     */
    removeConstraint(tableName, constraintName, options) {
      options = options || {};
  
      switch (this.sequelize.options.dialect) {
        case 'mysql':
          //Mysql does not support DROP CONSTRAINT. Instead DROP PRIMARY, FOREIGN KEY, INDEX should be used
          return MySQLQueryInterface.removeConstraint.call(this, tableName, constraintName, options);
        case 'sqlite':
          return SQLiteQueryInterface.removeConstraint.call(this, tableName, constraintName, options);
        default:
          const sql = this.QueryGenerator.removeConstraintQuery(tableName, constraintName);
          return this.sequelize.query(sql, options);
      }
    }
  
    insert(instance, tableName, values, options) {
      options = Utils.cloneDeep(options);
      options.hasTrigger = instance && instance.constructor.options.hasTrigger;
      const sql = this.QueryGenerator.insertQuery(tableName, values, instance && instance.constructor.rawAttributes, options);
  
      options.type = QueryTypes.INSERT;
      options.instance = instance;
  
      return this.sequelize.query(sql, options).then(results => {
        if (instance) results[0].isNewRecord = false;
        return results;
      });
    }
  
    /**
     * Upsert
     *
     * @param {string} tableName    table to upsert on
     * @param {Object} insertValues values to be inserted, mapped to field name
     * @param {Object} updateValues values to be updated, mapped to field name
     * @param {Object} where        various conditions
     * @param {Model}  model        Model to upsert on
     * @param {Object} options      query options
     *
     * @returns {Promise<boolean,?number>} Resolves an array with <created, primaryKey>
     */
    upsert(tableName, insertValues, updateValues, where, model, options) {
      const wheres = [];
      const attributes = Object.keys(insertValues);
      let indexes = [];
      let indexFields;
  
      options = _.clone(options);
  
      if (!Utils.isWhereEmpty(where)) {
        wheres.push(where);
      }
  
      // Lets combine unique keys and indexes into one
      indexes = _.map(model.uniqueKeys, value => {
        return value.fields;
      });
  
      _.each(model._indexes, value => {
        if (value.unique) {
          // fields in the index may both the strings or objects with an attribute property - lets sanitize that
          indexFields = _.map(value.fields, field => {
            if (_.isPlainObject(field)) {
              return field.attribute;
            }
            return field;
          });
          indexes.push(indexFields);
        }
      });
  
      for (const index of indexes) {
        if (_.intersection(attributes, index).length === index.length) {
          where = {};
          for (const field of index) {
            where[field] = insertValues[field];
          }
          wheres.push(where);
        }
      }
  
      where = { [Op.or]: wheres };
  
      options.type = QueryTypes.UPSERT;
      options.raw = true;
  
      const sql = this.QueryGenerator.upsertQuery(tableName, insertValues, updateValues, where, model, options);
      return this.sequelize.query(sql, options).then(result => {
        switch (this.sequelize.options.dialect) {
          case 'postgres':
            return [result.created, result.primary_key];
  
          case 'mssql':
            return [
              result.$action === 'INSERT',
              result[model.primaryKeyField]
            ];
  
          // MySQL returns 1 for inserted, 2 for updated
          // http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html.
          case 'mysql':
            return [result === 1, undefined];
  
          default:
            return [result, undefined];
        }
      });
    }
  
    /**
     * Insert records into a table
     *
     * @example
     * queryInterface.bulkInsert('roles', [{
     *    label: 'user',
     *    createdAt: new Date(),
     *    updatedAt: new Date()
     *  }, {
     *    label: 'admin',
     *    createdAt: new Date(),
     *    updatedAt: new Date()
     *  }]);
     *
     * @param {string} tableName   Table name to insert record to
     * @param {Array}  records     List of records to insert
     * @param {Object} options     Various options, please see Model.bulkCreate options
     * @param {Object} attributes  Various attributes mapped by field name
     *
     * @returns {Promise}
     */
    bulkInsert(tableName, records, options, attributes) {
      options = _.clone(options) || {};
      options.type = QueryTypes.INSERT;
  
      return this.sequelize.query(
        this.QueryGenerator.bulkInsertQuery(tableName, records, options, attributes),
        options
      ).then(results => results[0]);
    }
  
    update(instance, tableName, values, identifier, options) {
      options = _.clone(options || {});
      options.hasTrigger = !!(instance && instance._modelOptions && instance._modelOptions.hasTrigger);
  
      const sql = this.QueryGenerator.updateQuery(tableName, values, identifier, options, instance.constructor.rawAttributes);
  
      options.type = QueryTypes.UPDATE;
  
      options.instance = instance;
      return this.sequelize.query(sql, options);
    }
  
    bulkUpdate(tableName, values, identifier, options, attributes) {
      options = Utils.cloneDeep(options);
      if (typeof identifier === 'object') identifier = Utils.cloneDeep(identifier);
  
      const sql = this.QueryGenerator.updateQuery(tableName, values, identifier, options, attributes);
      const table = _.isObject(tableName) ? tableName : { tableName };
      const model = _.find(this.sequelize.modelManager.models, { tableName: table.tableName });
  
      options.model = model;
      return this.sequelize.query(sql, options);
    }
  
    delete(instance, tableName, identifier, options) {
      const cascades = [];
      const sql = this.QueryGenerator.deleteQuery(tableName, identifier, {}, instance.constructor);
  
      options = _.clone(options) || {};
  
      // Check for a restrict field
      if (!!instance.constructor && !!instance.constructor.associations) {
        const keys = Object.keys(instance.constructor.associations);
        const length = keys.length;
        let association;
  
        for (let i = 0; i < length; i++) {
          association = instance.constructor.associations[keys[i]];
          if (association.options && association.options.onDelete &&
            association.options.onDelete.toLowerCase() === 'cascade' &&
            association.options.useHooks === true) {
            cascades.push(association.accessors.get);
          }
        }
      }
  
      return Promise.each(cascades, cascade => {
        return instance[cascade](options).then(instances => {
          // Check for hasOne relationship with non-existing associate ("has zero")
          if (!instances) {
            return Promise.resolve();
          }
  
          if (!Array.isArray(instances)) instances = [instances];
  
          return Promise.each(instances, instance => instance.destroy(options));
        });
      }).then(() => {
        options.instance = instance;
        return this.sequelize.query(sql, options);
      });
    }
  
    /**
     * Delete records from a table
     *
     * @param {string}  tableName            table name from where to delete records
     * @param {Object}  where                where conditions to find records to delete
     * @param {Object}  [options]            options
     * @param {boolean} [options.truncate]  Use truncate table command
     * @param {Model}   [model]             Model
     *
     * @returns {Promise}
     */
    bulkDelete(tableName, where, options, model) {
      options = Utils.cloneDeep(options);
      options = _.defaults(options, { limit: null });
  
      if (options.truncate === true) {
        return this.sequelize.query(
          this.QueryGenerator.truncateTableQuery(tableName, options),
          options
        );
      }
  
      if (typeof identifier === 'object') where = Utils.cloneDeep(where);
  
      return this.sequelize.query(
        this.QueryGenerator.deleteQuery(tableName, where, options, model),
        options
      );
    }
  
    select(model, tableName, options) {
      options = Utils.cloneDeep(options);
      options.type = QueryTypes.SELECT;
      options.model = model;
  
      return this.sequelize.query(
        this.QueryGenerator.selectQuery(tableName, options, model),
        options
      );
    }
  
    increment(model, tableName, values, identifier, options) {
      options = Utils.cloneDeep(options);
  
      const sql = this.QueryGenerator.arithmeticQuery('+', tableName, values, identifier, options, options.attributes);
  
      options.type = QueryTypes.UPDATE;
      options.model = model;
  
      return this.sequelize.query(sql, options);
    }
  
    decrement(model, tableName, values, identifier, options) {
      options = Utils.cloneDeep(options);
  
      const sql = this.QueryGenerator.arithmeticQuery('-', tableName, values, identifier, options, options.attributes);
  
      options.type = QueryTypes.UPDATE;
      options.model = model;
  
      return this.sequelize.query(sql, options);
    }
  
    rawSelect(tableName, options, attributeSelector, Model) {
      if (options.schema) {
        tableName = this.QueryGenerator.addSchema({
          tableName,
          _schema: options.schema
        });
      }
  
      options = Utils.cloneDeep(options);
      options = _.defaults(options, {
        raw: true,
        plain: true,
        type: QueryTypes.SELECT
      });
  
      const sql = this.QueryGenerator.selectQuery(tableName, options, Model);
  
      if (attributeSelector === undefined) {
        throw new Error('Please pass an attribute selector!');
      }
  
      return this.sequelize.query(sql, options).then(data => {
        if (!options.plain) {
          return data;
        }
  
        let result = data ? data[attributeSelector] : null;
  
        if (options && options.dataType) {
          const dataType = options.dataType;
  
          if (dataType instanceof DataTypes.DECIMAL || dataType instanceof DataTypes.FLOAT) {
            result = parseFloat(result);
          } else if (dataType instanceof DataTypes.INTEGER || dataType instanceof DataTypes.BIGINT) {
            result = parseInt(result, 10);
          } else if (dataType instanceof DataTypes.DATE) {
            if (!_.isNull(result) && !_.isDate(result)) {
              result = new Date(result);
            }
          } else if (dataType instanceof DataTypes.STRING) {
            // Nothing to do, result is already a string.
          }
        }
  
        return result;
      });
    }
  
    createTrigger(tableName, triggerName, timingType, fireOnArray, functionName, functionParams, optionsArray, options) {
      const sql = this.QueryGenerator.createTrigger(tableName, triggerName, timingType, fireOnArray, functionName, functionParams, optionsArray);
      options = options || {};
      if (sql) {
        return this.sequelize.query(sql, options);
      } else {
        return Promise.resolve();
      }
    }
  
    dropTrigger(tableName, triggerName, options) {
      const sql = this.QueryGenerator.dropTrigger(tableName, triggerName);
      options = options || {};
  
      if (sql) {
        return this.sequelize.query(sql, options);
      } else {
        return Promise.resolve();
      }
    }
  
    renameTrigger(tableName, oldTriggerName, newTriggerName, options) {
      const sql = this.QueryGenerator.renameTrigger(tableName, oldTriggerName, newTriggerName);
      options = options || {};
  
      if (sql) {
        return this.sequelize.query(sql, options);
      } else {
        return Promise.resolve();
      }
    }
  
    /**
     * Create SQL function
     *
     * @example
     * queryInterface.createFunction(
     *   'someFunction',
     *   [
     *     {type: 'integer', name: 'param', direction: 'IN'}
     *   ],
     *   'integer',
     *   'plpgsql',
     *   'RETURN param + 1;',
     *   [
     *     'IMMUTABLE',
     *     'LEAKPROOF'
     *   ]
     * );
     *
     * @param {string} functionName Name of SQL function to create
     * @param {Array}  params       List of parameters declared for SQL function
     * @param {string} returnType   SQL type of function returned value
     * @param {string} language     The name of the language that the function is implemented in
     * @param {string} body         Source code of function
     * @param {Array}  optionsArray Extra-options for creation
     * @param {Object} [options]    query options
     *
     * @returns {Promise}
     */
    createFunction(functionName, params, returnType, language, body, optionsArray, options) {
      const sql = this.QueryGenerator.createFunction(functionName, params, returnType, language, body, optionsArray);
      options = options || {};
  
      if (sql) {
        return this.sequelize.query(sql, options);
      } else {
        return Promise.resolve();
      }
    }
  
    /**
     * Drop SQL function
     *
     * @example
     * queryInterface.dropFunction(
     *   'someFunction',
     *   [
     *     {type: 'varchar', name: 'param1', direction: 'IN'},
     *     {type: 'integer', name: 'param2', direction: 'INOUT'}
     *   ]
     * );
     *
     * @param {string} functionName Name of SQL function to drop
     * @param {Array}  params       List of parameters declared for SQL function
     * @param {Object} [options]    query options
     *
     * @returns {Promise}
     */
    dropFunction(functionName, params, options) {
      const sql = this.QueryGenerator.dropFunction(functionName, params);
      options = options || {};
  
      if (sql) {
        return this.sequelize.query(sql, options);
      } else {
        return Promise.resolve();
      }
    }
  
    /**
     * Rename SQL function
     *
     * @example
     * queryInterface.renameFunction(
     *   'fooFunction',
     *   [
     *     {type: 'varchar', name: 'param1', direction: 'IN'},
     *     {type: 'integer', name: 'param2', direction: 'INOUT'}
     *   ],
     *   'barFunction'
     * );
     *
     * @param {string} oldFunctionName  Current name of function
     * @param {Array}  params           List of parameters declared for SQL function
     * @param {string} newFunctionName  New name of function
     * @param {Object} [options]        query options
     *
     * @returns {Promise}
     */
    renameFunction(oldFunctionName, params, newFunctionName, options) {
      const sql = this.QueryGenerator.renameFunction(oldFunctionName, params, newFunctionName);
      options = options || {};
  
      if (sql) {
        return this.sequelize.query(sql, options);
      } else {
        return Promise.resolve();
      }
    }
  
    // Helper methods useful for querying
  
    /**
     * Escape an identifier (e.g. a table or attribute name)
     *
     * @param {string} identifier identifier to quote
     * @param {boolean} [force]   If force is true,the identifier will be quoted even if the `quoteIdentifiers` option is false.
     *
     * @private
     */
    quoteIdentifier(identifier, force) {
      return this.QueryGenerator.quoteIdentifier(identifier, force);
    }
  
    quoteTable(identifier) {
      return this.QueryGenerator.quoteTable(identifier);
    }
  
    /**
     * Quote array of identifiers at once
     *
     * @param {string[]} identifiers array of identifiers to quote
     * @param {boolean} [force]   If force is true,the identifier will be quoted even if the `quoteIdentifiers` option is false.
     *
     * @private
     */
    quoteIdentifiers(identifiers, force) {
      return this.QueryGenerator.quoteIdentifiers(identifiers, force);
    }
  
    /**
     * Escape a value (e.g. a string, number or date)
     *
     * @param {string} value string to escape
     *
     * @private
     */
    escape(value) {
      return this.QueryGenerator.escape(value);
    }
  
    setAutocommit(transaction, value, options) {
      if (!transaction || !(transaction instanceof Transaction)) {
        throw new Error('Unable to set autocommit for a transaction without transaction object!');
      }
      if (transaction.parent) {
        // Not possible to set a separate isolation level for savepoints
        return Promise.resolve();
      }
  
      options = _.assign({}, options, {
        transaction: transaction.parent || transaction
      });
  
      const sql = this.QueryGenerator.setAutocommitQuery(value, {
        parent: transaction.parent
      });
  
      if (!sql) return Promise.resolve();
  
      return this.sequelize.query(sql, options);
    }
  
    setIsolationLevel(transaction, value, options) {
      if (!transaction || !(transaction instanceof Transaction)) {
        throw new Error('Unable to set isolation level for a transaction without transaction object!');
      }
  
      if (transaction.parent || !value) {
        // Not possible to set a separate isolation level for savepoints
        return Promise.resolve();
      }
  
      options = _.assign({}, options, {
        transaction: transaction.parent || transaction
      });
  
      const sql = this.QueryGenerator.setIsolationLevelQuery(value, {
        parent: transaction.parent
      });
  
      if (!sql) return Promise.resolve();
  
      return this.sequelize.query(sql, options);
    }
  
    startTransaction(transaction, options) {
      if (!transaction || !(transaction instanceof Transaction)) {
        throw new Error('Unable to start a transaction without transaction object!');
      }
  
      options = _.assign({}, options, {
        transaction: transaction.parent || transaction
      });
      options.transaction.name = transaction.parent ? transaction.name : undefined;
      const sql = this.QueryGenerator.startTransactionQuery(transaction);
  
      return this.sequelize.query(sql, options);
    }
  
    deferConstraints(transaction, options) {
      options = _.assign({}, options, {
        transaction: transaction.parent || transaction
      });
  
      const sql = this.QueryGenerator.deferConstraintsQuery(options);
  
      if (sql) {
        return this.sequelize.query(sql, options);
      }
  
      return Promise.resolve();
    }
  
    commitTransaction(transaction, options) {
      if (!transaction || !(transaction instanceof Transaction)) {
        throw new Error('Unable to commit a transaction without transaction object!');
      }
      if (transaction.parent) {
        // Savepoints cannot be committed
        return Promise.resolve();
      }
  
      options = _.assign({}, options, {
        transaction: transaction.parent || transaction,
        supportsSearchPath: false
      });
  
      const sql = this.QueryGenerator.commitTransactionQuery(transaction);
      const promise = this.sequelize.query(sql, options);
  
      transaction.finished = 'commit';
  
      return promise;
    }
  
    rollbackTransaction(transaction, options) {
      if (!transaction || !(transaction instanceof Transaction)) {
        throw new Error('Unable to rollback a transaction without transaction object!');
      }
  
      options = _.assign({}, options, {
        transaction: transaction.parent || transaction,
        supportsSearchPath: false
      });
      options.transaction.name = transaction.parent ? transaction.name : undefined;
      const sql = this.QueryGenerator.rollbackTransactionQuery(transaction);
      const promise = this.sequelize.query(sql, options);
  
      transaction.finished = 'rollback';
  
      return promise;
    }
  }
  
  module.exports = QueryInterface;
  module.exports.QueryInterface = QueryInterface;
  module.exports.default = QueryInterface;
  