Module: ActiveRecord::ConnectionAdapters::MSSQL::SchemaStatements

Included in:
ActiveRecord::ConnectionAdapters::MSSQLAdapter
Defined in:
lib/arjdbc/mssql/schema_statements.rb

Overview

:nodoc:

Constant Summary collapse

NATIVE_DATABASE_TYPES = 
{
  # Logical Rails types to SQL Server types
  primary_key:    'bigint NOT NULL IDENTITY(1,1) PRIMARY KEY',
  integer:        { name: 'int', limit: 4 },
  boolean:        { name: 'bit' },
  decimal:        { name: 'decimal' },
  float:          { name: 'float' },
  date:           { name: 'date' },
  time:           { name: 'time' },
  datetime:       { name: 'datetime2' },
  string:         { name: 'nvarchar', limit: 4000 },
  text:           { name: 'nvarchar(max)' },
  binary:         { name: 'varbinary(max)' },
  json:           { name: 'nvarchar(max)' },
  # Other types or SQL Server specific
  bigint:         { name: 'bigint' },
  smalldatetime:  { name: 'smalldatetime' },
  datetime_basic: { name: 'datetime' },
  timestamp:      { name: 'datetime' },
  real:           { name: 'real' },
  money:          { name: 'money' },
  smallmoney:     { name: 'smallmoney' },
  char:           { name: 'char' },
  nchar:          { name: 'nchar' },
  varchar:        { name: 'varchar', limit: 8000 },
  varchar_max:    { name: 'varchar(max)' },
  uuid:           { name: 'uniqueidentifier' },
  binary_basic:   { name: 'binary' },
  varbinary:      { name: 'varbinary', limit: 8000 },
  # Deprecated SQL Server types
  image:          { name: 'image' },
  ntext:          { name: 'ntext' },
  text_basic:     { name: 'text' }
}.freeze

Instance Method Summary collapse

Instance Method Details

#add_column(table_name, column_name, type, **options) ⇒ Object 



308
309
310
311
312
313
314
315
316
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 308

def add_column(table_name, column_name, type, **options)
  if supports_datetime_with_precision?
    if type == :datetime && !options.key?(:precision)
      options[:precision] = 7
    end
  end

  super
end

#add_timestamps(table_name, **options) ⇒ Object 



297
298
299
300
301
302
303
304
305
306
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 297

def add_timestamps(table_name, **options)
  if !options.key?(:precision) && supports_datetime_with_precision?
    options[:precision] = 7
  end

  # In SQL Server only the first column added should have the `ADD` keyword.
  fragments = add_timestamps_for_alter(table_name, **options)
  fragments[1..].each { |fragment| fragment.sub!('ADD ', '') }
  execute("ALTER TABLE #{quote_table_name(table_name)} #{fragments.join(', ')}")
end

#build_change_column_default_definition(table_name, column_name, default_or_changes) ⇒ Object

:nodoc:



89
90
91
92
93
94
95
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 89

def build_change_column_default_definition(table_name, column_name, default_or_changes) # :nodoc:
  column = column_for(table_name, column_name)
  return unless column

  default = extract_new_default_value(default_or_changes)
  ChangeColumnDefaultDefinition.new(column, default)
end

#build_change_column_definition(table_name, column_name, type, **options) ⇒ Object

:nodoc:



83
84
85
86
87
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 83

def build_change_column_definition(table_name, column_name, type, **options) # :nodoc:
  td = create_table_definition(table_name)
  cd = td.new_column_definition(column_name, type, **options)
  ChangeColumnDefinition.new(cd, column_name)
end

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



346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 346

def change_column(table_name, column_name, type, options = {})
  column = columns(table_name).find { |c| c.name.to_s == column_name.to_s }

  indexes = []
  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_expression(options[:default], column)} " \
      "WHERE #{quote_column_name(column_name)} IS NULL"
    )
  end

  change_column_type(table_name, column_name, type, options)

  if options_include_default?(options)
    change_column_default(table_name, column_name, options[:default])
  elsif options.key?(:default) && options[:null] == false
    # Drop default constraint when null option is false
    remove_default_constraint(table_name, column_name)
  end

  # add any removed indexes back
  indexes.each do |index|
    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_or_changes) ⇒ Object 



331
332
333
334
335
336
337
338
339
340
341
342
343
344
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 331

def change_column_default(table_name, column_name, default_or_changes)
  remove_default_constraint(table_name, column_name)

  default = extract_new_default_value(default_or_changes)
  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_expression(default, column)} FOR #{quote_column_name(column_name)}"
    )
    result
  end
end

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



380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 380

