module ActiveRecord::ConnectionAdapters::SQLServer::DatabaseStatements

Public Instance Methods

affected_rows(raw_result) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 41
def affected_rows(raw_result)
  raw_result.first['AffectedRows']
end
begin_db_transaction() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 71
def begin_db_transaction
  internal_execute("BEGIN TRANSACTION", "TRANSACTION", allow_retry: true, materialize_transactions: false)
end
begin_isolated_db_transaction(isolation) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 79
def begin_isolated_db_transaction(isolation)
  set_transaction_isolation_level(transaction_isolation_levels.fetch(isolation))
  begin_db_transaction
end
case_sensitive_comparison(attribute, value) click to toggle source
Calls superclass method
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 96
def case_sensitive_comparison(attribute, value)
  column = column_for_attribute(attribute)

  if column.collation && !column.case_sensitive?
    attribute.eq(Arel::Nodes::Bin.new(value))
  else
    super
  end
end
cast_result(raw_result) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 33
def cast_result(raw_result)
  if raw_result.columns.empty?
    ActiveRecord::Result.empty
  else
    ActiveRecord::Result.new(raw_result.columns, raw_result.rows)
  end
end
commit_db_transaction() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 88
def commit_db_transaction
  internal_execute("COMMIT TRANSACTION", "TRANSACTION", allow_retry: false, materialize_transactions: true)
end
exec_delete(sql, name = nil, binds = []) click to toggle source
Calls superclass method
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 61
def exec_delete(sql, name = nil, binds = [])
  sql = sql.dup << "; SELECT @@ROWCOUNT AS AffectedRows"
  super(sql, name, binds)
end
exec_rollback_db_transaction() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 92
def exec_rollback_db_transaction
  internal_execute("IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION", "TRANSACTION", allow_retry: false, materialize_transactions: true)
end
exec_update(sql, name = nil, binds = []) click to toggle source
Calls superclass method
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 66
def exec_update(sql, name = nil, binds = [])
  sql = sql.dup << "; SELECT @@ROWCOUNT AS AffectedRows"
  super(sql, name, binds)
end
execute_procedure(proc_name, *variables) { |r| ... } click to toggle source

SQLServer Specific ======================================== #

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 162
def execute_procedure(proc_name, *variables)
  vars = if variables.any? && variables.first.is_a?(Hash)
           variables.first.map { |k, v| "@#{k} = #{quote(v)}" }
         else
           variables.map { |v| quote(v) }
         end.join(", ")
  sql = "EXEC #{proc_name} #{vars}".strip

  log(sql, "Execute Procedure") do |notification_payload|
    with_raw_connection do |conn|
      result = internal_raw_execute(sql, conn)
      verified!
      options = { as: :hash, cache_rows: true, timezone: ActiveRecord.default_timezone || :utc }

      result.each(options) do |row|
        r = row.with_indifferent_access
        yield(r) if block_given?
      end

      result = result.each.map { |row| row.is_a?(Hash) ? row.with_indifferent_access : row }
      notification_payload[:row_count] = result.count
      result
    end
  end
end
insert_fixtures_set(fixture_set, tables_to_delete = []) { || ... } click to toggle source

We should propose this change to Rails team

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 107
def insert_fixtures_set(fixture_set, tables_to_delete = [])
  fixture_inserts = []

  fixture_set.each do |table_name, fixtures|
    fixtures.each_slice(insert_rows_length) do |batch|
      fixture_inserts << build_fixture_sql(batch, table_name)
    end
  end

  table_deletes = tables_to_delete.map { |table| "DELETE FROM #{quote_table_name table}" }
  total_sqls = Array.wrap(table_deletes + fixture_inserts)

  disable_referential_integrity do
    transaction(requires_new: true) do
      total_sqls.each do |sql|
        execute sql, "Fixtures Load"
        yield if block_given?
      end
    end
  end
end
internal_exec_sql_query(sql, conn) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 54
def internal_exec_sql_query(sql, conn)
  handle = internal_raw_execute(sql, conn)
  handle_to_names_and_values(handle, ar_result: true)
ensure
  finish_statement_handle(handle)
end
newid_function() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 254
def newid_function
  select_value "SELECT NEWID()"
end
newsequentialid_function() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 258
def newsequentialid_function
  select_value "SELECT NEWSEQUENTIALID()"
