[RUBY] Migration Database Client

migration_database_client.rb

require 'mysql'

class MigrationDB

	def initialize domain, user_name, password, db_name
		begin
			@db_connection = Mysql.new(domain, user_name, password)
			@db_name = db_name
		rescue Exception => e
			p "Error connecting to DB Server.."
		end
		create_and_use_db
	end

	def create_table table_name, columns
		execute_query do
			"CREATE TABLE IF NOT EXISTS #{table_name}(`#{columns.join('` TEXT,`')}` TEXT)"
		end
	end

	def update_table table_name, key, value, id_key, id_value
		execute_query do
			"UPDATE #{table_name} SET `#{key}` = \"#{escape_string(value)}\""
		end
	end

	def insert_table table_name, i_hash
		execute_query do
			"INSERT #{table_name}(`#{i_hash.keys.join('`,`')}`) VALUES(\"#{i_hash.values.map{|p| escape_string(p)}.join('","')}\")"
		end
	end
	
	def create_and_insert_table table_name, a_hash
		column_names = a_hash.map{|h| h.keys}.flatten.uniq
		create_table table_name, column_names
		a_hash.each do |i_hash|
			insert_table table_name, i_hash  
		end
	end

	private

	def create_and_use_db
		execute_query do
			"CREATE DATABASE IF NOT EXISTS #{@db_name}"
		end

		execute_query do
			"USE #{@db_name}"
		end
	end

	def escape_string str
		str.gsub!(/\"/,'\'') if str.include? '"'
		str.gsub!(/\'/,'\'') if str.include? "'"
		str
	end

	def execute_query
		begin
			@db_connection.query(yield) if block_given?
		rescue Exception => e
			p e
		end
	end

end

# Usage:
# connection = MigrationDB.new "localhost", "root", "", "amma"
# connection.create_table 'tickets', ['first_name', 'last_name']
# connection.insert_table 'tickets', {'first_name' => 'dinesh', 'last_name' => 'prabu'}
# connection.update_table 'tickets', 'first_name', 'ramya', 'first_name', 'dinesh'
# connection.create_and_insert_table 'notes', [{'first_name' => 'dinesh'}, {'last_name' => 'prabu'}]