Import from U 232

<?php

$start = microtime(true);

$user = "username";
$pass= "secret";
$pu239_db = "dev";
$old_db = "temp";
$host = "localhost";
$limit = '1000'; // if mysql errors max packet or similar, decrease this by 100 until the errors go away

if ($user === 'username' || $pass === 'secret') {
    die("please edit this file\n");
}

$pdo = new PDO("mysql:host={$host};dbname={$pu239_db}", "$user", "$pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$pdo->setAttribute(PDO::ATTR_PERSISTENT, true);

$sql = "SET FOREIGN_KEY_CHECKS = 0;";
$pdo->exec($sql);

$tables = [
    'files',
    'requests',
    'request_votes',
    'friends',
    'forum_config',
    'forum_poll',
    'forum_poll_votes',
    'forums',
    'topics',
    'posts',
    'achievements',
    'snatched',
    'funds',
    'thanks',
    'thankyou',
    'rating',
    'wiki',
    'subscriptions',
    'reputation',
    'freeleech',
    'freeslots',
    'bookmarks',
];
foreach ($tables as $table) {
    echo "Truncating '{$pu239_db}.{$table}' table\n";
    $sql = "TRUNCATE TABLE {$pu239_db}.{$table}";
    $pdo->exec($sql);

    echo "Populating '{$pu239_db}.{$table}' table\n";
    $sql = "INSERT INTO {$pu239_db}.{$table} SELECT * FROM {$old_db}.{$table}";
    $pdo->exec($sql);

    compare_counts($table);
}

echo "Truncating '{$pu239_db}.users' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.users";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.users' table\n";
$iter = get_count("{$old_db}.users");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.users ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = ['secret', 'editsecret', 'passkey', 'show_shout', 'show_staffshout', 'shoutboxbg', 'support_lang', 'hash1', 'uclass', 'view_uclass'];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $values[$i]['passhash'] = bin2hex(random_bytes(32));
        $values[$i]['torrent_pass'] = bin2hex(random_bytes(32));
        $values[$i]['auth'] = bin2hex(random_bytes(32));
        $values[$i]['apikey'] = bin2hex(random_bytes(32));
        $values[$i]['ip'] = '';
        if(filter_var($row['ip'], FILTER_VALIDATE_IP) !== false) {
            $values[$i]['ip'] = inet_pton($row['ip']);
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.users (" . implode(', ', array_keys($values[0])) . ")", $values);
    unset($values);
}
compare_counts('users');

echo "Truncating '{$pu239_db}.torrents' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.torrents";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.torrents' table\n";
$iter = get_count("{$old_db}.torrents");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.torrents ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = ['username', 'type', 'motw', 'seedbox'];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.torrents (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('torrents');

echo "Truncating '{$pu239_db}.messages' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.messages";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.messages' table\n";
$iter = get_count("{$old_db}.messages");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.messages ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = [];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.messages (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('messages');


echo "Truncating '{$pu239_db}.staffmessages' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.staffmessages";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.staffmessages' table\n";
$iter = get_count("{$old_db}.staffmessages");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.staffmessages ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = [];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.staffmessages (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('staffmessages');

echo "Truncating '{$pu239_db}.polls' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.polls";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.polls' table\n";
$iter = get_count("{$old_db}.polls");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.polls ORDER BY pid ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = ['starter_name'];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.polls (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('polls');

echo "Truncating '{$pu239_db}.comments' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.comments";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.comments' table\n";
$iter = get_count("{$old_db}.comments");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.comments ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = [];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.comments (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('comments');

echo "Truncating '{$pu239_db}.categories' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.categories";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.categories' table\n";
$iter = get_count("{$old_db}.categories");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.categories ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = ['parent_id', 'tabletype'];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.categories (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('categories');

echo "Truncating '{$pu239_db}.usersachiev' table\n";
$sql = "TRUNCATE TABLE {$pu239_db}.usersachiev";
$pdo->exec($sql);

echo "Populating '{$pu239_db}.usersachiev' table\n";
$iter = get_count("{$old_db}.usersachiev");
for ($x = 0; $x < $iter; $x++) {
    $values = [];
    $y = $x + 1;
    $a = $y === 1 ? 0 : ($x * $limit);
    $sql = "SELECT * FROM {$old_db}.usersachiev ORDER BY id ASC LIMIT $a, $limit";
    $i = 0;
    foreach ($pdo->query($sql) as $row) {
        $ignore = ['username'];
        foreach ($row as $key => $value) {
            if (!in_array($key, $ignore)) {
                if ($key === 'id') {
                    $key = 'userid';
                }
                $values[$i][$key] = $value;
            }
        }
        $i++;
    }
    insertMultiple("INSERT INTO {$pu239_db}.usersachiev (" . implode(', ', array_keys($values[0])) . ")", $values);
}
compare_counts('usersachiev');

$sql = "SET FOREIGN_KEY_CHECKS = 1;";
$pdo->exec($sql);

$end = (microtime(true) - $start);
echo "Completed in " . gmdate("H:i:s", $end) . "\n\n";

function compare_counts($table) {
    global $old_db, $pu239_db, $pdo;

    echo "Comparing table counts ";
    $sql = "SELECT COUNT(*) FROM {$old_db}.{$table}";
    $stmt1 = $pdo->query($sql)->fetchColumn();

    $sql = "SELECT COUNT(*) FROM {$pu239_db}.{$table}";
    $stmt2 = $pdo->query($sql)->fetchColumn();
    if ($stmt1 === $stmt2) {
        echo "$stmt1 === $stmt2\n\n";
    } else {
        die("\n$stmt1 !== $stmt2\n");
    }
}


function insertMultiple($query, $rows) {
    global $pdo;
    if (count($rows)>0) {
        $args = array_fill(0, count($rows[0]), '?');

        $params = array();
        foreach($rows as $row) {
            $values[] = "(" . implode(',', $args) . ")";
            foreach($row as $value) {
                $params[] = $value;
            }
        }

        $query = $query . " VALUES " . implode(',', $values);
        $stmt = $pdo->prepare($query);
        $stmt->execute($params);
    }
}

function get_count($table) {
    global $pdo, $limit;

    $sql = "SELECT COUNT(*) FROM {$table}";
    $count = $pdo->query($sql)->fetchColumn();
    return ceil($count / $limit);
}