Module: JdbcSpec::MsSQL

Includes:
TSqlMethods
Defined in:
lib/jdbc_adapter/jdbc_mssql.rb

Defined Under Namespace

Modules: Column

Class Method Summary collapse

Instance Method Summary collapse

Methods included from TSqlMethods

#add_limit_offset!, #modify_types, #type_to_sql

Class Method Details

.adapter_selector ⇒ Object 



30
31
32
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 30

def self.adapter_selector
  [/sqlserver|tds/i, lambda {|cfg,adapt| adapt.extend(::JdbcSpec::MsSQL)}]
end

.column_selector ⇒ Object 



26
27
28
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 26

def self.column_selector
  [/sqlserver|tds/i, lambda {|cfg,col| col.extend(::JdbcSpec::MsSQL::Column)}]
end

Instance Method Details

#_execute(sql, name = nil) ⇒ Object 



232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 232

def _execute(sql, name = nil)
  if sql.lstrip =~ /^insert/i
    if query_requires_identity_insert?(sql)
      table_name = get_table_name(sql)
      with_identity_insert_enabled(table_name) do
        id = @connection.execute_insert(sql)
      end
    else
      @connection.execute_insert(sql)
    end
  elsif sql.lstrip =~ /^\(?\s*(select|show)/i
    repair_special_columns(sql)
    @connection.execute_query(sql)
  else
    @connection.execute_update(sql)
  end
end

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

Adds a new column to the named table. See TableDefinition#column for details of the options you can use.



170
171
172
173
174
175
176
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 170

def add_column(table_name, column_name, type, options = {})
  add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  add_column_options!(add_column_sql, options)
  # TODO: Add support to mimic date columns, using constraints to mark them as such in the database
  # add_column_sql << " CONSTRAINT ck__#{table_name}__#{column_name}__date_only CHECK ( CONVERT(CHAR(12), #{quote_column_name(column_name)}, 14)='00:00:00:000' )" if type == :date
  execute(add_column_sql)
end

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

:nodoc:



182
183
184
185
186
187
188
189
190
191
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 182

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"]
  if options_include_default?(options)
    remove_default_constraint(table_name, column_name)
    sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(options[:default], options[:column])} FOR #{column_name}"
  end
  sql_commands.each {|c|
    execute(c)
  }
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



192
193
194
195
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 192

def change_column_default(table_name, column_name, default) #:nodoc:
  remove_default_constraint(table_name, column_name)
  execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default, column_name)} FOR #{column_name}"
end

#change_order_direction(order) ⇒ Object 



141
142
143
144
145
146
147
148
149
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 141

def change_order_direction(order)
  order.split(",").collect {|fragment|
    case fragment
    when  /\bDESC\b/i     then fragment.gsub(/\bDESC\b/i, "ASC")
    when  /\bASC\b/i      then fragment.gsub(/\bASC\b/i, "DESC")
    else                  String.new(fragment).split(',').join(' DESC,') + ' DESC'
    end
  }.join(",")
end

#columns(table_name, name = nil) ⇒ Object 



222
223
224
225
226
227
228
229
230
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 222

def columns(table_name, name = nil)
  return [] if table_name =~ /^information_schema\./i
  cc = super
  cc.each do |col|
    col.identity = true if col.sql_type =~ /identity/i
    col.is_special = true if col.sql_type =~ /text|ntext|image/i
  end
  cc
end

#create_database(name) ⇒ Object 



160
161
162
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 160

def create_database(name)
  execute "CREATE DATABASE #{name}"
end

#drop_database(name) ⇒ Object 



156
157
158
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 156

def drop_database(name)
  execute "DROP DATABASE #{name}"
end

#quote(value, column = nil) ⇒ Object 



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 107

def quote(value, column = nil)
  return value.quoted_id if value.respond_to?(:quoted_id)

  case value
  when String, ActiveSupport::Multibyte::Chars
    value = value.to_s
    if column && column.type == :binary
      "'#{quote_string(JdbcSpec::MsSQL::Column.string_to_binary(value))}'" # ' (for ruby-mode)
    elsif column && [:integer, :float].include?(column.type)
      value = column.type == :integer ? value.to_i : value.to_f
      value.to_s
    else
      "'#{quote_string(value)}'" # ' (for ruby-mode)
    end
  when TrueClass             then '1'
  when FalseClass            then '0'
  when Time, DateTime        then "'#{value.strftime("%Y%m%d %H:%M:%S")}'"
  when Date                  then "'#{value.strftime("%Y%m%d")}'"
  else                       super
  end
end

#quote_column_name(name) ⇒ Object 



137
138
139
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 137

def quote_column_name(name)
  "[#{name}]"
end

#quote_string(string) ⇒ Object 



129
130
131
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 129

def quote_string(string)
  string.gsub(/\'/, "''")
end

#quote_table_name(name) ⇒ Object 



133
134
135
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 133

def quote_table_name(name)
  name
end

#recreate_database(name) ⇒ Object 



151
152
153
154
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 151

def recreate_database(name)
  drop_database(name)
  create_database(name)
end

#remove_check_constraints(table_name, column_name) ⇒ Object 



209
210
211
212
213
214
215
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 209

def remove_check_constraints(table_name, column_name)
  # TODO remove all constraints in single method
  constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
  constraints.each do |constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
  end
end

#remove_column(table_name, column_name) ⇒ Object 



196
197
198
199
200
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 196

def remove_column(table_name, column_name)
  remove_check_constraints(table_name, column_name)
  remove_default_constraint(table_name, column_name)
  execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
end

#remove_default_constraint(table_name, column_name) ⇒ Object 



202
203
204
205
206
207
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 202

def remove_default_constraint(table_name, column_name)
  defaults = select "select def.name from sysobjects def, syscolumns col, sysobjects tab where col.cdefault = def.id and col.name = '#{column_name}' and tab.name = '#{table_name}' and col.id = tab.id"
  defaults.each {|constraint|
    execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
  }
end

#remove_index(table_name, options = {}) ⇒ Object 



217
218
219
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 217

def remove_index(table_name, options = {})
  execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
end

#rename_column(table, column, new_column_name) ⇒ Object 



178
179
180
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 178

def rename_column(table, column, new_column_name)
  execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end

#rename_table(name, new_name) ⇒ Object 



164
165
166
 
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 164

def rename_table(name, new_name)
  execute "EXEC sp_rename '#{name}', '#{new_name}'"
end