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 



278
279
280
281
282
283
284
285
286
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 278

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 



267
268
269
270
271
272
273
274
275
276
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 267

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

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



316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 316

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 



301
302
303
304
305
306
307
308
309
310
311
312
313
314
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 301

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 



350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 350

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 



87
88
89
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 87

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

#collation ⇒ Object 



91
92
93
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 91

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.



255
256
257
258
259
260
261
262
263
264
265
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 255

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 



112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 112

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 



288
289
290
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 288

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

#current_database ⇒ Object 



95
96
97
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 95

def current_database
  select_value 'SELECT DB_NAME()'
end

#drop_database(name) ⇒ Object 



104
105
106
107
108
109
110
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 104

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 



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 150

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

#foreign_keys(table_name) ⇒ Object 



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

def foreign_keys(table_name)
  valid_raw_connection.foreign_keys(table_name)
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_column_name(name) ⇒ Object

This overrides the abstract method to be specific to SQL Server.



191
192
193
194
195
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 191

def quote_column_name(name)
  name = name.to_s.split('.')
  name.map! { |n| quote_name_part(n) } # "[#{name}]"
  name.join('.')
end

#quote_database_name(name) ⇒ Object 



197
198
199
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 197

def quote_database_name(name)
  quote_name_part(name.to_s)
end

#quote_table_name(name) ⇒ Object

This is the same as the abstract method



186
187
188
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 186

def quote_table_name(name)
  quote_column_name(name)
end

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



126
127
128
129
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 126

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

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



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

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 



131
132
133
134
135
136
137
138
139
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 131

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 



292
293
294
295
296
297
298
299
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 292

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 



177
178
179
180
181
182
183
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 177

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.



205
206
207
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
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 205

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:



371
372
373
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 371

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

#use_database(database = nil) ⇒ Object 



99
100
101
102
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 99

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