| 1 |
1 |
require 'active_record/connection_adapters/abstract_adapter'
|
| 2 |
2 |
|
| 3 |
|
require 'base64'
|
| 4 |
3 |
require 'bigdecimal'
|
| 5 |
4 |
require 'bigdecimal/util'
|
| 6 |
5 |
|
| ... | ... | |
| 42 |
41 |
raise ArgumentError, "Missing Database. Argument ':database' must be set in order for this adapter to work." unless config.has_key?(:database)
|
| 43 |
42 |
database = config[:database]
|
| 44 |
43 |
host = config[:host] ? config[:host].to_s : 'localhost'
|
| 45 |
|
driver_url = "DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};User ID=#{username};Password=#{password};"
|
|
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
|
| 46 |
49 |
end
|
| 47 |
50 |
conn = DBI.connect(driver_url, username, password)
|
| 48 |
51 |
conn["AutoCommit"] = autocommit
|
| ... | ... | |
| 60 |
63 |
@is_special = sql_type =~ /text|ntext|image/i
|
| 61 |
64 |
# TODO: check ok to remove @scale = scale_value
|
| 62 |
65 |
# SQL Server only supports limits on *char and float types
|
| 63 |
|
@limit = nil unless @type == :float or @type == :string
|
|
66 |
@limit = nil unless @type == :string
|
| 64 |
67 |
end
|
| 65 |
68 |
|
| 66 |
69 |
def simplified_type(field_type)
|
| 67 |
70 |
case field_type
|
| 68 |
|
when /real/i then :float
|
| 69 |
71 |
when /money/i then :decimal
|
| 70 |
72 |
when /image/i then :binary
|
| 71 |
73 |
when /bit/i then :boolean
|
| ... | ... | |
| 79 |
81 |
case type
|
| 80 |
82 |
when :datetime then cast_to_datetime(value)
|
| 81 |
83 |
when :timestamp then cast_to_time(value)
|
| 82 |
|
when :time then cast_to_time(value)
|
| 83 |
|
when :date then cast_to_datetime(value)
|
| 84 |
84 |
when :boolean then value == true or (value =~ /^t(rue)?$/i) == 0 or value.to_s == '1'
|
| 85 |
85 |
else super
|
| 86 |
86 |
end
|
| 87 |
87 |
end
|
| 88 |
|
|
|
88 |
|
| 89 |
89 |
def cast_to_time(value)
|
| 90 |
90 |
return value if value.is_a?(Time)
|
| 91 |
91 |
time_array = ParseDate.parsedate(value)
|
| ... | ... | |
| 105 |
105 |
|
| 106 |
106 |
if value.is_a?(DateTime)
|
| 107 |
107 |
return Time.mktime(value.year, value.mon, value.day, value.hour, value.min, value.sec)
|
| 108 |
|
#return DateTime.new(value.year, value.mon, value.day, value.hour, value.min, value.sec)
|
| 109 |
108 |
end
|
| 110 |
109 |
|
| 111 |
110 |
return cast_to_time(value) if value.is_a?(Date) or value.is_a?(String) rescue nil
|
| ... | ... | |
| 114 |
113 |
|
| 115 |
114 |
# TODO: Find less hack way to convert DateTime objects into Times
|
| 116 |
115 |
|
| 117 |
|
def self.string_to_time(value)
|
| 118 |
|
if value.is_a?(DateTime)
|
| 119 |
|
return Time.mktime(value.year, value.mon, value.day, value.hour, value.min, value.sec)
|
| 120 |
|
else
|
| 121 |
|
super
|
|
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
|
| 122 |
134 |
end
|
| 123 |
135 |
end
|
| 124 |
136 |
|
| 125 |
|
# These methods will only allow the adapter to insert binary data with a length of 7K or less
|
| 126 |
|
# because of a SQL Server statement length policy.
|
| 127 |
|
def self.string_to_binary(value)
|
| 128 |
|
Base64.encode64(value)
|
| 129 |
|
end
|
| 130 |
|
|
| 131 |
|
def self.binary_to_string(value)
|
| 132 |
|
Base64.decode64(value)
|
| 133 |
|
end
|
|
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
|
| 134 |
147 |
end
|
| 135 |
148 |
|
| 136 |
149 |
# In ADO mode, this adapter will ONLY work on Windows systems,
|
| ... | ... | |
| 157 |
170 |
# * <tt>:mode</tt> -- ADO or ODBC. Defaults to ADO.
|
| 158 |
171 |
# * <tt>:username</tt> -- Defaults to sa.
|
| 159 |
172 |
# * <tt>:password</tt> -- Defaults to empty string.
|
| 160 |
|
# * <tt>:windows_auth</tt> -- Defaults to "User ID=#{username};Password=#{password}"
|
| 161 |
173 |
#
|
| 162 |
174 |
# ADO specific options:
|
| 163 |
175 |
#
|
| 164 |
176 |
# * <tt>:host</tt> -- Defaults to localhost.
|
| 165 |
177 |
# * <tt>:database</tt> -- The name of the database. No default, must be provided.
|
| 166 |
|
# * <tt>:windows_auth</tt> -- Use windows authentication instead of username/password.
|
| 167 |
178 |
#
|
| 168 |
179 |
# ODBC specific options:
|
| 169 |
180 |
#
|
| ... | ... | |
| 186 |
197 |
{
|
| 187 |
198 |
:primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
|
| 188 |
199 |
:string => { :name => "varchar", :limit => 255 },
|
| 189 |
|
:text => { :name => "text" },
|
|
200 |
:text => { :name => "varchar", :limit => "MAX"},
|
| 190 |
201 |
:integer => { :name => "int" },
|
| 191 |
|
:float => { :name => "float", :limit => 8 },
|
|
202 |
:float => { :name => "float" },
|
| 192 |
203 |
:decimal => { :name => "decimal" },
|
| 193 |
204 |
:datetime => { :name => "datetime" },
|
| 194 |
205 |
:timestamp => { :name => "datetime" },
|
| 195 |
|
:time => { :name => "datetime" },
|
| 196 |
|
:date => { :name => "datetime" },
|
| 197 |
|
:binary => { :name => "image"},
|
|
206 |
:time => { :name => "time" },
|
|
207 |
:date => { :name => "date" },
|
|
208 |
:binary => { :name => "varchar", :limit => "MAX"},
|
| 198 |
209 |
:boolean => { :name => "bit"}
|
| 199 |
210 |
}
|
| 200 |
211 |
end
|
| ... | ... | |
| 244 |
255 |
@connection.disconnect rescue nil
|
| 245 |
256 |
end
|
| 246 |
257 |
|
| 247 |
|
def select_rows(sql, name = nil)
|
|
258 |
def select_rows(sql, name = nil)
|
| 248 |
259 |
rows = []
|
| 249 |
260 |
repair_special_columns(sql)
|
| 250 |
261 |
log(sql, name) do
|
| ... | ... | |
| 261 |
272 |
end
|
| 262 |
273 |
end
|
| 263 |
274 |
rows
|
| 264 |
|
end
|
| 265 |
|
|
| 266 |
|
def columns(table_name, name = nil)
|
|
275 |
end
|
|
276 |
|
|
277 |
def columns(table_name, name = nil)
|
| 267 |
278 |
return [] if table_name.blank?
|
| 268 |
279 |
table_name = table_name.to_s if table_name.is_a?(Symbol)
|
| 269 |
280 |
table_name = table_name.split('.')[-1] unless table_name.nil?
|
| 270 |
281 |
table_name = table_name.gsub(/[\[\]]/, '')
|
| 271 |
282 |
sql = %Q{
|
| 272 |
|
SELECT
|
| 273 |
|
cols.COLUMN_NAME as ColName,
|
| 274 |
|
cols.COLUMN_DEFAULT as DefaultValue,
|
| 275 |
|
cols.NUMERIC_SCALE as numeric_scale,
|
| 276 |
|
cols.NUMERIC_PRECISION as numeric_precision,
|
| 277 |
|
cols.DATA_TYPE as ColType,
|
| 278 |
|
cols.IS_NULLABLE As IsNullable,
|
| 279 |
|
COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,
|
| 280 |
|
COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') as IsIdentity,
|
| 281 |
|
cols.NUMERIC_SCALE as Scale
|
| 282 |
|
FROM INFORMATION_SCHEMA.COLUMNS cols
|
| 283 |
|
WHERE cols.TABLE_NAME = '#{table_name}'
|
|
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
|
| 284 |
303 |
}
|
| 285 |
304 |
# Comment out if you want to have the Columns select statment logged.
|
| 286 |
305 |
# Personally, I think it adds unnecessary bloat to the log.
|
| ... | ... | |
| 289 |
308 |
#result = @connection.select_all(sql)
|
| 290 |
309 |
columns = []
|
| 291 |
310 |
result.each do |field|
|
| 292 |
|
default = field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/i ? nil : field[:DefaultValue]
|
|
311 |
default = field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null|NULL/ ? nil : field[:DefaultValue]
|
| 293 |
312 |
if field[:ColType] =~ /numeric|decimal/i
|
| 294 |
313 |
type = "#{field[:ColType]}(#{field[:numeric_precision]},#{field[:numeric_scale]})"
|
| 295 |
314 |
else
|
| ... | ... | |
| 301 |
320 |
end
|
| 302 |
321 |
columns
|
| 303 |
322 |
end
|
| 304 |
|
|
| 305 |
|
def empty_insert_statement(table_name)
|
| 306 |
|
"INSERT INTO #{table_name} DEFAULT VALUES"
|
| 307 |
|
end
|
| 308 |
323 |
|
| 309 |
|
def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
|
| 310 |
|
super || select_value("SELECT @@IDENTITY AS Ident")
|
|
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"]
|
| 311 |
327 |
end
|
| 312 |
328 |
|
| 313 |
|
def update_sql(sql, name = nil)
|
| 314 |
|
autoCommiting = @connection["AutoCommit"]
|
| 315 |
|
begin
|
| 316 |
|
begin_db_transaction if autoCommiting
|
| 317 |
|
execute(sql, name)
|
| 318 |
|
affectedRows = select_value("SELECT @@ROWCOUNT AS AffectedRows")
|
| 319 |
|
commit_db_transaction if autoCommiting
|
| 320 |
|
affectedRows
|
| 321 |
|
rescue
|
| 322 |
|
rollback_db_transaction if autoCommiting
|
| 323 |
|
raise
|
| 324 |
|
end
|
|
329 |
def update(sql, name = nil)
|
|
330 |
execute(sql, name) do |handle|
|
|
331 |
handle.rows
|
|
332 |
end || select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]
|
| 325 |
333 |
end
|
|
334 |
|
|
335 |
alias_method :delete, :update
|
| 326 |
336 |
|
| 327 |
337 |
def execute(sql, name = nil)
|
| 328 |
338 |
if sql =~ /^\s*INSERT/i && (table_name = query_requires_identity_insert?(sql))
|
| ... | ... | |
| 366 |
376 |
case value
|
| 367 |
377 |
when TrueClass then '1'
|
| 368 |
378 |
when FalseClass then '0'
|
| 369 |
|
else
|
| 370 |
|
if value.acts_like?(:time)
|
| 371 |
|
"'#{value.strftime("%Y%m%d %H:%M:%S")}'"
|
| 372 |
|
elsif value.acts_like?(:date)
|
| 373 |
|
"'#{value.strftime("%Y%m%d")}'"
|
| 374 |
|
else
|
| 375 |
|
super
|
| 376 |
|
end
|
|
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
|
| 377 |
382 |
end
|
| 378 |
383 |
end
|
| 379 |
384 |
|
| ... | ... | |
| 381 |
386 |
string.gsub(/\'/, "''")
|
| 382 |
387 |
end
|
| 383 |
388 |
|
| 384 |
|
def quote_column_name(name)
|
| 385 |
|
"[#{name}]"
|
|
389 |
def quoted_true
|
|
390 |
"1"
|
| 386 |
391 |
end
|
| 387 |
392 |
|
| 388 |
|
def add_limit_offset!(sql, options)
|
| 389 |
|
if options[:limit] and options[:offset]
|
| 390 |
|
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
|
| 391 |
|
if (options[:limit] + options[:offset]) >= total_rows
|
| 392 |
|
options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0
|
| 393 |
|
end
|
| 394 |
|
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]} ")
|
| 395 |
|
sql << ") AS tmp1"
|
| 396 |
|
if options[:order]
|
| 397 |
|
order = options[:order].split(',').map do |field|
|
| 398 |
|
parts = field.split(" ")
|
| 399 |
|
tc = parts[0]
|
| 400 |
|
if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query
|
| 401 |
|
tc.gsub!(/\./, '\\.\\[')
|
| 402 |
|
tc << '\\]'
|
| 403 |
|
end
|
| 404 |
|
if sql =~ /#{tc} AS (t\d_r\d\d?)/
|
| 405 |
|
parts[0] = $1
|
| 406 |
|
elsif parts[0] =~ /\w+\.(\w+)/
|
| 407 |
|
parts[0] = $1
|
| 408 |
|
end
|
| 409 |
|
parts.join(' ')
|
| 410 |
|
end.join(', ')
|
| 411 |
|
sql << " ORDER BY #{change_order_direction(order)}) AS tmp2 ORDER BY #{order}"
|
| 412 |
|
else
|
| 413 |
|
sql << " ) AS tmp2"
|
| 414 |
|
end
|
| 415 |
|
elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
|
| 416 |
|
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
|
| 417 |
|
"SELECT#{$1} TOP #{options[:limit]}"
|
| 418 |
|
end unless options[:limit].nil?
|
| 419 |
|
end
|
|
393 |
def quoted_false
|
|
394 |
"0"
|
| 420 |
395 |
end
|
| 421 |
396 |
|
| 422 |
|
def add_lock!(sql, options)
|
| 423 |
|
@logger.info "Warning: SQLServer :lock option '#{options[:lock].inspect}' not supported" if @logger && options.has_key?(:lock)
|
| 424 |
|
sql
|
|
397 |
def quote_column_name(name)
|
|
398 |
"[#{name}]"
|
| 425 |
399 |
end
|
| 426 |
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 |
|
| 427 |
410 |
def recreate_database(name)
|
| 428 |
411 |
drop_database(name)
|
| 429 |
412 |
create_database(name)
|
| ... | ... | |
| 443 |
426 |
|
| 444 |
427 |
def tables(name = nil)
|
| 445 |
428 |
execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth|
|
| 446 |
|
result = sth.inject([]) do |tables, field|
|
|
429 |
sth.inject([]) do |tables, field|
|
| 447 |
430 |
table_name = field[0]
|
| 448 |
|
tables << table_name unless table_name == 'dtproperties'
|
|
431 |
tables << table_name unless table_name == 'dtproperties'
|
| 449 |
432 |
tables
|
| 450 |
433 |
end
|
| 451 |
434 |
end
|
| ... | ... | |
| 454 |
437 |
def indexes(table_name, name = nil)
|
| 455 |
438 |
ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
|
| 456 |
439 |
indexes = []
|
| 457 |
|
execute("EXEC sp_helpindex '#{table_name}'", name) do |handle|
|
| 458 |
|
if handle.column_info.any?
|
| 459 |
|
handle.each do |index|
|
| 460 |
|
unique = index[1] =~ /unique/
|
| 461 |
|
primary = index[1] =~ /primary key/
|
| 462 |
|
if !primary
|
| 463 |
|
indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", ").map {|e| e.gsub('(-)','')})
|
| 464 |
|
end
|
|
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(", "))
|
| 465 |
446 |
end
|
| 466 |
447 |
end
|
| 467 |
448 |
end
|
| 468 |
449 |
indexes
|
| 469 |
|
ensure
|
| 470 |
|
ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
|
|
450 |
ensure
|
|
451 |
ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
|
| 471 |
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
|
| 472 |
459 |
|
| 473 |
460 |
def rename_table(name, new_name)
|
| 474 |
461 |
execute "EXEC sp_rename '#{name}', '#{new_name}'"
|
| 475 |
462 |
end
|
| 476 |
|
|
|
463 |
|
|
464 |
# Adds a new column to the named table.
|
|
465 |
# See TableDefinition#column for details of the options you can use.
|
| 477 |
466 |
def add_column(table_name, column_name, type, options = {})
|
| 478 |
|
add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
|
|
467 |
add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
|
| 479 |
468 |
add_column_options!(add_column_sql, options)
|
| 480 |
469 |
# TODO: Add support to mimic date columns, using constraints to mark them as such in the database
|
| 481 |
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
|
| ... | ... | |
| 487 |
476 |
end
|
| 488 |
477 |
|
| 489 |
478 |
def change_column(table_name, column_name, type, options = {}) #:nodoc:
|
| 490 |
|
sql = "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
|
| 491 |
|
sql << " NOT NULL" if options[:null] == false
|
| 492 |
|
sql_commands = [sql]
|
|
479 |
sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"]
|
| 493 |
480 |
if options_include_default?(options)
|
| 494 |
481 |
remove_default_constraint(table_name, column_name)
|
| 495 |
|
sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(options[:default], options[:column])} FOR #{quote_column_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}"
|
| 496 |
483 |
end
|
| 497 |
484 |
sql_commands.each {|c|
|
| 498 |
485 |
execute(c)
|
| 499 |
486 |
}
|
| 500 |
487 |
end
|
| 501 |
488 |
|
| 502 |
|
def change_column_default(table_name, column_name, default)
|
| 503 |
|
remove_default_constraint(table_name, column_name)
|
| 504 |
|
execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default, column_name)} FOR #{quote_column_name(column_name)}"
|
| 505 |
|
end
|
| 506 |
|
|
| 507 |
489 |
def remove_column(table_name, column_name)
|
| 508 |
490 |
remove_check_constraints(table_name, column_name)
|
| 509 |
491 |
remove_default_constraint(table_name, column_name)
|
| 510 |
|
execute "ALTER TABLE [#{table_name}] DROP COLUMN #{quote_column_name(column_name)}"
|
|
492 |
execute "ALTER TABLE [#{table_name}] DROP COLUMN [#{column_name}]"
|
| 511 |
493 |
end
|
| 512 |
494 |
|
| 513 |
495 |
def remove_default_constraint(table_name, column_name)
|
| ... | ... | |
| 533 |
515 |
private
|
| 534 |
516 |
def select(sql, name = nil)
|
| 535 |
517 |
repair_special_columns(sql)
|
| 536 |
|
|
|
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)
|
| 537 |
535 |
result = []
|
| 538 |
536 |
execute(sql) do |handle|
|
| 539 |
537 |
handle.each do |row|
|
| ... | ... | |
| 550 |
548 |
result
|
| 551 |
549 |
end
|
| 552 |
550 |
|
|
551 |
def query_has_limit_and_offset?(sql)
|
|
552 |
match = sql.match(/#limit_offset_temp -- limit => (\d+) offset => (\d+)/)
|
|
553 |
end
|
|
554 |
|
| 553 |
555 |
# Turns IDENTITY_INSERT ON for table during execution of the block
|
| 554 |
556 |
# N.B. This sets the state of IDENTITY_INSERT to OFF after the
|
| 555 |
557 |
# block has been executed without regard to its previous state
|