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 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 72

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 



94
95
96
97
98
99
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 94

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 



169
170
171
172
173
174
175
176
177
178
179
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 169

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 



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

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 



160
161
162
163
164
165
166
167
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 160

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 



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

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

#extract_foreign_key_action(action, fk_name) ⇒ Object 



137
138
139
140
141
142
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 137

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 



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

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 



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

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 



55
56
57
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 55

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) 


64
65
66
67
68
69
70
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 64

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 



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

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 



101
102
103
104
105
106
107
108
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 101

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) 


110
111
112
113
114
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 110

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 



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

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) 


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

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 



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

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 



144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 144

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



183
184
185
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 183

def views
  tables('VIEW')
end