Module: ArJdbc::MSSQL

Includes:
ExplainSupport, LimitHelpers, Utils
Included in:
ActiveRecord::ConnectionAdapters::MSSQLAdapter
Defined in:
lib/arjdbc/mssql/types.rb,
lib/arjdbc/mssql/utils.rb,
lib/arjdbc/mssql/column.rb,
lib/arjdbc/mssql/adapter.rb,
lib/arjdbc/mssql/lock_methods.rb,
lib/arjdbc/mssql/limit_helpers.rb,
lib/arjdbc/mssql/explain_support.rb

Defined Under Namespace

Modules: Column, ExplainSupport, LimitHelpers, LockMethods, Utils

Constant Summary collapse

NATIVE_DATABASE_TYPES = 
{
  :primary_key => 'int NOT NULL IDENTITY(1,1) PRIMARY KEY',
  :integer   =>  { :name => 'int', }, # :limit => 4
  :boolean   =>  { :name => 'bit' },
  :decimal   =>  { :name => 'decimal' },
  :float     =>  { :name => 'float' },
  :bigint    =>  { :name => 'bigint' },
  :real      =>  { :name => 'real' },
  :date      =>  { :name => 'date' },
  :time      =>  { :name => 'time' },
  :datetime  =>  { :name => 'datetime' },
  :timestamp =>  { :name => 'datetime' },

  :string    =>  { :name => 'nvarchar', :limit => 4000 },
  #:varchar    =>  { :name => 'varchar' }, # limit: 8000
  :text      =>  { :name => 'nvarchar(max)' },
  :text_basic =>  { :name => 'text' },
  #:ntext      =>  { :name => 'ntext' },
  :char      =>  { :name => 'char' },
  #:nchar     =>  { :name => 'nchar' },
  :binary    =>  { :name => 'image' }, # NOTE: :name => 'varbinary(max)'
  :binary_basic => { :name => 'binary' },
  :uuid      =>  { :name => 'uniqueidentifier' },
  :money     =>  { :name => 'money' },
  #:smallmoney => { :name => 'smallmoney' },
}
ADAPTER_NAME = 
'MSSQL'.freeze

Constants included from ExplainSupport

ExplainSupport::DISABLED

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 ExplainSupport

#explain, #set_showplan_option, #supports_explain?, #with_showplan_on

Methods included from Utils

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

Class Method Details

.arel2_visitors(config) ⇒ Object

Deprecated.

no longer used

See Also:

  • ActiveRecord::ConnectionAdapters::JdbcAdapter#arel2_visitors


117
118
119
120
 
# File 'lib/arjdbc/mssql/adapter.rb', line 117

def self.arel2_visitors(config)
  visitor = arel_visitor_type(config)
  { 'mssql' => visitor, 'jdbcmssql' => visitor, 'sqlserver' => visitor }
end

.arel_visitor_type(config) ⇒ Object

See Also:



105
106
107
108
109
 
# File 'lib/arjdbc/mssql/adapter.rb', line 105

def self.arel_visitor_type(config)
  require 'arel/visitors/sql_server'
  ( config && config[:sqlserver_version].to_s == '2000' ) ?
    ::Arel::Visitors::SQLServer2000 : ::Arel::Visitors::SQLServer
end

.column_selector ⇒ Object

See Also:

  • ActiveRecord::ConnectionAdapters::JdbcColumn#column_types


5
6
7
 
# File 'lib/arjdbc/mssql/column.rb', line 5

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

.cs_equality_operator ⇒ Object

Operator for sorting strings in SQLServer, setup as :

ArJdbc::MSSQL.cs_equality_operator = 'COLLATE Latin1_General_CS_AS_WS'



82
 
# File 'lib/arjdbc/mssql/adapter.rb', line 82

def self.cs_equality_operator; @@cs_equality_operator; end

.cs_equality_operator=(operator) ⇒ Object

See Also:

  • #cs_equality_operator


84
 
# File 'lib/arjdbc/mssql/adapter.rb', line 84

def self.cs_equality_operator=(operator); @@cs_equality_operator = operator; end

.jdbc_connection_class ⇒ Object

See Also:



97
98
99
 
# File 'lib/arjdbc/mssql/adapter.rb', line 97

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

.update_lob_values=(update) ⇒ Object

See Also:

  • #update_lob_values?


73
 
# File 'lib/arjdbc/mssql/adapter.rb', line 73

