Added migration support to SQL Server adapter (please someone do the same for Oracle and DB2) (closes #2625) [Tom Ward]

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

6 files changed, 143 insertions, 31 deletions

diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG
index 606b910c96..e228cbaeb1 100644
--- a/activerecord/CHANGELOG
+++ b/activerecord/CHANGELOG
@@ -1,5 +1,7 @@
 *SVN*
 
+* Added migration support to SQL Server adapter (please someone do the same for Oracle and DB2) #2625 [Tom Ward]
+
 * Use AR::Base.silence rather than AR::Base.logger.silence in fixtures to preserve Log4r compatibility.  #2618 [dansketcher@gmail.com]
 
 * Constraints are cloned so they can't be inadvertently modified while they're
diff --git a/activerecord/lib/active_record/base.rb b/activerecord/lib/active_record/base.rb
index fa7ed9482a..d408173c01 100755
--- a/activerecord/lib/active_record/base.rb
+++ b/activerecord/lib/active_record/base.rb
@@ -707,6 +707,22 @@ module ActiveRecord #:nodoc:
         class_name
       end
 
+      # Indicates whether the table associated with this class exists
+      def table_exists?
+        if connection.respond_to?(:tables)
+          connection.tables.include? table_name
+        else
+          # if the connection adapter hasn't implemented tables, there are two crude tests that can be
+          # used - see if getting column info raises an error, or if the number of columns returned is zero
+          begin
+            reset_column_information
+            columns.size > 0
+          rescue ActiveRecord::StatementInvalid
+            false
+          end          
+        end
+      end
+
       # Returns an array of column objects for the table associated with this class.
       def columns
         unless @columns
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
index 366192909e..6a6f368210 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
@@ -184,23 +184,25 @@ module ActiveRecord
       #   remove_index :accounts, :column => :branch_id
       # Remove the index named by_branch_party in the accounts table.
       #   remove_index :accounts, :name => :by_branch_party
+      
       def remove_index(table_name, options = {})
+        execute "DROP INDEX #{index_name(table_name, options)} ON #{table_name}"
+      end
+
+      def index_name(table_name, options) #:nodoc:
         if Hash === options # legacy support
           if options[:column]
-            index_name = "#{table_name}_#{options[:column]}_index"
+            "#{table_name}_#{options[:column]}_index"
           elsif options[:name]
-            index_name = options[:name]
+            options[:name]
           else
             raise ArgumentError, "You must specify the index name"
           end
         else
-          index_name = "#{table_name}_#{options}_index"
+          "#{table_name}_#{options}_index"
         end
-
-        execute "DROP INDEX #{index_name} ON #{table_name}"
       end
 
-
       # Returns a string of <tt>CREATE TABLE</tt> SQL statement(s) for recreating the
       # entire structure of the database.
       def structure_dump
diff --git a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
index c6e82605ee..7f8933fbb4 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
@@ -13,6 +13,10 @@ require 'active_record/connection_adapters/abstract_adapter'
 #
 # Current maintainer: Ryan Tomayko <rtomayko@gmail.com>
 #
+# Modifications (Migrations): Tom Ward <tom@popdog.net>
+# Date: 27/10/2005
+#
+
 module ActiveRecord
   class Base
     def self.sqlserver_connection(config) #:nodoc:
@@ -73,6 +77,7 @@ module ActiveRecord
         when :datetime  then cast_to_datetime(value)
         when :timestamp then cast_to_time(value)
         when :time      then cast_to_time(value)
+        when :boolean   then value == true or (value =~ /^t(rue)?$/i) == 0 or value.to_s == '1'
         else value
         end
       end
@@ -183,6 +188,10 @@ module ActiveRecord
       def adapter_name
         'SQLServer'
       end
+      
+      def supports_migrations? #:nodoc:
+        true
+      end
 
       def select_all(sql, name = nil)
         select(sql, name)
@@ -332,6 +341,63 @@ module ActiveRecord
       def create_database(name)
         execute "CREATE DATABASE #{name}"
       end
+      
+      def tables(name = nil)
+        tables = []
+        execute("SELECT table_name from information_schema.tables WHERE table_type = 'BASE TABLE'", name).each {|field| tables << field[0]}
+        tables
+      end
+      
+      def indexes(table_name, name = nil)
+        indexes = []
+        execute("EXEC sp_helpindex #{table_name}", name).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(", "))
+          end
+        end
+        indexes
+      end
+            
+      def rename_table(name, new_name)
+        execute "EXEC sp_rename '#{name}', '#{new_name}'"
+      end
+      
+      def remove_column(table_name, column_name)
+        execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
+      end 
+      
+      def rename_column(table, column, new_column_name)
+        execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
+      end
+      
+      def change_column(table_name, column_name, type, options = {}) #:nodoc:
+        sql_commands = ["ALTER TABLE #{table_name} ALTER COLUMN #{column_name} #{type_to_sql(type, options[:limit])}"]
+        if options[:default]
+          remove_default_constraint(table_name, column_name)
+          sql_commands << "ALTER TABLE #{table_name} ADD CONSTRAINT DF_#{table_name}_#{column_name} DEFAULT #{options[:default]} FOR #{column_name}"
+        end
+        sql_commands.each {|c|
+          execute(c)
+        }
+      end
+      
+      def remove_column(table_name, column_name)
+        remove_default_constraint(table_name, column_name)
+        execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
+      end
+      
+      def remove_default_constraint(table_name, column_name)
+        defaults = select "select def.name from sysobjects def, syscolumns col, sysobjects tab where col.cdefault = def.id and col.name = '#{column_name}' and tab.name = '#{table_name}' and col.id = tab.id"
+        defaults.each {|constraint|
+          execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
+        }
+      end
+      
+      def remove_index(table_name, options = {})
+        execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
+      end
 
       private
         def select(sql, name = nil)
diff --git a/activerecord/test/base_test.rb b/activerecord/test/base_test.rb
index 5d924f540a..0b8c08a27e 100755
--- a/activerecord/test/base_test.rb
+++ b/activerecord/test/base_test.rb
@@ -16,6 +16,7 @@ class CreditCard < ActiveRecord::Base; end
 class MasterCreditCard < ActiveRecord::Base; end
 class Post < ActiveRecord::Base; end
 class Computer < ActiveRecord::Base; end
+class NonExistentTable < ActiveRecord::Base; end
 
 class LoosePerson < ActiveRecord::Base
   attr_protected :credit_rating, :administrator
@@ -42,6 +43,11 @@ end
 class BasicsTest < Test::Unit::TestCase
   fixtures :topics, :companies, :developers, :projects, :computers
 
+  def test_table_exists
+    assert !NonExistentTable.table_exists?
+    assert Topic.table_exists?
+  end
+  
   def test_set_attributes
     topic = Topic.find(1)
     topic.attributes = { "title" => "Budget", "author_name" => "Jason" }
diff --git a/activerecord/test/migration_test.rb b/activerecord/test/migration_test.rb
index 4d10767991..be1dfef7b0 100644
--- a/activerecord/test/migration_test.rb
+++ b/activerecord/test/migration_test.rb
@@ -4,7 +4,6 @@ require File.dirname(__FILE__) + '/fixtures/migrations/1_people_have_last_names'
 require File.dirname(__FILE__) + '/fixtures/migrations/2_we_need_reminders'
 
 if ActiveRecord::Base.connection.supports_migrations? 
-
   class Reminder < ActiveRecord::Base; end
 
   class MigrationTest < Test::Unit::TestCase
@@ -19,6 +18,7 @@ if ActiveRecord::Base.connection.supports_migrations?
 
       Reminder.connection.drop_table("reminders") rescue nil
       Reminder.connection.drop_table("people_reminders") rescue nil
+      Reminder.connection.drop_table("prefix_reminders_suffix") rescue nil
       Reminder.reset_column_information
 
       Person.connection.remove_column("people", "last_name") rescue nil
@@ -92,11 +92,29 @@ if ActiveRecord::Base.connection.supports_migrations?
       Person.connection.drop_table :testings rescue nil
     end
   
-    def test_add_column_not_null
+    # SQL Server will not allow you to add a NOT NULL column
+    # to a table without specifying a default value, so the
+    # following test must be skipped  
+    unless current_adapter?(:SQLServerAdapter)
+      def test_add_column_not_null_without_default
+        Person.connection.create_table :testings do |t|
+          t.column :foo, :string
+        end
+        Person.connection.add_column :testings, :bar, :string, :null => false
+  
+        assert_raises(ActiveRecord::StatementInvalid) do
+          Person.connection.execute "insert into testings (foo, bar) values ('hello', NULL)"
+        end
+      ensure
+        Person.connection.drop_table :testings rescue nil
+      end
+    end
+    
+    def test_add_column_not_null_with_default
       Person.connection.create_table :testings do |t|
         t.column :foo, :string
       end
-      Person.connection.add_column :testings, :bar, :string, :null => false
+      Person.connection.add_column :testings, :bar, :string, :null => false, :default => "default"
 
       assert_raises(ActiveRecord::StatementInvalid) do
         Person.connection.execute "insert into testings (foo, bar) values ('hello', NULL)"
