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