Überflüssige Daten aus MediaStorage löschen

Hallo zusammen, 

ich finde ja grundsätzlich das Soft-Delete Konzept von Smartstore wirklich sinnig: Inhalte werden nicht gelöscht, sondern auf Deleted gestellt. 

Jetzt hat sich aber durch dieses Verfahren in unserer MediaStorage Tabelle ein Datenbestand von einigen GB ergeben, die ich gerne mal bereinigen würde. 

Hat jemand ein SQL Statement für mich, dass keine vorhandenen Produkte, Logos, Waregnruppen usw. entfernt und die wirklich überflüssigen Binärdaten aus der MediaStorage Tabelle löscht?

Danke schon mal

Lars

Hi Marcus

besten Dank für die Antwort.
1. Wie schreibt man einen Bereinigungstask?
2. Ich habe jetzt mal nur ein Join auf die picture-Tabelle gelegt und komme auf über 200.000 Daten. Ich vermute, dass das etwas mit den mehrfachen Importen zu tun hat, die wir automatisiert durchgeführt hatten. Danach haben wir zwar ein Script, um die Datensätze aus der product-Tabelle zu löschen, aber die picture-Tabelle hatten wir nicht angefasst. Ich bin aber gerade überfragt, wie ich da die überflüssigen löschen könnte. 

Lars

Hier ist mal etwas ungetesteter Pseudo-Code für einen solchen Task. Datenbank-Backup vor Ausführung nicht vergessen.

public class MyDataCleanupTask : ITask
    {
        private readonly IRepository<ProductPicture> _productPictureRepository;
        private readonly IRepository<Picture> _pictureRepository;
        private readonly ICommonServices _services;
        private readonly Provider<IMediaStorageProvider> _databaseStorageProvider;

        public MyDataCleanupTask(
            IRepository<ProductPicture> productPictureRepository,
            IRepository<Picture> pictureRepository,
            ICommonServices services,
            IProviderManager providerManager)
        {
            _productPictureRepository = productPictureRepository;
            _pictureRepository = pictureRepository;
            _services = services;

            // We want to delete media data from database.
            _databaseStorageProvider = providerManager.GetProvider<IMediaStorageProvider>(DatabaseMediaStorageProvider.SystemName);

            Logger = NullLogger.Instance;
        }

        public ILogger Logger { get; set; }

        public void Execute(TaskExecutionContext ctx)
        {
            try
            {
                using (var scope = new DbContextScope(ctx: _services.DbContext, autoDetectChanges: false, validateOnSave: false, hooksEnabled: false, autoCommit: false))
                {
                    var assignedPictureIds = new HashSet<int>();
                    var pageIndex = 0;
                    IPagedList<Picture> pictures = null;

                    // 1. get all assigned picture ids.
                    assignedPictureIds.AddRange(_productPictureRepository.TableUntracked.Select(x => x.PictureId).ToList());
                    // TODO: same with Category.PictureId, Manufacturer.PictureId, ProductVariantAttributeCombination.AssignedPictureIds...

                    // 2. delete non-referenced pictures.
                    var pictureQuery = _pictureRepository.Table
                        .Where(x => x.MediaStorageId != null)
                        .OrderBy(x => x.Id);

                    do
                    {
                        pictures = new PagedList<Picture>(pictureQuery, pageIndex++, 200);

                        foreach (var picture in pictures)
                        {
                            if (!assignedPictureIds.Contains(picture.Id))
                            {
                                // Similar what PictureService.DeletePicture do.
                                // Delete from storage.
                                _databaseStorageProvider.Value.Remove(picture.ToMedia());

                                // Delete entity.
                                _pictureRepository.Delete(picture);
                            }
                        }

                        // Commit batch.
                        scope.Commit();
                    }
                    while (pictures.HasNextPage);
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
    }

Hi Marcus,

besten Dank für die ausführliche Antwort. Ich sitze gerade am SQL Statement und wollte erst mal rausselektieren, was denn überhaupt gebraucht wird. 
Das sieht dann so aus wie unten. Eventuell ja für jemanden interessant. 

Da kommen bei mir jetzt so ca. 2000 Ergebnisse. Das passt. 

Aber insgesamt sind in der MediaStorage Tabelle über 200.000 Datensätze und ich weiß noch nicht genau, wo die überhaupt herkommen. Das ist ein bisschen viel, um das auf fehlerhafte Importe zurückzuführen.

Bevor ich da was lösche, würde ich gerne nochmal nachfragen: Werden denn z.B. die Bilddaten aus dem Pagebuilder auch in der Mediastorage-Tabelle gespeichert?

Super wäre, wenn so ein Task irgendwann mal zentral unter dem Wartungs-Bereich im Backend verfügbar wäre:

"remove unused pictures" oder so

*************

Ja, der PageBuilder nutzt ebenfalls IPictureService, d.h. auch dessen Bilddaten landen in MediaStorage, sofern die Datenbank als Speicherort für Medien festgelegt ist.