Archive for October 2007
Configuring ActiveRecord to work with SQL Server 2005
As much as possible, I like a zero-install configuration. In other words, I want to simply checkout a codebase, run an automated build process, and start working. Ideally, I’d like to be able to do that on a clean machine.
It doesn’t always work, of course. For instance, even though most of our production code is written in .NET, we use Ruby extensively for automation. Installing Ruby is one of those dependencies that we live with. But installing Ruby isn’t enough; we also need Rails (or at least ActiveRecord) for some data management scripts we have, Watir, and some fixes for ActiveRecord to work with SQL Server.
All of that can be done fairly easily by somebody who knows what they’re doing, but new developers often don’t know what they’re doing, and I strive to be dispensable. We wrote a script that configured
Ruby and the necessary libraries to work for our development environment.
First, we needed to install the necessary gems. This is quite easy to do on the command line, but it took me a little digging before I figured out how to do it in code:
needed_gems = ["rails"]
require "rubygems"
Gem.manage_gems
needed_gems.each do |gem|
puts "Installing gem #{gem}..."
Gem::GemRunner.new.run ["install", gem, "--include-dependencies"]
end
SQL Server requires an ADO adapter that doesn’t ship with Ruby. You can read about it here. All that’s needed is to download the ADO.rb file (which we have versioned next to our setup script) and copy it to the right place, creating the directory if needed:
setup_dir = File.dirname(__FILE__)
ruby_dir = "C:/ruby/lib/ruby"
ruby_version = 1.8
# The ADO adapter needed for using dbi (the standard db access)
# with SQL Server, which does not come with ruby by default.
# See http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServer.
puts "creating ADO adapter..."
ado_dir = "#{ruby_dir}/site_ruby/#{ruby_version}/DBD/ADO"
system "if not exist #{ado_dir} mkdir #{ado_dir}"
system "copy /Y #{setup_dir}/ADO.rb #{ado_dir}"
Finally, we use SQL Server 2005, and we want to use Windows Authentication for all of our Ruby scripts. Neither SQL Server 2005 nor Windows Authentication is supported by rails out of the box. The problem, described on the SQL Server wiki for rails, is the connection string rails builds. At first, we were taking the suggestions of some of the comments on the wiki and changing the sqlserver_adapter.rb file that ships with rails. This obviously isn’t ideal, so now we monkey-patch it in our code that accesses the database:
module DBI
# We have to monkey patch this because the SQL Server adapter that comes
# with rails (active_record\connection_adapters\sqlserver_adapter.rb)
# doesn't work with SQL Server 2005 or with Integrated Security yet.
class << self
alias_method :original_connect, :connect
def connect(driver_url, username, password)
# Change to SQL 2005
driver_url.sub! "SQLOLEDB", "SQLNCLI"
# Change to Windows Authentication
driver_url.sub! /User Id=[^;]*;Password=[^;]*;/, "Integrated Security=SSPI;"
original_connect(driver_url, username, password)
end
end
end
And that’s it. You still can’t checkout the codebase and start working on a clean machine, but it’s not bad. Install Ruby, run setup.rb. All database code has been patched to deal with our environment.
Auto-merging fixes
Paul Gross recently blogged about a rake task to automatically merge changes to the trunk if the change was made in a branch. This seemed like a useful feature, even though we don’t use rake.
Fixing productions bugs and merging is no fun, but why not take some of the pain out of the process? Depending on the developer and their mood, we either fix the bug in the trunk and merge to the branch, or fix it it the branch and merge to the trunk. Where it really gets ugly is when we have to merge two release branches back, because we make our release branch a few days before actually pushing it to production. Any urgent bug fix requiring a merge during that time has to be merged both to the new release branch as well as to the release branch currently in production.
Using Paul’s code as a base, I wrote automerge.rb, which, by default, either merges to the trunk (if you’re in a branch), or merges to the latest release branch (if you’re in the trunk). Alternatively, you can pass a release number, and automerge.rb will merge to that release branch. In all cases, you have to have the working copy checked out on your machine, and, if you’re on Windows, you need to make sure to put patch in your path.
The script assumes that your directory structure looks something like the following:
/trunk
/branches
/Release-2.8
/Release-2.9
The Release-major-minor format is just our branch naming standard; it’s easy to change.
if !ARGV[0].nil?
dest_dir = "branches/Release-#{ARGV[0]}"
replace_pattern = /(trunk|branches).*$/i
elsif %x[svn info].include? "branches"
dest_dir = "trunk"
replace_pattern = /branches.*$/i
elsif %x[svn info].include? "trunk"
pattern = /^Release-(\d+).(\d+)\/$/
branches = %x[svn list http://devtools01:8080/svn/lazarus/branches]
releases = branches.split.find_all {|branch| branch =~ pattern}
# sort them in reverse order by major, minor
releases.sort! do |first, second|
first_major, first_minor = pattern.match(first)[1..2]
second_major, second_minor = pattern.match(second)[1..2]
if first_major != second_major
second_major.to_i <=> first_major.to_i
else
second_minor.to_i <=> first_minor.to_i
end
end
dest_dir = "branches/#{releases[0]}"
replace_pattern = /trunk/
end
puts "Merging changes into #{dest_dir}. Don't forget to check these in."
dest_path = Dir.pwd.gsub(replace_pattern, dest_dir)
puts dest_path
system "svn diff | patch -p0 -d #{dest_path}"
The only tricky part here is figuring out the latest release branch, which is done by using the svn list
command followed by a custom sort.
C# Enum Generation
Ayende recently asked on the ALT.NET mailing list about the various methods developers use to provide lookup values, with the question framed as one between lookup tables and enums. My own preference is to use both, but keep it DRY with code generation.
To demonstrate the idea, I wrote a Ruby script that generates a C# enum file from some metadata. I much prefer Ruby to pure .NET solutions like CodeSmith—I find it easier and more powerful (I do think CodeSmith is excellent if there is no Ruby expertise on the team, however). The full source for this example can be grabbed here.
The idea is simple. I want a straightforward and extensible way to provide metadata for lookup values, following the Ruby Way of convention over configuration. XML is very popular in the .NET world, but the Ruby world views it as overly verbose, and prefers lighter markup languages like YAML. For my purposes, I decided not to mess with markup at all (although I’m still considering switching to YAML—the hash of hashes approach describes what I want well). Here’s some example metadata:
enums = {
'OrderType' => {},
'MethodOfPayment' => {:table => 'PaymentMethod',},
'StateProvince' => {:table => 'StateProvinces',
:name_column => 'Abbreviation',
:id_column => 'StateProvinceId',
:transformer => lambda {|value| value.upcase},
:filter => lambda {|value| !value.empty?}}
}
That list, which is valid Ruby code, describes three enums, which will be named OrderType
, MethodOfPayment
, and StateProvince
. The intention is that, where you followed your database standards, you should usually be able to get by without adding any extra metadata, as shown in the OrderType
example. The code generator will get the ids and enum names from the OrderType
table (expecting the columns to be named OrderTypeId
and Description
) and create the enum from those values. As StateProvince
shows, the table name and two column names can be overridden.
More interestingly, you can both transform and filter the enum names by passing lambdas (which are like anonymous delegates in C#). The ‘StateProvince’ example above will filter out any states that, after cleaning up any illegal characters, equal an empty string, and then it will upper case the name.
We use a pre-build event in our project to build the enum file. However, if you simply overwrite the file every time you build, you may slow down the build process considerably. MSBuild (used by Visual Studio) evidently sees that the timestamp has been updated, so it rebuilds the project, forcing a rebuild of all downstream dependent projects. A better solution is to only overwrite the file if there are changes:
require File.dirname(__FILE__) + '/enum_generator'
gen = EnumGenerator.new('localhost', ‘database-name’)
source = gen.generate_all(‘Namespace', enums)
filename = File.join(File.dirname(__FILE__), 'Enums.cs')
if Dir[filename].empty? || source != IO.read(filename)
File.open(filename, 'w') {|file| file << source}
end
I define the basic templates straight in the EnumGenerator
class, but allow them to be swapped out. In theory, the default name column and the default lambda for generating the id column name given the table name (or enum name) could be handled the same way. Below is the EnumGenerator
code:
class EnumGenerator
FILE_TEMPLATE = <<EOT
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a tool from <%= catalog %> on <%= server %>.
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace <%= namespace %>
{
<%= enums %>
}
EOT
ENUM_TEMPLATE = <<EOT
public enum <%= enum_name %>
{
<% values.keys.sort.each_with_index do |id, i| -%>
<%= values[id] %> = <%= id %><%= ',' unless i == values.length - 1 %>
<% end -%>
}
EOT
# Change the templates by calling these setters
attr_accessor :enum_template, :file_template
attr_reader :server, :catalog
def initialize(server, catalog)
@server, @catalog = server, catalog
@enum_template, @file_template = ENUM_TEMPLATE, FILE_TEMPLATE
end
end
The code generation uses erb, the standard Ruby templating language:
def transform(template, template_binding)
erb = ERB.new(template, nil, '-')
erb.result template_binding
end
template_binding
describes the variables available to use in the template in much the same way that Castle Monorail’s PropertyBag
describes the variables available to the views. The difference is that, because Ruby is dynamic, you don’t have to explictly add values to the binding. The rest of the code is shown below:
def generate(enum_name, attributes)
table = attributes[:table] || enum_name
filter = attributes[:filter] || lambda {|value| true}
values = enum_values(table, attributes)
values.delete_if {|key, value| !filter.call(value)}
transform enum_template, binding
end
def generate_all(namespace, metadata)
enums = ''
metadata.keys.sort.each {|enum_name| enums << generate(enum_name, metadata[enum_name])}
enums = enums.gsub(/\n/m, "\n\t").strip
transform file_template, binding
end
private
def enum_values(table, attributes)
sql = get_sql table, attributes
@dbh ||= DBI.connect("DBI:ADO:Provider=SQLNCLI;server=#{server};database=#{catalog};Integrated Security=SSPI")
sth = @dbh.execute sql
values = {}
sth.each {|row| values[row['Id']] = clean(row['Name'], attributes[:transformer])}
sth.finish
values
end
def get_sql(table, attributes)
id_column = attributes[:id_column] || "#{table}Id"
name_column = attributes[:name_column] || "Description"
"SELECT #{id_column} AS Id, #{name_column} AS Name FROM #{table} ORDER BY Id"
end
def clean(enum_value, transformer=nil)
enum_value = '_' + enum_value if enum_value =~ /^\d/
enum_value = enum_value.gsub /[^\w]/, ''
transformer ||= lambda {|value| value}
transformer.call enum_value
end
Caveat Emptor: I wrote this code from scratch today; it is not the same code we currently use in production. I think it’s better, but if you find a problem with it please let me know.