end
perform_query(raw_connection, sql, binds, type_casted_binds, prepare:, notification_payload:, batch:) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 16
def perform_query(raw_connection, sql, binds, type_casted_binds, prepare:, notification_payload:, batch:)
  result = if id_insert_table_name = query_requires_identity_insert?(sql)
             # If the table name is a view, we need to get the base table name for enabling identity insert.
             id_insert_table_name = view_table_name(id_insert_table_name) if view_exists?(id_insert_table_name)

             with_identity_insert_enabled(id_insert_table_name, raw_connection) do
               internal_exec_sql_query(sql, raw_connection)
             end
           else
             internal_exec_sql_query(sql, raw_connection)
           end

  verified!
  notification_payload[:row_count] = result.count
  result
end
raw_execute(sql, name = nil, binds = [], prepare: false, async: false, allow_retry: false, materialize_transactions: true, batch: false) click to toggle source
Calls superclass method
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 45
def raw_execute(sql, name = nil, binds = [], prepare: false, async: false, allow_retry: false, materialize_transactions: true, batch: false)
  unless binds.nil? || binds.empty?
    types, params = sp_executesql_types_and_parameters(binds)
    sql = sp_executesql_sql(sql, types, params, name)
  end

  super
end
set_transaction_isolation_level(isolation_level) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 84
def set_transaction_isolation_level(isolation_level)
  internal_execute("SET TRANSACTION ISOLATION LEVEL #{isolation_level}", "TRANSACTION", allow_retry: true, materialize_transactions: false)
end
transaction_isolation_levels() click to toggle source
Calls superclass method
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 75
def transaction_isolation_levels
  super.merge snapshot: "SNAPSHOT"
end
use_database(database = nil) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 196
def use_database(database = nil)
  return if sqlserver_azure?

  name = SQLServer::Utils.extract_identifiers(database || @connection_parameters[:database]).quoted
  execute("USE #{name}", "SCHEMA") unless name.blank?
end
user_options() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 203
def user_options
  return {} if sqlserver_azure?

  rows = select_rows("DBCC USEROPTIONS WITH NO_INFOMSGS", "SCHEMA")
  rows = rows.first if rows.size == 2 && rows.last.empty?
  rows.reduce(HashWithIndifferentAccess.new) do |values, row|
    if row.instance_of? Hash
      set_option = row.values[0].gsub(/\s+/, "_")
      user_value = row.values[1]
    elsif row.instance_of? Array
      set_option = row[0].gsub(/\s+/, "_")
      user_value = row[1]
    end
    values[set_option] = user_value
    values
  end
end
user_options_dateformat() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 221
def user_options_dateformat
  if sqlserver_azure?
    select_value "SELECT [dateformat] FROM [sys].[syslanguages] WHERE [langid] = @@LANGID", "SCHEMA"
  else
    user_options["dateformat"]
  end
end
user_options_isolation_level() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 229
def user_options_isolation_level
  if sqlserver_azure?
    sql = %(SELECT CASE [transaction_isolation_level]
            WHEN 0 THEN NULL
            WHEN 1 THEN 'READ UNCOMMITTED'
            WHEN 2 THEN 'READ COMMITTED'
            WHEN 3 THEN 'REPEATABLE READ'
            WHEN 4 THEN 'SERIALIZABLE'
            WHEN 5 THEN 'SNAPSHOT' END AS [isolation_level]
            FROM [sys].[dm_exec_sessions]
            WHERE [session_id] = @@SPID).squish
    select_value sql, "SCHEMA"
  else
    user_options["isolation_level"]
  end
end
user_options_language() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 246
def user_options_language
  if sqlserver_azure?
    select_value "SELECT @@LANGUAGE AS [language]", "SCHEMA"
  else
    user_options["language"]
  end
end
with_identity_insert_enabled(table_name, conn) { || ... } click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 188
def with_identity_insert_enabled(table_name, conn)
  table_name = quote_table_name(table_name)
  set_identity_insert(table_name, conn, true)
  yield
ensure
  set_identity_insert(table_name, conn, false)
end

Protected Instance Methods

_raw_select(sql, conn) click to toggle source

SQLServer Specific (Selecting) ============================ #

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 421
def _raw_select(sql, conn)
  handle = internal_raw_execute(sql, conn)
  handle_to_names_and_values(handle, fetch: :rows)
