TYPO3: Unbenutzte Dateien im fileadmin finden

Ihr könnt ganz einfach verwaiste Dateien finden und auch bei Bedarf gleich löschen. Hierzu stellen wir euch einen Command bereit, den ihr lediglich bei euch in euer Sitepackage einfügen müsst (der Namespace muss natürlich noch entsprechend angepasst werden).

Aber Achtung: Nicht alle genutzten Dateien können hundertprozentig identifiziert werden (manchmal werden Assets in einem HTML-Content oder in einem externen Newsletter-Tool verwendet, etc…). Auch Dateien, die lediglich über ihren Ordner (zB als File-Collection) genutzt werden, erkennt das Command nicht als genutzt. 
Ihr solltet euch daher - wie bei allen destruktiven Aktionen - im Dryrun zuvor einmal anschauen, ob diese Dateien wirklich gelöscht werden können.

Beispielaufrufe:

# Dryrun (show only affected files) in Folder fileadmin/Images/ with a limit of 1000 files:
./vendor/bin/typo3 in2template:deleteorphanedsysfiles 1 1:/Images/ 1000

# Hotrun (show and delete affected files) in Folder fileadmin/Images/ with a limit of 1000 files:
./vendor/bin/typo3 in2template:deleteorphanedsysfiles 0 1:/General/ 1000

Der Command als PHP-Datei:

<?php

declare(strict_types=1);

namespace In2code\In2template\Command;

use Doctrine\DBAL\Exception as DbalException;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use TYPO3\CMS\Core\Core\Bootstrap;
use TYPO3\CMS\Core\Core\Environment;
use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\DataHandling\DataHandler;
use TYPO3\CMS\Core\Resource\Folder;
use TYPO3\CMS\Core\Resource\ResourceFactory;
use TYPO3\CMS\Core\Resource\ResourceStorage;
use TYPO3\CMS\Core\Resource\StorageRepository;
use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * Find orphaned files and delete them
 *  E.g. in 3:/General/
 *      # Dryrun:
 *      ./vendor/bin/typo3 in2template:deleteorphanedsysfiles 1 3:/General/ 99
 *
 *      # Hotrun:
 *      ./vendor/bin/typo3 in2template:deleteorphanedsysfiles 0 3:/General/ 99
 */
#[AsCommand(
    name: 'in2template:deleteorphanedsysfiles',
    description: 'Deletes orphaned sys_file records in a certain folder',
)]
class DeleteOrphanedSysFilesCommand extends Command
{
    private const string TABLE_SYS_FILE = 'sys_file';
    private const string TABLE_SYS_FILE_REFERENCE = 'sys_file_reference';
    private const int DEFAULT_LIMIT = 100;

    /**
     * @var array<int, ResourceStorage>
     */
    private array $storageCache = [];

    public function __construct(
        private readonly ConnectionPool $connectionPool,
        private readonly StorageRepository $storageRepository,
        private readonly ResourceFactory $resourceFactory,
    ) {
        parent::__construct();
    }

    public function configure(): void
    {
        $this->addArgument('dryrun', InputArgument::REQUIRED, 'Run command as dry run (1 or 0)');
        $this->addArgument(
            'storageAndPath',
            InputArgument::REQUIRED,
            'Storage and folder path in format "storage:/path/" (e.g. "3:/General/")'
        );
        $this->addArgument('limit', InputArgument::OPTIONAL, 'Limits the deleted files per run', self::DEFAULT_LIMIT);
    }

    public function execute(InputInterface $input, OutputInterface $output): int
    {
        $result = Command::FAILURE;
        $dryRun = (bool)$input->getArgument('dryrun');
        $storageAndPath = $input->getArgument('storageAndPath');
        $limit = (int)$input->getArgument('limit');

        if (is_string($storageAndPath) === false || $storageAndPath === '') {
            $output->writeln('storageAndPath must be a non empty string (e.g. "3:/General/").');
        } else {
            $folder = $this->resolveFolder($storageAndPath);
            $storageId = $folder->getStorage()->getUid();
            $folderPath = $folder->getIdentifier();

            if ($limit > self::DEFAULT_LIMIT) {
                $output->writeln('Be careful increasing that number otherwise this might take a while.');
            }

            try {
                $orphanedSysFiles = $this->findOrphanedSysFiles($folderPath, $storageId, $limit);
                $this->outputOrphanedSysFiles($output, $orphanedSysFiles);

                if ($dryRun === true) {
                    $result = Command::SUCCESS;
                } else {
                    $result = $this->processFileDeletion($output, $orphanedSysFiles);
                }
            } catch (\Throwable $exception) {
                $output->writeln('Error: ' . $exception->getMessage());
            }
        }

        return $result;
    }

