From f30b7a31bda74715c09759d03064f6f7c7c784b9 Mon Sep 17 00:00:00 2001 From: David Heinemeier Hansson Date: Wed, 23 Mar 2005 00:56:13 +0000 Subject: Fixed the MS SQL adapter to work with the new limit/offset approach and with binary data (still suffering from 7KB limit, though) #901 [delynnb] git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@982 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- .../connection_adapters/sqlserver_adapter.rb | 260 +++++++++++---------- 1 file changed, 133 insertions(+), 127 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb') diff --git a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb index adc7e9d98f..3c0df411aa 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb @@ -5,6 +5,27 @@ require 'active_record/connection_adapters/abstract_adapter' # Author: Joey Gibson # Date: 10/14/2004 # +# Modifications: DeLynn Berry +# Date: 3/22/2005 +# +# This adapter will ONLY work on Windows systems, since it relies on Win32OLE, which, +# to my knowledge, is only available on Window. +# +# It relies on the ADO support in the DBI module. If you are using the +# one-click installer of Ruby, then you already have DBI installed, but +# the ADO module is *NOT* installed. You will need to get the latest +# source distribution of Ruby-DBI from http://ruby-dbi.sourceforge.net/ +# unzip it, and copy the file src/lib/dbd_ado/ADO.rb to +# X:/Ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb (you will need to create +# the ADO directory). Once you've installed that file, you are ready to go. +# +# Options: +# +# * :host -- Defaults to localhost +# * :username -- Defaults to sa +# * :password -- Defaults to nothing +# * :database -- The name of the database. No default, must be provided. +# # I have tested this code on a WindowsXP Pro SP1 system, # ruby 1.8.2 (2004-07-29) [i386-mswin32], SQL Server 2000. # @@ -19,7 +40,7 @@ module ActiveRecord username = config[:username] ? config[:username].to_s : 'sa' password = config[:password].to_s - if config.has_key? :database + if config.has_key?(:database) database = config[:database] else raise ArgumentError, "No database specified. Missing argument: database." @@ -38,92 +59,110 @@ module ActiveRecord 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 + 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 + when /int|bigint|smallint|tinyint/i : :integer + when /float|double|decimal|money|numeric|real|smallmoney/i : @scale == 0 ? :integer : :float + when /datetime|smalldatetime/i : :datetime + when /timestamp/i : :timestamp + when /time/i : :time + when /text|ntext/i : :text + when /binary|image|varbinary/i : :binary + when /char|nchar|nvarchar|string|varchar/i : :string + when /bit/i : :boolean + end + end + + def type_cast(value) + return nil if value.nil? || value =~ /^\s*null\s*$/i + case type + when :string then value + when :integer then value == true || value == false ? value == true ? '1' : '0' : value.to_i + when :float then value.to_f + when :datetime then cast_to_date_or_time(value) + when :timestamp then cast_to_time(value) + when :time then cast_to_time(value) + else value + end + end + + def cast_to_date_or_time(value) + return value if value.is_a?(Date) + guess_date_or_time (value.is_a?(Time)) ? value : cast_to_time(value) + end + + def cast_to_time(value) + return value if value.is_a?(Time) + time_array = ParseDate.parsedate value + time_array[0] ||= 2000; time_array[1] ||= 1; time_array[2] ||= 1; + Time.send Base.default_timezone, *time_array + end + + def guess_date_or_time(value) + (value.hour == 0 and value.min == 0 and value.sec == 0) ? + Date.new(value.year, value.month, value.day) : value + end + + # These methods will only allow the adapter to insert binary data with a length of 7K or less + # because of a SQL Server statement length policy. + def string_to_binary(value) + value.gsub(/(\r|\n|\0|\x1a)/) do + case $1 + when "\r" + "%00" + when "\n" + "%01" + when "\0" + "%02" + when "\x1a" + "%03" + end 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 + def binary_to_string(value) + value.gsub(/(%00|%01|%02|%03)/) do + case $1 + when "%00" + "\r" + when "%01" + "\n" + when "%02\0" + "\0" + when "%03" + "\x1a" end end - # treat 0000-00-00 00:00:00 as nil - Time.send(Base.default_timezone, *time_array) rescue nil end end - # This adapter will ONLY work on Windows systems, since it relies on Win32OLE, which, - # to my knowledge, is only available on Window. - # - # It relies on the ADO support in the DBI module. If you are using the - # one-click installer of Ruby, then you already have DBI installed, but - # the ADO module is *NOT* installed. You will need to get the latest - # source distribution of Ruby-DBI from http://ruby-dbi.sourceforge.net/ - # unzip it, and copy the file src/lib/dbd_ado/ADO.rb to - # X:/Ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb (you will need to create - # the ADO directory). Once you've installed that file, you are ready to go. - # - # Options: - # - # * :host -- Defaults to localhost - # * :username -- Defaults to sa - # * :password -- Defaults to nothing - # * :database -- The name of the database. No default, must be provided. class SQLServerAdapter < AbstractAdapter + + def native_database_types + { + :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY", + :string => { :name => "varchar(255)" }, + :text => { :name => "text(16)" }, + :integer => { :name => "int(4)", :limit => 11 }, + :float => { :name => "float(8)" }, + :datetime => { :name => "datetime(8)" }, + :timestamp => { :name => "datetime(8)" }, + :time => { :name => "datetime(8)" }, + :date => { :name => "datetime(8)" }, + :binary => { :name => "image(16)" }, + :boolean => { :name => "bit(1)" } + } + end + + def adapter_name + 'SQLServer' + end + def select_all(sql, name = nil) add_limit!(sql, nil) select(sql, name) @@ -136,36 +175,22 @@ module ActiveRecord end def columns(table_name, name = nil) - sql = < e # Couldn't turn off IDENTITY_INSERT end @@ -196,11 +219,24 @@ EOL end end + def execute(sql, name = nil) + if sql =~ /^INSERT/i + insert(sql, name) + elsif sql =~ /^UPDATE|DELETE/i + log(sql, name, @connection) do |conn| + conn.execute(sql) + retVal = select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"] + end + else + log(sql, name, @connection) do |conn| + conn.execute(sql) + end + end + end + def update(sql, name = nil) execute(sql, name) - affected_rows(name) end - alias_method :delete, :update def begin_db_transaction @@ -236,8 +272,8 @@ EOL "'#{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 TrueClass then '1' + when FalseClass then '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")}'" @@ -254,11 +290,12 @@ EOL end def add_limit_with_offset!(sql, limit, offset) - raise ArgumentError, 'add_limit_with_offset! not implemented' + order_by = sql.include?("ORDER BY") ? get_order_by(sql.sub(/.*ORDER\sBY./, "")) : nil + sql.gsub!(/SELECT/i, "SELECT * FROM ( SELECT TOP #{limit} * FROM ( SELECT TOP #{limit + offset}")<<" ) AS tmp1 ORDER BY #{order_by[1]} ) AS tmp2 ORDER BY #{order_by[0]}" end def add_limit_without_offset!(sql, limit) - raise ArgumentError, 'add_limit_without_offset! not implemented' + limit.nil? ? sql : sql.gsub!(/SELECT/i, "SELECT TOP #{limit}") end def recreate_database(name) @@ -274,36 +311,18 @@ EOL execute "CREATE DATABASE #{name}" 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 adapter_name() - 'SqlServer' - end - private def select(sql, name = nil) rows = [] - log(sql, name, @connection) do |conn| conn.select_all(sql) do |row| record = {} - row.column_names.each do |col| record[col] = row[col] end - rows << record end end - rows end @@ -346,10 +365,6 @@ 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 @@ -358,15 +373,6 @@ EOL limit = limit.gsub!(/.OFFSET./i, ",").split(',') return limit[0].to_i, limit[0].to_i+limit[1].to_i end - - def affected_rows(name = nil) - sql = "SELECT @@ROWCOUNT AS AffectedRows" - log(sql, name, @connection) do |conn| - conn.select_all(sql) do |row| - return row[:AffectedRows].to_i - end - end - end end end end \ No newline at end of file -- cgit v1.2.3