Привет, Хабр! Меня зовут Кирилл Салихов, и я занимаюсь разработкой бизнес-приложений на платформе .NET в компании КРОК. При оптимизации процессов развертывания приложений возник вопрос о том, как эффективно хранить SQL-код, предназначенный для применения к базе данных, чтобы избежать необходимости в ручном труде и перемещении файлов.

Наша задача состояла в том, чтобы разработать удобный и надежный подход к работе с SQL-кодом, который позволил бы ускорить деплой и повысить общую эффективность работы с базой данных.

В короткой статье дам пошаговую инструкцию автоматического применения миграций к базе данных и управлению представлениями и процедурами, без всяких сиай/сиди…

В начале может показаться, что зумеры добрались до Entity Framework, но все интереснее.

Для упрощения развертывания приложения мы передаем полномочия по управлению состоянием базы данных непосредственно приложению.

Чтобы миграция применилась сама при старте приложения, добавим, например, в Program.cs, следующий код:

if (!environment.IsDevelopment())
{
   using var scope = app.Services.CreateScope();
   var context = scope.ServiceProvider.GetRequiredService<SejkeContext>();
   await context.Database.MigrateAsync();
}

Спасибо за внимание, ставьте классы!

Вот незадача, EF Core никак не может предоставить управления процедурами или представлениями, не считая этого…

modelBuilder.Entity<AwesomeView>(entity =>
{
   entity.HasNoKey();
   entity.ToView(nameof(AwesomeView));
});

Таким образом, при получении await context.Set<AwesomeView>().ToListAsync(), будет генерироваться SQL код, который селектит нашу вью. 

Возникает логичный вопрос, как это представление должно добраться до базы данных?

Очевидно, что вот это безобразие и надо применить к базе.

create or alter view AwesomeView 
as 
select * from ProcedureInfo

Давным давно, в далекой галактике, ответственный за публикацию приложения собирал в кучу все скрипты. Их надо было накатить на базу и по очереди, очень аккуратно, выполнить ручками. При этом у него должны быть все доступы, что осложняло задачу.

Мы решили, что это не дело, надо это как-то автоматизировать.

Казалось бы, вставил нужный SQL-код в migrationBuilder.Sql(...), и по домам. 

Но не тут-то было.

Все наши скрипты хранятся в папке SQL  внутри проекта Sejke.Migrations.Data, и изменения отслеживаются гитом. Просто копировать содержимое файла и вставлять в код миграции не хотелось бы. Докажи потом, что ты ничего не потерял при копировании, особенно когда скрипт очень-очень длинный.

Таким образом, нам надо каким-то образом взять содержимое файла и применить его к базе. На помощь приходит Embed.

Создаем новую библиотеку классов Sejke.Migrations.Embed в нашем решении и в его .csproj добавляем пару строчек.

<ItemGroup>
   <EmbeddedResource Include="..\Sejke.Migrations.Data\Sql\**"/>
</ItemGroup>

Этот кусок разметки код поднимаемся на директорию выше, переходим в директорию с проектом \Sejke.Migrations.Data и там берем все, то что находится в поддиректории \Sql. 

Таким образом, мы встраиваем все файлы, находящиеся в этой директории, в сборку приложения. Нам нужен небольшой менеджер, чтобы дотягиваться до этих ресурсов из кода.

public static class EmbedManager
{
   private static readonly Assembly ExecutionAssembly = Assembly.GetExecutingAssembly();
  
   /// <summary>
   /// Получить содержимое ресурса
   /// </summary>
   /// <param name="resourceName"> Путь до файла. Пример: Files.ExcelTemplates.TemplateName.xlsx </param>
   /// <returns></returns>
   /// <exception cref="ArgumentException"> Ошибка падает, если ресурс не найден </exception>
   public static async Task<string> GetContentAsync(string resourceName)
   {
      await using var stream = ExecutionAssembly.GetManifestResourceStream($"{ExecutionAssembly.GetName().Name}.{resourceName}");

      if (stream is null)
         throw new ArgumentException($"Нет ресурса: {resourceName}");

      using var reader = new StreamReader(stream);

      return await reader.ReadToEndAsync();
   }
}

Дальше ловкость рук и никакого мошенничества. Создаем пустую миграцию и закидываем в нее код:

namespace Sejke.Migrations.Data.Migrations
{
   /// <inheritdoc />
   public partial class RunOnce : Microsoft.EntityFrameworkCore.Migrations.Migration
   {
      /// <inheritdoc />
      protected override void Up(MigrationBuilder migrationBuilder)
      {
         migrationBuilder.Sql(EmbedManager.GetContentAsync("Views.AwesomeView.sql").Result);
      }