    /**
     * @return array<int, array{uid: int, identifier: string, storage: int}>
     * @throws DbalException
     */
    private function findOrphanedSysFiles(string $folderPath, int $storageId, int $limit): array
    {
        $normalizedPath = $this->normalizeFolderPath($folderPath);
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_SYS_FILE);
        $queryBuilder
            ->select('sf.uid', 'sf.identifier', 'sf.storage')
            ->from(self::TABLE_SYS_FILE, 'sf')
            ->leftJoin(
                'sf',
                self::TABLE_SYS_FILE_REFERENCE,
                'sfr',
                $queryBuilder->expr()->eq('sf.uid', $queryBuilder->quoteIdentifier('sfr.uid_local'))
            )
            ->where(
                $queryBuilder->expr()->like(
                    'sf.identifier',
                    $queryBuilder->createNamedParameter($normalizedPath . '/%')
                ),
                $queryBuilder->expr()->eq(
                    'sf.storage',
                    $queryBuilder->createNamedParameter($storageId, Connection::PARAM_INT)
                ),
                $queryBuilder->expr()->isNull('sfr.uid')
            )
            ->setMaxResults($limit);

        return $queryBuilder->executeQuery()->fetchAllAssociative();
    }

    /**
     * @param array<int, array{uid: int, identifier: string, storage: int}> $sysFiles
     */
    private function outputOrphanedSysFiles(OutputInterface $output, array $sysFiles): void
    {
        foreach ($sysFiles as $orphanedSysFile) {
            $output->writeln($orphanedSysFile['uid'] . ': ' . $orphanedSysFile['identifier']);
        }

        $output->writeln(sprintf('Found %s orphaned file(s)', count($sysFiles)));
    }

    /**
     * @param array<int, array{uid: int, identifier: string, storage: int}> $orphanedSysFiles
     */
    private function processFileDeletion(OutputInterface $output, array $orphanedSysFiles): int
    {
        $output->writeln('Start deleting files...');

        $orphanedSysFilesAmount = count($orphanedSysFiles);
        $failedSysFiles = $this->deleteSysFiles($orphanedSysFiles);
        $deletedSysFilesAmount = $orphanedSysFilesAmount - count($failedSysFiles);

        $output->writeln('Deleted ' . $deletedSysFilesAmount . ' out of ' . $orphanedSysFilesAmount . ' files.');

        $result = Command::FAILURE;
        if ($orphanedSysFilesAmount === $deletedSysFilesAmount) {
            $result = Command::SUCCESS;
        }

        return $result;
    }

    /**
     * @param array<int, array{uid: int, identifier: string, storage: int}> $sysFiles
     * @return array<int, array{uid: int, identifier: string, storage: int}>
     */
    private function deleteSysFiles(array $sysFiles): array
    {
        $failedSysFiles = [];

        Bootstrap::initializeBackendAuthentication();
        $dataHandler = GeneralUtility::makeInstance(DataHandler::class);
        $dataHandler->start([], []);

        foreach ($sysFiles as $sysFile) {
            $uid = $sysFile['uid'] ?? null;
            $identifier = $sysFile['identifier'] ?? null;
            $storageId = $sysFile['storage'] ?? null;

            if ($uid === null || $identifier === null || $storageId === null) {
                $failedSysFiles[] = $sysFile;
                continue;
            }

            $absoluteFilePath = $this->getAbsoluteFilePath($identifier, $storageId);

            if ($absoluteFilePath === '') {
                $failedSysFiles[] = $sysFile;
                continue;
            }

            if (file_exists($absoluteFilePath)) {
                unlink($absoluteFilePath);
            }

            $dataHandler->deleteRecord(self::TABLE_SYS_FILE, $uid, true, true);
        }

        return $failedSysFiles;
    }

    private function getAbsoluteFilePath(string $identifier, int $storageId): string
    {
        $absolutePath = '';

        if (array_key_exists($storageId, $this->storageCache) === false) {
            $storage = $this->storageRepository->findByUid($storageId);
            if ($storage !== null) {
                $this->storageCache[$storageId] = $storage;
            }
        }

        if (array_key_exists($storageId, $this->storageCache)) {
            $basePath = $this->storageCache[$storageId]->getConfiguration()['basePath'] ?? '';

            if ($basePath !== '') {
                $basePath = str_starts_with($basePath, '/') ? $basePath : '/' . $basePath;
                $basePath = str_ends_with($basePath, '/') ? substr($basePath, 0, -1) : $basePath;
                $identifier = str_starts_with($identifier, '/') ? $identifier : '/' . $identifier;

                $absolutePath = Environment::getPublicPath() . $basePath . $identifier;
            }
        }

        return $absolutePath;
    }

    private function resolveFolder(string $storageAndPath): Folder
    {
        $resource = $this->resourceFactory->retrieveFileOrFolderObject($storageAndPath);

        if ($resource instanceof Folder === false) {
            throw new \Exception(
                'The given path "' . $storageAndPath . '" does not resolve to a folder.',
                1738764000
            );
        }

        return $resource;
    }

    private function normalizeFolderPath(string $folderPath): string
    {
        $normalizedPath = str_starts_with($folderPath, '/') ? $folderPath : '/' . $folderPath;
        $normalizedPath = str_ends_with($normalizedPath, '/') ? substr($normalizedPath, 0, -1) : $normalizedPath;

        return $normalizedPath;
    }
}