Module: ArJdbc::MsSQL

Includes:
LimitHelpers, TSqlMethods
Defined in:
lib/arjdbc/mssql/adapter.rb,
lib/arjdbc/mssql/limit_helpers.rb

Defined Under Namespace

Modules: Column, LimitHelpers

Class Method Summary collapse

Instance Method Summary collapse

Methods included from LimitHelpers

get_table_name

Methods included from TSqlMethods

#add_limit_offset!

Class Method Details

.arel2_visitors(config) ⇒ Object 



37
38
39
40
41
 
# File 'lib/arjdbc/mssql/adapter.rb', line 37

def self.arel2_visitors(config)
  require 'arel/visitors/sql_server'
  visitor_class = config[:sqlserver_version] == "2000" ? ::Arel::Visitors::SQLServer2000 : ::Arel::Visitors::SQLServer
  {}.tap {|v| %w(mssql sqlserver jdbcmssql).each {|x| v[x] = visitor_class } }
end

.column_selector ⇒ Object 



29
30
31
 
# File 'lib/arjdbc/mssql/adapter.rb', line 29

def self.column_selector
  [/sqlserver|tds|Microsoft SQL/i, lambda {|cfg,col| col.extend(::ArJdbc::MsSQL::Column)}]
end

.extended(mod) ⇒ Object 



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/arjdbc/mssql/adapter.rb', line 9

def self.extended(mod)
  unless defined?(@lob_callback_added)
    ActiveRecord::Base.class_eval do
      def after_save_with_mssql_lob
        self.class.columns.select { |c| c.sql_type =~ /image/i }.each do |c|
          value = self[c.name]
          value = value.to_yaml if unserializable_attribute?(c.name, c)
          next if value.nil?  || (value == '')

          connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value)
        end
      end
    end

    ActiveRecord::Base.after_save :after_save_with_mssql_lob
    @lob_callback_added = true
  end
  mod.add_version_specific_add_limit_offset
end

.jdbc_connection_class ⇒ Object 



33
34
35
 
# File 'lib/arjdbc/mssql/adapter.rb', line 33

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::MssqlJdbcConnection
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
 
# File 'lib/arjdbc/mssql/adapter.rb', line 356

def _execute(sql, name = nil)
  # Match the start of the sql to determine appropriate behaviour.  Be aware of
  # multi-line sql which might begin with 'create stored_proc' and contain 'insert into ...' lines.
  # Possible improvements include ignoring comment blocks prior to the first statement.
  if sql.lstrip =~ /\Ainsert/i
    if query_requires_identity_insert?(sql)
      table_name = get_table_name(sql)
      with_identity_insert_enabled(table_name) do
        id = @connection.execute_insert(sql)
      end
    else
      @connection.execute_insert(sql)
    end
  elsif sql.lstrip =~ /\A(create|exec)/i
    @connection.execute_update(sql)
  elsif sql.lstrip =~ /\A\(?\s*(select|show)/i
    repair_special_columns(sql)
    @connection.execute_query(sql)
  else
    @connection.execute_update(sql)
  end
end

#adapter_name ⇒ Object

:nodoc:



230
231
232
 
# File 'lib/arjdbc/mssql/adapter.rb', line 230

def adapter_name #:nodoc:
  'MsSQL'
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object

Adds a new column to the named table. See TableDefinition#column for details of the options you can use.



270
271
272
273
274
275
276
277
 
# File 'lib/arjdbc/mssql/adapter.rb', line 270

def add_column(table_name, column_name, type, options = {})
  clear_cached_table(table_name)
  add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  add_column_options!(add_column_sql, options)
  # TODO: Add support to mimic date columns, using constraints to mark them as such in the database
  # add_column_sql << " CONSTRAINT ck__#{table_name}__#{column_name}__date_only CHECK ( CONVERT(CHAR(12), #{quote_column_name(column_name)}, 14)='00:00:00:000' )" if type == :date
  execute(add_column_sql)
end

#add_lock!(sql, options) ⇒ Object

SELECT .. FOR UPDATE is not supported on Microsoft SQL Server



387
388
389
 
# File 'lib/arjdbc/mssql/adapter.rb', line 387

def add_lock!(sql, options)
  sql
end

#add_version_specific_add_limit_offset ⇒ Object 



47
48
49
50
51
52
53
54
 
# File 'lib/arjdbc/mssql/adapter.rb', line 47

def add_version_specific_add_limit_offset
  config[:sqlserver_version] = version = sqlserver_version
  if version == "2000"
    extend LimitHelpers::SqlServer2000AddLimitOffset
  else
    extend LimitHelpers::SqlServerAddLimitOffset
  end
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



284
285
286
287
288
 
# File 'lib/arjdbc/mssql/adapter.rb', line 284

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  clear_cached_table(table_name)
  change_column_type(table_name, column_name, type, options)
  change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



299
300
301
302
303
304
305
 
# File 'lib/arjdbc/mssql/adapter.rb', line 299

def change_column_default(table_name, column_name, default) #:nodoc:
  clear_cached_table(table_name)
  remove_default_constraint(table_name, column_name)
  unless default.nil?
    execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default)} FOR #{quote_column_name(column_name)}"
  end
