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