ensure
  finish_statement_handle(handle)
end
active_model_attribute?(type) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 357
def active_model_attribute?(type)
  type.is_a?(::ActiveModel::Attribute)
end
exclude_output_inserted_id_sql_type(pk, exclude_output_inserted) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 396
def exclude_output_inserted_id_sql_type(pk, exclude_output_inserted)
  return "bigint" if exclude_output_inserted.is_a?(TrueClass)
  return exclude_output_inserted[pk.to_sym] if exclude_output_inserted.is_a?(Hash)
  exclude_output_inserted
end
exclude_output_inserted_table_name?(table_name, sql) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 387
def exclude_output_inserted_table_name?(table_name, sql)
  return false unless exclude_output_inserted_table_names?

  table_name ||= get_table_name(sql)
  return false unless table_name

  self.class.exclude_output_inserted_table_names[table_name]
end
exclude_output_inserted_table_names?() click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 383
def exclude_output_inserted_table_names?
  !self.class.exclude_output_inserted_table_names.empty?
end
finish_statement_handle(handle) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 443
def finish_statement_handle(handle)
  handle.cancel if handle
  handle
end
handle_to_names_and_values(handle, options = {}) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 428
def handle_to_names_and_values(handle, options = {})
  query_options = {}.tap do |qo|
    qo[:timezone] = ActiveRecord.default_timezone || :utc
    qo[:as] = (options[:ar_result] || options[:fetch] == :rows) ? :array : :hash
  end
  results = handle.each(query_options)

  columns = handle.fields
  # If query returns multiple result sets, only return the columns of the last one.
  columns = columns.last if columns.any? && columns.all? { |e| e.is_a?(Array) }
  columns = columns.map(&:downcase) if lowercase_schema_reflection

  options[:ar_result] ? ActiveRecord::Result.new(columns, results) : results
end
identity_columns(table_name) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 415
def identity_columns(table_name)
  schema_cache.columns(table_name).select(&:is_identity?)
