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

#activity_stats ⇒ Object 



193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 193

def activity_stats
  select_all %|
    SELECT
       [session_id]    = s.session_id,
       [user_process]  = CONVERT(CHAR(1), s.is_user_process),
       [login]         = s.login_name,
       [database]      = ISNULL(db_name(r.database_id), N''),
       [task_state]    = ISNULL(t.task_state, N''),
       [command]       = ISNULL(r.command, N''),
       [application]   = ISNULL(s.program_name, N''),
       [wait_time_ms]  = ISNULL(w.wait_duration_ms, 0),
       [wait_type]     = ISNULL(w.wait_type, N''),
       [wait_resource] = ISNULL(w.resource_description, N''),
       [blocked_by]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
       [head_blocker]  =
            CASE
                -- session has an active request, is blocked, but is blocking others
                WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1'
                -- session is idle but has an open tran and is blocking others
                WHEN r.session_id IS NULL THEN '1'
                ELSE ''
            END,
       [total_cpu_ms]   = s.cpu_time,
       [total_physical_io_mb]   = (s.reads + s.writes) * 8 / 1024,
       [memory_use_kb]  = s.memory_usage * 8192 / 1024,
       [open_transactions] = ISNULL(r.open_transaction_count,0),
       [login_time]     = s.login_time,
       [last_request_start_time] = s.last_request_start_time,
       [host_name]      = ISNULL(s.host_name, N''),
       [net_address]    = ISNULL(c.client_net_address, N''),
       [execution_context_id] = ISNULL(t.exec_context_id, 0),
       [request_id]     = ISNULL(r.request_id, 0),
       [workload_group] = N''
    FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
    LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
    LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
    LEFT OUTER JOIN
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
        FROM sys.dm_os_waiting_tasks
    ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
    LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
    WHERE db_name(r.database_id) = '#{current_database}'
    ORDER BY s.session_id|
end

#add_limit_offset!(_sql, _options) ⇒ Object

Raises:

  • (NotImplementedError) 


70
71
72
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 70

def add_limit_offset!(_sql, _options)
  raise NotImplementedError, 'This has been moved to the SQLServerCompiler in Arel.'
end

#begin_db_transaction ⇒ Object 



47
48
49
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 47

def begin_db_transaction
  do_execute 'BEGIN TRANSACTION'
end

#case_sensitive_modifier(node) ⇒ Object 



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

def case_sensitive_modifier(node)
  node.acts_like?(:string) ? Arel::Nodes::Bin.new(node) : node
end

#charset ⇒ Object 



288
289
290
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 288

def charset
  select_value "SELECT SERVERPROPERTY('SqlCharSetName')"
end

#commit_db_transaction ⇒ Object 



51
52
53
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 51

def commit_db_transaction
  disable_auto_reconnect { do_execute 'COMMIT TRANSACTION' }
end

#create_database(database, collation = ) ⇒ Object 



276
277
278
279
280
281
282
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 276

def create_database(database, collation = @connection_options[:collation])
  if collation
    do_execute "CREATE DATABASE #{quote_database_name(database)} COLLATE #{collation}"
  else
    do_execute "CREATE DATABASE #{quote_database_name(database)}"
  end
end

#create_savepoint(name = current_savepoint_name) ⇒ Object 



59
60
61
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 59

def create_savepoint(name = current_savepoint_name)
  disable_auto_reconnect { do_execute "SAVE TRANSACTION #{name}" }
end

#current_database ⇒ Object 



284
285
286
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 284

def current_database
  select_value 'SELECT DB_NAME()'
end

#drop_database(database) ⇒ Object 



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 257

def drop_database(database)
  retry_count = 0
  max_retries = 1
  begin
    do_execute "DROP DATABASE #{quote_database_name(database)}"
  rescue ActiveRecord::StatementInvalid => err
    if err.message =~ /because it is currently in use/i
      raise if retry_count >= max_retries
      retry_count += 1
      remove_database_connections_and_rollback(database)
      retry
    elsif err.message =~ /does not exist/i
      nil
    else
      raise
    end
  end
end

#empty_insert_statement_value ⇒ Object 



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

def empty_insert_statement_value
  'DEFAULT VALUES'
end

#exec_delete(sql, name, binds) ⇒ Object 



33
34
35
36
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 33

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

#exec_insert(sql, name, binds, _pk = nil, _sequence_name = nil) ⇒ Object

The abstract adapter ignores the last two parameters also



29
30
31
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 29

def exec_insert(sql, name, binds, _pk = nil, _sequence_name = nil)
  exec_query sql, name, binds, insert: true
end

#exec_query(sql, name = 'SQL', binds = [], sqlserver_options = {}) ⇒ Object 



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

def exec_query(sql, name = 'SQL', binds = [], sqlserver_options = {})
  if id_insert_table_name = sqlserver_options[:insert] ? query_requires_identity_insert?(sql) : nil
    with_identity_insert_enabled(id_insert_table_name) { do_exec_query(sql, name, binds) }
  elsif update_sql?(sql)
    sql = strip_ident_from_update(sql)
    do_exec_query(sql, name, binds)
  else
    do_exec_query(sql, name, binds)
  end