end

#change_column_type(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



290
291
292
293
294
295
296
297
 
# File 'lib/arjdbc/mssql/adapter.rb', line 290

def change_column_type(table_name, column_name, type, options = {}) #:nodoc:
  clear_cached_table(table_name)
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  if options.has_key?(:null)
    sql += (options[:null] ? " NULL" : " NOT NULL")
  end
  execute(sql)
end

#change_order_direction(order) ⇒ Object 



234
235
236
237
238
239
240
241
242
 
# File 'lib/arjdbc/mssql/adapter.rb', line 234

def change_order_direction(order)
  order.split(",").collect do |fragment|
    case fragment
    when  /\bDESC\b/i     then fragment.gsub(/\bDESC\b/i, "ASC")
    when  /\bASC\b/i      then fragment.gsub(/\bASC\b/i, "DESC")
    else                  String.new(fragment).split(',').join(' DESC,') + ' DESC'
    end
  end.join(",")
end

#clear_cached_table(name) ⇒ Object 



463
464
465
 
# File 'lib/arjdbc/mssql/adapter.rb', line 463

def clear_cached_table(name)
  (@table_columns ||= {}).delete(name.to_s)
end

#columns(table_name, name = nil) ⇒ Object 



335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
 
# File 'lib/arjdbc/mssql/adapter.rb', line 335

def columns(table_name, name = nil)
  # It's possible for table_name to be an empty string, or nil, if something attempts to issue SQL
  # which doesn't involve a table.  IE. "SELECT 1" or "SELECT * from someFunction()".
  return [] if table_name.blank?
  table_name = table_name.to_s if table_name.is_a?(Symbol)

  # Remove []'s from around the table name, valid in a select statement, but not when matching metadata.
  table_name = table_name.gsub(/[\[\]]/, '')

  return [] if table_name =~ /^information_schema\./i
  @table_columns ||= {}
  unless @table_columns[table_name]
    @table_columns[table_name] = super
    @table_columns[table_name].each do |col|
      col.identity = true if col.sql_type =~ /identity/i
      col.is_special = true if col.sql_type =~ /text|ntext|image|xml/i
    end
  end
  @table_columns[table_name]
end

#create_database(name) ⇒ Object 



258
259
260
261
 
# File 'lib/arjdbc/mssql/adapter.rb', line 258

def create_database(name)
  execute "CREATE DATABASE #{name}"
  execute "USE #{name}"
end

#determine_order_clause(sql) ⇒ Object 



448
449
450
451
452
 
# File 'lib/arjdbc/mssql/adapter.rb', line 448

def determine_order_clause(sql)
  return $1 if sql =~ /ORDER BY (.*)$/
  table_name = get_table_name(sql)
  "#{table_name}.#{determine_primary_key(table_name)}"
end

#determine_primary_key(table_name) ⇒ Object 



454
455
456
457
458
459
460
461
 
# File 'lib/arjdbc/mssql/adapter.rb', line 454

def determine_primary_key(table_name)
  primary_key = columns(table_name).detect { |column| column.primary || column.identity }
  return primary_key.name if primary_key
  # Look for an id column.  Return it, without changing case, to cover dbs with a case-sensitive collation.
  columns(table_name).each { |column| return column.name if column.name =~ /^id$/i }
  # Give up and provide something which is going to crash almost certainly
  columns(table_name)[0].name
end

#drop_database(name) ⇒ Object 



253
254
255
256
 
# File 'lib/arjdbc/mssql/adapter.rb', line 253

def drop_database(name)
  execute "USE master"
  execute "DROP DATABASE #{name}"