@@ -128,7 +146,14 @@ if ActiveRecord::Base.connection.supports_migrations?
       assert_equal String, bob.bio.class
       assert_equal Fixnum, bob.age.class
       assert_equal Time, bob.birthday.class
-      assert_equal Date, bob.favorite_day.class
+
+      if current_adapter?(:SQLServerAdapter)
+        # SQL Server doesn't differentiate between date/time
+        assert_equal Time, bob.favorite_day.class
+      else
+        assert_equal Date, bob.favorite_day.class
+      end
+
       assert_equal TrueClass, bob.male?.class
     end
 
@@ -162,11 +187,11 @@ if ActiveRecord::Base.connection.supports_migrations?
     
     def test_add_rename
       Person.delete_all
-            
-      Person.connection.add_column "people", "girlfriend", :string      
-      Person.create :girlfriend => 'bobette'      
-      
+           
       begin
+        Person.connection.add_column "people", "girlfriend", :string      
+        Person.create :girlfriend => 'bobette'      
+      
         Person.connection.rename_column "people", "girlfriend", "exgirlfriend"
       
         Person.reset_column_information      
@@ -237,8 +262,8 @@ if ActiveRecord::Base.connection.supports_migrations?
     end    
 
     def test_add_table
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
-    
+      assert !Reminder.table_exists?
+      
       WeNeedReminders.up
     
       assert Reminder.create("content" => "hello world", "remind_at" => Time.now)
@@ -250,7 +275,7 @@ if ActiveRecord::Base.connection.supports_migrations?
 
     def test_migrator
       assert !Person.column_methods_hash.include?(:last_name)
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
+      assert !Reminder.table_exists?
 
       ActiveRecord::Migrator.up(File.dirname(__FILE__) + '/fixtures/migrations/')
 
@@ -270,14 +295,13 @@ if ActiveRecord::Base.connection.supports_migrations?
 
     def test_migrator_one_up
       assert !Person.column_methods_hash.include?(:last_name)
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
-
+      assert !Reminder.table_exists?
+      
       ActiveRecord::Migrator.up(File.dirname(__FILE__) + '/fixtures/migrations/', 1)
 
       Person.reset_column_information
       assert Person.column_methods_hash.include?(:last_name)
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
-
+      assert !Reminder.table_exists?
 
       ActiveRecord::Migrator.up(File.dirname(__FILE__) + '/fixtures/migrations/', 2)
 
@@ -292,7 +316,7 @@ if ActiveRecord::Base.connection.supports_migrations?
 
       Person.reset_column_information
       assert Person.column_methods_hash.include?(:last_name)
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
+      assert !Reminder.table_exists?
     end
   
     def test_migrator_one_up_one_down
@@ -300,7 +324,7 @@ if ActiveRecord::Base.connection.supports_migrations?
       ActiveRecord::Migrator.down(File.dirname(__FILE__) + '/fixtures/migrations/', 0)
 
       assert !Person.column_methods_hash.include?(:last_name)
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
+      assert !Reminder.table_exists?
     end
     
     def test_migrator_going_down_due_to_version_target
@@ -308,7 +332,7 @@ if ActiveRecord::Base.connection.supports_migrations?
       ActiveRecord::Migrator.migrate(File.dirname(__FILE__) + '/fixtures/migrations/', 0)
 
       assert !Person.column_methods_hash.include?(:last_name)
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
+      assert !Reminder.table_exists?
 
       ActiveRecord::Migrator.migrate(File.dirname(__FILE__) + '/fixtures/migrations/')
 
@@ -359,13 +383,11 @@ if ActiveRecord::Base.connection.supports_migrations?
     end  
 
     def test_add_drop_table_with_prefix_and_suffix
-      assert_raises(ActiveRecord::StatementInvalid) { Reminder.column_methods_hash }
-
+      assert !Reminder.table_exists?
       ActiveRecord::Base.table_name_prefix = 'prefix_'
       ActiveRecord::Base.table_name_suffix = '_suffix'
       Reminder.reset_table_name
       WeNeedReminders.up
-
       assert Reminder.create("content" => "hello world", "remind_at" => Time.now)
       assert_equal "hello world", Reminder.find(:first).content
 
@@ -375,7 +397,5 @@ if ActiveRecord::Base.connection.supports_migrations?
       ActiveRecord::Base.table_name_suffix = ''
       Reminder.reset_table_name
     end
-
-  end  
-  
-end
+  end
+end
\ No newline at end of file