Миграция данных MSSQL → MySQL (порциями)

Миграция как последовательное считывание записей порциями (по 100) с переносом связанных данных из других таблиц — пример CLI‑скрипта для Битрикс и устаревшего расширения mssql_* (исторический материал с pushorigin.ru).

Скрипт

/**
 *
 * Скрипт миграции данных во временную или реальную базу данных под CMS Bitrix
 *
**/

if(php_sapi_name() !== 'cli') die('Access denied'); // только по расписанию из CLI

$_SERVER["DOCUMENT_ROOT"] = dirname(dirname(dirname(dirname(__DIR__))));

define("LANGUAGE_ID", "s1");
define("NO_KEEP_STATISTIC", true);
define("NOT_CHECK_PERMISSIONS", true);
require($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/main/include/prolog_before.php");

// Зависимости
require_once($_SERVER["DOCUMENT_ROOT"].'/bitrix/components/vendor/migrate/lib/index.php');

class MSSQLMigration {

  private $KodClient = '00000000'; // <------- код клиента

  private $db, $limitPerQuery = 5, $offsetQuery2 = 5, $offsetQuery1 = 1, $rows, $fields, $docIds;
  public function __construct() {
    // подключение к БД
  }

  public function run() {
    $this->clear();
    $this->migrate();
  }

  private function clear() {
    $this->db->Query('TRUNCATE Document');
    $this->db->Query('TRUNCATE FieldMapping');
  }

  private function resetAndSetPortion($portion) {
    $this->offsetQuery1 = 1;
    $this->limitPerQuery = $portion;
    $this->offsetQuery2 = $portion;
  }

  private function migrate() {
    $this->resetAndSetPortion(100);
    $this->count = 0;
    while($this->fetchDocs($this->offsetQuery1, $this->offsetQuery2)) {
      $this->offsetQuery1 += $this->limitPerQuery; $this->offsetQuery2 += $this->limitPerQuery;
      $this->fetchFields();

      $this->pullDocs();
      $this->pullFieldMapping();
    }
  }

  private function pullDocs() {
    $this->db->Query("START TRANSACTION");
    foreach($this->rows as $row) {
      $query = "INSERT IGNORE INTO `Document` (`ID`, `IDEDO`, `SigOne`, `SigTwo`,
        `KeyOne`, `KeyTwo`, `NameFile`, `FileBody`, `IsError`)
        VALUES ('".addslashes($row['ID'])."', '".addslashes($row['IDEDO'])."', '".addslashes($row['SigOne'])."', '".addslashes($row['SigTwo'])."',
          '".addslashes($row['KeyOne'])."', '".addslashes($row['KeyTwo'])."', '".addslashes($row['NameFile'])."',
          ".$this->binData($row['FileBody']).", '".addslashes($row['IsError'])."')";
      $this->db->Query($query);
    }
    $this->db->Query("COMMIT");
  }

  private function pullFieldMapping() {
    $this->db->Query("START TRANSACTION");
    foreach($this->fields as $row) {
      $query = "INSERT INTO `FieldMapping` (`DocumentID`, `FieldID`, `FieldValue`)
        VALUES ('".addslashes($row['DocumentID'])."', '".addslashes($row['FieldID'])."', '".addslashes($row['FieldValue'])."')";
      $this->db->Query($query);
    }
    $this->db->Query("COMMIT");
  }

  private function fetchDocs($offsetQuery1, $offsetQuery2) {
    $offsetQuery1 = (int)$offsetQuery1;
    $offsetQuery2 = (int)$offsetQuery2;
    $this->rows = array();
    $this->docIds = array();
    $query = "WITH Documents AS
    (
      SELECT Document.*,
        ROW_NUMBER() OVER (ORDER BY Document.ID) AS 'RowNumber'
      FROM Document
      JOIN FieldMapping ON FieldMapping.DocumentId = Document.ID AND FieldMapping.FieldID = 39 AND FieldMapping.FieldValue = '{$this->KodClient}'
    )
    SELECT *
    FROM Documents
    WHERE RowNumber BETWEEN {$offsetQuery1} AND {$offsetQuery2}";

    $resp = mssql_query($query);
    if(!$resp) throw new Exception("MSSQL query error", 1);
    $this->count += mssql_num_rows($resp);
    while($row = mssql_fetch_assoc($resp)) {
      $this->docIds[] = $row['ID'];
      $this->rows[] = $row;
    }

    mssql_free_result($query);
    return is_array($this->rows) && count($this->rows) > 0;
  }

  private function fetchFields() {
    $this->fields = array();
    $query = "SELECT FieldMapping.*
      FROM FieldMapping
      WHERE FieldMapping.DocumentID IN(".implode(',', $this->docIds).")";
    $resp = mssql_query($query);
    if(!$resp) throw new Exception("MSSQL query error", 1);
    while($row = mssql_fetch_assoc($resp)) {
      $this->fields[] = $row;
    }
    mssql_free_result($query);
    return is_array($this->fields) && count($this->fields) > 0;
  }

  private function binData($binData) {
    if(strlen($binData) == 0) return 'NULL';
    return '0x'.bin2hex($binData);
  }

}

$begin_time = time() - 1272000000 + floatval(microtime());
$m = new MSSQLMigration();
$m->run();
$end_time = time() - 1272000000 + floatval(microtime()) - $begin_time;

echo 'Done: '.$end_time.PHP_EOL;