end
insert_sql?(sql) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 411
def insert_sql?(sql)
  !(sql =~ /\A\s*(INSERT|EXEC sp_executesql N'INSERT)/i).nil?
end
internal_raw_execute(sql, raw_connection, perform_do: false) click to toggle source

TinyTDS returns false instead of raising an exception if connection fails. Getting around this by raising an exception ourselves while PR github.com/rails-sqlserver/tiny_tds/pull/469 is not released.

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 451
def internal_raw_execute(sql, raw_connection, perform_do: false)
  result = raw_connection.execute(sql)
  raise TinyTds::Error, "failed to execute statement" if result.is_a?(FalseClass)

  perform_do ? result.do : result
end
query_requires_identity_insert?(sql) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 402
def query_requires_identity_insert?(sql)
  return false unless insert_sql?(sql)

  raw_table_name = get_raw_table_name(sql)
  id_column = identity_columns(raw_table_name).first

  id_column && sql =~ /^\s*(INSERT|EXEC sp_executesql N'INSERT)[^(]+\([^)]*\b(#{id_column.name})\b,?[^)]*\)/i ? SQLServer::Utils.extract_identifiers(raw_table_name).quoted : false
end
set_identity_insert(table_name, conn, enable) click to toggle source

SQLServer Specific ======================================== #

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 306
def set_identity_insert(table_name, conn, enable)
  internal_raw_execute("SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}", conn , perform_do: true)
rescue Exception
  raise ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}"
end
sp_executesql_sql(sql, types, params, name) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 361
def sp_executesql_sql(sql, types, params, name)
  if name == "EXPLAIN"
    params.each.with_index do |param, index|
      substitute_at_finder = /(@#{index})(?=(?:[^']|'[^']*')*$)/ # Finds unquoted @n values.
      sql = sql.sub substitute_at_finder, param.to_s
    end
  else
    types = quote(types.join(", "))
    params = params.map.with_index { |p, i| "@#{i} = #{p}" }.join(", ") # Only p is needed, but with @i helps explain regexp.
    sql = "EXEC sp_executesql #{quote(sql)}"
    sql += ", #{types}, #{params}" unless params.empty?
  end

  sql.freeze
end
sp_executesql_sql_param(attr) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 346
def sp_executesql_sql_param(attr)
  return quote(attr) unless active_model_attribute?(attr)

  case value = attr.value_for_database
  when Type::Binary::Data, ActiveRecord::Type::SQLServer::Data
    quote(value)
  else
    quote(type_cast(value))
  end
end
sp_executesql_sql_type(attr) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 325
def sp_executesql_sql_type(attr)
  if attr.respond_to?(:type)
    type = attr.type.is_a?(ActiveRecord::Normalization::NormalizedValueType) ? attr.type.cast_type : attr.type
    type = type.subtype if type.serialized?

    return type.sqlserver_type if type.respond_to?(:sqlserver_type)

    if type.is_a?(ActiveRecord::Encryption::EncryptedAttributeType) && type.instance_variable_get(:@cast_type).respond_to?(:sqlserver_type)
      return type.instance_variable_get(:@cast_type).sqlserver_type
    end
  end

  value = active_model_attribute?(attr) ? attr.value_for_database : attr

  if value.is_a?(Numeric)
    value > 2_147_483_647 ? "bigint".freeze : "int".freeze
  else
    "nvarchar(max)".freeze
  end
end
sp_executesql_types_and_parameters(binds) click to toggle source

SQLServer Specific (Executing) ============================ #

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 314
def sp_executesql_types_and_parameters(binds)
  types, params = [], []
  binds.each_with_index do |attr, index|
    attr = attr.value if attr.is_a?(Arel::Nodes::BindParam)

    types << "@#{index} #{sp_executesql_sql_type(attr)}"
    params << sp_executesql_sql_param(attr)
  end
  [types, params]
end
sql_for_insert(sql, pk, binds, returning) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 264
        def sql_for_insert(sql, pk, binds, returning)
          if pk.nil?
            table_name = query_requires_identity_insert?(sql)
            pk = primary_key(table_name)
          end

          sql = if pk && use_output_inserted? && !database_prefix_remote_server?
                  table_name ||= get_table_name(sql)
                  exclude_output_inserted = exclude_output_inserted_table_name?(table_name, sql)

                  if exclude_output_inserted
                    pk_and_types = Array(pk).map do |subkey|
                      {
                        quoted: SQLServer::Utils.extract_identifiers(subkey).quoted,
                        id_sql_type: exclude_output_inserted_id_sql_type(subkey, exclude_output_inserted)
                      }
                    end

                    <<~SQL.squish
                      DECLARE @ssaIdInsertTable table (#{pk_and_types.map { |pk_and_type| "#{pk_and_type[:quoted]} #{pk_and_type[:id_sql_type]}"}.join(", ") });
                      #{sql.dup.insert sql.index(/ (DEFAULT )?VALUES/i), " OUTPUT #{ pk_and_types.map { |pk_and_type| "INSERTED.#{pk_and_type[:quoted]}" }.join(", ") } INTO @ssaIdInsertTable"}
                      SELECT #{pk_and_types.map {|pk_and_type| "CAST(#{pk_and_type[:quoted]} AS #{pk_and_type[:id_sql_type]}) #{pk_and_type[:quoted]}"}.join(", ")} FROM @ssaIdInsertTable
                    SQL
                  else
                    returning_columns = returning || Array(pk)

                    if returning_columns.any?
                      returning_columns_statements = returning_columns.map { |c| " INSERTED.#{SQLServer::Utils.extract_identifiers(c).quoted}" }
                      sql.dup.insert sql.index(/ (DEFAULT )?VALUES/i), " OUTPUT" + returning_columns_statements.join(",")
                    else
                      sql
                    end
                  end
                else
                  "#{sql}; SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident"
                end

          [sql, binds]
        end
use_output_inserted?() click to toggle source

SQLServer Specific (Identity Inserts) ===================== #

# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 379
def use_output_inserted?
  self.class.use_output_inserted
end

Private Instance Methods

can_perform_case_insensitive_comparison_for?(column) click to toggle source
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 129
def can_perform_case_insensitive_comparison_for?(column)
  column.type == :string && (!column.collation || column.case_sensitive?)
end
default_insert_value(column) click to toggle source
Calls superclass method
# File lib/active_record/connection_adapters/sqlserver/database_statements.rb, line 134
def default_insert_value(column)
  if column.is_identity?
    table_name = quote(quote_table_name(column.table_name))
    Arel.sql("IDENT_CURRENT(#{table_name}) + IDENT_INCR(#{table_name})")
  else
    super
  end
end