Skip to content

Instantly share code, notes, and snippets.

@sempostma

sempostma/migrate_hikashop_cli.php

Created November 19, 2022 22:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sempostma/113987bdf51fb086726b377b726887d0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sempostma/113987bdf51fb086726b377b726887d0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sempostma created this gist Nov 19, 2022.
    1,110 changes: 1,110 additions & 0 deletions migrate_hikashop_cli.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,1110 @@
    <?php
    $counter = 0;
    $flags = array();
    $db1;
    $db2;
    $db1_table_prefix = '';
    $db2_table_prefix = '';
    $truncate = false;
    $truncate_user_groups = false;
    $batch_size = 1000;
    $id_padding = 10;
    $overwrite_user_login_credentials = false;
    $ignore_image_move_warning = false;
    $i_know_what_im_doing = false;
    $log_location = './log.txt';
    $logging_enabled = false;
    $exist_cache = array();

    function starts_with($string, $start_string)
    {
    $len = strlen($start_string);
    return (substr($string, 0, $len) === $start_string);
    }

    function array_remove(&$array, $item)
    {
    $index = array_search($item, $array);
    if ($index === false)
    return false;
    array_splice($array, $index, 1);
    return true;
    }

    function remove_prefix($string, $prefix)
    {
    if (substr($string, 0, strlen($prefix)) == $prefix) {
    $string = substr($string, strlen($prefix));
    }
    return $string;
    }

    for ($i = 1; $i < $argc; $i++) {
    $arg = $argv[$i];
    $is_option = starts_with($arg, '--');

    if ($is_option) {
    $kvp = remove_prefix($arg, '--');
    $kvp = explode('=', $kvp, 2);
    $key = $kvp[0];
    $value = array_key_exists(1, $kvp) ? $kvp[1] : NULL;

    switch ($key) {
    case 'db1_table_prefix':
    $db1_table_prefix = $value;
    break;

    case 'db2_table_prefix':
    $db2_table_prefix = $value;
    break;

    case 'overwrite_user_login_credentials':
    $overwrite_user_login_credentials = true;
    break;

    case 'truncate':
    $truncate = true;
    break;

    case 'truncate':
    $truncate = true;
    break;

    case 'truncate_user_groups':
    $truncate_user_groups = true;
    break;

    case 'ignore_image_move_warning':
    $ignore_image_move_warning = true;
    break;

    case 'log':
    $logging_enabled = true;
    $log_location = $value;
    break;

    case 'confirm':
    $i_know_what_im_doing = $value === 'i know what im doing';
    break;

    case 'id_padding':
    $value = intval($value);
    if ($value < 1) throw new Exception('id_padding must be larger than 1');
    if ($value > 1000000) throw new Exception('id_padding must not be larger than 1000000');
    $id_padding = $value;

    case 'batch_size':
    $value = intval($value);
    if ($batch_size < 1) throw new Exception('batch_size must be larger than 1');
    if ($batch_size > 1000000) throw new Exception('batch_size must not be larger than 1000000');
    $batch_size = $value;

    break;

    default:
    throw new Exception('Invalid option "' . $key . '"');
    break;
    }
    } else if ($counter === 0) {
    $db1 = parse_url($arg);
    } else if ($counter === 1) {
    $db2 = parse_url($arg);
    }

    if (!$is_option) $counter++;
    }

    if (!$i_know_what_im_doing) {
    echo "
    This is a powerful and dangerous tool and must be yielded with care.
    Please never execute this script on a production database.
    Pass in --confirm=\"i know what im doing\" to continue" . PHP_EOL;
    die();
    }

    if ($logging_enabled) {
    ob_start();
    }

    if (!$ignore_image_move_warning) {
    echo "
    Make sure to copy all images from one site to the other using ftp, rsync or whatever other method:
    https://www.hikashop.com/forum/install-update/896417-move-existing-image-directory.html
    " . PHP_EOL;
    }

    $arr = array($db2['host'], $db2['user'], $db2['pass'], ltrim($db2['path'], '/'), $db2['port']);

    $conn1 = mysqli_connect($db1['host'], $db1['user'], $db1['pass'], ltrim($db1['path'], '/'), $db1['port']);

    if ($conn1->connect_error) {
    die("Connection failed: " . $conn1->connect_error);
    }

    $conn2 = mysqli_connect($db2['host'], $db2['user'], $db2['pass'], ltrim($db2['path'], '/'), $db2['port']);

    if ($conn2->connect_error) {
    die("Connection failed: " . $conn2->connect_error);
    }

    try {

    $conn1->begin_transaction();
    $conn2->begin_transaction();

    $truncate_list = array();

    if ($truncate) {
    array_push(
    $truncate_list,
    'hikashop_product',
    'hikashop_product_category',
    'hikashop_product_related',
    'hikashop_shipping',
    'hikashop_shipping_price',
    'hikashop_tax',
    'hikashop_taxation',
    'hikashop_price',
    'hikashop_file',
    'hikashop_field',
    'hikashop_entry',
    'hikashop_email_log',
    'hikashop_download',
    'hikashop_discount',
    'hikashop_currency',
    'hikashop_click',
    'hikashop_characteristic',
    'hikashop_category',
    'hikashop_cart_product',
    'hikashop_cart',
    'hikashop_address',
    'hikashop_user',
    'hikashop_file',
    'hikashop_variant',
    'hikashop_zone',
    'hikashop_zone_link'
    );
    }

    if ($truncate_user_groups || $truncate) {
    $truncate_list[] = 'viewlevels';
    $truncate_list[] = 'usergroups';
    $truncate_list[] = 'user_usergroup_map';
    }

    function get_columns($table)
    {
    static $cache = array();

    if (array_key_exists($table, $cache)) return $cache[$table];

    echo "get list of columns for table: " . $table . PHP_EOL;

    global $db2_table_prefix;
    global $conn2;
    $t2 = $db2_table_prefix . $table;

    $result = $conn2->query("
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = '" . $t2 . "'
    ");

    if (!$result) throw new Exception(mysqli_error($conn2));
    $column_names = array();
    if ($result->num_rows > 0) {
    while ($data = $result->fetch_assoc()) {
    $column_names[] = $data['column_name'];
    }
    }
    $cache[$table] = $column_names;
    return $column_names;
    }

    function table_exists($conn, $table_with_prefix)
    {
    echo "check if table exists " . $table_with_prefix . PHP_EOL;

    $t2 = $table_with_prefix;

    $result = $conn->query("
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = '" . $t2 . "'
    ");

    if (!$result) throw new Exception(mysqli_error($conn));
    return $result->num_rows > 0;
    }

    function table_exists_on_both_dbs($table)
    {
    static $cache = array();

    if (array_key_exists($table, $cache)) return $cache[$table];

    echo "get list of columns for table: " . $table . PHP_EOL;

    global $db2_table_prefix, $db1_table_prefix, $conn2, $conn1;

    $exists = table_exists($conn1, $db1_table_prefix . $table)
    && table_exists($conn2, $db2_table_prefix . $table);

    $cache[$table] = $exists;
    return $exists;
    }

    function get_primary_id_column_name($table)
    {
    static $cache = array();

    if (array_key_exists($table, $cache)) return $cache[$table];

    echo "get primary id column for table: " . $table . PHP_EOL;

    global $db1_table_prefix;
    global $conn1;
    $t1 = $db1_table_prefix . $table;

    $stmt = $conn1->prepare("
    SELECT COLUMN_NAME as column_name
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = ?
    AND CONSTRAINT_NAME = 'PRIMARY'
    ");
    if (!$stmt) throw new Exception(mysqli_error($conn1));
    $stmt->bind_param('s', $t1);
    $stmt->execute();
    $stmt->bind_result($column_name);
    $stmt->fetch();
    $stmt->close();
    $cache[$table] = $column_name;
    return $column_name;
    }

    function id_offset($table)
    {
    static $cache = array();
    if (array_key_exists($table, $cache)) return $cache[$table];
    echo "get id offset for table: " . $table . PHP_EOL;
    global $db1_table_prefix;
    global $db2_table_prefix;
    global $conn1;
    global $conn2;
    $primary_column = get_primary_id_column_name($table);
    $t1 = $db1_table_prefix . $table;
    $t2 = $db2_table_prefix . $table;

    $q1 = "SELECT MIN(" . $primary_column . ") as min1 FROM " . $t1;
    $res1 = $conn1->query($q1);
    if (!$res1) throw new Exception("statement: " . $q1 . ", error: " . mysqli_error($conn1));
    $q2 = "SELECT MAX(" . $primary_column . ") as max2 FROM " . $t2;
    $res2 = $conn2->query($q2);
    if (!$res2) throw new Exception("statement: " . $q2 . ", error: " . mysqli_error($conn2));

    $row1 = $res1->fetch_assoc();
    $row2 = $res2->fetch_assoc();

    $max2 = $row2['max2'] || 0;
    $min1 = $row1['min1'] || 0;

    $result = $max2 - $min1;
    echo "id offset for table: " . $table . ' is ' . $result . ', minimum id for db1 is ' . $min1 . ' and maximum id for db2 is ' . $max2 . PHP_EOL;
    $cache[$table] = $result;
    return $result;
    }

    function process_user_id($db1user_id)
    {
    static $cache = array();
    global $exist_cache;

    if (array_key_exists($db1user_id, $cache)) return $cache[$db1user_id];
    global $db1_table_prefix, $db2_table_prefix, $conn1, $conn2;
    $t1 = $db1_table_prefix . 'users';
    $t2 = $db2_table_prefix . 'users';
    $sql = 'SELECT * FROM ' . $t1 . ' WHERE id = ' . $db1user_id;
    $result = $conn1->query($sql);
    if (!$result) throw new Exception(mysqli_error($conn1) . ', ' . $sql);
    $row = $result->fetch_assoc();
    $result = $conn2->query('SELECT * FROM ' . $t2 . " WHERE email = '" . mysqli_real_escape_string($conn2, $row['email']) . "'");
    if (!$result) {
    throw new Exception(mysqli_error($conn2));
    }
    if ($result->num_rows === 0) {
    global $id_padding;
    $offset = id_offset('users');
    $id = $db1user_id + $offset + $id_padding;
    echo 'did not find a match for user with email: ' . $row['email'] . ' and id: ' . $db1user_id . '...' . PHP_EOL;
    $exists = false;
    $cache[$db1user_id] = $id;
    $exist_cache[$db1user_id] = $exists;
    return $id;
    } else if ($result->num_rows === 1) {
    $row = $result->fetch_assoc();
    $id = $row['id'];
    echo 'found a match for user with email: ' . $row['email'] . ' it already exists and has id: ' . $id . '...' . PHP_EOL;
    $exists = true;
    $cache[$db1user_id] = $id;
    $exist_cache[$db1user_id] = $exists;
    return $id;
    }
    }

    function process_id($table, $id)
    {
    if ($id == 0) return '0';
    if ($table === 'usergroups') return $id;
    if ($table === 'users') {
    return '' . process_user_id($id);
    } else {
    global $id_padding;
    $offset = id_offset($table);
    return '' . ($id + $offset + $id_padding);
    }
    }

    // truncate
    if (!$truncate && !$truncate_user_groups) {
    throw new Exception('You need to at least truncate usergroups');
    }
    foreach ($truncate_list as &$table) {
    $result = $conn2->query('TRUNCATE TABLE ' . $db2_table_prefix . $table);
    echo "truncated " . $db2_table_prefix . $table . ' table' . PHP_EOL;
    if (!$result) throw new Exception(mysqli_error($conn2));
    }

    // process categories

    echo 'processing categories...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_category';
    $t2 = $db2_table_prefix . 'hikashop_category';

    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $column_names = get_columns('hikashop_category');
    $list = array();

    function flush_data()
    {
    global $list, $conn2, $t2, $column_names;
    if (count($list) === 0) return;
    // flush buffer if count becomes too large
    $query_values = implode(",\n", $list);
    $list = array();
    $query = "INSERT INTO " . $t2 . " (" . implode(',', $column_names) . ") VALUES " . $query_values;
    $result = $conn2->query($query);
    if (!$result) {
    echo "table: " . $t2 . PHP_EOL;
    echo "statement: " . $query . PHP_EOL;
    throw new Exception(mysqli_error($conn2));
    }
    }

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['category_id'] = process_id('hikashop_category', $record['category_id']);
    if ($record['category_type'] !== 'root') {
    $record['category_parent_id'] = process_id('hikashop_category', $record['category_parent_id']);
    }
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process users
    echo 'processing users...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'users';
    $t2 = $db2_table_prefix . 'users';
    $column_names = get_columns('users');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $old_id = $record['id'];
    $columns = implode(',', array_keys($record));
    $new_id = process_user_id($old_id);
    $record['id'] = $new_id;
    // old id will now be in the exists cache (side effect)
    $exists = $exist_cache[$old_id];
    if ($exists) {
    $columns_to_update = $columns;
    array_remove($column_names, 'username');
    array_remove($column_names, 'email');
    array_remove($column_names, 'password');
    array_remove($column_names, 'password');
    echo 'user with email: ' . $record['email'] . ' already exists...' . PHP_EOL;
    $values = array_map(function ($column) use ($conn2, $record) {
    return $column . " = '" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $escaped_values = implode(', ', $values);
    $sql = 'UPDATE ' . $t2 . ' SET ' . $escaped_values . ' WHERE id = ' . $record['id'];
    $r = $conn2->query($sql);
    if (!$r) throw new Exception(mysqli_error($conn2) . ', sql: ' . $sql);
    } else {
    echo 'old id: ' . $old_id . PHP_EOL;
    echo 'old email: ' . $record['email'] . PHP_EOL;
    $values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $escaped_values = implode(', ', $values);
    $sql = 'INSERT INTO ' . $t2 . ' (' . $columns . ') VALUES (' . $escaped_values . ')';
    $r = $conn2->query($sql);
    if (!$r) throw new Exception(mysqli_error($conn2) . ', sql: ' . $sql);
    }
    }
    }

    // process usergroup map
    echo 'processing usergroup map...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'user_usergroup_map';
    $t2 = $db2_table_prefix . 'user_usergroup_map';
    $column_names = get_columns('user_usergroup_map');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $old_id = $record['user_id'];
    if ($old_id == 833) var_dump($record);
    $record['user_id'] = process_id('users', $record['user_id']);
    $record['group_id'] = process_id('usergroups', $record['group_id']);

    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();

    if ($old_id == 833) var_dump($record);
    }
    flush_data();
    }

    // process usergroups
    echo 'processing usergroups...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'usergroups';
    $t2 = $db2_table_prefix . 'usergroups';
    $column_names = get_columns('usergroups');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();
    $records = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop users
    echo 'processing hikashop users...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_user';
    $t2 = $db2_table_prefix . 'hikashop_user';
    $column_names = get_columns('hikashop_user');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['user_id'] = process_id('hikashop_user', $record['user_id']);
    $record['user_cms_id'] = process_id('users', $record['user_cms_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop viewlevels
    echo 'processing hikashop viewlevels...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'viewlevels';
    $t2 = $db2_table_prefix . 'viewlevels';
    $column_names = get_columns('viewlevels');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop products
    echo 'processing hikashop products...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_product';
    $t2 = $db2_table_prefix . 'hikashop_product';
    $column_names = get_columns('hikashop_product');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['product_id'] = process_id('hikashop_product', $record['product_id']);
    $record['product_parent_id'] = process_id('hikashop_product', $record['product_parent_id']);
    $record['product_tax_id'] = process_id('hikashop_tax', $record['product_tax_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop product-category relations
    echo 'processing hikashop product-category relations...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_product_category';
    $t2 = $db2_table_prefix . 'hikashop_product_category';
    $column_names = get_columns('hikashop_product_category');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['product_category_id'] = process_id('hikashop_product_category', $record['product_category_id']);
    $record['category_id'] = process_id('hikashop_category', $record['category_id']);
    $record['product_id'] = process_id('hikashop_product', $record['product_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop product-product relations
    echo 'processing hikashop product-product relations...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_product_related';
    $t2 = $db2_table_prefix . 'hikashop_product_related';
    $column_names = get_columns('hikashop_product_related');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['product_id'] = process_id('hikashop_product', $record['product_id']);
    $record['product_related_id'] = process_id('hikashop_product', $record['product_related_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop currency
    echo 'processing hikashop currencies...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_currency';
    $t2 = $db2_table_prefix . 'hikashop_currency';
    $column_names = get_columns('hikashop_currency');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['currency_id'] = process_id('hikashop_currency', $record['currency_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }


    // process hikashop product-product relations
    echo 'processing hikashop price...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_price';
    $t2 = $db2_table_prefix . 'hikashop_price';
    $column_names = get_columns('hikashop_price');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['price_id'] = process_id('hikashop_price', $record['price_id']);
    $record['price_currency_id'] = process_id('hikashop_currency', $record['price_currency_id']);
    $record['price_product_id'] = process_id('hikashop_product', $record['price_product_id']);

    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }


    // process hikashop shipping
    echo 'processing hikashop shipping...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_shipping';
    $t2 = $db2_table_prefix . 'hikashop_shipping';
    $column_names = get_columns('hikashop_shipping');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['shipping_id'] = process_id('hikashop_shipping', $record['shipping_id']);
    $record['shipping_tax_id'] = process_id('hikashop_tax', $record['shipping_tax_id']);
    $record['shipping_currency_id'] = process_id('hikashop_currency', $record['shipping_currency_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop shipping prices
    echo 'processing hikashop shipping prices...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_shipping_price';
    $t2 = $db2_table_prefix . 'hikashop_shipping_price';
    $column_names = get_columns('hikashop_shipping_price');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['shipping_price_id'] = process_id('hikashop_shipping_price', $record['shipping_price_id']);
    $record['shipping_id'] = process_id('hikashop_shipping', $record['shipping_id']);
    $record['shipping_currency_id'] = process_id('hikashop_currency', $record['shipping_currency_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop shipping tax data
    echo 'processing hikashop tax data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_tax';
    $t2 = $db2_table_prefix . 'hikashop_tax';
    $column_names = get_columns('hikashop_tax');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop zones data
    echo 'processing hikashop zones data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_zone';
    $t2 = $db2_table_prefix . 'hikashop_zone';
    $column_names = get_columns('hikashop_zone');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['zone_id'] = process_id('hikashop_zone', $record['zone_id']);
    $record['zone_currency_id'] = process_id('hikashop_currency', $record['zone_currency_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop zone links
    echo 'processing hikashop zone links...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_zone_link';
    $t2 = $db2_table_prefix . 'hikashop_zone_link';
    $column_names = get_columns('hikashop_zone_link');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop taxation data
    echo 'processing hikashop taxation data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_taxation';
    $t2 = $db2_table_prefix . 'hikashop_taxation';
    $column_names = get_columns('hikashop_taxation');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['taxation_id'] = process_id('hikashop_taxation', $record['taxation_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    if (table_exists_on_both_dbs('hikashop_file')) {
    // process hikashop files
    echo 'processing hikashop files data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_file';
    $t2 = $db2_table_prefix . 'hikashop_file';
    $column_names = get_columns('hikashop_file');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['file_id'] = process_id('hikashop_file', $record['file_id']);
    if ($record['file_type'] === 'product') {
    $record['file_ref_id'] = process_id('hikashop_product', $record['file_ref_id']);
    } else if ($record['file_type'] === 'category') {
    $record['file_ref_id'] = process_id('hikashop_category', $record['file_ref_id']);
    }
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }
    } else {
    echo "skipping hikashop_file table because it does not exist on both databases..." . PHP_EOL;
    }

    if (table_exists_on_both_dbs('hikashop_field')) {

    // process hikashop field
    echo 'processing hikashop field data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_field';
    $t2 = $db2_table_prefix . 'hikashop_field';
    $column_names = get_columns('hikashop_field');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['field_id'] = process_id('hikashop_field', $record['field_id']);
    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    $val = array_key_exists($column, $record) ? $record[$column] : NULL;
    return "'" . mysqli_real_escape_string($conn2, $val) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }
    } else {
    echo "skipping hikashop_field table because it does not exist on both databases..." . PHP_EOL;
    }

    // process hikashop discounts
    echo 'processing hikashop discounts...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_discount';
    $t2 = $db2_table_prefix . 'hikashop_discount';
    $column_names = get_columns('hikashop_discount');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['discount_id'] = process_id('hikashop_discount', $record['discount_id']);

    $discount_access_ids = explode(',', $record['discount_access']);
    $new_dca_list = array();
    foreach ($discount_access_ids as $dca) {
    if (empty($dci)) {
    $new_dca_list[] = $dca;
    } else {
    $new_dca_list[] = process_id('usergroups', $dca);
    }
    }
    $record['discount_access'] = implode(',', $new_dca_list);

    $discount_category_ids = explode(',', $record['discount_category_id']);
    $new_dci_list = array();
    foreach ($discount_category_ids as $dci) {
    if (empty($dci)) {
    $new_dci_list[] = $dci;
    } else {
    $new_dci_list[] = process_id('hikashop_category', $dci);
    }
    }
    $record['discount_category_id'] = implode(',', $new_dci_list);
    $record['discount_product_id'] = process_id('hikashop_product', $record['discount_product_id']);
    $record['discount_zone_id'] = process_id('hikashop_zone', $record['discount_zone_id']);
    $record['discount_currency_id'] = process_id('hikashop_currency', $record['discount_currency_id']);
    $record['discount_tax_id'] = process_id('hikashop_tax', $record['discount_tax_id']);
    if (!empty($record['discount_user_id'])) $record['discount_user_id'] = process_id('hikashop_user', $record['discount_user_id']);

    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop characteristics
    echo 'processing hikashop characteristics...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_characteristic';
    $t2 = $db2_table_prefix . 'hikashop_characteristic';
    $column_names = get_columns('hikashop_characteristic');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['characteristic_id'] = process_id('hikashop_characteristic', $record['characteristic_id']);
    $record['characteristic_parent_id'] = process_id('hikashop_characteristic', $record['characteristic_parent_id']);

    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }


    // process hikashop adresses
    echo 'processing hikashop adresses...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_address';
    $t2 = $db2_table_prefix . 'hikashop_address';
    $column_names = get_columns('hikashop_address');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['address_id'] = process_id('hikashop_address', $record['address_id']);
    $record['address_user_id'] = process_id('hikashop_user', $record['address_user_id']);

    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // process hikashop variants
    echo 'processing hikashop variants...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_variant';
    $t2 = $db2_table_prefix . 'hikashop_variant';
    $column_names = get_columns('hikashop_variant');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
    $record = array_merge(array(), $row);
    $record['variant_characteristic_id'] = process_id('hikashop_characteristic', $record['variant_characteristic_id']);
    $record['variant_product_id'] = process_id('hikashop_product', $record['variant_product_id']);

    $columns = implode(',', array_keys($record));
    $escaped_values = array_map(function ($column) use ($conn2, $record) {
    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
    }, $column_names);
    $vals = implode(", ", $escaped_values);
    array_push($list, "(" . $vals . ")");
    if (count($list) >= $batch_size) flush_data();
    }
    flush_data();
    }

    // commit

    echo "done!" . PHP_EOL;

    if ($logging_enabled) {
    $output = ob_get_contents();
    ob_end_clean();
    file_put_contents($log_location, $output);
    }

    // $conn1->commit();
    // $conn2->commit();
    mysqli_rollback($conn1);
    mysqli_rollback($conn2);
    } catch (mysqli_sql_exception $exception) {
    mysqli_rollback($conn1);
    mysqli_rollback($conn2);

    if ($logging_enabled) {
    $output = ob_get_contents();
    ob_end_clean();
    file_put_contents($log_location, $output);
    }

    throw $exception;
    }