def change_column_null(table_name, column_name, null, default = nil)
  column = column_for(table_name, column_name)
  quoted_table = quote_table_name(table_name)
  quoted_column = quote_column_name(column_name)
  quoted_default = quote(default)

  unless null || default.nil?
    execute("UPDATE #{quoted_table} SET #{quoted_column}=#{quoted_default} WHERE #{quoted_column} IS NULL")
  end

  options = { limit: column.limit, precision: column.precision, scale: column.scale }

  sql_alter = [
    "ALTER TABLE #{quoted_table}",
    "ALTER COLUMN #{quoted_column} #{type_to_sql(column.type, **options)}",
    ('NOT NULL' unless null)
  ]

  execute(sql_alter.compact.join(' '))
end

#charset ⇒ Object 



129
130
131
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 129

def charset
  select_value "SELECT SqlCharSetName = CAST(SERVERPROPERTY('SqlCharSetName') AS NVARCHAR(128))"
end

#collation ⇒ Object 



133
134
135
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 133

def collation
  @collation ||= select_value("SELECT Collation = CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128))")
end

#columns_for_distinct(columns, orders) ⇒ Object

SQL Server requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column.



285
286
287
288
289
290
291
292
293
294
295
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 285

def columns_for_distinct(columns, orders) #:nodoc:
  order_columns = orders.reject(&:blank?).map{ |s|
      # Convert Arel node to string
      s = s.to_sql unless s.is_a?(String)
      # Remove any ASC/DESC modifiers
      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}" }

  (order_columns << super).join(', ')
end

#create_database(name, options = {}) ⇒ Object 



154
155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 154

def create_database(name, options = {})
  edition_options = create_db_edition_options(options)

  if options[:collation] && edition_options.present?
    execute "CREATE DATABASE #{quote_database_name(name)} COLLATE #{options[:collation]} (#{edition_options.join(', ')})"
  elsif options[:collation]
    execute "CREATE DATABASE #{quote_database_name(name)} COLLATE #{options[:collation]}"
  elsif edition_options.present?
    execute "CREATE DATABASE #{quote_database_name(name)} (#{edition_options.join(', ')})"
  else
    execute "CREATE DATABASE #{quote_database_name(name)}"
  end
end

#create_schema_dumper(options) ⇒ Object 



318
319
320
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 318

def create_schema_dumper(options)
  MSSQL::SchemaDumper.create(self, options)
end

#current_database ⇒ Object 



137
138
139
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 137

def current_database
  select_value 'SELECT DB_NAME()'
end

#drop_database(name) ⇒ Object 



146
147
148
149
150
151
152
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 146

def drop_database(name)
  current_db = current_database
  use_database('master') if current_db.to_s == name
  # Only SQL Server 2016 onwards:
  # execute "DROP DATABASE IF EXISTS #{quote_database_name(name)}"
  execute "IF EXISTS(SELECT name FROM sys.databases WHERE name='#{name}') DROP DATABASE #{quote_database_name(name)}"
end

#drop_table(table_name, **options) ⇒ Object 



192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 192

def drop_table(table_name, **options)
  # mssql cannot recreate referenced table with force: :cascade
  # https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-2017
  if options[:force] == :cascade
    execute_procedure(:sp_fkeys, pktable_name: table_name).each do |fkdata|
      raw_fktable = fkdata['FKTABLE_NAME']
      remove_foreign_key(raw_fktable, name: fkdata['FK_NAME'])

      fktable = quote_table_name(fkdata['FKTABLE_NAME'])
      fkcolmn = quote_column_name(fkdata['FKCOLUMN_NAME'])

      pktable = quote_table_name(fkdata['PKTABLE_NAME'])
      pkcolmn = quote_column_name(fkdata['PKCOLUMN_NAME'])

      execute("DELETE FROM #{fktable} WHERE #{fkcolmn} IN ( SELECT #{pkcolmn} FROM #{pktable} )")
    end
  end

  if options[:if_exists] && mssql_version.major < '13'
    # this is for sql server 2012 and 2014
    execute "IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = #{quote(table_name)}) DROP TABLE #{quote_table_name(table_name)}"
  else
    # For sql server 2016 onwards
    super
  end
end

#extract_foreign_key_action(action, fk_name) ⇒ Object 



122
123
124
125
126
127
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 122

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 



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 97

def foreign_keys(table_name)
  # valid_raw_connection.foreign_keys(table_name)
  fk_info = execute_procedure(:sp_fkeys, nil, nil, nil, table_name, nil)

  grouped_fk = fk_info.group_by { |row| row["FK_NAME"] }.values.each { |group| group.sort_by! { |row| row["KEY_SEQ"] } }
  grouped_fk.map do |group|
    row = group.first
    options = {
      name: row["FK_NAME"],
      on_update: extract_foreign_key_action("update", row["FK_NAME"]),
      on_delete: extract_foreign_key_action("delete", row["FK_NAME"])
    }

    if group.one?
      options[:column] = row["FKCOLUMN_NAME"]
      options[:primary_key] = row["PKCOLUMN_NAME"]
    else
      options[:column] = group.map { |row| row["FKCOLUMN_NAME"] }
      options[:primary_key] = group.map { |row| row["PKCOLUMN_NAME"] }
    end

    ForeignKeyDefinition.new(table_name, row["PKTABLE_NAME"], options)
  end
