Python database convert script for Thomas Leister's Mailserver Guide

convert-vmail-db.py

# This script converts the DB layout of the old mailserver guide (Ubuntu 14.04) [1]
# to the new one, introduced with Ubuntu Xenial (16.04) [2].
# Please note that the DB schema / tables for the new schema already need to exist.
# This script is just transferring / converting table _contents_.
#
# No not forget to provide your own database name and credentials in the 
# MySQL "connect" commands at the beginning of the script!
#
# Prerequisites:
#  apt install python3-pip
#  pip3 install --user mysql-connector-python
#
# [1]: https://legacy.thomas-leister.de/mailserver-ubuntu-server-dovecot-postfix-mysql/
# [2]: https://legacy.thomas-leister.de/sicherer-mailserver-dovecot-postfix-virtuellen-benutzern-mysql-ubuntu-server-xenial/
#
#
# SPDX-LICENSE-IDENTIFIER: MIT
#
# Copyright 2020 Thomas Leister <thomas.leister@mailbox.org>
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy of
# this software and associated documentation files (the "Software"), to deal in 
# the Software without restriction, including without limitation the rights to 
# use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies 
# of the Software, and to permit persons to whom the Software is furnished to do 
# so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in all 
# copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
# IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, 
# DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, 
# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
# DEALINGS IN THE SOFTWARE.


import mysql.connector

olddb = mysql.connector.connect(
  host="localhost",
  user="vmail",
  password="vmaildbpass",
  database="vmail_old"
)

newdb = mysql.connector.connect(
  host="localhost",
  user="vmail",
  password="vmaildbpass",
  database="vmail_new"
)

olddbCursor = olddb.cursor()
newdbCursor = newdb.cursor()


##
## Read old domains and write them to new database
##
def migrateDomains():

    olddbCursor.execute("SELECT domain FROM domains")
    domains = olddbCursor.fetchall()
    for domain in domains:
        print("Found domain", domain[0])
        sql = "INSERT INTO domains (domain) VALUES (%s)"
        val = (domain[0],)
        newdbCursor.execute(sql, val)
        newdb.commit()


##
## Read old account / "users" data
##
def migrateAccounts():
    olddbCursor.execute("SELECT username, domain, password FROM users")
    accounts = olddbCursor.fetchall()
    for account in accounts:
        print("Found account", account[0], "@", account[1])
        sql = "INSERT INTO accounts (username, domain, password, quota, enabled, sendonly) VALUES (%s, %s, %s, 0, true, false)"
        val = (account[0], account[1], account[2])
        newdbCursor.execute(sql, val)
        newdb.commit()


def migrateAliases():
    olddbCursor.execute("SELECT source, destination FROM aliases")
    aliases = olddbCursor.fetchall()
    for alias in aliases:
        print("Found alias", alias[0], " => ", alias[1])
        split_src = alias[0].split('@')
        split_dst = alias[1].split('@')
        sql = "INSERT INTO aliases (source_username, source_domain, destination_username, destination_domain, enabled) VALUES (%s, %s,%s, %s, true)"
        val = (split_src[0], split_src[1], split_dst[0], split_dst[1])
        newdbCursor.execute(sql, val)
        newdb.commit()
    return 0
        

##
## Migrate all the tables
##

migrateDomains()
migrateAccounts()
migrateAliases()