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)' },
  # 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 



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

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 



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

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

  super
end

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



305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 305

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 



290
291
292
293
294
295
296
297
298
299
300
301
302
303
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 290

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 



339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 339

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 



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

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

#collation ⇒ Object 



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

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.



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

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 



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

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 



277
278
279
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 277

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

#current_database ⇒ Object 



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

def current_database
  select_value 'SELECT DB_NAME()'
end

#drop_database(name) ⇒ Object 



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

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 



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

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|
      fktable = fkdata['FKTABLE_NAME']
      fkcolmn = fkdata['FKCOLUMN_NAME']
      pktable = fkdata['PKTABLE_NAME']
      pkcolmn = fkdata['PKCOLUMN_NAME']
      remove_foreign_key(fktable, name: fkdata['FK_NAME'])
      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] && mssql_major_version < 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 



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

def foreign_keys(table_name)
  @connection.foreign_keys(table_name)
end

#indexes(table_name) ⇒ Object

Returns an array of indexes for the given table.



47
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
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 47

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 



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

def native_database_types
  NATIVE_DATABASE_TYPES
end

#primary_keys(table_name) ⇒ Object 



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

def primary_keys(table_name)
  @connection.primary_keys(table_name)
end

#quote_column_name(name) ⇒ Object

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



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

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 



189
190
191
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 189

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



178
179
180
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 178

def quote_table_name(name)
  quote_column_name(name)
end

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



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

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

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



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

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 



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

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 



281
282
283
284
285
286
287
288
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 281

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_table_name) ⇒ Object 



172
173
174
175
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 172

def rename_table(table_name, new_table_name)
  execute "EXEC sp_rename '#{table_name}', '#{new_table_name}'"
  rename_table_indexes(table_name, new_table_name)
end

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

Maps logical Rails types to MSSQL-specific data types.



197
198
199
200
201
202
203
204
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
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 197

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:



360
361
362
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 360

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

#use_database(database = nil) ⇒ Object 



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

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