File: README — Documentation for activerecord-sqlserver-adapter (3.0.15)

SQL Server 2005/2008 & Azure Adapter For ActiveRecord

The SQL Server adapter for ActiveRecord.

What’s New

New dblib connection mode using TinyTds!
Rails 3 support!

Testing Rake Tasks Support

This is a long story, but if you are not working with a legacy database and you can trust your schema.rb to setup you local development or test database, then we have adapter level support for rails :db rake tasks. Please read this wiki page for full details.

wiki.github.com/rails-sqlserver/activerecord-sqlserver-adapter/rails-db-rake-tasks

Date/Time Data Type Hinting

SQL Server 2005 does not include a native data type 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 pass 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 conventions. So a table of ‘foo_bar_widgets’ will look for coerced column types in the FooBarWidget class.

Executing Stored Procedures

Every class that sub classes ActiveRecord::Base will now have an execute_procedure class method to use. This method takes the name of the stored procedure which can be a string or symbol and any number of variables to pass to the procedure. Arguments will automatically be quoted per the connection’s standards as normal. For example.

Account.execute_procedure :update_totals, 'admin', nil, true

Native Data Type Support

Currently the following custom data types have been tested for schema definitions.

char
nchar
nvarchar
ntext
varchar(max)
nvarchar(max)

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) 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.

Native Text/String/Binary Data Type Accessor

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 a ‘varchar(max)’ data type. This type can be queried using the SQL = operator and has plenty of storage space which is why we made it the default. If for some reason you want to change the data type created during migrations you can configure this line to your liking in a config/initializers file.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.native_text_database_type = 'varchar(8000)'

Also, there is a class attribute setter for the native string database type. This is the same for all SQL Server versions, ‘varchar’. However it can be used instead of the #enable_default_unicode_types below for finer grain control over which types you want unicode safe when adding or changing the schema.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.native_string_database_type = 'nvarchar'

By default any :binary column created by migrations will create a ‘varbinary(max)’ data type. This too can be set using an initializer.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.native_binary_database_type = 'image'

Setting Unicode Types As Default

By default the adapter will use non-unicode safe data types for :string and :text types when defining/changing the schema. If you choose, you can set the following class attribute in a config/initializers file that will change this behavior. When set to true it has the equivalent meaning as the two lower items. These examples show detail level alternatives to achieve similar effects.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.enable_default_unicode_types = true

ActiveRecord::ConnectionAdapters::SQLServerAdapter.native_text_database_type = 'nvarchar(max)'
ActiveRecord::ConnectionAdapters::SQLServerAdapter.native_string_database_type = 'nvarchar'

It is important to remember that unicode types in SQL Server have approximately half the storage capacity as their counter parts. So where a normal string would max out at (8000) a unicode string will top off at (4000).

Schema Information Logging

By default all queries to the INFORMATION_SCHEMA table is silenced. If you think logging these queries are useful, you can enable it by adding this like to a initializer file.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.log_info_schema_queries = true

Auto Connecting

By default the adapter will auto connect to lost DB connections. For every query it will retry at intervals of 2, 4, 8, 16 and 32 seconds. During each retry it will callback out to ActiveRecord::Base.did_retry_sqlserver_connection(connection,count). When all retries fail, it will callback to ActiveRecord::Base.did_lose_sqlserver_connection(connection). Both implementations of these methods are to write to the rails logger, however, they make great override points for notifications like Hoptoad. If you want to disable automatic reconnections use the following in an initializer.

ActiveRecord::ConnectionAdapters::SQLServerAdapter.auto_connect = false

Versions

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.3.x will work on any 2.3.x version of ActiveRecord. Version 3.x will track ActiveRecord 3. This convention will be used in both the Git tags as well as the gems versioning.

Installation

You will need Ruby ODBC. If you are using the adapter under 1.9, then you need at least ruby-odbc version 0.99992. ODBC is the preferred mode, however if you are using IronRuby you can use the ADONET connection mode which uses native System.Data connection. Other connection modes may be supported, possibly a straight FreeTDS layer. The sky is the limit for optional transports. If you are interested in helping, open a ticket and submit a patch. Or start a conversation on the Google Group.

$ gem install activerecord-sqlserver-adapter

Optionally configure your gem dependencies in your Gemfile.

gem 'activerecord-sqlserver-adapter', '3.x.xx'

If you have any troubles installing the lower level libraries for the adapter, please consult the wiki pages for various platform installation guides. Tons of good info can be found and we ask that you contribute too!

wiki.github.com/rails-sqlserver/activerecord-sqlserver-adapter/platform-installation

IronRuby ADONET Mode

A few details on this implementation. All that is needed in your database.yml configuration file is “mode: adonet” vs “odbc” and if you are running IronRuby, the connection will be native. You can also specify an “integrated_security: true” option in your configuration, remember to remove the username/password options too. To use this adapter, you will not need need ANY DBI middle layer or special extension gems to the adapter.

This adapter is opinionated in regards to IronRuby on types going in and out of the DB. For example strings will be String, not System::String and DateTime vs System::Datetime. There are many more examples but the rule of thumb is that the types will be simple types that correlate to a standard Ruby implementation. We enforce this basic rule because it is necessary to pass the tests and let the framework do its job. We recommend sticking to native Ruby types in your application code too.

The adapter establishes a System::Data::SqlClient connection that has both MultipleActiveResultSets (MARS) and Pooling turned off. There are good reasons for this one because the connection would not work otherwise for all the code issued by ActiveRecord. Remember too that ActiveRecord has it’s own connection pooling and these underlying features like MARS/Pooling work against the adapter code.

Currently IronRuby is passing most of the ActiveRecord and Adapter tests. Here is a list of the ones remaining. Some are in the adapter’s realm and some are in Marshaling area of IronRuby’s core to fix. Feel like helping knock these out? Submit a patch to github issues.

gist.github.com/381101

Contributing

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 on github issues. 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.

Github: github.com/rails-sqlserver/activerecord-sqlserver-adapter
Google Group: groups.google.com/group/rails-sqlserver-adapter
IRC Room: #rails-sqlserver on irc.freenode.net

Credits & Contributions

Many many people have contributed. If you do not see your name here and it should be let us know. Also, many thanks go out to those that have pledged financial contributions.

Contributers

Up-to-date list of contributors: github.com/rails-sqlserver/activerecord-sqlserver-adapter/contributors

metaskills (Ken Collins)
h-lame (Murray Steele)
vegantech
cjheath (Clifford Heath)
jrafanie (Joe Rafaniello)
nerdrew (Andrew Ryan)
snowblink (Jonathan Lim)
koppen (Jakob Skjerning)
ebryn (Erik Bryn)
adzap (Adam Meehan)
neomindryan (Ryan Findley)
jeremydurham (Jeremy Durham)

Donators

pledgie.com/campaigns/11630