Getting Capistrano to play with Oracle on Red Hat Enterprise Linux

I've been working on an internal project that connects to a legacy Oracle database. The process of getting the driver and client set up was a bit annoying, but I'd done it before and I managed to get through it again. (For those of you braving it, be sure you don't include a trailing slash on your ORACLE_HOME environment variable).

The Oracle driver and client are like most Oracle products - unnecessarily complex. One of its requirements is that you set an ORACLE_HOME environment variable. I'd managed to do this and add it to my .bashrc file so that when I manually logged into the app server via SSH I could get the rails app up and running. The problems was that remotely deploying via Capistrano was causing errors - anything that loaded the rails framework, and therefore the Oracle driver, would die with a stack trace about an undefined method. Luckily in my past troubles with the driver, I knew that meant that it didn't have the ORACLE_HOME environment variable set.

In case anyone else is running into this (or I do again), here's a patch:

First, add the following to your deploy.rb recipe:

set :use_sudo, false

Next, modify your capistrano/actor.rb file to force all run commands through bash with BASH_ENV set:

alias :run_without_env :run
def run(cmd, options={}, &block)
  cmd = <<-CMD
  BASH_ENV=.bashrc /bin/bash -c -- "#{cmd}"
  CMD
  run_without_env cmd, options, &block
end

These two changes will force the environment to be loaded and also make all command be run as the original user (which should have rights to do so).

You can read more about it on the Google group for Capistrano

Database conversion

Following on the heels of my earlier entry about db_structure_dump, I’ve created another small useful Ruby script.

As I said before, my production databse is Oracle 10g and so is my own development database - but the development databases of the others helping out are MySQL. Now that I can dump the CREATE TABLE statements for my Oracle databases I need some way to convert back and forth between MySQL and Oracle syntax – since both aren’t really standard. Or at least I need to be able to dump my development oracle DB to SQL and then convert it to MySQL for the others.

Below is the hack I created that will take an Oracle database’s structure (or  sqlite, odbc, mssql and mysql)and convert it to standard SQL, then to MySQL syntax(since MySQL can’t handle VARCHAR’s longer than 255, for intance).

The code is not generic enough to be useful for everyone, but should give a good start. Watch out if you:

• Don’t use ID as your primary key. This is assumed right now, but a smarter script could check for keys.
• Actually use NUMBER in Oracle as a DECIMAL (and want to retain that in MySQL, rather than have it converted to INTEGER).
• Have join tables (since usually you won’t use ID as the primary key, you’ll have to manually fix the primary key).

class SQLStandardizer

def initialize(type, source)
    @data_type, @data = type, source
    init_substitutions
  end     
    
  # Convert data to compatible SQL
  def convert
    replacements = @subst[@data_type]
    new_data = @data
    replacements.each do |original, replacer|
      new_data.gsub!(original, replacer)
    end
    new_data
  end    
  
   # Initialize the substitution array
  private
  def init_substitutions
    @subst = {}
    @subst['mysql'] = {"int"=>"INTEGER","dec"=>"DECIMAL"}
    @subst['oracle'] = {"BFILE"=>"BLOB",
 				"CLOB"=>"TEXT",
 				"DATE"=>"DATETIME",
 				"LONG"=>"BIGINT",
 				"LONG RAW"=>"BIGINT",
 				"NCHAR"=>"CHAR",
 				"NCLOB"=>"TEXT",
 				"NUMBER"=>"DECIMAL",
 				"NVARCHAR2"=>"VARCHAR",
 				"RAW"=>"BLOB",
 				"VARCHAR2"=>"VARCHAR",
 				"DOUBLE PRECISION"=>"DECIMAL",
 				"\r\nLOGGING"=>"",
 				"\r\nNOCACHE"=>"",
 				"\r\nNOPARALLEL"=>"",
 				" BYTE"=>""}
    @subst['odbc'] = {"LONGCHAR"=>"BLOB"}
    @subst['sqlite'] = {"INTEGER(10)"=>"INTEGER"}
    @subst['mssql'] = {"nvarchar"=>"VARCHAR",
				"nchar"=>"CHAR",
				"int identity"=>"INTEGER",
				"ntext"=>"TEXT",
				"int"=>"INTEGER",
				"image"=>"LONGBLOB",
				"money"=>"FLOAT" }
  end
end

class DBSpecializer
  def initialize(type, source)
    @data_type, @data = type, source
  end
  
  # Right now we can only convert to MySQL
  def convert
    return @data if @data_type != 'mysql'
    new_data = @data.gsub(/VARCHAR\((\d+)\)/) do |match|
      length = $1.to_i
      if length > 255
        'TEXT'
      else
        "VARCHAR(#{length})"
      end
    end
    # We don't really use decimals
    new_data.gsub!('DECIMAL', 'INTEGER')
    # Make primary keys auto-incremented
    new_data.gsub!(' ID INTEGER NOT NULL', ' ID INTEGER NOT NULL AUTO_INCREMENT')
    # Set up primary key and initial auto-increment value
    # FIXME What if our primary key is not ID?
    new_data.gsub!(');', ",\n PRIMARY KEY (`id`)\n) AUTO_INCREMENT=1;")
    new_data
  end
end


if __FILE__ == $0
  from = 'oracle'
  to = 'mysql'
  source_file = 'production_structure.sql'
  output_file = "converted_#{source_file}"
  source = open(source_file) do |f|
    f.readlines.join
  end
  # Convert from oracle to mysql
  standardized = SQLStandardizer.new(from, source).convert
  converted = DBSpecializer.new(to, standardized).convert
  open(output_file, 'w') do |f|
    f << converted
  end
end