Migrate from ViMbAdmin2

In ViMbAdmin V3, we really tried to move away from calling this project a replacement for Postfix Admin and stepped away from the easy migration path. Unfortunately this also affects ViMbAdmin2.

It should be relatively easy to migrate as we've kept the table and column names the same were possible. Essentially, it's just a bunch of SQL queries. See the Postfix Admin -> ViMbAdmin2 migration guide for an example.

SQL Migration from v2 to v3

In June 2014, @dustinfarris provided the following in #91. It's currently untested by the developers but looks fine.

• Move the old database out of the way (or just create a new one for ViMbAdmin3):

  mysqldump vimbadmin > vimbadmin.sql
  mysqladmin create vimbold
  mysql vimbold < vimbadmin.sql

  And then in MySQL:

  DROP DATABASE vimbadmin;

• Create new database as per the instructions - including the schema via doctrine2-cli.

• Make sure application.ini salts match from v2.x.

• Copy over the old domains:

  INSERT INTO vimbadmin.domain 
      (domain, description, max_aliases, alias_count, max_mailboxes, mailbox_count, max_quota, 
          quota, transport, backupmx, active, created) 
  SELECT domain, description, aliases, 0, mailboxes, 0, maxquota, 0, transport, backupmx, 
      active, created 
  FROM vimbold.domain;

• Copy over the old mailboxes:

  INSERT INTO vimbadmin.mailbox 
      (username, password, name, quota, local_part, active, access_restriction, homedir, 
          maildir, uid, gid, created, Domain_id, delete_pending, alt_email) 
  SELECT old.username, old.password, old.name, old.quota, old.local_part, old.active, 
      old.access_restriction, old.homedir, old.maildir, old.uid, old.gid, old.created, 
      vimbadmin.domain.id, 0, "" 
  FROM vimbold.mailbox old 
  JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;

(if the mailbox copy complains about character encoding refer to NOTE at https://github.com/opensolutions/ViMbAdmin/issues/91 to fix it previously)

• Copy old aliases:

  INSERT INTO vimbadmin.alias 
      (address, goto, active, created, Domain_id) SELECT old.address, old.goto, 
          old.active, old.created, vimbadmin.domain.id 
  FROM vimbold.alias old 
  JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;

• Copy admins

  INSERT INTO vimbadmin.admin 
      (username, password, super, active, created) 
  SELECT username, password, super, active, created 
  FROM vimbold.admin;
  
  INSERT INTO vimbadmin.domain_admins 
      (Admin_id, Domain_id) 
  SELECT vimbadmin.admin.id, vimbadmin.domain.id 
  FROM vimbold.domain_admin 
  JOIN vimbadmin.admin ON vimbadmin.admin.username = vimbold.domain_admin.username 
  JOIN vimbadmin.domain ON vimbadmin.domain.domain = vimbold.domain_admin.domain;

  NB: Your admins will have to do a password reset as we have moved to bcrypt hashing.

• Update counts

  USE vimbadmin;
  UPDATE domain SET mailbox_count = (SELECT COUNT(*) FROM mailbox WHERE mailbox.Domain_Id=domain.id);
  UPDATE domain SET alias_count = (SELECT COUNT(*) FROM alias WHERE alias.Domain_Id=domain.id AND address != goto);

• Migrate logs:

  INSERT INTO vimbadmin.log (action, data, timestamp, Admin_id, Domain_id) 
      SELECT old.action, old.data, old.timestamp, vimbadmin.admin.id, vimbadmin.domain.id 
  FROM vimbold.log old 
      JOIN vimbadmin.admin ON vimbadmin.admin.username = old.username 
      JOIN vimbadmin.domain ON vimbadmin.domain.domain = old.domain;

• Drop old database

  DROP DATABASE vimbold;