Discover assets uploaded via FTP

Hi, is there a possibility to discover assets uploaded via FTP and add proper records to database?

You could write a script that checks every day if there are new assets and update the database through Pimcore PHP API (https://pimcore.com/docs/6.x/Development_Documentation/Assets/Working_with_PHP_API.html).

1 Like

I got a command running in production syncing new files from an s3 storage with the files in Pimcore. works quite fast actually, also runs every 5 mins for just a few seconds.

Note: It doesn’t use the Pimcore PHP API to create new assets, since that is quite slow… It inserts them directly into the db.

<?php

namespace AppBundle\Command;

use Aws\S3\S3Client;
use Pimcore\Db\Connection;
use Pimcore\File;
use Pimcore\Model\Asset;
use Pimcore\Model\Tool\Lock;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Helper\ProgressBar;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Stopwatch\Stopwatch;

class SyncFilesCommand extends ContainerAwareCommand
{
    protected function configure()
    {
        $this
            ->setName('app:sync-files')
            ->addOption('skip-delete', null, InputOption::VALUE_NONE)
            ->addOption('s3-cache', null, InputOption::VALUE_NONE)
            ->addOption('write-s3-cache', null, InputOption::VALUE_NONE)
        ;
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        if (Lock::isLocked('s3sync', 300)) {
            $output->writeln('LOCKED');
            return 1;
        }

        Lock::lock('s3sync');

        $watch = new Stopwatch();
        $watch->start('sync');

        $skipDelete = $input->getOption('skip-delete');
        $s3Cache = $input->getOption('s3-cache');
        $writeS3Cache = $input->getOption('write-s3-cache');
        $db = $this->getContainer()->get('database_connection');

        $s3Files = [];
        $s3Folders = [];

        $watch->start('sync-read');

        $output->writeln('<info>Read S3 Files</info>');

        if (!$s3Cache) {
            $s3Client = new S3Client([
                'version' => 'latest',
                'region' => getenv("S3_REGION"),
                'credentials' => [
                    'key' => getenv('S3_KEY'),
                    'secret' => getenv('S3_SECRET'),
                ],
            ]);

            $results = $s3Client->getPaginator('ListObjects', [
                'Bucket' => getenv('S3_BUCKET'),
                'Prefix' => 'pimcore/assets/'
            ]);

            $addFolder = function ($path) use (&$s3Folders) {
                $parts = explode('/', $path);
                $parts = array_filter($parts, function ($var) {
                    return strlen($var) > 0;
                });

                $sanitizedPath = '/';
                $pathsArray = [];

                foreach ($parts as $part) {
                    $sanitizedPath = $sanitizedPath.Asset\Service::getValidKey($part, 'asset').'/';
                }

                foreach ($parts as $part) {
                    $pathPart = $pathsArray[count($pathsArray) - 1] ?? '';
                    $pathsArray[] = $pathPart.'/'.Asset\Service::getValidKey($part, 'asset');
                }

                $total = count($pathsArray);

                for ($i = 0; $i < $total; $i++) {
                    $currentPath = $pathsArray[$i];

                    if (in_array($currentPath, $s3Folders, true)) {
                        continue;
                    }

                    $s3Folders[] = $currentPath;
                }
            };

            foreach ($results as $result) {
                foreach ($result['Contents'] as $object) {
                    $key = str_replace('pimcore/assets', '', $object['Key']);

                    $folderPath = dirname($key);
                    $addFolder($folderPath);

                    if ($this->endsWith($key, '/')) {
                        if ($key !== '/') {
                            $s3Folders[] = substr($key, 0, -1);
                        }
                        $s3Folders[] = $key;
                    } else {
                        $s3Files[] = $key;
                    }
                }
            }

            $watch->stop('sync-read');
            $output->writeln(sprintf('<info>Reading Files took exactly: %s</info>', $watch->getEvent('sync-read')->__toString()));

            if ($writeS3Cache) {
                File::put(PIMCORE_SYSTEM_TEMP_DIRECTORY . '/s3-files.serialize', serialize($s3Files));
                File::put(PIMCORE_SYSTEM_TEMP_DIRECTORY . '/s3-folders.serialize', serialize($s3Folders));
            }
        }
        else {
            $s3Files = unserialize(file_get_contents(PIMCORE_SYSTEM_TEMP_DIRECTORY . '/s3-files.serialize'));
            $s3Folders = unserialize(file_get_contents(PIMCORE_SYSTEM_TEMP_DIRECTORY . '/s3-folders.serialize'));
        }

        $watch->start('sql-query');
        $output->writeln('<info>Executing Query READ_EXISTING_FILES</info>');
        $pimcoreFiles = $db->fetchCol('SELECT CONCAT(path, filename) from assets WHERE type <> "folder"');
        $output->writeln(sprintf('<info>Executing Query READ_EXISTING_FILES took exactly: %s</info>', $watch->getEvent('sql-query')->__toString()));
        $watch->stop('sql-query');

        $watch->start('sql-query');
        $output->writeln('<info>Executing Query READ_EXISTING_FOLDER</info>');
        $pimcoreFolders = $db->fetchCol('SELECT CONCAT(path, filename) from assets WHERE type = "folder"');
        $output->writeln(sprintf('<info>Executing Query READ_EXISTING_FOLDER took exactly: %s</info>', $watch->getEvent('sql-query')->__toString()));
        $watch->stop('sql-query');

        $missingFilesInPimcore = array_diff($s3Files, $pimcoreFiles);
        //$missingFoldersPimcore = array_diff($s3Folders, $pimcoreFolders);

        $filesInPimcoreMissingS3 = array_diff($pimcoreFiles, $s3Files);
        $foldersInPimcoreMissingS3 = array_diff($pimcoreFolders, $s3Folders);

        if (!$skipDelete) {
            if (count($filesInPimcoreMissingS3) > 0) {
                $progress = new ProgressBar($output, count($filesInPimcoreMissingS3));
                $progress->setFormat(
                    ' %current%/%max% [%bar%] %percent:3s%% (%elapsed:6s%/%estimated:-6s%) %memory:6s%: DELETE: %message%'
                );
                $progress->start();

                foreach ($filesInPimcoreMissingS3 as $fileToDelete) {
                    $asset = Asset::getByPath($fileToDelete);

                    if ($asset) {
                        $asset->delete();

                        $progress->setMessage('Deleted Asset: '.$asset->getRealFullPath());
                    }

                    $progress->advance();
                }
            } else {
                $output->writeln('<info>No Files to delete found</info>');
            }

            if (count($foldersInPimcoreMissingS3) > 0) {
                $progress = new ProgressBar($output, count($foldersInPimcoreMissingS3));
                $progress->setFormat(
                    ' %current%/%max% [%bar%] %percent:3s%% (%elapsed:6s%/%estimated:-6s%) %memory:6s%: DELETE Folder: %message%'
                );
                $progress->start();

                foreach ($foldersInPimcoreMissingS3 as $folderToDelete) {
                    $asset = Asset::getByPath($folderToDelete);

                    if ($asset) {
                        $asset->delete();
                        $progress->setMessage('Deleted Folder: '.$asset->getRealFullPath());
                    }

                    $progress->advance();
                }
            } else {
                $output->writeln('<info>No Folders to delete found</info>');
            }
        }
        else {
            $output->writeln(sprintf('<info>Found %s files to delete, but skipped</info>', count($filesInPimcoreMissingS3)));
        }

        if (count($missingFilesInPimcore) > 0) {

            $knownExtendsion = $this->getContainer()->getParameter('pimcore.mime.extensions');

            $progress = new ProgressBar($output, count($missingFilesInPimcore));
            $progress->setFormat(
                ' %current%/%max% [%bar%] %percent:3s%% (%elapsed:6s%/%estimated:-6s%) %memory:6s%: CREATE: %message%'
            );
            $progress->start();

            foreach ($missingFilesInPimcore as $fileToCreate) {
                $path = dirname($fileToCreate);
                $filename = basename($fileToCreate);

                $mimetype = 'application/octet-stream';

                if ($filename) {
                    $extension = \Pimcore\File::getFileExtension($filename);
                    if (array_key_exists($extension, $knownExtendsion)) {
                        $mimetype =  $knownExtendsion[$extension];
                    }
                }

                $parentId = $this->insertPath($db, $path);

                $db->insert('assets', [
                    'parentId' => $parentId,
                    'path' => $path === '/' ? '/' : $path . '/',
                    'type' => Asset::getTypeFromMimeMapping($mimetype, $filename),
                    'filename' => $filename,
                    'mimetype' => $mimetype,
                    'creationDate' => time(),
                    'modificationDate' => time(),
                    'userOwner' => 0,
                    'customSettings' => 'a:1:{s:25:"imageDimensionsCalculated";b:1;}',
                    'hasMetaData' => 0,
                    'versionCount' => 0,
                ]);

                $progress->setMessage('Created Asset: '.$fileToCreate);
                $progress->display();
                $progress->advance();
            }
        } else {
            $output->writeln('<info>No Files to create found</info>');
        }

        $watch->stop('sync');

        $output->writeln(sprintf('<info>Syncing Files took exactly: %s</info>', $watch->getEvent('sync')->__toString()));

        Lock::release('s3sync');

        return 0;
    }

    private $cachedPathIds = [
        '/' => 1
    ];

    private function insertPath(Connection $db, $path)
    {
        $parts = explode('/', $path);
        $parts = array_filter($parts, function($var) {
            return strlen($var) > 0;
        });

        $sanitizedPath = '/';
        $pathsArray = [];

        foreach ($parts as $part) {
            $sanitizedPath = $sanitizedPath . Asset\Service::getValidKey($part, 'asset') . '/';
        }

        foreach ($parts as $part) {
            $pathPart = $pathsArray[count($pathsArray) - 1] ?? '';
            $pathsArray[] = $pathPart . '/' . Asset\Service::getValidKey($part, 'asset');
        }

        $total = count($pathsArray);
        $parentId = 1;

        for ($i = 0; $i < $total; $i++) {
            $currentPath = $pathsArray[$i];

            if (array_key_exists($currentPath, $this->cachedPathIds)) {
                $id = $this->cachedPathIds[$currentPath];
            }
            else {
                $keyAndPath = $this->extractKeyAndPath($currentPath);
                $id = $db->fetchOne('SELECT id FROM assets WHERE `path` = :path AND `filename` = :key', $keyAndPath);
            }

            if ($id) {
                $this->cachedPathIds[$currentPath] = $id;

                $parentId = $id;
                continue;
            }

            $db->insert('assets', [
                'parentId' => $parentId,
                'path' => $keyAndPath['path'],
                'type' => 'folder',
                'filename' => $keyAndPath['key'],
                'mimetype' => null,
                'creationDate' => time(),
                'modificationDate' => time(),
                'userOwner' => 0,
                'customSettings' => 'a:0:{}',
                'hasMetaData' => 0,
                'versionCount' => 0,
            ]);
        }

        $parentId = $db->fetchOne('SELECT id FROM assets WHERE `path` = :path AND `filename` = :key', $this->extractKeyAndPath($path));

        $this->cachedPathIds[$path] = $parentId;

        return $parentId;
    }

    protected function extractKeyAndPath($fullpath)
    {
        $key = '';
        $path = $fullpath;
        if ($fullpath !== '/') {
            $lastPart = strrpos($fullpath, '/') + 1;
            $key = substr($fullpath, $lastPart);
            $path = substr($fullpath, 0, $lastPart);
        }

        return [
            'key' => $key,
            'path' => $path
        ];
    }

    protected function endsWith($haystack, $needle)
    {
        $length = strlen($needle);
        if ($length == 0) {
            return true;
        }

        return (substr($haystack, -$length) === $needle);
    }
}
1 Like