The SQL Server adapter for rails is back for ActiveRecord 2.2 and up! We are currently passing all tests and hope to continue to do so moving forward.
-
Enabled support for DDL transactions.
-
Micro second support. Time#usec is automatically converted to SQL Server’s 3.33 millisecond limitation.
-
Datetime data types before type casting are represented correctly. For example: 1998-01-01 23:59:59.997
-
Implementation for #disable_referential_integrity used by ActiveRecord’s Fixtures class.
-
Pessimistic locking suppot. See the #add_lock! method for details.
-
Enabled #case_sensitive_equality_operator used by unique validations.
-
Unicode character support for nchar, nvarchar and ntext data types.
Both SQL Server 2000 and 2005 do not have native data types for just ‘date’ or ‘time’, it only has ‘datetime’. To pass the ActiveRecord tests we implemented two simple class methods that can teach your models to coerce column information to be cast correctly. Simply past a list of symbols to either the coerce_sqlserver_date or coerce_sqlserver_time methods that correspond to ‘datetime’ columns that need to be cast correctly.
class Topic < ActiveRecord::Base coerce_sqlserver_date :last_read coerce_sqlserver_time :bonus_time end
This implementation has some limitations. To date we can only coerce date/time types for models that conform to the expected ActiveRecord class to table naming convention. So a table of ‘foo_bar_widgets’ will look for coerced types in the FooBarWidget class if it exists.
Currently the following custom data types have been tested for schema definitions.
-
char
-
nchar
-
nvarchar
-
ntext
-
varchar(max) for SQL Server 2005 only.
-
nvarchar(max) for SQL Server 2005 only.
For example:
create_table :sql_server_custom_types, :force => true do |t| t.column :ten_code, :char, :limit => 10 t.column :ten_code_utf8, :nchar, :limit => 10 t.column :title_utf8, :nvarchar t.column :body, :varchar_max # Creates varchar(max) t.column :body_utf8, :ntext t.column :body2_utf8, :nvarchar_max # Creates nvarchar(max) end
Manually creating a varchar(max) on SQL Server 2005 is not necessary since this is the default type created when specifying a :text field. As time goes on we will be testing other SQL Server specific data types are handled correctly when created in a migration.
To pass the ActiveRecord tests we had to implement an class accessor for the native type created for :text columns. By default any :text column created by migrations will create these native types.
-
SQL Server 2000 is ‘text’
-
SQL Server 2005 is ‘varchar(max)’
During testing this type is set to ‘varchar(8000)’ for both versions. The reason is that rails expects to be able to use SQL = operators on text data types and this is not possible with a native ‘text’ data type in SQL Server. The default ‘varchar(max)’ for SQL Server 2005 can be queried using the SQL = operator and has plenty of storage space which is why we made it the default for 2005. If for some reason you want to change the data type created during migrations for any SQL Server version, you can include this line in your environment.rb file.
ActiveRecord::ConnectionAdapters::SQLServerAdapter.native_text_database_type = 'varchar(8000)'
By default any :binary column created by migrations will create these native types
-
SQL Server 2000 is ‘image’
-
SQL Server 2005 is ‘varbinary(max)’
It is our goal to match the adapter version with each version of rails. However we will track our own tiny version independent of ActiveRecord. For example, an adapter version of 2.2.x will work on any 2.2.x version of ActiveRecord. This convention will be used in both the Git tags as well as the Rubygems versioning.
First, you will need Ruby DBI and Ruby ODBC. To my knowledge the ADO DBD for DBI is no longer supported. The installation below is not a comprehensive walk thru on how to get all the required moving parts like FreeTDS installed and/or configured. It will also assume gem installations of both the dependent libraries and the adapter itself.
It should be noted that this version of the adapter was developed using both the ancient 0.0.23 version of DBI up to the current stable release of 0.4.0. Because later versions of DBI will be changing many things, IT IS HIGHLY RECOMMENDED that you max your install to version 0.4.0 which the examples below show. For the time being we are not supporting DBI versions higher than 0.4.0. The good news is that if you were using a very old DBI with ADO, technically this adapter will still work for you, but be warned your path is getting old and may not be supported for long.
$ gem install dbi --version 0.4.0 $ gem install dbd-odbc --version 0.2.4 $ gem install rails-sqlserver-2000-2005-adapter
Optionally configure your gem dependencies in your rails environment.rb file.
config.gem 'dbi', :version => '0.4.0' config.gem 'dbd-odbc', :version => '0.2.4', :lib => 'dbd/ODBC' config.gem 'rails-sqlserver-2000-2005-adapter', :lib => 'active_record/connection_adapters/sqlserver_adapter'
Here are some external links for libraries and/or tutorials on how to install any other additional components to use this adapter. If you know of a good one that we can include here, just let us know.
If you’d like to contribute a feature or bugfix, thanks! To make sure your fix/feature has a high chance of being added, please read the following guidelines. First, ask on the Google list, IRC, or post a ticket in Lighthouse. Second, make sure there are tests! We will not accept any patch that is not tested. Please read the RUNNING_UNIT_TESTS file for the details of how to run the unit tests.
-
Lighthouse: rails-sqlserver.lighthouseapp.com/projects/20277-sql-server-05-adapter/overview
-
Google Group: groups.google.com/group/rails-sqlserver-adapter
-
IRC Room: #rails-sqlserver on irc.freenode.net
Many many people have contributed. If you do not see your name here and it should be let us know.
-
Ken Collins
-
Murray Steele
-
Shawn Balestracci
-
Tom Ward
Copyright © 2008. It is free software, and may be redistributed under the terms specified in the MIT-LICENSE file.