Эффективность взаимодействия пользователей во многом определяется сервером приложений, который обслуживает клиентские запросы. Новые инструменты в Microsoft SQL Server 2005 позволяют перенести часть задач реализуемых сервером приложения непосредственно на базу данных, а также реализовать получение необходимой пользователю информации в удобном виде.
Сегодня все больше данных хранится, передается и обрабатывается в формате XML, который стал стандартом взаимодействия между различными платформами. Благодаря широкому распространению XML-документы зачастую напрямую сохраняются в базе данных. Ранее в SQL Server 2000 такие документы приходилось записывать как тип "ntext", а единственным способом обращения к их элементам содержимого была команда OPENXML, ее функциональности зачастую не хватало. Для выполнения операции приходилось загружать порцию данных в компонент бизнес-логики на клиенте или в сервере приложения и обрабатывать ее с помощью XML-парсера. Производительность страдала из-за необходимости передачи и обработки излишних данных. С появлением в SQL Server 2005 поддержки XML типа всю обработку можно производить на стороне сервера непосредственно в хранимых процедурах -- можно строить запросы к содержимому Xml-документа прямо из T-SQL и строго типизировать его, присоединив Xsd-схему.
Рассмотрим типичную задачу возникающую при журналировании операций в базе данных. Журнал операций обычно содержит имя учетной записи, код операции, код объекта и некоторые дополнительные данные, специфичные для конкретного события. Каждой операции соответствуют некие исходные параметры и результат, их тип и количество изменяется в зависимости от операции. Это квазиструктурированные данные, которые будет формировать приложение. Удобнее всего данные подобного типа хранить в XML-формате и тогда в SQL 2005 можно построить T-SQL запрос, возвращающий искомые данные записей журнала.
Определим таблицу журнала OperationLog, где каждая строка содержит стандартные атрибуты операции, а в xml поле Data сохраняются дополнительные данные. Допустим, что операция переименования объекта с кодом 1 изменяет его имя с "SomeObject" на "TestObject". Администратор может двумя способами получить учетную запись пользователя, который изменил имя объекта. Первый из них использует метод value типа xml: value (XQuery, SQLType). Где XQuery - запрос XQuery, возвращающий только одно значение, SQLType - тип SQL, к которому следует привести возварщаемое значение. Его удобно использовать для получения скалярных значений из XML-документа. Можно отфильтровать результаты запроса по возвращаемому значению метода value. Но более эффективным способом будет использование метода exist с сигнатурой exist(XQuery). С использованием exist запрос будет выполняться быстрее, потому что SQL Server сможет применить оптимизации, опираясь на внутреннее представление XML-данных.
-- Поиск по значению атрибута
SELECT ID, Account
FROM OperationLog
WHERE Operation = 1 -- Операция переименования
AND ObjectID = 3 -- Идентификатор объекта
AND Data.value('(/Data/Result/@Name)[1]', 'varchar(128)') = 'TestObject'
-- Более эффективный способ поиска
SELECT ID, Account
FROM OperationLog
WHERE Operation = 1 -- Операция переименования
AND ObjectID = 3 -- Идентификатор объекта
AND Data.exist('/Data/Result[@Name = "TestObject"]') = 1
Аналогично можно строить любые запросы к данным журнала. Примечателен метод nodes, который возвращает последовательность XML-узлов из экземпляра xml-типа и полезен при обработке XML-данных в хранимой процедуре. Рассмотрим типичный Internet-магазин, где каждый продукт имеет различные ключевые позиции и особенности. Возможность поиска, быстрый доступ и редактирование параметров являются основными требованиями к такого рода приложениям, поэтому логичным решением будет хранение свойств продукта в специальной таблице базы данных. Каждый параметр продукта в таблице Properties будет уникально идентифицироваться по первичному ключу.
Эффективно организовать поиск можно, определив индекс по названию и значению свойства. Продукт и его описание - это тесно связанные данные, которые зачастую передаются из внешних систем в едином XML-документе. В прежних версиях загрузку подобной информации в базу можно было осуществить с помощью команды OPENXML и ее неизменных спутников sp_xml_preparedocument и sp_xml_removedocument , теперь для этого лучше применить функцию nodes в типе XML. Допустим необходимо загрузить в базу данных новый продукт, описание которого хранится в XML-документе. Для этого надо написать процедуру, которая с помощью метода nodes преобразует XML-тип в реляционные данные и записывает их в базу в соответствии со схемой уровня данных.
CREATE PROCEDURE LoadProduct(@Description xml)
AS
BEGIN
DECLARE @ProductID int
-- Регистрация нового продукта
INSERT Products(ProductName, Price)
SELECT tProducts.Item.value('@Name', 'varchar(128)'),
tProducts.Item.value('@Price', 'money')
FROM @Description.nodes('/Root/Product') tProducts(Item)
SELECT @ProductID = @@IDENTITY
-- Сохранение свойств продукта
INSERT Properties (ProductID, PropName, PropValue)
SELECT @ProductID, tProps.Item.value('@Name', 'varchar(128)') ,
tProps.Item.value('@Value', 'varchar(128)')
FROM @Description.nodes('/Root/Product/Properties/Property') tProps(Item)
END
Использование Common Table Expressions
Традиционно реализация операций на уровне SQL сервера в промышленных приложениях приводит к созданию хранимых процедур с сотнями строк T-SQL кода, что естественно усложняет их поддержку и модернизацию. С появлением Common Table Expressions (CTE) в SQL Server 2005 можно снизить сложность кода в подобных хранимых процедурах. Теперь запрос можно разбить на простые блоки, определив их как СТЕ, и создавать окончательное T-SQL выражение из полученных элементов. Это позволяет улучшить читаемость кода, а значит снизить кол-во ошибок при доработках. Кроме того, вместо многократного использования вложенных запросов в одном T-SQL выражении, лучше применять CTE, т.к. однажды сгенерированный набор строк будет переиспользован при следующем обращении.
-- Определение CTE
WITH Files_CTE(FolderID, FileCount)
AS
(
SELECT FolderID, COUNT(*)
FROM Files
GROUP BY FolderID
)
-- Использование CTE
SELECT tFolders.[Name], tFiles.FileCount
FROM Folders tFolders
JOIN Files_CTE tFiles ON tFolders.ID = tFiles.FolderID
WHERE tFiles.FileCount > 10
ORDER BY tFiles.FileCount DESC
Рекурсивные CTE незаменимы при обработке иерархических данных. В прежних версиях для обхода иерархии сотрудников в компании или структуры папок приходилось использовать временные таблицы и циклы, а в SQL Server 2005 можно просто и наглядно построить рекурсивные выражения для обработки подобных иерархических структур. Для этого достаточно определить CTE с базовым и рекурсивным элементами, объединенными командой UNION ALL.
-- Определение рекурсивного CTE
WITH FolderBranch(FolderID, FolderName, NestLevel)
AS
(
-- Определение базового элемента
SELECT ID, [Name], 1
FROM Folders
WHERE ID = 1 -- Исходная папка с идентификатором 1
UNION ALL
-- Определение рекурсивного элемента
SELECT tData.ID, tData.[Name], tParent.NestLevel + 1
FROM Folders tData
INNER JOIN FolderBranch tParent ON tParent.FolderID = tData.ParentID
)
-- Получение иерархии под папкой с идентификатором 1
SELECT * FROM FolderBranch
Рекурсивные CTE упрощают анализ и обработку иерархий, однако необходимо быть аккуратным при их определении, чтобы шаг рекурсии не возвращал идентичные с родительским уровнем данные, иначе будет создан бесконечный цикл.
Поддержка .NET CLR
Революционным нововведением SQL Server 2005 является поддержка .NET CLR - прежде разработчики были вынуждены реализовывать бизнес-логику в хранимых процедурах на T-SQL и возникали неудобства из-за недостатка доступных функций при обработке наборов данных и громоздкости конструкций по манипулированию ими в циклах и курсорах. К тому же эффективность интерпретируемого языка была ограничена при выходе за пределы табличных операций.
Теперь всю функциональность можно реализовать с использованием обширных возможностей .NET Framework 2.0, а программисту доступен весь набор классов CLR. Для создания хранимой процедуры нужно реализовать необходимую логику в открытом статическом методе класса и пометить его атрибутом SqlProcedure. Взаимодействие с базой данных строится через SqlConnection, связанный с контекстом выполнения процедуры. Операции доступа к данным во многом аналогичны ADO.NET, разве что теперь не нужно указывать строку соединения.
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
[SqlProcedure]
public static void SaveProperties(string properties)
{
if (properties == null // properties.Length == 0) return;
// Инициализация регулярного выражения
Regex regex = new Regex(
"Name=\"(?<Name>.+?)\", Value=\"(?<Value>.*?)\"");
Match match = regex.Match(properties);
using (SqlConnection conn =
new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText =
"INSERT Properties(PropName, PropValue) VALUES(@Name, @Val)";
SqlParameter nameParam =
command.Parameters.Add("@Name", SqlDbType.NVarChar, 128);
SqlParameter valueParam =
command.Parameters.Add("@Val", SqlDbType.NVarChar, 128);
// Запись свойств в таблицу базы данных
while (match.Success)
{
nameParam.Value = match.Groups["Name"].Value;
valueParam.Value = match.Groups["Value"].Value;
command.ExecuteNonQuery();
match = match.NextMatch();
}
}
}
}
Ранее в серверном приложении часто возникала задача обработки данных перед их записью в базу. Теперь эту задачу можно смело реализовывать в хранимой процедуре, а потом загрузить сборку в базу данных с помощью команды CREATE ASSEMBLY и создать хранимую процедуру со ссылкой на метод managed класса. Сигнатура новой процедуры должна быть совместима с определением соответствующего статического метода в CLR сборке. Необходимо убедиться, что поддержка managed кода включена на текущем экземпляре SQL Server 2005. Если это не так, то необходимо воспользоваться системной процедурой sp_configure и командой RECONFIGURE для обновления параметров сервера.
-- Загрузка сборки в базу данных
CREATE ASSEMBLY DataLayer
FROM 'D:\Code\NET\DataLayer\bin\Release\DataLayer.dll'
WITH PERMISSION_SET = SAFE
GO
-- Создание хранимой процедуры
CREATE PROCEDURE SaveProperties(@Properties nvarchar(512))
AS EXTERNAL NAME DataLayer.StoredProcedures.SaveProperties
GO
-- Включение поддержки CLR на текущем экземпляре SQL Server
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
В новой версии бизнес-логика сервера хранилищ может быть полностью воплощена на .NET -- кроме хранимых процедур с помощью CLR можно создавать пользовательские типы данных, триггеры, скалярные, табличные и агрегирующие функции. Управляемый код легче поддерживать и дорабатывать, поэтому использование managed сборок на уровне базы данных облегчает процесс развития системы.
Зачастую в серверах приложений реализована специальная логика для работы с данными, в результате обработка одного серверного метода приводит к нескольким вызовам сервера баз данных, отсюда потери на сетевых операциях и накладные расходы на передачу промежуточных данных. Интеграция SQL Server 2005 c .NET позволяет перенести уровень бизнес-логики на сервер баз данных и повысить эффективность сервера приложений в целом.
Пользовательские типы данных
Пользовательские типы данных (UDT) позволяют расширить стандартный набор типов SQL Server 2005, для этого требуется реализовать класс на одном из языков .NET и зарегистрировать его на сервере. Новый тип можно использовать в определении таблиц и переменных, причем его методы и свойства будут доступны в T-SQL. Каждый UDT должен иметь конструктор без параметров, реализовывать интерфейс INullable, преобразовываться в строку и обратно с помощью методов ToString и Parse, а также поддерживать сериализацию для сохранения экземпляров типа в базе данных. Интерфейс INullable позволяет SQL Server использовать стандартные методы обработки для Null-экземпляра и не тратить ресурсы на его сериализацию и хранение. Необходимые SQL Server метаданные для обработки UDT указываются в атрибуте SqlUserDefinedType.
Параметр Format определяет способ сериализации экземпляров типа. Если UDT содержит данные value-типов, которые совместимы со встроенными SQL типами, то можно указать Format.Native в качестве значения параметра Format, иначе нужно указывать Format.UserDefined и реализовывать интерфейс IBinarySerialize. Параметр МaxByteSize определяет максимальный размер в байтах, требуемый для сохранения сериализованного экземпляра UDT, его необходимо указывать для UserDefined-формата. Хранение пути файловой системы в базе данных - стандартная практика, это могут быть конфигурационные настройки, архивы или ссылки на оригинальные файлы. Поэтому тип SqlPath со свойствами Name, Extension и DirName облегчит обработку подобных данных в хранимых процедурах.
using System;
using System.IO;
using System.Data;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedType(
Format.UserDefined,
IsByteOrdered = true,
MaxByteSize = 600)]
public struct SqlPath : INullable, IBinarySerialize
{
private bool _isNull;
private string _path;
public bool IsNull { get { return _isNull; } }
public static SqlPath Null
{
get
{
SqlPath sqlPath = new SqlPath();
sqlPath._isNull = true;
return sqlPath;
}
}
public override string ToString()
{
return _isNull ? "NULL" : FullPath.Value;
}
public static SqlPath Parse(SqlString data)
{
if (data.IsNull) return Null;
SqlPath sqlPath = new SqlPath();
sqlPath._isNull = false;
sqlPath._path = data.Value;
return sqlPath;
}
public SqlString FullPath
{
get { return _path != null ? _path : string.Empty; }
set
{
if (value.IsNull)
_isNull = true;
else
_path = value.Value;
}
}
public SqlString Name
{
get
{
return _path != null ?
Path.GetFileNameWithoutExtension(_path) : string.Empty;
}
}
public SqlString Extension
{
get
{
return _path != null ?
Path.GetExtension(_path) : string.Empty;
}
}
public SqlString DirName
{
get
{
return _path != null ?
Path.GetDirectoryName(_path) : string.Empty;
}
}
public void Read(BinaryReader r)
{
_path = r.ReadString();
}
public void Write(BinaryWriter w)
{
w.Write(_path != null ? _path : string.Empty);
}
}
Индексация данных является типовой задачей администрирования и оптимизации производительности сервера хранилищ и для ее поддержки SQL Server должен сравнивать бинарные представления данных. Если результат побайтового сравнения сериализованных объектов нового типа аналогичен этой операции в managed коде, то указанный тип данных поддерживает индексацию. Параметр IsByteOrdered в атрибуте SqlUserDefinedType декларирует эту возможность. Перед использованием UDT должен быть зарегистрирован на SQL Server с помощью команды CREATE TYPE. После регистрации новый тип можно использовать для полей в таблицах, для переменных в хранимых процедурах, в отдельных T-SQL выражениях.
-- Создание пользовательского типа
CREATE TYPE SqlPath
EXTERNAL NAME DataLayer.SqlPath
-- Возможности нового типа данных
DECLARE @FilePath SqlPath
SET @FilePath = 'C:\Temp\SomeFile.txt'
SELECT @FilePath.[Name], @FilePath.Extension, @FilePath.DirName
UDT позволяют сохранять композитные данные без необходимости их обработки перед загрузкой и после получения из базы.
Агрегирующие функции
Сервер приложений невозможно представить без средств для функции построения отчетов -- гибкая система отчетности позволяет руководителям оперативно оценить текущую ситуацию и выработать необходимые управленческие решения. Зачастую отчет является показателем проделанной работы и включает данные из различных таблиц базы данных. Однако стандартных агрегирующих функции AVG, SUM, COUNT становится недостаточно, поэтому в SQL Server 2005 появилась возможность создавать пользовательские агрегирующие функции.
Для регистрации новой агрегирующей функции нужно создать CLR-класс, который реализует контракт агрегации (aggregation contract). Этот контракт включает механизм сбора новых значений и сохранения промежуточных результатов и реализуется с помощью методов Init, Accumulate, Terminate и Merge. Атрибут SqlUserDefinedAggregate определяет параметры сериализации и характеристики агрегирующей функции. Метод Init инициализирует агрегирующую функцию для новой группы, метод Accumulate используется для накопления значений. Объединение с промежуточным результатом реализуется с помощью метода Merge, а метод Terminate вычисляет значение агрегирующей функции для накопленных данных. Если CLR-класс содержит данные value-типов, совместимые со встроенными типами данных SQL Server, то для сериализации следует использовать стандартные средства SQL Server, указав в атрибуте SqlUserDefinedAggregate формат Native, иначе в классе нужно реализовывать интерфейс IBinarySerialize, указывать формат UserDefined и параметр MaxByteSize.
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = false,
MaxByteSize = 8000)]
public class Concatenate : IBinarySerialize
{
private StringBuilder _builder;
public void Init()
{
_builder = new StringBuilder();
}
public void Accumulate(SqlString value)
{
if (value.IsNull) return;
_builder.Append(value.Value).Append(", ");
}
public void Merge(Concatenate other)
{
_builder.Append(other._builder);
}
public SqlString Terminate()
{
string output = string.Empty;
if (_builder != null && _builder.Length > 0)
{
//Удаляем завершающую запятую
output = _builder.ToString(0, _builder.Length - 2);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
_builder = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(_builder.ToString());
}
}
При регистрации агрегирующей функции на SQL Server тип входного параметра должен быть совместим с параметром метода Accumulate, а тип возвращаемого значения должен быть совместим с методом Terminate.
-- Создание новой агрегирующей функции
CREATE AGGREGATE Concatenate (@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME DataLayer.Concatenate
-- Пример использования новой функции
SELECT ProductID, dbo.Concatenate(PropValue)
FROM Properties
GROUP BY ProductID
Следует обратить особое внимание на инициализацию свойств алгоритма, т.к. оптимизатор запросов будет составлять план выполнения T-SQL выражения на основе этих значений, ошибка в определении свойств приведет к неправильному результату. Приведенный класс можно использовать для конкатенации строк в одной из колонок результирующего набора. Прежде подобная задача решалась с помощью вспомогательного курсора, теперь появилась логичная возможность расширять список доступных агрегирующих функций.
Заключение
Нововведения в SQL Server 2005 позволяют говорить не просто о поддержке формата XML, а об интеграции с этим стандартом. У разработчика нет необходимости в создании программных компонентов для адаптации данных из внешнего мира к хранилищу данных -- все необходимые возможности уже есть в арсенале продукта. В новой версии отсутствует барьер между кодом приложения и кодом уровня базы данных -- в какой-то степени код стал взаимозаменяемым. С интеграцией CLR на сервере хранилищ стала доступна мощная библиотека классов .NET и разработчик более не стеснен возможностями T-SQL.