end

#get_special_columns(table_name) ⇒ Object 



431
432
433
434
435
436
437
 
# File 'lib/arjdbc/mssql/adapter.rb', line 431

def get_special_columns(table_name)
  special = []
  columns(table_name).each do |col|
    special << col.name if col.is_special
  end
  special
end

#identity_column(table_name) ⇒ Object 



407
408
409
410
411
412
 
# File 'lib/arjdbc/mssql/adapter.rb', line 407

def identity_column(table_name)
  columns(table_name).each do |col|
    return col.name if col.identity
  end
  return nil
end

#modify_types(tp) ⇒ Object

:nodoc:



56
57
58
59
60
61
62
63
64
65
 
# File 'lib/arjdbc/mssql/adapter.rb', line 56

def modify_types(tp) #:nodoc:
  super(tp)
  tp[:string] = {:name => "NVARCHAR", :limit => 255}
  if sqlserver_version == "2000"
    tp[:text] = {:name => "NTEXT"}
  else
    tp[:text] = {:name => "NVARCHAR(MAX)"}
  end
  tp
end

#query_requires_identity_insert?(sql) ⇒ Boolean

Returns:

  • (Boolean) 


414
415
416
417
418
419
420
421
 
# File 'lib/arjdbc/mssql/adapter.rb', line 414

def query_requires_identity_insert?(sql)
  table_name = get_table_name(sql)
  id_column = identity_column(table_name)
  if sql.strip =~ /insert into [^ ]+ ?\((.+?)\)/i
    insert_columns = $1.split(/, */).map(&method(:unquote_column_name))
    return table_name if insert_columns.include?(id_column)
  end
end

#quote(value, column = nil) ⇒ Object 



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
 
# File 'lib/arjdbc/mssql/adapter.rb', line 186

def quote(value, column = nil)
  return value.quoted_id if value.respond_to?(:quoted_id)

  case value
  # SQL Server 2000 doesn't let you insert an integer into a NVARCHAR
  # column, so we include Integer here.
  when String, ActiveSupport::Multibyte::Chars, Integer
    value = value.to_s
    if column && column.type == :binary
      "'#{quote_string(ArJdbc::MsSQL::Column.string_to_binary(value))}'" # ' (for ruby-mode)
    elsif column && [:integer, :float].include?(column.type)
      value = column.type == :integer ? value.to_i : value.to_f
      value.to_s
    elsif !column.respond_to?(:is_utf8?) || column.is_utf8?
      "N'#{quote_string(value)}'" # ' (for ruby-mode)
    else
      super
    end
  when TrueClass             then '1'
  when FalseClass            then '0'
  else                       super
  end
end

#quote_column_name(name) ⇒ Object 



218
219
220
 
# File 'lib/arjdbc/mssql/adapter.rb', line 218

def quote_column_name(name)
  "[#{name}]"
end

#quote_string(string) ⇒ Object 



210
211
212
 
# File 'lib/arjdbc/mssql/adapter.rb', line 210

