Blob support for non image files

[thecaptncode]

Is there a way to use an IBlobProvider to return a non-image file such as a PDF to the browser? I have set one up to read varbinary(max) from our SQL server and it works well displaying and resizing images but non-image documents do not appear to be working. It would be great if I could expand our IBlobProvider to handle those as well.

Thanks!
Greg

[lilith]

What are you wanting Imageflow to do with them? Any reason not to just use normal file serving.

…

On Sat, Oct 16, 2021, 7:00 PM Greg ***@***.***> wrote: Is there a way to use an IBlobProvider to return a non-image file such as a PDF to the browser? I have set one up to read varbinary(max) from our SQL server and it works well displaying and resizing images but non-image documents do not appear to be working. It would be great if I could expand our IBlobProvider to handle those as well. Thanks! Greg — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#53>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAA2LH6ODLBOVE6KPM5RO2TUHIN37ANCNFSM5GEG4WBQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[thecaptncode]

We are storing uploaded attachments to digital documents in SQL Server and have been using ImageResizer to serve them up as well as serve and resize catalog images. This approach with disk caching allows us to improve performance over file servers, is very scalable and we can record statistics on retrievals.

I have an IBlobProverd now that can retrieve the stored images data. It is working well with images but I have not succeeded in getting non-image files like a PDF from the database.

[thecaptncode]

@lilith - Is there any way to have the ImageFlow middleware use an IBlobProvider for non image file types?

[ezdavis1993]

@thecaptncode Could you share your code for retrieving stored images from SQL Server? I'm trying to do this and can't figure it out.

[thecaptncode]

@ezdavis1993 Sure. Here is what I have. I'm sure there is room for improvement.

I wrote an IFileProvider wrapper for it so I can use it with UseStaticFiles as well to solve this issue. It seems to work but it definitely needs improvements like caching.

Hope it helps,
Greg

using Imazen.Common.Storage;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.IO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading.Tasks;

namespace ImageServer
{
    /// <summary>
    /// Register in ConfigureServices with: services.AddImageflowSqlBlobService(...);
    /// </summary>
    public class SqlBlobProvider : IBlobProvider
    {
        private readonly SqlBlobServiceOptions _options;
        private readonly ILogger<SqlBlobProvider> _logger;
        private static readonly RecyclableMemoryStreamManager _recyclableMemoryStreamManager = new();


        public SqlBlobProvider(SqlBlobServiceOptions options, ILogger<SqlBlobProvider> logger)
        {
            _options = options;
            _logger = logger;
            _logger.Log(LogLevel.Information, "Blob service starting");
        }

        public IEnumerable<string> GetPrefixes()
        {
            return _options.ProcessedPrefix;
        }

        public bool SupportsPath(string virtualPath)
        {
            _logger.Log(LogLevel.Information, $"Blob service received image with path: {virtualPath}");
            foreach (string prefix in _options.StaticPrefix)
            {
                if (virtualPath.StartsWith(prefix,
                    _options.IgnorePrefixCase ? StringComparison.OrdinalIgnoreCase : StringComparison.Ordinal))
                {
                    return false;
                }
            }

            foreach (string prefix in _options.ProcessedPrefix)
            {
                if (virtualPath.StartsWith(prefix,
                    _options.IgnorePrefixCase ? StringComparison.OrdinalIgnoreCase : StringComparison.Ordinal))
                {
                    return true;
                }
            }

            return false;
        }

        public async Task<IBlobData> Fetch(string virtualPath)
        {
            if (!SupportsPath(virtualPath))
            {
                _logger.Log(LogLevel.Information, $"Blob service doesn't support: {virtualPath}");
                return null;
            }

            _logger.Log(LogLevel.Information, $"Blob service fetchng: {virtualPath}");
            (string key, string file) = _options.ContainerKeyFilterFunction(virtualPath, _options);

            if (key != null)
            {
                try
                {
                    using (SqlConnection Conn = new SqlConnection(_options.ConnectionString, _options.Credential))
                    {
                        await Conn.OpenAsync();
                        using SqlCommand command = new("SELECT DocDat, DocObj FROM DOCUMENTS WHERE DocKey = @key and DocFnm = @file", Conn);
                        command.Parameters.Add("@key", SqlDbType.Char, 32).Value = key;
                        command.Parameters.Add("@file", SqlDbType.VarChar, 50).Value = file;

                        // The reader needs to be executed with the SequentialAccess behavior to enable network streaming
                        // Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions
                        using SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess);
                        if (await reader.ReadAsync())
                        {
                            if (!await reader.IsDBNullAsync(0))
                                return new SqlBlob(reader.GetDateTime(0), reader.GetStream(1));
                        }
                        await Conn.CloseAsync();
                    }
                }
                catch (Exception ex)
                {
                    string msg = $"Error occured fetching SQL blob \"{virtualPath}\".";
                    _logger.Log(LogLevel.Error, ex, msg);
                    throw new BlobMissingException(msg, ex);
                }
            }

