Roundcube merge two separate intersecting databases #roundcube #mysql #contacts #merge #ruby #activerecord

README.md

So... we forgot to copy the old roundcube contacts database to the new server and thus ended up with two different contact databases..

A few days later, and our customers want the old contacts back. But some of them had already started creating new ones. So we had a dilemma, should we throw away the new database and replace with the old one? Or try to use the built in export vCard-feature?

I decided to make a ruby script for going through the old database and recreate missing records with new ids.

This would have been more trivial if it weren't for all the relations that had to be kept.

What do you think of the resulting code?

ruby main.rb

$ irb
irb(main):001:0> require "./models"
irb(main):002:0> NewDB::User.count
=> 116

Gemfile

source "https://rubygems.org"

gem 'mysql2'
gem 'activerecord'
gem 'safe_attributes'

main.rb

require './models'

OldDB::User.all.each do |old_user|
  new_user = NewDB::User.find_or_initialize_by(username: old_user.username)

  unless new_user.persisted?
    attrs_to_keep = old_user.attributes
    attrs_to_keep.delete("user_id")
    attrs_to_keep.delete("alias")
    new_user.attributes = attrs_to_keep
    p new_user
    new_user.save
  end

  contact_groups = OldDB::ContactGroup.where(user_id: old_user.id)

  contact_groups.each do |old_contact_group|
    new_contact_group = NewDB::ContactGroup.find_or_initialize_by(
      name: old_contact_group.name,
      user_id: new_user.id
    )

    unless new_contact_group.persisted?
      attrs_to_keep = old_contact_group.attributes
      attrs_to_keep.delete("name")
      attrs_to_keep.delete("user_id")
      attrs_to_keep.delete("contactgroup_id")
      new_contact_group.attributes = attrs_to_keep
      p new_contact_group
      new_contact_group.save
    end
  end

  contacts = OldDB::Contact.where(user_id: old_user.id)

  contacts.each do |old_contact|
    new_contact = NewDB::Contact.find_or_initialize_by(
      email: old_contact.email,
      user_id: new_user.id
    )

    unless new_contact.persisted?
      attrs_to_keep = old_contact.attributes
      attrs_to_keep.delete("email")
      attrs_to_keep.delete("user_id")
      attrs_to_keep.delete("contact_id")
      new_contact.attributes = attrs_to_keep
      p new_contact
      new_contact.save
    end
  end
end

OldDB::ContactGroupMember.all.each do |old_contact_group_member|
  old_contact_group = OldDB::ContactGroup.find_by!(
    contactgroup_id: old_contact_group_member.contactgroup_id
  )
  old_contact = OldDB::Contact.find_by!(
    contact_id: old_contact_group_member.contact_id
  )
  old_user = OldDB::User.find_by!(
    user_id: old_contact.user_id
  )
  new_user = NewDB::User.find_by!(
    username: old_user.username
  )
  new_contact_group = NewDB::ContactGroup.find_by!(
    name: old_contact_group.name,
    user_id: new_user.id
  )
  new_contact = NewDB::Contact.find_by!(
    email: old_contact.email,
    user_id: new_user.id
  )
  new_contact_group_member = NewDB::ContactGroupMember.find_or_initialize_by(
    contactgroup_id: new_contact_group.id,
    contact_id: new_contact.id
  )

  unless new_contact_group_member.persisted?
    attrs_to_keep = old_contact_group_member.attributes
    attrs_to_keep.delete("contactgroupmember_id")
    attrs_to_keep.delete("contact_id")
    attrs_to_keep.delete("contactgroup_id")
    new_contact_group_member.attributes = attrs_to_keep
    p new_contact_group_member
    new_contact_group_member.save
  end
end

OldDB::Identity.all.each do |old_identity|
  old_user = OldDB::User.find_by(
    user_id: old_identity.user_id
  )

  unless old_user
    puts "Skipping #{old_identity.inspect}"
    next
  end

  new_user = NewDB::User.find_by!(
    username: old_user.username
  )

  new_identity = NewDB::Identity.find_or_initialize_by(
    user_id: new_user.id
  )

  unless new_identity.persisted?
    attrs_to_keep = old_identity.attributes
    attrs_to_keep.delete("identity_id")
    attrs_to_keep.delete("user_id")
    new_identity.attributes = attrs_to_keep
    p new_identity
    new_identity.save
  end
end

models.rb

require 'active_record'
require 'safe_attributes/base'

module OldDB
  class Base < ActiveRecord::Base
    self.abstract_class = true
    establish_connection(
      adapter: 'mysql2',
      host: 'localhost',
      encoding: 'utf8',
      pool: 5,
      username: 'root',
      password: '',
      database: 'roundcube_old',
    )
  end
  class User < Base
    include SafeAttributes::Base
  end
  class ContactGroup < Base
    include SafeAttributes::Base
    self.table_name = "contactgroups"
    bad_attribute_names :changed
  end
  class Contact < Base
    include SafeAttributes::Base
    bad_attribute_names :changed
  end
  class ContactGroupMember < Base
    include SafeAttributes::Base
    self.table_name = "contactgroupmembers"
  end
  class Identity < Base
    include SafeAttributes::Base
  end
end

module NewDB
  class Base < ActiveRecord::Base
    self.abstract_class = true
    establish_connection(
      adapter: 'mysql2',
      host: 'localhost',
      encoding: 'utf8',
      pool: 5,
      username: 'root',
      password: '',
      database: 'roundcube_new',
    )
  end
  class User < Base
    include SafeAttributes::Base
  end
  class ContactGroup < Base
    include SafeAttributes::Base
    self.table_name = "contactgroups"
    bad_attribute_names :changed
  end
  class Contact < Base
    include SafeAttributes::Base
    bad_attribute_names :changed
  end
  class ContactGroupMember < Base
    include SafeAttributes::Base
    self.table_name = "contactgroupmembers"
  end
  class Identity < Base
    include SafeAttributes::Base
    bad_attribute_names :changed
  end
end