chrisv2/froxmerge.php

## froxmerge.php
#!/usr/bin/php
<?php

/**
 * merge the customers and domains from two or more Froxlor databases into a new one.
 *
 * Prerequisites:
 *   1.) all admins must exist in the dest database with the same loginname,
 *       we rewrite admin ids, but do not create/transfer admins
 *
 *   2.) domains, ftps, emailaccounts etc. must be unique, e.g. we cannot merge
 *       two databases both containing the same domains etc.
 *
 *   3.) the databases must be of the same version, e.g. if you want to merge an older
 *       installation, upgrade first, then merge.
 *
 *   4.) the absolute paths must be the same (e.g. /var/customers/ everywhere) - there is no
 *       path rewriting (yet)
 *
 * Usage:
 *   1.) upgrade all databases to be merged to the latest version
 *
 *   2.) import all databases to the local db server
 *
 *   3.) copy the first database to the dest db
 *
 *   4.) adapt global settings, especially IPs
 *
 *   5.) run this script for all other dbs, they get merged into dest db
 *
 *  Additional Notes:
 *
 *   1.) the merging may change the uid/gid of the user, since these must be unique. Make sure
 *       to chown the customer dirs properly after merging.
 *
 *   2.) if a customer exists in different databases, we try to merge accounts, databases and
 *       domains. However, for accounts (e.g. ftp, mail, ...) which exists in both databases,
 *       no settings (like password, shell, ...) will be merged (instead, the settings will be
 *       taken from the first database).
 *
 *   3.) IPs and Ports are merged using IP and Port as keys; depending on your setup you may
 *       want to reassign them later.
 */


const DEFAULT_ADMIN_ID = '1';

class FroxMerger
{

    function __construct($src, $dest)
    {
        $this->src = $src;
        $this->src->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->src->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

        $this->dest = $dest;
        $this->dest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->dest->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

        // we need sorting function for domains (stored procedure)
        // $this->src->exec(_storedproc);

        $this->admin_map = array();
        $this->customer_map = array();
        $this->customer_guids = array();
        $this->ipport_map = array();
        $this->domain_map = array();
        $this->account_map = array();
    }

    function merge()
    {
        // merge customers
        $this->merge_customers();

        // merge tickets
        $this->merge_tickets();

        // merge databases
        $this->merge_databases();

        // merge ftp_groups, ftp_users
        // TODO: ftp_quotalimits are not customer dependent?
        $this->merge_ftps();

        // merge htaccess, htpasswd
        $this->merge_htaccess();

        // merge ips and ports
        $this->merge_ips_ports();

        // merge domains (w/ IPs, redirects, SSL settings)
        $this->merge_domains();

        // merge mail_users, mail_virtual
        $this->merge_mail();

        // merge panel_syslog
        $this->merge_syslog();

        // merge diskspace & traffic
        // -> skip, takes VERY long
        // $this->merge_traffic();
    }