            _logger.Log(LogLevel.Information, $"Blob service did not find: {key} / {file}");

            if (string.IsNullOrEmpty(_options.NotFoundImagePath))
                throw new BlobMissingException($"SQL blob \"{virtualPath}\" not found.");

            try
            {
                using FileStream noimage = File.Open(_options.NotFoundImagePath, FileMode.Open);
                return new SqlBlob(new DateTime(1601, 1, 1), noimage);
            }
            catch (Exception ex)
            {
                string msg = $"Error occured fetching 'not found' image \"{virtualPath}\".";
                _logger.Log(LogLevel.Error, ex, msg);
                throw new BlobMissingException(msg, ex);
            }
        }

        internal class SqlBlob : IBlobData
        {
            private readonly DateTime? DocDate = null;
            private readonly Stream DocStream = new RecyclableMemoryStream(_recyclableMemoryStreamManager);
            private bool _disposed = false;

            #region Constructor / Dispose / Finalizer

            /// <summary>
            /// Sql Blob results
            /// </summary>
            /// <param name="ModificationDate">Modification data of the document</param>
            /// <param name="Doc">Document stream</param>
            internal SqlBlob(DateTime ModificationDate, Stream Doc)
            {
                DocDate = ModificationDate;
                Doc.CopyTo(DocStream);
                Doc.Close();
                Doc.Dispose();
                DocStream.Position = 0;
            }

            /// <summary>
            /// Class dispose handler
            /// </summary>
            /// <param name="disposing">Dispose was explicitly called</param>
            protected void Dispose(bool disposing)
            {
                if (_disposed)
                {
                    return;
                }

                if (disposing)
                {
                    DocStream?.Dispose();
                }

                _disposed = true;
            }

            /// <summary>
            /// Dispose of SQL Blob class
            /// </summary>
            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }

            /// <summary>
            /// SQL Blob class finalizer
            /// </summary>
            ~SqlBlob() => Dispose(false);

            #endregion Constructor / Dispose / Finalizer

            /// <summary>
            /// Does document exist?
            /// </summary>
            public bool? Exists => true;

            /// <summary>
            /// Last modification date of document
            /// </summary>
            public DateTime? LastModifiedDateUtc => DocDate;

            /// <summary>
            /// Open document
            /// </summary>
            /// <returns>Document contents stream</returns>
            public Stream OpenRead()
            {
                return DocStream;
            }
        }
    }

    public static class SqlBlobServiceExtensions
    {
        public static IServiceCollection AddImageflowSqlBlobService(this IServiceCollection services,
            SqlBlobServiceOptions options)
        {
            services.AddSingleton<IBlobProvider>((container) =>
            {
                ILogger<SqlBlobProvider> logger = container.GetRequiredService<ILogger<SqlBlobProvider>>();
                return new SqlBlobProvider(options, logger);
            });

            return services;
        }
    }

    public class SqlBlobServiceOptions
    {
        public string ConnectionString { get; init; }

        public SqlCredential Credential { get; init; }

        public string NotFoundImagePath { get; init; }

        public bool IgnorePrefixCase { get; init; }

        public List<string> ProcessedPrefix { get; init; }

        public List<string> StaticPrefix { get; init; }

        /// <summary>
        /// Can block container/key pairs by returning null
        /// </summary>
        public Func<string, SqlBlobServiceOptions, (string key, string file)> ContainerKeyFilterFunction = (virtualPath, options) =>
        {
            string path = null;
            foreach (string prefix in options.ProcessedPrefix)
            {
                if (virtualPath.StartsWith(prefix,
                    options.IgnorePrefixCase ? StringComparison.OrdinalIgnoreCase : StringComparison.Ordinal))
                {
                    path = virtualPath[prefix.Length..].TrimStart('/');
                }
            }

            if (path == null)
                return (null, null);

            int indexOfSlash = path.IndexOf('/');
            if (indexOfSlash < 1) return (null, null);

            string key = path[..indexOfSlash];
            string file = path[(indexOfSlash + 1)..];

            return (key, file);
        };
    }
}