DacFx/SSDT public model #3. DacFx API: TSqlModel

Сегодня мы возвращаемся к теме изучения API DacFx, начатой в предыдущей статье и поговорим уже о том, как анализировать содержимое DacFx пакета, а точнее – как работать с его моделью.

Большая часть сегодняшней статьи основана на материале блога DacFx Public Model Tutorial.

Что такое модель SQL скрипта?

Давайте рассмотрим вот такой T-SQL скрипт:

create table Users
(
	Id int NOT NULL IDENTITY(1, 1),
	Name nvarchar(MAX) NOT NULL,
	BirthDate date NULL,

	primary key (Id)
); 
GO

create table Logins
(
	Id int NOT NULL IDENTITY(1, 1),
	Name nvarchar(MAX) NOT NULL,
	UserId int NOT NULL,

	foreign key (UserId) references Users(Id)
); GO

Что мы видим на нем:

• Две таблицы (с именами Users и Logins), связанные отношением 1:M

• Каждая таблица имеет по три столбца, у каждого из которых указаны:
• Имя,
• Тип данных
• Допустимость NULL значений
• Некоторая дополнительная информация (например, то, что столбцы Id – это автоинкрементируемые поля с шагом 1)А это есть ничто иное, как структурированное описание содержимого приведенного выше скрипта (объектов, их свойств и связей между ними). Иными словами, модель скрипта.

  В чем смысл использования такого структурированного представления? Что оно может дать?

  На самом деле весьма немало. Вот несколько приходящих с ходу на ум сценариев:

• Поиск ошибок. Например, внешний ключ в таблице Logins ссылается на таблицу Users и поле Id. Имя модель мы во-первых, без особых сложностей можем узнать о наличии такой связи, а во-вторых, проверить, что указанная таблица и поле существуют
• Проверка соответствия требованиям. Например, у нас может быть требование наличие кластерного индекса в каждой таблице. Или что все имена таблиц должны быть представлены во множественном числе (т.е. не User, а Users).
• Управление порядком деплоя. Имея полное дерево зависимостей между таблицами мы можем определить порядок в котором можно их создавать / менять, не рискуя получить ошибку (впрочем, конкретно эту задачу можно решить проще – сначала создать сами таблицы, а потом все внешние ключи).
• …Итак, в полезности работы со структурированной моделью, а не исходным скриптом, мы убедились. Следующий логичный вопрос – как её получить.

  Модель в DacFx – TSqlModel

  Центральной точкой в управлении моделями SQL в DacFx является класс Microsoft.SqlServer.Dac.Model.TSqlModel.

  Для начала попробуем с помощью него загрузить модель для имеющегося SQL-скрипта:

  public void CreateModelFromScript()
  {
      using (var sqlModel = new TSqlModel(
  			SqlServerVersion.Sql120, new TSqlModelOptions()))
      {
          var sqlScript = File.ReadAllText(@"SQLScripts\TSqlModel_1.sql");
          sqlModel.AddObjects(sqlScript);
  
          var validationResult = sqlModel.Validate();
  		if (validationResult.Any())
  			validationResult.ToList().ForEach(
  					message => Console.WriteLine(message.Message));
  		else
  			Console.WriteLine("No error");
      }
  }
  

  За парсинг скриптов отвечает метод AddObjects(), а с помощью метода Validate() можно проверить некоторые ошибки в самой модели.

  Например, если в исходном скрипте мы заменим строку со ссылкой на внешний ключ на вот такую:

  foreign key (UserId) references Users(Id_)
  
  

  то валидация вернет вот такой отчет об ошибках:

  Error validating element on [dbo].[Logins]: 
  The referenced table '[dbo].[Users]' contains no primary or candidate 
  keys that match the referencing column list in the foreign key. If the referenced 
  column is a computed column, it should be persisted.
  
  Error validating element on [dbo].[Logins]: 
  Foreign Key on [dbo].[Logins] has an unresolved 
  reference to Column [dbo].[Users].[Id_].
  

  Добавлять объекты в модель можно многократно.

  Второй вариант получения модели – прочитать её из DACPAC файла:

  public void ReadModelFromDacpacFile()
  {
  	using (var sqlModel = new TSqlModel("Northwind.dacpac"))
  	{
  		Console.WriteLine(sqlModel.Version.ToString());
  	}
  }
  

  Поиск по модели

  Итак, модель прочитана и загружена в память. Что дальше?

  Дальше можно получить список объектов, входящих в модель. Например, вот код, возвращающий список таблиц в модели:

  public void GetTableObjects()
  {
  	using (var sqlModel = new 
  		TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions()))
  	{
  		var sqlScript = File.ReadAllText(
  			@"SQLScripts\TSqlModel_1.sql");
  		sqlModel.AddObjects(sqlScript);
  
  		foreach (var table in sqlModel.GetObjects(
  			DacQueryScopes.All, ModelSchema.Table))
  		{
  			Console.WriteLine(
  				sqlModel.DisplayServices.GetDisplayName(
  					table.Name, 
  					EscapeStyle.DontEscape, 
  					true));
  		}
  	}
  }
  

  Этот код требует ряда пояснений.

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

  public sealed class TSqlModel : IDisposable
  {
       // ...
  
  	public TSqlObject GetObject(ModelTypeClass objectType, ObjectIdentifier id, DacQueryScopes queryScopes);
  	public IEnumerable GetObjects(DacQueryScopes queryScopes, params ModelTypeClass[] typeFilters);
  	public IEnumerable GetObjects(ModelTypeClass objectType, ObjectIdentifier id, DacQueryScopes queryScopes);
  }
  

  Названия методов вполне говорящие, а вот параметры могут сбить с толку.

• DacQueryScopes – описывает область поисков. Возможны следующие значения (их можно комбинировать!):
  

  Значения	Комментарий
  All	Все элементы в модели. Внешние ссылки не обрабатываются
  BuiltIn	Встроенные объекты/типы SQL Server (например, nvarchar)
  Default	= UserDefined + BuiltIn
  None	Пустое пространство поиска
  SameDatabase	Объекты из внешних моделей, на которое есть ссылка из текущей модели, причем ссылка типа “SameDatabase” (т.е. это по сути просто разбиение одной базы по нескольким моделям)
  System	Системные (но не встроенные!) объекты. Например, объекты из базы master (если на эту базу есть ссылка в модели)
  UserDefined	Пользовательские объекты, объявленные в текущей модели)

• ModelTypeClass – описатель типа объекта (например “таблица”, “схема”, “хранимая процедура”, “роль”, “пользователь”, …). Однако, хитрость в том, что данный класс не имеет публичного конструктора, т.е. самостоятельно создавать его экземпляр не получится. Где же взять нужный описатель? Есть 2 варианта:
  • через статическое поле класса ModelSchema (как в примере выше)

  public static class ModelSchema
  {
  	// ...
  
  	public static readonly ModelTypeClass SymmetricKeyPassword;
  	public static readonly ModelTypeClass Synonym;
  	public static readonly ModelTypeClass Table;
  	public static readonly ModelTypeClass TableType;
  	public static readonly ModelTypeClass TableTypeCheckConstraint;
  	public static readonly ModelTypeClass TableTypeColumn;
  	public static readonly ModelTypeClass TableTypeDefaultConstraint;
  	public static readonly ModelTypeClass TableTypeIndex;
  	public static readonly ModelTypeClass TableTypePrimaryKeyConstraint;
  	public static readonly ModelTypeClass TableTypeUniqueConstraint;
  	public static readonly ModelTypeClass TableValuedFunction;
  	public static readonly ModelTypeClass TcpProtocolSpecifier;
  	public static readonly ModelTypeClass UniqueConstraint;
  	public static readonly ModelTypeClass User;
  	// ...
  
  }
  

  • через статическое же поле TypeClass, но в одном из объектов-описателей в пространстве имен Microsoft.SqlServer.Dac.Model. Например, там есть классы Table, View, User, … Эти классы, помимо описателя типа, содержат еще описатели всех возможных свойств и всех возможных ссылок на объекты такого типа. Вот например, как выглядит класс Table:

  public static class Table
  {
  	public static ModelPropertyClass AnsiNullsOn { get; internal set; }
  	public static ModelPropertyClass ChangeDataCaptureEnabled { get; internal set; }
  	public static ModelPropertyClass ChangeTrackingEnabled { get; internal set; }
  	public static ModelRelationshipClass Columns { get; internal set; }
  	public static ModelRelationshipClass DataCompressionOptions { get; internal set; }
  	public static ModelPropertyClass Durability { get; internal set; }
  	public static ModelRelationshipClass Filegroup { get; internal set; }
  	public static ModelRelationshipClass FilegroupForTextImage { get; internal set; }
  	public static ModelRelationshipClass FileStreamFilegroup { get; internal set; }
  	public static ModelPropertyClass FileStreamNull { get; internal set; }
  	public static ModelRelationshipClass FileStreamPartitionScheme { get; internal set; }
  	public static ModelPropertyClass IsReplicated { get; internal set; }
  	public static ModelPropertyClass LargeValueTypesOutOfRow { get; internal set; }
  	public static ModelPropertyClass LockEscalation { get; internal set; }
  	public static ModelPropertyClass MemoryOptimized { get; internal set; }
  	public static ModelRelationshipClass PartitionColumn { get; internal set; }
  	public static ModelRelationshipClass PartitionScheme { get; internal set; }
  	public static ModelPropertyClass QuotedIdentifierOn { get; internal set; }
  	public static ModelRelationshipClass Schema { get; internal set; }
  	public static ModelPropertyClass TableLockOnBulkLoad { get; internal set; }
  	public static ModelPropertyClass TextInRowSize { get; internal set; }
  	public static ModelPropertyClass TrackColumnsUpdated { get; internal set; }
  	public static ModelTypeClass TypeClass { get; internal set; }
  	public static ModelPropertyClass VardecimalStorageFormatEnabled { get; internal set; }
  }
  

  Таким образом строчки

  sqlModel.GetObjects(DacQueryScopes.None, Table.TypeClass)
  

  и

  sqlModel.GetObjects(DacQueryScopes.None, ModelSchema.Table)
  

  будут эквивалентны.

• ObjectIdentifier – имя искомого объекта. Имя SQL-объектов состоят из нескольких частей (например, для таблиц это схема и сама таблица: dbo.Users)

  public void GetSpecifiedTableObject()
  {
  	using (var sqlModel = new TSqlModel(SqlServerVersion.Sql120, 
  		new TSqlModelOptions()))
  	{
  		var sqlScript = File.ReadAllText(@"SQLScripts\TSqlModel_1.sql");
  		sqlModel.AddObjects(sqlScript);
  
  		var table = sqlModel.GetObject(
  			Table.TypeClass, 
  			new ObjectIdentifier("dbo", "Users"), 
  			DacQueryScopes.All);
  
  		Console.WriteLine(sqlModel.DisplayServices.GetDisplayName(
  				table.Name, EscapeStyle.DontEscape, true));
  	}
  }
  

  При этом нужно иметь в виду следующее – объекты модели делятся на 2 группы: TopLevel (“таблицы”, “хранимые процедуры”, …) и все остальные (“столбцы”, “параметры процедур”, …). Это важно, т.к. в запросах объектов через SqlModel можно запрашивать только объектов верхнего уровня.

  Получить полный список всех объектов верхнего уровня и просто всех объектов можно через уже известный объект ModelSchema:

  public void ShowTypesByLevel()
  {
  	Console.WriteLine("-- TopLevel --");
  	foreach (var typeClass in ModelSchema.SchemaInstance.TopLevelTypes)
  	{
  		Console.WriteLine(typeClass.Name);
  	}
  
  	Console.WriteLine("\n-- Non TopLevel --");
  	foreach (var typeClass in ModelSchema.SchemaInstance.AllTypes
  		.Except(ModelSchema.SchemaInstance.TopLevelTypes))
  	{
  		Console.WriteLine(typeClass.Name);
  	}
  }
  

  Например, получение всех объектов верхнего уровня, описанных в схеме будет выглядеть так:

  public void GetAllTopLevelObjects()
  {
  	using (var sqlModel = new TSqlModel("Northwind.dacpac"))
  	{
  		foreach (var sqlObject in sqlModel.GetObjects(
  			DacQueryScopes.UserDefined, 
  			ModelSchema.SchemaInstance.TopLevelTypes.ToArray()))
  		{
  			
  			Console.WriteLine("{0}: {1}",
  				sqlObject.ObjectType.Name,
  				sqlObject.Name.HasName ? 
  					sqlModel.DisplayServices.GetDisplayName(sqlObject.Name, EscapeStyle.DontEscape, true) :
  					""
  				);
  		}
  	}
  }
  
  

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