def self.update_lob_values=(update); @@update_lob_values = update; end

.update_lob_values? ⇒ Boolean

Note:

This only applies when prepared statements are not used.

Updating records with LOB values (binary/text columns) in a separate statement can be disabled using :

ArJdbc::MSSQL.update_lob_values = false

Returns:

  • (Boolean) 


71
 
# File 'lib/arjdbc/mssql/adapter.rb', line 71

def self.update_lob_values?; @@update_lob_values; end

Instance Method Details

#adapter_name ⇒ Object 



320
321
322
 
# File 'lib/arjdbc/mssql/adapter.rb', line 320

def adapter_name
  ADAPTER_NAME
end

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

Adds a new column to the named table.



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

def add_column(table_name, column_name, type, options = {})
  clear_cached_table(table_name)
  add_column_sql = "ALTER TABLE #{quote_table_name(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 



482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
 
# File 'lib/arjdbc/mssql/adapter.rb', line 482

def change_column(table_name, column_name, type, options = {})
  column = columns(table_name).find { |c| c.name.to_s == column_name.to_s }

  indexes = EMPTY_ARRAY
  if options_include_default?(options) || (column && column.type != type.to_sym)
    remove_default_constraint(table_name, column_name)
    indexes = indexes(table_name).select{ |index| index.columns.include?(column_name.to_s) }
    remove_indexes(table_name, column_name)
  end

  if ! options[:null].nil? && options[:null] == false && ! options[:default].nil?
    execute "UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote_default_value(options[:default], column)} WHERE #{quote_column_name(column_name)} IS NULL"
    clear_cached_table(table_name)
  end
  change_column_type(table_name, column_name, type, options)
  change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)

  indexes.each do |index| # add any removed indexes back
    index_columns = index.columns.map { |c| quote_column_name(c) }.join(', ')
    execute "CREATE INDEX #{quote_table_name(index.name)} ON #{quote_table_name(table_name)} (#{index_columns})"
  end
end

#change_column_default(table_name, column_name, default) ⇒ Object 



513
514
515
516
517
518
519
520
521
 
# File 'lib/arjdbc/mssql/adapter.rb', line 513

def change_column_default(table_name, column_name, default)
  remove_default_constraint(table_name, column_name)
  unless default.nil?
    column = columns(table_name).find { |c| c.name.to_s == column_name.to_s }
    result = execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote_default_value(default, column)} FOR #{quote_column_name(column_name)}"
    clear_cached_table(table_name)
    result
  end
end

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



505
506
507
508
509
510
511
 
# File 'lib/arjdbc/mssql/adapter.rb', line 505

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

#change_order_direction(order) ⇒ Object 



324
325
326
327
328
329
330
331
332
333
 
# File 'lib/arjdbc/mssql/adapter.rb', line 324

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

#charset ⇒ Object 



384
385
386
 
# File 'lib/arjdbc/mssql/adapter.rb', line 384

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

#clear_cache! ⇒ Object 



79
80
81
82
 
# File 'lib/arjdbc/mssql/types.rb', line 79

def clear_cache!
  super
  reload_type_map
end

#clear_cached_table(table_name) ⇒ Object 



617
618
619
 
# File 'lib/arjdbc/mssql/adapter.rb', line 617

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

#collation ⇒ Object 



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

def collation
  select_value "SELECT SERVERPROPERTY('Collation')"
end

#columns(table_name, name = nil, default = EMPTY_ARRAY) ⇒ Object 



601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
 
# File 'lib/arjdbc/mssql/adapter.rb', line 601

def columns(table_name, name = nil, default = EMPTY_ARRAY)
  # 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 default if table_name.blank?

  table_name = unquote_table_name(table_name)

  return default if table_name =~ SKIP_COLUMNS_TABLE_NAMES_RE

  unless columns = ( @table_columns ||= {} )[table_name]
    @table_columns[table_name] = columns = super(table_name, name)
  end
  columns
end

#columns_for_distinct(columns, orders) ⇒ Object 



461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
 
# File 'lib/arjdbc/mssql/adapter.rb', line 461

def columns_for_distinct(columns, orders)
  return columns if orders.blank?

  # construct a clean list of column names from the ORDER BY clause,
  # removing any ASC/DESC modifiers
  order_columns = [ orders ]; order_columns.flatten! # AR 3.x vs 4.x
  order_columns.map! do |column|
    column = column.to_sql unless column.is_a?(String) # handle AREL node
    column.split(',').collect!{ |s| s.split.first }
  end.flatten!
  order_columns.reject!(&:blank?)
  order_columns = order_columns.zip(0...order_columns.size).to_a
  order_columns = order_columns.map{ |s, i| "#{s}" }

  columns = [ columns ]; columns.flatten!
  columns.push( *order_columns ).join(', ')
  # return a DISTINCT clause that's distinct on the columns we want but
  # includes all the required columns for the ORDER BY to work properly
end

#configure_connection ⇒ Object 



122
123
124
 
# File 'lib/arjdbc/mssql/adapter.rb', line 122

def configure_connection
  use_database # config[:database]
end

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



424
425
426
 
# File 'lib/arjdbc/mssql/adapter.rb', line 424

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

#current_database ⇒ Object 



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

def current_database
  select_value 'SELECT DB_NAME()'
end

#current_user ⇒ Object

SELECT CURRENT_USER



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

def current_user
  @current_user ||= @connection.execute_query_raw("SELECT CURRENT_USER").first['']
end

#database_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


428
429
430
 
# File 'lib/arjdbc/mssql/adapter.rb', line 428

def database_exists?(name)
  select_value "SELECT name FROM sys.databases WHERE name = '#{name}'"
end

#default_schema ⇒ Object Also known as: current_schema

Returns the default schema (to be used for table resolution) used for the #current_user.



360
361
362
363
364
365
366
 
# File 'lib/arjdbc/mssql/adapter.rb', line 360

def default_schema
  return current_user if sqlserver_2000?
  @default_schema ||=
    @connection.execute_query_raw(
      "SELECT default_schema_name FROM sys.database_principals WHERE name = CURRENT_USER"
    ).first['default_schema_name']
end

#default_schema=(default_schema) ⇒ Object Also known as: current_schema=

Note:

This is not supported on SQL Server 2000 !

Allows for changing of the default schema (to be used during unqualified table name resolution).



372
373
374
375
376
 
# File 'lib/arjdbc/mssql/adapter.rb', line 372

def default_schema=(default_schema) # :nodoc:
  raise "changing DEFAULT_SCHEMA only supported on SQLServer 2005+" if sqlserver_2000?
  execute("ALTER #{current_user} WITH DEFAULT_SCHEMA=#{default_schema}")
  @default_schema = nil if defined?(@default_schema)
end

#disable_referential_integrity ⇒ Object 



642
643
644
645
646
647
 
# File 'lib/arjdbc/mssql/adapter.rb', line 642

def disable_referential_integrity
  execute "EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'"
  yield
ensure
  execute "EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'"
end

#distinct(columns, order_by) ⇒ Object

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause. MSSQL requires the ORDER BY columns in the select list for distinct queries.



457
458
459
 
# File 'lib/arjdbc/mssql/adapter.rb', line 457

def distinct(columns, order_by)
  "DISTINCT #{columns_for_distinct(columns, order_by)}"
end

#drop_database(name) ⇒ Object 



418
419
420
421
422
 
# File 'lib/arjdbc/mssql/adapter.rb', line 418

def drop_database(name)
  current_db = current_database
  use_database('master') if current_db.to_s == name
  execute "DROP DATABASE #{quote_database_name(name)}"
end

#exec_proc(proc_name, *variables) ⇒ Object Also known as: execute_procedure

Support for executing a stored procedure.



669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
 
# File 'lib/arjdbc/mssql/adapter.rb', line 669

