Module: JdbcSpec::MsSQL
- Includes:
- TSqlMethods
- Defined in:
- lib/jdbc_adapter/jdbc_mssql.rb
Defined Under Namespace
Modules: Column, SqlServer2000LimitOffset, SqlServerLimitOffset
Class Method Summary
collapse
Instance Method Summary
collapse
-
#_execute(sql, name = nil) ⇒ Object
-
#add_column(table_name, column_name, type, options = {}) ⇒ Object
Adds a new column to the named table.
-
#add_lock!(sql, options) ⇒ Object
-
#add_version_specific_add_limit_offset ⇒ Object
-
#change_column(table_name, column_name, type, options = {}) ⇒ Object
-
#change_column_default(table_name, column_name, default) ⇒ Object
-
#change_column_type(table_name, column_name, type, options = {}) ⇒ Object
-
#change_order_direction(order) ⇒ Object
-
#columns(table_name, name = nil) ⇒ Object
-
#create_database(name) ⇒ Object
-
#drop_database(name) ⇒ Object
-
#modify_types(tp) ⇒ Object
-
#quote(value, column = nil) ⇒ Object
-
#quote_column_name(name) ⇒ Object
-
#quote_string(string) ⇒ Object
-
#quote_table_name(name) ⇒ Object
-
#quoted_false ⇒ Object
-
#quoted_true ⇒ Object
-
#recreate_database(name) ⇒ Object
-
#remove_check_constraints(table_name, column_name) ⇒ Object
-
#remove_column(table_name, column_name) ⇒ Object
-
#remove_default_constraint(table_name, column_name) ⇒ Object
-
#remove_index(table_name, options = {}) ⇒ Object
-
#rename_column(table, column, new_column_name) ⇒ Object
-
#rename_table(name, new_name) ⇒ Object
-
#sqlserver_version ⇒ Object
-
#supports_ddl_transactions? ⇒ Boolean
#add_limit_offset!, #type_to_sql
Class Method Details
.adapter_matcher(name) ⇒ Object
42
43
44
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 42
def self.adapter_matcher(name, *)
name =~ /sqlserver|tds/i ? self : false
end
|
.column_selector ⇒ Object
46
47
48
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 46
def self.column_selector
[/sqlserver|tds/i, lambda {|cfg,col| col.extend(::JdbcSpec::MsSQL::Column)}]
end
|
.extended(mod) ⇒ Object
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 22
def self.extended(mod)
unless @lob_callback_added
ActiveRecord::Base.class_eval do
def after_save_with_mssql_lob
self.class.columns.select { |c| c.sql_type =~ /image/i }.each do |c|
value = self[c.name]
value = value.to_yaml if unserializable_attribute?(c.name, c)
next if value.nil? || (value == '')
connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value)
end
end
end
ActiveRecord::Base.after_save :after_save_with_mssql_lob
@lob_callback_added = true
end
mod.add_version_specific_add_limit_offset
end
|
.jdbc_connection_class ⇒ Object
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 354
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 =~ /^(create|exec)/i
@connection.execute_update(sql)
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.
287
288
289
290
291
292
293
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 287
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)
execute(add_column_sql)
end
|
#add_lock!(sql, options) ⇒ Object
SELECT .. FOR UPDATE is not supported on Microsoft SQL Server
375
376
377
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 375
def add_lock!(sql, options)
sql
end
|
#add_version_specific_add_limit_offset ⇒ Object
#change_column(table_name, column_name, type, options = {}) ⇒ Object
299
300
301
302
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 299
def change_column(table_name, column_name, type, options = {}) change_column_type(table_name, column_name, type, options)
change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
end
|
#change_column_default(table_name, column_name, default) ⇒ Object
312
313
314
315
316
317
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 312
def change_column_default(table_name, column_name, default) remove_default_constraint(table_name, column_name)
unless default.nil?
execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default)} FOR #{quote_column_name(column_name)}"
end
end
|
#change_column_type(table_name, column_name, type, options = {}) ⇒ Object
304
305
306
307
308
309
310
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 304
def change_column_type(table_name, column_name, type, options = {}) sql = "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
if options.has_key?(:null)
sql += (options[:null] ? " NULL" : " NOT NULL")
end
execute(sql)
end
|
#change_order_direction(order) ⇒ Object
252
253
254
255
256
257
258
259
260
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 252
def change_order_direction(order)
order.split(",").collect do |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
end.join(",")
end
|
#columns(table_name, name = nil) ⇒ Object
344
345
346
347
348
349
350
351
352
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 344
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
276
277
278
279
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 276
def create_database(name)
execute "CREATE DATABASE #{name}"
execute "USE #{name}"
end
|
#drop_database(name) ⇒ Object
271
272
273
274
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 271
def drop_database(name)
execute "USE master"
execute "DROP DATABASE #{name}"
end
|
#modify_types(tp) ⇒ Object
66
67
68
69
70
71
72
73
74
75
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 66
def modify_types(tp) super(tp)
tp[:string] = {:name => "NVARCHAR", :limit => 255}
if sqlserver_version == "2000"
tp[:text] = {:name => "NTEXT"}
else
tp[:text] = {:name => "NVARCHAR(MAX)"}
end
tp
end
|
#quote(value, column = nil) ⇒ Object
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 161
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))}'" elsif column && [:integer, :float].include?(column.type)
value = column.type == :integer ? value.to_i : value.to_f
value.to_s
elsif !column.respond_to?(:is_utf8?) || column.is_utf8?
"N'#{quote_string(value)}'" else
super
end
when TrueClass then '1'
when FalseClass then '0'
else super
end
end
|
#quote_column_name(name) ⇒ Object
191
192
193
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 191
def quote_column_name(name)
"[#{name}]"
end
|
#quote_string(string) ⇒ Object
183
184
185
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 183
def quote_string(string)
string.gsub(/\'/, "''")
end
|
#quote_table_name(name) ⇒ Object
187
188
189
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 187
def quote_table_name(name)
name
end
|
#quoted_false ⇒ Object
199
200
201
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 199
def quoted_false
quote false
end
|
#quoted_true ⇒ Object
195
196
197
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 195
def quoted_true
quote true
end
|
#recreate_database(name) ⇒ Object
266
267
268
269
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 266
def recreate_database(name)
drop_database(name)
create_database(name)
end
|
#remove_check_constraints(table_name, column_name) ⇒ Object
332
333
334
335
336
337
338
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 332
def remove_check_constraints(table_name, column_name)
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
319
320
321
322
323
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 319
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
325
326
327
328
329
330
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 325
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
340
341
342
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 340
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
295
296
297
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 295
def rename_column(table, column, new_column_name)
execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end
|
#rename_table(name, new_name) ⇒ Object
281
282
283
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 281
def rename_table(name, new_name)
execute "EXEC sp_rename '#{name}', '#{new_name}'"
end
|
#sqlserver_version ⇒ Object
54
55
56
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 54
def sqlserver_version
@sqlserver_version ||= select_value("select @@version")[/Microsoft SQL Server\s+(\d{4})/, 1]
end
|
#supports_ddl_transactions? ⇒ Boolean
262
263
264
|
# File 'lib/jdbc_adapter/jdbc_mssql.rb', line 262
def supports_ddl_transactions?
true
end
|