Module: ArJdbc::MsSQL
- Includes:
- LimitHelpers, TSqlMethods
- Defined in:
- lib/arjdbc/mssql/adapter.rb,
lib/arjdbc/mssql/lock_helpers.rb,
lib/arjdbc/mssql/limit_helpers.rb
Defined Under Namespace
Modules: Column, LimitHelpers, LockHelpers
Constant Summary
collapse
- @@_lob_callback_added =
nil
Class Method Summary
collapse
Instance Method Summary
collapse
-
#adapter_name ⇒ Object
-
#add_column(table_name, column_name, type, options = {}) ⇒ Object
Adds a new column to the named table.
-
#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
-
#clear_cached_table(name) ⇒ Object
-
#columns(table_name, name = nil) ⇒ Object
-
#create_database(name, options = {}) ⇒ Object
-
#determine_order_clause(sql) ⇒ Object
-
#determine_primary_key(table_name) ⇒ Object
-
#drop_database(name) ⇒ Object
-
#get_special_columns(table_name) ⇒ Object
-
#identity_column(table_name) ⇒ Object
-
#modify_types(tp) ⇒ Object
-
#query_requires_identity_insert?(sql) ⇒ Boolean
-
#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, options = {}) ⇒ Object
-
#remove_check_constraints(table_name, column_name) ⇒ Object
-
#remove_column(table_name, *column_names) ⇒ 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
-
#repair_special_columns(sql) ⇒ Object
-
#reset_column_information ⇒ Object
-
#set_identity_insert(table_name, enable = true) ⇒ Object
-
#sqlserver_version ⇒ Object
-
#supports_ddl_transactions? ⇒ Boolean
-
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
-
#unquote_column_name(name) ⇒ Object
-
#with_identity_insert_enabled(table_name, &block) ⇒ Object
Turns IDENTITY_INSERT ON for table during execution of the block N.B.
get_primary_key, get_table_name
#add_limit_offset!
Class Method Details
.arel2_visitors(config) ⇒ Object
45
46
47
48
49
|
# File 'lib/arjdbc/mssql/adapter.rb', line 45
def self.arel2_visitors(config)
require 'arel/visitors/sql_server'
visitor_class = config[:sqlserver_version] == "2000" ? ::Arel::Visitors::SQLServer2000 : ::Arel::Visitors::SQLServer
{}.tap {|v| %w(mssql sqlserver jdbcmssql).each {|x| v[x] = visitor_class } }
end
|
.column_selector ⇒ Object
37
38
39
|
# File 'lib/arjdbc/mssql/adapter.rb', line 37
def self.column_selector
[/sqlserver|tds|Microsoft SQL/i, lambda {|cfg,col| col.extend(::ArJdbc::MsSQL::Column)}]
end
|
.extended(mod) ⇒ Object
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
# File 'lib/arjdbc/mssql/adapter.rb', line 14
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 |column|
value = ::ArJdbc::SerializedAttributesHelper.dump_column_value(self, column)
next if value.nil? || (value == '')
connection.write_large_object(
column.type == :binary, column.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
#adapter_name ⇒ Object
246
247
248
|
# File 'lib/arjdbc/mssql/adapter.rb', line 246
def adapter_name 'MsSQL'
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.
286
287
288
289
290
291
292
293
|
# File 'lib/arjdbc/mssql/adapter.rb', line 286
def add_column(table_name, column_name, type, options = {})
clear_cached_table(table_name)
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_version_specific_add_limit_offset ⇒ Object
#change_column(table_name, column_name, type, options = {}) ⇒ Object
300
301
302
303
304
|
# File 'lib/arjdbc/mssql/adapter.rb', line 300
def change_column(table_name, column_name, type, options = {}) clear_cached_table(table_name)
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
315
316
317
318
319
320
321
|
# File 'lib/arjdbc/mssql/adapter.rb', line 315
def change_column_default(table_name, column_name, default) clear_cached_table(table_name)
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
306
307
308
309
310
311
312
313
|
# File 'lib/arjdbc/mssql/adapter.rb', line 306
def change_column_type(table_name, column_name, type, options = {}) clear_cached_table(table_name)
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
250
251
252
253
254
255
256
257
258
|
# File 'lib/arjdbc/mssql/adapter.rb', line 250
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
|
#clear_cached_table(name) ⇒ Object
446
447
448
|
# File 'lib/arjdbc/mssql/adapter.rb', line 446
def clear_cached_table(name)
(@table_columns ||= {}).delete(name.to_s)
end
|
#columns(table_name, name = nil) ⇒ Object
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
|
# File 'lib/arjdbc/mssql/adapter.rb', line 353
def columns(table_name, name = nil)
return [] if table_name.blank?
table_name = table_name.to_s if table_name.is_a?(Symbol)
table_name = table_name.gsub(/[\[\]]/, '')
return [] if table_name =~ /^information_schema\./i
@table_columns ||= {}
unless @table_columns[table_name]
@table_columns[table_name] = super
@table_columns[table_name].each do |col|
col.identity = true if col.sql_type =~ /identity/i
col.is_special = true if col.sql_type =~ /text|ntext|image|xml/i
end
end
@table_columns[table_name]
end
|
#create_database(name, options = {}) ⇒ Object
274
275
276
277
|
# File 'lib/arjdbc/mssql/adapter.rb', line 274
def create_database(name, options={})
execute "CREATE DATABASE #{name}"
execute "USE #{name}"
end
|
#determine_order_clause(sql) ⇒ Object
431
432
433
434
435
|
# File 'lib/arjdbc/mssql/adapter.rb', line 431
def determine_order_clause(sql)
return $1 if sql =~ /ORDER BY (.*)$/
table_name = get_table_name(sql)
"#{table_name}.#{determine_primary_key(table_name)}"
end
|
#determine_primary_key(table_name) ⇒ Object
437
438
439
440
441
442
443
444
|
# File 'lib/arjdbc/mssql/adapter.rb', line 437
def determine_primary_key(table_name)
primary_key = columns(table_name).detect { |column| column.primary || column.identity }
return primary_key.name if primary_key
columns(table_name).each { |column| return column.name if column.name =~ /^id$/i }
columns(table_name)[0].name
end
|
#drop_database(name) ⇒ Object
269
270
271
272
|
# File 'lib/arjdbc/mssql/adapter.rb', line 269
def drop_database(name)
execute "USE master"
execute "DROP DATABASE #{name}"
end
|
#get_special_columns(table_name) ⇒ Object
414
415
416
417
418
419
420
|
# File 'lib/arjdbc/mssql/adapter.rb', line 414
def get_special_columns(table_name)
special = []
columns(table_name).each do |col|
special << col.name if col.is_special
end
special
end
|
#identity_column(table_name) ⇒ Object
390
391
392
393
394
395
|
# File 'lib/arjdbc/mssql/adapter.rb', line 390
def identity_column(table_name)
columns(table_name).each do |col|
return col.name if col.identity
end
return nil
end
|
#modify_types(tp) ⇒ Object
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
# File 'lib/arjdbc/mssql/adapter.rb', line 64
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[:primary_key] = "int NOT NULL IDENTITY(1, 1) PRIMARY KEY"
tp[:integer][:limit] = nil
tp[:boolean] = {:name => "bit"}
tp[:binary] = {:name => "image"}
tp
end
|
#query_requires_identity_insert?(sql) ⇒ Boolean
397
398
399
400
401
402
403
404
|
# File 'lib/arjdbc/mssql/adapter.rb', line 397
def query_requires_identity_insert?(sql)
table_name = get_table_name(sql)
id_column = identity_column(table_name)
if sql.strip =~ /insert into [^ ]+ ?\((.+?)\)/i
insert_columns = $1.split(/, */).map(&method(:unquote_column_name))
return table_name if insert_columns.include?(id_column)
end
end
|
#quote(value, column = nil) ⇒ Object
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
# File 'lib/arjdbc/mssql/adapter.rb', line 202
def quote(value, column = nil)
return value.quoted_id if value.respond_to?(:quoted_id)
case value
when String, ActiveSupport::Multibyte::Chars, Integer
value = value.to_s
if column && column.type == :binary
"'#{quote_string(ArJdbc::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
234
235
236
|
# File 'lib/arjdbc/mssql/adapter.rb', line 234
def quote_column_name(name)
"[#{name}]"
end
|
#quote_string(string) ⇒ Object
226
227
228
|
# File 'lib/arjdbc/mssql/adapter.rb', line 226
def quote_string(string)
string.gsub(/\'/, "''")
end
|
#quote_table_name(name) ⇒ Object
230
231
232
|
# File 'lib/arjdbc/mssql/adapter.rb', line 230
def quote_table_name(name)
quote_column_name(name)
end
|
#quoted_false ⇒ Object
242
243
244
|
# File 'lib/arjdbc/mssql/adapter.rb', line 242
def quoted_false
quote false
end
|
#quoted_true ⇒ Object
238
239
240
|
# File 'lib/arjdbc/mssql/adapter.rb', line 238
def quoted_true
quote true
end
|
#recreate_database(name, options = {}) ⇒ Object
264
265
266
267
|
# File 'lib/arjdbc/mssql/adapter.rb', line 264
def recreate_database(name, options = {})
drop_database(name)
create_database(name, options)
end
|
#remove_check_constraints(table_name, column_name) ⇒ Object
340
341
342
343
344
345
346
347
|
# File 'lib/arjdbc/mssql/adapter.rb', line 340
def remove_check_constraints(table_name, column_name)
clear_cached_table(table_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_names) ⇒ Object
323
324
325
326
327
328
329
330
|
# File 'lib/arjdbc/mssql/adapter.rb', line 323
def remove_column(table_name, *column_names) clear_cached_table(table_name)
for column_name in column_names.flatten
remove_check_constraints(table_name, column_name)
remove_default_constraint(table_name, column_name)
execute "ALTER TABLE #{table_name} DROP COLUMN [#{column_name}]"
end
end
|
#remove_default_constraint(table_name, column_name) ⇒ Object
332
333
334
335
336
337
338
|
# File 'lib/arjdbc/mssql/adapter.rb', line 332
def remove_default_constraint(table_name, column_name)
clear_cached_table(table_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
349
350
351
|
# File 'lib/arjdbc/mssql/adapter.rb', line 349
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
298
|
# File 'lib/arjdbc/mssql/adapter.rb', line 295
def rename_column(table, column, new_column_name)
clear_cached_table(table)
execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
end
|
#rename_table(name, new_name) ⇒ Object
279
280
281
282
|
# File 'lib/arjdbc/mssql/adapter.rb', line 279
def rename_table(name, new_name)
clear_cached_table(name)
execute "EXEC sp_rename '#{name}', '#{new_name}'"
end
|
#repair_special_columns(sql) ⇒ Object
422
423
424
425
426
427
428
429
|
# File 'lib/arjdbc/mssql/adapter.rb', line 422
def repair_special_columns(sql)
special_cols = get_special_columns(get_table_name(sql))
for col in special_cols.to_a
sql.gsub!(Regexp.new(" #{col.to_s} = "), " #{col.to_s} LIKE ")
sql.gsub!(/ORDER BY #{col.to_s}/i, '')
end
sql
end
|
450
451
452
|
# File 'lib/arjdbc/mssql/adapter.rb', line 450
def reset_column_information
@table_columns = nil
end
|
#set_identity_insert(table_name, enable = true) ⇒ Object
384
385
386
387
388
|
# File 'lib/arjdbc/mssql/adapter.rb', line 384
def set_identity_insert(table_name, enable = true)
execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}"
rescue Exception => e
raise ActiveRecord::ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}"
end
|
#sqlserver_version ⇒ Object
51
52
53
|
# File 'lib/arjdbc/mssql/adapter.rb', line 51
def sqlserver_version
@sqlserver_version ||= select_value("select @@version")[/Microsoft SQL Server\s+(\d{4})/, 1]
end
|
#supports_ddl_transactions? ⇒ Boolean
260
261
262
|
# File 'lib/arjdbc/mssql/adapter.rb', line 260
def supports_ddl_transactions?
true
end
|
#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
# File 'lib/arjdbc/mssql/adapter.rb', line 81
def type_to_sql(type, limit = nil, precision = nil, scale = nil) if type.to_s == 'string' and limit == 1073741823 and sqlserver_version != "2000"
'NVARCHAR(MAX)'
elsif %w( boolean date datetime ).include?(type.to_s)
super(type) else
super
end
end
|
#unquote_column_name(name) ⇒ Object
406
407
408
409
410
411
412
|
# File 'lib/arjdbc/mssql/adapter.rb', line 406
def unquote_column_name(name)
if name =~ /^\[.*\]$/
name[1..-2]
else
name
end
end
|
#with_identity_insert_enabled(table_name, &block) ⇒ Object
Turns IDENTITY_INSERT ON for table during execution of the block N.B. This sets the state of IDENTITY_INSERT to OFF after the block has been executed without regard to its previous state
377
378
379
380
381
382
|
# File 'lib/arjdbc/mssql/adapter.rb', line 377
def with_identity_insert_enabled(table_name, &block)
set_identity_insert(table_name, true)
yield
ensure
set_identity_insert(table_name, false)
end
|