(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Реализация сервера приложений на основе SQL Server 2005

Источник: docsvision

Эффективность взаимодействия пользователей во многом определяется сервером приложений, который обслуживает клиентские запросы. Новые инструменты в 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.

 

Ссылки по теме


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 16.07.2008 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
VMware Workstation 14 Player for Linux and Windows, ESD
Контур.Доступ
IBM RATIONAL Quality Manager Quality Professional Authorized User Single Install License + Sw Subscription & Support 12 Months
Business Studio 4.2 Enterprise. Конкурентная лицензия + Business Studio Portal 4.2. Пользовательская именная лицензия.
Quest Software. TOAD for SQL Server Xpert Edition
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
СУБД Oracle "с нуля"
Новые материалы
Вопросы и ответы по MS SQL Server
Мастерская программиста
Проект mic-hard - все об XP - новости, статьи, советы
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100