Skip to content

Usenet streaming with a WebDAV server and a SABnzbd-compatible API

C#
18.9 KB
Raw Blame
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using NzbWebDAV.Clients.Rclone;
using NzbWebDAV.Database.Interceptors;
using NzbWebDAV.Database.MigrationHelpers;
using NzbWebDAV.Database.Models;
using NzbWebDAV.Utils;
using NzbWebDAV.WebDav;

namespace NzbWebDAV.Database;

public sealed class DavDatabaseContext() : DbContext(Options.Value)
{
    public static string ConfigPath => EnvironmentUtil.GetEnvironmentVariable("CONFIG_PATH") ?? "/config";
    public static string DatabaseFilePath => Path.Join(ConfigPath, "db.sqlite");

    private static readonly Lazy<DbContextOptions<DavDatabaseContext>> Options = new(() =>
        new DbContextOptionsBuilder<DavDatabaseContext>()
            .UseSqlite($"Data Source={DatabaseFilePath}")
            .AddInterceptors(new SqliteForeignKeyEnabler())
            .ReplaceService<IMigrationsSqlGenerator, SqliteMigrationsSqlGenerator<SqliteMigrationsSqlGenerator>>()
            .Options
    );

    // database sets
    public DbSet<Account> Accounts => Set<Account>();
    public DbSet<DavItem> Items => Set<DavItem>();
    public DbSet<DavNzbFile> NzbFiles => Set<DavNzbFile>();
    public DbSet<DavRarFile> RarFiles => Set<DavRarFile>();
    public DbSet<DavMultipartFile> MultipartFiles => Set<DavMultipartFile>();
    public DbSet<QueueItem> QueueItems => Set<QueueItem>();
    public DbSet<HistoryItem> HistoryItems => Set<HistoryItem>();
    public DbSet<QueueNzbContents> QueueNzbContents => Set<QueueNzbContents>();
    public DbSet<HealthCheckResult> HealthCheckResults => Set<HealthCheckResult>();
    public DbSet<HealthCheckStat> HealthCheckStats => Set<HealthCheckStat>();
    public DbSet<ConfigItem> ConfigItems => Set<ConfigItem>();
    public DbSet<BlobCleanupItem> BlobCleanupItems => Set<BlobCleanupItem>();
    public DbSet<HistoryCleanupItem> HistoryCleanupItems => Set<HistoryCleanupItem>();
    public DbSet<DavCleanupItem> DavCleanupItems => Set<DavCleanupItem>();
    public DbSet<NzbName> NzbNames => Set<NzbName>();
    public DbSet<NzbBlobCleanupItem> NzbBlobCleanupItems => Set<NzbBlobCleanupItem>();

    // blob items
    public List<DavNzbFile> BlobNzbFiles = [];
    public List<DavRarFile> BlobRarFiles = [];
    public List<DavMultipartFile> BlobMultipartFiles = [];

    // tables
    protected override void OnModelCreating(ModelBuilder b)
    {
        // Account
        b.Entity<Account>(e =>
        {
            e.ToTable("Accounts");
            e.HasKey(i => new { i.Type, i.Username });

            e.Property(i => i.Type)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.Username)
                .IsRequired()
                .HasMaxLength(255);

            e.Property(i => i.PasswordHash)
                .IsRequired();

            e.Property(i => i.RandomSalt)
                .IsRequired();
        });

