Source of file TextOrJSONToRelationshipMigration.php
Size: 8,727 Bytes - Last Modified: 2021-12-23T10:42:12+00:00
/var/www/docs.ssmods.com/process/src/src/Tasks/TextOrJSONToRelationshipMigration.php
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246 | <?php namespace Sunnysideup\MigrateData\Tasks; use SilverStripe\Core\Config\Config; use SilverStripe\ORM\DB; use SilverStripe\Versioned\Versioned; /** * Used to debug a QueueJob. * * @todo: UPGRADE: remove after upgrade */ class TextOrJSONToRelationshipMigration extends MigrateDataTaskBase { /** * @var string */ protected $title = 'Migrate Text Or JSON to Proper Relationship in DB'; /** * @var string */ protected $description = ' For example, it can convert all the values of all DB columnns used by listbox fields from comma separated strings to JSON and then to Many Many'; protected $enabled = true; protected $sanitiseCharList = [ '"', '[', ']', "'", ]; protected $dbTablesTypes = [ '', '_Live', '_Versions', ]; protected $lookupClassNames = []; protected $tables = []; /** * add your data here, like this: * ClassNameA * OldField => NewRelation * ClassNameB * OldField => NewRelation. * * @var array */ private static $data_to_fix = []; /** * @throws \Exception */ protected function performMigration() { $dataToFix = $this->Config()->data_to_fix; if (0 === count($dataToFix)) { user_error('You need to specify at least some data to fix!'); } for ($i = 1; $i < 3; ++$i) { $this->flushNow('LOOP LOOP: ' . $i); foreach ($dataToFix as $className => $columns) { $this->flushNow('... LOOP ClassName: ' . $className); foreach ($this->dbTablesTypes as $tableExtension) { $this->flushNow('... ... LOOP Table Extension: ' . $tableExtension); foreach ($columns as $column => $lookupMethod) { $this->flushNow('... ... ... LOOP Field: ' . $column); $this->updateRows($className, $tableExtension, $column, $lookupMethod); $stage = null; if ('' === $tableExtension) { $stage = Versioned::DRAFT; } elseif ('_Live' === $tableExtension) { $stage = Versioned::LIVE; } if (null !== $stage) { $this->testRelationships($className, $lookupMethod, $stage); } } } } } } protected function updateRows(string $className, string $tableExtension, string $column, string $lookupMethod): void { $tableName = $this->getTableName($className, $tableExtension); $sql = ' SELECT "ID", ' . $column . ' FROM ' . $tableName . ';'; $rows = DB::query($sql); foreach ($rows as $row) { $id = $row['ID']; $this->flushNow( '... ... ... ... ' . 'LOOP Table: ' . $tableName . ' Row ID: ' . $row['ID'] ); $fieldValue = $row[$column]; $fieldValue = $this->updateRow($tableName, $id, $column, $fieldValue); $fieldValue = $this->updateEmptyRows($tableName, $id, $column, $fieldValue); if ('' === $tableExtension) { $this->addToRelationship($className, $id, $lookupMethod, $fieldValue); } } } protected function updateRow($tableName, $id, $column, $fieldValue): string { if (0 === strpos($fieldValue, '["') && strpos($fieldValue, '"]')) { $this->flushNow( '... ... ... ... ... ' . 'column ' . $column . ' in table: ' . $tableName . ' with row ID: ' . $id . ' already has the correct format, the value is: ' . $fieldValue, 'created' ); } else { //adding empty string ... $fieldValue = $this->sanitiseChars($fieldValue . ''); if ('' !== $fieldValue) { $fieldValue = json_encode(explode(',', $fieldValue)); $sql = ' UPDATE ' . $tableName . ' SET ' . $column . " = '" . $fieldValue . '\' WHERE ' . $tableName . '."ID" = ' . $id . ';'; $this->flushNow( '... ... ... ... ... ' . 'updating value of column ' . $column . ' in table: ' . $tableName . ' with row ID: ' . $id . ' to new value of ' . $fieldValue, 'repaired' ); $this->runUpdateQuery($sql); } else { $this->flushNow( '... ... ... ... ... ' . 'column ' . $column . ' in table: ' . $tableName . ' with row ID: ' . $id . " is empty so doesn't need to be updated", 'repaired' ); } } return $fieldValue; } protected function updateEmptyRows(string $tableName, int $id, string $column, string $fieldValue): string { $array = @json_decode($fieldValue, false); if (empty($array)) { $fieldValue = ''; $sql = ' UPDATE ' . $tableName . ' SET ' . $column . " = '" . $fieldValue . '\' WHERE ' . $tableName . '."ID" = ' . $id . ';'; $this->flushNow( '... ... ... ... ... ' . 'column ' . $column . ' in table: ' . $tableName . ' with row ID: ' . $id . ' had an incorrect empty value so has been updated to an empty string', 'repaired' ); DB::query($sql); } return $fieldValue; } protected function addToRelationship(string $className, int $id, string $lookupMethod, string $fieldValue): void { if ('' !== $fieldValue) { $array = @json_decode($fieldValue, false); if (! empty($array)) { $obj = $className::get_by_id($id); $lookupClassName = $this->getlookupClassName($className, $lookupMethod); $obj->{$lookupMethod}()->removeAll(); foreach ($array as $value) { $lookupItem = $lookupClassName::find_or_create(['Code' => $value]); $this->flushNow( '... ... ... ... ... ... ' . 'adding ' . $value . ' as many-many relation', 'created' ); $obj->{$lookupMethod}()->add($lookupItem); } } } } protected function sanitiseChars(string $value): string { foreach ($this->sanitiseCharList as $char) { if (false !== strpos($value, $char)) { $this->flushNow( '... ... ... ... ... ... ' . $char . ' was found in ' . $value . ' we are removing it', 'error' ); } $value = str_replace($char, '', $value); } return $value; } protected function getTableName(string $className, string $tableExtension): string { $key = $className . '_' . $tableExtension; if (! isset($this->tables[$key])) { $dbtable = Config::inst()->get($className, 'table_name'); $tableName = $dbtable . $tableExtension; $this->tables[$key] = $tableName; } return $this->tables[$key]; } protected function getLookupClassName(string $className, string $lookupMethod): string { $key = $className . '_' . $lookupMethod; if (! isset($this->lookupClassNames[$key])) { /** @var array $fields */ $fields = Config::inst()->get($className, 'has_many'); $fields += Config::inst()->get($className, 'many_many'); $fields += Config::inst()->get($className, 'belongs_many_many'); $this->lookupClassNames[$key] = $fields[$lookupMethod] ?? $this->lookupClassNames[$key]; } return $this->lookupClassNames[$key]; } protected function testRelationships(string $className, string $lookupMethod, $stage) { $objects = Versioned::get_by_stage($className, $stage); foreach ($objects as $obj) { $count = $obj->{$lookupMethod}()->count(); $this->flushNow( '... ... ... ' . 'Testing (' . $stage . ') - ' . $className . '.' . $lookupMethod . ' for ID ' . $obj->ID . ' => ' . $count ); } } } |