Naïve migration of Atmail 4 to Atmail 7.1.

migrate-4-to-7.py

#!/usr/bin/env python

""" Atmail 4 to Atmail 7.1 migration
    Copyright (C) 2013 Ace Internet Services Pty Ltd

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
"""

import string
import mysql.connector

ATMAIL4_DB = mysql.connector.connect(
	host='atmail4.localdomain',
	user='atmail',
	password='',
	database='atmail')

ATMAIL7_DB = mysql.connector.connect(
	host='atmail7.localdomain',
	user='atmail',
	password='',
	database='atmail')

def alphashard(name, other=False, shared=False, reply=False):
	shards = ['%s_%s' % (name, x) for x in string.ascii_lowercase]
	if other:
		shards += ['%s_other' % name]
	if shared:
		shards += ['%s_shared' % name]
	if reply:
		shards += ['%s_Reply' % name]
	return shards

def common_columns(set1, set2, invert=False):
	if not invert:
		return [c for c in set2 if c in set1]
	else:
		return [c for c in set2 if c not in set1]

def print_common_columns(am4_tbl, am7_tbl):
	cur4 = ATMAIL4_DB.cursor()
	cur4.execute('SHOW COLUMNS FROM %s' % am4_tbl)

	cur7 = ATMAIL7_DB.cursor()
	cur7.execute('SHOW COLUMNS FROM %s' % am7_tbl)

	set1 = [x[0] for x in cur4]
	set2 = [x[0] for x in cur7]

	c = common_columns(set1, set2)
	print 'Common columns between %s and %s:' % (am4_tbl, am7_tbl)
	for row in c:
		print '\t%s' % str(row)

	print

	c = common_columns(set1, set2, invert=True)
	print 'Uncommon columns between %s and %s:' % (am4_tbl, am7_tbl)
	for row in c:
		print '\t%s' % str(row)

	print
	print

def database_fixups():
	cur = ATMAIL7_DB.cursor()

	# Allow users to be seen within web admin
	cur.execute('INSERT INTO Abook (Global, Account) SELECT 1 AS Global, Account FROM Users')

	# Ugroups are "domains" -- been renamed since Atmail 4.x
	# Atmail 4.x measured in kilobytes, Atmail 7.x measures in megabytes
	cur.execute('UPDATE Users SET Ugroup = \'default\', UserQuota = UserQuota / 1024')

	# Set settings to sane defaults
	# Many of the Atmail 4.x values cause weirdities in Atmail 7
	cur.execute("""UPDATE UserSettings SET
		MailServer = NULL,
		Language = 'en',
		MailType = 'sql',
		EmailEncoding = 'UTF-8',
		TimeZone = 'Australia/Sydney',
		Refresh = 300,
		DeleteTrashOnLogout = 1,
		enableExtraAddressFields = 1,
		DefaultView = '3p',
		DisplayEmailImages = 0
	""")

def migrate_table(am4_tbl, am7_tbl, truncate=True, skip_pk=False, limit=''):
	if not isinstance(am4_tbl, basestring):
		if truncate:
			cur7 = ATMAIL7_DB.cursor()
			cur7.execute('TRUNCATE TABLE %s' % am7_tbl)
		for t in am4_tbl:
			migrate_table(t, am7_tbl, truncate=False, skip_pk=skip_pk)
		return

	cur4 = ATMAIL4_DB.cursor()
	cur7 = ATMAIL7_DB.cursor()

	cur4.execute('SHOW COLUMNS FROM %s' % am4_tbl)
	cur7.execute('SHOW COLUMNS FROM %s' % am7_tbl)

	if skip_pk:
		set1 = [x[0] for x in cur4 if x[3] != 'PRI']
		set2 = [x[0] for x in cur7 if x[3] != 'PRI']
	else:
		set1 = [x[0] for x in cur4]
		set2 = [x[0] for x in cur7]
	c = common_columns(set1, set2)

	selection = ', '.join(['`%s`' % x for x in c])
	cur4.execute('SELECT %s FROM %s %s' % (selection, am4_tbl, limit))

	if truncate:
		cur7.execute('TRUNCATE TABLE %s' % am7_tbl)

	for row in cur4:
		cur7.execute('INSERT INTO %s (%s) VALUES (%s)' % (am7_tbl, selection, ', '.join(['%s' for x in c])), row)

if __name__=='__main__':
	migrate_table('Users', 'Users')
	migrate_table('UserSession', 'UserSession')
	migrate_table(alphashard('UserSettings', other=True), 'UserSettings')
	migrate_table(alphashard('Abook', other=True, shared=True), 'Abook', skip_pk=True)
	migrate_table(alphashard('AbookGroup', other=True, shared=True), 'AbookGroup', skip_pk=True)
	migrate_table('SpamSettings', 'SpamSettings')
	migrate_table('MailAliases', 'MailAliases')
	database_fixups()
	ATMAIL7_DB.commit()