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 



131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 131

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_expression(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, limit: options[:limit], precision: options[:precision], scale: options[:scale])}"
  sql_commands.last << ' NOT NULL' if !options[:null].nil? && options[:null] == false
  if options.key?(:default) && default_constraint_name(table_name, column_name).present?
    change_column_default(table_name, column_name, options[:default])
  elsif 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_expression(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) }
  clear_cache!
end

#change_column_default(table_name, column_name, default_or_changes) ⇒ Object 



156
157
158
159
160
161
162
163
164
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 156

def change_column_default(table_name, column_name, default_or_changes)
  clear_cache!
  column = column_for(table_name, column_name)
  return unless column
  remove_default_constraint(table_name, column_name)
  default = extract_new_default_value(default_or_changes)
  do_execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{default_constraint_name(table_name, column_name)} DEFAULT #{quote_default_expression(default, column)} FOR #{quote_column_name(column_name)}"
  clear_cache!
end

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



247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 247

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, limit: column.limit, precision: column.precision, scale: column.scale}"
  sql << ' NOT NULL' if !allow_null.nil? && allow_null == false
  do_execute sql
end

#columns(table_name) ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 55

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

#columns_for_distinct(columns, orders) ⇒ Object 



234
235
236
237
238
239
240
241
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 234

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, comment: nil, **options) ⇒ Object 



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

def create_table(table_name, comment: nil, **options)
  res = super
  clear_cache!
  res
end

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



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 16

def drop_table(table_name, options = {})
  # Mimic CASCADE option as best we can.
  if options[:force] == :cascade
    execute_procedure(:sp_fkeys, pktable_name: table_name).each do |fkdata|
      fktable = fkdata['FKTABLE_NAME']
      fkcolmn = fkdata['FKCOLUMN_NAME']
      pktable = fkdata['PKTABLE_NAME']
      pkcolmn = fkdata['PKCOLUMN_NAME']
      remove_foreign_key fktable, name: fkdata['FK_NAME']
      do_execute "DELETE FROM #{quote_table_name(fktable)} WHERE #{quote_column_name(fkcolmn)} IN ( SELECT #{quote_column_name(pkcolmn)} FROM #{quote_table_name(pktable)} )"
    end
  end
  if options[:if_exists] && @version_year < 2016
    execute "IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = #{quote(table_name)}) DROP TABLE #{quote_table_name(table_name)}"
  else
    super
  end
end

#extract_foreign_key_action(action, fk_name) ⇒ Object 



201
202
203
204
205
206
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 201

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 



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 184

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 



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 35

def indexes(table_name, name = nil)
  data = (select("EXEC sp_helpindex #{quote(table_name)}", name) || []) rescue [] # JDBC returns nil instead of an array or erring out for no results
  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, sql_type_metadata, null, table_name, default_function = nil, collation = nil, comment = nil, sqlserver_options = {}) ⇒ Object 



74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 74

def new_column(name, default, sql_type_metadata, null, table_name, default_function = nil, collation = nil, comment = nil, sqlserver_options = {})
  SQLServerColumn.new(
    name,
    default,
    sql_type_metadata,
    null, table_name,
    default_function,
    collation,
    comment,
    sqlserver_options
  )
end

#primary_keys(table_name) ⇒ Object 



87
88
89
90
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 87

def primary_keys(table_name)
  primaries = primary_keys_select(table_name)
  primaries.present? ? primaries : identity_columns(table_name).map(&:name)
end

#primary_keys_select(table_name) ⇒ Object 



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 92

def primary_keys_select(table_name)
  identifier = database_prefix_identifier(table_name)
  database = identifier.fully_qualified_database_quoted
  sql = %{
    SELECT KCU.COLUMN_NAME AS [name]
    FROM #{database}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    LEFT OUTER JOIN #{database}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
      ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
      AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
      AND KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
      AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
      AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY'
    WHERE KCU.TABLE_NAME = #{prepared_statements ? COLUMN_DEFINITION_BIND_STRING_0 : quote(identifier.object)}
    AND KCU.TABLE_SCHEMA = #{identifier.schema.blank? ? 'schema_name()' : (prepared_statements ? COLUMN_DEFINITION_BIND_STRING_1 : quote(identifier.schema))}
    AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY'
    ORDER BY KCU.ORDINAL_POSITION ASC
  }.gsub(/[[:space:]]/, ' ')
  binds = []
  if prepared_statements
    nv128 = SQLServer::Type::UnicodeVarchar.new limit: 128
    binds << Relation::QueryAttribute.new('TABLE_NAME', identifier.object, nv128)
    binds << Relation::QueryAttribute.new('TABLE_SCHEMA', identifier.schema, nv128) unless identifier.schema.blank?
  end
  sp_executesql(sql, 'SCHEMA', binds).map { |r| r['name'] }
end

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

Raises:

  • (ArgumentError) 


123
124
125
126
127
128
129
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 123

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 



180
181
182
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 180

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 



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

def rename_column(table_name, column_name, new_column_name)
  clear_cache!
  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)
  clear_cache!
end

#rename_index(table_name, old_name, new_name) ⇒ Object

Raises:

  • (ArgumentError) 


174
175
176
177
178
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 174

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 



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

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

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



208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 208

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
  when 'datetime2'
    column_type_sql = super
    if precision
      if (0..7) === precision
        column_type_sql << "(#{precision})"
      else
        raise(ActiveRecordError, "The dattime2 type has precision of #{precision}. The allowed range of precision is from 0 to 7")
      end
    end
    column_type_sql
  else
    super
  end
end

#update_table_definition(table_name, base) ⇒ Object 



243
244
245
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 243

def update_table_definition(table_name, base)
  SQLServer::Table.new(table_name, base)
end