    function merge_customers()
    {
        print "*** merging customers ***\n";

        $stmt = $this->src->query("SELECT * FROM panel_customers");

        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $customer) {
            $old_customer_id = $customer['customerid'];
            // lookup customer
            $new_customer_id = $this->lookup_entity_by_name('panel_customers',
                'loginname',
                $old_customer_id,
                false,
                'customerid');
            if ($new_customer_id === false) {
                $guid = $this->get_next_available_guid();
                $replacements = array(
                    'adminid' => $this->get_dest_admin_id($customer['adminid']),
                    'guid' => $guid
                );
                $new_customer_id = $this->merge_entity_by_name('panel_customers',
                    'loginname',
                    $old_customer_id,
                    $replacements,
                    'customerid');
            } else {
                $guid = $this->qc($this->dest,
                    'SELECT guid FROM panel_customers WHERE customerid=?',
                    array($new_customer_id));
            }
            print "\t" . $customer['loginname'] . '/' . $guid . ' (' . $old_customer_id . ' => ' . $new_customer_id . ")\n";
            $this->customer_map[$old_customer_id] = $new_customer_id;
            $this->customer_guids[$old_customer_id] = $guid;
        }
    }

    /** merge tickets */
    function merge_tickets()
    {
        print "*** merging tickets ***\n";

        $stmt = $this->q($this->src, "SELECT * FROM panel_tickets", array());

        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $ticket) {
            unset($ticket['id']);
            $ticket['adminid'] = $this->get_dest_admin_id($ticket['adminid']);
            if ($ticket['customerid']) {
                $ticket['customerid'] = $this->customer_map[$ticket['customerid']];
            }
            // handle category (may be NULL or 0)
            if ($ticket['category'])
                $ticket['category'] = $this->merge_entity_by_name('panel_ticket_categories',
                    'name',
                    $ticket['category']);
            $this->ins($this->dest, 'panel_tickets', $ticket);
            print "\tmerged \"" . $ticket['subject'] . "\"\n";
        }
    }

    function merge_databases()
    {
        print "*** merging databases ***\n";

        $stmt = $this->q($this->src, "SELECT * FROM panel_databases", array());
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            unset($row['id']);
            $row['customerid'] = $this->customer_map[$row['customerid']];
            if ($row['customerid']) {
                $this->ins($this->dest, 'panel_databases', $row);
                print "\tmerged " . $row['databasename'] . "\n";
            } else {
                print "\tskipped " . $row['databasename'] . "\n";
            }
        }
    }

    function merge_ftps()
    {

        print "*** merging ftpgroups ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM ftp_groups", array());
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            // if we merge the same customers from different dbs,
            // the ftp group may already be there
            $dest = $this->qs($this->dest,
                "SELECT * FROM ftp_groups WHERE groupname=?",
                array($row['groupname']));
            if ($dest !== false) {
                // group already exists, merge it
                $srcmembers = explode(',', $row['members']);
                $destmembers = explode(',', $dest['members']);
                $merged = array_unique(array_merge($srcmembers, $destmembers));
                $dest['members'] = implode(',', $merged);
                $this->upd($this->dest, 'ftp_groups', $dest);
                print "\tupdated " . $row['groupname'] . " (already existing in dest)\n";
            } else {
                // create new group
                unset($row['id']);
                $old_customer_id = $row['customerid'];
                if (array_key_exists($old_customer_id, $this->customer_map)) {
                    $row['customerid'] = $this->customer_map[$old_customer_id];
                    $row['gid'] = $this->customer_guids[$old_customer_id];
                    $this->ins($this->dest, 'ftp_groups', $row);
                    print "\tadded " . $row['groupname'] . "\n";
                } else {
                    print "\tskipping " . $row['groupname'] . " (pointing to nonexistent customer)\n";
                }
            }
        }

        print "*** merging ftpusers ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM ftp_users", array());
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $dest = $this->qs($this->dest,
                "SELECT * FROM ftp_users WHERE username=?",
                array($row['username']));
            if ($dest !== false) {
                print "\tskipping " . $row['username'] . " (already existing in dest)\n";
            } else {
                unset($row['id']);
                $old_customer_id = $row['customerid'];
                $row['customerid'] = $this->customer_map[$old_customer_id];
                if ($row['customerid']) {
                    $row['uid'] = $this->customer_guids[$old_customer_id];
                    $row['gid'] = $this->customer_guids[$old_customer_id];
                    $this->ins($this->dest, 'ftp_users', $row);
                    print "\tcreated " . $row['username'] . "\n";
                } else {
                    print "\tskipping " . $row['username'] . " (pointing to nonexistent customer)\n";
                }
            }
        }

        print "*** merging quotatallies ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM ftp_quotatallies", array());
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $dest = $this->qs($this->dest,
                'SELECT * FROM ftp_quotatallies WHERE name=?',
                array($row['name']));
            if ($dest === false) {
                $this->ins($this->dest, 'ftp_quotatallies', $row);
                print "\tmerged " . $row['name'] . "\n";
            } else {
                // skip, next traffic calculation will correct values
            }
        }
    }

    function merge_htaccess()
    {
        print "*** merging htaccess ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM panel_htaccess", array());
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            unset($row['id']);
            $row['customerid'] = $this->customer_map[$row['customerid']];
            if ($row['customerid']) {
                $this->ins($this->dest, 'panel_htaccess', $row);
                print "\tmerged " . $row['path'] . "\n";
            } else {
                print "\tskipping " . $row['path'] . "\n";
            }
        }

        print "*** merging htpasswd ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM panel_htpasswds", array());
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            unset($row['id']);
            $row['customerid'] = $this->customer_map[$row['customerid']];
            if ($row['customerid']) {
                $this->ins($this->dest, 'panel_htpasswds', $row);
                print "\tmerged " . $row['path'] . "\n";
            } else {
                print "\tskipping " . $row['path'] . "\n";
            }
        }
    }

    function merge_ips_ports()
    {
        print "*** merging ips and ports ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM panel_ipsandports");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $destid = $this->qc($this->dest,
                "SELECT id FROM panel_ipsandports WHERE ip=? and port=?",
                array($row['ip'], $row['port']));
            if ($destid === false) {
                $oldid = $row['id'];
                unset($row['id']);
                $this->ipport_map[$oldid] = $this->ins($this->dest, 'panel_ipsandports', $row);
            }
        }
    }

    function merge_domains()
    {
        print "*** merging domains ***\n";
        // build ipportmap
        $ipportmap = array();
        $stmt = $this->q($this->src, "SELECT * FROM panel_ipsandports");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $destid = $this->qc($this->dest,
                "SELECT id FROM panel_ipsandports WHERE ip=? and port=?",
                array($row['ip'], $row['port']));
            if ($destid !== false) {
                $ipportmap[$row['id']] = $destid;
            } else {
                $ipportmap[$row['id']] = ($row['ssl'] == '1') ? DEFAULT_IPPORT_HTTPS_ID : DEFAULT_IPPORT_HTTP_ID;
            }
        }
        $stmt = $this->q($this->src, "SELECT * FROM panel_domains ORDER BY id, parentdomainid, ismainbutsubto, aliasdomain");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $old_domain_id = $row['id'];
            unset($row['id']);
            if ($row['parentdomainid']) {
                $row['parentdomainid'] = $this->domain_map[$row['parentdomainid']];
            }
            if ($row['ismainbutsubto']) {
                $row['ismainbutsubto'] = $this->domain_map[$row['ismainbutsubto']];
            }
            if ($row['aliasdomain']) {
                $row['aliasdomain'] = $this->domain_map[$row['aliasdomain']];
            }
            // for subdomain created by customer, adminid is null
            if ($row['adminid'])
                $row['adminid'] = $this->get_dest_admin_id($row['adminid']);
            $row['customerid'] = $this->customer_map[$row['customerid']];

            // TODO: map phpsettingid (but we don't have that - it's always "1" here)

            $new_domain_id = $this->ins($this->dest, 'panel_domains', $row);
            $this->domain_map[$old_domain_id] = $new_domain_id;

            print "\tmerged " . $row['domain'] . "\n";
        }

        print "*** merging domaintoip ***\n\t";
        $stmt = $this->q($this->src, "SELECT * FROM panel_domaintoip");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $row['id_domain'] = $this->domain_map[$row['id_domain']];
            $row['id_ipandports'] = $this->ipport_map[$row['id_ipandports']];
            $this->ins($this->dest, 'panel_domaintoip', $row);
            print ".";
        }
        print "\n";

        print "*** merging domain_redirect_codes ***\n\t";
        $stmt = $this->q($this->src, "SELECT * FROM domain_redirect_codes");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $row['did'] = $this->domain_map[$row['did']];
            $this->ins($this->dest, 'domain_redirect_codes', $row);
            print ".";
        }
        print "\n";

        print "*** merging domain_ssl_settings ***\n\t";
        $stmt = $this->q($this->src, "SELECT * FROM domain_ssl_settings");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $row['domainid'] = $this->domain_map[$row['id_domain']];
            $this->ins($this->dest, 'domain_ssl_settings', $row);
            print ".";
        }
        print "\n";

    }

    function merge_mail()
    {

        print "*** merging mail_users ***\n";

        $stmt = $this->q($this->src, "SELECT * FROM mail_users");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $old_account_id = $row['id'];
            unset($row['id']);
            $row['domainid'] = $this->domain_map[$row['domainid']];
            $row['customerid'] = $this->customer_map[$row['customerid']];
            // we don't rewrite uid/gid as that's always 2000 in every
            // system we have
            $this->account_map[$old_account_id] = $this->ins($this->dest,
                'mail_users',
                $row);
            print "\tmerged " . $row['email'] . "\n";
        }

        print "*** merging mail_virtual ***\n";

        $stmt = $this->q($this->src, "SELECT * FROM mail_virtual");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $old_account_id = $row['id'];
            unset($row['id']);
            $row['domainid'] = $this->domain_map[$row['domainid']];
            $row['customerid'] = $this->customer_map[$row['customerid']];
            if ($row['popaccountid'])
                $row['popaccountid'] = $this->account_map[$row['popaccountid']];
            $this->ins($this->dest, 'mail_virtual', $row);
            print "\tmerged " . $row['email'] . "\n";
        }

    }

    function merge_syslog()
    {
        print "*** merging syslog ***\n\t";

        $stmt = $this->q($this->src, "SELECT * FROM panel_syslog");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            // TODO: eventually add some identifier to the text to indicate the source
            unset($row['logid']);
            $this->ins($this->dest, 'panel_syslog', $row);
            print ".";
        }
        print "\n";

    }

    function merge_traffic()
    {

        print "*** merging customer traffic ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM panel_traffic", array());
        $count = 0;
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            unset($row['id']);
            $row['customerid'] = $this->customer_map[$row['customerid']];
            if ($row['customerid']) {
                $this->ins($this->dest, 'panel_traffic', $row);
            }
            $count++;
            if ($count % 1000 == 0) {
                print "\tmerged $count rows\n";
                flush();
            }
        }

        print "*** merging admin traffic ***\n";
        $count = 0;
        $stmt = $this->q($this->src, "SELECT * FROM panel_traffic_admins", array());
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            unset($row['id']);
            $row['adminid'] = $this->get_dest_admin_id($row['adminid']);
            if ($row['adminid']) {
                $this->ins($this->dest, 'panel_traffic_admins', $row);
            }
            $count++;
            if ($count % 1000 == 0) {
                print "\tmerged $count rows\n";
                flush();
            }
        }

        print "*** merging customer diskspace ***\n";
        $stmt = $this->q($this->src, "SELECT * FROM panel_diskspace", array());
        $count = 0;
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            unset($row['id']);
            $row['customerid'] = $this->customer_map[$row['customerid']];
            if ($row['customerid']) {
                $this->ins($this->dest, 'panel_diskspace', $row);
            }
            $count++;
            if ($count % 1000 == 0) {
                print "\tmerged $count rows\n";
                flush();
            }
        }

        print "*** merging admin diskspace ***\n";
        $count = 0;
        $stmt = $this->q($this->src, "SELECT * FROM panel_diskspace_admins", array());
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            unset($row['id']);
            $row['adminid'] = $this->get_dest_admin_id($row['adminid']);
            if ($row['adminid']) {
                $this->ins($this->dest, 'panel_diskspace_admins', $row);
            }
            $count++;
            if ($count % 1000 == 0) {
                print "\tmerged $count rows\n";
                flush();
            }
        }
    }


    /**
     * get the id for the new admin by searching over the name. If there's no admin with
     *  the given loginname in $dest, use DEFAULT_ADMIN_ID instead.
     *
     * @param $adminid  adminid in src database
     * @return int adminid in dest database
     */
    protected function get_dest_admin_id($adminid)
    {
        if (array_key_exists($adminid, $this->admin_map)) {
            return $this->admin_map[$adminid];
        } else {
            $result = $this->lookup_entity_by_name('panel_admins',
                'loginname',
                $adminid,
                DEFAULT_ADMIN_ID,
                'adminid');
            $this->admin_map[$adminid] = $result;
            return $result;
        }
    }

    protected function get_next_available_guid()
    {
        $guid = (int)$this->qc($this->dest, 'SELECT MAX(guid) FROM panel_customers');
        return $guid + 1;
    }

    /**
     * look up an entity in the dest system by a common, unique key such as loginname
     *
     * @param $table
     * @param $name_col
     * @param $src_id
     * @param bool|false $default
     * @param string $id_col
     * @return id in the new system, or $default if not found
     */
    protected function lookup_entity_by_name($table, $name_col, $src_id, $default = false, $id_col = 'id')
    {
        // retrieve id in old system
        $name = $this->qc($this->src,
            'SELECT `' . $name_col . '` FROM `' . $table . '` WHERE `' . $id_col . '`=?',
            array($src_id));

        // look it up in new system
        $destid = $this->qc($this->dest,
            'SELECT `' . $id_col . '` FROM `' . $table . '` WHERE `' . $name_col . '`=?',
            array($name));

        if ($destid === false) {
            return $default;
        } else {
            return $destid;
        }
    }

    /**
     * merge an entity by a common, unique key, such as loginname.
     *
     * This function looks up the entity in the source db, and either
     * returns the id of the matching column in the dest db, or creates
     * the entity in the dest db and returns the id of the newly created
     * row.
     *
     * @param string $table table name
     * @param string $name_col name of column with key field
     * @param mixed $src_id id in source table
     * @param array $replacements (optional) replacement values if entity is inserted
     * @param string $id_col (optional) name of primary key column
     * @return int id of merged entity
     */
    protected function merge_entity_by_name($table, $name_col, $src_id, $replacements = array(), $id_col = 'id')
    {
        // retrieve item in old system
        $src = $this->qs($this->src,
            'SELECT * FROM `' . $table . '` WHERE `' . $id_col . '`=?',
            array($src_id));
        $name = $src[$name_col];
        // look it up in new system
        $destid = $this->qc($this->dest,
            'SELECT `' . $id_col . '` FROM `' . $table . '` WHERE `' . $name_col . '`=?',
            array($name));
        if ($destid === false) {
            // not found, insert it
            unset($src[$id_col]);
            foreach ($replacements as $col => $val) {
                $src[$col] = $val;
            }
            return $this->ins($this->dest, $table, $src);
        } else {
            // found, return dest id
            return $destid;
        }
    }

    /**
     * helper: execute a query using prepared statement, and return the result
     * @param $connection
     * @param $query
     * @param $params
     * @return mixed
     */
    protected function q($connection, $query, $params = array())
    {
        $stmt = $connection->prepare($query);
        $stmt->execute($params);
        return $stmt;
    }

    /**
     * helper: fetch a single row
     * @param $connection
     * @param $query
     * @param $params
     * @return mixed row, or false if there are no rows
     */
    protected function qs($connection, $query, $params = array())
    {
        $result = false;
        $stmt = $this->q($connection, $query, $params);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $data) {
            $result = $data;
        }
        return $result;
    }

    /**
     * helper: execute a query trough prepared statement, returning a single column
     *
     * @param $connection
     * @param $query
     * @param $params
     * @return mixed
     */
    protected function qc($connection, $query, $params = array())
    {
        $stmt = $this->q($connection, $query, $params);
        $result = $stmt->fetchColumn();
        $stmt->closeCursor();
        unset($stmt);
        return $result;
    }

    /**
     * helper: insert data from associative array through PDO
     * @param $connection   PDO     db connection
     * @param $query    string  query
     * @param $data   array   data to insert (assoc. array)
     * @return  int    id of inserted element
     */
    protected function ins($connection, $table, $data)
    {
        $fields = array_keys($data);
        $values = array_values($data);
        $fieldlist = implode('`,`', $fields);
        $qs = str_repeat("?,", count($fields) - 1);

        $sql = "INSERT INTO `" . $table . "` (`" . $fieldlist . "`) VALUES (${qs}?)";

        $q = $connection->prepare($sql);
        $q->execute($values);
        return $connection->lastInsertId();
    }

    /**
     * helper:
     * @param $connection
     * @param $table
     * @param $data
     * @param string $keycol
     * @return mixed
     */
    protected function upd($connection, $table, $data, $keycol = 'id')
    {
        $key = $data[$keycol];
        unset($data[$keycol]);
        $fields = array_keys($data);
        $fieldlist = implode('`=?,`', $fields);
        $values = array_values($data);
        $values[] = $key;
        $sql = "UPDATE `" . $table . "` SET `" . $fieldlist . "`=? WHERE `" . $keycol . "`=?";
        $q = $connection->prepare($sql);
        $q->execute($values);
        return $key;
    }

}

