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 



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

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 



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

def change_column_default(table_name, column_name, default)
  schema_cache.clear_table_cache!(table_name)
  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 



174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 174

def change_column_null(table_name, column_name, allow_null, default = nil)
  table_id = SQLServer::Utils.extract_identifiers(table_name)
  column_id = SQLServer::Utils.extract_identifiers(column_name)
  column = detect_column_for! table_name, column_name
  if !allow_null.nil? && allow_null == false && !default.nil?
    do_execute("UPDATE #{table_id} SET #{column_id}=#{quote(default)} WHERE #{column_id} IS NULL")
  end
  sql = "ALTER TABLE #{table_id} ALTER COLUMN #{column_id} #{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 



50
51
52
53
54
55
56
57
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 50

def columns(table_name, _name = nil)
  return [] if table_name.blank?
  column_definitions(table_name).map do |ci|
    sqlserver_options = ci.slice :ordinal_position, :is_primary, :is_identity, :default_function, :table_name, :collation
    cast_type = lookup_cast_type(ci[:type])
    new_column ci[:name], ci[:default_value], cast_type, ci[:type], ci[:null], sqlserver_options
  end
end

#columns_for_distinct(columns, orders) ⇒ Object 



165
166
167
168
169
170
171
172
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 165

def columns_for_distinct(columns, orders)
  order_columns = orders.reject(&:blank?).map{ |s|
      s = s.to_sql unless s.is_a?(String)
      s.gsub(/\s+(?:ASC|DESC)\b/i, '')
       .gsub(/\s+NULLS\s+(?:FIRST|LAST)\b/i, '')
    }.reject(&:blank?).map.with_index { |column, i| "#{column} AS alias_#{i}" }
  [super, *order_columns].join(', ')
end

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



24
25
26
27
28
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 24

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

#data_sources ⇒ Object 



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

def data_sources
  tables + views
end

#extract_foreign_key_action(action, fk_name) ⇒ Object 



142
143
144
145
146
147
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 142

def extract_foreign_key_action(action, fk_name)
  case select_value("SELECT #{action}_referential_action_desc FROM sys.foreign_keys WHERE name = '#{fk_name}'")
  when 'CASCADE' then :cascade
  when 'SET_NULL' then :nullify
  end
end

#foreign_keys(table_name) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 125

def foreign_keys(table_name)
  identifier = SQLServer::Utils.extract_identifiers(table_name)
  fk_info = execute_procedure :sp_fkeys, nil, identifier.schema, nil, identifier.object, identifier.schema
  fk_info.map do |row|
    from_table = identifier.object
    to_table = row['PKTABLE_NAME']
    options = {
      name: row['FK_NAME'],
      column: row['FKCOLUMN_NAME'],
      primary_key: row['PKCOLUMN_NAME'],
      on_update: extract_foreign_key_action('update', row['FK_NAME']),
      on_delete: extract_foreign_key_action('delete', row['FK_NAME'])
    }
    ForeignKeyDefinition.new from_table, to_table, options
  end
end

#indexes(table_name, name = nil) ⇒ Object 



30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 30

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 



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

def native_database_types
  @native_database_types ||= initialize_native_database_types.freeze
end

#new_column(name, default, cast_type, sql_type = nil, null = true, sqlserver_options = {}) ⇒ Object 



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

def new_column(name, default, cast_type, sql_type = nil, null = true, sqlserver_options={})
  SQLServerColumn.new name, default, cast_type, sql_type, null, sqlserver_options
end

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

Raises:

  • (ArgumentError) 


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

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 



121
122
123
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 121

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
113
 
# 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
  identifier = SQLServer::Utils.extract_identifiers("#{table_name}.#{column_name}")
  execute_procedure :sp_rename, identifier.quoted, 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

Raises:

  • (ArgumentError) 


115
116
117
118
119
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 115

def rename_index(table_name, old_name, new_name)
  raise ArgumentError, "Index name '#{new_name}' on table '#{table_name}' is too long; the limit is #{allowed_index_name_length} characters" if new_name.length > allowed_index_name_length
  identifier = SQLServer::Utils.extract_identifiers("#{table_name}.#{old_name}")
  execute_procedure :sp_rename, identifier.quoted, new_name, 'INDEX'
end

#rename_table(table_name, new_name) ⇒ Object 



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

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) 


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

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

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



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

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 



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 149

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 ======================================== #



188
189
190
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 188

def views
  tables('VIEW')
end