Миграция данных
Миграции представляет собой последовательное считывание записей порциями (по 100), с переносом связанных данных из других таблиц:
- migrate-mssql-mysql.php
/** * * Скрипт миграции данных во временную или реальную базу данных под 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;