| 1: | <?php |
| 2: | |
| 3: | namespace Aurora\System\Console\Commands\Migrations; |
| 4: | |
| 5: | use Aurora\System\Api; |
| 6: | use Aurora\System\Console\Commands\BaseCommand; |
| 7: | use Illuminate\Database\Capsule\Manager as DB; |
| 8: | use Symfony\Component\Console\Command\Command; |
| 9: | use Symfony\Component\Console\Helper\ProgressBar; |
| 10: | use Symfony\Component\Console\Input\InputDefinition; |
| 11: | use Symfony\Component\Console\Input\InputInterface; |
| 12: | use Symfony\Component\Console\Input\InputOption; |
| 13: | use Symfony\Component\Console\Logger\ConsoleLogger; |
| 14: | use Symfony\Component\Console\Output\OutputInterface; |
| 15: | use Symfony\Component\Console\Question\ConfirmationQuestion; |
| 16: | use Illuminate\Database\Capsule\Manager as Capsule; |
| 17: | |
| 18: | class EavToSqlCommand extends BaseCommand |
| 19: | { |
| 20: | private $sFilePrefix = 'eav-to-sql-'; |
| 21: | |
| 22: | private $iOffset = 0; |
| 23: | private $iLimit = 1000; |
| 24: | private $oP8Settings = false; |
| 25: | private $logger = false; |
| 26: | |
| 27: | |
| 28: | |
| 29: | |
| 30: | public function __construct() |
| 31: | { |
| 32: | parent::__construct(); |
| 33: | \Aurora\Api::Init(); |
| 34: | } |
| 35: | |
| 36: | protected function configure(): void |
| 37: | { |
| 38: | $this->setName('migrate:eav-to-sql') |
| 39: | ->setDescription('Migrate EAV data structure to SQL') |
| 40: | ->setDefinition( |
| 41: | new InputDefinition([ |
| 42: | new InputOption('wipe', 'w'), |
| 43: | new InputOption('migrate-file', 'f'), |
| 44: | ]) |
| 45: | ); |
| 46: | } |
| 47: | |
| 48: | protected function getProperties($class, $object) |
| 49: | { |
| 50: | $extendedPropsUser = \Aurora\System\ObjectExtender::getInstance()->getExtendedProps($class); |
| 51: | $extendedProps = []; |
| 52: | foreach (array_keys($extendedPropsUser) as $extendedProp) { |
| 53: | if ($object->get($extendedProp)) { |
| 54: | $extendedProps[$extendedProp] = $object->get($extendedProp); |
| 55: | } |
| 56: | } |
| 57: | return $extendedProps; |
| 58: | } |
| 59: | |
| 60: | protected function rewriteFile($fd, $str) |
| 61: | { |
| 62: | ftruncate($fd, 0); |
| 63: | fseek($fd, 0, SEEK_END); |
| 64: | fwrite($fd, $str); |
| 65: | } |
| 66: | |
| 67: | protected function migrateMinHashes($oConnection) |
| 68: | { |
| 69: | if (!Capsule::schema()->hasTable('core_min_hashes')) { |
| 70: | return false; |
| 71: | } |
| 72: | if (!Capsule::schema()->hasTable('min_hashes')) { |
| 73: | return false; |
| 74: | } |
| 75: | |
| 76: | $sql = "TRUNCATE `" . $this->oP8Settings->DBPrefix . "core_min_hashes`;"; |
| 77: | $oConnection->execute($sql); |
| 78: | $sql = "SELECT * FROM " . $this->oP8Settings->DBPrefix . "min_hashes;"; |
| 79: | $oConnection->execute($sql); |
| 80: | if ($oConnection->ResultCount() > 0) { |
| 81: | $sql = "INSERT INTO " . $this->oP8Settings->DBPrefix . "core_min_hashes (`HashId`, `UserId`,`Hash`, `Data`, `ExpireDate`) |
| 82: | SELECT * |
| 83: | FROM " . $this->oP8Settings->DBPrefix . "min_hashes;"; |
| 84: | $oConnection->execute($sql); |
| 85: | } |
| 86: | return true; |
| 87: | } |
| 88: | |
| 89: | protected function migrateActivityHistory($oConnection) |
| 90: | { |
| 91: | if (!Capsule::schema()->hasTable('core_activity_history')) { |
| 92: | return false; |
| 93: | } |
| 94: | if (!Capsule::schema()->hasTable('activity_history')) { |
| 95: | return false; |
| 96: | } |
| 97: | $sql = "TRUNCATE `" . $this->oP8Settings->DBPrefix . "core_activity_history`;"; |
| 98: | $oConnection->execute($sql); |
| 99: | $sql = "INSERT INTO " . $this->oP8Settings->DBPrefix . "core_activity_history (`Id`, `UserId`, `ResourceType`,`ResourceId`, `IpAddress`, `Action`, `Timestamp`, `GuestPublicId`) |
| 100: | SELECT * |
| 101: | FROM " . $this->oP8Settings->DBPrefix . "activity_history;"; |
| 102: | $oConnection->execute($sql); |
| 103: | return true; |
| 104: | } |
| 105: | |
| 106: | protected function wipeP9Tables() |
| 107: | { |
| 108: | $aModels = $this->getAllModels(); |
| 109: | foreach ($aModels as $modelName => $modelPath) { |
| 110: | $model = str_replace('/', DIRECTORY_SEPARATOR, $modelPath); |
| 111: | $model = str_replace('\\', DIRECTORY_SEPARATOR, $model); |
| 112: | $model = explode(DIRECTORY_SEPARATOR, $model); |
| 113: | $modelClass = []; |
| 114: | |
| 115: | while ($model[0] !== 'modules') { |
| 116: | array_shift($model); |
| 117: | } |
| 118: | $model[0] = 'Modules'; |
| 119: | array_unshift($model, "Aurora"); |
| 120: | $model = implode('\\', $model); |
| 121: | $this->logger->info('wiping ' . $model::query()->getQuery()->from); |
| 122: | $model::truncate(); |
| 123: | } |
| 124: | } |
| 125: | |
| 126: | protected function jsonPretify($sJsonStr) |
| 127: | { |
| 128: | $sOutput = '{'; |
| 129: | $bFirstElement = true; |
| 130: | foreach ($sJsonStr as $key => $value) { |
| 131: | if (!$bFirstElement) { |
| 132: | $sOutput .= ","; |
| 133: | } |
| 134: | $bFirstElement = false; |
| 135: | |
| 136: | $sOutput .= PHP_EOL . "\t\"" . $key . "\": ["; |
| 137: | $sOutput .= PHP_EOL . "\t\t" . implode(',', $value); |
| 138: | $sOutput .= PHP_EOL . "\t]"; |
| 139: | } |
| 140: | $sOutput .= PHP_EOL . '}'; |
| 141: | $sOutput = str_replace('\\', '\\\\', $sOutput); |
| 142: | |
| 143: | return $sOutput; |
| 144: | } |
| 145: | |
| 146: | protected function execute(InputInterface $input, OutputInterface $output): int |
| 147: | { |
| 148: | DB::statement('SET FOREIGN_KEY_CHECKS=0;'); |
| 149: | $migrateEntitiesList = []; |
| 150: | $offset = 0; |
| 151: | $time = new \DateTime(); |
| 152: | $time = $time->format('YmdHis'); |
| 153: | $intProgress = 0; |
| 154: | $dirName = \Aurora\System\Api::DataPath() . "/migration-eav-to-sql"; |
| 155: | $filename = $dirName . "/migration-" . $time . ".txt"; |
| 156: | $progressFilename = $dirName . "/migration-progress.txt"; |
| 157: | $entitiesListFilename = $dirName . "/migration-list.txt"; |
| 158: | $missedEntitiesFilename = $dirName . "/migration-" . $time . "-missed-entities.txt"; |
| 159: | |
| 160: | $dirname = dirname($filename); |
| 161: | if (!is_dir($dirname)) { |
| 162: | mkdir($dirname, 0755, true); |
| 163: | } |
| 164: | |
| 165: | $verbosityLevelMap = array( |
| 166: | 'notice' => OutputInterface::VERBOSITY_NORMAL, |
| 167: | 'info' => OutputInterface::VERBOSITY_NORMAL, |
| 168: | ); |
| 169: | |
| 170: | $fdProgress = file_exists($progressFilename) ? fopen($progressFilename, 'r+') : false; |
| 171: | $this->logger = new ConsoleLogger($output, $verbosityLevelMap); |
| 172: | $this->oP8Settings = \Aurora\System\Api::GetSettings(); |
| 173: | $helper = $this->getHelper('question'); |
| 174: | |
| 175: | $wipe = $input->getOption('wipe'); |
| 176: | $migrateFile = $input->getOption('migrate-file'); |
| 177: | $defaultAnswer = $input->getOption('no-interaction'); |
| 178: | if ($wipe) { |
| 179: | if (!$defaultAnswer) { |
| 180: | $question = new ConfirmationQuestion('Do you really wish to wipe all data in target tables? (Y/N)', $defaultAnswer); |
| 181: | |
| 182: | if (!$helper->ask($input, $output, $question)) { |
| 183: | return Command::SUCCESS; |
| 184: | } |
| 185: | } |
| 186: | $this->wipeP9Tables(); |
| 187: | } elseif ($migrateFile) { |
| 188: | $sEntitiesList = @file_get_contents($entitiesListFilename, true); |
| 189: | if (!$sEntitiesList) { |
| 190: | $this->logger->error('Entities list is empty!'); |
| 191: | return false; |
| 192: | } |
| 193: | |
| 194: | $migrateEntitiesList = explode(',', $sEntitiesList); |
| 195: | if (!$defaultAnswer) { |
| 196: | $question = new ConfirmationQuestion("Proceed with migrating " . count($migrateEntitiesList) . " entities? (Y/N)", $defaultAnswer); |
| 197: | if (!$helper->ask($input, $output, $question)) { |
| 198: | return Command::SUCCESS; |
| 199: | } |
| 200: | } |
| 201: | } else { |
| 202: | if ($fdProgress) { |
| 203: | $progress = htmlentities(file_get_contents($progressFilename)); |
| 204: | $intProgress = intval($progress); |
| 205: | if ($intProgress) { |
| 206: | if (!$defaultAnswer) { |
| 207: | $question = new ConfirmationQuestion("Resume from entity with ID $intProgress (last successfully migrated)? (Y/N)", $defaultAnswer); |
| 208: | if (!$helper->ask($input, $output, $question)) { |
| 209: | return Command::SUCCESS; |
| 210: | } |
| 211: | } |
| 212: | $cItems = DB::Table('eav_entities')->select('id')->where('id', '<=', $intProgress)->get(); |
| 213: | $offset = count($cItems); |
| 214: | } else { |
| 215: | if (!$defaultAnswer) { |
| 216: | $question = new ConfirmationQuestion("File $progressFilename is invalid. Do you wish migrate all entities? (Y/N)", $defaultAnswer); |
| 217: | if (!$helper->ask($input, $output, $question)) { |
| 218: | return Command::SUCCESS; |
| 219: | } |
| 220: | } |
| 221: | } |
| 222: | } |
| 223: | } |
| 224: | |
| 225: | $fdProgress = fopen($progressFilename, 'a+') or die("Can't create migration-progress.txt file"); |
| 226: | $fdErrors = fopen($filename, 'w+') or die("Can't create migration-" . $time . ".txt file"); |
| 227: | $fdMissedIds = fopen($missedEntitiesFilename, 'w+') or die("Can't create migration-" . $time . "-missed-entities.txt file"); |
| 228: | |
| 229: | $sql = "SELECT id, entity_type FROM `" . $this->oP8Settings->DBPrefix . "eav_entities` GROUP BY id, entity_type"; |
| 230: | $sqlIn = ""; |
| 231: | foreach ($migrateEntitiesList as $entityId) { |
| 232: | $sqlIn .= $entityId . ','; |
| 233: | } |
| 234: | $sqlIn = substr($sqlIn, 0, -1); |
| 235: | |
| 236: | if ($migrateEntitiesList) { |
| 237: | $sql = "SELECT id, entity_type FROM `" . $this->oP8Settings->DBPrefix . "eav_entities` WHERE id IN ($sqlIn) GROUP BY id, entity_type;"; |
| 238: | } |
| 239: | |
| 240: | $oConnection = \Aurora\System\Api::GetConnection(); |
| 241: | $oConnection->execute($sql); |
| 242: | |
| 243: | $entities = []; |
| 244: | $passedEntities = 0; |
| 245: | while (false !== ($oRow = $oConnection->GetNextRecord())) { |
| 246: | if ($oRow->id <= $intProgress) { |
| 247: | $passedEntities++; |
| 248: | continue; |
| 249: | } |
| 250: | $entities[] = $oRow; |
| 251: | } |
| 252: | |
| 253: | $progressBar = new ProgressBar($output, $passedEntities + count($entities)); |
| 254: | $progressBar->setFormat('%current%/%max% [%bar%] %percent:3s%% %elapsed:6s%/%estimated:6s% %memory:6s%'); |
| 255: | $progressBar->start(); |
| 256: | $progressBar->advance($passedEntities); |
| 257: | |
| 258: | $this->migrateActivityHistory($oConnection); |
| 259: | $this->migrateMinHashes($oConnection); |
| 260: | $result = $this->migrate($fdProgress, $fdErrors, $migrateEntitiesList, $entities, $progressBar, $fdMissedIds, $wipe); |
| 261: | $this->rewriteFile($fdErrors, $this->jsonPretify($result['MissedEntities'])); |
| 262: | $this->rewriteFile($fdMissedIds, implode(',', $result['MissedIds'])); |
| 263: | |
| 264: | fclose($fdMissedIds); |
| 265: | fclose($fdErrors); |
| 266: | fclose($fdProgress); |
| 267: | return Command::SUCCESS; |
| 268: | } |
| 269: | |
| 270: | private function migrate($fdProgress, $fdErrors, $migrateEntitiesList, $entities, $progressBar, $fdMissedIds, $wipe) |
| 271: | { |
| 272: | $missedEntities = []; |
| 273: | $missedIds = []; |
| 274: | $contactsCache = []; |
| 275: | $groupsCache = []; |
| 276: | $migrateArray = []; |
| 277: | $truncatedTables = []; |
| 278: | |
| 279: | $modelsMap = [ |
| 280: | 'Aurora\Modules\Mail\Classes\Account' => 'Aurora\Modules\Mail\Models\MailAccount', |
| 281: | 'Aurora\Modules\OAuthIntegratorWebclient\Classes\Account' => 'Aurora\Modules\OAuthIntegratorWebclient\Models\OauthAccount', |
| 282: | 'Aurora\Modules\Mail\Classes\Sender' => 'Aurora\Modules\Mail\Models\TrustedSender' |
| 283: | ]; |
| 284: | |
| 285: | foreach ($entities as $i => $entity) { |
| 286: | try { |
| 287: | $migrateArray = ['Id' => $entity->id]; |
| 288: | if (!class_exists($entity->entity_type)) { |
| 289: | $missedEntities[$entity->entity_type][] = $entity->id; |
| 290: | $missedIds[] = $entity->id; |
| 291: | $this->logger->warning("Didn't find EAV class for entity with id $entity->id, skipping."); |
| 292: | |
| 293: | $progressBar->advance(); |
| 294: | continue; |
| 295: | } |
| 296: | |
| 297: | $aItem = collect( |
| 298: | (new \Aurora\System\EAV\Query($entity->entity_type)) |
| 299: | ->where(['EntityId' => $entity->id]) |
| 300: | ->asArray() |
| 301: | ->exec() |
| 302: | )->first(); |
| 303: | $laravelModel = $modelsMap[$entity->entity_type] ?? str_replace('Classes', 'Models', $entity->entity_type); |
| 304: | |
| 305: | switch ($entity->entity_type) { |
| 306: | case 'Aurora\Modules\StandardAuth\Classes\Account': |
| 307: | $oItem = collect((new \Aurora\System\EAV\Query($entity->entity_type)) |
| 308: | ->where(['EntityId' => [$entity->id, '=']]) |
| 309: | ->exec())->first(); |
| 310: | $password = str_replace($oItem->Login, '', $oItem->Password); |
| 311: | $migrateArray['Password'] = $password; |
| 312: | break; |
| 313: | |
| 314: | case 'Aurora\Modules\Mail\Classes\Account': |
| 315: | $oItem = collect((new \Aurora\System\EAV\Query($entity->entity_type)) |
| 316: | ->where(['EntityId' => [$entity->id, '=']]) |
| 317: | ->exec())->first(); |
| 318: | $migrateArray['IncomingPassword'] = $oItem->getPassword(); |
| 319: | break; |
| 320: | |
| 321: | case 'Aurora\Modules\Contacts\Classes\GroupContact': |
| 322: | if (isset($contactsCache[$aItem['ContactUUID']])) { |
| 323: | $migrateArray['ContactId'] = $contactsCache[$aItem['ContactUUID']]; |
| 324: | } else { |
| 325: | $contact = collect( |
| 326: | (new \Aurora\System\EAV\Query('Aurora\Modules\Contacts\Classes\Contact')) |
| 327: | ->where(['UUID' => $aItem['ContactUUID']]) |
| 328: | ->asArray() |
| 329: | ->exec() |
| 330: | )->first(); |
| 331: | |
| 332: | $contactsCache[$aItem['ContactUUID']] = $contact['EntityId']; |
| 333: | $migrateArray['ContactId'] = $contact['EntityId']; |
| 334: | } |
| 335: | |
| 336: | if (isset($groupsCache[$aItem['GroupUUID']])) { |
| 337: | $migrateArray['GroupId'] = $groupsCache[$aItem['GroupUUID']]; |
| 338: | } else { |
| 339: | $group = collect( |
| 340: | (new \Aurora\System\EAV\Query('Aurora\Modules\Contacts\Classes\Group')) |
| 341: | ->where(['UUID' => $aItem['GroupUUID']]) |
| 342: | ->asArray() |
| 343: | ->exec() |
| 344: | )->first(); |
| 345: | $groupsCache[$aItem['GroupUUID']] = $group['EntityId']; |
| 346: | $migrateArray['GroupId'] = $group['EntityId']; |
| 347: | } |
| 348: | break; |
| 349: | |
| 350: | default: |
| 351: | break; |
| 352: | } |
| 353: | |
| 354: | $properties = $this->getProperties($entity->entity_type, collect($aItem)); |
| 355: | if ($properties) { |
| 356: | $migrateArray['Properties'] = $properties; |
| 357: | } |
| 358: | |
| 359: | if ($entity->entity_type === 'Aurora\Modules\Core\Classes\User' || $entity->entity_type === 'Aurora\Modules\Core\Classes\Tenant') { |
| 360: | $oItem = collect( |
| 361: | (new \Aurora\System\EAV\Query($entity->entity_type)) |
| 362: | ->where(['EntityId' => [$entity->id, '=']]) |
| 363: | ->exec() |
| 364: | )->first(); |
| 365: | $disabledModules = $oItem->getDisabledModules(); |
| 366: | if ($disabledModules) { |
| 367: | $migrateArray['Properties']['DisabledModules'] = implode('|', $disabledModules); |
| 368: | } |
| 369: | } |
| 370: | $newRow = $laravelModel::create(array_merge($aItem, $migrateArray)); |
| 371: | |
| 372: | $this->rewriteFile($fdProgress, $entity->id); |
| 373: | $progressBar->advance(); |
| 374: | } catch (\Illuminate\Database\QueryException $e) { |
| 375: | $errorCode = $e->getCode(); |
| 376: | $errorMessage = $e->getMessage(); |
| 377: | $shortErrorMessage = $e->errorInfo[2]; |
| 378: | $missedEntities[$entity->entity_type][] = $entity->id; |
| 379: | $missedIds[] = $entity->id; |
| 380: | $progressBar->advance(); |
| 381: | switch ($errorCode) { |
| 382: | case 23000: |
| 383: | $this->logger->error("Found duplicate for entity with id $entity->id, skipping."); |
| 384: | break; |
| 385: | |
| 386: | default: |
| 387: | $this->logger->error($shortErrorMessage); |
| 388: | break; |
| 389: | } |
| 390: | } finally { |
| 391: | $this->rewriteFile($fdErrors, $this->jsonPretify($missedEntities)); |
| 392: | $this->rewriteFile($fdMissedIds, implode(',', $missedIds)); |
| 393: | } |
| 394: | } |
| 395: | $this->logger->info('Migration Completed!'); |
| 396: | DB::statement('SET FOREIGN_KEY_CHECKS=1;'); |
| 397: | return ['MissedEntities' => $missedEntities, 'MissedIds' => $missedIds]; |
| 398: | } |
| 399: | } |
| 400: | |