sqlserver_adapter.rb
1 |
require 'active_record/connection_adapters/abstract_adapter'
|
---|---|
2 |
|
3 |
require 'bigdecimal'
|
4 |
require 'bigdecimal/util'
|
5 |
|
6 |
# sqlserver_adapter.rb -- ActiveRecord adapter for Microsoft SQL Server
|
7 |
#
|
8 |
# Author: Joey Gibson <joey@joeygibson.com>
|
9 |
# Date: 10/14/2004
|
10 |
#
|
11 |
# Modifications: DeLynn Berry <delynnb@megastarfinancial.com>
|
12 |
# Date: 3/22/2005
|
13 |
#
|
14 |
# Modifications (ODBC): Mark Imbriaco <mark.imbriaco@pobox.com>
|
15 |
# Date: 6/26/2005
|
16 |
|
17 |
# Modifications (Migrations): Tom Ward <tom@popdog.net>
|
18 |
# Date: 27/10/2005
|
19 |
#
|
20 |
# Modifications (Numerous fixes as maintainer): Ryan Tomayko <rtomayko@gmail.com>
|
21 |
# Date: Up to July 2006
|
22 |
|
23 |
# Current maintainer: Tom Ward <tom@popdog.net>
|
24 |
|
25 |
module ActiveRecord |
26 |
class Base |
27 |
def self.sqlserver_connection(config) #:nodoc: |
28 |
require_library_or_gem 'dbi' unless self.class.const_defined?(:DBI) |
29 |
|
30 |
config = config.symbolize_keys |
31 |
|
32 |
mode = config[:mode] ? config[:mode].to_s.upcase : 'ADO' |
33 |
username = config[:username] ? config[:username].to_s : 'sa' |
34 |
password = config[:password] ? config[:password].to_s : '' |
35 |
autocommit = config.key?(:autocommit) ? config[:autocommit] : true |
36 |
if mode == "ODBC" |
37 |
raise ArgumentError, "Missing DSN. Argument ':dsn' must be set in order for this adapter to work." unless config.has_key?(:dsn) |
38 |
dsn = config[:dsn]
|
39 |
driver_url = "DBI:ODBC:#{dsn}"
|
40 |
else
|
41 |
raise ArgumentError, "Missing Database. Argument ':database' must be set in order for this adapter to work." unless config.has_key?(:database) |
42 |
database = config[:database]
|
43 |
host = config[:host] ? config[:host].to_s : 'localhost' |
44 |
unless config[:trusted_connection] |
45 |
driver_url = "DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};"
|
46 |
else
|
47 |
driver_url = "DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};Trusted_Connection=Yes;"
|
48 |
end
|
49 |
end
|
50 |
conn = DBI.connect(driver_url, username, password)
|
51 |
conn["AutoCommit"] = autocommit
|
52 |
ConnectionAdapters::SQLServerAdapter.new(conn, logger, [driver_url, username, password]) |
53 |
end
|
54 |
end # class Base |
55 |
|
56 |
module ConnectionAdapters |
57 |
class SQLServerColumn < Column# :nodoc: |
58 |
attr_reader :identity, :is_special |
59 |
|
60 |
def initialize(name, default, sql_type = nil, identity = false, null = true) # TODO: check ok to remove scale_value = 0 |
61 |
super(name, default, sql_type, null)
|
62 |
@identity = identity
|
63 |
@is_special = sql_type =~ /text|ntext|image/i |
64 |
# TODO: check ok to remove @scale = scale_value
|
65 |
# SQL Server only supports limits on *char and float types
|
66 |
@limit = nil unless @type == :string |
67 |
end
|
68 |
|
69 |
def simplified_type(field_type) |
70 |
case field_type
|
71 |
when /money/i then :decimal |
72 |
when /image/i then :binary |
73 |
when /bit/i then :boolean |
74 |
when /uniqueidentifier/i then :string |
75 |
else super |
76 |
end
|
77 |
end
|
78 |
|
79 |
def type_cast(value) |
80 |
return nil if value.nil? |
81 |
case type
|
82 |
when :datetime then cast_to_datetime(value) |
83 |
when :timestamp then cast_to_time(value) |
84 |
when :time then cast_to_time(value) |
85 |
when :date then cast_to_datetime(value) |
86 |
# when :date then cast_to_date(value)
|
87 |
when :boolean then value == true or (value =~ /^t(rue)?$/i) == 0 or value.to_s == '1' |
88 |
else super |
89 |
end
|
90 |
end
|
91 |
|
92 |
def cast_to_time(value) |
93 |
return value if value.is_a?(Time) |
94 |
time_array = ParseDate.parsedate(value)
|
95 |
Time.send(Base.default_timezone, *time_array) rescue nil |
96 |
end
|
97 |
|
98 |
def cast_to_datetime(value) |
99 |
return value.to_time if value.is_a?(DBI::Timestamp) |
100 |
|
101 |
if value.is_a?(Time) |
102 |
if value.year != 0 and value.month != 0 and value.day != 0 |
103 |
return value
|
104 |
else
|
105 |
return Time.mktime(2000, 1, 1, value.hour, value.min, value.sec) rescue nil |
106 |
end
|
107 |
end
|
108 |
|
109 |
if value.is_a?(DateTime) |
110 |
return Time.mktime(value.year, value.mon, value.day, value.hour, value.min, value.sec) |
111 |
end
|
112 |
|
113 |
return cast_to_time(value) if value.is_a?(Date) or value.is_a?(String) rescue nil |
114 |
value |
115 |
end
|
116 |
|
117 |
# TODO: Find less hack way to convert DateTime objects into Times
|
118 |
|
119 |
def self.string_to_time(value) |
120 |
if value.is_a?(DateTime) |
121 |
return Time.mktime(value.year, value.mon, value.day, value.hour, value.min, value.sec) |
122 |
else
|
123 |
super
|
124 |
end
|
125 |
end
|
126 |
|
127 |
# These methods will only allow the adapter to insert binary data with a length of 7K or less
|
128 |
# because of a SQL Server statement length policy.
|
129 |
def self.string_to_binary(value) |
130 |
value.gsub(/(\r|\n|\0|\x1a)/) do |
131 |
case $1 |
132 |
when "\r" then "%00" |
133 |
when "\n" then "%01" |
134 |
when "\0" then "%02" |
135 |
when "\x1a" then "%03" |
136 |
end
|
137 |
end
|
138 |
end
|
139 |
|
140 |
def self.binary_to_string(value) |
141 |
value.gsub(/(%00|%01|%02|%03)/) do |
142 |
case $1 |
143 |
when "%00" then "\r" |
144 |
when "%01" then "\n" |
145 |
when "%02\0" then "\0" |
146 |
when "%03" then "\x1a" |
147 |
end
|
148 |
end
|
149 |
end
|
150 |
end
|
151 |
|
152 |
# In ADO mode, this adapter will ONLY work on Windows systems,
|
153 |
# since it relies on Win32OLE, which, to my knowledge, is only
|
154 |
# available on Windows.
|
155 |
#
|
156 |
# This mode also relies on the ADO support in the DBI module. If you are using the
|
157 |
# one-click installer of Ruby, then you already have DBI installed, but
|
158 |
# the ADO module is *NOT* installed. You will need to get the latest
|
159 |
# source distribution of Ruby-DBI from http://ruby-dbi.sourceforge.net/
|
160 |
# unzip it, and copy the file
|
161 |
# <tt>src/lib/dbd_ado/ADO.rb</tt>
|
162 |
# to
|
163 |
# <tt>X:/Ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb</tt>
|
164 |
# (you will more than likely need to create the ADO directory).
|
165 |
# Once you've installed that file, you are ready to go.
|
166 |
#
|
167 |
# In ODBC mode, the adapter requires the ODBC support in the DBI module which requires
|
168 |
# the Ruby ODBC module. Ruby ODBC 0.996 was used in development and testing,
|
169 |
# and it is available at http://www.ch-werner.de/rubyodbc/
|
170 |
#
|
171 |
# Options:
|
172 |
#
|
173 |
# * <tt>:mode</tt> -- ADO or ODBC. Defaults to ADO.
|
174 |
# * <tt>:username</tt> -- Defaults to sa.
|
175 |
# * <tt>:password</tt> -- Defaults to empty string.
|
176 |
#
|
177 |
# ADO specific options:
|
178 |
#
|
179 |
# * <tt>:host</tt> -- Defaults to localhost.
|
180 |
# * <tt>:database</tt> -- The name of the database. No default, must be provided.
|
181 |
#
|
182 |
# ODBC specific options:
|
183 |
#
|
184 |
# * <tt>:dsn</tt> -- Defaults to nothing.
|
185 |
#
|
186 |
# ADO code tested on Windows 2000 and higher systems,
|
187 |
# running ruby 1.8.2 (2004-07-29) [i386-mswin32], and SQL Server 2000 SP3.
|
188 |
#
|
189 |
# ODBC code tested on a Fedora Core 4 system, running FreeTDS 0.63,
|
190 |
# unixODBC 2.2.11, Ruby ODBC 0.996, Ruby DBI 0.0.23 and Ruby 1.8.2.
|
191 |
# [Linux strongmad 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 i686 i386 GNU/Linux]
|
192 |
class SQLServerAdapter < AbstractAdapter |
193 |
|
194 |
def initialize(connection, logger, connection_options=nil) |
195 |
super(connection, logger)
|
196 |
@connection_options = connection_options
|
197 |
end
|
198 |
|
199 |
def native_database_types |
200 |
{ |
201 |
:primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY", |
202 |
:string => { :name => "varchar", :limit => 255 }, |
203 |
:text => { :name => "varchar", :limit => "MAX"}, |
204 |
# :text => { :name => "text" },
|
205 |
:integer => { :name => "int" }, |
206 |
:float => { :name => "float" }, |
207 |
:decimal => { :name => "decimal" }, |
208 |
:datetime => { :name => "datetime" }, |
209 |
:timestamp => { :name => "datetime" }, |
210 |
:time => { :name => "datetime" }, |
211 |
:date => { :name => "datetime" }, |
212 |
:binary => { :name => "image"}, |
213 |
:boolean => { :name => "bit"} |
214 |
} |
215 |
end
|
216 |
|
217 |
def adapter_name |
218 |
'SQLServer'
|
219 |
end
|
220 |
|
221 |
def supports_migrations? #:nodoc: |
222 |
true
|
223 |
end
|
224 |
|
225 |
def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc: |
226 |
return super unless type.to_s == 'integer' |
227 |
|
228 |
if limit.nil? || limit == 4 |
229 |
'integer'
|
230 |
elsif limit < 4 |
231 |
'smallint'
|
232 |
else
|
233 |
'bigint'
|
234 |
end
|
235 |
end
|
236 |
|
237 |
# CONNECTION MANAGEMENT ====================================#
|
238 |
|
239 |
# Returns true if the connection is active.
|
240 |
def active? |
241 |
@connection.execute("SELECT 1").finish |
242 |
true
|
243 |
rescue DBI::DatabaseError, DBI::InterfaceError |
244 |
false
|
245 |
end
|
246 |
|
247 |
# Reconnects to the database, returns false if no connection could be made.
|
248 |
def reconnect! |
249 |
disconnect! |
250 |
@connection = DBI.connect(*@connection_options) |
251 |
rescue DBI::DatabaseError => e |
252 |
@logger.warn "#{adapter_name} reconnection failed: #{e.message}" if @logger |
253 |
false
|
254 |
end
|
255 |
|
256 |
# Disconnects from the database
|
257 |
|
258 |
def disconnect! |
259 |
@connection.disconnect rescue nil |
260 |
end
|
261 |
|
262 |
def columns(table_name, name = nil) |
263 |
return [] if table_name.blank? |
264 |
table_name = table_name.to_s if table_name.is_a?(Symbol) |
265 |
table_name = table_name.split('.')[-1] unless table_name.nil? |
266 |
table_name = table_name.gsub(/[\[\]]/, '') |
267 |
sql = %Q{
|
268 |
SELECT
|
269 |
cols.COLUMN_NAME as ColName,
|
270 |
cols.COLUMN_DEFAULT as DefaultValue,
|
271 |
cols.NUMERIC_SCALE as numeric_scale,
|
272 |
cols.NUMERIC_PRECISION as numeric_precision,
|
273 |
cols.DATA_TYPE as ColType,
|
274 |
cols.IS_NULLABLE As IsNullable,
|
275 |
COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,
|
276 |
COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') as IsIdentity,
|
277 |
cols.NUMERIC_SCALE as Scale
|
278 |
FROM INFORMATION_SCHEMA.COLUMNS cols
|
279 |
WHERE cols.TABLE_NAME = '#{table_name}'
|
280 |
}
|
281 |
# Comment out if you want to have the Columns select statment logged.
|
282 |
# Personally, I think it adds unnecessary bloat to the log.
|
283 |
# If you do comment it out, make sure to un-comment the "result" line that follows
|
284 |
result = log(sql, name) { @connection.select_all(sql) }
|
285 |
#result = @connection.select_all(sql)
|
286 |
columns = [] |
287 |
result.each do |field|
|
288 |
default = field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null|NULL/ ? nil : field[:DefaultValue] |
289 |
if field[:ColType] =~ /numeric|decimal/i |
290 |
type = "#{field[:ColType]}(#{field[:numeric_precision]},#{field[:numeric_scale]})"
|
291 |
else
|
292 |
type = "#{field[:ColType]}(#{field[:Length]})"
|
293 |
end
|
294 |
is_identity = field[:IsIdentity] == 1 |
295 |
is_nullable = field[:IsNullable] == 'YES' |
296 |
columns << SQLServerColumn.new(field[:ColName], default, type, is_identity, is_nullable) |
297 |
end
|
298 |
columns |
299 |
end
|
300 |
|
301 |
def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) |
302 |
execute(sql, name) |
303 |
id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"] |
304 |
end
|
305 |
|
306 |
def update(sql, name = nil) |
307 |
execute(sql, name) do |handle|
|
308 |
handle.rows |
309 |
end || select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"] |
310 |
end
|
311 |
|
312 |
alias_method :delete, :update |
313 |
|
314 |
def execute(sql, name = nil) |
315 |
if sql =~ /^\s*INSERT/i && (table_name = query_requires_identity_insert?(sql)) |
316 |
log(sql, name) do
|
317 |
with_identity_insert_enabled(table_name) do
|
318 |
@connection.execute(sql) do |handle| |
319 |
yield(handle) if block_given? |
320 |
end
|
321 |
end
|
322 |
end
|
323 |
else
|
324 |
log(sql, name) do
|
325 |
@connection.execute(sql) do |handle| |
326 |
yield(handle) if block_given? |
327 |
end
|
328 |
end
|
329 |
end
|
330 |
end
|
331 |
|
332 |
def begin_db_transaction |
333 |
@connection["AutoCommit"] = false |
334 |
rescue Exception => e |
335 |
@connection["AutoCommit"] = true |
336 |
end
|
337 |
|
338 |
def commit_db_transaction |
339 |
@connection.commit
|
340 |
ensure
|
341 |
@connection["AutoCommit"] = true |
342 |
end
|
343 |
|
344 |
def rollback_db_transaction |
345 |
@connection.rollback
|
346 |
ensure
|
347 |
@connection["AutoCommit"] = true |
348 |
end
|
349 |
|
350 |
def quote(value, column = nil) |
351 |
return value.quoted_id if value.respond_to?(:quoted_id) |
352 |
|
353 |
case value
|
354 |
when TrueClass then '1' |
355 |
when FalseClass then '0' |
356 |
when Time, DateTime then "'#{value.strftime("%Y%m%d %H:%M:%S")}'" |
357 |
when Date then "'#{value.strftime("%Y%m%d")}'" |
358 |
else super |
359 |
end
|
360 |
end
|
361 |
|
362 |
def quote_string(string) |
363 |
string.gsub(/\'/, "''") |
364 |
end
|
365 |
|
366 |
def quoted_true |
367 |
"1"
|
368 |
end
|
369 |
|
370 |
def quoted_false |
371 |
"0"
|
372 |
end
|
373 |
|
374 |
def quote_column_name(name) |
375 |
"[#{name}]"
|
376 |
end
|
377 |
|
378 |
def add_limit_offset_2005!(sql, options) |
379 |
if options[:limit] && options[:offset] && options[:offset] > 0 |
380 |
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) {"SELECT#{$1} TOP #{options[:limit] + options[:offset]} "} |
381 |
sql.sub!(/ FROM /i, " INTO #limit_offset_temp -- limit => #{options[:limit]} offset => #{options[:offset]} \n FROM ") |
382 |
elsif options[:limit] && (sql !~ /^\s*SELECT (@@|COUNT\()/i) |
383 |
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) {"SELECT#{$1} TOP #{options[:limit]} "} |
384 |
end
|
385 |
end
|
386 |
|
387 |
def add_limit_offset!(sql,options) |
388 |
add_limit_offset_2005!(sql,options) |
389 |
end
|
390 |
|
391 |
def add_limit_offset_old!(sql, options) |
392 |
if options[:limit] and options[:offset] |
393 |
total_rows = @connection.select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:TotalRows].to_i |
394 |
if (options[:limit] + options[:offset]) >= total_rows |
395 |
options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0 |
396 |
end
|
397 |
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]} ") |
398 |
sql << ") AS tmp1"
|
399 |
if options[:order] |
400 |
order1 = options[:order].split(',').map do |field| |
401 |
parts = field.split(" ")
|
402 |
tc = parts[0]
|
403 |
if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query |
404 |
tc.gsub!(/\./, '\\.\\[') |
405 |
tc << '\\]'
|
406 |
end
|
407 |
if sql =~ /#{tc} AS (t\d_r\d\d?)/ |
408 |
parts[0] = $1 |
409 |
elsif parts[0] =~ /\w+\.(\w+)/ |
410 |
parts[0] = 'tmp1.' + $1 |
411 |
end
|
412 |
parts.join(' ')
|
413 |
end.join(', ') |
414 |
|
415 |
order2 = order1.gsub(/tmp1/, 'tmp2') |
416 |
# order2 = options[:order].split(',').map do |field|
|
417 |
# parts = field.split(" ")
|
418 |
# tc = parts[0]
|
419 |
# if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query
|
420 |
# tc.gsub!(/\./, '\\.\\[')
|
421 |
# tc << '\\]'
|
422 |
# end
|
423 |
# if sql =~ /#{tc} AS (t\d_r\d\d?)/
|
424 |
# parts[0] = $1
|
425 |
# elsif parts[0] =~ /\w+\.(\w+)/
|
426 |
# parts[0] = 'tmp2.' + $1
|
427 |
# end
|
428 |
# parts.join(' ')
|
429 |
# end.join(', ')
|
430 |
sql << " ORDER BY #{change_order_direction(order1)}) AS tmp2 ORDER BY #{order2}"
|
431 |
else
|
432 |
sql << " ) AS tmp2"
|
433 |
end
|
434 |
elsif sql !~ /^\s*SELECT (@@|COUNT\()/i |
435 |
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do |
436 |
"SELECT#{$1} TOP #{options[:limit]}"
|
437 |
end unless options[:limit].nil? |
438 |
end
|
439 |
end
|
440 |
|
441 |
def recreate_database(name) |
442 |
drop_database(name) |
443 |
create_database(name) |
444 |
end
|
445 |
|
446 |
def drop_database(name) |
447 |
execute "DROP DATABASE #{name}"
|
448 |
end
|
449 |
|
450 |
def create_database(name) |
451 |
execute "CREATE DATABASE #{name}"
|
452 |
end
|
453 |
|
454 |
def current_database |
455 |
@connection.select_one("select DB_NAME()")[0] |
456 |
end
|
457 |
|
458 |
def tables(name = nil) |
459 |
execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth| |
460 |
sth.inject([]) do |tables, field|
|
461 |
table_name = field[0]
|
462 |
tables << table_name unless table_name == 'dtproperties' |
463 |
tables |
464 |
end
|
465 |
end
|
466 |
end
|
467 |
|
468 |
def indexes(table_name, name = nil) |
469 |
ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false |
470 |
indexes = [] |
471 |
execute("EXEC sp_helpindex '#{table_name}'", name) do |sth| |
472 |
sth.each do |index|
|
473 |
unique = index[1] =~ /unique/ |
474 |
primary = index[1] =~ /primary key/ |
475 |
if !primary
|
476 |
indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", ")) |
477 |
end
|
478 |
end
|
479 |
end
|
480 |
indexes |
481 |
ensure
|
482 |
ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true |
483 |
end
|
484 |
|
485 |
def add_order_by_for_association_limiting!(sql, options) |
486 |
# Just skip ORDER BY clause. I dont know better solution for DISTINCT plus ORDER BY.
|
487 |
# And this doesnt cause to much problem..
|
488 |
return sql
|
489 |
end
|
490 |
|
491 |
def rename_table(name, new_name) |
492 |
execute "EXEC sp_rename '#{name}', '#{new_name}'"
|
493 |
end
|
494 |
|
495 |
# Adds a new column to the named table.
|
496 |
# See TableDefinition#column for details of the options you can use.
|
497 |
def add_column(table_name, column_name, type, options = {}) |
498 |
add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
|
499 |
add_column_options!(add_column_sql, options) |
500 |
# TODO: Add support to mimic date columns, using constraints to mark them as such in the database
|
501 |
# 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
|
502 |
execute(add_column_sql) |
503 |
end
|
504 |
|
505 |
def rename_column(table, column, new_column_name) |
506 |
execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
|
507 |
end
|
508 |
|
509 |
def change_column(table_name, column_name, type, options = {}) #:nodoc: |
510 |
sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"]
|
511 |
if options_include_default?(options)
|
512 |
remove_default_constraint(table_name, column_name) |
513 |
sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(options[:default], options[:column])} FOR #{column_name}"
|
514 |
end
|
515 |
sql_commands.each {|c| |
516 |
execute(c) |
517 |
} |
518 |
end
|
519 |
|
520 |
def remove_column(table_name, column_name) |
521 |
remove_check_constraints(table_name, column_name) |
522 |
remove_default_constraint(table_name, column_name) |
523 |
execute "ALTER TABLE [#{table_name}] DROP COLUMN [#{column_name}]"
|
524 |
end
|
525 |
|
526 |
def remove_default_constraint(table_name, column_name) |
527 |
constraints = 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"
|
528 |
|
529 |
constraints.each do |constraint|
|
530 |
execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
|
531 |
end
|
532 |
end
|
533 |
|
534 |
def remove_check_constraints(table_name, column_name) |
535 |
# TODO remove all constraints in single method
|
536 |
constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
|
537 |
constraints.each do |constraint|
|
538 |
execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
|
539 |
end
|
540 |
end
|
541 |
|
542 |
def remove_index(table_name, options = {}) |
543 |
execute "DROP INDEX #{table_name}.#{quote_column_name(index_name(table_name, options))}"
|
544 |
end
|
545 |
|
546 |
private |
547 |
def select(sql, name = nil) |
548 |
repair_special_columns(sql) |
549 |
if match = query_has_limit_and_offset?(sql)
|
550 |
matched, limit, offset = *match |
551 |
execute(sql) |
552 |
# SET ROWCOUNT n causes all statements to only affect n rows, which we use
|
553 |
# to delete offset rows from the temporary table
|
554 |
execute("SET ROWCOUNT #{offset}")
|
555 |
execute("DELETE from #limit_offset_temp")
|
556 |
execute("SET ROWCOUNT 0")
|
557 |
result = execute_select("SELECT * FROM #limit_offset_temp")
|
558 |
execute("DROP TABLE #limit_offset_temp")
|
559 |
result |
560 |
else
|
561 |
execute_select(sql) |
562 |
end
|
563 |
end
|
564 |
|
565 |
def execute_select(sql) |
566 |
result = [] |
567 |
execute(sql) do |handle|
|
568 |
handle.each do |row|
|
569 |
row_hash = {} |
570 |
row.each_with_index do |value, i|
|
571 |
if value.is_a? DBI::Timestamp |
572 |
value = DateTime.new(value.year, value.month, value.day, value.hour, value.minute, value.sec)
|
573 |
end
|
574 |
row_hash[handle.column_names[i]] = value |
575 |
end
|
576 |
result << row_hash |
577 |
end
|
578 |
end
|
579 |
result |
580 |
end
|
581 |
|
582 |
def query_has_limit_and_offset?(sql) |
583 |
match = sql.match(/#limit_offset_temp -- limit => (\d+) offset => (\d+)/)
|
584 |
end
|
585 |
|
586 |
# Turns IDENTITY_INSERT ON for table during execution of the block
|
587 |
# N.B. This sets the state of IDENTITY_INSERT to OFF after the
|
588 |
# block has been executed without regard to its previous state
|
589 |
|
590 |
def with_identity_insert_enabled(table_name, &block) |
591 |
set_identity_insert(table_name, true)
|
592 |
yield
|
593 |
ensure
|
594 |
set_identity_insert(table_name, false)
|
595 |
end
|
596 |
|
597 |
def set_identity_insert(table_name, enable = true) |
598 |
execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}"
|
599 |
rescue Exception => e |
600 |
raise ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}" |
601 |
end
|
602 |
|
603 |
def get_table_name(sql) |
604 |
if sql =~ /^\s*insert\s+into\s+([^\(\s]+)\s*|^\s*update\s+([^\(\s]+)\s*/i |
605 |
$1
|
606 |
elsif sql =~ /from\s+([^\(\s]+)\s*/i |
607 |
$1
|
608 |
else
|
609 |
nil
|
610 |
end
|
611 |
end
|
612 |
|
613 |
def identity_column(table_name) |
614 |
@table_columns = {} unless @table_columns |
615 |
@table_columns[table_name] = columns(table_name) if @table_columns[table_name] == nil |
616 |
@table_columns[table_name].each do |col| |
617 |
return col.name if col.identity |
618 |
end
|
619 |
|
620 |
return nil |
621 |
end
|
622 |
|
623 |
def query_requires_identity_insert?(sql) |
624 |
table_name = get_table_name(sql) |
625 |
id_column = identity_column(table_name) |
626 |
sql =~ /\[#{id_column}\]/ ? table_name : nil |
627 |
end
|
628 |
|
629 |
def change_order_direction(order) |
630 |
order.split(",").collect {|fragment|
|
631 |
case fragment
|
632 |
when /\bDESC\b/i then fragment.gsub(/\bDESC\b/i, "ASC") |
633 |
when /\bASC\b/i then fragment.gsub(/\bASC\b/i, "DESC") |
634 |
else String.new(fragment).split(',').join(' DESC,') + ' DESC' |
635 |
end
|
636 |
}.join(",")
|
637 |
end
|
638 |
|
639 |
def get_special_columns(table_name) |
640 |
special = [] |
641 |
@table_columns ||= {}
|
642 |
@table_columns[table_name] ||= columns(table_name)
|
643 |
@table_columns[table_name].each do |col| |
644 |
special << col.name if col.is_special
|
645 |
end
|
646 |
special |
647 |
end
|
648 |
|
649 |
def repair_special_columns(sql) |
650 |
special_cols = get_special_columns(get_table_name(sql)) |
651 |
for col in special_cols.to_a |
652 |
sql.gsub!(Regexp.new(" #{col.to_s} = "), " #{col.to_s} LIKE ") |
653 |
sql.gsub!(/ORDER BY #{col.to_s}/i, '') |
654 |
end
|
655 |
sql |
656 |
end
|
657 |
|
658 |
end #class SQLServerAdapter < AbstractAdapter |
659 |
end #module ConnectionAdapters |
660 |
end #module ActiveRecord |