SQLServer: recognize real column type as Ruby float, correctly schema-dump tables with no indexes or descending indexes. References #7057, #7703. Closes #7333.

git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@6297 5ecf4fe2-1ee6-0310-87b1-e25e094e27de

4 files changed, 47 insertions, 12 deletions

diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG
index 93ae6e9c36..faa521f5ee 100644
--- a/activerecord/CHANGELOG
+++ b/activerecord/CHANGELOG
@@ -1,5 +1,9 @@
 *SVN*
 
+* SQLServer: correctly schema-dump tables with no indexes or descending indexes.  #7333, #7703 [Jakob S, Tom Ward]
+
+* SQLServer: recognize real column type as Ruby float.  #7057 [sethladd, Tom Ward]
+
 * Added fixtures :all as a way of loading all fixtures in the fixture directory at once #7214 [manfred]
 
 * Added database connection as a yield parameter to ActiveRecord::Base.transaction so you can manually rollback [DHH]. Example:
diff --git a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
index e85a826942..5463bb645f 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
@@ -41,7 +41,7 @@ module ActiveRecord
         raise ArgumentError, "Missing Database. Argument ':database' must be set in order for this adapter to work." unless config.has_key?(:database)
         database  = config[:database]
         host      = config[:host] ? config[:host].to_s : 'localhost'
-        driver_url = "DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};"
+        driver_url = "DBI:ADO:Provider=SQLOLEDB;Data Source=#{host};Initial Catalog=#{database};User ID=#{username};Password=#{password};"
       end
       conn      = DBI.connect(driver_url, username, password)
       conn["AutoCommit"] = autocommit
@@ -64,6 +64,7 @@ module ActiveRecord
 
       def simplified_type(field_type)
         case field_type
+          when /real/i              then :float
           when /money/i             then :decimal
           when /image/i             then :binary
           when /bit/i               then :boolean
@@ -168,11 +169,13 @@ module ActiveRecord
     # * <tt>:mode</tt>      -- ADO or ODBC. Defaults to ADO.
     # * <tt>:username</tt>  -- Defaults to sa.
     # * <tt>:password</tt>  -- Defaults to empty string.
+    # * <tt>:windows_auth</tt> -- Defaults to "User ID=#{username};Password=#{password}"
     #
     # ADO specific options:
     #
     # * <tt>:host</tt>      -- Defaults to localhost.
     # * <tt>:database</tt>  -- The name of the database. No default, must be provided.
+    # * <tt>:windows_auth</tt> -- Use windows authentication instead of username/password.
     #
     # ODBC specific options:                   
     #
@@ -419,9 +422,9 @@ module ActiveRecord
 
       def tables(name = nil)
         execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth|
-          sth.inject([]) do |tables, field|
+          result = sth.inject([]) do |tables, field|
             table_name = field[0]
-            tables << table_name unless table_name == 'dtproperties'
+            tables << table_name unless table_name == 'dtproperties'            
             tables
           end
         end
@@ -430,18 +433,20 @@ module ActiveRecord
       def indexes(table_name, name = nil)
         ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
         indexes = []        
-        execute("EXEC sp_helpindex '#{table_name}'", name) do |sth|
-          sth.each do |index| 
-            unique = index[1] =~ /unique/
-            primary = index[1] =~ /primary key/
-            if !primary
-              indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", "))
+        execute("EXEC sp_helpindex '#{table_name}'", name) do |handle|
+          if handle.column_info.any?
+            handle.each do |index| 
+              unique = index[1] =~ /unique/
+              primary = index[1] =~ /primary key/
+              if !primary
+                indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", ").map {|e| e.gsub('(-)','')})
+              end
             end
           end
         end
         indexes
-        ensure
-          ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
+      ensure
+        ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
       end
             
       def rename_table(name, new_name)
@@ -473,6 +478,11 @@ module ActiveRecord
         }
       end
       
+      def change_column_default(table_name, column_name, default)
+        remove_default_constraint(table_name, column_name)
+        execute "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{quote(default, column_name)} FOR #{column_name}"   
+      end
+      
       def remove_column(table_name, column_name)
         remove_check_constraints(table_name, column_name)
         remove_default_constraint(table_name, column_name)
diff --git a/activerecord/test/adapter_test_sqlserver.rb b/activerecord/test/adapter_test_sqlserver.rb
index bf74671209..548668d5e7 100644
--- a/activerecord/test/adapter_test_sqlserver.rb
+++ b/activerecord/test/adapter_test_sqlserver.rb
@@ -4,6 +4,8 @@ require 'fixtures/post'
 require 'fixtures/task'

 

 class SqlServerAdapterTest < Test::Unit::TestCase

+  class TableWithRealColumn < ActiveRecord::Base; end

+

   fixtures :posts, :tasks

 

   def setup

@@ -11,7 +13,11 @@ class SqlServerAdapterTest < Test::Unit::TestCase
   end

 

   def teardown

-    @connection.execute("SET LANGUAGE us_english")

+    @connection.execute("SET LANGUAGE us_english") rescue nil

+  end

+

+  def test_real_column_has_float_type

+    assert_equal :float, TableWithRealColumn.columns_hash["real_number"].type

   end

 

   # SQL Server 2000 has a bug where some unambiguous date formats are not 

@@ -24,6 +30,14 @@ class SqlServerAdapterTest < Test::Unit::TestCase
     end

   end

 

+  def test_indexes_with_descending_order

+    # Make sure we have an index with descending order

+    @connection.execute "CREATE INDEX idx_credit_limit ON accounts (credit_limit DESC)" rescue nil

+    assert_equal ["credit_limit"], @connection.indexes('accounts').first.columns

+  ensure

+    @connection.execute "DROP INDEX accounts.idx_credit_limit"

+  end

+

   def test_execute_without_block_closes_statement

     assert_all_statements_used_are_closed do

       @connection.execute("SELECT 1")

diff --git a/activerecord/test/fixtures/db_definitions/schema.rb b/activerecord/test/fixtures/db_definitions/schema.rb
index b5d7704d2e..3dc9a9b8a3 100644
--- a/activerecord/test/fixtures/db_definitions/schema.rb
+++ b/activerecord/test/fixtures/db_definitions/schema.rb
@@ -64,4 +64,11 @@ ActiveRecord::Schema.define do
       t.column :name, :string
     end
   end
+  
+  # For sqlserver 2000+, ensure real columns can be used
+  if adapter_name.starts_with?("SQLServer")
+    create_table :table_with_real_columns, :force => true do |t|
+      t.column :real_number, :real
+    end
+  end
 end