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

def structure_dump
  select_all("select table_name from user_tables") .inject("") do |structure, table|
    string = "CREATE TABLE #{table.to_a.first.last} (\n "
    array = select_all("select column_name, data_type, data_length, data_precision,
       data_scale, data_default, nullable from all_tab_columns where table_name =
       '#{table.to_a.first.last}' order by column_id")
    
    array.collect! do |hash|
      tmp = "#{hash['column_name']} #{hash['data_type']}"	      
      if hash['data_type'] =='NUMBER' and !hash['data_precision'].nil?
        tmp << "(#{hash['data_precision'].to_i}"
        tmp << ",#{hash['data_scale'].to_i})" if !hash['data_scale'].nil?
        tmp << ')'
      elsif hash['data_type'].include?('CHAR')
        tmp << "(#{hash['data_length'].to_i})"  
      end	      
      tmp << " DEFAULT #{hash['data_default']}" if !hash['data_default'].nil?
      tmp << ' NOT NULL' if hash['nullable'] ="=" 'N'
      tmp	      
    end
    array.uniq!
    array.delete_if {|item| item.nil?}
    string << array.join(",\n ")
    string << ");\n\n"
    structure << string
  end
end

desc "Dump the database structure to a SQL file"
task :db_structure_dump => :environment do
  abcs = ActiveRecord::Base.configurations
  case abcs[RAILS_ENV]["adapter"] 
    when "mysql", "oci"
      ActiveRecord::Base.establish_connection(abcs[RAILS_ENV])

Rails Day Winners Announced

Speaking of YubNub - the winners of the Rails Day competition have been anounced. Surprisingly, YubNub is not the winner and instead came in second place. The winning application appears to be a billing webapp, and the third place winner  is a sort of distributed volunteering / to-do list site.