From 57c32677c929c84b7988a843d407b9105a3fabd3 Mon Sep 17 00:00:00 2001 From: David Heinemeier Hansson Date: Sat, 19 Feb 2005 00:05:40 +0000 Subject: Fixed the SQL Server adapter on a bunch of issues #667 [DeLynn] git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@675 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/CHANGELOG | 9 + .../connection_adapters/sqlserver_adapter.rb | 189 +++++++++++++++------ 2 files changed, 148 insertions(+), 50 deletions(-) (limited to 'activerecord') 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 = < 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 -- cgit v1.2.3