Skip to content

Instantly share code, notes, and snippets.

@soerenbernstein
Created April 21, 2016 06:36
Show Gist options
  • Save soerenbernstein/e0bf825ace65659da5ae8bb0dd63cfdd to your computer and use it in GitHub Desktop.
Save soerenbernstein/e0bf825ace65659da5ae8bb0dd63cfdd to your computer and use it in GitHub Desktop.
Revisions cleanup for SimpleThings/EntityAudit as symfony command
<?php
/**
* Created by PhpStorm.
* User: Sören Bernstein
* Date: 15.09.2015
* Time: 12:57
*/
namespace Redlink\CASBundle\Command;
use Doctrine\ORM\EntityManager;
use SimpleThings\EntityAudit\AuditManager;
use SimpleThings\EntityAudit\AuditReader;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
class RevisionsCleanupCommand extends ContainerAwareCommand
{
const CMD_NAME = 'redlink:revisions:cleanup';
/** @var AuditManager */
protected $auditManager;
/** @var AuditReader */
protected $auditReader;
/** @var EntityManager */
protected $entityManager;
/** @var array */
protected $globalIgnoreColumns;
/** @var OutputInterface */
protected $output;
protected $removedEntityRevisions = [];
protected $removedRevisions = 0;
protected $failed = false;
/**
*/
protected function showStatistics()
{
$this->output->writeln('');
if (count($this->removedEntityRevisions) > 0) {
$this->output->writeln('Entfernte Entity Revisionen der Klasse');
foreach ($this->removedEntityRevisions as $class => $count) {
$this->output->writeln(' ' . $class . ': ' . $count);
}
$this->output->writeln('');
}
$this->output->writeln('Entfernte Revisionen: ' . $this->removedRevisions);
}
/**
* Configures the current command.
*/
protected function configure()
{
$this->setName(self::CMD_NAME)
->setDescription('Cleanup unnecessary revisions');
}
/**
* Initializes the command just after the input has been validated.
*
* This is mainly useful when a lot of commands extends one main command
* where some things need to be initialized based on the input arguments and options.
*
* @param InputInterface $input An InputInterface instance
* @param OutputInterface $output An OutputInterface instance
*/
protected function initialize(InputInterface $input, OutputInterface $output)
{
$this->auditManager = $this->getContainer()->get('simplethings_entityaudit.manager');
$this->entityManager = $this->getContainer()->get('doctrine.orm.entity_manager');
$this->auditReader = $this->auditManager->createAuditReader($this->entityManager);
$this->globalIgnoreColumns = $this->auditManager->getConfiguration()->getGlobalIgnoreColumns();
$this->output = $output;
if ($this->entityManager instanceof EntityManager) {
// Disable SQLLogger to minimize memory consumption
$this->entityManager
->getConnection()
->getConfiguration()
->setSQLLogger(null);
}
}
/**
* Executes the current command.
*
* This method is not abstract because you can use this class
* as a concrete class. In this case, instead of defining the
* execute() method, you set the code to execute by passing
* a Closure to the setCode() method.
*
* @param InputInterface $input An InputInterface instance
* @param OutputInterface $output An OutputInterface instance
*
* @return null|int null or 0 if everything went fine, or an error code
*
* @throws \LogicException When this abstract method is not implemented
* @see setCode()
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
foreach ($this->auditManager->getMetadataFactory()->getAllClassNames() as $auditedEntity) {
// Cleanup audit information of entity
$this->cleanupEntityRevisions($auditedEntity);
if ($this->failed) {
break;
};
}
if (!$this->failed) {
$this->removeRevisions();
}
$this->showStatistics();
}
protected function cleanupEntityRevisions($auditedEntity)
{
$entityClass = $this->entityManager->getClassMetadata($auditedEntity);
$auditTableName = $this->auditManager->getConfiguration()->getTablePrefix() .
$entityClass->getTableName() .
$this->auditManager->getConfiguration()->getTableSuffix();
$groupingFields = array_diff(
$entityClass->getFieldNames(),
$this->globalIgnoreColumns
);
$groupingFields = $entityClass->getColumnNames($groupingFields);
$countQuery = 'SELECT COUNT(*) FROM ' . $auditTableName;
$originalSize = intval($this->entityManager->getConnection()->fetchColumn($countQuery));
// Create entity audit table
$query = 'CREATE TABLE ' . $auditTableName . '_new LIKE ' . $auditTableName;
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Insert actually changing revisions into new entity audit table
$query = 'INSERT INTO ' . $auditTableName . '_new ' .
'SELECT * FROM ' . $auditTableName . ' ' .
'GROUP BY ' . $this->auditManager->getConfiguration()->getRevisionTypeFieldName() . ', '
. join(', ', $groupingFields) . ' ' .
'ORDER BY ' . $this->auditManager->getConfiguration()->getRevisionFieldName();
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Drop old entity audit table
$query = 'DROP TABLE ' . $auditTableName;
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Rename new entity audit table to expected name
$query = 'ALTER TABLE ' . $auditTableName . '_new RENAME TO ' . $auditTableName;
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() != 0) {
$this->failed = true;
}
} else {
$query = 'DROP TABLE ' . $auditTableName . '_new';
$this->entityManager->getConnection()->exec($query);
}
} else {
$query = 'DROP TABLE ' . $auditTableName . '_new';
$this->entityManager->getConnection()->exec($query);
}
}
$newSize = intval($this->entityManager->getConnection()->fetchColumn($countQuery));
$this->removedEntityRevisions[$entityClass->getName()] = $originalSize - $newSize;
return;
}
protected function removeRevisions()
{
$revisionsTableName = $this->auditManager->getConfiguration()->getRevisionTableName();
$auditTables = array_map(function ($entity) {
return $this->auditManager->getConfiguration()->getTablePrefix() .
$this->entityManager->getClassMetadata($entity)->getTableName() .
$this->auditManager->getConfiguration()->getTableSuffix();
},
$this->auditManager->getMetadataFactory()->getAllClassNames()
);
$countQuery = 'SELECT COUNT(*) FROM ' . $revisionsTableName;
$originalSize = intval($this->entityManager->getConnection()->fetchColumn($countQuery));
// Create temporary revs table
$query = 'CREATE TABLE revs ' .
join(' UNION ', array_map(function ($tableName) {
return 'SELECT ' . $this->auditManager->getConfiguration()->getRevisionFieldName() . ' FROM ' . $tableName;
}, $auditTables)
) . ' ' .
'ORDER BY ' . $this->auditManager->getConfiguration()->getRevisionFieldName();
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Create revisions table
$query = 'CREATE TABLE ' . $revisionsTableName . '_new LIKE ' . $revisionsTableName;
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Insert existing revisions into new revisions table
$query = 'INSERT INTO ' . $revisionsTableName . '_new ' .
'SELECT * FROM ' . $revisionsTableName . ' ' .
'WHERE id IN (SELECT ' . $this->auditManager->getConfiguration()->getRevisionFieldName() . ' FROM revs)';
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Drop old entity audit table
$query = 'DROP TABLE ' . $revisionsTableName;
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() == 0) {
// Rename new entity audit table to expected name
$query = 'ALTER TABLE ' . $revisionsTableName . '_new RENAME TO ' . $revisionsTableName;
$this->entityManager->getConnection()->exec($query);
if ($this->entityManager->getConnection()->errorCode() != 0) {
$this->failed = true;
}
} else {
$this->failed = true;
}
} else {
$this->failed = true;
$query = 'DROP TABLE ' . $revisionsTableName . '_new';
$this->entityManager->getConnection()->exec($query);
}
} else {
$this->failed = true;
}
$query = 'DROP TABLE revs';
$this->entityManager->getConnection()->exec($query);
}
$newSize = intval($this->entityManager->getConnection()->fetchColumn($countQuery));
$this->removedRevisions = $originalSize - $newSize;
return;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment