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 



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
242
243
244
245
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 202

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) 


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

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

#begin_db_transaction ⇒ Object 



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

def begin_db_transaction
  do_execute "BEGIN TRANSACTION"
end

#case_sensitive_modifier(node) ⇒ Object 



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

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

#charset ⇒ Object 



297
298
299
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 297

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

#commit_db_transaction ⇒ Object 



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

def commit_db_transaction
  disable_auto_reconnect { do_execute "COMMIT TRANSACTION" }
end

#create_database(database, collation = ) ⇒ Object 



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

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

#create_savepoint ⇒ Object 



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

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

#current_database ⇒ Object 



293
294
295
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 293

def current_database
  select_value 'SELECT DB_NAME()'
end

#drop_database(database) ⇒ Object 



266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 266

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 



83
84
85
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 83

def empty_insert_statement_value
  "DEFAULT VALUES"
end

#exec_delete(sql, name, binds) ⇒ Object 



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

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



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

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

TODO I bet there’s a better way than a regex to take care of this



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 19

def exec_query(sql, name = 'SQL', binds = [], sqlserver_options = {})
  # We can't update Identiy columns in sqlserver.  So, strip out the id from the update.
  if sql =~ /UPDATE/ 
			# take off a comma before or after.  This could probably be done better 
    if sql =~ /, \[id\] = @?[0-9]*/
      sql.gsub! /, \[id\] = @?[0-9]*/, '' 
    elsif sql =~ /\s\[id\] = @?[0-9]*,/
      sql.gsub! /\s\[id\] = @?[0-9]*,/, ''  
    end
  end

  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 



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

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

#execute(sql, name = nil) ⇒ Object 



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

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



93
94
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
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 93

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 



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

def newid_function
  select_value "SELECT NEWID()"
end

#newsequentialid_function ⇒ Object 



198
199
200
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 198

def newsequentialid_function
  select_value "SELECT NEWSEQUENTIALID()"
end

#recreate_database ⇒ Object

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



249
250
251
252
253
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 249

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

#recreate_database!(database = nil) ⇒ Object 



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

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 



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

def release_savepoint
end

#rollback_db_transaction ⇒ Object 



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

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

#rollback_to_savepoint ⇒ Object 



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

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

#run_with_isolation_level(isolation_level) ⇒ Object

Raises:

  • (ArgumentError) 


183
184
185
186
187
188
189
190
191
192
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 183

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



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

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

#supports_statement_cache? ⇒ Boolean

Returns:

  • (Boolean) 


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

def supports_statement_cache?
  true
end

#use_database(database = nil) ⇒ Object 



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

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 



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

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



150
151
152
153
154
155
156
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 150

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 



158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 158

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 



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

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