Migration script for segments

test.php

<?php
namespace Piwik\Plugins\MyTest\Commands;

use Piwik\ArchiveProcessor\Rules;
use Piwik\Common;
use Piwik\DataAccess\ArchiveTableCreator;
use Piwik\Db;
use Piwik\Plugins\SitesManager\Model;
use Piwik\Plugins\VisitFrequency\API as VisitFrequencyAPI;
use Piwik\Segment;
use Piwik\Segment\SegmentExpression;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

/**
 * @package Piwik\Plugins\Cloud\Commands
 */
class MigrateSegmentGoalsChange extends CloudCommand
{
    protected function configure()
    {
        $this->setName('cloud:migrate-segment-goals-change');
        $this->setDescription('.');

        $this->addOption('yes', null, InputOption::VALUE_NONE, 'Not a dry run');
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $sites = new Model();
        $idSites = $sites->getSitesId();
        $dryRun = !$input->getOption('yes');

        $doneFlagsToMigrate = [];
        $differentSegments = [];
        foreach ($idSites as $idSite) {
            $output->writeln('Processing Site: ' . $idSite);
            $segmentStrings = Rules::getSegmentsToProcess([$idSite]);

            foreach ($segmentStrings as $segmentString) {
                $segment = new Segment($segmentString, [$idSite]);
                if ($segment->getOriginalString() === $segment->getString()) {
                    $output->writeln('Segment string is the same for ' . $segment->getString());
                    continue;
                }

                $differentSegments[] = $segment->getOriginalString() . ' ('. $segment->getHash().')';
                $segmentsToAppend = [VisitFrequencyAPI::NEW_VISITOR_SEGMENT, VisitFrequencyAPI::RETURNING_VISITOR_SEGMENT];
                foreach ($segmentsToAppend as $segmentToAppend) {
                    // we need to migrate the existing archive
                    $oldSegmentString = Segment::combine($segment->getString(), SegmentExpression::AND_DELIMITER, $segmentToAppend);
                    $newSegmentString = Segment::combine($segment->getOriginalString(), SegmentExpression::AND_DELIMITER, $segmentToAppend);
                    $oldSegmentHash = Segment::getSegmentHash($oldSegmentString);
                    $newSegmentHash = Segment::getSegmentHash($newSegmentString);

                    if ($oldSegmentHash === $newSegmentHash) {
                        $output->writeln('Old and new segment hash is the same for ' . $segmentString);
                        continue;
                    }

                    $doneFlagsToMigrate['done' . $oldSegmentHash . '.Goals'] = 'done' . $newSegmentHash . '.Goals';
                    $doneFlagsToMigrate['done' . $oldSegmentHash . '.VisitsSummary'] = 'done' . $newSegmentHash . '.VisitsSummary';
                    $doneFlagsToMigrate['done' . $oldSegmentHash . '.UserCountry'] =  'done' . $newSegmentHash . '.UserCountry';
                }
            }

        }

        if (empty($doneFlagsToMigrate)) {
            $output->writeln('nothing to migrate');
            return;
        }

        $output->writeln('Different segments: '. json_encode($differentSegments));

        foreach (ArchiveTableCreator::getTablesArchivesInstalled() as $table) {
            if (strpos($table, 'numeric') === false) {
                continue;
            }

            if ($dryRun) {
                $query = sprintf('SELECT idarchive, `name` from %s where `name` IN (%s)', $table, Common::getSqlStringFieldsArray($doneFlagsToMigrate));
                $archives = Db::fetchAll($query, array_keys($doneFlagsToMigrate));
                $output->writeln(sprintf('Found %s archives in %s', count($archives), $table));

                $idArchives = array_column($archives, 'idarchive');


                $query = sprintf('SELECT idarchive, `name` from %s where `idarchive` IN (%s)', $table, Common::getSqlStringFieldsArray($idArchives));
                $archiveEntries = Db::fetchAll($query, $idArchives);
                $output->writeln(sprintf('Found %s archive entries in %s', count($archiveEntries), $table));


            } else {

                $output->writeln('migrating ' . $table);

                $sql = 'update ' . $table . ' set `name` = (case';
                foreach ($doneFlagsToMigrate as $oldDoneFlag => $newDoneFlag) {
                    $sql .= " when `name`   = '$oldDoneFlag' then '$newDoneFlag' ";
                }
                $sql .= ' else `name` end) where `name` in (' . Common::getSqlStringFieldsArray($doneFlagsToMigrate) . ')';
                $bind = array_keys($doneFlagsToMigrate);

               // $output->writeln($sql);
                $affected = Db::query($sql, $bind);
                $output->writeln('Affected entries: ' . $affected->rowCount());
            }
        }
    }

}