zohanzohan 19 фев в 15:37

Рассмотрим нововведения SQL Server Data Tools, связанные со средствами разработки БД для Microsoft SQL Server

Средний

17 мин

1.4K

Блог компании ЮMoneyMicrosoft SQL Server*SQL*

Обзор

Привет, Хабр. Меня зовут Артём, я старший инженер в департаменте аналитических решений ЮMoney. Вас ждёт очень большая статья, в которой мы вместе:

посмотрим на изменения формата файла проекта sqlproj;
разберёмся с новой возможностью публикации dacpac в репозиторий артефактов (например, NuGet) с целью многократного переиспользования в других проектах sqlproj;
напишем анализатор кода tsql на C#, опубликуем его в репозиторий и используем в проекте sqlproj.

Приятного чтения! 😊

Предисловие

Прежде чем переходить к нововведениям SQL Server Data Tools, расскажу, какое место этот инструмент занимает в жизненном цикле разработки БД Microsoft SQL Server. Также обсудим другие инструменты, используемые при разработке БД.

В качестве модели ветвления используем gitflow.
Для решения задач инженера данных — инструменты Microsoft, такие как SQL Server, SSRS, SSIS, SSAS.
При разработке — Visual Studio, SQL Server Management Studio (SSMS).

Все эти инструменты покрывают большую часть потребностей при решении задач инженера данных. Безусловно, у них есть недостатки, но в целом это хорошие инструменты. Код баз данных содержится в проектах sqlproj, которые хранятся в корпоративном git-репозитории. Разработка БД производится на локальном ПК разработчика с использованием Microsoft SQL Server Developer Edition. Типичный процесс разработки БД выглядит следующим образом:

1. Из ветки dev срезаем ветку feature, в рамках которой будет выполняться задача.

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

2. Синхронизируем объекты из проекта sqlproj (изменения, сделанные коллегами) в БД локального экземпляра Microsoft SQL Server.

Синхронизировать схему БД можно следующими инструментами:

o SQL Server Data Tools (SSDT), который устанавливается как расширение Visual Studio;

o Flyway;

o Liquibase;

o Azure Data Studio.

Только первый из списка до сих пор может работать исключительно на Windows. Мы используем именно его, так как он входит в состав инструментов Microsoft, предназначенных для решения задач инженера данных.

Azure Data Studio умеет делать сравнение БД/dacpac/SDK-style sqlproj (о котором и идёт речь в этой статье).

3. Разрабатываем объекты БД: добавляем таблицы, процедуры и прочие sql-объекты.

При разработке можно использовать:

У каждой из этих IDE есть как достоинства, так и недостатки. Их сравнение заслуживает отдельной статьи.

4. Синхронизируем объекты БД локального экземпляра Microsoft SQL Server обратно в проект баз данных sqlproj. То есть делаем то же самое, что и в пункте 2, только в обратную сторону.

5. Делаем commit изменений и git push.

6. Создаем pull request в ветку dev. После устранения всех замечаний вливаем ветку feature в dev.

7. Запускаем сборку релиза. При сборке происходит сравнение объектной модели (dacpac) предыдущего релиза с текущей сборкой dacpac. Для этого используется SqlPackage Script. В качестве артефакта этого сравнения получаем скрипт миграции sql, который приводит БД из состояния предыдущего релиза к актуальному состоянию. Тестировщики развёртывают скрипт миграции на тестовый стенд.

8. Протестированный релиз выкладываем на боевые сервера. Релизную ветку вливаем в master.

Какие есть неудобства

Когда мы разобрались, как устроен процесс разработки БД, хочется подсветить неудобства, с которыми мы сталкиваемся:

1. На сборочных узлах (CI) приходится ставить тяжеловесный Visual Studio ради расширения SSDT, которое умеет собирать проекты sqlproj.

2. Visual Studio можно устанавливать только на Windows.

3. Как следствие — мы не можем запускать построение проекта в Docker-контейнере.

4. Неудобно искать путь к MsBuild, с помощью которого происходит построение sqlproj:

# Можно искать путь к MsBuild
$pathToMsBuild = &"${env:ProgramFiles(x86)}\Microsoft Visual Studio\Installer\vswhere.exe" `
    -latest `
    -prerelease `
    -products * `
    -requires Microsoft.Component.MSBuild `
    -find MSBuild\**\Bin\MSBuild.exe

# А можно забить статикой
$pathToMsBuild = "C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\MSBuild.exe"

# Путь к проекту
$pathToProject = "C:\Projects\Legacy\Legacy.sqlproj"

# Построение проекта sqlproj.
&"$pathToMsBuild" -target:Build $pathToProject

Получается либо слишком многословно, либо костыльно, так как при смене версии Visual Studio придётся исправлять эти пути.

5. Для создания скрипта миграции необходимо найти путь к SqlPackage. На моём домашнем ПК он такой: C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.sql. Тоже не совсем удобно его хардкорно где-то прописывать.

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

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>SqlConsumer</Name>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{4101619b-c2b0-4936-a51b-bfb09c86f386}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <OutputType>Database</OutputType>
    <RootPath>
    </RootPath>
    <RootNamespace>SqlConsumer</RootNamespace>
    <AssemblyName>SqlConsumer</AssemblyName>
    <ModelCollation>1033, CI</ModelCollation>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <DeployToDatabase>True</DeployToDatabase>
    <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>
  <Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <ItemGroup>
    <Folder Include="Properties" />
  </ItemGroup>
</Project>

Хотелось бы ещё знать, чем отличаются артефакты проекта sql при смене конфигураций Release/Debug. 🤔

7. Нет возможности добавлять ссылку на dacpac других проектов (БД), расположенных в других репозиториях. Давайте посмотрим правде в глаза: Microsoft SQL Server стоит достаточно дорого, поэтому на одном сервере устанавливается много разных БД. Наверняка у каждого на предприятии есть справочные данные, которые используются разными БД. Эту БД можно подключить, если разработка ведётся в рамках одного solution. Но что делать, если базы данных, использующие справочные данные, расположены в разных репозиториях?

8. Неудобство запуска самописных статических анализаторов. Написать собственные анализаторы можно, а вот возможность их распространения формально присутствует, но она не Enterprise-ready (далее будет более подробное описание).

Нововведения в инструментах

Для решения перечисленных проблем команда разработчиков SSDT приступила к мощному рефакторингу, в результате которого исчезла прямая зависимость инструментов построения проектов SQL от Visual Studio. Рассмотрим подробнее.

Microsoft.Build.Sql

Microsoft.Build.Sql — это SDK, позволяющий вести командную разработку базы данных, выполнять построение проекта, в результате которого происходит проверка на ошибки и выполняется статический анализ кода. Этот SDK стал одним из модулей, выпиленных из монолита SSDT. Именно за счёт этого SDK для построения проекта sqlproj теперь не требуется устанавливать Visual Studio с расширением SSDT. Более того, этот SDK — кроссплатформенный, как и сам dotnet. В ноябре 2021 года была опубликована первая версия 0.1.1-alpha SDK Microsoft.Build .Sql, а спустя три года выпустили 1.0.0-rc1. Первая новость про Microsoft.Build.Sql появилась 21 апреля 2022 года в блоге Azure SQL Blog.

Перейдём от слов к делу и создадим sqlproj в формате SDK-style:

1. Один раз необходимо установить шаблон проекта Microsoft.Build.Sql.Templates (GitHub, NuGet):

PS > dotnet new -i Microsoft.Build.Sql.Templates

2. Создадим проект sqlproj:

PS > dotnet new sqlproj -n ProductsTutorial

3. Откроем только что созданный ProductsTutorial.sqlproj:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="1.0.0-rc1" />
  <PropertyGroup>
    <Name>ProductsTutorial</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>
</Project>

Можете сравнить его с содержимым из предыдущего раздела в формате old-style. В новом формате строк получилось сильно меньше. Осталось лишь то, на что мы можем повлиять. Обратите внимание, что в новом формате проекта файлы sql не будут вноситься в sqlproj в явном виде, а будут включены автоматически. Наверняка вы не раз получали merge-конфликт, когда один человек вливал в ветку dev свои новые файлы раньше, чем это сделали вы.

В первый раз, когда вы запустите построение проекта sqlproj, на вашем ПК, скорее всего, не будет установлен Microsoft.Build.Sql. Он скачается автоматически из NuGet.

Как видите, введение этого модуля решает несколько проблем, описанных ранее:

Во-первых, sqlproj стал более лаконичным.
Во-вторых, построение sqlproj можно выполнять на большинстве современных операционных систем.
В-третьих, мы (разработчики, пишущие автоматизацию сборки проекта) не завязаны на расположении MsBuild при построении проекта:

> dotnet build E:\Projects\Modern\Modern.sqlproj

Наконец, нет необходимости включать список файлов sql в sqlproj в явном виде.

SqlPackage

SqlPackage — это программа командной строки, которая автоматизирует задачи разработки баз данных, предоставляя некоторые из общедоступных API Платформы приложений уровня данных (DacFx). Основные варианты использования SqlPackage связаны с переносимостью баз данных и развёртыванием для семейства баз данных SQL Server, SQL Azure и Azure Synapse Analytics. SqlPackage можно автоматизировать с помощью Azure Pipelines и GitHub или других средств CI/CD.

Как я говорил ранее, SqlPackage был частью SSDT. А сейчас его можно установить как dotnet tool:

PS > dotnet tool install -g microsoft.sqlpackage

После этого сразу можно использовать SqlPackage без указания полного пути, где установлен этот модуль. Более того, SqlPackage теперь кроссплатформенный.

Для построения скрипта миграции от одного релиза к другому необходимо выполнить команду:

PS > sqlpackage /Action:Script `
                /OutputPath:"E:\Projects\Scratch\New folder\Dictionary\Migration.sql" `
                /SourceFile:"E:\Projects\Scratch\New folder\Dictionary\Dictionary_1.0.1.dacpac" `
                /TargetFile:"E:\Projects\Scratch\New folder\Dictionary\Dictionary_1.0.0.dacpac" `
                /TargetDatabaseName:Dictionary

Новые возможности в проектах MS SQL

Помимо выпиливания части инструментов SSDT из Visual Studio, появились также новые функциональные возможности.

Подключение БД из NuGet

В отличие от некоторых других СУБД, Microsoft SQL Server позволяет из одной БД обращаться к объектам другой БД, расположенной на том же сервере. Чтобы использовать такую возможность, во время работы с проектом необходимо подключить ссылку на используемую БД, которая находится в том же solution. А что, если используемая БД расположена в проекте, который находится в другом репозитории? В таком случае можно добавлять dacpac. Но dacpac — это артефакт построения sqlproj (либо результат импорта схемы БД). А артефакты принято хранить где? Правильно: в репозитории артефактов, например в NuGet. С появлением SDK-style-проектов Как раз-таки вместе со всеми нововведениями, начиная с SDK-style-проектов добавилась возможность отправлять dacpac в виде NuGet-пакета в репозиторий с артефактами, который в дальнейшем может быть переиспользован другими проектами.

А теперь давайте разберём, как это выглядит, на конкретном примере.

Предположим, у нас есть информационная система, которая обеспечивает нужды очень известной сети розничных магазинов, расположенных по всему миру. В нашем распоряжении — мощный сервер, на котором установлен Microsoft SQL Server и за который сеть магазинов заплатила огромное количество денег. На этом сервере много разных баз данных, и его ресурсы должны окупаться. Вот эти базы данных:

HumanResources (сотрудники, заработные платы);
ProductInventory (продаваемые товары);
Sales (продажи);
Marketing (акции, выгодные предложения).

Все перечисленные базы данных не связаны друг с другом, пользуются курсами валют из БД Dictionary и хранятся в одном solution.

Такой подход напоминает монолитное приложение, но есть важный недостаток: при увеличении количества объектов сильно растёт время построения решения. Предположим, мы внесли правки только в БД Sales, но строиться будут все базы данных. С решением этой проблемы нам может помочь та самая возможность подключения используемой БД (в нашем случае — Dictionary) в качестве NuGet-пакета.

Чтобы распилить наш монолит, надо выделить каждую из перечисленных баз данных в отдельные независимые репозитории. В каждую из этих БД необходимо через NuGet подключить БД Dictionary (предполагается, что она собрана и опубликована в репозиторий артефактов), чтобы получать из неё курсы валют. На момент написания статьи проще всего подключить NuGet-библиотеку через GUI в Azure Dev Studio. В остальные IDE эта возможность, скорее всего, доедет немного позже.

Теперь посмотрим, как это выглядит на практике:

1. Создадим БД Dictionary:

PS > dotnet new sqlproj -n Dictionary

2. Затем — таблицу CurrencyRate:

PS > '
CREATE TABLE dbo.CurrencyRate (
  Date         DATE          NOT NULL,
  FromCurrency INT           NOT NULL,
  ToCurrency   INT           NOT NULL,
  Unit         INT           NOT NULL,
  Rate         DECIMAL(20,5) NOT NULL
);' > Dictionary\CurrencyRate.sql

3. Соберём пакет NuGet с БД Dictionary и опубликуем его в локальный репозиторий:

PS > dotnet pack Dictionary\Dictionary.sqlproj --configuration Release
PS > dotnet nuget push "Dictionary\bin\Release\Dictionary.1.0.0.nupkg" --source NuGetLocalRepository

4. Также создадим проект ProductInventory с таблицей:

PS > dotnet new sqlproj -n ProductInventory

PS > '
CREATE TABLE dbo.ProductPriceHistory (
  ProductId    INT            NOT NULL,
  Date         DATE           NOT NULL,
  Price        DECIMAL(20, 5) NOT NULL,
  CurrencyCode INT            NOT NULL,
);' > ProductInventory\ProductPriceHistory.sql

PS > '
CREATE PROCEDURE dbo.GetProductPrice @productId      INT,
                                     @targetCurrency INT
AS
   SELECT ProductPriceInTargetCurrency = p.Price * c.Unit / c.Rate
   FROM dbo.ProductPriceHistory AS p
   INNER JOIN [$(Dictionary)].dbo.CurrencyRate AS c
                                               ON c.FromCurrency = p.CurrencyCode
                                              AND c.ToCurrency   = @targetCurrency
                                              AND c.Date         = p.Date
                                              AND c.Date         = CAST(GETDATE() AS DATE)
' > ProductInventory\GetProductPrice.sql

5. Попробуем собрать проект ProductInventory:

PS > dotnet build ProductInventory\ProductInventory.sqlproj

В output мы получим семь похожих предупреждений:

Procedure: [dbo].[GetProductPrice] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [$(Dictionary)].[dbo].[CurrencyRate].[c]::[Rate], [$(Dictionary)].[dbo].[CurrencyRate].[Rate] or [dbo].[ProductPriceHistory].[c]::[Rate].

Как можно догадаться, проект ProductInventory ничего не знает о БД Dictionary. Чтобы это исправить, мы добавим dacpac БД Dictionary из NuGet-репозитория. Сейчас это проще всего сделать через GUI Azure Data Studio. Либо можно использовать наш любимый Copy/Paste в файл sqlproj:

  <ItemGroup>
    <SqlCmdVariable Include="Dictionary">
      <Value>$(Dictionary)</Value>
      <DefaultValue>Dictionary</DefaultValue>
    </SqlCmdVariable>
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Dictionary">
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      <DatabaseSqlCmdVariable>Dictionary</DatabaseSqlCmdVariable>
      <Version>1.0.0</Version>
    </PackageReference>
  </ItemGroup>

Когда соберём проект, получим успешный результат без ошибок.

И что с этой возможностью делать?

В департаменте аналитических решений ЮMoney есть большие репозитории, в которых хранятся десятки БД с такими же зависимостями, как в примере выше. В таких больших репозиториях, даже если изменения происходят только в одной базе данных из 20, пересобирать приходится все 20. А с появлением возможности использования NuGet-пакетов можно разделить эти БД по разным репозиториям и собирать только те базы, в которых были реальные изменения. Это существенно сократит время сборки.

Подробное описание этой возможности можно прочитать тут: https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/database-references?view=sql-server-ver16&pivots=sq1-visual-studio-code

Подключение анализаторов кода TSQL

В предыдущей статье я рассказывал, как можно писать анализатор кода TSQL. Но там я намеренно умолчал о способе публикации этих расширений (далее подробно описываю причину). С тех пор прошло много времени, и команда, занимающаяся разработкой SSDT, начала навёрстывать упущенное.

У Microsoft DacFx есть прекрасный инструментарий для написания проверок семантической модели БД и синтаксических деревьев TSQL. Но вариант публикации самописных анализаторов был неприемлем для Enterprise: в официальной документации предлагали копировать скомпилированный dll с анализаторами в строго определённую папку, находящуюся неподалеку от SSDT:

Next, copy the assembly information to the Extensions directory. When Visual Studio starts, it identifies any extensions in \Common7\IDE\Extensions\Microsoft\SQLDB\DAC\Extensions directory and subdirectories, and makes them available for use.

For Visual Studio 2022, the is usually C:\Program Files\Microsoft Visual Studio\2022\Enterprise. Replace Enterprise with Professional or Community depending in your installed Visual Studio edition.

Copy the SampleRules.dll assembly file from the output directory to the \Common7\IDE\Extensions\Microsoft\SQLDB\DAC\Extensions directory. By default, the path of your compiled .dll file is YourSolutionPath\YourProjectPath\bin\Debug or YourSolutionPath\YourProjectPath\bin\Release.

Это сложно и неудобно по нескольким причинам:

1. Нет готового механизма распространения dll внутри департамента. Библиотеку, которая содержит наши проверки TSQL, необходимо каким-то образом распространить на ПК разработчиков команд, использующих TSQL. Для распространения необходимо создать заявку нового релиза на HelpDesc, перечислив всех разработчиков, на которых надо раскатить dll с проверками TSQL. То есть для распространения этой библиотеки нет никакой готовой автоматизации. Даже если её и разработать, она будет выглядеть костыльной. А ещё при копировании свежей версии библиотеки из нашего репозитория с артефактами в локальную папку DAC может возникнуть ошибка типа "Unable to copy file "MyTsqlAnalyzer.dll" to "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\IDE\Extensions\Microsoft\SQLDB\DAC\Extensions\MyTsqlAnalyzer.dll". The process cannot access the file 'MyTsqlAnalyzer.dll' because it is being used by another process.", так как в момент копирования на ПК разработчика будет открыт Visual Studio.

2. При обновлении библиотеки проверки TSQL могут посыпаться проекты, успешно построенные с использованием предыдущей версии анализатора TSQL. Предположим, у нас есть правило, которое проверяло наличие обязательного описания к таблицам. Это правило включено в большом проекте с сотнями таблиц. Позже мы решаем, что надо также обязательно описывать все столбцы. Обновили имеющееся правило, развернули на все ПК разработчиков. Разработчику приходит какая-то бизнесовая задача, он её выполняет, компилирует проект и получает сотни/тысячи ошибок, так как новая версия валидатора требует описания всех столбцов. Нехорошо получается. Развивать правила валидаторов тяжело, так как усиление требований может нарушить совместимость со многими проектами.

3. Тяжело обновлять библиотеку на сервере CI, так как одновременно может быть запущено построение нескольких проектов, которые будут удерживать эту библиотеку. Это создаст препятствие при обновлении на более свежую версию библиотеки.

Чтобы устранить перечисленные проблемы, команда SSDT решила вдохновиться примером своего «старшего брата» — C#, у которого есть анализаторы Roslyn.

Roslyn-анализаторы для C#/Visual Basic

Анализаторы кода существуют не первый десяток лет и очень востребованы, так как здорово помогают находить ошибки, писать более лаконичный код, поддерживать общепринятый code style.

Ещё лет 10 назад анализаторы кода распространялись в виде плагинов к Visual Studio. Dotnet в какой-то момент стал кроссплатформенным, и, помимо Visual Studio, обрели популярность другие IDE. Всё это привело к тому, что возникла необходимость использовать одни и те же проверки в разных средах разработки и на разных операционных системах. Так появилась на свет платформа для написания анализаторов Roslyn для C#/VBasic (первый pull request документации от марта 2018 года).

Для тех, кто не знаком с миром dotnet, анализаторы Roslyn представляют собой обычные NuGet-пакеты (как и все библиотеки из общедоступных репозиториев, которые можно переиспользовать в проектах). После подключения такого анализатора во время написания кода в любой IDE происходит его проверка, подсветка предупреждений с вариантами возможных исправлений.

А теперь давайте представим, что у нас есть два проекта C#: P1 и P2. Предположим, что к ним подключены анализаторы кода версии V1. После выхода анализатора версии V2 можно обновить его в проекте P1, исправить новые появившиеся code-smells, при этом у нас не поломается проект P2, так как в нём ещё никто не перешёл на версию V2. Именно такого подхода не хватало при написании собственных анализаторов для TSQL.

Команда SSDT позаимствовала эту идею — и теперь можно писать и распространять анализаторы TSQL в виде NuGet-пакета: в sdk-style sqlproj появилась возможность использовать анализаторы кода, как это сделано в Roslyn. Если вам не терпится написать свой анализатор, то можно воспользоваться инструкцией из первоисточника:

1. Создадим проект с анализатором:

PS > dotnet new sqlcodeanalysis -n "WaitForDelay"

После выполнения команды будут созданы:

файл проекта анализатора WaitForDelay.csproj;
пример анализатора tsql WaitForDelay.cs, который, находя выражение WAITFOR DELAY в проекте TSQL, будет выдавать ошибку.

2. Соберём NuGet-пакет с нашим анализатором и опубликуем его в локальный репозиторий:

PS > dotnet pack WaitForDelay\WaitForDelay.csproj --configuration Release
PS > dotnet nuget push "WaitForDelay\bin\Release\Sample.WaitForDelay.1.0.0.nupkg" --source NuGetLocalRepository

3. Подключим анализатор к БД, например к Dictionary:

PS > dotnet add package Sample.WaitForDelay

4. Создадим процедуру, в которую намеренно добавим WAITFOR DELAY:

PS > '
CREATE PROCEDURE dbo.UselessProcedure
AS
  WAITFOR DELAY '02:00:00';
' > UselessProcedure.sql

5. Построим проект БД:

PS > dotnet build Dictionary.sqlproj

Но в консоли не отобразится никаких предупреждений, так как статический анализатор кода не отработал при построении проекта. Чтобы это исправить, в файл Dictionary.sqlproj необходимо добавить строку в PropertyGroup:

<RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>

Построим проект ещё раз:

PS > dotnet build Dictionary.sqlproj

Теперь в консоли отобразится:

...\Dictionary\UselessProcedure.sql(4,5,4,5): StaticCodeAnalysis warning SSCA1004: Sample.WaitForDelay : Avoid using WAITFOR DELAY in [dbo].[UselessProcedure]

Build succeeded with 1 warning(s) in 3.2s

Чего греха таить: на предупреждения никто не обращает внимания. Поэтому будем трактовать все предупреждения от статического анализатора как ошибки. Для этого в Dictionary.sqlproj в блок PropertyGroup необходимо добавить:

<SqlCodeAnalysisRules>+!Sample.WaitForDelay.SSCA1004;</SqlCodeAnalysisRules>

Синтаксис достаточно прост:

в начале идёт +/-, что показывает, включено правило или выключено;
восклицательный знак — опциональный, он означает, что предупреждение будет интерпретировано как ошибка;
код правила (в нашем случае он автоматически появился в шаблоне при создании проекта WaitForDelay).

После очередного построения получим:

Dictionary failed with 1 error(s) (2.2s) → bin\Debug\Dictionary.dll
    E:\Projects\Scratch\New Folder\Dictionary\UselessProcedure.sql(4,5,4,5): StaticCodeAnalysis error SSCA1004: Sample.WaitForDelay : Avoid using WAITFOR DELAY in [dbo].[UselessProcedure]

Build failed with 1 error(s) in 2.7s

Выводы

Итак, давайте соберём все новые плюсы:

1. В результате разделения SSDT на отдельные модули у нас появилась возможность разрабатывать проекты для MS SQL на разных операционных системах. Теперь нет никаких ограничений, чтобы вести разработку, например, на Ubuntu (если вы, конечно, не используете SSRS, SSAS, SSIS — их, скорее всего, никогда не сделают кроссплатформенными).

2. Можно переиспользовать dacpac в других проектах через NuGet.

3. Появилась возможность подключать анализаторы кода TSQL через NuGet.

4. Файл проекта sqlproj стал более лаконичным.

Не знаю, как вас, а меня эти нововведения очень вдохновляют. 😊

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

Делитесь в комментариях своим мнением.

В департаменте аналитических решений, где я работаю, открыта вакансия системного аналитика. Будем рады пообщаться! 😉

Теги:

Хабы: