Module: ActiveRecord::ConnectionAdapters::SQLServer::DatabaseStatements

Included in:
ActiveRecord::ConnectionAdapters::SQLServerAdapter
Defined in:
lib/active_record/connection_adapters/sqlserver/database_statements.rb

Instance Method Summary collapse

Instance Method Details

#begin_db_transaction ⇒ Object 



77
78
79
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 77

def begin_db_transaction
  internal_execute("BEGIN TRANSACTION", "TRANSACTION", allow_retry: true, materialize_transactions: false)
end

#begin_isolated_db_transaction(isolation) ⇒ Object 



85
86
87
88
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 85

def begin_isolated_db_transaction(isolation)
  set_transaction_isolation_level(transaction_isolation_levels.fetch(isolation))
  begin_db_transaction
end

#build_insert_sql(insert) ⇒ Object

:nodoc:



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 150

def build_insert_sql(insert) # :nodoc:
  sql = +"INSERT #{insert.into}"

  if returning = insert.send(:insert_all).returning
    returning_sql = if returning.is_a?(String)
                      returning
                    else
                      returning.map { |column| "INSERTED.#{quote_column_name(column)}" }.join(", ")
                    end
    sql << " OUTPUT #{returning_sql}"
  end

  sql << " #{insert.values_list}"
  sql
end

#case_sensitive_comparison(attribute, value) ⇒ Object 



102
103
104
105
106
107
108
109
110
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 102

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

#commit_db_transaction ⇒ Object 



94
95
96
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 94

def commit_db_transaction
  internal_execute("COMMIT TRANSACTION", "TRANSACTION", allow_retry: false, materialize_transactions: true)
end

#exec_delete(sql, name, binds) ⇒ Object 



67
68
69
70
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 67

def exec_delete(sql, name, binds)
  sql = sql.dup << "; SELECT @@ROWCOUNT AS AffectedRows"
  super(sql, name, binds).rows.first.first
end

#exec_rollback_db_transaction ⇒ Object 



98
99
100
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 98

def exec_rollback_db_transaction
  internal_execute("IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION", "TRANSACTION", allow_retry: false, materialize_transactions: true)
end

#exec_update(sql, name, binds) ⇒ Object 



72
73
74
75
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 72

def exec_update(sql, name, binds)
  sql = sql.dup << "; SELECT @@ROWCOUNT AS AffectedRows"
  super(sql, name, binds).rows.first.first
end

#execute_procedure(proc_name, *variables) ⇒ Object

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



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 168

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 = []) ⇒ Object

We should propose this change to Rails team



113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 113

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_query(sql, name = "SQL", binds = [], prepare: false, async: false, allow_retry: false) ⇒ Object 



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 31

def internal_exec_query(sql, name = "SQL", binds = [], prepare: false, async: false, allow_retry: false)
  sql = transform_query(sql)

  check_if_write_query(sql)
  mark_transaction_written_if_write(sql)

  type_casted_binds = type_casted_binds(binds)
  log(sql, name, binds, type_casted_binds, async: async) do |notification_payload|
    unless without_prepared_statement?(binds)
      types, params = sp_executesql_types_and_parameters(binds)
      sql = sp_executesql_sql(sql, types, params, name)
    end

    with_raw_connection do |conn|
      result = if id_insert_table_name = query_requires_identity_insert?(sql)
                 with_identity_insert_enabled(id_insert_table_name, conn) do
                   internal_exec_sql_query(sql, conn)
                 end
               else
                 internal_exec_sql_query(sql, conn)
               end

      verified!
      notification_payload[:row_count] = result.count
      result
    end
  end
end

#internal_exec_sql_query(sql, conn) ⇒ Object 



60
61
62
63
64
65
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 60

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 ⇒ Object 



263
264
265
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 263

def newid_function
  select_value "SELECT NEWID()"
end

#newsequentialid_function ⇒ Object 



267
268
269
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 267

def newsequentialid_function
  select_value "SELECT NEWSEQUENTIALID()"
end

#raw_execute(sql, name, async: false, allow_retry: false, materialize_transactions: true) ⇒ Object 



16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 16

def raw_execute(sql, name, async: false, allow_retry: false, materialize_transactions: true)
  log(sql, name, async: async) do |notification_payload|
    with_raw_connection(allow_retry: allow_retry, materialize_transactions: materialize_transactions) do |conn|
      result = if id_insert_table_name = query_requires_identity_insert?(sql)
                 with_identity_insert_enabled(id_insert_table_name, conn) { internal_raw_execute(sql, conn, perform_do: true) }
               else
                 internal_raw_execute(sql, conn, perform_do: true)
               end
      verified!
      notification_payload[:row_count] = result
      result
    end
  end
end

#set_transaction_isolation_level(isolation_level) ⇒ Object 



90
91
92
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 90

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 ⇒ Object 



81
82
83
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 81

def transaction_isolation_levels
  super.merge snapshot: "SNAPSHOT"
end

#use_database(database = nil) ⇒ Object 



205
206
207
208
209
210
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 205

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 ⇒ Object 



212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 212

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 ⇒ Object 



230
231
232
233
234
235
236
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 230

def user_options_dateformat
  if sqlserver_azure?
    select_value "SELECT [dateformat] FROM [sys].[syslanguages] WHERE [name] = @@LANGUAGE", "SCHEMA"
  else
    user_options["dateformat"]
  end
end

#user_options_isolation_level ⇒ Object 



238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 238

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 ⇒ Object 



255
256
257
258
259
260
261
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 255

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) ⇒ Object 



194
195
196
197
198
199
200
201
202
203
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 194

def with_identity_insert_enabled(table_name, conn)
  # If the table name is a view, we need to get the base table name for enabling identity insert.
  table_name = view_table_name(table_name) if view_exists?(table_name)
  quoted_table_name = quote_table_name(table_name)

  set_identity_insert(quoted_table_name, conn, true)
  yield
ensure
  set_identity_insert(quoted_table_name, conn, false)
end

#write_query?(sql) ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


10
11
12
13
14
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 10

def write_query?(sql) # :nodoc:
  !READ_QUERY.match?(sql)
rescue ArgumentError # Invalid encoding
  !READ_QUERY.match?(sql.b)
end