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 



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 74

def change_column(table_name, column_name, type, options = {})
  sql_commands = []
  indexes = []
  column_object = schema_cache.columns(table_name).detect { |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(options[:default])} 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, options[:limit], options[:precision], options[:scale])}"
  sql_commands[-1] << " NOT NULL" if !options[:null].nil? && options[:null] == false
  if options_include_default?(options)
    sql_commands << "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{default_constraint_name(table_name,column_name)} DEFAULT #{quote(options[:default])} 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.collect {|c|quote_column_name(c)}.join(', ')})"
  end
  sql_commands.each { |c| do_execute(c) }
end

#change_column_default(table_name, column_name, default) ⇒ Object 



98
99
100
101
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 98

def change_column_default(table_name, column_name, default)
  remove_default_constraint(table_name, column_name)
  do_execute "ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{default_constraint_name(table_name, column_name)} DEFAULT #{quote(default)} FOR #{quote_column_name(column_name)}"
end

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



135
136
137
138
139
140
141
142
143
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 135

def change_column_null(table_name, column_name, allow_null, default = nil)
  column = detect_column_for! table_name, column_name
  if !allow_null.nil? && allow_null == false && !default.nil?
    do_execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end
  sql = "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql column.type, column.limit, column.precision, column.scale}"
  sql << " NOT NULL" if !allow_null.nil? && allow_null == false
  do_execute sql
end

#columns(table_name, name = nil) ⇒ Object 



39
40
41
42
43
44
45
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 39

def columns(table_name, name = nil)
  return [] if table_name.blank?
  column_definitions(table_name).collect do |ci|
    sqlserver_options = ci.except(:name,:default_value,:type,:null).merge(:database_year=>database_year)
    SQLServerColumn.new ci[:name], ci[:default_value], ci[:type], ci[:null], sqlserver_options
  end
end

#columns_for_distinct(columns, orders) ⇒ Object

like postgres, sqlserver requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column. this method is idental to the postgres method



50
51
52
53
54
55
56
57
58
59
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 50

def columns_for_distinct(columns, orders) #:nodoc:
  order_columns = orders.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)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '')
    }.reject(&:blank?).map.with_index { |column, i| "#{column} AS alias_#{i}" }

  [super, *order_columns].join(', ')
end

#indexes(table_name, name = nil) ⇒ Object 



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 20

def indexes(table_name, name = nil)
  data = select("EXEC sp_helpindex #{quote(table_name)}",name) rescue []
  data.inject([]) 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/
      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)
    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

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

Raises:

  • (ArgumentError) 


66
67
68
69
70
71
72
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 66

def remove_column(table_name, column_name, type = nil)
  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 



115
116
117
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 115

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 



103
104
105
106
107
108
109
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 103

def rename_column(table_name, column_name, new_column_name)
  schema_cache.clear_table_cache!(table_name)
  detect_column_for! table_name, column_name
  do_execute "EXEC sp_rename '#{table_name}.#{column_name}', '#{new_column_name}', 'COLUMN'"
  rename_column_indexes(table_name, column_name, new_column_name)
  schema_cache.clear_table_cache!(table_name)
end

#rename_index(table_name, old_name, new_name) ⇒ Object 



111
112
113
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 111

def rename_index(table_name, old_name, new_name)
  execute "EXEC sp_rename N'#{table_name}.#{old_name}', N'#{new_name}', N'INDEX'"
end

#rename_table(table_name, new_name) ⇒ Object 



61
62
63
64
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 61

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

#table_exists?(table_name) ⇒ Boolean

Returns:

  • (Boolean) 


14
15
16
17
18
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 14

def table_exists?(table_name)
  return false if table_name.blank?
  unquoted_table_name = Utils.unqualify_table_name(table_name)
  super || tables.include?(unquoted_table_name) || views.include?(unquoted_table_name)
end

#tables(table_type = 'BASE TABLE') ⇒ Object 



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

def tables(table_type = 'BASE TABLE')
  select_values "SELECT #{lowercase_schema_reflection_sql('TABLE_NAME')} FROM INFORMATION_SCHEMA.TABLES #{"WHERE TABLE_TYPE = '#{table_type}'" if table_type} ORDER BY TABLE_NAME", 'SCHEMA'
end

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



119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 119

def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  type_limitable = ['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
  else
    super
  end
end

#views ⇒ Object

SQLServer Specific ======================================== #



147
148
149
 
# File 'lib/active_record/connection_adapters/sqlserver/schema_statements.rb', line 147

def views
  tables('VIEW')
end