#!/usr/bin/python

#
# converts MySQL DB relaydelay to PostgreSQL DB relaydelay
#
# (c) GPL 2007 Robert Sander <gurubert@gurubert.de>
#

import MySQLdb, datetime
from pyPgSQL import libpq

mysqldb = MySQLdb.connect(user="milter", db="relaydelay", host="localhost", passwd="retlim")
pgconn = libpq.PQconnectdb("dbname=relaydelay")

def convert_ip(ip):
    dots = ip.count(".")
    if dots == 3:
        return ip
    if dots == 2:
        return ip + ".0/24"
    if dots == 1:
        return ip + ".0.0/16"
    if dots == 0:
        return ip + ".0.0.0/8"
    raise SyntaxError

def convert_type(type):
    if type == "MANUAL":
        return "M"
    if type == "AUTO":
        return "A"
    raise SyntaxError

if pgconn.status == libpq.CONNECTION_OK:
    print "connection established"

    mysqlc = mysqldb.cursor()

    fields = ["relay_ip", "mail_from", "rcpt_to", "block_expires", "record_expires", "blocked_count", "passed_count", "aborted_count", "origin_type", "create_time", "last_update"]

    mysqlc.execute("select %s from relaytofrom" % ", ".join(fields))

    while 1:
        try:
            relay_ip, mail_from, rcpt_to, block_expires, record_expires, blocked_count, passed_count, aborted_count, origin_type, create_time, last_update = mysqlc.fetchone()
        except TypeError:
            break

        # print convert_ip(relay_ip), mail_from, rcpt_to, block_expires, record_expires, blocked_count, passed_count, aborted_count, convert_type(origin_type), create_time, last_update

        query = "insert into relaytofrom "
        keys = []
        values = []
        for field in fields:
            if eval(field):
                keys.append(field)
                if field == "relay_ip":
                    values.append(convert_ip(relay_ip))
                elif field == "origin_type":
                    values.append(convert_type(origin_type))
                elif field == "block_expires" or field == "record_expires":
                    value = "%s" % eval(field)
                    if value == "0000-00-00 00:00:00":
                        value = "-infinity"
                    if value == "9999-12-31 23:59:59":
                        value = "infinity"
                    values.append(value)
                else:
                    values.append("%s" % eval(field))

        query = "insert into relaytofrom (%s) values ('%s')" % (", ".join(keys), "', '".join(values))
 
        pgconn.query(query)