201910101010_RemoveDoubleRoleLimitations.php

<?php

use Doctrine\DBAL\Connection;
use Symfony\Component\Console\Helper\ProgressBar;
use Symfony\Component\Console\Output\ConsoleOutput;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Class RemoveDoubleRoleLimitations.
 *
 * Script that remove all limitation doublons
 */
class RemoveDoubleRoleLimitations implements \Kaliop\eZMigrationBundle\API\MigrationInterface
{
    /** @var Connection */
    protected $dbHandler;

    /** @var OutputInterface */
    protected $output;

    /**
     * RemoveDoubleRoleLimitations constructor.
     * @param Connection $dbHandler
     * @param OutputInterface $output
     */
    public function __construct( Connection $dbHandler, OutputInterface $output )
    {
        $this->dbHandler = $dbHandler;
        $this->output = $output;
    }

    /**
     * @param ContainerInterface $container
     */
    public static function execute( ContainerInterface $container )
    {
        $command = new self(
            $container->get( 'ezpublish.persistence.connection' ),
            new ConsoleOutput()
        );

        return $command->run();
    }

    /**
     * {@inheritdoc}
     */
    protected function run()
    {
        $i = 0;
        $querySelectDoublon = $this->getQuerySelectDoublon();

        $statementSelect = $this->dbHandler->prepare($querySelectDoublon);
        $statementSelect->execute();
        $rows = $statementSelect->fetchAll( \PDO::FETCH_ASSOC );

        while(count($rows) > 0) {
            $i += count($rows);

            $this->deleteLimitations($rows);

            $this->output->writeln($i);

            $statementSelect = $this->dbHandler->prepare($this->getQuerySelectDoublon());
            $statementSelect->execute();
            $rows = $statementSelect->fetchAll( \PDO::FETCH_ASSOC );
        }
    }


    /**
     * Get query for limitation doublons
     *
     * @return string
     */
    private function getQuerySelectDoublon(){
        return 'SELECT
              v1.id
            FROM ezpolicy_limitation_value v1
                   INNER JOIN (
              SELECT v2.limitation_id,
                     count(value) as count,
                     value
              FROM ezpolicy_limitation_value v2
              GROUP BY limitation_id, value
              HAVING COUNT(value) > 1
              ORDER BY limitation_id, id
            ) doublon_limitation_id ON doublon_limitation_id.limitation_id = v1.limitation_id AND doublon_limitation_id.value = v1.value
            WHERE v1.id not in (
              SELECT v3.id
              FROM ezpolicy_limitation_value v3
              GROUP BY limitation_id, value
              HAVING COUNT(value) > 1
              ORDER BY limitation_id, id
            )
            ORDER BY v1.limitation_id, v1.value, v1.id
            LIMIT 500';
    }

    /**
     * Remove limitation Ids
     *
     * @param array $limitationIds
     */
    private function deleteLimitations(array $limitationIds) {
        if(is_null($limitationIds)) {
            $this->output->writeln('no limitation');
            return;
        }

        $progress = new ProgressBar( $this->output );
        $this->dbHandler->beginTransaction();

        $progress->start( count($limitationIds) );

        foreach($limitationIds as $limitation) {
            $queryDelete = sprintf('DELETE FROM ezpolicy_limitation_value WHERE id = %s', $limitation['id']);

            $statementDelete = $this->dbHandler->prepare($queryDelete);
            $statementDelete->execute();

            $progress->advance();
        }
        $this->dbHandler->commit();
        $progress->finish();
        $this->output->writeln('');
    }
}