function usage()
{
    print "\nphp froxmerge.php -u dbuser -p password [-h host] SRC_DATABASE DEST_DATABASE\n\n";
    print "merges SRC_DATABASE into DEST_DATABASE\n";
}

function get_db($dbname, $opts)
{
    $dsn = "mysql:charset=utf8;dbname=$dbname";
    if (array_key_exists('h', $opts)) {
        $hostname = $opts['h'];
        $dsn .= ";hostname=$hostname";
    }
    return new PDO($dsn, $opts['u'], $opts['p']);
}

/* parse arguments */

$shortopts = "";
$shortopts .= "u:"; // database user
$shortopts .= "p:"; // database password
$shortopts .= "h:"; // database host (optional)

$opts = getopt($shortopts);
$args = array_slice($argv, count($opts) * 2 + 1);

if (count($args) < 2 || !array_key_exists('u', $opts) || !array_key_exists('p', $opts)) {
    usage();
    exit();
}

/* build connections */
$src = get_db($args[0], $opts);
$dest = get_db($args[1], $opts);

/* run merger */
$merger = new FroxMerger($src, $dest);
$merger->merge();
	#!/usr/bin/php
	<?php

	/**
	* merge the customers and domains from two or more Froxlor databases into a new one.
	*
	* Prerequisites:
	* 1.) all admins must exist in the dest database with the same loginname,
	* we rewrite admin ids, but do not create/transfer admins
	*
	* 2.) domains, ftps, emailaccounts etc. must be unique, e.g. we cannot merge
	* two databases both containing the same domains etc.
	*
	* 3.) the databases must be of the same version, e.g. if you want to merge an older
	* installation, upgrade first, then merge.
	*
	* 4.) the absolute paths must be the same (e.g. /var/customers/ everywhere) - there is no
	* path rewriting (yet)
	*
	* Usage:
	* 1.) upgrade all databases to be merged to the latest version
	*
	* 2.) import all databases to the local db server
	*
	* 3.) copy the first database to the dest db
	*
	* 4.) adapt global settings, especially IPs
	*
	* 5.) run this script for all other dbs, they get merged into dest db
	*
	* Additional Notes:
	*
	* 1.) the merging may change the uid/gid of the user, since these must be unique. Make sure
	* to chown the customer dirs properly after merging.
	*
	* 2.) if a customer exists in different databases, we try to merge accounts, databases and
	* domains. However, for accounts (e.g. ftp, mail, ...) which exists in both databases,
	* no settings (like password, shell, ...) will be merged (instead, the settings will be
	* taken from the first database).
	*
	* 3.) IPs and Ports are merged using IP and Port as keys; depending on your setup you may
	* want to reassign them later.
	*/


	const DEFAULT_ADMIN_ID = '1';

	class FroxMerger
	{

	function __construct($src, $dest)
	{
	$this->src = $src;
	$this->src->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$this->src->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

	$this->dest = $dest;
	$this->dest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$this->dest->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

	// we need sorting function for domains (stored procedure)
	// $this->src->exec(_storedproc);

	$this->admin_map = array();
	$this->customer_map = array();
	$this->customer_guids = array();
	$this->ipport_map = array();
	$this->domain_map = array();
	$this->account_map = array();
	}

	function merge()
	{
	// merge customers
	$this->merge_customers();

	// merge tickets
	$this->merge_tickets();

	// merge databases
	$this->merge_databases();

	// merge ftp_groups, ftp_users
	// TODO: ftp_quotalimits are not customer dependent?
	$this->merge_ftps();

	// merge htaccess, htpasswd
	$this->merge_htaccess();

	// merge ips and ports
	$this->merge_ips_ports();

	// merge domains (w/ IPs, redirects, SSL settings)
	$this->merge_domains();

	// merge mail_users, mail_virtual
	$this->merge_mail();

	// merge panel_syslog
	$this->merge_syslog();

	// merge diskspace & traffic
	// -> skip, takes VERY long
	// $this->merge_traffic();
	}

	function merge_customers()
	{
	print "* merging customers *\n";

	$stmt = $this->src->query("SELECT * FROM panel_customers");

	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $customer) {
	$old_customer_id = $customer['customerid'];
	// lookup customer
	$new_customer_id = $this->lookup_entity_by_name('panel_customers',
	'loginname',
	$old_customer_id,
	false,
	'customerid');
	if ($new_customer_id === false) {
	$guid = $this->get_next_available_guid();
	$replacements = array(
	'adminid' => $this->get_dest_admin_id($customer['adminid']),
	'guid' => $guid
	);
	$new_customer_id = $this->merge_entity_by_name('panel_customers',
	'loginname',
	$old_customer_id,
	$replacements,
	'customerid');
	} else {
	$guid = $this->qc($this->dest,
	'SELECT guid FROM panel_customers WHERE customerid=?',
	array($new_customer_id));
	}
	print "\t" . $customer['loginname'] . '/' . $guid . ' (' . $old_customer_id . ' => ' . $new_customer_id . ")\n";
	$this->customer_map[$old_customer_id] = $new_customer_id;
	$this->customer_guids[$old_customer_id] = $guid;
	}
	}

	/** merge tickets */
	function merge_tickets()
	{
	print "* merging tickets *\n";

	$stmt = $this->q($this->src, "SELECT * FROM panel_tickets", array());

	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $ticket) {
	unset($ticket['id']);
	$ticket['adminid'] = $this->get_dest_admin_id($ticket['adminid']);
	if ($ticket['customerid']) {
	$ticket['customerid'] = $this->customer_map[$ticket['customerid']];
	}
	// handle category (may be NULL or 0)
	if ($ticket['category'])
	$ticket['category'] = $this->merge_entity_by_name('panel_ticket_categories',
	'name',
	$ticket['category']);
	$this->ins($this->dest, 'panel_tickets', $ticket);
	print "\tmerged \"" . $ticket['subject'] . "\"\n";
	}
	}

	function merge_databases()
	{
	print "* merging databases *\n";

	$stmt = $this->q($this->src, "SELECT * FROM panel_databases", array());
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	unset($row['id']);
	$row['customerid'] = $this->customer_map[$row['customerid']];
	if ($row['customerid']) {
	$this->ins($this->dest, 'panel_databases', $row);
	print "\tmerged " . $row['databasename'] . "\n";
	} else {
	print "\tskipped " . $row['databasename'] . "\n";
	}
	}
	}

	function merge_ftps()
	{

	print "* merging ftpgroups *\n";
	$stmt = $this->q($this->src, "SELECT * FROM ftp_groups", array());
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	// if we merge the same customers from different dbs,
	// the ftp group may already be there
	$dest = $this->qs($this->dest,
	"SELECT * FROM ftp_groups WHERE groupname=?",
	array($row['groupname']));
	if ($dest !== false) {
	// group already exists, merge it
	$srcmembers = explode(',', $row['members']);
	$destmembers = explode(',', $dest['members']);
	$merged = array_unique(array_merge($srcmembers, $destmembers));
	$dest['members'] = implode(',', $merged);
	$this->upd($this->dest, 'ftp_groups', $dest);
	print "\tupdated " . $row['groupname'] . " (already existing in dest)\n";
	} else {
	// create new group
	unset($row['id']);
	$old_customer_id = $row['customerid'];
	if (array_key_exists($old_customer_id, $this->customer_map)) {
	$row['customerid'] = $this->customer_map[$old_customer_id];
	$row['gid'] = $this->customer_guids[$old_customer_id];
	$this->ins($this->dest, 'ftp_groups', $row);
	print "\tadded " . $row['groupname'] . "\n";
	} else {
	print "\tskipping " . $row['groupname'] . " (pointing to nonexistent customer)\n";
	}
	}
	}

	print "* merging ftpusers *\n";
	$stmt = $this->q($this->src, "SELECT * FROM ftp_users", array());
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$dest = $this->qs($this->dest,
	"SELECT * FROM ftp_users WHERE username=?",
	array($row['username']));
	if ($dest !== false) {
	print "\tskipping " . $row['username'] . " (already existing in dest)\n";
	} else {
	unset($row['id']);
	$old_customer_id = $row['customerid'];
	$row['customerid'] = $this->customer_map[$old_customer_id];
	if ($row['customerid']) {
	$row['uid'] = $this->customer_guids[$old_customer_id];
	$row['gid'] = $this->customer_guids[$old_customer_id];
	$this->ins($this->dest, 'ftp_users', $row);
	print "\tcreated " . $row['username'] . "\n";
	} else {
	print "\tskipping " . $row['username'] . " (pointing to nonexistent customer)\n";
	}
	}
	}

	print "* merging quotatallies *\n";
	$stmt = $this->q($this->src, "SELECT * FROM ftp_quotatallies", array());
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$dest = $this->qs($this->dest,
	'SELECT * FROM ftp_quotatallies WHERE name=?',
	array($row['name']));
	if ($dest === false) {
	$this->ins($this->dest, 'ftp_quotatallies', $row);
	print "\tmerged " . $row['name'] . "\n";
	} else {
	// skip, next traffic calculation will correct values
	}
	}
	}

	function merge_htaccess()
	{
	print "* merging htaccess *\n";
	$stmt = $this->q($this->src, "SELECT * FROM panel_htaccess", array());
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	unset($row['id']);
	$row['customerid'] = $this->customer_map[$row['customerid']];
	if ($row['customerid']) {
	$this->ins($this->dest, 'panel_htaccess', $row);
	print "\tmerged " . $row['path'] . "\n";
	} else {
	print "\tskipping " . $row['path'] . "\n";
	}
	}

	print "* merging htpasswd *\n";
	$stmt = $this->q($this->src, "SELECT * FROM panel_htpasswds", array());
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	unset($row['id']);
	$row['customerid'] = $this->customer_map[$row['customerid']];
	if ($row['customerid']) {
	$this->ins($this->dest, 'panel_htpasswds', $row);
	print "\tmerged " . $row['path'] . "\n";
	} else {
	print "\tskipping " . $row['path'] . "\n";
	}
	}
	}

	function merge_ips_ports()
	{
	print "* merging ips and ports *\n";
	$stmt = $this->q($this->src, "SELECT * FROM panel_ipsandports");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$destid = $this->qc($this->dest,
	"SELECT id FROM panel_ipsandports WHERE ip=? and port=?",
	array($row['ip'], $row['port']));
	if ($destid === false) {
	$oldid = $row['id'];
	unset($row['id']);
	$this->ipport_map[$oldid] = $this->ins($this->dest, 'panel_ipsandports', $row);
	}
	}
	}

	function merge_domains()
	{
	print "* merging domains *\n";
	// build ipportmap
	$ipportmap = array();
	$stmt = $this->q($this->src, "SELECT * FROM panel_ipsandports");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$destid = $this->qc($this->dest,
	"SELECT id FROM panel_ipsandports WHERE ip=? and port=?",
	array($row['ip'], $row['port']));
	if ($destid !== false) {
	$ipportmap[$row['id']] = $destid;
	} else {
	$ipportmap[$row['id']] = ($row['ssl'] == '1') ? DEFAULT_IPPORT_HTTPS_ID : DEFAULT_IPPORT_HTTP_ID;
	}
	}
	$stmt = $this->q($this->src, "SELECT * FROM panel_domains ORDER BY id, parentdomainid, ismainbutsubto, aliasdomain");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$old_domain_id = $row['id'];
	unset($row['id']);
	if ($row['parentdomainid']) {
	$row['parentdomainid'] = $this->domain_map[$row['parentdomainid']];
	}
	if ($row['ismainbutsubto']) {
	$row['ismainbutsubto'] = $this->domain_map[$row['ismainbutsubto']];
	}
	if ($row['aliasdomain']) {
	$row['aliasdomain'] = $this->domain_map[$row['aliasdomain']];
	}
	// for subdomain created by customer, adminid is null
	if ($row['adminid'])
	$row['adminid'] = $this->get_dest_admin_id($row['adminid']);
	$row['customerid'] = $this->customer_map[$row['customerid']];

	// TODO: map phpsettingid (but we don't have that - it's always "1" here)

	$new_domain_id = $this->ins($this->dest, 'panel_domains', $row);
	$this->domain_map[$old_domain_id] = $new_domain_id;

	print "\tmerged " . $row['domain'] . "\n";
	}

	print "* merging domaintoip *\n\t";
	$stmt = $this->q($this->src, "SELECT * FROM panel_domaintoip");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$row['id_domain'] = $this->domain_map[$row['id_domain']];
	$row['id_ipandports'] = $this->ipport_map[$row['id_ipandports']];
	$this->ins($this->dest, 'panel_domaintoip', $row);
	print ".";
	}
	print "\n";

	print "* merging domain_redirect_codes *\n\t";
	$stmt = $this->q($this->src, "SELECT * FROM domain_redirect_codes");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$row['did'] = $this->domain_map[$row['did']];
	$this->ins($this->dest, 'domain_redirect_codes', $row);
	print ".";
	}
	print "\n";

	print "* merging domain_ssl_settings *\n\t";
	$stmt = $this->q($this->src, "SELECT * FROM domain_ssl_settings");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$row['domainid'] = $this->domain_map[$row['id_domain']];
	$this->ins($this->dest, 'domain_ssl_settings', $row);
	print ".";
	}
	print "\n";

	}

	function merge_mail()
	{

	print "* merging mail_users *\n";

	$stmt = $this->q($this->src, "SELECT * FROM mail_users");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$old_account_id = $row['id'];
	unset($row['id']);
	$row['domainid'] = $this->domain_map[$row['domainid']];
	$row['customerid'] = $this->customer_map[$row['customerid']];
	// we don't rewrite uid/gid as that's always 2000 in every
	// system we have
	$this->account_map[$old_account_id] = $this->ins($this->dest,
	'mail_users',
	$row);
	print "\tmerged " . $row['email'] . "\n";
	}

	print "* merging mail_virtual *\n";

	$stmt = $this->q($this->src, "SELECT * FROM mail_virtual");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	$old_account_id = $row['id'];
	unset($row['id']);
	$row['domainid'] = $this->domain_map[$row['domainid']];
	$row['customerid'] = $this->customer_map[$row['customerid']];
	if ($row['popaccountid'])
	$row['popaccountid'] = $this->account_map[$row['popaccountid']];
	$this->ins($this->dest, 'mail_virtual', $row);
	print "\tmerged " . $row['email'] . "\n";
	}

	}

	function merge_syslog()
	{
	print "* merging syslog *\n\t";

	$stmt = $this->q($this->src, "SELECT * FROM panel_syslog");
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
	// TODO: eventually add some identifier to the text to indicate the source
	unset($row['logid']);
	$this->ins($this->dest, 'panel_syslog', $row);
	print ".";
	}
	print "\n";

	}

	function merge_traffic()
	{

	print "* merging customer traffic *\n";
	$stmt = $this->q($this->src, "SELECT * FROM panel_traffic", array());
	$count = 0;
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	unset($row['id']);
	$row['customerid'] = $this->customer_map[$row['customerid']];
	if ($row['customerid']) {
	$this->ins($this->dest, 'panel_traffic', $row);
	}
	$count++;
	if ($count % 1000 == 0) {
	print "\tmerged $count rows\n";
	flush();
	}
	}

	print "* merging admin traffic *\n";
	$count = 0;
	$stmt = $this->q($this->src, "SELECT * FROM panel_traffic_admins", array());
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	unset($row['id']);
	$row['adminid'] = $this->get_dest_admin_id($row['adminid']);
	if ($row['adminid']) {
	$this->ins($this->dest, 'panel_traffic_admins', $row);
	}
	$count++;
	if ($count % 1000 == 0) {
	print "\tmerged $count rows\n";
	flush();
	}
	}

	print "* merging customer diskspace *\n";
	$stmt = $this->q($this->src, "SELECT * FROM panel_diskspace", array());
	$count = 0;
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	unset($row['id']);
	$row['customerid'] = $this->customer_map[$row['customerid']];
	if ($row['customerid']) {
	$this->ins($this->dest, 'panel_diskspace', $row);
	}
	$count++;
	if ($count % 1000 == 0) {
	print "\tmerged $count rows\n";
	flush();
	}
	}

	print "* merging admin diskspace *\n";
	$count = 0;
	$stmt = $this->q($this->src, "SELECT * FROM panel_diskspace_admins", array());
	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	unset($row['id']);
	$row['adminid'] = $this->get_dest_admin_id($row['adminid']);
	if ($row['adminid']) {
	$this->ins($this->dest, 'panel_diskspace_admins', $row);
	}
	$count++;
	if ($count % 1000 == 0) {
	print "\tmerged $count rows\n";
	flush();
	}
	}
	}


	/**
	* get the id for the new admin by searching over the name. If there's no admin with
	* the given loginname in $dest, use DEFAULT_ADMIN_ID instead.
	*
	* @param $adminid adminid in src database
	* @return int adminid in dest database
	*/
	protected function get_dest_admin_id($adminid)
	{
	if (array_key_exists($adminid, $this->admin_map)) {
	return $this->admin_map[$adminid];
	} else {
	$result = $this->lookup_entity_by_name('panel_admins',
	'loginname',
	$adminid,
	DEFAULT_ADMIN_ID,
	'adminid');
	$this->admin_map[$adminid] = $result;
	return $result;
	}
	}

	protected function get_next_available_guid()
	{
	$guid = (int)$this->qc($this->dest, 'SELECT MAX(guid) FROM panel_customers');
	return $guid + 1;
	}

	/**
	* look up an entity in the dest system by a common, unique key such as loginname
	*
	* @param $table
	* @param $name_col
	* @param $src_id
	* @param bool\|false $default
	* @param string $id_col
	* @return id in the new system, or $default if not found
	*/
	protected function lookup_entity_by_name($table, $name_col, $src_id, $default = false, $id_col = 'id')
	{
	// retrieve id in old system
	$name = $this->qc($this->src,
	'SELECT `' . $name_col . '` FROM `' . $table . '` WHERE `' . $id_col . '`=?',
	array($src_id));

	// look it up in new system
	$destid = $this->qc($this->dest,
	'SELECT `' . $id_col . '` FROM `' . $table . '` WHERE `' . $name_col . '`=?',
	array($name));

	if ($destid === false) {
	return $default;
	} else {
	return $destid;
	}
	}

	/**
	* merge an entity by a common, unique key, such as loginname.
	*
	* This function looks up the entity in the source db, and either
	* returns the id of the matching column in the dest db, or creates
	* the entity in the dest db and returns the id of the newly created
	* row.
	*
	* @param string $table table name
	* @param string $name_col name of column with key field
	* @param mixed $src_id id in source table
	* @param array $replacements (optional) replacement values if entity is inserted
	* @param string $id_col (optional) name of primary key column
	* @return int id of merged entity
	*/
	protected function merge_entity_by_name($table, $name_col, $src_id, $replacements = array(), $id_col = 'id')
	{
	// retrieve item in old system
	$src = $this->qs($this->src,
	'SELECT * FROM `' . $table . '` WHERE `' . $id_col . '`=?',
	array($src_id));
	$name = $src[$name_col];
	// look it up in new system
	$destid = $this->qc($this->dest,
	'SELECT `' . $id_col . '` FROM `' . $table . '` WHERE `' . $name_col . '`=?',
	array($name));
	if ($destid === false) {
	// not found, insert it
	unset($src[$id_col]);
	foreach ($replacements as $col => $val) {
	$src[$col] = $val;
	}
	return $this->ins($this->dest, $table, $src);
	} else {
	// found, return dest id
	return $destid;
	}
	}

	/**
	* helper: execute a query using prepared statement, and return the result
	* @param $connection
	* @param $query
	* @param $params
	* @return mixed
	*/
	protected function q($connection, $query, $params = array())
	{
	$stmt = $connection->prepare($query);
	$stmt->execute($params);
	return $stmt;
	}

	/**
	* helper: fetch a single row
	* @param $connection
	* @param $query
	* @param $params
	* @return mixed row, or false if there are no rows
	*/
	protected function qs($connection, $query, $params = array())
	{
	$result = false;
	$stmt = $this->q($connection, $query, $params);
	foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $data) {
	$result = $data;
	}
	return $result;
	}

	/**
	* helper: execute a query trough prepared statement, returning a single column
	*
	* @param $connection
	* @param $query
	* @param $params
	* @return mixed
	*/
	protected function qc($connection, $query, $params = array())
	{
	$stmt = $this->q($connection, $query, $params);
	$result = $stmt->fetchColumn();
	$stmt->closeCursor();
	unset($stmt);
	return $result;
	}

	/**
	* helper: insert data from associative array through PDO
	* @param $connection PDO db connection
	* @param $query string query
	* @param $data array data to insert (assoc. array)
	* @return int id of inserted element
	*/
	protected function ins($connection, $table, $data)
	{
	$fields = array_keys($data);
	$values = array_values($data);
	$fieldlist = implode('`,`', $fields);
	$qs = str_repeat("?,", count($fields) - 1);

	$sql = "INSERT INTO `" . $table . "` (`" . $fieldlist . "`) VALUES (${qs}?)";

	$q = $connection->prepare($sql);
	$q->execute($values);
	return $connection->lastInsertId();
	}

	/**
	* helper:
	* @param $connection
	* @param $table
	* @param $data
	* @param string $keycol
	* @return mixed
	*/
	protected function upd($connection, $table, $data, $keycol = 'id')
	{
	$key = $data[$keycol];
	unset($data[$keycol]);
	$fields = array_keys($data);
	$fieldlist = implode('`=?,`', $fields);
	$values = array_values($data);
	$values[] = $key;
	$sql = "UPDATE `" . $table . "` SET `" . $fieldlist . "`=? WHERE `" . $keycol . "`=?";
	$q = $connection->prepare($sql);
	$q->execute($values);
	return $key;
	}

	}

	function usage()
	{
	print "\nphp froxmerge.php -u dbuser -p password [-h host] SRC_DATABASE DEST_DATABASE\n\n";
	print "merges SRC_DATABASE into DEST_DATABASE\n";
	}

	function get_db($dbname, $opts)
	{
	$dsn = "mysql:charset=utf8;dbname=$dbname";
	if (array_key_exists('h', $opts)) {
	$hostname = $opts['h'];
	$dsn .= ";hostname=$hostname";
	}
	return new PDO($dsn, $opts['u'], $opts['p']);
	}

	/* parse arguments */

	$shortopts = "";
	$shortopts .= "u:"; // database user
	$shortopts .= "p:"; // database password
	$shortopts .= "h:"; // database host (optional)

	$opts = getopt($shortopts);
	$args = array_slice($argv, count($opts) * 2 + 1);

	if (count($args) < 2 \|\| !array_key_exists('u', $opts) \|\| !array_key_exists('p', $opts)) {
	usage();
	exit();
	}

	/* build connections */
	$src = get_db($args[0], $opts);
	$dest = get_db($args[1], $opts);

	/* run merger */
	$merger = new FroxMerger($src, $dest);
	$merger->merge();