end

#exec_update(sql, name, binds) ⇒ Object 



38
39
40
41
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 38

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

#execute(sql, name = nil) ⇒ Object 



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

def execute(sql, name = nil)
  if id_insert_table_name = query_requires_identity_insert?(sql)
    with_identity_insert_enabled(id_insert_table_name) { do_execute(sql, name) }
  else
    do_execute(sql, name)
  end
end

#execute_procedure(proc_name, *variables) ⇒ Object

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



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 84

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
  name = 'Execute Procedure'
  log(sql, name) do
    case @connection_options[:mode]
    when :dblib
      result = @connection.execute(sql)
      result.each(as: :hash, cache_rows: true) do |row|
        r = row.with_indifferent_access
        yield(r) if block_given?
      end
      result.each.map { |row| row.is_a?(Hash) ? row.with_indifferent_access : row }
    when :odbc
      results = []
      raw_connection_run(sql) do |handle|
        get_rows = lambda do
          rows = handle_to_names_and_values handle, fetch: :all
          rows.each_with_index { |r, i| rows[i] = r.with_indifferent_access }
          results << rows
        end
        get_rows.call
        get_rows.call while handle_more_results?(handle)
      end
      results.many? ? results : results.first
    end
  end
end

#newid_function ⇒ Object 



185
186
187
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 185

def newid_function
  select_value 'SELECT NEWID()'
end

#newsequentialid_function ⇒ Object 



189
190
191
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 189

def newsequentialid_function
  select_value 'SELECT NEWSEQUENTIALID()'
end

#recreate_database ⇒ Object

SQLServer Specific (Rake/Test Helpers) ==================== #



240
241
242
243
244
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 240

def recreate_database
  remove_database_connections_and_rollback do
    do_execute "EXEC sp_MSforeachtable 'DROP TABLE ?'"
  end
end

#recreate_database!(database = nil) ⇒ Object 



246
247
248
249
250
251
252
253
254
255
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 246

def recreate_database!(database = nil)
  current_db = current_database
  database ||= current_db
  this_db = database.to_s == current_db
  do_execute 'USE master' if this_db
  drop_database(database)
  create_database(database)
ensure
  use_database(current_db) if this_db
end

#release_savepoint(name = current_savepoint_name) ⇒ Object 



63
64
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 63

def release_savepoint(name = current_savepoint_name)
end

#rollback_db_transaction ⇒ Object 



55
56
57
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 55

def rollback_db_transaction
  do_execute 'IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION'
end

#rollback_to_savepoint(name = current_savepoint_name) ⇒ Object 



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

def rollback_to_savepoint(name = current_savepoint_name)
  disable_auto_reconnect { do_execute "ROLLBACK TRANSACTION #{name}" }
end

#run_with_isolation_level(isolation_level) ⇒ Object 



172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 172

def run_with_isolation_level(isolation_level)
  unless valid_isolation_levels.include?(isolation_level.upcase)
    raise ArgumentError, "Invalid isolation level, #{isolation_level}. Supported levels include #{valid_isolation_levels.to_sentence}."
  end
  initial_isolation_level = user_options_isolation_level || 'READ COMMITTED'
  do_execute "SET TRANSACTION ISOLATION LEVEL #{isolation_level}"
  begin
    yield
  ensure
    do_execute "SET TRANSACTION ISOLATION LEVEL #{initial_isolation_level}"
  end if block_given?
end

#select_rows(sql, name = nil, binds = []) ⇒ Object 



5
6
7
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 5

def select_rows(sql, name = nil, binds = [])
  do_exec_query sql, name, binds, fetch: :rows
end

#supports_statement_cache? ⇒ Boolean

Returns:

  • (Boolean) 


43
44
45
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 43

def supports_statement_cache?
  true
end

#use_database(database = nil) ⇒ Object 



117
118
119
120
121
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 117

def use_database(database = nil)
  return if sqlserver_azure?
  database ||= @connection_options[:database]
  do_execute "USE #{quote_database_name(database)}" unless database.blank?
end

#user_options ⇒ Object 



123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 123

def user_options
  return {} if sqlserver_azure?
  select_rows('dbcc useroptions', 'SCHEMA').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

TODO: Rails 4 now supports isolation levels



139
140
141
142
143
144
145
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 139

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



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

def user_options_isolation_level
  if sqlserver_azure?
    sql = %(SELECT CASE [transaction_isolation_level]
            WHEN 0 THEN NULL
            WHEN 1 THEN 'READ UNCOMITTED'
            WHEN 2 THEN 'READ COMITTED'
            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 



164
165
166
167
168
169
170
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 164

def user_options_language
  if sqlserver_azure?
    select_value 'SELECT @@LANGUAGE AS [language]', 'SCHEMA'
  else
    user_options['language']
  end
end