Как стать автором
Обновить

Как я реализовал git-flow для SQL

.NET *SQL *Git *
Из песочницы
✏️ Технотекст 2021

Если у Вас в команде используется MSSQL Server и у Вас есть хранимые процедуры, функции или представления которые используют Ваши приложения либо они используются для интеграции данных с другими системами то возможно эта статья для Вас.

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

В обычном процессе все пакеты по изменению объектов базы данных скидываются на администраторов и они после проверки их применяют на тестовую среду, после тестирование процедуры применяются на продуктивную среду. В компании бывают моменты, когда происходит какой то сбой ночью либо в выходной, специалисты поддержки 24/7 вызванивают разработчика и администратора, администраторы выдают временные права и разработчик решает проблему. Но когда и кем были внесены изменения не ясно. Были ли они внесены намеренно или случайно(например в следствии правки другой процедуры открыл случайно и начал менять эту) тоже непонятно.

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

Это была не первая причина для поиска решений позволяющих использовать git для SQL, но данная проблема побудила нас к действию. После чтения кучи статей и развертывания разных систем управления исходным кодом для SQL Server, приложений которые позволяют сливать коды в гит и встраиваются в Management Studio в песочнице, мы так и не смогли найти подходящее нам решение: какие - то не поддерживали нашу версию MSSQL Server, некоторые пробные версии вообще не ставились и выдавали ошибку.

Проанализировав ситуацию мы решили это своими руками.

  1. Нужен был инструмент для быстрой работы с git и SQL что бы не пришлось держать открытыми несколько приложений либо Management Studio и консоль.

  2. Гит должен был отображать корректный SQL и мы могли его подвязать на Code Review и CI/CD.

  3. Должно быть все просто без особых танцев с бубном что бы разработчиков не нагружать лишней работой

Шаг первый. Для работы с SQL и GIT был выбран инструмент Azure Data Studio. По началу он показался не особо удобным, но это дело привычки сейчас проблем нет. Особенно для тех кто использует в своей разработке VSCode привыкнуть будет легко.

Поскольку основная структура таблиц генерируется из системы(CodeFirst), код создания таблиц мы выгружать не стали, выгрузили в папку только все хранимые процедуры, функции и представление в итоге получилась вот такая структура:

Так же добавили папку для разных SQL плюшек упрощающих жизнь. Для упрощение будущего кода на CI был введен стандарт именования: scheme.Name.Type.sql например хранимые процедуры в схеме dbo: dbo.CalculateAcidNetto.Procedure.sql, представления: dbo.CalculateAcidNetto.View.sql и тд.

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

Шаг второй. залить данную папку на GIT. Поскольку в компании есть строгий Code style для SQL нужно было что бы GitLab корректно отображал файлы и можно было проводить Code review и писать замечания. Немного потанцевав с бубном возле кодировки файлов (MSSQL выгружает файлы не в UTF-8 и GitLab не отображал их корректно) мы смогли добиться желаемых результатов достаточно легко.

Мы ведем разработку по принципу непрерывной интеграции, при слиянии каждого Merge Request данные публикуются на тестовый сервер и доступны для тестирования по окончанию спринта все протестированные фитчи идут в срез на продуктивный сервер.

Шаг третий. Нужно связать данный репозиторий и CI/CD в нашем случае это TeamCity. Идея такая после слияния в ветку для теста все скрипты должны обновляться в тестовой среде(на тестовом сервере). И по такому же принципу обновлять продуктивную среду. В этом случае даже если кто то залезет и поправит код напрямую(сейчас это запрещено) то можно будет вернуть все на рабочее, согласованное и протестированное состояние.

Вариантов решения было не много написать скрипт на PowerShell который берет папку с билда, и пробегая по всем SQL файлам проверяет есть там данный объект, если есть то обновляет его, если нет то создает либо написать консольное приложение на C#.

Выбрали второй вариант просто потому что проще, подвязан в GIT и может автоматически публиковаться в нужную папку на сервере с CI. CI запускает данную утилиту, путь к папке с билдом и имя строки подключения передает параметрами.

И вот все заработало.

Что бы не быть голословным приведу скрипты из утилитки.

Program.cs выглядит достаточно просто:

IFileExecuter fileExecuter = new SqlFileToDBExecuter(conectionString);

var updater = new UpdateScriptService(fileExecuter, ".git", "UsefulSQLQueries");

updater.UpdateFromTheDirectory(path, isRelease);

Console.WriteLine("Процедуры только для PROD:\n" + string.Join(", \n", updater.ProductionOnly));
            Console.WriteLine("\n");
            Console.WriteLine("Процедуры требующие внимания:\n" + string.Join(", \n", updater.Warnings));
            Console.WriteLine("\n");
            Console.WriteLine("Оставшиеся ошибки (файл - ошибка):\n" + string.Join(", \n", updater.Errors.Select(x => x.Key + " - " + x.Value)));

Где :

conectionString - это строка подключения которую мы получили(либо получили название и подтянули её из кофига),

path - это путь до билда с файлами.

IFileExecuter - интерфейс, на случает если мы решим сменить БД можно будет варьировать реализацию.

    public interface IFileExecuter
    {
        void Execute(ExecutingModel model);
    }

Код SqlFileToDBExecuter:

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Util.UpdateDatabaseScripts
{
    public class SqlFileToDBExecuter : IFileExecuter
    {
        private readonly string _conectionString;

        private const string ALTER = "ALTER ";
        private const string CREATE = "CREATE ";

        public SqlFileToDBExecuter(string conectionString)
        {
            _conectionString = conectionString;
        }

        public List<string> ProductOnlyCollection { get; set; }

        private T SQLQuery<T>(SqlConnection connection, string sql)
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    return reader.GetFieldValue<T>(0);
                }
            }
        }

        private void SQLExec(SqlConnection connection, string sql)
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        public void Execute(ExecutingModel model)
        {
            using (SqlConnection connection = new SqlConnection(_conectionString))
            {
                connection.Open();

                UpdateScript(model.Name, model.Content, model.Folder, model.Type, connection);

                connection.Close();
            }
        }

        private string UpdateScript(string name, string text, string folder, string type, SqlConnection connection)
        {
            string checkedIfExist = "";

            if (type.Trim().ToUpper() != "FUNCTION")
            {
                checkedIfExist = $"select count(*) FROM sys.{folder} where name = '{name}'";
            }
            else
            {
                checkedIfExist = @$"SELECT COUNT(*)
                  FROM sys.sql_modules m 
                  INNER JOIN sys.objects o ON m.object_id=o.object_id
                  WHERE type_desc like '%function%' and name='{name}'";
            }

            var count = SQLQuery<int>(connection, checkedIfExist);

            var pattern = @"(CREATE|ALTER) *" + type;

            Regex regex = new Regex(pattern, RegexOptions.IgnoreCase);

            var replaceTo = "";

            if (count == 0)
            {
                replaceTo = CREATE + type.ToUpper();
            }
            else
            {
                replaceTo = ALTER + type.ToUpper();
            }

            text = regex.Replace(text, replaceTo);

            SQLExec(connection, text);

            return text;
        }
    }
}

Данный файл создает/обновляет объекты базы данных. Реализован по патерну команда.

В данном листинге кода видно что представления и хранимые процедуры находятся в одних системных таблицах, а функции в других, название папки очень помогло что бы не заводить данные объекты константами(исключение только фукнции).

UpdateScriptService:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace Util.UpdateDatabaseScripts
{
    public class UpdateScriptService
    {
        private readonly IFileExecuter _fileExecuter;
        private string[] _excludes;

        /// <summary>
        /// Количество проходов при создании объектов БД        
        /// </summary>
        private const int COUNT_REPEATS = 3;
        public const string WARNING = "--WARNING";
        public const string PRODUCTION_ONLY = "--PRODUCTION_ONLY";

        public UpdateScriptService(IFileExecuter fileExecuter, params string[] excludes)
        {
            _fileExecuter = fileExecuter;
            _excludes = excludes;

            ProductionOnly = new List<string>();
            Warnings = new List<string>();
            Errors = new List<KeyValuePair<string, string>>();
        }

        public List<string> ProductionOnly { get; set; }

        public List<string> Warnings { get; set; }

        public List<KeyValuePair<string, string>> Errors { get; set; }

        public void UpdateFromTheDirectory(string rootDirectory, bool isRelease)
        {
            var absolutePath = rootDirectory;
            var folderEntries = Directory.GetDirectories(absolutePath);
            var typeFolder = "";

            foreach (string folderName in folderEntries)
            {
                if (_excludes.Any(x => folderName.Contains(x)))
                {
                    continue;
                }

                typeFolder = Path.GetFileName(folderName);
                string[] fileEntries = Directory.GetFiles(folderName);

                var errorsFile = new List<KeyValuePair<string, string>>();

                var forDoing = fileEntries.ToList();

                for (int i = 0; i < COUNT_REPEATS; i++)
                {
                    foreach (string fileName in forDoing)
                    {
                        try
                        {
                            var name = Path.GetFileName(fileName).Split(".")[1];
                            var type = Path.GetFileName(fileName).Split(".")[2];

                            string text = System.IO.File.ReadAllText(fileName);

                            var model = new ExecutingModel
                            {
                                Folder = typeFolder,
                                Name = name,
                                Type = type,
                                Content = text
                            };

                            if (text.Contains(WARNING))
                            {
                                Warnings.Add(fileName);
                            }

                            if (text.Contains(PRODUCTION_ONLY))
                            {
                                ProductionOnly.Add(fileName);
                            }

                            if (!isRelease)
                            {
                                if (text.Contains(PRODUCTION_ONLY)) continue;
                            }

                            _fileExecuter.Execute(model);
                        }
                        catch (Exception ex)
                        {
                            errorsFile.Add(new KeyValuePair<string, string>(fileName, ex.Message));
                        }
                    }

                    if (errorsFile.Count() > 0)
                    {
                        forDoing = errorsFile.Select(x => x.Key).ToList();

                        if (i == COUNT_REPEATS - 1) Errors.AddRange(errorsFile);

                        errorsFile.Clear();
                    }
                    else
                    {
                        break;
                    }
                }
            };
        }
    }
}

Данный файл перебирает все файлы в папке, находит наши пометки в SQL:

--WARNIN - файл требует внимание - так мы помечаем скрипты которые либо не всегда работают, либо их нужно оптимизировать и пока мы наблюдаем за работой.

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

При выводе всех данных в консоль мы получает отчет в логах TeamCity:

Или можно отправить его на почту.

Для развитие данной системы управления исходным кодом SQL объектов мы планируем реализовать выборочное обнволение для этого нам нужно получить по коду MR с гита список файлов которые изменились и передать их утилите тогда утилита сможет обновлять только измененые файлы а не постоянно обновлять все объекты.

Я надеюсь эта статья окажется полезной, спасибо за внимание.

Репозиторий можно скачать по ссылки

Теги:
Хабы:
Всего голосов 5: ↑5 и ↓0 +5
Просмотры 5.4K
Комментарии Комментарии 12