William Graham’s blog

October 22, 2008

Some convenience code for using Micah Alles’ Rails migration testing plugin

Filed under: Programming,Ruby on Rails,Web Development — liamgraham @ 9:05 pm

Lately I’ve been using Micah Alles’ migration testing plugin in my Ruby on Rails development. I very much like being able to use it to test migrations, and I’ve also been using it to compare schemas that should be mirrors of each other. The syntax is very nice, with assert_table statements basically mirroring the migration create_table syntax, performing an assertion that the table should be in the target database and should match the structure defined in the statement, such as:

    assert_table :groups do |t|
      t.column :group_id, :integer
      t.column :site_id, :integer, :limit => 10
      t.column :group_desc, :string, :limit => 100
      t.column :fam, :integer, :limit => 10
      t.index ["fam"], :name => :groups_fam_idx
    end

The problem, as you may notice from my syntax above, is that this is set up for the older t.column syntax that was current when Micah wrote the plugin, rather than the newer t.<datatype> syntax. Since I’m using this plugin to check mirrors of databases (often to compare production schemas against dev or test), I wanted a quick way to do something set up assert_table statements using a schema.rb file as a template. I initially looked at the plugin code thinking maybe I would write a version to work with the new syntax, but quickly figured out that I’m just too much of a Ruby newb to tackle that task. I ended up just writing some code which takes a schema.rb file (in the new syntax) as a commandline argument and creates the necessary assert_table statements in the old syntax, which can then be cut and pasted into the migration test file generated from Micah’s template.

Save the code below to a Ruby file (mine is named generate_migration_test_asserts.rb). Usage is simply

  ruby generate_migration_test_asserts.rb schema.rb

That is assuming that you want to read in a schema.rb file and are in the file’s directory. Adjust to suit if you need to read in another file. You can redirect the output to a text file if you’d rather do that than copy from the screen.

I mention in the TODO section of the file header that the code could also be enhanced to do more general translation between the current create_table syntax and the older syntax. Maybe I’ll tackle that when I get a few minutes to spare …

-William

################################################################################
#
# generate_migration_test_asserts.rb
#
# code to translate new-syntax table definitions into the older syntax for use
# in 'assert_table' statements with Micah Alles' migration testing plugin:
#   http://spin.atomicobject.com/2007/02/27/migration-testing-in-rails/
#
# With some modification or enhancement to add a choice between 'assert_table'
# and 'create_table' statements, it could also be used more generally just to
# translate from this:
#
#   create_table "foo", :primary_key => "foo_id", :force => true do |t|
#     t.string   "name", :limit => 40
#     t.string   "desc", :limit => 100
#     t.datetime "created_at"
#     t.string   "created_by",  :limit => 30
#   end
#
# to this:
#
#   create_table "foo", :primary_key => "foo_id", :force => true do |t|
#     t.column :name, :string, :limit => 40
#     t.column :desc, :string, :limit => 100
#     t.column :created_at, :datetime
#     t.column :created_by, :string, :limit => 30
#   end
#
#
# TODO:
#  to enhance for table creation, will need to support the specification of
#  column defaults and nullability
#
# William Graham, 10/22/2008
#
################################################################################

########################################################
#
# helper class for indices
#
########################################################
class Index
  attr_accessor :name, :columns, :unique

  def initialize(name)
    @name = name
  end
end

########################################################
#
# helper class for columns
#
########################################################
class Column
  attr_accessor :name, :type, :limit, :default, :null

  def initialize(name = nil, type = nil, null = nil, limit = nil, default = nil)
    @name, @type, @null, @limit, @default = name, type, null, limit, default
  end
end

########################################################
#
# helper class for tables
#
########################################################

