Wednesday, November 12, 2008

Conversion from mysql to sqlite database.. via rails

I faced this problem to have a sqlite database from an existing mysql.. and being the rails guy... i made it the rails way.. but of course there are other option available which will be fast .. this is kind of slow... but works for small stores :))

Steps are..
1. Create a source model which is connected with mysql connection/using mysql adaptor
class SourceDB < ActiveRecord::Base
end
SourceDB.establish_connection($config["database_mysql"])

2. Create a destination model which is connected with sqlite connection/using sqlite adaptor
class TargetDB < ActiveRecord::Base
end
TargetDB.establish_connection($config["database_sqlite"])

3. Create schema from source database so that can reproduce it in destination(sqlite) database.
File.open(name_of_schema_file,"w") do |file|
  ActiveRecord::SchemaDumper.dump(SourceDB.connection, file)
end

4. Alter the generated schema file to remove line which contains "add_index" as these are of no use in sqlite conversion process

5. Then load the altered schema file (change the ActiveRecord::Base.connection to point to destination database before loading)
ActiveRecord::Base.connection = TargetDB.connection
load(name_of_schema_file)

6. and now resurvely iterate with all tables in source database and transport it to derstination (sqlite) database

Source file [mysql_to_sqlite.rb]
require 'rubygems'
require 'active_record'
require 'active_support'
require 'sqlite3'
require 'active_record/schema_dumper'

require "yaml"
require "create_class.rb"

t1 = Time.now
$config   = YAML.load_file("config/config.yml")
name_of_schema_file = ($config["schema_file"])

class SourceDB < ActiveRecord::Base
end
SourceDB.establish_connection($config["database_mysql"])

class TargetDB < ActiveRecord::Base
end
TargetDB.establish_connection($config["database_sqlite"])

puts "dumping the schema"
File.open(name_of_schema_file,"w") do |file|
  ActiveRecord::SchemaDumper.dump(SourceDB.connection, file)
end

puts "discarding index(es)"
line_array = Array.new
File.open(name_of_schema_file,"r") do |file|
  file.each { |line|    line_array << line  unless line.include?("add_index")}
end

File.rename(name_of_schema_file, "original_#{name_of_schema_file}")

File.open(name_of_schema_file,"w") do |file|
  file.puts(line_array)
end

puts "loading the schema"
ActiveRecord::Base.connection = TargetDB.connection
load(name_of_schema_file)


SourceDB.connection.tables.each do |tbl|
  puts "Table_initiated: #{tbl.inspect}"
    SourceDB.set_table_name tbl
    SourceDB.set_inheritance_column ""
    
    create_class('TargetModel', TargetDB) do
      set_table_name tbl
      set_inheritance_column ""
    end
    puts "=========for table: #{tbl}=========="
    total_record_in_table = SourceDB.count_by_sql("SELECT COUNT(*) from #{tbl}")

    tub_size = 1000
    no_of_iteration = ((total_record_in_table % tub_size) == 0) ? (total_record_in_table / tub_size) : ((total_record_in_table / tub_size) + 1)

    for j in 0..(no_of_iteration-1)
      current_record_set = SourceDB.find(:all,:offset => (j*tub_size), :limit => (tub_size - 1))

      current_record_set.each_with_index do |record,ind|
        record_copy = TargetModel.new
        record.attributes.each do |key,value|
        record_copy.send("#{key}=",value)
        end
        record_copy.save
        puts "Completed: #{((j*tub_size) + ind)} of #{total_record_in_table}   with id: #{record_copy.id} ."
      end
    end
end
        
t2 = Time.now

puts "Process initiated at: #{t1}"
puts "Process completed at: #{t2}"
puts "Time elapsed        : #{t2-t1} seconds"

Source file [create_class.rb]
def create_class(class_name, superclass, &block)
  klass = Class.new superclass, &block
  Object.const_set class_name, klass
end

Configuration file [config.yml]
database_mysql:
  adapter: mysql
  database: databse_name
  username: root
  password: xxxxxxxx
  host: xxx.xx.x.xxx
  timeout: 5000
  encoding: utf8

database_sqlite:
  adapter: sqlite3
  database: sqlite_database_file_with_path.db
  
schema_file: schema.txt

3 comments:

  1. Great work mate! I've almost gotten this solution to work
    But sadly it fails at the followin line:
    ActiveRecord::Base.connection = TargetDB.connection

    After some research I still don't see what it's supposed to achieve nor if it should be there at all.

    ReplyDelete
  2. Hi telamon,
    Sorry was on a small holiday trip.
    I can surely help you on this if you can provide me more info on what is the error you were/are getting

    ReplyDelete
  3. Praveen,

    Great work on this! I'm trying to implement it but am getting an error from this line:

    ActiveRecord::Base.connection = TargetDB.connection

    The error is:

    /Library/Ruby/Gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1959:in "method_missing": undefined method "connection=" for ActiveRecord::Base:Class (NoMethodError)

    Any ideas?

    Thanks!

    -Matt

    ReplyDelete

Sky diving! What it feels like.

Writing a new post after almost a year :) Penning it down here.. so that can relive that moment when i did the jump from 10000 feet up in...