module ArJdbc::MSSQL

Constants

ADAPTER_NAME
BLOB_VALUE_MARKER

@see quote @private

EMPTY_ARRAY

@private

NATIVE_DATABASE_TYPES
NO_LIMIT_TYPES

@private these cannot specify a limit

SKIP_COLUMNS_TABLE_NAMES_RE

@private

SmallIntegerType

@private

StringType

@private

Public Class Methods

arel2_visitors(config) click to toggle source

@deprecated no longer used @see ActiveRecord::ConnectionAdapters::JdbcAdapter#arel2_visitors

# 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) click to toggle source

@see ActiveRecord::ConnectionAdapters::Jdbc::ArelSupport

# 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() click to toggle source

@see ActiveRecord::ConnectionAdapters::JdbcColumn#column_types

# 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() click to toggle source

Operator for sorting strings in SQLServer, setup as :

ArJdbc::MSSQL.cs_equality_operator = 'COLLATE Latin1_General_CS_AS_WS'
# File lib/arjdbc/mssql/adapter.rb, line 82
def self.cs_equality_operator; @@cs_equality_operator; end
cs_equality_operator=(operator) click to toggle source

@see cs_equality_operator

# File lib/arjdbc/mssql/adapter.rb, line 84
def self.cs_equality_operator=(operator); @@cs_equality_operator = operator; end
extended(adapter) click to toggle source

@private

# File lib/arjdbc/mssql/adapter.rb, line 44
def self.extended(adapter)
  initialize!

  version = adapter.config[:sqlserver_version] ||= adapter.sqlserver_version
  adapter.send(:setup_limit_offset!, version)
end
initialize!() click to toggle source

@private

# File lib/arjdbc/mssql/adapter.rb, line 55
def self.initialize!
  return if @@_initialized; @@_initialized = true

  require 'arjdbc/util/serialized_attributes'
  Util::SerializedAttributes.setup /image/i, 'after_save_with_mssql_lob'
end
jdbc_connection_class() click to toggle source

@see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_connection_class

# File lib/arjdbc/mssql/adapter.rb, line 97
def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::MSSQLJdbcConnection
end
update_lob_values=(update) click to toggle source

@see update_lob_values?

# File lib/arjdbc/mssql/adapter.rb, line 73
def self.update_lob_values=(update); @@update_lob_values = update; end
update_lob_values?() click to toggle source

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

ArJdbc::MSSQL.update_lob_values = false

@note This only applies when prepared statements are not used.

# File lib/arjdbc/mssql/adapter.rb, line 71
def self.update_lob_values?; @@update_lob_values; end

Public Instance Methods

adapter_name() click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 320
def adapter_name
  ADAPTER_NAME
end
add_column(table_name, column_name, type, options = {}) click to toggle source

Adds a new column to the named table. @override

# 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 = {}) click to toggle source

@override

# 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) click to toggle source
# 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 = {}) click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 384
def charset
  select_value "SELECT SERVERPROPERTY('SqlCharSetName')"
end
clear_cache!() click to toggle source
Calls superclass method
# File lib/arjdbc/mssql/types.rb, line 79
def clear_cache!
  super
  reload_type_map
end
clear_cached_table(table_name) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 617
def clear_cached_table(table_name)
  ( @table_columns ||= {} ).delete(table_name.to_s)
end
collation() click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 388
def collation
  select_value "SELECT SERVERPROPERTY('Collation')"
end
columns(table_name, name = nil, default = EMPTY_ARRAY) click to toggle source
Calls superclass method
# 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) click to toggle source
# 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() click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 122
def configure_connection
  use_database # config[:database]
end
create_database(name, options = {}) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 424
def create_database(name, options = {})
  execute "CREATE DATABASE #{quote_database_name(name)}"
end
current_database() click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 392
def current_database
  select_value 'SELECT DB_NAME()'
end
current_schema()
Alias for: default_schema
current_user() click to toggle source

`SELECT CURRENT_USER`

# 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) click to toggle source
# 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() click to toggle source

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

# 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
Also aliased as: current_schema
determine_order_clause(sql) click to toggle source

@private @see ArJdbc::MSSQL::LimitHelpers

# File lib/arjdbc/mssql/adapter.rb, line 651
def determine_order_clause(sql)
  return $1 if sql =~ /ORDER BY (.*)$/i
  columns = self.columns(table_name = get_table_name(sql))
  primary_column = columns.find { |column| column.primary? || column.identity? }
  unless primary_column # look for an id column and return it,
    # without changing case, to cover DBs with a case-sensitive collation :
    primary_column = columns.find { |column| column.name =~ /^id$/i }
    raise "no columns for table: #{table_name}" if columns.empty?
  end
  # NOTE: if still no PK column simply get something for ORDER BY ...
  "#{quote_table_name(table_name)}.#{quote_column_name((primary_column || columns.first).name)}"
end
disable_referential_integrity() { || ... } click to toggle source
# 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) click to toggle source

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.

# File lib/arjdbc/mssql/adapter.rb, line 457
def distinct(columns, order_by)
  "DISTINCT #{columns_for_distinct(columns, order_by)}"
end
drop_database(name) click to toggle source
# 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) { |row| ... } click to toggle source

Support for executing a stored procedure.

# 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
Also aliased as: execute_procedure
exec_query(sql, name = 'SQL', binds = []) click to toggle source

@override

# 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) click to toggle source

@override

# 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
execute_procedure(proc_name, *variables)
Alias for: exec_proc
initialize_type_map(m) click to toggle source
# 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() click to toggle source

@see ActiveRecord::ConnectionAdapters::JdbcAdapter#jdbc_column_class