class Table
  attr_accessor :name, :columns, :indices

  def initialize(name = nil, cols = Array.new, inds = Array.new)
    @name, @columns, @indices = name, cols, inds
  end

  # write out an assert_table statement
  def to_test_s
    s = "assert_table :", self.name, ' do |t|', "\n"
    self.columns.each do |c|
      s << "  t.column :" << c.name << ", :" << c.type
      if (c.limit)
        s << ', :limit => ' << c.limit
      end
      s << "\n"
    end

    self.indices.each do |i|
      s << "  t.index [" << i.columns << "], :name => :" << i.name
      if (i.unique)
        s << ', :unique => true'
      end
      s << "\n"
    end

    s << "end"

    return s
  end

  ########################################################
  #
  # parse a create_table line to get the table name plus
  # the name of the primary key column, if it's defined
  #
  ########################################################
  def parse_create(create_str)
    tabname_regex = /^\s*create_table\s+[\:\"\'](\w+)/
    pk_regex = /primary_key\s*\=\>\s*[\:\"\'](\w+)/

    tabname_regex =~ create_str
    data = Regexp.last_match
    self.name = data[1] if (data)

    pk_regex =~ create_str
    data = Regexp.last_match
    self.columns << Column.new(data[1], :integer) if (data)
  end

  ########################################################
  #
  # parse a column definition line to get column name,
  # type, limit, and default value
  #
  ########################################################
  def parse_col(col_str)
    main_regex  = /^\s*\w\.(\w+)\s+[\:\"\'](\w+)/
    limit_regex = /\:limit\s*\=\>\s*(\d+)/
    null_regex  = /\:null\s*\=\>\s*(\w+)/
    default_regex  = /\:default\s*\=\>\s*(\w+)/

    main_regex =~ col_str
    data = Regexp.last_match

    if (data)
      c = Column.new(data[2], data[1])
      limit_regex =~ col_str
      data = Regexp.last_match
      c.limit = data[1] if (data)

      null_regex =~ col_str
      data = Regexp.last_match
      c.null = data[1] if (data)

      default_regex =~ col_str
      data = Regexp.last_match
      c.default = data[1] if (data)

      self.columns << c
    end
  end

  ##########################################################
  #
  # parse an add_index line to get the index name, list of
  # columns, and whether or not it is a unique index
  #
  ##########################################################
  def parse_index(index_str)
    #note: capture the first \w+ if table name is needed in the future
    col_regex = /\[(.*)\]/
    name_regex = /\:name\s*\=\>\s*[\"\'](\w+)/
    unq_regex = /\:unique\s*\=\>\s*(\w+)/

    name_regex =~ index_str
    data = Regexp.last_match

    if (data)
      indx = Index.new(data[1])
      col_regex =~ index_str
      data = Regexp.last_match
      indx.columns = data[1] if (data)

      unq_regex =~ index_str
      data = Regexp.last_match
      indx.unique = data[1] if (data)

      self.indices << indx
    end
  end

  def print_match_data(data)
    if (!data)
      return
    end

    print "DataMatch object:\n"
    0.upto(data.size) do |i|
      print "\t",i,": ",data[i],"\n"
    end
  end
end

################################################################################

fname = ARGV[0]
f = File.open(fname, 'r')

t = nil
f.each do |line|
  if (line =~ /create_table/)
    print t.to_test_s,"\n\n" if (t)
    t = Table.new
    t.parse_create(line)
    next
  end

  if (line =~ /t\./)
    t.parse_col(line)
    next
  end

  if (line =~ /add_index/)
    t.parse_index(line)
    next
  end
end

print t.to_test_s, "\n"

################################################################################
Advertisements

July 1, 2008

Adding support for Oracle’s bitmap indexes to Rails migrations

Filed under: Oracle,Programming,Ruby on Rails,Web Development — liamgraham @ 9:29 pm

My coworker Greg Donald helped me find a way to override the way Rails migrations create Oracle sequences by putting code in the environment.rb file. I mentioned at the bottom of that post that I would like to find a way to make it also support Oracle’s bitmap indexes. This turned out to be trivial, simply a matter of adding another section to the override code I already had. The method which creates the indexes is ActiveRecord::ConnectionAdapters::SchemaStatements.add_index. It surprises me that there isn’t an Oracle-specific override in the OracleAdapter. Anyway, my code from the last blog post already had the ActiveRecord::ConnectionAdapters in scope, so I added another section as below:

module SchemaStatements
  def add_index(table_name, column_name, options = })
    column_names = Array(column_name)
    index_name   = index_name(table_name, :column => column_names)

    if Hash === options # legacy support, since this param was a string
      index_type = options[:unique] ? "UNIQUE" : options[:bitmap] ? "BITMAP" : ""
      index_name = options[:name] || index_name
    else
      index_type = options
    end
    quoted_column_names = column_names.map { |e| quote_column_name(e) }.join(", ")
    execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})"
  end
end

Only one change was made to the original:

index_type = options[:unique] ? "UNIQUE" : ""

became

index_type = options[:unique] ? "UNIQUE" : options[:bitmap] ? "BITMAP" : ""

This now allows the specification of a bitmap option to add_index in the migration file:

add_index "mytable", ["mycolumn"], :name => "mytable_bitmap_idx", :bitmap => true

I’m pretty happy with the ability Ruby on Rails gives you to customize things like this. Being able to override class methods in this way is really pretty special.

Altering the way Rails migrations create Oracle sequences

Filed under: Oracle,Programming,Ruby on Rails,Web Development — liamgraham @ 7:04 pm

Lately I’ve been using Ruby on Rails for a project at work, and am really enjoying it. Several things bug me about managing an Oracle database with migrations, though. The main one that just struck me as bizarre is how each table’s sequence is created with a starting value of 10,000. Why? Where did that number come from, who came up with it, and why is there not a way to override it within the migration, short of using execute to drop the sequence after the fact and recreate it (since you can’t change an Oracle sequence’s starting value with an alter statement).

I dug into the code for the OracleAdapter, and sure enough, there it is, right there in the create_table method:

def create_table(name, options = {}) #:nodoc:
  super(name, options)
  seq_name = options[:sequence_name] || "#{name}_seq"
  execute "CREATE SEQUENCE #{seq_name} START WITH 10000" unless options[:id] == false
end

I was all set to just modify the CREATE SEQUENCE code above when my coworker Greg Donald, who knows much more about Ruby and RoR than I do, knocked me out with yet another in the long line of things about Ruby on Rails that make me yell “Sweet!” Thanks, Greg!

Within your Rails app’s environment.rb file you can override code. Simply by putting this snippet at the bottom of the file, I was able to change the way migrate creates my Oracle sequences:

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def create_table(name, options = {}) #:nodoc:
        super(name, options)
        seq_name = options[:sequence_name] || "#{name}_seq"
        execute "CREATE SEQUENCE #{seq_name} START WITH 1 NOCACHE" unless options[:id] == false
      end
    end
  end
end

Notice that I also added in NOCACHE while I was at it, to prevent Oracle from grabbing 20 sequence values at a time.

This is just cool. Without having to keep track of custom changes made to the adapter, which could be lost with future upgrades, and without having to resort to a bunch of execute statements within the migration file to drop and recreate the sequences, I’m able to get the custom behavior that I need. Honestly, of course, I think that the starting value should default to 1, not 10000, and I wish that something like NOCACHE was an option that could be passed. But this gets me what I need, and I can see this sort of override capability coming in very handy for other Rails customizations.

Now on to adding something to support bitmap indexes! 🙂

Create a free website or blog at WordPress.com.