end

#indexes(table_name) ⇒ Object

Returns an array of indexes for the given table.



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 48

def indexes(table_name)
  data = select("EXEC sp_helpindex #{quote(table_name)}", "SCHEMA") 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].to_s.match?(/unique/)
      where   = select_value("SELECT [filter_definition] FROM sys.indexes WHERE name = #{quote(name)}")
      orders  = {}
      columns = []

      index[:index_keys].split(',').each do |column|
        column.strip!

        if column.ends_with?('(-)')
          column.gsub! '(-)', ''
          orders[column] = :desc
        end

        columns << column
      end

      indexes << IndexDefinition.new(table_name, name, unique, columns, where: where, orders: orders)
    end
  end
end

#native_database_types ⇒ Object 



43
44
45
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 43

def native_database_types
  NATIVE_DATABASE_TYPES
end

#primary_keys(table_name) ⇒ Object 



79
80
81
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 79

def primary_keys(table_name)
  valid_raw_connection.primary_keys(table_name)
end

#quote_database_name(name) ⇒ Object 



227
228
229
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 227

def quote_database_name(name)
  self.class.mssql_quote_name_part(name.to_s)
end

#recreate_database(name, options = {}) ⇒ Object 



168
169
170
171
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 168

def recreate_database(name, options = {})
  drop_database(name)
  create_database(name, options)
end

#remove_column(table_name, column_name, _type = nil, **options) ⇒ Object 



183
184
185
186
187
188
189
190
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 183

def remove_column(table_name, column_name, _type = nil, **options)
  return if options[:if_exists] == true && !column_exists?(table_name, column_name)

  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  remove_indexes(table_name, column_name)
  execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
end

#remove_columns(table_name, *column_names, type: nil, **options) ⇒ Object 



173
174
175
176
177
178
179
180
181
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 173

def remove_columns(table_name, *column_names, type: nil, **options)
  if column_names.empty?
    raise ArgumentError.new('You must specify at least one column name. Example: remove_columns(:people, :first_name)')
  end

  column_names.each do |column_name|
    remove_column(table_name, column_name, type, **options)
  end
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



322
323
324
325
326
327
328
329
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 322

def rename_column(table_name, column_name, new_column_name)
  # The below line checks if column exists otherwise raise activerecord
  # default exception for this case.
  _column = column_for(table_name, column_name)

  execute "EXEC sp_rename '#{table_name}.#{column_name}', '#{new_column_name}', 'COLUMN'"
  rename_column_indexes(table_name, column_name, new_column_name)
end

#rename_table(table_name, new_name, **options) ⇒ Object 



219
220
221
222
223
224
225
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 219

def rename_table(table_name, new_name, **options)
  validate_table_length!(new_name) unless options[:_uses_legacy_table_name]
  schema_cache.clear_data_source_cache!(table_name.to_s)
  schema_cache.clear_data_source_cache!(new_name.to_s)
  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

Maps logical Rails types to MSSQL-specific data types.



235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 235

def type_to_sql(type, limit: nil, precision: nil, scale: nil, **) # :nodoc:
  # 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.
  #
  # See: http://msdn.microsoft.com/en-us/library/ms186939.aspx
  #
  type = type.to_sym if type
  native = native_database_types[type]

  if type == :string && limit == 1_073_741_823
    'nvarchar(max)'
  elsif NO_LIMIT_TYPES.include?(type)
    super(type)
  elsif i[int integer].include?(type)
    if limit.nil? || limit == 4
      'int'
    elsif limit == 2
      'smallint'
    elsif limit == 1
      'tinyint'
    else
      'bigint'
    end
  elsif type == :uniqueidentifier
    'uniqueidentifier'
  elsif i[datetime time].include?(type)
    precision ||= 7
    column_type_sql = (native.is_a?(Hash) ? native[:name] : native).dup
    if (0..7).include?(precision)
      column_type_sql << "(#{precision})"
    else
      raise(
        ArgumentError,
        "No #{native[:name]} type has precision of #{precision}. The " \
        'allowed range of precision is from 0 to 7, even though the ' \
        'sql type precision is 7 this adapter will persist up to 6 ' \
        'precision only.'
      )
    end
  else
    super
  end
end

#update_table_definition(table_name, base) ⇒ Object

:nodoc:



401
402
403
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 401

def update_table_definition(table_name, base) #:nodoc:
  MSSQL::Table.new(table_name, base)
end

#use_database(database = nil) ⇒ Object 



141
142
143
144
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 141

def use_database(database = nil)
  database ||= config[:database]
  execute "USE #{quote_database_name(database)}" unless database.blank?
end