Module: ActiveRecord::ConnectionAdapters::Sqlserver::DatabaseStatements

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

Instance Method Summary collapse

Methods included from CoreExt::DatabaseStatements

#transaction_with_retry_deadlock_victim

Instance Method Details

#activity_stats ⇒ Object 



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
237
238
239
240
241
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 198

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) 


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

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

#begin_db_transaction ⇒ Object 



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

def begin_db_transaction
  do_execute "BEGIN TRANSACTION"
end

#case_sensitive_modifier(node) ⇒ Object 



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

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

#charset ⇒ Object 



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

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

#commit_db_transaction ⇒ Object 



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

def commit_db_transaction
  disable_auto_reconnect { do_execute "COMMIT TRANSACTION" }
end

#create_database(database) ⇒ Object 



281
282
283
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 281

def create_database(database)
  do_execute "CREATE DATABASE #{quote_table_name(database)}"
end

#create_savepoint ⇒ Object 



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

def create_savepoint
  disable_auto_reconnect { do_execute "SAVE TRANSACTION #{current_savepoint_name}" }
end

#current_database ⇒ Object 



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

def current_database
  select_value 'SELECT DB_NAME()'
end

#drop_database(database) ⇒ Object 



262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 262

def drop_database(database)
  retry_count = 0
  max_retries = 1
  begin
    do_execute "DROP DATABASE #{quote_table_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 



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

def empty_insert_statement_value
  "DEFAULT VALUES"
end

#exec_delete(sql, name, binds) ⇒ Object 



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

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

#exec_insert(sql, name, binds) ⇒ Object 



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

def exec_insert(sql, name, binds)
  exec_query sql, name, binds, :insert => true
end

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



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

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) }
  else
    do_exec_query(sql, name, binds)
  end
end

#exec_update(sql, name, binds) ⇒ Object 



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

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

#execute(sql, name = nil) ⇒ Object 



12
13
14
15
16
17
18
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 12

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 ======================================== #



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 95

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 {
          rows = handle_to_names_and_values handle, :fetch => :all
          rows.each_with_index { |r,i| rows[i] = r.with_indifferent_access }
          results << rows
        }
        get_rows.call
        while handle_more_results?(handle)
          get_rows.call
        end
      end
      results.many? ? results : results.first
    end
  end
end

#newid_function ⇒ Object 



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

def newid_function
  select_value "SELECT NEWID()"
end

#newsequentialid_function ⇒ Object 



194
195
196
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 194

def newsequentialid_function
  select_value "SELECT NEWSEQUENTIALID()"
end

#outside_transaction? ⇒ Boolean

Returns:

  • (Boolean) 


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

def outside_transaction?
  select_value('SELECT @@TRANCOUNT', 'SCHEMA') == 0
end

#recreate_database ⇒ Object

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



245
246
247
248
249
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 245

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

#recreate_database!(database = nil) ⇒ Object 



251
252
253
254
255
256
257
258
259
260
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 251

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



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

def release_savepoint
end

#rollback_db_transaction ⇒ Object 



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

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

#rollback_to_savepoint ⇒ Object 



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

def rollback_to_savepoint
  disable_auto_reconnect { do_execute "ROLLBACK TRANSACTION #{current_savepoint_name}" }
end

#run_with_isolation_level(isolation_level) ⇒ Object

Raises:

  • (ArgumentError) 


179
180
181
182
183
184
185
186
187
188
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 179

def run_with_isolation_level(isolation_level)
  raise ArgumentError, "Invalid isolation level, #{isolation_level}. Supported levels include #{valid_isolation_levels.to_sentence}." if !valid_isolation_levels.include?(isolation_level.upcase)
  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) ⇒ Object 



8
9
10
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 8

def select_rows(sql, name = nil)
  raw_select sql, name, [], :fetch => :rows
end

#supports_statement_cache? ⇒ Boolean

Returns:

  • (Boolean) 


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

def supports_statement_cache?
  true
end

#transaction(options = {}) ⇒ Object 



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

def transaction(options = {})
  if retry_deadlock_victim?
    block_given? ? transaction_with_retry_deadlock_victim(options) { yield } : transaction_with_retry_deadlock_victim(options)
  else
    block_given? ? super(options) { yield } : super(options)
  end
end

#use_database(database = nil) ⇒ Object 



130
131
132
133
134
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 130

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

#user_options ⇒ Object 



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

def user_options
  return {} if sqlserver_azure?
  select_rows("dbcc useroptions",'SCHEMA').inject(HashWithIndifferentAccess.new) do |values,row| 
    set_option = row[0].gsub(/\s+/,'_')
    user_value = row[1]
    values[set_option] = user_value
    values
  end
end

#user_options_dateformat ⇒ Object 



146
147
148
149
150
151
152
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 146

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 



154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 154

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' 
            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 



171
172
173
174
175
176
177
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 171

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