def quote_string(string)
  string.gsub(/\'/, "''")
end

#quote_table_name(name) ⇒ Object 



214
215
216
 
# File 'lib/arjdbc/mssql/adapter.rb', line 214

def quote_table_name(name)
  quote_column_name(name)
end

#quoted_false ⇒ Object 



226
227
228
 
# File 'lib/arjdbc/mssql/adapter.rb', line 226

def quoted_false
  quote false
end

#quoted_true ⇒ Object 



222
223
224
 
# File 'lib/arjdbc/mssql/adapter.rb', line 222

def quoted_true
  quote true
end

#recreate_database(name) ⇒ Object 



248
249
250
251
 
# File 'lib/arjdbc/mssql/adapter.rb', line 248

def recreate_database(name)
  drop_database(name)
  create_database(name)
end

#remove_check_constraints(table_name, column_name) ⇒ Object 



322
323
324
325
326
327
328
329
 
# File 'lib/arjdbc/mssql/adapter.rb', line 322

def remove_check_constraints(table_name, column_name)
  clear_cached_table(table_name)
  # TODO remove all constraints in single method
  constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
  constraints.each do |constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
  end
end

#remove_column(table_name, column_name) ⇒ Object 



307
308
309
310
311
312
 
# File 'lib/arjdbc/mssql/adapter.rb', line 307

def remove_column(table_name, column_name)
  clear_cached_table(table_name)
  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
end

#remove_default_constraint(table_name, column_name) ⇒ Object 



314
315
316
317
318
319
320
 
# File 'lib/arjdbc/mssql/adapter.rb', line 314

def remove_default_constraint(table_name, column_name)
  clear_cached_table(table_name)
  defaults = select "select def.name from sysobjects def, syscolumns col, sysobjects tab where col.cdefault = def.id and col.name = '#{column_name}' and tab.name = '#{table_name}' and col.id = tab.id"
  defaults.each {|constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
  }
end

#remove_index(table_name, options = {}) ⇒ Object 



331
332
333
 
# File 'lib/arjdbc/mssql/adapter.rb', line 331

def remove_index(table_name, options = {})
  execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
end

#rename_column(table, column, new_column_name) ⇒ Object 



279
280
281
282
 
# File 'lib/arjdbc/mssql/adapter.rb', line 279

def rename_column(table, column, new_column_name)
  clear_cached_table(table)
  execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end

#rename_table(name, new_name) ⇒ Object 



263
264
265
266
 
# File 'lib/arjdbc/mssql/adapter.rb', line 263

def rename_table(name, new_name)
  clear_cached_table(name)
  execute "EXEC sp_rename '#{name}', '#{new_name}'"
end

#repair_special_columns(sql) ⇒ Object 



439
440
441
442
443
444
445
446
 
# File 'lib/arjdbc/mssql/adapter.rb', line 439

def repair_special_columns(sql)
  special_cols = get_special_columns(get_table_name(sql))
  for col in special_cols.to_a
    sql.gsub!(Regexp.new(" #{col.to_s} = "), " #{col.to_s} LIKE ")
    sql.gsub!(/ORDER BY #{col.to_s}/i, '')
  end
  sql
end

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



379
380
381
382
383
384
 
# File 'lib/arjdbc/mssql/adapter.rb', line 379

def select(sql, name = nil, binds = [])
  sql = substitute_binds(sql, binds)
  log(sql, name) do
    @connection.execute_query(sql)
  end
end

#set_identity_insert(table_name, enable = true) ⇒ Object 



401
402
403
404
405
 
# File 'lib/arjdbc/mssql/adapter.rb', line 401

def set_identity_insert(table_name, enable = true)
  execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}"
rescue Exception => e
  raise ActiveRecord::ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}"
end

#sqlserver_version ⇒ Object 



43
44
45
 
# File 'lib/arjdbc/mssql/adapter.rb', line 43

def sqlserver_version
  @sqlserver_version ||= select_value("select @@version")[/Microsoft SQL Server\s+(\d{4})/, 1]
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


244
245
246
 
# File 'lib/arjdbc/mssql/adapter.rb', line 244

def supports_ddl_transactions?
  true
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

:nodoc:



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/arjdbc/mssql/adapter.rb', line 67

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  # MSSQL's NVARCHAR(n | max) column supports either a number between 1 and
  # 4000, or the word "MAX", which corresponds to 2**30-1 UCS-2 characters.
  #
  # It does not accept NVARCHAR(1073741823) here, so we have to change it
  # to NVARCHAR(MAX), even though they are logically equivalent.
  #
  # MSSQL Server 2000 is skipped here because I don't know how it will behave.
  #
  # See: http://msdn.microsoft.com/en-us/library/ms186939.aspx
  if type.to_s == 'string' and limit == 1073741823 and sqlserver_version != "2000"
    'NVARCHAR(MAX)'
  elsif %w( boolean date datetime ).include?(type.to_s)
    super(type)   # cannot specify limit/precision/scale with these types
  else
    super
  end
end

#unquote_column_name(name) ⇒ Object 



423
424
425
426
427
428
429
 
# File 'lib/arjdbc/mssql/adapter.rb', line 423

def unquote_column_name(name)
  if name =~ /^\[.*\]$/
    name[1..-2]
  else
    name
  end
end

#with_identity_insert_enabled(table_name, &block) ⇒ Object

Turns IDENTITY_INSERT ON for table during execution of the block N.B. This sets the state of IDENTITY_INSERT to OFF after the block has been executed without regard to its previous state



394
395
396
397
398
399
 
# File 'lib/arjdbc/mssql/adapter.rb', line 394

def with_identity_insert_enabled(table_name, &block)
  set_identity_insert(table_name, true)
  yield
ensure
  set_identity_insert(table_name, false)
end