# File lib/arjdbc/mssql/adapter.rb, line 102
def jdbc_column_class; ::ActiveRecord::ConnectionAdapters::MSSQLColumn end
modify_types(types) click to toggle source
# 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() click to toggle source
Calls superclass method
# 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) click to toggle source

@override

Calls superclass method
# 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) click to toggle source
# 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) click to toggle source
# 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) click to toggle source

Does not quote function default values for UUID columns

# 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) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 295
def quote_table_name(name)
  quote_column_name(name)
end
quoted_date(value) click to toggle source

@override

Calls superclass method
# 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
quoted_datetime(value) click to toggle source

@deprecated no longer used @private

# File lib/arjdbc/mssql/adapter.rb, line 279
def quoted_datetime(value)
  quoted_date(value)
end
quoted_full_iso8601(value) click to toggle source

@deprecated no longer used @private

# File lib/arjdbc/mssql/adapter.rb, line 285
def quoted_full_iso8601(value)
  if value.acts_like?(:time)
    value.is_a?(Date) ?
      get_time(value).to_time.xmlschema.to(18) :
        get_time(value).iso8601(7).to(22)
  else
    quoted_date(value)
  end
end
quoted_time(value) click to toggle source

@private

# File lib/arjdbc/mssql/adapter.rb, line 267
def quoted_time(value)
  if value.acts_like?(:time)
    tz_value = get_time(value)
    usec = value.respond_to?(:usec) ? ( value.usec / 1000 ) : 0
    sprintf("%02d:%02d:%02d.%03d", tz_value.hour, tz_value.min, tz_value.sec, usec)
  else
    quoted_date(value)
  end
end
recreate_database(name, options = {}) click to toggle source

@private

# File lib/arjdbc/mssql/adapter.rb, line 402
def recreate_database(name, options = {})
  drop_database(name)
  create_database(name, options)
end
recreate_database!(database = nil) click to toggle source

@private

# File lib/arjdbc/mssql/adapter.rb, line 408
def recreate_database!(database = nil)
  current_db = current_database
  database ||= current_db
  use_database('master') if this_db = ( database.to_s == current_db )
  drop_database(database)
  create_database(database)
ensure
  use_database(current_db) if this_db
end
release_savepoint(name = current_savepoint_name(false)) click to toggle source

@override

# 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) click to toggle source
# 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_name, type = nil, options = {}) click to toggle source

@override

# 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) click to toggle source
# 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) click to toggle source
# 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 = {}) click to toggle source
# 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) click to toggle source
# 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) click to toggle source

@override

# 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) click to toggle source

@override

# 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() click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# 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?() click to toggle source

@override

# File lib/arjdbc/mssql/adapter.rb, line 336
def supports_ddl_transactions?; true end
supports_views?() click to toggle source

@override

# File lib/arjdbc/mssql/adapter.rb, line 339
def supports_views?; true end
tables(schema = current_schema) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 341
def tables(schema = current_schema)
  @connection.tables(nil, schema)
end
truncate(table_name, name = nil) click to toggle source
# 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) click to toggle source
Calls superclass method
# 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) click to toggle source

@see update_lob_values? @see ArJdbc::Util::SerializedAttributes#update_lob_columns

# 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) click to toggle source
# 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) { || ... } click to toggle source

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

# 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

Private Instance Methods

_execute(sql, name = nil) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 730
def _execute(sql, name = nil)
  # Match the start of the SQL to determine appropriate behavior.
  # Be aware of multi-line SQL which might begin with 'create stored_proc'
  # and contain 'insert into ...' lines.
  # NOTE: ignoring comment blocks prior to the first statement ?!
  if self.class.insert?(sql)
    if id_insert_table_name = identity_insert_table_name(sql)
      with_identity_insert_enabled(id_insert_table_name) do
        @connection.execute_insert(sql)
      end
    else
      @connection.execute_insert(sql)
    end
  elsif self.class.select?(sql)
    @connection.execute_query_raw repair_special_columns(sql)
  else # create | exec
    @connection.execute_update(sql)
  end
end
do_remove_column(table_name, column_name) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 533
def do_remove_column(table_name, column_name)
  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  remove_indexes(table_name, column_name) unless sqlserver_2000?
  execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
end
identity_column_name(table_name) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 759
def identity_column_name(table_name)
  for column in columns(table_name)
    return column.name if column.identity
  end
  nil
end
identity_insert_table_name(sql) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 750
def identity_insert_table_name(sql)
  table_name = get_table_name(sql)
  id_column = identity_column_name(table_name)
  if id_column && 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
repair_special_columns(sql) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 766
def repair_special_columns(sql)
  qualified_table_name = get_table_name(sql, true)
  if special_columns = special_column_names(qualified_table_name)
    return sql if special_columns.empty?
    special_columns = special_columns.sort { |n1, n2| n2.size <=> n1.size }
    for column in special_columns
      sql.gsub!(/\s?\[?#{column}\]?\s?=\s?/, " [#{column}] LIKE ")
      sql.gsub!(/ORDER BY \[?#{column}([^\.\w]|$)\]?/i, '') # NOTE: a bit stupid
    end
  end
  sql
end
special_column_names(qualified_table_name) click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 779
def special_column_names(qualified_table_name)
  columns = self.columns(qualified_table_name, nil, nil)
  return columns if ! columns || columns.empty?
  special = []
  columns.each { |column| special << column.name if column.special? }
  special
end
sqlserver_2000?() click to toggle source
# File lib/arjdbc/mssql/adapter.rb, line 787
def sqlserver_2000?
  sqlserver_version <= '2000'
end