def exec_proc(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
  log(sql, 'Execute Procedure') do
    result = @connection.execute_query_raw(sql)
    result.map! do |row|
      row = row.is_a?(Hash) ? row.with_indifferent_access : row
      yield(row) if block_given?
      row
    end
    result
  end
end

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



690
691
692
693
694
695
696
697
698
699
700
701
702
703
 
# File 'lib/arjdbc/mssql/adapter.rb', line 690

def exec_query(sql, name = 'SQL', binds = [])
  # NOTE: we allow to execute SQL as requested returning a results.
  # e.g. this allows to use SQLServer's EXEC with a result set ...
  if sql.respond_to?(:to_sql)
    sql = to_sql(sql, binds); to_sql = true
  end
  sql = repair_special_columns(sql)
  if prepared_statements?
    log(sql, name, binds) { @connection.execute_query(sql, binds) }
  else
    sql = suble_binds(sql, binds) unless to_sql # deprecated behavior
    log(sql, name) { @connection.execute_query(sql) }
  end
end

#exec_query_raw(sql, name = 'SQL', binds = [], &block) ⇒ Object 



706
707
708
709
710
711
712
713
714
715
716
717
 
# File 'lib/arjdbc/mssql/adapter.rb', line 706

def exec_query_raw(sql, name = 'SQL', binds = [], &block)
  if sql.respond_to?(:to_sql)
    sql = to_sql(sql, binds); to_sql = true
  end
  sql = repair_special_columns(sql)
  if prepared_statements?
    log(sql, name, binds) { @connection.execute_query_raw(sql, binds, &block) }
  else
    sql = suble_binds(sql, binds) unless to_sql # deprecated behavior
    log(sql, name) { @connection.execute_query_raw(sql, &block) }
  end
end

#initialize_type_map(m) ⇒ Object 



4
5
6
7
8
9
10
11
12
13
14
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/arjdbc/mssql/types.rb', line 4

def initialize_type_map(m)
  #m.register_type              %r{.*},             UnicodeStringType.new
  # Exact Numerics
  register_class_with_limit m, /^bigint./,          BigIntegerType
  m.alias_type                 'bigint',            'bigint(8)'
  register_class_with_limit m, /^int\(|\s/,         ActiveRecord::Type::Integer
  m.alias_type                 /^integer/,          'int(4)'
  m.alias_type                 'int',               'int(4)'
  register_class_with_limit m, /^smallint./,        SmallIntegerType
  m.alias_type                 'smallint',          'smallint(2)'
  register_class_with_limit m, /^tinyint./,         TinyIntegerType
  m.alias_type                 'tinyint',           'tinyint(1)'
  m.register_type              /^bit/,              ActiveRecord::Type::Boolean.new
  m.register_type              %r{\Adecimal} do |sql_type|
    scale = extract_scale(sql_type)
    precision = extract_precision(sql_type)
    DecimalType.new :precision => precision, :scale => scale
    #if scale == 0
    #  ActiveRecord::Type::Integer.new(:precision => precision)
    #else
    #  DecimalType.new(:precision => precision, :scale => scale)
    #end
  end
  m.alias_type                 %r{\Anumeric},       'decimal'
  m.register_type              /^money/,            MoneyType.new
  m.register_type              /^smallmoney/,       SmallMoneyType.new
  # Approximate Numerics
  m.register_type              /^float/,            ActiveRecord::Type::Float.new
  m.register_type              /^real/,             RealType.new
  # Date and Time
  m.register_type              /^date\(?/,          ActiveRecord::Type::Date.new
  m.register_type              /^datetime\(?/,      DateTimeType.new
  m.register_type              /smalldatetime/,     SmallDateTimeType.new
  m.register_type              %r{\Atime} do |sql_type|
    TimeType.new :precision => extract_precision(sql_type)
  end
  # Character Strings
  register_class_with_limit m, %r{\Achar}i,         CharType
  #register_class_with_limit m, %r{\Avarchar}i,      VarcharType
  m.register_type              %r{\Anvarchar}i do |sql_type|
    limit = extract_limit(sql_type)
    if limit == 2_147_483_647 # varchar(max)
      VarcharMaxType.new
    else
      VarcharType.new :limit => limit
    end
  end
  #m.register_type              'varchar(max)',      VarcharMaxType.new
  m.register_type              /^text/,             TextType.new
  # Unicode Character Strings
  register_class_with_limit m, %r{\Anchar}i,        UnicodeCharType
  #register_class_with_limit m, %r{\Anvarchar}i,     UnicodeVarcharType
  m.register_type              %r{\Anvarchar}i do |sql_type|
    limit = extract_limit(sql_type)
    if limit == 1_073_741_823 # nvarchar(max)
      UnicodeVarcharMaxType.new
    else
      UnicodeVarcharType.new :limit => limit
    end
  end
  #m.register_type              'nvarchar(max)',     UnicodeVarcharMaxType.new
  m.alias_type                 'string',            'nvarchar(4000)'
  m.register_type              /^ntext/,            UnicodeTextType.new
  # Binary Strings
  register_class_with_limit m, %r{\Aimage}i,        ImageType
  register_class_with_limit m, %r{\Abinary}i,       BinaryType
  register_class_with_limit m, %r{\Avarbinary}i,    VarbinaryType
  #m.register_type              'varbinary(max)',    VarbinaryMaxType.new
  # Other Data Types
  m.register_type              'uniqueidentifier',  UUIDType.new
  # TODO
  #m.register_type              'timestamp',         SQLServer::Type::Timestamp.new
  m.register_type              'xml',               XmlType.new
end

#jdbc_column_class ⇒ Object

See Also:



102
 
# File 'lib/arjdbc/mssql/adapter.rb', line 102

def jdbc_column_class; ::ActiveRecord::ConnectionAdapters::MSSQLColumn end

#modify_types(types) ⇒ Object 



167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/arjdbc/mssql/adapter.rb', line 167

def modify_types(types)
  if sqlserver_2000?
    types[:primary_key] = NATIVE_DATABASE_TYPES[:primary_key]
    types[:string] = NATIVE_DATABASE_TYPES[:string]
    types[:boolean] = NATIVE_DATABASE_TYPES[:boolean]
    types[:text] = { :name => "ntext" }
    types[:integer][:limit] = nil
    types[:binary] = { :name => "image" }
  else
    # ~ private types for better "native" adapter compatibility
    types[:varchar_max]   =  { :name => 'varchar(max)' }
    types[:nvarchar_max]  =  { :name => 'nvarchar(max)' }
    types[:varbinary_max] =  { :name => 'varbinary(max)' }
  end
  types[:string][:limit] = 255 unless AR40 # backwards compatibility
  types
end

#native_database_types ⇒ Object 



161
162
163
164
165
 
# File 'lib/arjdbc/mssql/adapter.rb', line 161

def native_database_types
  # NOTE: due compatibility we're using the generic type resolution
  # ... NATIVE_DATABASE_TYPES won't be used at all on SQLServer 2K
  sqlserver_2000? ? super : super.merge(NATIVE_DATABASE_TYPES)
end

#quote(value, column = nil) ⇒ Object 



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
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/arjdbc/mssql/adapter.rb', line 221

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

  case value
  # SQL Server 2000 doesn't let you insert an integer into a NVARCHAR
  when String, ActiveSupport::Multibyte::Chars, Integer
    value = value.to_s
    column_type = column && column.type
    if column_type == :binary
      if update_lob_value?(value, column)
        BLOB_VALUE_MARKER
      else
        "'#{quote_string(column.class.string_to_binary(value))}'" # ' (for ruby-mode)
      end
    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 Date, Time
    if column && column.type == :time
      "'#{quoted_time(value)}'"
    else
      "'#{quoted_date(value)}'"
    end
  when TrueClass  then '1'
  when FalseClass then '0'
  else super
  end
end

#quote_column_name(name) ⇒ Object 



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

def quote_column_name(name)
  name = name.to_s.split('.')
  name.map! { |n| quote_name_part(n) } # "[#{name}]"
  name.join('.')
end

#quote_database_name(name) ⇒ Object 



305
306
307
 
# File 'lib/arjdbc/mssql/adapter.rb', line 305

def quote_database_name(name)
  quote_name_part(name.to_s)
end

#quote_default_value(value, column) ⇒ Object

Does not quote function default values for UUID columns



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

def quote_default_value(value, column)
  if column.type == :uuid && value =~ /\(\)/
    value
  else
    quote(value)
  end
end

#quote_table_name(name) ⇒ Object 



295
296
297
 
# File 'lib/arjdbc/mssql/adapter.rb', line 295

def quote_table_name(name)
  quote_column_name(name)
end

#quoted_date(value) ⇒ Object 



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

def quoted_date(value)
  if value.respond_to?(:usec)
    "#{super}.#{sprintf("%03d", value.usec / 1000)}"
  else
    super
  end
end

#release_savepoint(name = current_savepoint_name(false)) ⇒ Object 



720
721
722
723
724
725
726
 
# File 'lib/arjdbc/mssql/adapter.rb', line 720

def release_savepoint(name = current_savepoint_name(false))
  if @connection.jtds_driver?
    @connection.release_savepoint(name)
  else # MS invented it's "own" way
    @connection.rollback_savepoint(name)
  end
end

#remove_check_constraints(table_name, column_name) ⇒ Object 



574
575
576
577
578
579
580
581
582
 
# File 'lib/arjdbc/mssql/adapter.rb', line 574

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 



544
545
546
 
# File 'lib/arjdbc/mssql/adapter.rb', line 544

def remove_column(table_name, column_name, type = nil, options = {})
  remove_columns(table_name, column_name)
end

#remove_columns(table_name, *column_names) ⇒ Object

Raises:

  • (ArgumentError) 


523
524
525
526
527
528
529
530
531
 
# File 'lib/arjdbc/mssql/adapter.rb', line 523

def remove_columns(table_name, *column_names)
  raise ArgumentError.new("You must specify at least one column name. Example: remove_column(:people, :first_name)") if column_names.empty?
  # remove_columns(:posts, :foo, :bar) old syntax : remove_columns(:posts, [:foo, :bar])
  clear_cached_table(table_name)

  column_names = column_names.flatten
  return do_remove_column(table_name, column_names.first) if column_names.size == 1
  column_names.each { |column_name| do_remove_column(table_name, column_name) }
end

#remove_default_constraint(table_name, column_name) ⇒ Object 



554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
 
# File 'lib/arjdbc/mssql/adapter.rb', line 554

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 



591
592
593
 
# File 'lib/arjdbc/mssql/adapter.rb', line 591

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

#remove_indexes(table_name, column_name) ⇒ Object 



584
585
586
587
588
589
 
# File 'lib/arjdbc/mssql/adapter.rb', line 584

def remove_indexes(table_name, column_name)
  indexes = self.indexes(table_name)
  indexes.select{ |index| index.columns.include?(column_name.to_s) }.each do |index|
    remove_index(table_name, { :name => index.name })
  end
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



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

def rename_column(table_name, column_name, new_column_name)
  clear_cached_table(table_name)
  execute "EXEC sp_rename '#{table_name}.#{column_name}', '#{new_column_name}', 'COLUMN'"
end

#rename_table(table_name, new_table_name) ⇒ Object 



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

def rename_table(table_name, new_table_name)
  clear_cached_table(table_name)
  execute "EXEC sp_rename '#{table_name}', '#{new_table_name}'"
end

#reset_column_information ⇒ Object 



621
622
623
 
# File 'lib/arjdbc/mssql/adapter.rb', line 621

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

#set_identity_insert(table_name, enable = true) ⇒ Object 



635
636
637
638
639
640
 
# File 'lib/arjdbc/mssql/adapter.rb', line 635

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 



126
127
128
129
130
131
132
 
# File 'lib/arjdbc/mssql/adapter.rb', line 126

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+|Microsoft SQL Azure.+\n.+)(\d{4})/, 2]
  end
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


336
 
# File 'lib/arjdbc/mssql/adapter.rb', line 336

def supports_ddl_transactions?; true end

#supports_views? ⇒ Boolean

Returns:

  • (Boolean) 


339
 
# File 'lib/arjdbc/mssql/adapter.rb', line 339

def supports_views?; true end

#tables(schema = current_schema) ⇒ Object 



341
342
343
 
# File 'lib/arjdbc/mssql/adapter.rb', line 341

def tables(schema = current_schema)
  @connection.tables(nil, schema)
end

#truncate(table_name, name = nil) ⇒ Object 



664
665
666
 
# File 'lib/arjdbc/mssql/adapter.rb', line 664

def truncate(table_name, name = nil)
  execute "TRUNCATE TABLE #{quote_table_name(table_name)}", name
end

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



188
189
190
191
192
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
 
# File 'lib/arjdbc/mssql/adapter.rb', line 188

def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  type_s = type.to_s
  # 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_s == 'string' && limit == 1073741823 && ! sqlserver_2000?
    'NVARCHAR(MAX)'
  elsif NO_LIMIT_TYPES.include?(type_s)
    super(type)
  elsif type_s == 'integer' || type_s == 'int'
    if limit.nil? || limit == 4
      'int'
    elsif limit == 2
      'smallint'
    elsif limit == 1
      'tinyint'
    else
      'bigint'
    end
  elsif type_s == 'uniqueidentifier'
    type_s
  else
    super
  end
end

#update_lob_value?(value, column = nil) ⇒ Boolean

Returns:

  • (Boolean)

See Also:



92
93
94
 
# File 'lib/arjdbc/mssql/adapter.rb', line 92

def update_lob_value?(value, column = nil)
  MSSQL.update_lob_values? && ! prepared_statements? # && value
end

#use_database(database = nil) ⇒ Object 



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

def use_database(database = nil)
  database ||= config[:database]
  execute "USE #{quote_database_name(database)}" unless database.blank?
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



628
629
630
631
632
633
 
# File 'lib/arjdbc/mssql/adapter.rb', line 628

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