      /// <inheritdoc />
      protected override void Down(MigrationBuilder migrationBuilder)
      {
        //ignore или писать рядом со скриптом ранванса еще и скрипт отката
      }
   }
}

Но, как оказалось, в это представление со временем надо вносить изменения и заново добавлять миграцию с применением этого SQL-кода. Все бы ничего, но это все еще требует дополнительных действий. 

1) каждый раз удалять запись о миграции из базы, удалять миграцию из кода 

2) каждый раз писать новый скрипт с версионированием, и добавлять миграцию

Но мы не просто так здесь собрались – будем обновлять процедуры и представления при каждом запуске приложения с помощью команды create or alter, чтобы, например, избежать потери доступа при их удалении.

Для этого допишем наш EmbedManager

// ReSharper disable once ReturnTypeCanBeEnumerable.Global
public static string[] GetResourceFileNames(string folder)
{
   var assemblyName = ExecutionAssembly.GetName().Name;
  
   var resFiles = ExecutionAssembly
               .GetManifestResourceNames()
               .Where(n => n.StartsWith($"{assemblyName}.{folder}"))
               .Select(n => n.Replace($"{assemblyName}.", string.Empty));

   return resFiles.ToArray();
}

Дополним кодом, который выполняется при старте приложения:

if (!environment.IsDevelopment())
{
   using var scope = app.Services.CreateScope();
   var context = scope.ServiceProvider.
                  GetRequiredService<SejkeContext>();
   await context.Database.MigrateAsync();
   var fileNames = EmbedManager.GetResourceFileNames("Views");
   foreach (var fileName in fileNames)
   {
      var fileContent = await EmbedManager.GetContentAsync(fileName);
      await context.Database.ExecuteSqlRawAsync(fileContent);
   }
}

Но мы задроты, и будем отслеживать хэши скриптов. Делаем это для того, чтобы накатывать только то, что изменили:

public class ProcedureInfo
{
   [Key]
   [MaxLength(100)]
   public required string Name { get; init; }
  
   [MaxLength(100)]
   public required string HashSum { get; set; }
  
   public required DateTime CreateDate { get; init; }
}

if (!environment.IsDevelopment())
{
   using var scope = app.Services.CreateScope();
   var context = scope.ServiceProvider.GetRequiredService<SejkeContext>();
   await context.Database.MigrateAsync();

   var repository = scope.ServiceProvider.GetRequiredService<IRepository<ProcedureInfo>>();

   var procedureInfos = await repository.Get().ToListAsync();
   var procedureHashMap = procedureInfos.ToFrozenDictionary(x => x.Name);
  
   var fileNames = EmbedManager.GetResourceFileNames("Views");
   var procedureInfoToAdd = new List<ProcedureInfo>(fileNames.Length);

   await repository.BeginAsync();

   try
   {
      foreach (var fileName in fileNames)
      {
         var procedureName = fileName.Replace(".sql", string.Empty);
         var procedureInfo = procedureHashMap!.GetValueOrDefault(procedureName, null);
        
         var fileContent = await EmbedManager.GetContentAsync(fileName);
         var fileContentHashSum = SHA1.HashData(Encoding.UTF8.GetBytes(fileContent));
         var fileContentHashString = Convert.ToBase64String(fileContentHashSum);

         if (procedureInfo is null)
         {
            await context.Database.ExecuteSqlRawAsync(fileContent);
           
            // ReSharper disable once ArrangeObjectCreationWhenTypeNotEvident
            procedureInfoToAdd.Add(new()
            {
               Name = procedureName,
               HashSum = Convert.ToBase64String(fileContentHashSum),
               CreateDate = DateTime.Now
            });
         }
         else if (procedureInfo.HashSum != fileContentHashString)
         {
            await context.Database.ExecuteSqlRawAsync(fileContent);

            //из миллиона вариантов сохранения байтовых значений я выбрал самый изящный!!!!
            procedureInfo.HashSum = fileContentHashString;
         }
      }

      await repository.AddRangeAsync(procedureInfoToAdd);
     
      await context.SaveChangesAsync();
     
      await repository.CommitAsync();
   }
   catch //тут надо перехватить исключение и записать его в лог, но мы панки
   {
      await repository.RollbackAsync();
   }
}

Так, легким движением рук (у кого-то ног), мы получаем удобный и надежный подход к работе с SQL-кодом, который позволил ускорить публикацию приложения и повысить общую эффективность работы с базой данных, а также освободить от сомнительных временных затрат и ответственности инженера, который отвечает за внедрение. Выполняйте этот план и спина болеть не будет.

Господа, кто на текущий момент уверен, что я дилетант и проходимец, добро пожаловать в комментарии, с удовольствием отвечу на мучительные вопросы и поудаляю комментарии с критикой.

Спасибо за внимание. Всем удачи, всем пока.