Module: ArJdbc::MSSQL

Includes:
TSqlMethods, Utils
Defined in:
lib/arjdbc/mssql/utils.rb,
lib/arjdbc/mssql/adapter.rb,
lib/arjdbc/mssql/lock_helpers.rb,
lib/arjdbc/mssql/tsql_methods.rb,
lib/arjdbc/mssql/limit_helpers.rb

Defined Under Namespace

Modules: Column, LimitHelpers, LockHelpers, TSqlMethods, Utils

Constant Summary collapse

ADAPTER_NAME = 
'MSSQL'
SKIP_COLUMNS_TABLE_NAMES_RE =

:nodoc:

/^information_schema\./i
@@_lob_callback_added = 
nil

Constants included from Utils

Utils::GET_TABLE_NAME_FROM_RE, Utils::GET_TABLE_NAME_INSERT_UPDATE_RE

Class Method Summary collapse

Instance Method Summary collapse

Methods included from TSqlMethods

#add_limit_offset!

Methods included from Utils

get_table_name, unqualify_db_name, unqualify_table_name, unqualify_table_schema, unquote_column_name, unquote_string, unquote_table_name

Class Method Details

.arel2_visitors(config) ⇒ Object 



52
53
54
55
56
57
 
# File 'lib/arjdbc/mssql/adapter.rb', line 52

def self.arel2_visitors(config)
  require 'arel/visitors/sql_server'
  visitors = config[:sqlserver_version] == '2000' ? 
    ::Arel::Visitors::SQLServer2000 : ::Arel::Visitors::SQLServer
  { 'mssql' => visitors, 'jdbcmssql' => visitors, 'sqlserver' => visitors }
end

.column_selector ⇒ Object 



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

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

.extended(base) ⇒ Object 



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
# File 'lib/arjdbc/mssql/adapter.rb', line 15

def self.extended(base)
  unless @@_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 |column|
          value = ::ArJdbc::SerializedAttributesHelper.dump_column_value(self, column)
          next if value.nil? || (value == '')

          connection.write_large_object(
            column.type == :binary, column.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
  
  if ( version = base.sqlserver_version ) == '2000'
    extend LimitHelpers::SqlServer2000AddLimitOffset
  else
    extend LimitHelpers::SqlServerAddLimitOffset
  end
  base.config[:sqlserver_version] ||= version
end

.jdbc_connection_class ⇒ Object 



48
49
50
 
# File 'lib/arjdbc/mssql/adapter.rb', line 48

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

Instance Method Details

#adapter_name ⇒ Object

:nodoc:



241
242
243
 
# File 'lib/arjdbc/mssql/adapter.rb', line 241

def adapter_name # :nodoc:
  ADAPTER_NAME
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.



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

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

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

:nodoc:



296
297
298
299
300
 
# File 'lib/arjdbc/mssql/adapter.rb', line 296

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:



311
312
313
314
315
316
317
 
# File 'lib/arjdbc/mssql/adapter.rb', line 311

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:



302
303
304
305
306
307
308
309
 
# File 'lib/arjdbc/mssql/adapter.rb', line 302

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 



245
246
247
248
249
250
251
252
253
254
 
# File 'lib/arjdbc/mssql/adapter.rb', line 245

def change_order_direction(order)
  asc, desc = /\bASC\b/i, /\bDESC\b/i
  order.split(",").collect do |fragment|
    case fragment
    when desc  then fragment.gsub(desc, "ASC")
    when asc   then fragment.gsub(asc, "DESC")
    else "#{fragment.split(',').join(' DESC,')} DESC"
    end
  end.join(",")
end

#clear_cached_table(name) ⇒ Object 



437
438
439
 
# File 'lib/arjdbc/mssql/adapter.rb', line 437

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

#columns(table_name, name = nil) ⇒ Object 



368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
 
# File 'lib/arjdbc/mssql/adapter.rb', line 368

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 = unquote_table_name(table_name)

  return [] if table_name =~ SKIP_COLUMNS_TABLE_NAMES_RE
  
  unless (@table_columns ||= {})[table_name]
    @table_columns[table_name] = super
    @table_columns[table_name].each do |column|
      column.identity = true if column.sql_type =~ /identity/i
      column.is_special = true if column.sql_type =~ /text|ntext|image|xml/i
    end
  end
  @table_columns[table_name]
end

#create_database(name, options = {}) ⇒ Object 



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

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

#determine_order_clause(sql) ⇒ Object 



421
422
423
424
425
 
# File 'lib/arjdbc/mssql/adapter.rb', line 421

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 



427
428
429
430
431
432
433
434
435
 
# File 'lib/arjdbc/mssql/adapter.rb', line 427

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



265
266
267
268
 
# File 'lib/arjdbc/mssql/adapter.rb', line 265

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

#identity_column(table_name) ⇒ Object 



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

def identity_column(table_name)
  for column in columns(table_name)
    return column.name if column.identity
  end
  nil
end

#modify_types(types) ⇒ Object

:nodoc:



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

def modify_types(types) #:nodoc:
  super(types)
  types[:string] = { :name => "NVARCHAR", :limit => 255 }
  if sqlserver_2000?
    types[:text] = { :name => "NTEXT" }
  else
    types[:text] = { :name => "NVARCHAR(MAX)" }
  end
  types[:primary_key] = "int NOT NULL IDENTITY(1, 1) PRIMARY KEY"
  types[:integer][:limit] = nil
  types[:boolean] = { :name => "bit" }
  types[:binary] = { :name => "image" }
  types
end

#query_requires_identity_insert?(sql) ⇒ Boolean

Returns:

  • (Boolean) 


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

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 



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
 
# File 'lib/arjdbc/mssql/adapter.rb', line 203

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
    column_type = column && column.type
    if column_type == :binary
      "'#{quote_string(ArJdbc::MSSQL::Column.string_to_binary(value))}'" # ' (for ruby-mode)
    elsif column_type == :integer
      value.to_i.to_s
    elsif column_type == :float
      value.to_f.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 



233
234
235
236
237
 
# File 'lib/arjdbc/mssql/adapter.rb', line 233

def quote_column_name(name)
  name.to_s.split('.').map do |n| # "[#{name}]"
    n =~ /^\[.*\]$/ ? n : "[#{n.gsub(']', ']]')}]"
  end.join('.')
end

#quote_table_name(name) ⇒ Object 



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

def quote_table_name(name)
  quote_column_name(name)
end

#recreate_database(name, options = {}) ⇒ Object 



260
261
262
263
 
# File 'lib/arjdbc/mssql/adapter.rb', line 260

def recreate_database(name, options = {})
  drop_database(name)
  create_database(name, options)
end

#remove_check_constraints(table_name, column_name) ⇒ Object 



348
349
350
351
352
353
354
355
356
 
# File 'lib/arjdbc/mssql/adapter.rb', line 348

def remove_check_constraints(table_name, column_name)
  clear_cached_table(table_name)
  constraints = select_values "SELECT constraint_name" <<
    " FROM information_schema.constraint_column_usage" <<
    " WHERE table_name = '#{table_name}' AND column_name = '#{column_name}'"
  constraints.each do |constraint_name|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint_name}"
  end
