Автоматический редеплоймент сборки
Не все сборки могут быть автоматически продеплоены из VS (см., напр., "Использование COMовских dll в SQL CLR"). В этом случае приходится их собирать как обычные библиотеки классов и каталогизировать вручную, выполняя CREATE ASSEMBLY и создавая содержащиеся в ней процедуры и функции (см. "Полнотекстовый поиск. Наполнение таблицы."\Скрипт 4). По мере того, как сборка обрастает функциональностью и количество содержащихся в ней процедур, функций, агрегатов, UDT растет, скрипт становится все более монструозным. Кроме того, SQL Server не дает удалить сборку, предварительно не удалив созданные на ее основе модули. На мой дилетантский взгляд, это ограничение имеет смысл, если модули, в свою очередь, имеют зависимые от них объекты, как например, таблица TestFTS ((см. "Полнотекстовый поиск. Наполнение таблицы."\Скрипт 5), которая использует в качестве значения по умолчанию для одного из полей результат CLRной функции GetFileExtension(). Точно так же нельзя удалить таблицу, пока имеется другая, зависящая от нее по foreign key. Но когда зависимостей нет, таблица спокойно удаляется, не требуя предварительного удаления содержащихся в ней колонок. Так же, наверно, можно было поступить и со сборками. Если ее модули не имеют дальнейших зависимостей, удаление сборки могло бы автоматом удалять и модули. Но это мои досужие философствования, которые к делу отношения не имеют, тем более, что чаще всего дальнейшие зависимости у модулей есть, как в нашем случае. Для того они и создаются. Стало быть, при передеплойменте нужно удалять зависимые объекты, модули, сборку, а потом все пересоздавать в обратной последовательности. Писать всякий раз руками для этого скрипт ломает. Обшаривать sys.assemblies, sys.assembly_files, sys.assembly_modules и другие DMV, чтобы руками составлять строчку со скриптом, тоже не есть практическое решение, тем более, что полезного поля definition, где в явном виде содержится скрипт создания процедуры/функции, как в sys.sql_modules, для CLRных процедур и функций нет.
В SSMS есть замечательная функциональность, когда кликнув правой кнопкой по объекту в Object Explorer, можно сказать Script ... as, и она сгенерирует на выбор скрипт его создания или удаления. Но хотелось бы не кликать всякий раз вручную, а автоматизировать процесс генерации скрипта. Как мы знаем, практически каждый клик в SSMS можно автоматизировать при помощи программной модели SMO, именно поэтому я так возбудился на эту тему в предыдущем посте. Если нельзя засунуть вызовы SMO на уровень сервера, давайте напишем элементарное консольное приложение, которое будет генерить скрипты удаления и создания для сборки и всех зависимых от нее по цепочке объектов и выполнять эти скрипты.
Простейшее использование скриптования в SMO не составляет сложности - практически у каждого объекта имеется метод Script. Имеется также отдельный класс Scripter, содержащий более тонкие возможности по скриптованию объектов. Мы соединяемся с заданным экземпляром SQL Server, получаем из коллекции баз нужную базу, находим в ней нужную сборку и говорим создать скрипты для удаления (Options.ScriptDrops = true) и создания (Options.ScriptDrops = false). Можно скриптовать сразу несколько объектов, в нашем случае массив состоит из всего одного - нашей сборки: new SMO.SqlSmoObject[] { asm }. Options.WithDependencies = true означает скриптовать не только переданный скриптеру объект, но и связанные с ним.
static void Main(string[] args)
{
string srvName = "", dbName = "TestFS", asmName = "MyAssembly";
SMO.Server srv = new SMO.Server(srvName);
SMO.Database db = srv.Databases[dbName];
SMO.SqlAssembly asm = db.Assemblies[asmName];
SMO.Scripter scr = new SMO.Scripter(srv);
StringBuilder sb = new StringBuilder();
scr.Options.SchemaQualify = true;
scr.Options.WithDependencies = true;
scr.Options.ScriptDrops = true;
scr.Options.IncludeIfNotExists = true;
foreach (string s in scr.Script(new SMO.SqlSmoObject[] { asm })) sb.Append(s + "\n\n");
string scriptToDrop = sb.ToString();
scr.Options.ScriptDrops = false;
sb.Length = 0;
foreach (string s in scr.Script(new SMO.SqlSmoObject[] { asm })) sb.Append(s + "\n\n");
string scriptToCreate = sb.ToString();
Debug.WriteLine(scriptToDrop); Debug.WriteLine(scriptToCreate);
}
Скрипт 1
Результат получается в виде коллекции строк, которые StringBuilder собирает в одну (sb.Append). Вот, что получилось в нашем случае для удаления сборки:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileExtension]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetFileExtension]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoadDir]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LoadDir]
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyAssembly' and is_user_defined = 1)
DROP ASSEMBLY [MyAssembly]
Скрипт 2
И для ее создания:
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Interop.Shell32' and is_user_defined = 1)
CREATE ASSEMBLY [Interop.Shell32]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000...
WITH PERMISSION_SET = UNSAFE
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyAssembly' and is_user_defined = 1)
CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000...
WITH PERMISSION_SET = UNSAFE
Скрипт 3
Скрипт 2 полностью устраивает. В Скрипте 3 не устраивают две вещи. Первое, если мы перекомпилили dllю и хотим пересоздать в SQL Server сборку на ее основе, оператор CREATE ASSEMBLY должен содержать путь к этой dlle, а не прочитанный из нее прошлый раз бинарный контент. Второе - в Скрипте 3 она поменяла направление зависимости. Представим себе объект как узел в иерархии объектов. Под связанными объектами можно понимать как дочерние ссылающиеся на него (referencing) объекты, т.е. зависящие от него, так и родительские (referenced, выше по иерархии), на которые ссылается он, т.е. от которых он сам зависит. Нас интересуют дочерние объекты, т.е. зависящие от этой сборки, и в скрипте удаления она абсолютно правильно выбрала их. Но в скрипте создания Options.WithDependencies поменяла направление, и она пошла скриптовать вверх по иерархии. Это не есть хорошо. Для того, чтобы это дело поправить, требуется прибегнуть к более тонким настройкам скриптера. Их у него есть, но документированы они отвратительно. Знать, параметры каких типов принимает или возвращает тот или иной метод, вообще говоря, недостаточно, чтобы понять, для чего он нужен и как работает. Практически изо всех членов класса Scripter идут в качестве иллюстрации ссылки на пример https://msdn.microsoft.com/ru-ru/library/ms162153.aspx, в котором задействовано от силы один, два и обчелся. Пример не позволяет представить цельной картины принципов работы скриптера, мало того, "Создание сценария зависимостей для базы данных на языке Visual C#" в нем просто не работает, потому что это какая-то смесь С#, VB.Net, французского, нижегородского... Неудивительно, что по форумам народ постоянно задает вопросы про скриптер, а тамошние гуры ничтоже сумняшеся копипастят этот пример, больше нечего: https://stackoverflow.com/questions/539067/how-can-i-programatically-clone-a-database-schema-in-sql-server. Впрочем, у нас таких гур тоже хватает, которые заработали немеренное число постов за счет копи-паста, а самостоятельно подумать уже нечем, потому что модераторы. Остается надувать щеки и гнобить новичков.
Если кратко, тонкие настройки скриптера позволяют построить дерево иерархии объектов. Это делается при помощи метода DiscoverDependencies(new SMO.SqlSmoObject[] { asm }, false), где в первом параметре передаются базовые объекты, а во втором - направление движения по иерархии: true = вверх от каждого базового объекта к тем, от которых он зависит, или false = вниз, к тем, которые зависят от него. Построенное дерево иерархий можно посмотреть и при необходимости подправить в нем узлы. На основе дерева строится коллекция объектов, т.е. уже плоский список, в каком порядке они будут обходиться, чтобы сгенерировать скрипт для каждого. Это делается при помощи метода WalkDependencies(дерево). Коллекцию, то есть порядок, при необходимости тоже можно подправить. В коде это иллюстрируется. Наконец, генерация скриптов, которая делается при помощи метода ScriptWithList(коллекция). Этого нам на первый раз достаточно.
В построенном скрипте создания при помощи шаблона регулярных выражений отыскивается строка с бинарным контентом сборки и заменяется на пути к ее файлам. Полные имена файлов сборки находятся в sys.assembly_files, а в случае SMO это коллекция в свойстве SqlAssemblyFiles объекта сборки. Для иллюстрации идеи, наверно, все. Понятно, что делалось на коленках, так что простор для исправлений и совершенствований, но для моей узкой задачи автоматизации редеплоймента сборки, переносящей файлы из папки в таблицу, оно, кажется, даже работает:
using System;
using System.Text;
using System.Diagnostics;
using SMO = Microsoft.SqlServer.Management.Smo; //C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
//Также требует references на
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
//C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
using System.Text.RegularExpressions;
class Program
{
static int dependencyTreeLevel;
static void Main(string[] args)
{
string srvName = "", dbName = "TestFS", asmName = "MyAssembly";
SMO.Server srv = new SMO.Server(srvName);
SMO.Database db = srv.Databases[dbName];
SMO.SqlAssembly asm = db.Assemblies[asmName];
SMO.Scripter scr = new SMO.Scripter(srv);
StringBuilder sb = new StringBuilder();
scr.Options.SchemaQualify = true; //чтобы объекты скриптовались со своими схемами (dbo, ...)
scr.Options.IncludeIfNotExists = true; //предваряются оператором if exists при удалении и if not exists при создании
scr.Options.ScriptDrops = true; //создается скрипт на удаление
scr.Options.WithDependencies = true; //при генерации скрипта на удаление означает, что в скрипт также включаются referencing objects, т.е. которые зависят от данного
scr.Options.DriUniqueKeys = true; //таблице TestFTS требуется ограничение unique на rowguidcol, его также нужно заскриптовать
foreach (string s in scr.Script(new SMO.SqlSmoObject[] { asm })) sb.Append(s + "\n\n");
string scriptToDrop = sb.ToString(); sb.Length = 0;
scr.Options.ScriptDrops = false; //генерируется скрипт на создание
//при этом Options.WithDependencies = true означает, что будут также заскриптованы referenced objects, т.е. объекты, от которых зависит данный
//нас это не устраивает, нам по-прежнему нужны referencing objects, поэтому прибегаем к явному построению дерева связанных объектов
SMO.DependencyTree dt = scr.DiscoverDependencies(new SMO.SqlSmoObject[] { asm }, false); //parent = false означает, что под связанными объектами понимаются не родительские, а дочерние, т.е. referencing
dependencyTreeLevel = 0; //это будет глубина текущего уровня для отступов при выводе
EnlistNodesInDependencyTree(dt.FirstChild);
SMO.DependencyCollection dc = scr.WalkDependencies(dt); //скрипт получается в порядке от дочерних объектов к родительским, что неправильно, т.к. сначала должен создаваться родительский объект, а уже потом дочерние на его основе
SMO.DependencyCollection dc1 = new SMO.DependencyCollection();
for (int i = dc.Count; i > 0; i--) dc1.Add(dc[i - 1]); //переставляем в обратном порядке
foreach (string s in scr.ScriptWithList(dc1)) //генерим скрипты от коллекции явно построенных связанных объектов
sb.Append(s + "\n\n");
string scriptToCreate = sb.ToString();
scriptToCreate = ReplaceAssemblyBytesToFilePath(scriptToCreate, asm.SqlAssemblyFiles);
Console.WriteLine("Вы согласны с тем, что будут выполнены следующие скрипты [Y/N]?");
Console.WriteLine();
Console.WriteLine(scriptToDrop); Console.WriteLine(scriptToCreate);
if (Console.ReadKey().KeyChar.ToString().ToUpper() == "Y")
{
db.ExecuteNonQuery(scriptToDrop); db.ExecuteNonQuery(scriptToCreate);
}
}
/// <summary>
/// Процедура визуализирует построенное дерево связанных объектов.
/// </summary>
/// <param name="firstNodeOnLevel">Первый узел дерева</param>
static void EnlistNodesInDependencyTree(SMO.DependencyTreeNode firstNodeOnLevel)
{
if (firstNodeOnLevel == null) return;
SMO.DependencyTreeNode dtn = firstNodeOnLevel; dependencyTreeLevel++;
do
{
Debug.WriteLine(new String(' ', dependencyTreeLevel), dtn.Urn);
EnlistNodesInDependencyTree(dtn.FirstChild);
dtn = dtn.NextSibling;
}
while (dtn != null);
}
/// <summary>
/// Процедура производит замену в скрипте создания сборки
/// create assembly ... from 0x4D5A900003000 ... with permission_set = ...
/// бинарщины на пути к файлам этой сборки:
/// create assembly ... from 'C:\...\bin\Debug\ClassLibrary1.dll' with permission_set ...
/// </summary>
/// <param name="scriptToCreate">
/// Сгенеренный скрипт создания сборки и, возможно, других объектов.
/// Однако предполагается, что в этом скрипте оператор create assembly один.
/// В противном случае каждую сборку надо скриптовать отдельно.
/// </param>
/// <param name="files">Файлы из SqlAssembly.SqlAssemblyFiles</param>
/// <returns></returns>
static string ReplaceAssemblyBytesToFilePath(string scriptToCreate, SMO.SqlAssemblyFileCollection files)
{
Regex pattern = new Regex(@"create assembly \[\w+\]\r\nauthorization \[\w+\]\r\nfrom ", RegexOptions.IgnoreCase);
Match m = pattern.Match(scriptToCreate);
StringBuilder sb = new StringBuilder(scriptToCreate.Substring(0, m.Index + m.Length));
foreach (SMO.SqlAssemblyFile file in files) { sb.Append('\''); sb.Append(file.Name); sb.Append("', "); }
sb.Remove(sb.Length - 2, 1); //убираем последнюю запятую (пробел оставляем)
pattern = new Regex(@"with permission_set", RegexOptions.IgnoreCase);
m = pattern.Match(scriptToCreate, m.Index);
sb.Append(scriptToCreate.Substring(m.Index));
return sb.ToString();
}
}
Скрипт 4
Рис.1
Потребовалось дополнительно отметить скриптование в таблице ограничения unique (scr.Options.DriUniqueKeys = true), т.к. это условие на колонку $rowguid является обязательным, когда в таблице присутствуют файлстримовские поля - см. пост "Введение в FILESTREAM", создание таблицы Media. Можно проверить, что действительно сборка и зависимые от нее объекты были пересозданы:
select name, create_date from sys.assemblies where name = 'MyAssembly'
select a.name, am.assembly_method, o.create_date from sys.objects o join sys.assembly_modules am on o.object_id = am.object_id join sys.assemblies a on am.assembly_id = a.assembly_id
и т.д.
Рис.2
Написание Powershellьного варианта Скрипт 4 предоставляется читателям в качестве самостоятельного упражнения.