Module: ActiveRecord::ConnectionAdapters::Sqlserver::SchemaStatements

Included in:
ActiveRecord::ConnectionAdapters::SQLServerAdapter
Defined in:
lib/active_record/connection_adapters/sqlserver/schema_statements.rb

Instance Method Summary collapse

Instance Method Details

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



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 75

def change_column(table_name, column_name, type, options = {})
  sql_commands = []
  indexes = []
  column_object = schema_cache.columns(table_name).find { |c| c.name.to_s == column_name.to_s }

  if options_include_default?(options) || (column_object && column_object.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
  sql_commands << "UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote_default_value(options[:default], column_object)} WHERE #{quote_column_name(column_name)} IS NULL" if !options[:null].nil? && options[:null] == false && !options[:default].nil?
  sql_commands << "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_commands[-1] << ' NOT NULL' if !options[:null].nil? && options[:null] == false
  if options_include_default?(options)
    sql_commands << "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{default_constraint_name(table_name, column_name)} DEFAULT #{quote_default_value(options[:default], column_object)} FOR #{quote_column_name(column_name)}"
  end

  # Add any removed indexes back
  indexes.each do |index|
    sql_commands << "CREATE INDEX #{quote_table_name(index.name)} ON #{quote_table_name(table_name)} (#{index.columns.map { |c| quote_column_name(c) }.join(', ')})"
  end
  sql_commands.each { |c| do_execute(c) }
end

#change_column_default(table_name, column_name, default) ⇒ Object 



99
100
101
102
103
104
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 99

def change_column_default(table_name, column_name, default)
  remove_default_constraint(table_name, column_name)
  column_object = schema_cache.columns(table_name).find { |c| c.name.to_s == column_name.to_s }
  do_execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{default_constraint_name(table_name, column_name)} DEFAULT #{quote_default_value(default, column_object)} FOR #{quote_column_name(column_name)}"
  schema_cache.clear_table_cache!(table_name)
end

#change_column_null(table_name, column_name, allow_null, default = nil) ⇒ Object 



138
139
140
141
142
143
144
145
146
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 138

def change_column_null(table_name, column_name, allow_null, default = nil)
  column = detect_column_for! table_name, column_name
  if !allow_null.nil? && allow_null == false && !default.nil?
    do_execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql column.type, column.limit, column.precision, column.scale}"
  sql << ' NOT NULL' if !allow_null.nil? && allow_null == false
  do_execute sql
end

#columns(table_name, _name = nil) ⇒ Object 



45
46
47
48
49
50
51
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 45

def columns(table_name, _name = nil)
  return [] if table_name.blank?
  column_definitions(table_name).map do |ci|
    sqlserver_options = ci.except(:name, :default_value, :type, :null).merge(database_year: database_year)
    SQLServerColumn.new ci[:name], ci[:default_value], ci[:type], ci[:null], sqlserver_options
  end
end

#columns_for_distinct(columns, _orders) ⇒ Object

like postgres, sqlserver requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column. Unfortunately, sqlserver does not support DISTINCT ON () like Posgres, or FIRST_VALUE() like Oracle (at least before SQL Server 2012). Because of these facts, we don’t actually add any extra columns for distinct, but instead have to create a subquery with ROW_NUMBER() and DENSE_RANK() in our monkey-patches to Arel.



58
59
60
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 58

def columns_for_distinct(columns, _orders) #:nodoc:
  columns
end

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



19
20
21
22
23
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 19

def create_table(table_name, options = {})
  res = super
  schema_cache.clear_table_cache!(table_name)
  res
end

#indexes(table_name, name = nil) ⇒ Object 



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 25

def indexes(table_name, name = nil)
  data = select("EXEC sp_helpindex #{quote(table_name)}", name) rescue []
  data.reduce([]) do |indexes, index|
    index = index.with_indifferent_access
    if index[:index_description] =~ /primary key/
      indexes
    else
      name    = index[:index_name]
      unique  = index[:index_description] =~ /unique/
      where   = select_value("SELECT [filter_definition] FROM sys.indexes WHERE name = #{quote(name)}")
      columns = index[:index_keys].split(',').map do |column|
        column.strip!
        column.gsub! '(-)', '' if column.ends_with?('(-)')
        column
      end
      indexes << IndexDefinition.new(table_name, name, unique, columns, nil, nil, where)
    end
  end
end

#native_database_types ⇒ Object 



5
6
7
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 5

def native_database_types
  @native_database_types ||= initialize_native_database_types.freeze
end

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

Raises:

  • (ArgumentError) 


67
68
69
70
71
72
73
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 67

def remove_column(table_name, column_name, type = nil, options = {})
  raise ArgumentError.new('You must specify at least one column name.  Example: remove_column(:people, :first_name)') if column_name.is_a? Array
  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  remove_indexes(table_name, column_name)
  do_execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
end

#remove_index!(table_name, index_name) ⇒ Object 



118
119
120
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 118

def remove_index!(table_name, index_name)
  do_execute "DROP INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



106
107
108
109
110
111
112
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 106

def rename_column(table_name, column_name, new_column_name)
  schema_cache.clear_table_cache!(table_name)
  detect_column_for! table_name, column_name
  do_execute "EXEC sp_rename '#{table_name}.#{column_name}', '#{new_column_name}', 'COLUMN'"
  rename_column_indexes(table_name, column_name, new_column_name)
  schema_cache.clear_table_cache!(table_name)
end

#rename_index(table_name, old_name, new_name) ⇒ Object 



114
115
116
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 114

def rename_index(table_name, old_name, new_name)
  execute "EXEC sp_rename N'#{table_name}.#{old_name}', N'#{new_name}', N'INDEX'"
end

#rename_table(table_name, new_name) ⇒ Object 



62
63
64
65
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 62

def rename_table(table_name, new_name)
  do_execute "EXEC sp_rename '#{table_name}', '#{new_name}'"
  rename_table_indexes(table_name, new_name)
end

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


13
14
15
16
17
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 13

def table_exists?(table_name)
  return false if table_name.blank?
  unquoted_table_name = Utils.unqualify_table_name(table_name)
  super || tables.include?(unquoted_table_name) || views.include?(unquoted_table_name)
end

#tables(table_type = 'BASE TABLE') ⇒ Object 



9
10
11
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 9

def tables(table_type = 'BASE TABLE')
  select_values "SELECT #{lowercase_schema_reflection_sql('TABLE_NAME')} FROM INFORMATION_SCHEMA.TABLES #{"WHERE TABLE_TYPE = '#{table_type}'" if table_type} ORDER BY TABLE_NAME", 'SCHEMA'
end

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



122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 122

def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  type_limitable = %w(string integer float char nchar varchar nvarchar).include?(type.to_s)
  limit = nil unless type_limitable
  case type.to_s
  when 'integer'
    case limit
    when 1..2       then  'smallint'
    when 3..4, nil  then  'integer'
    when 5..8       then  'bigint'
    else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
    end
  else
    super
  end
end

#views ⇒ Object

SQLServer Specific ======================================== #



150
151
152
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 150

def views
  tables('VIEW')
end