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_timestamps(table_name, **options) ⇒ Object 



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

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 



287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 287

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 



272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 272

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 



321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 321

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
  sql_alter = [
    "ALTER TABLE #{quoted_table}",
    "ALTER COLUMN #{quoted_column} #{type_to_sql(column.type, limit: column.limit, precision: column.precision, scale: column.scale)}",
    (' NOT NULL' unless null)
  ]

  execute(sql_alter.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.



239
240
241
242
243
244
245
246
247
248
249
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 239

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 



259
260
261
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 259

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 



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 141

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.



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

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 



181
182
183
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 181

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



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

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

Raises:

  • (ArgumentError) 


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

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

  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

#rename_column(table_name, column_name, new_column_name) ⇒ Object 



263
264
265
266
267
268
269
270
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 263

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 



164
165
166
167
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 164

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.



189
190
191
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
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 189

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:



338
339
340
 
# File 'lib/arjdbc/mssql/schema_statements.rb', line 338

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