        // DavItem
        b.Entity<DavItem>(e =>
        {
            e.ToTable("DavItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(i => i.CreatedAt)
                .ValueGeneratedNever()
                .IsRequired();

            e.Property(i => i.Name)
                .IsRequired()
                .HasMaxLength(255);

            e.Property(i => i.Type)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.SubType)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.Path)
                .IsRequired();

            e.Property(i => i.IdPrefix)
                .IsRequired();

            e.Property(i => i.ReleaseDate)
                .ValueGeneratedNever()
                .HasConversion(
                    x => x.HasValue ? x.Value.ToUnixTimeSeconds() : (long?)null,
                    x => x.HasValue ? DateTimeOffset.FromUnixTimeSeconds(x.Value) : null
                );

            e.Property(i => i.LastHealthCheck)
                .ValueGeneratedNever()
                .HasConversion(
                    x => x.HasValue ? x.Value.ToUnixTimeSeconds() : (long?)null,
                    x => x.HasValue ? DateTimeOffset.FromUnixTimeSeconds(x.Value) : null
                );

            e.Property(i => i.NextHealthCheck)
                .ValueGeneratedNever()
                .HasConversion(
                    x => x.HasValue ? x.Value.ToUnixTimeSeconds() : (long?)null,
                    x => x.HasValue ? DateTimeOffset.FromUnixTimeSeconds(x.Value) : null
                );

            e.Property(i => i.FileBlobId)
                .ValueGeneratedNever()
                .IsRequired(false);

            e.Property(i => i.HistoryItemId)
                .ValueGeneratedNever()
                .IsRequired(false);

            e.Property(i => i.NzbBlobId)
                .ValueGeneratedNever()
                .IsRequired(false);

            e.HasIndex(i => new { i.ParentId, i.Name })
                .IsUnique();

            e.HasIndex(i => new { i.IdPrefix, i.Type });

            e.HasIndex(i => new { i.Type, i.HistoryItemId, i.NextHealthCheck, i.ReleaseDate, i.Id });

            e.HasIndex(i => new { i.HistoryItemId, i.Type, i.CreatedAt });

            e.HasIndex(i => new { i.HistoryItemId, i.SubType, i.CreatedAt });

            e.HasIndex(i => i.NzbBlobId)
                .IsUnique(false);
        });

        // DavNzbFile
        b.Entity<DavNzbFile>(e =>
        {
            e.ToTable("DavNzbFiles");
            e.HasKey(f => f.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(f => f.SegmentIds)
                .HasConversion(new ValueConverter<string[], string>
                (
                    v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
                    v => JsonSerializer.Deserialize<string[]>(v, (JsonSerializerOptions?)null) ?? Array.Empty<string>()
                ))
                .HasColumnType("TEXT") // store raw JSON
                .IsRequired();

            e.HasOne(f => f.DavItem)
                .WithOne()
                .HasForeignKey<DavNzbFile>(f => f.Id)
                .OnDelete(DeleteBehavior.Cascade);
        });

        // DavRarFile
        b.Entity<DavRarFile>(e =>
        {
            e.ToTable("DavRarFiles");
            e.HasKey(f => f.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(f => f.RarParts)
                .HasConversion(new ValueConverter<DavRarFile.RarPart[], string>
                (
                    v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
                    v => JsonSerializer.Deserialize<DavRarFile.RarPart[]>(v, (JsonSerializerOptions?)null)
                         ?? Array.Empty<DavRarFile.RarPart>()
                ))
                .HasColumnType("TEXT") // store raw JSON
                .IsRequired();

            e.HasOne(f => f.DavItem)
                .WithOne()
                .HasForeignKey<DavRarFile>(f => f.Id)
                .OnDelete(DeleteBehavior.Cascade);
        });

        // DavMultipartFile
        b.Entity<DavMultipartFile>(e =>
        {
            e.ToTable("DavMultipartFiles");
            e.HasKey(f => f.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(f => f.Metadata)
                .HasConversion(new ValueConverter<DavMultipartFile.Meta, string>
                (
                    v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
                    v => JsonSerializer.Deserialize<DavMultipartFile.Meta>(v, (JsonSerializerOptions?)null) ??
                         new DavMultipartFile.Meta()
                ))
                .HasColumnType("TEXT") // store raw JSON
                .IsRequired();

            e.HasOne(f => f.DavItem)
                .WithOne()
                .HasForeignKey<DavMultipartFile>(f => f.Id)
                .OnDelete(DeleteBehavior.Cascade);
        });

        // QueueItem
        b.Entity<QueueItem>(e =>
        {
            e.ToTable("QueueItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(i => i.CreatedAt)
                .ValueGeneratedNever()
                .IsRequired();

            e.Property(i => i.FileName)
                .IsRequired();

            e.Property(i => i.NzbFileSize)
                .IsRequired();

            e.Property(i => i.TotalSegmentBytes)
                .IsRequired();

            e.Property(i => i.Category)
                .IsRequired();

            e.Property(i => i.Priority)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.PostProcessing)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.PauseUntil)
                .ValueGeneratedNever();

            e.Property(i => i.JobName)
                .IsRequired();

            e.HasIndex(i => new { i.Category, i.FileName })
                .IsUnique();

            e.HasIndex(i => new { i.Priority })
                .IsUnique(false);

            e.HasIndex(i => new { i.CreatedAt })
                .IsUnique(false);

            e.HasIndex(i => new { i.Category })
                .IsUnique(false);

            e.HasIndex(i => new { i.Priority, i.CreatedAt })
                .IsUnique(false);

            e.HasIndex(i => new { i.Category, i.Priority, i.CreatedAt })
                .IsUnique(false);
        });

        // HistoryItem
        b.Entity<HistoryItem>(e =>
        {
            e.ToTable("HistoryItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(i => i.CreatedAt)
                .ValueGeneratedNever()
                .IsRequired();

            e.Property(i => i.FileName)
                .IsRequired();

            e.Property(i => i.JobName)
                .IsRequired();

            e.Property(i => i.Category)
                .IsRequired();

            e.Property(i => i.DownloadStatus)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.TotalSegmentBytes)
                .IsRequired();

            e.Property(i => i.DownloadTimeSeconds)
                .IsRequired();

            e.Property(i => i.FailMessage)
                .IsRequired(false);

            e.Property(i => i.DownloadDirId)
                .IsRequired(false);

            e.Property(i => i.NzbBlobId)
                .IsRequired(false);

            e.HasIndex(i => new { i.CreatedAt })
                .IsUnique(false);

            e.HasIndex(i => new { i.Category })
                .IsUnique(false);

            e.HasIndex(i => new { i.Category, i.CreatedAt })
                .IsUnique(false);

            e.HasIndex(i => new { i.Category, i.DownloadDirId })
                .IsUnique(false);

            e.HasIndex(i => i.NzbBlobId)
                .IsUnique(false);
        });

        // QueueNzbContents
        b.Entity<QueueNzbContents>(e =>
        {
            e.ToTable("QueueNzbContents");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(i => i.NzbContents)
                .IsRequired();

            e.HasOne(f => f.QueueItem)
                .WithOne()
                .HasForeignKey<QueueNzbContents>(f => f.Id)
                .OnDelete(DeleteBehavior.Cascade);
        });

        // HealthCheckResult
        b.Entity<HealthCheckResult>(e =>
        {
            e.ToTable("HealthCheckResults");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever()
                .IsRequired();

            e.Property(i => i.CreatedAt)
                .ValueGeneratedNever()
                .IsRequired()
                .HasConversion(
                    x => x.ToUnixTimeSeconds(),
                    x => DateTimeOffset.FromUnixTimeSeconds(x)
                );

            e.Property(i => i.DavItemId)
                .ValueGeneratedNever()
                .IsRequired();

            e.Property(i => i.Path)
                .IsRequired();

            e.Property(i => i.Result)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.RepairStatus)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.Message)
                .IsRequired(false);

            e.HasIndex(i => new { i.Result, i.RepairStatus, i.CreatedAt })
                .IsUnique(false);

            e.HasIndex(i => new { i.CreatedAt })
                .IsUnique(false);

            e.HasIndex(h => h.DavItemId)
                .HasFilter("\"RepairStatus\" = 3")
                .IsUnique(false);
        });

        // HealthCheckStats
        b.Entity<HealthCheckStat>(e =>
        {
            e.ToTable("HealthCheckStats");
            e.HasKey(i => new { i.DateStartInclusive, i.DateEndExclusive, i.Result, i.RepairStatus });

            e.Property(i => i.DateStartInclusive)
                .ValueGeneratedNever()
                .IsRequired()
                .HasConversion(
                    x => x.ToUnixTimeSeconds(),
                    x => DateTimeOffset.FromUnixTimeSeconds(x)
                );

            e.Property(i => i.DateEndExclusive)
                .ValueGeneratedNever()
                .IsRequired()
                .HasConversion(
                    x => x.ToUnixTimeSeconds(),
                    x => DateTimeOffset.FromUnixTimeSeconds(x)
                );

            e.Property(i => i.Result)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.RepairStatus)
                .HasConversion<int>()
                .IsRequired();

            e.Property(i => i.Count);
        });

        // ConfigItem
        b.Entity<ConfigItem>(e =>
        {
            e.ToTable("ConfigItems");
            e.HasKey(i => i.ConfigName);
            e.Property(i => i.ConfigValue)
                .IsRequired();
        });

        // BlobCleanupItem
        b.Entity<BlobCleanupItem>(e =>
        {
            e.ToTable("BlobCleanupItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();
        });

        // HistoryCleanupItem
        b.Entity<HistoryCleanupItem>(e =>
        {
            e.ToTable("HistoryCleanupItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(i => i.DeleteMountedFiles)
                .IsRequired();
        });

        // DavCleanupItem
        b.Entity<DavCleanupItem>(e =>
        {
            e.ToTable("DavCleanupItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();
        });

        // NzbName
        b.Entity<NzbName>(e =>
        {
            e.ToTable("NzbNames");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();

            e.Property(i => i.FileName)
                .IsRequired();
        });

        // NzbBlobCleanupItem
        b.Entity<NzbBlobCleanupItem>(e =>
        {
            e.ToTable("NzbBlobCleanupItems");
            e.HasKey(i => i.Id);

            e.Property(i => i.Id)
                .ValueGeneratedNever();
        });
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
    {
        try
        {
            // save blobs to blob-store
            foreach (var blobNzbFile in BlobNzbFiles)
                await BlobStore.WriteBlob(blobNzbFile.Id, blobNzbFile);
            foreach (var blobRarFile in BlobRarFiles)
                await BlobStore.WriteBlob(blobRarFile.Id, blobRarFile);
            foreach (var blobMultipartFile in BlobMultipartFiles)
                await BlobStore.WriteBlob(blobMultipartFile.Id, blobMultipartFile);

            // save db changes
            var addedOrRemovedDavItems = GetAddedOrRemovedDavItems();
            var result = await base.SaveChangesAsync(cancellationToken);
            _ = RcloneVfsForget(addedOrRemovedDavItems);

            // clear pending blob writes
            BlobNzbFiles.Clear();
            BlobRarFiles.Clear();
            BlobMultipartFiles.Clear();

            // return
            return result;
        }
        catch
        {
            // on errors, remove any already-written blob files
            foreach (var blobNzbFile in BlobNzbFiles)
                BlobStore.Delete(blobNzbFile.Id);
            foreach (var blobRarFile in BlobRarFiles)
                BlobStore.Delete(blobRarFile.Id);
            foreach (var blobMultipartFile in BlobMultipartFiles)
                BlobStore.Delete(blobMultipartFile.Id);

            // rethrow the exception
            throw;
        }
    }

    private List<DavItem> GetAddedOrRemovedDavItems()
    {
        return ChangeTracker.Entries<DavItem>()
            .Where(x => x.State is EntityState.Added or EntityState.Deleted)
            .Select(x => x.Entity)
            .ToList();
    }

    private static List<string> GetRcloneVfsForgetDirectories(List<DavItem> addedOrRemoved)
    {
        var contentDirs = addedOrRemoved
            .Select(x => x.Path)
            .Select(x => Path.GetDirectoryName(x)!)
            .ToList();

        var idDirs = addedOrRemoved
            .Where(x => x.Type == DavItem.ItemType.UsenetFile)
            .Select(x => DatabaseStoreSymlinkFile.GetTargetPath(x.Id))
            .Select(x => Path.GetDirectoryName(x)!)
            .ToList();

        var completedSymlinkDirs = contentDirs
            .Where(x => x.StartsWith("/content"))
            .Select(x => $"/completed-symlinks{x["/content".Length..]}")
            .ToList();

        return contentDirs
            .Concat(completedSymlinkDirs)
            .Concat(idDirs)
            .Distinct()
            .ToList();
    }

    public static Task RcloneVfsForget(List<DavItem> addedOrRemovedDavItems)
    {
        if (!RcloneClient.IsRemoteControlEnabled) return Task.CompletedTask;
        if (RcloneClient.Host == null) return Task.CompletedTask;
        if (addedOrRemovedDavItems.Count == 0) return Task.CompletedTask;
        var vfsForgetPaths = GetRcloneVfsForgetDirectories(addedOrRemovedDavItems);
        if (vfsForgetPaths.Count == 0) return Task.CompletedTask;
        return RcloneClient.ForgetVfsPaths(vfsForgetPaths);
    }

    public static Task RcloneVfsForget(List<string> paths)
    {
        if (!RcloneClient.IsRemoteControlEnabled) return Task.CompletedTask;
        if (RcloneClient.Host == null) return Task.CompletedTask;
        if (paths.Count == 0) return Task.CompletedTask;
        return RcloneClient.ForgetVfsPaths(paths);
    }

    public void ClearChangeTracker()
    {
        ChangeTracker.Clear();
        BlobNzbFiles.Clear();
        BlobRarFiles.Clear();
        BlobMultipartFiles.Clear();
    }
}