FM / mssql

/var/www/greso.tech/server/nsm/node_modules/sequelize/lib/dialects/mssql/

/var/www/greso.tech/server/nsm/node_modules/sequelize/lib/dialects/mssql

Name	Size	Mode	Actions
async-queue.js	2868	0644	edit dl rm
async-queue.js.map	2480	0644	edit dl rm
connection-manager.js	5221	0644	edit dl rm
connection-manager.js.map	8603	0644	edit dl rm
data-types.js	5318	0644	edit dl rm
data-types.js.map	9493	0644	edit dl rm
index.js	1717	0644	edit dl rm
index.js.map	3059	0644	edit dl rm
query-generator.js	32827	0644	edit dl rm
query-generator.js.map	52678	0644	edit dl rm
query-interface.js	3500	0644	edit dl rm
query-interface.js.map	4986	0644	edit dl rm
query.js	12383	0644	edit dl rm
query.js.map	21140	0644	edit dl rm

Edit: /var/www/greso.tech/server/nsm/node_modules/sequelize/lib/dialects/mssql/query-generator.js (32827B)

"use strict";
var __defProp = Object.defineProperty;
var __getOwnPropSymbols = Object.getOwnPropertySymbols;
var __hasOwnProp = Object.prototype.hasOwnProperty;
var __propIsEnum = Object.prototype.propertyIsEnumerable;
var __defNormalProp = (obj, key, value) => key in obj ? __defProp(obj, key, { enumerable: true, configurable: true, writable: true, value }) : obj[key] = value;
var __spreadValues = (a, b) => {
  for (var prop in b || (b = {}))
    if (__hasOwnProp.call(b, prop))
      __defNormalProp(a, prop, b[prop]);
  if (__getOwnPropSymbols)
    for (var prop of __getOwnPropSymbols(b)) {
      if (__propIsEnum.call(b, prop))
        __defNormalProp(a, prop, b[prop]);
    }
  return a;
};
const _ = require("lodash");
const Utils = require("../../utils");
const DataTypes = require("../../data-types");
const TableHints = require("../../table-hints");
const AbstractQueryGenerator = require("../abstract/query-generator");
const randomBytes = require("crypto").randomBytes;
const semver = require("semver");
const Op = require("../../operators");
const throwMethodUndefined = function(methodName) {
  throw new Error(`The method "${methodName}" is not defined! Please add it to your sql dialect.`);
};
class MSSQLQueryGenerator extends AbstractQueryGenerator {
  createDatabaseQuery(databaseName, options) {
    options = __spreadValues({ collate: null }, options);
    const collation = options.collate ? `COLLATE ${this.escape(options.collate)}` : "";
    return [
      "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name =",
      wrapSingleQuote(databaseName),
      ")",
      "BEGIN",
      "CREATE DATABASE",
      this.quoteIdentifier(databaseName),
      `${collation};`,
      "END;"
    ].join(" ");
  }
  dropDatabaseQuery(databaseName) {
    return [
      "IF EXISTS (SELECT * FROM sys.databases WHERE name =",
      wrapSingleQuote(databaseName),
      ")",
      "BEGIN",
      "DROP DATABASE",
      this.quoteIdentifier(databaseName),
      ";",
      "END;"
    ].join(" ");
  }
  createSchema(schema) {
    return [
      "IF NOT EXISTS (SELECT schema_name",
      "FROM information_schema.schemata",
      "WHERE schema_name =",
      wrapSingleQuote(schema),
      ")",
      "BEGIN",
      "EXEC sp_executesql N'CREATE SCHEMA",
      this.quoteIdentifier(schema),
      ";'",
      "END;"
    ].join(" ");
  }
  dropSchema(schema) {
    const quotedSchema = wrapSingleQuote(schema);
    return [
      "IF EXISTS (SELECT schema_name",
      "FROM information_schema.schemata",
      "WHERE schema_name =",
      quotedSchema,
      ")",
      "BEGIN",
      "DECLARE @id INT, @ms_sql NVARCHAR(2000);",
      "DECLARE @cascade TABLE (",
      "id INT NOT NULL IDENTITY PRIMARY KEY,",
      "ms_sql NVARCHAR(2000) NOT NULL );",
      "INSERT INTO @cascade ( ms_sql )",
      "SELECT CASE WHEN o.type IN ('F','PK')",
      "THEN N'ALTER TABLE ['+ s.name + N'].[' + p.name + N'] DROP CONSTRAINT [' + o.name + N']'",
      "ELSE N'DROP TABLE ['+ s.name + N'].[' + o.name + N']' END",
      "FROM sys.objects o",
      "JOIN sys.schemas s on o.schema_id = s.schema_id",
      "LEFT OUTER JOIN sys.objects p on o.parent_object_id = p.object_id",
      "WHERE o.type IN ('F', 'PK', 'U') AND s.name = ",
      quotedSchema,
      "ORDER BY o.type ASC;",
      "SELECT TOP 1 @id = id, @ms_sql = ms_sql FROM @cascade ORDER BY id;",
      "WHILE @id IS NOT NULL",
      "BEGIN",
      "BEGIN TRY EXEC sp_executesql @ms_sql; END TRY",
      "BEGIN CATCH BREAK; THROW; END CATCH;",
      "DELETE FROM @cascade WHERE id = @id;",
      "SELECT @id = NULL, @ms_sql = NULL;",
      "SELECT TOP 1 @id = id, @ms_sql = ms_sql FROM @cascade ORDER BY id;",
      "END",
      "EXEC sp_executesql N'DROP SCHEMA",
      this.quoteIdentifier(schema),
      ";'",
      "END;"
    ].join(" ");
  }
  showSchemasQuery() {
    return [
      'SELECT "name" as "schema_name" FROM sys.schemas as s',
      'WHERE "s"."name" NOT IN (',
      "'INFORMATION_SCHEMA', 'dbo', 'guest', 'sys', 'archive'",
      ")",
      "AND",
      '"s"."name" NOT LIKE',
      "'db_%'"
    ].join(" ");
  }
  versionQuery() {
    return [
      "DECLARE @ms_ver NVARCHAR(20);",
      "SET @ms_ver = REVERSE(CONVERT(NVARCHAR(20), SERVERPROPERTY('ProductVersion')));",
      "SELECT REVERSE(SUBSTRING(@ms_ver, CHARINDEX('.', @ms_ver)+1, 20)) AS 'version'"
    ].join(" ");
  }
  createTableQuery(tableName, attributes, options) {
    const primaryKeys = [], foreignKeys = {}, attributesClauseParts = [];
    let commentStr = "";
    for (const attr in attributes) {
      if (Object.prototype.hasOwnProperty.call(attributes, attr)) {
        let dataType = attributes[attr];
        let match;
        if (dataType.includes("COMMENT ")) {
          const commentMatch = dataType.match(/^(.+) (COMMENT.*)$/);
          const commentText = commentMatch[2].replace("COMMENT", "").trim();
          commentStr += this.commentTemplate(commentText, tableName, attr);
          dataType = commentMatch[1];
        }
        if (dataType.includes("PRIMARY KEY")) {
          primaryKeys.push(attr);
          if (dataType.includes("REFERENCES")) {
            match = dataType.match(/^(.+) (REFERENCES.*)$/);
            attributesClauseParts.push(`${this.quoteIdentifier(attr)} ${match[1].replace("PRIMARY KEY", "")}`);
            foreignKeys[attr] = match[2];
          } else {
            attributesClauseParts.push(`${this.quoteIdentifier(attr)} ${dataType.replace("PRIMARY KEY", "")}`);
          }
        } else if (dataType.includes("REFERENCES")) {
          match = dataType.match(/^(.+) (REFERENCES.*)$/);
          attributesClauseParts.push(`${this.quoteIdentifier(attr)} ${match[1]}`);
          foreignKeys[attr] = match[2];
        } else {
          attributesClauseParts.push(`${this.quoteIdentifier(attr)} ${dataType}`);
        }
      }
    }
    const pkString = primaryKeys.map((pk) => this.quoteIdentifier(pk)).join(", ");
    if (options.uniqueKeys) {
      _.each(options.uniqueKeys, (columns, indexName) => {
        if (columns.customIndex) {
          if (typeof indexName !== "string") {
            indexName = `uniq_${tableName}_${columns.fields.join("_")}`;
          }
          attributesClauseParts.push(`CONSTRAINT ${this.quoteIdentifier(indexName)} UNIQUE (${columns.fields.map((field) => this.quoteIdentifier(field)).join(", ")})`);
        }
      });
    }
    if (pkString.length > 0) {
      attributesClauseParts.push(`PRIMARY KEY (${pkString})`);
    }
    for (const fkey in foreignKeys) {
      if (Object.prototype.hasOwnProperty.call(foreignKeys, fkey)) {
        attributesClauseParts.push(`FOREIGN KEY (${this.quoteIdentifier(fkey)}) ${foreignKeys[fkey]}`);
      }
    }
    const quotedTableName = this.quoteTable(tableName);
    return Utils.joinSQLFragments([
      `IF OBJECT_ID('${quotedTableName}', 'U') IS NULL`,
      `CREATE TABLE ${quotedTableName} (${attributesClauseParts.join(", ")})`,
      ";",
      commentStr
    ]);
  }
  describeTableQuery(tableName, schema) {
    let sql = [
      "SELECT",
      "c.COLUMN_NAME AS 'Name',",
      "c.DATA_TYPE AS 'Type',",
      "c.CHARACTER_MAXIMUM_LENGTH AS 'Length',",
      "c.IS_NULLABLE as 'IsNull',",
      "COLUMN_DEFAULT AS 'Default',",
      "pk.CONSTRAINT_TYPE AS 'Constraint',",
      "COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') as 'IsIdentity',",
      "CAST(prop.value AS NVARCHAR) AS 'Comment'",
      "FROM",
      "INFORMATION_SCHEMA.TABLES t",
      "INNER JOIN",
      "INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA",
      "LEFT JOIN (SELECT tc.table_schema, tc.table_name, ",
      "cu.column_name, tc.CONSTRAINT_TYPE ",
      "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ",
      "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  cu ",
      "ON tc.table_schema=cu.table_schema and tc.table_name=cu.table_name ",
      "and tc.constraint_name=cu.constraint_name ",
      "and tc.CONSTRAINT_TYPE='PRIMARY KEY') pk ",
      "ON pk.table_schema=c.table_schema ",
      "AND pk.table_name=c.table_name ",
      "AND pk.column_name=c.column_name ",
      "INNER JOIN sys.columns AS sc",
      "ON sc.object_id = object_id(t.table_schema + '.' + t.table_name) AND sc.name = c.column_name",
      "LEFT JOIN sys.extended_properties prop ON prop.major_id = sc.object_id",
      "AND prop.minor_id = sc.column_id",
      "AND prop.name = 'MS_Description'",
      "WHERE t.TABLE_NAME =",
      wrapSingleQuote(tableName)
    ].join(" ");
    if (schema) {
      sql += `AND t.TABLE_SCHEMA =${wrapSingleQuote(schema)}`;
    }
    return sql;
  }
  renameTableQuery(before, after) {
    return `EXEC sp_rename ${this.quoteTable(before)}, ${this.quoteTable(after)};`;
  }
  showTablesQuery() {
    return "SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';";
  }
  tableExistsQuery(table) {
    const tableName = table.tableName || table;
    const schemaName = table.schema || "dbo";
    return `SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = ${this.escape(tableName)} AND TABLE_SCHEMA = ${this.escape(schemaName)}`;
  }
  dropTableQuery(tableName) {
    const quoteTbl = this.quoteTable(tableName);
    return Utils.joinSQLFragments([
      `IF OBJECT_ID('${quoteTbl}', 'U') IS NOT NULL`,
      "DROP TABLE",
      quoteTbl,
      ";"
    ]);
  }
  addColumnQuery(table, key, dataType) {
    dataType.field = key;
    let commentStr = "";
    if (dataType.comment && _.isString(dataType.comment)) {
      commentStr = this.commentTemplate(dataType.comment, table, key);
      delete dataType["comment"];
    }
    return Utils.joinSQLFragments([
      "ALTER TABLE",
      this.quoteTable(table),
      "ADD",
      this.quoteIdentifier(key),
      this.attributeToSQL(dataType, { context: "addColumn" }),
      ";",
      commentStr
    ]);
  }
  commentTemplate(comment, table, column) {
    return ` EXEC sp_addextendedproperty @name = N'MS_Description', @value = ${this.escape(comment)}, @level0type = N'Schema', @level0name = 'dbo', @level1type = N'Table', @level1name = ${this.quoteIdentifier(table)}, @level2type = N'Column', @level2name = ${this.quoteIdentifier(column)};`;
  }
  removeColumnQuery(tableName, attributeName) {
    return Utils.joinSQLFragments([
      "ALTER TABLE",
      this.quoteTable(tableName),
      "DROP COLUMN",
      this.quoteIdentifier(attributeName),
      ";"
    ]);
  }
  changeColumnQuery(tableName, attributes) {
    const attrString = [], constraintString = [];
    let commentString = "";
    for (const attributeName in attributes) {
      const quotedAttrName = this.quoteIdentifier(attributeName);
      let definition = attributes[attributeName];
      if (definition.includes("COMMENT ")) {
        const commentMatch = definition.match(/^(.+) (COMMENT.*)$/);
        const commentText = commentMatch[2].replace("COMMENT", "").trim();
        commentString += this.commentTemplate(commentText, tableName, attributeName);
        definition = commentMatch[1];
      }
      if (definition.includes("REFERENCES")) {
        constraintString.push(`FOREIGN KEY (${quotedAttrName}) ${definition.replace(/.+?(?=REFERENCES)/, "")}`);
      } else {
        attrString.push(`${quotedAttrName} ${definition}`);
      }
    }
    return Utils.joinSQLFragments([
      "ALTER TABLE",
      this.quoteTable(tableName),
      attrString.length && `ALTER COLUMN ${attrString.join(", ")}`,
      constraintString.length && `ADD ${constraintString.join(", ")}`,
      ";",
      commentString
    ]);
  }
  renameColumnQuery(tableName, attrBefore, attributes) {
    const newName = Object.keys(attributes)[0];
    return Utils.joinSQLFragments([
      "EXEC sp_rename",
      `'${this.quoteTable(tableName)}.${attrBefore}',`,
      `'${newName}',`,
      "'COLUMN'",
      ";"
    ]);
  }
  bulkInsertQuery(tableName, attrValueHashes, options, attributes) {
    const quotedTable = this.quoteTable(tableName);
    options = options || {};
    attributes = attributes || {};
    const tuples = [];
    const allAttributes = [];
    const allQueries = [];
    let needIdentityInsertWrapper = false, outputFragment = "";
    if (options.returning) {
      const returnValues = this.generateReturnValues(attributes, options);
      outputFragment = returnValues.outputFragment;
    }
    const emptyQuery = `INSERT INTO ${quotedTable}${outputFragment} DEFAULT VALUES`;
    attrValueHashes.forEach((attrValueHash) => {
      const fields = Object.keys(attrValueHash);
      const firstAttr = attributes[fields[0]];
      if (fields.length === 1 && firstAttr && firstAttr.autoIncrement && attrValueHash[fields[0]] === null) {
        allQueries.push(emptyQuery);
        return;
      }
      _.forOwn(attrValueHash, (value, key) => {
        if (value !== null && attributes[key] && attributes[key].autoIncrement) {
          needIdentityInsertWrapper = true;
        }
        if (!allAttributes.includes(key)) {
          if (value === null && attributes[key] && attributes[key].autoIncrement)
            return;
          allAttributes.push(key);
        }
      });
    });
    if (allAttributes.length > 0) {
      attrValueHashes.forEach((attrValueHash) => {
        tuples.push(`(${allAttributes.map((key) => this.escape(attrValueHash[key])).join(",")})`);
      });
      const quotedAttributes = allAttributes.map((attr) => this.quoteIdentifier(attr)).join(",");
      allQueries.push((tupleStr) => `INSERT INTO ${quotedTable} (${quotedAttributes})${outputFragment} VALUES ${tupleStr};`);
    }
    const commands = [];
    let offset = 0;
    const batch = Math.floor(250 / (allAttributes.length + 1)) + 1;
    while (offset < Math.max(tuples.length, 1)) {
      const tupleStr = tuples.slice(offset, Math.min(tuples.length, offset + batch));
      let generatedQuery = allQueries.map((v) => typeof v === "string" ? v : v(tupleStr)).join(";");
      if (needIdentityInsertWrapper) {
        generatedQuery = `SET IDENTITY_INSERT ${quotedTable} ON; ${generatedQuery}; SET IDENTITY_INSERT ${quotedTable} OFF;`;
      }
      commands.push(generatedQuery);
      offset += batch;
    }
    return commands.join(";");
  }
  updateQuery(tableName, attrValueHash, where, options, attributes) {
    const sql = super.updateQuery(tableName, attrValueHash, where, options, attributes);
    if (options.limit) {
      const updateArgs = `UPDATE TOP(${this.escape(options.limit)})`;
      sql.query = sql.query.replace("UPDATE", updateArgs);
    }
    return sql;
  }
  upsertQuery(tableName, insertValues, updateValues, where, model) {
    const targetTableAlias = this.quoteTable(`${tableName}_target`);
    const sourceTableAlias = this.quoteTable(`${tableName}_source`);
    const primaryKeysAttrs = [];
    const identityAttrs = [];
    const uniqueAttrs = [];
    const tableNameQuoted = this.quoteTable(tableName);
    let needIdentityInsertWrapper = false;
    for (const key in model.rawAttributes) {
      if (model.rawAttributes[key].primaryKey) {
        primaryKeysAttrs.push(model.rawAttributes[key].field || key);
      }
      if (model.rawAttributes[key].unique) {
        uniqueAttrs.push(model.rawAttributes[key].field || key);
      }
      if (model.rawAttributes[key].autoIncrement) {
        identityAttrs.push(model.rawAttributes[key].field || key);
      }
    }
    for (const index of model._indexes) {
      if (index.unique && index.fields) {
        for (const field of index.fields) {
          const fieldName = typeof field === "string" ? field : field.name || field.attribute;
          if (!uniqueAttrs.includes(fieldName) && model.rawAttributes[fieldName]) {
            uniqueAttrs.push(fieldName);
          }
        }
      }
    }
    const updateKeys = Object.keys(updateValues);
    const insertKeys = Object.keys(insertValues);
    const insertKeysQuoted = insertKeys.map((key) => this.quoteIdentifier(key)).join(", ");
    const insertValuesEscaped = insertKeys.map((key) => this.escape(insertValues[key])).join(", ");
    const sourceTableQuery = `VALUES(${insertValuesEscaped})`;
    let joinCondition;
    identityAttrs.forEach((key) => {
      if (insertValues[key] && insertValues[key] !== null) {
        needIdentityInsertWrapper = true;
      }
    });
    const clauses = where[Op.or].filter((clause) => {
      let valid = true;
      for (const key in clause) {
        if (typeof clause[key] === "undefined" || clause[key] == null) {
          valid = false;
          break;
        }
      }
      return valid;
    });
    const getJoinSnippet = (array) => {
      return array.map((key) => {
        key = this.quoteIdentifier(key);
        return `${targetTableAlias}.${key} = ${sourceTableAlias}.${key}`;
      });
    };
    if (clauses.length === 0) {
      throw new Error("Primary Key or Unique key should be passed to upsert query");
    } else {
      for (const key in clauses) {
        const keys = Object.keys(clauses[key]);
        if (primaryKeysAttrs.includes(keys[0])) {
          joinCondition = getJoinSnippet(primaryKeysAttrs).join(" AND ");
          break;
        }
      }
      if (!joinCondition) {
        joinCondition = getJoinSnippet(uniqueAttrs).join(" AND ");
      }
    }
    const filteredUpdateClauses = updateKeys.filter((key) => !identityAttrs.includes(key)).map((key) => {
      const value = this.escape(updateValues[key]);
      key = this.quoteIdentifier(key);
      return `${targetTableAlias}.${key} = ${value}`;
    });
    const updateSnippet = filteredUpdateClauses.length > 0 ? `WHEN MATCHED THEN UPDATE SET ${filteredUpdateClauses.join(", ")}` : "";
    const insertSnippet = `(${insertKeysQuoted}) VALUES(${insertValuesEscaped})`;
    let query = `MERGE INTO ${tableNameQuoted} WITH(HOLDLOCK) AS ${targetTableAlias} USING (${sourceTableQuery}) AS ${sourceTableAlias}(${insertKeysQuoted}) ON ${joinCondition}`;
    query += ` ${updateSnippet} WHEN NOT MATCHED THEN INSERT ${insertSnippet} OUTPUT $action, INSERTED.*;`;
    if (needIdentityInsertWrapper) {
      query = `SET IDENTITY_INSERT ${tableNameQuoted} ON; ${query} SET IDENTITY_INSERT ${tableNameQuoted} OFF;`;
    }
    return query;
  }
  truncateTableQuery(tableName) {
    return `TRUNCATE TABLE ${this.quoteTable(tableName)}`;
  }
  deleteQuery(tableName, where, options = {}, model) {
    const table = this.quoteTable(tableName);
    const whereClause = this.getWhereConditions(where, null, model, options);
    return Utils.joinSQLFragments([
      "DELETE",
      options.limit && `TOP(${this.escape(options.limit)})`,
      "FROM",
      table,
      whereClause && `WHERE ${whereClause}`,
      ";",
      "SELECT @@ROWCOUNT AS AFFECTEDROWS",
      ";"
    ]);
  }
  showIndexesQuery(tableName) {
    return `EXEC sys.sp_helpindex @objname = N'${this.quoteTable(tableName)}';`;
  }
  showConstraintsQuery(tableName) {
    return `EXEC sp_helpconstraint @objname = ${this.escape(this.quoteTable(tableName))};`;
  }
  removeIndexQuery(tableName, indexNameOrAttributes) {
    let indexName = indexNameOrAttributes;
    if (typeof indexName !== "string") {
      indexName = Utils.underscore(`${tableName}_${indexNameOrAttributes.join("_")}`);
    }
    return `DROP INDEX ${this.quoteIdentifiers(indexName)} ON ${this.quoteIdentifiers(tableName)}`;
  }
  attributeToSQL(attribute, options) {
    if (!_.isPlainObject(attribute)) {
      attribute = {
        type: attribute
      };
    }
    if (attribute.references) {
      if (attribute.Model && attribute.Model.tableName === attribute.references.model) {
        this.sequelize.log("MSSQL does not support self referencial constraints, we will remove it but we recommend restructuring your query");
        attribute.onDelete = "";
        attribute.onUpdate = "";
      }
    }
    let template;
    if (attribute.type instanceof DataTypes.ENUM) {
      if (attribute.type.values && !attribute.values)
        attribute.values = attribute.type.values;
      template = attribute.type.toSql();
      template += ` CHECK (${this.quoteIdentifier(attribute.field)} IN(${attribute.values.map((value) => {
        return this.escape(value);
      }).join(", ")}))`;
      return template;
    }
    template = attribute.type.toString();
    if (attribute.allowNull === false) {
      template += " NOT NULL";
    } else if (!attribute.primaryKey && !Utils.defaultValueSchemable(attribute.defaultValue)) {
      template += " NULL";
    }
    if (attribute.autoIncrement) {
      template += " IDENTITY(1,1)";
    }
    if (attribute.type !== "TEXT" && attribute.type._binary !== true && Utils.defaultValueSchemable(attribute.defaultValue)) {
      template += ` DEFAULT ${this.escape(attribute.defaultValue)}`;
    }
    if (attribute.unique === true) {
      template += " UNIQUE";
    }
    if (attribute.primaryKey) {
      template += " PRIMARY KEY";
    }
    if ((!options || !options.withoutForeignKeyConstraints) && attribute.references) {
      template += ` REFERENCES ${this.quoteTable(attribute.references.model)}`;
      if (attribute.references.key) {
        template += ` (${this.quoteIdentifier(attribute.references.key)})`;
      } else {
        template += ` (${this.quoteIdentifier("id")})`;
      }
      if (attribute.onDelete) {
        template += ` ON DELETE ${attribute.onDelete.toUpperCase()}`;
      }
      if (attribute.onUpdate) {
        template += ` ON UPDATE ${attribute.onUpdate.toUpperCase()}`;
      }
    }
    if (attribute.comment && typeof attribute.comment === "string") {
      template += ` COMMENT ${attribute.comment}`;
    }
    return template;
  }
  attributesToSQL(attributes, options) {
    const result = {}, existingConstraints = [];
    let key, attribute;
    for (key in attributes) {
      attribute = attributes[key];
      if (attribute.references) {
        if (existingConstraints.includes(attribute.references.model.toString())) {
          attribute.onDelete = "";
          attribute.onUpdate = "";
        } else {
          existingConstraints.push(attribute.references.model.toString());
          attribute.onUpdate = "";
        }
      }
      if (key && !attribute.field)
        attribute.field = key;
      result[attribute.field || key] = this.attributeToSQL(attribute, options);
    }
    return result;
  }
  createTrigger() {
    throwMethodUndefined("createTrigger");
  }
  dropTrigger() {
    throwMethodUndefined("dropTrigger");
  }
  renameTrigger() {
    throwMethodUndefined("renameTrigger");
  }
  createFunction() {
    throwMethodUndefined("createFunction");
  }
  dropFunction() {
    throwMethodUndefined("dropFunction");
  }
  renameFunction() {
    throwMethodUndefined("renameFunction");
  }
  _getForeignKeysQueryPrefix(catalogName) {
    return `${"SELECT constraint_name = OBJ.NAME, constraintName = OBJ.NAME, "}${catalogName ? `constraintCatalog = '${catalogName}', ` : ""}constraintSchema = SCHEMA_NAME(OBJ.SCHEMA_ID), tableName = TB.NAME, tableSchema = SCHEMA_NAME(TB.SCHEMA_ID), ${catalogName ? `tableCatalog = '${catalogName}', ` : ""}columnName = COL.NAME, referencedTableSchema = SCHEMA_NAME(RTB.SCHEMA_ID), ${catalogName ? `referencedCatalog = '${catalogName}', ` : ""}referencedTableName = RTB.NAME, referencedColumnName = RCOL.NAME FROM sys.foreign_key_columns FKC INNER JOIN sys.objects OBJ ON OBJ.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID INNER JOIN sys.tables TB ON TB.OBJECT_ID = FKC.PARENT_OBJECT_ID INNER JOIN sys.columns COL ON COL.COLUMN_ID = PARENT_COLUMN_ID AND COL.OBJECT_ID = TB.OBJECT_ID INNER JOIN sys.tables RTB ON RTB.OBJECT_ID = FKC.REFERENCED_OBJECT_ID INNER JOIN sys.columns RCOL ON RCOL.COLUMN_ID = REFERENCED_COLUMN_ID AND RCOL.OBJECT_ID = RTB.OBJECT_ID`;
  }
  getForeignKeysQuery(table, catalogName) {
    const tableName = table.tableName || table;
    let sql = `${this._getForeignKeysQueryPrefix(catalogName)} WHERE TB.NAME =${wrapSingleQuote(tableName)}`;
    if (table.schema) {
      sql += ` AND SCHEMA_NAME(TB.SCHEMA_ID) =${wrapSingleQuote(table.schema)}`;
    }
    return sql;
  }
  getForeignKeyQuery(table, attributeName) {
    const tableName = table.tableName || table;
    return Utils.joinSQLFragments([
      this._getForeignKeysQueryPrefix(),
      "WHERE",
      `TB.NAME =${wrapSingleQuote(tableName)}`,
      "AND",
      `COL.NAME =${wrapSingleQuote(attributeName)}`,
      table.schema && `AND SCHEMA_NAME(TB.SCHEMA_ID) =${wrapSingleQuote(table.schema)}`
    ]);
  }
  getPrimaryKeyConstraintQuery(table, attributeName) {
    const tableName = wrapSingleQuote(table.tableName || table);
    return Utils.joinSQLFragments([
      "SELECT K.TABLE_NAME AS tableName,",
      "K.COLUMN_NAME AS columnName,",
      "K.CONSTRAINT_NAME AS constraintName",
      "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C",
      "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K",
      "ON C.TABLE_NAME = K.TABLE_NAME",
      "AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG",
      "AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA",
      "AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME",
      "WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'",
      `AND K.COLUMN_NAME = ${wrapSingleQuote(attributeName)}`,
      `AND K.TABLE_NAME = ${tableName}`,
      ";"
    ]);
  }
  dropForeignKeyQuery(tableName, foreignKey) {
    return Utils.joinSQLFragments([
      "ALTER TABLE",
      this.quoteTable(tableName),
      "DROP",
      this.quoteIdentifier(foreignKey)
    ]);
  }
  getDefaultConstraintQuery(tableName, attributeName) {
    const quotedTable = this.quoteTable(tableName);
    return Utils.joinSQLFragments([
      "SELECT name FROM sys.default_constraints",
      `WHERE PARENT_OBJECT_ID = OBJECT_ID('${quotedTable}', 'U')`,
      `AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = ('${attributeName}')`,
      `AND object_id = OBJECT_ID('${quotedTable}', 'U'))`,
      ";"
    ]);
  }
  dropConstraintQuery(tableName, constraintName) {
    return Utils.joinSQLFragments([
      "ALTER TABLE",
      this.quoteTable(tableName),
      "DROP CONSTRAINT",
      this.quoteIdentifier(constraintName),
      ";"
    ]);
  }
  setIsolationLevelQuery() {
  }
  generateTransactionId() {
    return randomBytes(10).toString("hex");
  }
  startTransactionQuery(transaction) {
    if (transaction.parent) {
      return `SAVE TRANSACTION ${this.quoteIdentifier(transaction.name)};`;
    }
    return "BEGIN TRANSACTION;";
  }
  commitTransactionQuery(transaction) {
    if (transaction.parent) {
      return;
    }
    return "COMMIT TRANSACTION;";
  }
  rollbackTransactionQuery(transaction) {
    if (transaction.parent) {
      return `ROLLBACK TRANSACTION ${this.quoteIdentifier(transaction.name)};`;
    }
    return "ROLLBACK TRANSACTION;";
  }
  selectFromTableFragment(options, model, attributes, tables, mainTableAs, where) {
    this._throwOnEmptyAttributes(attributes, { modelName: model && model.name, as: mainTableAs });
    const dbVersion = this.sequelize.options.databaseVersion;
    const isSQLServer2008 = semver.valid(dbVersion) && semver.lt(dbVersion, "11.0.0");
    if (isSQLServer2008 && options.offset) {
      const offset = options.offset || 0;
      const isSubQuery = options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation;
      let orders = { mainQueryOrder: [] };
      if (options.order) {
        orders = this.getQueryOrders(options, model, isSubQuery);
      }
      if (orders.mainQueryOrder.length === 0) {
        orders.mainQueryOrder.push(this.quoteIdentifier(model.primaryKeyField));
      }
      const tmpTable = mainTableAs || "OffsetTable";
      if (options.include) {
        const subQuery = options.subQuery === void 0 ? options.limit && options.hasMultiAssociation : options.subQuery;
        const mainTable = {
          name: mainTableAs,
          quotedName: null,
          as: null,
          model
        };
        const topLevelInfo = {
          names: mainTable,
          options,
          subQuery
        };
        let mainJoinQueries = [];
        for (const include of options.include) {
          if (include.separate) {
            continue;
          }
          const joinQueries = this.generateInclude(include, { externalAs: mainTableAs, internalAs: mainTableAs }, topLevelInfo);
          mainJoinQueries = mainJoinQueries.concat(joinQueries.mainQuery);
        }
        return Utils.joinSQLFragments([
          "SELECT TOP 100 PERCENT",
          attributes.join(", "),
          "FROM (",
          [
            "SELECT",
            options.limit && `TOP ${options.limit}`,
            "* FROM (",
            [
              "SELECT ROW_NUMBER() OVER (",
              [
                "ORDER BY",
                orders.mainQueryOrder.join(", ")
              ],
              `) as row_num, ${tmpTable}.* FROM (`,
              [
                "SELECT DISTINCT",
                `${tmpTable}.* FROM ${tables} AS ${tmpTable}`,
                mainJoinQueries,
                where && `WHERE ${where}`
              ],
              `) AS ${tmpTable}`
            ],
            `) AS ${tmpTable} WHERE row_num > ${offset}`
          ],
          `) AS ${tmpTable}`
        ]);
      }
      return Utils.joinSQLFragments([
        "SELECT TOP 100 PERCENT",
        attributes.join(", "),
        "FROM (",
        [
          "SELECT",
          options.limit && `TOP ${options.limit}`,
          "* FROM (",
          [
            "SELECT ROW_NUMBER() OVER (",
            [
              "ORDER BY",
              orders.mainQueryOrder.join(", ")
            ],
            `) as row_num, * FROM ${tables} AS ${tmpTable}`,
            where && `WHERE ${where}`
          ],
          `) AS ${tmpTable} WHERE row_num > ${offset}`
        ],
        `) AS ${tmpTable}`
      ]);
    }
    return Utils.joinSQLFragments([
      "SELECT",
      isSQLServer2008 && options.limit && `TOP ${options.limit}`,
      attributes.join(", "),
      `FROM ${tables}`,
      mainTableAs && `AS ${mainTableAs}`,
      options.tableHint && TableHints[options.tableHint] && `WITH (${TableHints[options.tableHint]})`
    ]);
  }
  addLimitAndOffset(options, model) {
    if (semver.valid(this.sequelize.options.databaseVersion) && semver.lt(this.sequelize.options.databaseVersion, "11.0.0")) {
      return "";
    }
    const offset = options.offset || 0;
    const isSubQuery = options.subQuery === void 0 ? options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation : options.subQuery;
    let fragment = "";
    let orders = {};
    if (options.order) {
      orders = this.getQueryOrders(options, model, isSubQuery);
    }
    if (options.limit || options.offset) {
      if (!options.order || options.order.length === 0 || options.include && orders.subQueryOrder.length === 0) {
        let primaryKey = model.primaryKeyField;
        const tablePkFragment = `${this.quoteTable(options.tableAs || model.name)}.${this.quoteIdentifier(primaryKey)}`;
        const aliasedAttribute = (options.attributes || []).find((attr) => Array.isArray(attr) && attr[1] && (attr[0] === primaryKey || attr[1] === primaryKey));
        if (aliasedAttribute) {
          const modelName = this.quoteIdentifier(options.tableAs || model.name);
          const alias = this._getAliasForField(modelName, aliasedAttribute[1], options);
          primaryKey = new Utils.Col(alias || aliasedAttribute[1]);
        }
        if (!options.order || !options.order.length) {
          fragment += ` ORDER BY ${tablePkFragment}`;
        } else {
          const orderFieldNames = (options.order || []).map((order) => {
            const value = Array.isArray(order) ? order[0] : order;
            if (value instanceof Utils.Col) {
              return value.col;
            }
            if (value instanceof Utils.Literal) {
              return value.val;
            }
            return value;
          });
          const primaryKeyFieldAlreadyPresent = orderFieldNames.some((fieldName) => fieldName === (primaryKey.col || primaryKey));
          if (!primaryKeyFieldAlreadyPresent) {
            fragment += options.order && !isSubQuery ? ", " : " ORDER BY ";
            fragment += tablePkFragment;
          }
        }
      }
      if (options.offset || options.limit) {
        fragment += ` OFFSET ${this.escape(offset)} ROWS`;
      }
      if (options.limit) {
        fragment += ` FETCH NEXT ${this.escape(options.limit)} ROWS ONLY`;
      }
    }
    return fragment;
  }
  booleanValue(value) {
    return value ? 1 : 0;
  }
  quoteIdentifier(identifier, force) {
    return `[${identifier.replace(/[[\]']+/g, "")}]`;
  }
}
function wrapSingleQuote(identifier) {
  return Utils.addTicks(Utils.removeTicks(identifier, "'"), "'");
}
module.exports = MSSQLQueryGenerator;
//# sourceMappingURL=query-generator.js.map

cmd: