diff options
| -rw-r--r-- | activerecord/CHANGELOG | 9 | ||||
| -rw-r--r-- | activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb | 189 |
2 files changed, 148 insertions, 50 deletions
diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 78bb94d60a..cc906713be 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,14 @@ *SVN* +* Fixed the SQL Server adapter on a bunch of issues #667 [DeLynn] + + 1. Created a new columns method that is much cleaner. + 2. Corrected a problem with the select and select_all methods + that didn't account for the LIMIT clause being passed into raw SQL statements. + 3. Implemented the string_to_time method in order to create proper instances of the time class. + 4. Added logic to the simplified_type method that allows the database to specify the scale of float data. + 5. Adjusted the quote_column_name to account for the fact that MS SQL is bothered by a forward slash in the data string. + * Fixed that the dynamic finder like find_all_by_something_boolean(false) didn't work #649 [lmarlow@yahoo.com] * Added validates_each that validates each specified attribute against a block #610 [bitsweat]. Example: diff --git a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb index b4c9323b80..84293736e3 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb @@ -23,7 +23,7 @@ require 'active_record/connection_adapters/abstract_adapter' # with User Id replaced with your proper login, and Password with your # password. # -# I have tested this code on a Windows XP Pro SP1 system, +# I have tested this code on a WindowsXP Pro SP1 system, # ruby 1.8.2 (2004-07-29) [i386-mswin32], SQL Server 2000. # module ActiveRecord @@ -52,58 +52,112 @@ module ActiveRecord module ConnectionAdapters class ColumnWithIdentity < Column# :nodoc: - attr_reader :identity + attr_reader :identity, :scale - def initialize(name, default, sql_type = nil, is_identity = false) + def initialize(name, default, sql_type = nil, is_identity = false, scale_value = 0) super(name, default, sql_type) + @scale = scale_value @identity = is_identity + end + + def binary_to_string(value) + value + end + + def string_to_binary(value) + value + end + + def simplified_type(field_type) + case field_type + when /int/i + :integer + when /float|double|decimal|numeric/i + if @scale == 0 + :integer + else + :float + nil + end + when /datetime/i + :datetime + when /timestamp/i + :timestamp + when /time/i + :time + when /date/i + :date + when /clob|text|ntext/i + :text + when /blob|binary|image/i + :binary + when /char|string/i + :string + when /boolean|bit/i + :boolean + end end + + def string_to_time(string) + return string if string.is_a?(Time) + time_array = ParseDate.parsedate(string, true) + time_array.each_index do |i| + case i + when 0 + time_array[i] = time_array[i].nil? ? "2000" : time_array[i].to_s + when 1 + time_array[i] = time_array[i].nil? ? "Jan" : time_array[i].to_s + when 2 + time_array[i] = time_array[i].nil? ? "1" : time_array[i].to_s + when 3 + time_array[i] = time_array[i].nil? ? "0" : time_array[i].to_s + when 4 + time_array[i] = time_array[i].nil? ? "0" : time_array[i].to_s + when 5 + time_array[i] = time_array[i].nil? ? "0" : time_array[i].to_s + end + end + # treat 0000-00-00 00:00:00 as nil + Time.send(Base.default_timezone, *time_array) rescue nil + end + end class SQLServerAdapter < AbstractAdapter # :nodoc: - def quote_column_name(name) - "[#{name}]" - end def select_all(sql, name = nil) + add_limit!(sql, nil) select(sql, name) end def select_one(sql, name = nil) + add_limit!(sql, nil) result = select(sql, name) result.nil? ? nil : result.first end def columns(table_name, name = nil) sql = <<EOL -SELECT s.name AS TableName, c.id AS ColId, c.name AS ColName, t.name AS ColType, c.length AS Length, -c.AutoVal AS IsIdentity, -c.cdefault AS DefaultId, com.text AS DefaultValue -FROM syscolumns AS c -JOIN systypes AS t ON (c.xtype = t.xtype AND c.usertype = t.usertype) -JOIN sysobjects AS s ON (c.id = s.id) -LEFT OUTER JOIN syscomments AS com ON (c.cdefault = com.id) -WHERE s.name = '#{table_name}' +SELECT +COLUMN_NAME as ColName, +COLUMN_DEFAULT as DefaultValue, +DATA_TYPE as ColType, +COL_LENGTH('#{table_name}', COLUMN_NAME) as Length, +COLUMNPROPERTY(OBJECT_ID('#{table_name}'), COLUMN_NAME, 'IsIdentity') as IsIdentity, +NUMERIC_SCALE as Scale +FROM INFORMATION_SCHEMA.columns +WHERE TABLE_NAME = '#{table_name}' EOL - columns = [] - - log(sql, name, @connection) do |conn| - conn.select_all(sql) do |row| - default_value = row[:DefaultValue] - - if default_value =~ /null/i - default_value = nil - else - default_value =~ /\(([^)]+)\)/ - default_value = $1 - end - col = ColumnWithIdentity.new(row[:ColName], default_value, "#{row[:ColType]}(#{row[:Length]})", row[:IsIdentity] != nil) - - columns << col - end - end + result = nil + # Uncomment if you want to have the Columns select statment logged. + # Personnally, I think it adds unneccessary SQL statement bloat to the log. + # If you do uncomment, make sure to comment the "result" line that follows + log(sql, name, @connection) { |conn| result = conn.select_all(sql) } + #result = @connection.select_all(sql) + columns = [] + result.each { |field| columns << ColumnWithIdentity.new(field[:ColName], field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/ ? nil : field[:DefaultValue], "#{field[:ColType]}(#{field[:Length]})", field[:IsIdentity] == 1 ? true : false, field[:Scale]) } columns end @@ -122,7 +176,7 @@ EOL execute enable_identity_insert(table_name, true) ii_enabled = true rescue Exception => e - # Couldn't turn on IDENTITY_INSERT + # Coulnd't turn on IDENTITY_INSERT end end end @@ -144,16 +198,6 @@ EOL end end - def execute(sql, name = nil) - if sql =~ /^INSERT/i - insert(sql, name) - else - log(sql, name, @connection) do |conn| - conn.execute(sql) - end - end - end - def update(sql, name = nil) execute(sql, name) affected_rows(name) @@ -185,6 +229,47 @@ EOL end end + def quote(value, column = nil) + case value + when String + if column && column.type == :binary + "'#{quote_string(column.string_to_binary(value))}'" + else + "'#{quote_string(value)}'" + end + when NilClass then "NULL" + when TrueClass then (column && column.type == :boolean ? "'t'" : "1") + when FalseClass then (column && column.type == :boolean ? "'f'" : "0") + when Float, Fixnum, Bignum then value.to_s + when Date then "'#{value.to_s}'" + when Time, DateTime then "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'" + else "'#{quote_string(value.to_yaml)}'" + end + end + + def quote_string(s) + s.gsub(/\'/, "''") + end + + def quote_column_name(name) + "[#{name}]" + end + + def add_limit!(sql, limit) + if sql =~ /LIMIT/i + limit = sql.slice!(/LIMIT.*/).gsub(/LIMIT.(.*)$/, '\1') + end + if !limit.nil? + limit_amount = limit.to_s.include?("OFFSET") ? get_offset_amount(limit) : Array.new([limit]) + order_by = sql.include?("ORDER BY") ? get_order_by(sql.sub(/.*ORDER\sBY./, "")) : nil + if limit_amount.size == 2 + sql.gsub!(/SELECT/i, "SELECT * FROM ( SELECT TOP #{limit_amount[0]} * FROM ( SELECT TOP #{limit_amount[1]}")<<" ) AS tmp1 ORDER BY #{order_by[1]} ) AS tmp2 ORDER BY #{order_by[0]}" + else + sql.gsub!(/SELECT/i, "SELECT TOP #{limit_amount[0]}") + end + end + end + def recreate_database(name) drop_database(name) create_database(name) @@ -198,14 +283,14 @@ EOL execute "CREATE DATABASE #{name}" end - def add_limit!(sql, limit) - limit_amount = limit.to_s.include?("OFFSET") ? get_offset_amount(limit) : Array.new([limit]) - order_by = sql.include?("ORDER BY") ? get_order_by(sql.sub(/.*ORDER\sBY./, "")) : nil - if limit_amount.size == 2 - sql.gsub!(/SELECT/i, "SELECT * FROM ( SELECT TOP #{limit_amount[0]} * FROM ( SELECT TOP #{limit_amount[1]}")<<" ) AS tmp1 ORDER BY #{order_by[1]} ) AS tmp2 ORDER BY #{order_by[0]}" + def execute(sql, name = nil) + if sql =~ /^INSERT/i + insert(sql, name) else - sql.gsub!(/SELECT/i, "SELECT TOP #{limit_amount[0]}") - end + log(sql, name, @connection) do |conn| + conn.execute(sql) + end + end end private @@ -266,6 +351,10 @@ EOL return sql =~ /[\(\.\,]\s*#{col}/ end + def query_contains_text_column(sql, col) + + end + def get_order_by(sql) return sql, sql.gsub(/\s*DESC\s*/, "").gsub(/\s*ASC\s*/, " DESC") end @@ -285,4 +374,4 @@ EOL end end end -end +end
\ No newline at end of file |