<commit_before><?php
/**
 * @author <NAME> <<EMAIL>>
 * @copyright Zicht Online <http://www.zicht.nl>
 */
namespace Zicht\Bundle\PageBundle\Command;

use Doctrine\DBAL\Connection;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Helper\Table;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Question\ConfirmationQuestion;
use Zicht\Itertools as iter;

/**
 * Class CleanAliasCommand
 *
 * Remove aliases referencing pages that do not exist anymore.
 */
class CleanAliasCommand extends ContainerAwareCommand
{
    /**
     * {@inheritDoc}
     */
    protected function configure()
    {
        $this
            ->setName('zicht:page:clean:alias')
            ->addArgument('locale', InputArgument::REQUIRED, 'Page locale to clean')
            ->setDescription('Clean aliases that are not attached to pages any more.');
    }

    /**
     * {@inheritDoc}
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $locale = $input->getArgument('locale');

        /** @var Connection $connection */
        $connection = $this->getContainer()->get('doctrine')->getConnection();
        $stmt = $connection->query(
            sprintf(
                'SELECT 
                    page.id as original_page_id, 
                    url_alias.id, 
                    url_alias.internal_url, 
                    url_alias.public_url, 
                    REPLACE(url_alias.internal_url, "/nl/page/", "") AS page_id 
                FROM url_alias 
                LEFT JOIN page ON page.id = REPLACE(url_alias.internal_url, \'/%1$s/page/\', \'\') 
                WHERE internal_url LIKE "/%s/page/%%%%"
                AND page.id IS NULL',
                $locale
            )
        );

        $table = new Table($output);
        $table->setHeaders(['Alias', 'Internal URL', 'Stripped Id', 'Original Page Id']);

        $records = $stmt->fetchAll();

        if (!count($records)) {
            $output->writeln('No obsolete aliases found!');
            return 0;
        }

        foreach ($records as $record) {
            $table->addRow(
                [
                   $record['public_url'],
                   $record['internal_url'],
                   $record['page_id'],
                   $record['original_page_id'],
                ]
            );
        }

        $table->render();

        $helper = $this->getHelper('question');
        $confirm = new ConfirmationQuestion('Would you like to remove the above obsolete url aliases? [Y/n] : ', false);

        if (!$helper->ask($input, $output, $confirm)) {
            $output->writeln('Okay, bye');
            return 0;
        }

        $stmt->execute();
        $aliasIds = iter\map('id', $stmt->fetchAll());

        $connection->query(sprintf('DELETE FROM url_alias WHERE id IN (%s)', implode(', ', iterator_to_array($aliasIds))));
        $output->writeln(sprintf('Removed %d aliases', count($records)));

        return 0;
    }
}
<commit_msg>Fix non-dynamic locale in query. Remove itertools as page bundle is not yet dependent, easy rewrite.<commit_after><?php
/**
 * @author <NAME> <<EMAIL>>
 * @copyright Zicht Online <http://www.zicht.nl>
 */
namespace Zicht\Bundle\PageBundle\Command;

use Doctrine\DBAL\Connection;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Helper\Table;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Question\ConfirmationQuestion;

/**
 * Class CleanAliasCommand
 *
 * Remove aliases referencing pages that do not exist anymore.
 */
class CleanAliasCommand extends ContainerAwareCommand
{
    /**
     * {@inheritDoc}
     */
    protected function configure()
    {
        $this
            ->setName('zicht:page:clean:alias')
            ->addArgument('locale', InputArgument::REQUIRED, 'Page locale to clean')
            ->setDescription('Clean aliases that are not attached to pages any more.');
    }

    /**
     * {@inheritDoc}
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $locale = $input->getArgument('locale');

        /** @var Connection $connection */
        $connection = $this->getContainer()->get('doctrine')->getConnection();
        $stmt = $connection->query(
            sprintf(
                'SELECT 
                    page.id as original_page_id, 
                    url_alias.id, 
                    url_alias.internal_url, 
                    url_alias.public_url, 
                    REPLACE(url_alias.internal_url, "/%1$s/page/", "") AS page_id 
                FROM url_alias 
                LEFT JOIN page ON page.id = REPLACE(url_alias.internal_url, \'/%1$s/page/\', \'\') 
                WHERE internal_url LIKE "/%s/page/%%%%"
                AND page.id IS NULL',
                $locale
            )
        );

        $table = new Table($output);
        $table->setHeaders(['Alias', 'Internal URL', 'Stripped Id', 'Original Page Id']);

        $records = $stmt->fetchAll();

        if (!count($records)) {
            $output->writeln('No obsolete aliases found!');
            return 0;
        }

        foreach ($records as $record) {
            $table->addRow(
                [
                   $record['public_url'],
                   $record['internal_url'],
                   $record['page_id'],
                   $record['original_page_id'],
                ]
            );
        }

        $table->render();

        $helper = $this->getHelper('question');
        $confirm = new ConfirmationQuestion('Would you like to remove the above obsolete url aliases? [Y/n] : ', false);

        if (!$helper->ask($input, $output, $confirm)) {
            $output->writeln('Okay, bye');
            return 0;
        }

        $stmt->execute();

        $aliasIds = [];
        foreach ($stmt->fetchAll() as $record) {
            $aliasIds[] = $record['id'];
        }

        $connection->query(sprintf('DELETE FROM url_alias WHERE id IN (%s)', implode(', ', $aliasIds)));
        $output->writeln(sprintf('Removed %d aliases', count($records)));

        return 0;
    }
}

{'fraction_non_alphanumeric': 0.13806491691256406, 'fraction_numerical': 0.001397732567168815, 'mean_word_length': 2.072519083969466, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3399278', 'n_tokens_mistral': 1800, 'n_tokens_neox': 1716, 'n_words': 385}