end

#remove_column(table_name, *column_names) ⇒ Object

:nodoc:



319
320
321
322
323
324
325
326
 
# File 'lib/arjdbc/mssql/adapter.rb', line 319

def remove_column(table_name, *column_names) #:nodoc:
  clear_cached_table(table_name)
  for column_name in column_names.flatten
    remove_check_constraints(table_name, column_name)
    remove_default_constraint(table_name, column_name)
    execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
  end
end

#remove_default_constraint(table_name, column_name) ⇒ Object 



328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
 
# File 'lib/arjdbc/mssql/adapter.rb', line 328

def remove_default_constraint(table_name, column_name)
  clear_cached_table(table_name)
  if sqlserver_2000?
    # NOTE: since SQLServer 2005 these are provided as sys.sysobjects etc.
    # but only due backwards-compatibility views and should be avoided ...
    defaults = select_values "SELECT d.name" <<
      " FROM sysobjects d, syscolumns c, sysobjects t" <<
      " WHERE c.cdefault = d.id AND c.name = '#{column_name}'" <<
      " AND t.name = '#{table_name}' AND c.id = t.id"
  else
    defaults = select_values "SELECT d.name FROM sys.tables t" <<
      " JOIN sys.default_constraints d ON d.parent_object_id = t.object_id" <<
      " JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = d.parent_column_id" <<
      " WHERE t.name = '#{table_name}' AND c.name = '#{column_name}'"
  end
  defaults.each do |def_name|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{def_name}"
  end
end

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



358
359
360
 
# File 'lib/arjdbc/mssql/adapter.rb', line 358

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 



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

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 



275
276
277
278
 
# File 'lib/arjdbc/mssql/adapter.rb', line 275

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

#reset_column_information ⇒ Object 



441
442
443
 
# File 'lib/arjdbc/mssql/adapter.rb', line 441

def reset_column_information
  @table_columns = nil if defined? @table_columns
end

#set_identity_insert(table_name, enable = true) ⇒ Object 



398
399
400
401
402
403
 
# File 'lib/arjdbc/mssql/adapter.rb', line 398

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} due : #{e.inspect}"
end

#sqlserver_version ⇒ Object 



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

def sqlserver_version
  @sqlserver_version ||= begin
    config_version = config[:sqlserver_version]
    config_version ? config_version.to_s :
      select_value("SELECT @@version")[/Microsoft SQL Server\s+(\d{4})/, 1]
  end
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


256
257
258
 
# File 'lib/arjdbc/mssql/adapter.rb', line 256

def supports_ddl_transactions?
  true
end

#table_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


362
363
364
 
# File 'lib/arjdbc/mssql/adapter.rb', line 362

def table_exists?(name)
  !! ( jdbc_columns(name) rescue nil )
end

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

:nodoc:



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/arjdbc/mssql/adapter.rb', line 82

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' && limit == 1073741823 && ! sqlserver_2000?
    'NVARCHAR(MAX)'
  elsif %w( boolean date datetime ).include?(type.to_s)
    super(type) # cannot specify limit/precision/scale with these types
  else
    super # TSqlMethods#type_to_sql
  end
end

#with_identity_insert_enabled(table_name) ⇒ 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



391
392
393
394
395
396
 
# File 'lib/arjdbc/mssql/adapter.rb', line 391

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